ORACLE-常用函数

【一】、Oracle常用的统计函数

Avg(x):       求一组行中列x值的平均值
count(x):     求一组行中列x值的非空行数
count(*):     求一组行的总行数
max(x):       求一组行中列x值的最大值
min(x):       求一组行中列x值的最小值
stddev(x):    求一组行中列x值的标准差
sum(x):       求一组行中列x值的总和
variance(x):  求一组行中列x值的方差

【二】、group by与统计函数

使用上面介绍的函数时可以使用也可以不使用group by ,但在使用group by时，未在group by部分用到的列在select 部分出现时必须使用统计函数，如按角色统计平均年龄

Select user_name,avg(age) from users
Group by role_id; ×
Select count(user_name),avg(age) from users
Group by role_id; √

【三】、用having字句规定统计条件

having 子句的作用类似于where子句，只不过where 子句针对单个行，而having子句针对的是统计结果，一般和统计的函数搭配使用。Having子句后必须为前面select后面的子部分，或是group by 后面的字段

select count(uer_name),avg(age) from users group by role_id having role_id>20; ×
select count(uer_name),avg(age) from users group by role_id having avg(age)>20; √

【四】时间计算函数

两个Date类型字段：START_DATE，END_DATE，计算这两个日期的时间差（分别以天，小时，分钟，秒，毫秒）：

天：  ROUND(TO_NUMBER(END_DATE - START_DATE))
小时：ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)
分钟：ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)
秒：  ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)
毫秒：ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 1000)

【五】其他oracle常用函数

Decode(column1,value1,output1,value2,output2,…..)
如果column1 有一个值为value1那么将会用output1 来代替当前值,如果column1 的值为value2 那么就用OUTPUT2 来代替当前值,如果column1 中哪两个值都不是,那么就会用OUTPUT3 来代替当前值

Select decode(age,10,7,9,6,3),user_name from users;

创建一个日期计算函数,排除周末

create or replace function cacu_day(beg_day in date, end_day in date)
  return number is
  v_result number(1) := 0;
  v_sqls   varchar2(1000);
begin
  v_sqls := 'with t as
                   (select rownum - 1 rn from dual connect by rownum <= 100)
                  select count(*)
                    from t
                   where :begDay + rn between :begDay and :endDay
                     and to_char(:begDay + rn, ''d'') not in (6, 7)';

  execute immediate v_sqls
    into v_result
    USING beg_day, beg_day, end_day, beg_day;

  return(v_result);
end cacu_day;