pl/sql函数

1. CEIL(x)函数，用来返回大于或等于X的最小整数。
2. FLOOR(x)函数，用来返回小于或等于X的最大整数。
3. MOD(被除数，除数)求余函数，如果除数为0，则返回被除数。
4. POWER(x,y)函数，返回X的Y次幂。底X和指数Y都不必是正整数，但如果X是负数的话，Y必须是整数。
5. TRUNC(x[,y])截取值函数，Y缺省为0，这样X被截取成一个整数。如果Y为负数，那么截取到小数点左边相应位置
6. ADD_MONTHS(date,x)函数，返回加上X月后的日期DATE的值。X可以是任意整数。如果结果的月份中所包含的   日分量少于DATE的月份的日分量，则返回结果月份的最后一天。如果不小于，则    结果与DATE的日分量相同。时间分量也相同。
7. LAST_DAY（日期） 指定日期所在月份的最后一天的日期，这个函数可用来确定本月还有多少天。
8. TO_NUMBER(char[,'format_model'])  字符转换到数字类型
9. TO_DATE(char[,'format_model'])  字符转换到日期类型
     格式说明符：要与前边要转换的字符串的格式要相同才能转换（匹配问题：格式和位数）。
10. TO_DATE(char[,'format_model'])  字符转换到日期类型
      格式说明符：要与前边要转换的字符串的格式要相同才能转换（匹配问题：格式和位数）。
11. TO_CHAR(date[,'format_model'[,nlsparams]])
      第二个参数可以省略，不指定格式，按系统默认格式输出。
12. greatest(x,y,...)函数，返回参数列表中的最大值。其参数的类型是由第一个参数决定的，可以为数值型、日期型  、和字符型等，后面的参数被强制转换成此种数据类型。
13. least(x,y,......)函数，返回列表参数中的最小值。
14. NVL(EXPR1,EXPR2)
    类型必须匹配，如果EXPR1是NULL，则返回EXPR2，否则返回EXPR1。返回值与EXPR1类型相同，除非EXPR1是字符类型，在这种情况下将返回VARCHAR2类型。这个函数用于确保查询记录集中不包含NULL值。
15. CASE 表达式 WHEN  条件1 THEN 返回值1
         WHEN  条件2 THEN 返回值2
            WHEN  条件n THEN 返回值n
    ELSE 返回值
    END  
16. DECODE函数 DECODE（
     条件,比较值1，返回值1
          比较值2，返回值2
          .
           .
          .
          比较值n，返回值n
          返回值（不满足条件时）        
    ）
17. 字符连接 --  SELECT CONCAT('Hello','World') FROM DUAL;
18. ·  --3,求字母串中的某个子串

 SELECT SUBSTR('Hello World',3) FROM DUAL;

19. SELECT SUBSTR('Hello World',3,4) FROM DUAL;

 --截取从第3个字符连续4个字符

  20--4,字符数和字节数

SELECT LENGTH('China') 字符数,

LENGTHB('China') 字节数

FROM DUAL;

21.  --5,在母串中，查找子串的位置

SELECT INSTR('Hello World','ll')

FROM DUAL;

22. --6,左右填充，将abcd用*填充到10位

SELECT LPAD('abcd',10,'*') 左填充,

RPAD('abcd',10,'*') 右填充

FROM DUAL;

23. --7,去掉字符串前后指定的字符

SELECT TRIM('H' FROM 'Hello WorldH')

FROM DUAL;

24. --8,字符串替换函数

SELECT REPLACE('Hello World','l','*')

FROM DUAL;

25. --9,数字函数

SELECT ROUND(45.926，2) 四舍五入,

TRUNC(45.926,2) 截断,

MOD(1600，300) 求余

FROM DUAL;

26. ROUND函数

SELECT ROUND(45.923,0) 整数位,

ROUND(45.923,-1) 十位,

ROUND(45.923，-2) 百位

FROM DUAL;

27. --12,指定格式显示时间

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')

FROM DUAL;

 

28.--13,显示昨天，今天和明天，加减数字

SELECT SYSDATE - 1 昨天,

SYSDATE 今天,

SYSDATE + 1 明天

FROM DUAL;

 

29. --14,两个日期相减，结果为相差的天数，查询员工

--信息，显示员工工龄，两个日期不能相加

SELECT EMPNO,ENAME,SYSDATE - HIREDATE 天

FROM EMP;

30. --15,查询员工信息，显示员工工龄，分别按照天，

--星期，月显示

SELECT EMPNO,ENAME,SYSDATE - HIREDATE 天,

(SYSDATE - HIREDATE)/7 星期, (SYSDATE - HIREDATE)/30 月

FROM EMP;

31. --16,两个日期相差的月数

SELECT (SYSDATE-HIREDATE)/30 方式一,

MONTHS_BETWEEN(SYSDATE,HIREDATE) 方式二

FROM EMP;

32. --17,在指定日期上加上若干月

SELECT ADD_MONTHS(SYSDATE,1) 下个月,

ADD_MONTHS(SYSDATE,123) "123个月后"

FROM DUAL;

33. --18,某个日期当月的最后一天

SELECT LAST_DAY(SYSDATE)

FROM DUAL;

34 --19,下周六

SELECT NEXT_DAY(SYSDATE,'星期五')

FROM DUAL;

35. --20,对日期进行四舍五入

SELECT ROUND(SYSDATE,'MONTH') 月,

