MyException - 我的异常网
当前位置:我的异常网» 数据库 » oracle经验2-单行函数理论讲解与案例分析.doc

oracle经验2-单行函数理论讲解与案例分析.doc

www.myexceptions.net  网友分享于:2013-04-10  浏览:1次
oracle心得2--单行函数理论讲解与案例分析.doc

Sql有两种函数,单行函数和多行函数

1.单行函数

单行函数:操作数据对象、接受参数返回一个结果、只对一行进行变换、每行返回一个结果、可以转换数据类型、可以嵌套、参数可以是一列或一个值

DUAL是一个‘伪表’,可以用来测试函数和表达式

2.字符函数

大小写控制函数:这类函数改变字符的大小写。

例子:

select lower(ename) from emp;

LOWER(ENAM                                                                                          
----------                                                                                          
smith                                                                                               
allen                                                                                               
ward                                                                                

select upper(ename) from emp;

UPPER(ENAM                                                                                          
----------                                                                                          
SMITH                                                                                               
ALLEN                                                                                               
WARD        

select initcap(ename) from emp;

INITCAP(EN                                                                                          
----------                                                                                          
Smith                                                                                               
Allen                                                                                               
Ward     


字符控制函数:

select concat('hello','word')from dual;

CONCAT('H                                                                                           
---------                                                                                           
helloword  

select substr('helloword',1,3) from dual;

SUB                                                                                                 
---                                                                                                 
hel                                                                           

select length('helloword') from dual;

LENGTH('HELLOWORD')                                                                                 
-------------------                                                                                 
                  9  

select instr('helloworld','w') from dual;

INSTR('HELLOWORLD','W')                                                                             
-----------------------                                                                             
                      6  

select lpad('hello',10,'*')from dual;

LPAD('HELL                                                                                          
----------                                                                                          
*****hello

select rpad('hello',10,'#')from dual;

RPAD('HELL                                                                                          
----------                                                                                          
hello#####  

select trim('  hello  ') from dual;

TRIM(                                                                                               
-----                                                                                               
hello             


 

3.数字函数

ROUND:四舍五入

TRUNC: 截断

MOD:求余

Round 函数 :语法为ROUND(number,num_digits);其中Number是需要进行四舍五入的数字;Num_digits为指定的位数,按此位数进行四舍五入,如果 num_digits 大于 0,则四舍五入到指定的小数位; Num_digits值为多少就到相应的小数点位置四舍五入,如果 num_digits等于 0,则四舍五入到最接近的整数,如果 num_digits 小于 0,则在小数点左侧进行四舍五入;Num_digits值多少就到小数点左侧的整数相应的位置四舍五入。

例如:

ROUND(2.149, 0) 将 2.149 四舍五入到一个整数结果为2。

ROUND(2.15, 1) 将 2.15 四舍五入到一个小数位,结果为2.2。

ROUND(2.149, 1) 将 2.149 四舍五入到一个小数位结果为2.1。

ROUND(-1.475, 2) 将 -1.475 四舍五入到两小数位结果为-1.48)。

ROUND(21.5, -1) 将 21.5 四舍五入到小数点左侧一位结果为20。

例子:


 

SQL> select round(45.926,2) from dual;

ROUND(45.926,2)                                                                                     
---------------                                                                                     
          45.93                                                                                     

SQL> select round(45.926,-2) from dual;

ROUND(45.926,-2)                                                                                    
----------------                                                                                    
               0                                                                                    

SQL> select round(55.926,-2) from dual;

ROUND(55.926,-2)                                                                                    
----------------                                                                                    
             100                                                                                    

SQL> select round(50.926,-2) from dual;

ROUND(50.926,-2)                                                                                    
----------------                                                                                    
             100                                                                                    

SQL> select round(150.926,-2) from dual;

ROUND(150.926,-2)                                                                                   
-----------------                                                                                   
              200                                                                                   

SQL> select round(50.326,-2) from dual;

ROUND(50.326,-2)                                                                                    
----------------                                                                                    
             100                                                                                    

SQL> select round(550.326,-2) from dual;

ROUND(550.326,-2)                                                                                   
-----------------                                                                                   
              600                                                                                   

SQL> select trunc(45.926,2) from dual;

TRUNC(45.926,2)                                                                                     
---------------                                                                                     
          45.92                                                                                     

SQL> select trunc(45.926,-2) from dual;

TRUNC(45.926,-2)                                                                                    
----------------                                                                                    
               0                                                                                    

SQL> select trunc(55.926,-2) from dual;

TRUNC(55.926,-2)                                                                                    
----------------                                                                                    
               0                                                                                    

SQL> select trunc(155.926,-2) from dual;

TRUNC(155.926,-2)                                                                                   
-----------------                                                                                   
              100                                                                                   

SQL> select mod(1600,300) from dual;

MOD(1600,300)                                                                                       
-------------                                                                                       
          100                                                                                       

SQL> select mod(13,3) from dual;

 MOD(13,3)                                                                                          
----------                                                                                          
         1                                                                                          

SQL> select round(45.926,-1) from dual;

ROUND(45.926,-1)                                                                                    
----------------                                                                                    
              50             


 

4.日期

Oracle中的日期型数据实际含有两个值: 日期和时间。

默认的日期格式是 DD-MON-RR.函数SYSDATE 返回:日期、时间

在日期上加上或减去一个数字结果仍为日期。两个日期相减返回日期之间相差的天数。可以用数字除24来向日期中加上或减去小时。

日期函数

  注:日期转换格式不支持转换中文格式的日期

例子:

SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;

 

TO_CHAR(SY                                                                                                              

----------                                                                                                              

2013-04-04                                                                                                              

 

 

SQL> select to_char(sysdate,'yyyy/mm/dd') from dual;

 

TO_CHAR(SY                                                                                                              

----------                                                                                                              

2013/04/04            

 

select to_char(sysdate,'YEAR-MONTH-DAY') from dual;

 

TO_CHAR(SYSDATE,'YEAR-MONTH-DAY')                                                                                       

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

TWENTY THIRTEEN-4月 -星期四

 

select to_date('1212-12-12','yyyy/mm/dd') from dual;

 

TO_DATE('1212-                                                                                                          

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

12-12月-12

 

SQL> select to_date('1212-12-12','yyyy-mm-dd') from dual;

 

TO_DATE('1212-                                                                                                          

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

12-12月-12                                                                                                              

 

SQL> select to_char(sysdate,'dd month year') from dual;

 

TO_CHAR(SYSDATE,'DDMONTHYEAR')                                                                                          

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

04 4月  twenty thirteen                                                                                                 

 

SQL> select to_char(sysdate,'dd month yyyy') from dual;

 

TO_CHAR(SYSDAT                                                                                                          

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

04 4月  2013        


                                                                    

                                                                  

5.转换函数

隐式数据类型转换:Oracle自动完成下列转换:

TO_CHAR 函数对日期的转换

格式:必须包含在单引号中而且大小写敏感。可以包含任意的有效的日期格式。日期之间用逗号隔开。

例子:

select ename,to_char(sal,'$999,999.00') from emp;

ENAME      TO_CHAR(SAL,                                                                                                 
---------- ------------                                                                                                 
SMITH           $800.00                                                                                                 
ALLEN          $1,600.00                                                                                                 
WARD           $1,250.00     

select ename,to_char(sal,'l999,999.00') from emp;

ENAME      TO_CHAR(SAL,'L999,999                                                                                        
---------- ---------------------                                                                                        
SMITH                   ¥800.00                                                                                        
ALLEN                 ¥1,600.00                                                                                        
WARD                  ¥1,250.00      


 

6. 通用函数

这些函数适用于任何数据类型,同时也适用于空值:

NVL (expr1, expr2):将空值转换成一个已知的值:可以使用的数据类型有日期、字符、数字。

函数的一般形式:

NVL(commission_pct,0)

NVL(hire_date,'01-JAN-97')

NVL(job_id,'No Job Yet')

NVL2 (expr1, expr2, expr3) : expr1不为NULL,返回expr2;为NULL,返回expr3。相当于java中的三目运算符

NULLIF (expr1, expr2) : 相等返回NULL,不等返回expr1

COALESCE (expr1, expr2, ..., exprn):COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。如果第一个表达式为空,则返回下一个表达式,对其他的参数进行COALESCE 。

例子:

SQL> select ename,nvl(comm,0) from emp;

 

ENAME      NVL(COMM,0)                                                                                                  

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

SMITH                0                                                                                                  

ALLEN              300                                                                                                  

WARD               500   

 

错误写法,条件comm与0位置混乱

SQL> select ename,nvl2(comm,0,comm) from emp;

 

ENAME      NVL2(COMM,0,COMM)                                                                                            

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

SMITH                                                                                                                   

ALLEN                      0                                                                                            

WARD                       0                                                                                            

JONES                         

 

正确写法:

SQL> select ename,nvl2(comm,comm,0) from emp;

 

ENAME      NVL2(COMM,COMM,0)                                                                                            

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

SMITH                      0                                                                                            

ALLEN                    300                                                                                            

WARD                     500    

 

 

SQL> select nullif(2,2) from dual;

 

NULLIF(2,2)                                                                                                             

-----------                                                                                                             

                                                                                                                        

 

SQL> select nullif(2,1) from dual;

 

NULLIF(2,1)                                                                                                             

-----------                                                                                                             

          2                                                                                                             

 

SQL> select nullif(1,2) from dual;

 

NULLIF(1,2)                                                                                                             

-----------                                                                                                             

          1        

 

SQL> select ename,

  2  coalesce(sal,comm) from emp;

 

ENAME      COALESCE(SAL,COMM)                                                                                           

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

SMITH                     800                                                                                           

ALLEN                    1600                                                                                           

WARD                     1250           



COALESCE(COMM,SAL)                                                                                                      

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

               800                                                                                                      

               300                                                                                                      

               500                                                                                                      

              2975      

7条件表达式

在 SQL 语句中使用IF-THEN-ELSE 逻辑;使用两种方法:

CASE 表达式

DECODE 函数

例子:

SQL> select ename,job,sal,case

  2  job when 'SALESMAN' then sal*1.2

  3  when 'MANAGER' then sal*1.8

  4  else sal end  "revised_sal" from emp;

 

ENAME      JOB         SAL revised_sal                                                                                  

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

SMITH      CLERK       800         800                                                                                  

ALLEN      SALESMAN   1600        1920                                                                                  

WARD       SALESMAN   1250        1500                                                                                  

JONES      MANAGER    2975        5355  

 

 

SQL> select ename,job,sal,decode(

  2  job,'SALESMAN',1.2*sal,

  3  'MANAGER',1.8*sal,sal)

  4  revised_sal from emp;

 

ENAME      JOB         SAL REVISED_SAL                                                                                  

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

SMITH      CLERK       800         800                                                                                  

ALLEN      SALESMAN   1600        1920                                                                                  

WARD       SALESMAN   1250        1500   


8.嵌套函数

单行函数可以嵌套。嵌套函数的执行顺序是由内到外。

例子:

SQL> select ename,job,nvl2(to_char(job),'manager','NO MANAGER') from emp;

 

ENAME      JOB       NVL2(TO_CH                                                                                         

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

SMITH      CLERK     manager                                                                                            

ALLEN      SALESMAN  manager                                                                                            

WARD       SALESMAN  manager 


 

 

 

文章评论

软件开发程序错误异常ExceptionCopyright © 2009-2015 MyException 版权所有