Oracle常用函数有哪些？

引言

虚表dual，它是一行一列，没有任何数据，常常用于测试。

它是 oracle 特有的用于做函数测试的。

Oracle 常用函数

字符函数

upper(str) --,转为大写
lower(str)--,转为小写
substr(str,n,m)-- ,从 n 位开始，截取 m 个字符
substr(str,n)--,从 n 位开始，截取后面字符
length(str),--得到字符串的长度
ltrim(str),--去掉左边空格
rtrim(str),--去掉右边空格
instr(str,c),--得到字符 c 在 str 的位置
lpad(str,n,c),--将 str 补足为 n 位长度,不足左边用字符 c 代替
rpad(str,n,c),--将 str 补足为 n 位长度,不足右边以字符 c 代替 
concat --连接字符串
initcap(参数) --使当前的字符串的单词的首字母大写

select  initcap('hello mayunbiji') from dual;

select substr('hello',1,2) from dual ;--小标从 1 开始

select * from emp where ename=upper（‘&name’）;

数值函数

• ceil : 向上取整
• floor: 向下取整
• round(col,n) 四舍五入

round(457.628,2),小数点后 2 位四舍五入
      结果 457.63
round(457.628,-1),小数点前 1 位四舍五入
      结果 460

trunc(col,l) 截断数值：

当l为正数时，表示截取小数点右边的位数；l为负数时，表示过滤小数点左边的位数。当l不写的时候，效果与l=0一样。

trunc(457.628,2)  结果 457.62
trunc(457.628,-1) 结果 450 

日期函数

• next_day(参考日期，字符/数字) 下一个星期几的日期
• months_between(date1,date2),两个日期间的月数，结果为实数。
• add_months(date,m),增加 m 个月，m 可以为负数，结果为减少 m 个月。
• round,日期四舍五入trunc,截断日期
• last_day ,当月最后一天
• sysdate 获取当前系统时间

日期函数例子：

--当前日期增加 1 个月
select add_months(sysdate,1) from dual;
--去年同月
select add_months(sysdate,-12) from dual;
--年
select trunc(sysdate,'YYYY') from dual;
--月
select  trunc(sysdate,'MM') from dual;
--精确到天，截断小时分秒
select  trunc(sysdate) from dual;
-- 当月最后一天
select last_day(sysdate) from dual;

转换函数

日期转为字符

to_char(date1,’format_model’)
--format_model:转换后的显示格式
--YYYY  年,MM 月,DD 日,HH24 小时,MI 分,SS 秒

例子：

select * from emp where to_char(hiredate,'yyyy-mm-dd')='1980-12-17';

select  to_char(sysdate,'mm/dd/yyyy hh:mi:ss') from dual;

select  to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') from dual;--hh24 表示采用 24 小时制

select  to_char(sysdate,'yyyy-mm-dd day am') from dual；--day 表示星期几

字符转为日期

to_date('2007-11-11','YYYY-MM-DD')

数值转为字符

select  to_char(55676,'fm99,999.00')  from dual
--fm 表示去掉前面的空格和 0
--结果: 55,676.00

select to_char(1234) from dual;

select to_char(1234,'$999.99') from dual;

select to_char(1234,'l999.99') from dual;

select to_char(1234,'l0,0099') from dual;

to_number函数 将字符串转换成数字

select to_number('1234','9999.99') from dual ;--999.99 为字符串的输入格式
select to_number('1234') from dual ;
select to_number('$1234.56','$999.99') from dual;
select to_number('￥1,234.56'，‘L9,999.99’) from dual ;--使用￥或 RMB 表示金额，具体要看系统采用的表示方式。L9,999.99 只能使用 0 和 9。也可以由逗号和点号。

通用函数

nvl(字段，默认值)： 用于判断字段是否为空，如果为空就返回指定的值，否则返回字段本身的值。

select enamel，sal+nvl(comm,0) from emp;

Decode： 分支判断

    Decode(表达式, 条件 1,结果 1,
                                   条件 2,结果 2,
                                    …
                                      ,默认结果)
   select decode（&no,1,'值 1'，2，'值 2'） from dual

聚合函数(组函数)

多行函数，输入一组记录，输出一行记录。

max、min、avg、sum和count函数。

• 聚合函数，不能出现在 where 子句中， 比如where avg(salary) >4000。
• 除了 count(*)之外，其它的不统计 null 值

count(*)  --,所有行数量
count(swjg_mc)--,swjg_mc 非 null 值的记录的数量
count(distinct swjg_dm)--,去掉重复的记录
count(1),--第一列非 null 值的记录的数量

伪列：给返回结果加上序号

rownum伪列，可以给返回结果加上序号。

如果有 order by，序号是乱的，因为序号是 order by 之前分配的。

加一个子查询可以解决序号是乱的问题

select t.*, rownum
  from (select swjg_dm, swjg_mc 
             from dm_swjg 
       order by swjg_dm ) t

常用的分析函数

row_number() over(partition by ... order by ...) 
rank() over(partition by ... order by ...) 
dense_rank() over(partition by ... order by ...) 
count() over(partition by ... order by ...) 
max() over(partition by ... order by ...) 
min() over(partition by ... order by ...) 
sum() over(partition by ... order by ...) 
avg() over(partition by ... order by ...) 
first_value() over(partition by ... order by ...) 
last_value() over(partition by ... order by ...) 
lag() over(partition by ... order by ...) 
lead() over(partition by ... order by ...)