oracle常用函数

据才关联出来 3. 分组排序：

rank()over(partitionbysubstr('1'||t.BUSIDEPARTCODE,0,1)orderby descnullslast)

4. 排序：ROW_NUMBER() OVER (ORDERBYcolumn_nameDESC)

5. 分组求数量：count(字段a)over (partition by 字段a) 根据字段a分组 求同个字段a的有多少数

据量

t2.cost_rate

6. 求占百分比的分析函数：

create table test (

name varchar(20), kemuvarchar(20), score number );

insert into test values('testa','yuwen',10); insert into test values('testa','英语',100); insert into test values('testb','yuwen',60); insert into test values('testb','yuwen',120); insert into test values('testc','yuwen',40); select name, score,

ratio_to_report(score) over() as \"占所有科目的百分比\

ratio_to_report(score) over(partition by kemu) as \"占各科目的百分比\" from test ;

7. Sql函数

avg函数：计算查询中某一特定字段资料的算术平均值。 count函数：计算符合查询条件的记录数。

min, max函数：传回指定字段之中符合查询条件的第一条、最末条记录的资料。

first, last函数：传回指定字段之中符合查询条件的最小值、最大值。 stdev函数：计算指定字段之中符合查询条件的标准差。 sum函数：计算指定字段之中符合查询条件的资料总和。 var,函数：计算指定字段之中符合查询条件的变异数估计值。

8. GREATEST(n1,n2,...n) 返回序列中的最大值

例如：SELECT GREATEST(15,5,75,8) \"Greatest\" FROM DUAL; 9. LEAST(n1,n2....n) 返回序列中的最小值

例如：SELECT LEAST(15,5,75,8) LEAST FROM DUAL; 10. NULLIF(c1,c2)

Nullif也是个很有意思的函数。逻辑等价于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END 例如：SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;

11. NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何类型。如果两

者类型不同，则oracle会自动将c2转换为c1的类型。 例如：SELECT NVL(null, '12') FROM DUAL;

12. NVL2(c1,c2,c3) 大家可能都用到nvl，但你用过nvl2吗？如果c1非空则返回c2,如果c1

为空则返回c3

例如：select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual; 13. MOD(n1,n2) 返回n1除n2的余数，如果n2=0则返回n1的值。

例如：SELECT MOD(24,5) FROM DUAL;

14. ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值，n2缺省值为0，如果n2为

负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数，事实上执行时此处的判断并不严谨，即使n2为非整数，它也会自动将n2取整后做处理，但是我文档中其它提到必须为整的地方需要特别注意，如果不为整执行时会报错的)。 例如：SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL; 15. TRUNC(n1[,n2] 返回截尾到n2位小数的n1的值，n2缺省设置为0，当n2为缺省设置时

会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上。 例如：SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL;

16. LOWER(c) 将指定字符串内字符变为小写，支持

CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型 例如：SELECT LOWER('WhaT is tHis') FROM DUAL;

17. UPPER(c) 将指定字符串内字符变为大写，支持

CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型 例如：SELECT UPPER('WhaT is tHis') FROM DUAL;

18. LPAD(c1,n[,c2]) 返回指定长度=n的字符串，需要注意的有几点：

如果n如果n>c1.length and c2 is null，以空格从左向右补充字符长度至n并返回；

如果n>c1.length and c2 is not null，以指定字符c2从左向右补充c1长度至n并返回； 例如：SELECT LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') FROM DUAL; 最后大家再猜一猜，如果n<0，结果会怎么样

19. RPAD(c1,n[,c2]) 返回指定长度=n的字符串，基本与上同，不过补充字符是从右向左方向

正好与上相反；

例如：SELECT RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') FROM DUAL;

20. TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1) 哈哈，被俺无敌的形容方式搞晕头了吧，

这个地方还是看图更明了一些。 看起来很复杂，理解起来很简单：

如果没有指定任何参数则oracle去除c1头尾空格 例如：SELECT TRIM(' WhaT is tHis ') FROM DUAL; 如果指定了c2参数，则oracle去掉c1头尾c2(这个建议细致测试，有多种不同情形的哟)

例如：SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL; 如果指定了leading参数则会去掉c1头部c2

例如：SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL; 如果指定了trailing参数则会去掉c1尾部c2

例如：SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL;

如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗？没区别！) 例如：SELECT TRIM(both 'W' FROM 'WhaT is tHis w W') FROM DUAL;

注意：c2长度=1

21. LTRIM(c1[,c2]) 千万表以为与上面那个长的像，功能也与上面的类似，本函数是从字符串

c1左侧截取掉与指定字符串c2相同的字符并返回。如果c2为空则默认截取空格。 例如：SELECT LTRIM('WWhhhhhaT is tHis w W','Wh') FROM DUAL; 22. RTRIM(c1,c2)与上同，不过方向相反

例如：SELECT RTRIM('WWhhhhhaT is tHis w W','W w') FROM DUAL;

23. REPLACE(c1,c2[,c3]) 将c1字符串中的c2替换为c3，如果c3为空，则从c1中删除所有

c2。

例如：SELECT REPLACE('WWhhhhhaT is tHis w W','W','-') FROM DUAL; 24. LAST_DAY(d) 返回指定时间所在月的最后一天

例如：SELECT last_day(SYSDATE) FROM DUAL;

25. NEXT_DAY(d,n) 返回指定日期后第一个n的日期，n为一周中的某一天。但是，需要注

意的是n如果为字符的话，它的星期形式需要与当前session默认时区中的星期形式相同。

例如：三思用的中文nt，nls_language值为SIMPLIFIED CHINESE SELECT NEXT_DAY(SYSDATE,5) FROM DUAL;

SELECT NEXT_DAY(SYSDATE,'星期四') FROM DUAL; 两种方式都可以取到正确的返回，但是：

SELECT NEXT_DAY(SYSDATE,'Thursday') FROM DUAL;

则会执行出错，提供你说周中的日无效，就是这个原因了。

26. Oracle 中Nextval用法：

SQL Server,Sybase:

有個identity屬性可以讓系統自動增1 create table a (

a1 int identity(1,1), a2 varchar(6) )

然後在insert時:

insert into a values( 'hello! '); --不用管a1,系統會幫你自動增1

Oracle:

使用SEQUENCE(序列)可以達到你的要求

create table a (

a1 int ,

a2 varchar2(6) );

create SEQUENCE seq_a INCREMENT BY 1;

然後在insert時:

insert into a values(seq_a.nextval, 'hello! '); --seq_a.nextval是該序列的下個值

27. Oracle的序列sequence 的两个函数：

CurrVal：返回 sequence的当前值

NextVal：增加sequence的值，然后返回增加后sequence值

select seq_t_inlayout_log.currval into v_LogID from dual; 28. oracle 查询本月第一天，本年第一天，本季第一天

selecttrunc(v_enddate,'month')intov_firstday_monthfrom dual;--当月第一天 selecttrunc(v_enddate,'year')intov_firstday_yearfrom dual;--当年第一天

SELECT to_char(TRUNC(SYSDATE, 'Q'), 'YYYY-MM-DD') FROM dual;