函数
1单行函数
字符函数，日期型，数据类型转换

  1. SCOTT>select abs(5),abs(-5) from dual; #dual 只有一行一列，用于运算数据测试用 
  2.  
  3.     ABS(5)    ABS(-5) 
  4. ---------- ---------- 
  5.          5          5 
  6. SCOTT>select mod(4,2) from dual; #取余数 
  7.  
  8.   MOD(4,2) 
  9. ---------- 
  10.          0 




SCOTT>


  1. SCOTT>select lower('aaaaaaaaaaFFFFFFFFFFs') from dual; #大小写转换 
  2.  
  3. LOWER('AAAAAAAAAAFFFF 
  4. --------------------- 
  5. aaaaaaaaaaffffffffffs 
  6. SCOTT>select sysdate from dual; #求日期 
  7.  
  8. SYSDATE 
  9. --------- 
  10. 28-FEB-12 


 


  1. SCOTT>select sysdate+1 from dual;#日期加一天 
  2.  
  3. SYSDATE+1 
  4. --------- 
  5. 29-FEB-12 
  6. SCOTT>select to_char(sysdate,'yyyy-mm-dd') from dual; #日期转换 
  7.  
  8. TO_CHAR(SY 
  9. ---------- 
  10. 2012-02-28 
  11. SCOTT>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; #日期转换 
  12.  
  13. TO_CHAR(SYSDATE,'YY 
  14. ------------------- 
  15. 2012-02-28 17:49:20 




 


  1. SCOTT>select ename,to_char(sal,'L999,999,00') from emp; #工资转化为美元 
  2.  
  3. ENAME      TO_CHAR(SAL,'L999,999 
  4. ---------- --------------------- 
  5. SMITH                      $8,00 
  6. ALLEN                     $16,00 
  7. WARD                      $12,50 
  8. JONES                     $29,75 
  9. MARTIN                    $12,50 
  10. BLAKE                     $28,50 
  11. CLARK                     $24,50 
  12. SCOTT                     $30,00 
  13. KING                      $50,00 
  14. TURNER                    $15,00 
  15. ADAMS                     $11,00 
  16. JAMES                      $9,50 
  17. FORD                      $30,00 
  18. MILLER                    $13,00 
  19.  
  20. 14 rows selected. 
  21. null值转换 




nvl


  1. SCOTT>select  ename sql ,comm,nvl(comm,0) from emp; 
  2.  
  3. SQL              COMM NVL(COMM,0) 
  4. ---------- ---------- ----------- 
  5. SMITH                           0 
  6. ALLEN             300         300 
  7. WARD              500         500 
  8. JONES                           0 
  9. MARTIN           1400        1400 
  10. BLAKE                           0 
  11. CLARK                           0 
  12. SCOTT                           0 
  13. KING                            0 
  14. TURNER              0           0 
  15. ADAMS                           0 
  16. JAMES                           0 
  17. FORD                            0 
  18. MILLER                          0 
  19.  
  20. 14 rows selected. 


  1. SCOTT>select ename sql ,comm,nvl2(comm,1,0) from emp; #非空返回一 
  2.  
  3. SQL              COMM NVL2(COMM,1,0) 
  4. ---------- ---------- -------------- 
  5. SMITH                              0 
  6. ALLEN             300              1 
  7. WARD              500              1 
  8. JONES                              0 
  9. MARTIN           1400              1 
  10. BLAKE                              0 
  11. CLARK                              0 
  12. SCOTT                              0 
  13. KING                               0 
  14. TURNER              0              1 
  15. ADAMS                              0 
  16. JAMES                              0 
  17. FORD                               0 
  18. MILLER                             0 




case表达式


  1. SCOTT>select ename,job,sal,case when job='CLERK' then 1.10*sal 
  2.   2  when job='MANAGER' then 1.20*sal 
  3.   3  else sal end "revised_salary" 
  4.   4  from emp; 
  5. #秘书和经理工资各加%10和%20,别的不加 
  6. ENAME      JOB              SAL revised_salary 
  7. ---------- --------- ---------- -------------- 
  8. SMITH      CLERK            800            880 
  9. ALLEN      SALESMAN        1600           1600 
  10. WARD       SALESMAN        1250           1250 
  11. JONES      MANAGER         2975           3570 
  12. MARTIN     SALESMAN        1250           1250 
  13. BLAKE      MANAGER         2850           3420 
  14. CLARK      MANAGER         2450           2940 
  15. SCOTT      ANALYST         3000           3000 
  16. KING       PRESIDENT       5000           5000 
  17. TURNER     SALESMAN        1500           1500 
  18. ADAMS      CLERK           1100           1210 
  19. JAMES      CLERK            950           1045 
  20. FORD       ANALYST         3000           3000 
  21. MILLER     CLERK           1300           1430 
  22.  
  23. 14 rows selected.