Oracle基本类型函数

oracle基本函数

字符类型及函数

lpad() 向左补全字符串：select lpad(stuno,6,'0') from t_user3;
rpad() 向右补全字符串：select rpad(stuno,6,'0') from t_user3;
lower() 返回字符串小写：select lower(userName) from t_user3;
upper() 返回字符串大写：select upper(userName) from t_user3;
initcap() 单词首字符大写：select initcap(userName) from t_user3;
length() 返回字符串长度：select length(password) from t_user3;
substr() 截取字符串：select substr(userName,1,2) from t_user3;
instr() 获取字符串出现的位置：select instr(password,'23',2,2) from t_user3;//从2开始第二次出现的位置
ltrim() 删除左侧空格:select ltrim(userName) from t_user3;
rtrim() 删除右侧空格:select rtrim(userName) from t_user3;
trim() 删除两侧空格:select trim(userName) from t_user3;
concat() 串联字符串：select concat(userName,password) from t_user3;
reverse() 反转字符串：select reverse(userName) from t_user3;

数值类型及函数

abs() 求绝对值；select abs(n1) from t_number where id=1;
round() 四舍五入：select round(n1,2) from t_number where id=1;//2代表小数位四舍五入
ceil() 向上取整：select ceil(n1) from t_number where id=2;
floor 向下取整：select floor(n1) from t_number where id=2;
Mod()取模：select mod(5,3) from dual;
Sign()正负性：select sign(n1) from t_number where id=1;
Sqrt() 求平方根：select sqrt(9) from dual;
Power()求乘方：select power(2,3) from dual;
Trunc()截取：select trunc(123.456,2) from dual; //123.45
To_char() 格式化数值：常见的字符匹配有	0、9、，、$、FM、L、C select to_char(123.45,'0000.000') from dual;//0123.450
select to_char(123.45,'9999.999') from dual; select to_char(123123,'99,999,999.99') from dual;//123.450
select to_char(123123.3,'FM99,999,999.99') from dual; //去掉前后空格
select to_char(123123.3,'$99,999,999.99') from dual; 
、select to_char(123123.3,'L99,999,999.99') from dual;
 select to_char(123123.3,'99,999,999.99C') from dual;

日期类型及函数

Date 和 timestamp（时间戳）

Date 包含信息 century（世纪信息） year 年 month 月 day 日 hour 小时 minute  分钟 second 秒

Timestamp 一般用于日期时间要求非常精确的情况，精确到毫秒级；

insert into t_date values(1,sysdate,systimestamp);

select sysdate from  dual; select systimestamp from dual;

Add_months 添加月份 select add_months(d1,2) from t_date where id=1;//添加2个月
Last_day 返回指定日期月份的最后一天 select last_day(d1) from t_date where id=1;

update t_date set d3=to_date('2016-12-20','YYYY-MM-DD') where id=1;
update t_date set d3=to_date('2016-12-20 18:31:34','YYYY-MM-DD HH24:MI:SS') where id=1;

Months_between 返回两个日期的相差月数 select months_between(d1,d3) from t_date where id=1;
next_day 返回特定日期之后的一周内的日期：select next_day(d1,2) from t_date where id=1;//下一周的星期一
Trunc 截取日期：
select trunc(d1,'YYYY') from t_date where id=1; select trunc(d1,'MM') from t_date where id=1; select trunc(d1,'DD') from t_date where id=1; select trunc(d1,'HH') from t_date where id=1; select trunc(d1,'MI') from t_date where id=1;

Extract 返回日期的某个域：
select extract(year from sysdate) from dual; 
select extract(month from sysdate) from dual; select extract(day from sysdate) from dual;
select extract(Hour from systimestamp) from dual; select extract(minute from systimestamp) from dual; select extract(second from systimestamp) from dual;

To_char 将日期转换成字符串：
select to_char(d1,'YYYY-MM-DD') from t_date where id=1;
select to_char(d1,'YYYY-MM-DD HH24:MI:SS') from t_date where id=1;

聚合函数

Max 求最大值：select max(sal)	from emp ;
Min 求最小值：select min(sal)	from emp ;
Avg  求平均值：select avg(sal)	from emp ;
Sum 求和：select sum(sal)	from emp ;
Count 统计记录数：select count(ename)	from emp ;

根据创建日期查询最新的一条数据

left join 
  (select an.company_id,an.flow_node_id,an.create_time from pb_advice_flow_node an where an.advice_flow_id=(select ADVICE_FLOW_ID from pb_advice_flow_node where FLOW_NODE_ID = 'b63248c4b4274e2ca40e1c7fe3aa455c')
  and an.create_time = (select max(create_time) from pb_advice_flow_node where advice_flow_id=(select ADVICE_FLOW_ID from pb_advice_flow_node where FLOW_NODE_ID = 'b63248c4b4274e2ca40e1c7fe3aa455c'))) fo
  on o.flowNodeId = fo.flow_node_id