ROUND(SYSDATE,'YEAR')

FROM DUAL;

36. --21,对日期进行截断

SELECT TRUNC(SYSDATE,'MONTH') 月,

ROUND(SYSDATE,'YEAR')

FROM DUAL;

37. --22,日期格式

SELECT *

FROM EMP

WHERE HIREDATE =

TO_DATE('1982-01-23','YYYY-MM-DD');

38. --23,以指定格式当前日期

SELECT TO_CHAR(SYSDATE,

'YYYY-MM-DD HH24:MI:SS"今天是"DAY')

FROM DUAL;

39. --24,查询员工信息，显示员工的编号，姓名，月薪，

--要求有货币代码（L），千位符（，），小数点

SELECT EMPNO,ENAME,

TO_CHAR(SAL,'L9,999.99')

FROM EMP;

40. --25,NVL(EXP1，EXP2)：当exp1为空时，返回exp2

--NUL2(EXP1，EXP2，EXP3)：当exp1有值，返回exp2；

--否则返回exp3

SELECT ENAME,SAL*12+NVL2(COMM,COMM,0) 年收入

FROM EMP;

41. --26,NULLIF(EXP1，EXP2),如果exp1=exp2，返回null

--否则exp1

SELECT NULLIF('ABC','ABC'),NULLIF('A','AG')

FROM DUAL;

42. --27,找到参数列表中，第一个不为空的值

SELECT ENAME,COMM,SAL,COALESCE(COMM,SAL)

FROM EMP;

43. --28,给员工涨工资，根据职位涨，总裁涨1000，

--经理涨600，其他员工涨400

SELECT ENAME,JOB,SAL 涨前工资,

CASE JOB

     WHEN 'PRESIDENT' THEN SAL + 1000

     WHEN 'MANAGER' THEN SAL + 600

     ELSE SAL + 400

END

涨后工资

FROM EMP;

--------------------------------------------

SELECT ENAME,JOB,SAL 涨前工资,

DECODE(JOB,'PRESIDENT',SAL + 1000，

'MANAGER',SAL + 600, SAL + 400) 涨后工资

FROM EMP;

44. --类型转换为CHAR类型

SELECT TO_CHAR('AABBCC')

FROM DUAL;

45. --30,TO_CHAR(D[,FMT]):将指定的时间（DATE,TIMESTAMP,

--TIMESTAMP WITH TIME ZONE）按照指定格式转换为

--VARCHAR2类型。

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')

FROM DUAL;

46. --31,TO_CHAR(N,[,FMT])将指定数值n按照指定格式fmt

--转换为varchar2类型并返回

SELECT TO_CHAR(-100，'L999G99D99MI')

FROM DUAL;

47. --32,TO_DATE(C[,FMT[,NLS]])将char，nchar，

--varchar2，nvarchar2转换为日期类型，如果fmt

--参数不为空，则按照fmt中指定格式进行转换。

--注意这里的fmt参数，如果ftm为’J‘，则表示按照公元制

--（Julian day）转换，c则必须为大于0并小于5373484

--的正整数。为什么公元制的化，c的值必须不大于5373484

--呢？因为Oracle的DATE类型的取值范围是公元前

--4712年1月1日至公元9999年12月31日。

SELECT TO_DATE(2454334,'J')

FROM DUAL;

48. --33,TO_NUMBER(C,[,FMT[,NLS]])将char，nchar，

--varchar2，nvarchar2型字符串按照fmt中指定格式

--转换为数值类型并返回。

SELECT TO_NUMBER('-100.00','9G999D99')

FROM DUAL;

49. --34,DECODE(EXP,S1，R1，S2，R2，R[,DEF])可以

--把它理解成一个增强型的if else，只不过它

--并不通过多行语句，而是在一个函数内部实现if

--else的功能。exp作为初始参数，s作为对比值，

--相同则返回r，如果s有多个，则持续遍历所有s，

--直到某个条件为真为止，否则返回默认值def（

--如果指定了的话），如果没有默认值，并且前面

--的对比也都没有为真，则返回空。毫无疑问，

--decode是个非常重要的函数，在实现行转列等功能是，

--都会用到。

SELECT DECODE('a2','a1','true1','a2','true2',

'default')

FROM DUAL;

50. --35,GREATEST(N1,N2,...N)返回序列中的最大值

SELECT GREATEST(14,3,5,3)

FROM DUAL;

51. --36,LEAST(N1，N2，...N)返回序列中最小值

SELECT LEAST(12,3,5,5)

FROM DUAL;

52. --37,NULLIF(C1，C2)逻辑等于CASE WHEN C1 = C2

--THEN NULL ELSE C1 END

SELECT NULLIF('A','B'),NULLIF('A','A')

FROM DUAL;

53. --38,NVL(C1，C2)逻辑等价于IF C1 IS NULL THEN

--C2 ELSE C1 END,C1，C2可以是任何类型，如果两者

--类型不同，则oracle会自动将c2转换为c1的类型

SELECT NVL(NULL,'12')

FROM DUAL;

54. --39,NVL2(C1，C2，C3)如果c1非空则返回c2，如果c1为

--空则返回c3

SELECT NVL2('A','B','C') ,

NVL2(NULL,'B','C')

FROM DUAL;

55. --40,SYS_CONNECT_BY_PATH(COL,C)该函数只能应用于

--树状查询，返回通过c1连接的从根节点的路径，该

--函数必须与connect by子句共同使用