Single-Row Functions

                                单行函数
   #Objectives
    After compleing this lesson,you should be able to
    do the following:
     -Describe variours types of function available
      in SQL
     -Use character,number,and date functions in
      SELECT statements
     -Describe the use of conversion functions
 
   #SQL Functions 
    -Two types of SQL Functions
     ·Single-row functions (one to one)
     ·Multiple-row functions (multiple to one)
    -函数与过程的区别
        函数是用来计算一个值或某些值,过程是主要用来完成某个动作.
    -Single-Row Functions
      ·Manipulate data items 操作数据项
      ·Accept arguments and return one value 接受参数返回一个值
      ·Act on each row returned 命令在每一行返回
      ·Return one result per row 每行返回一个结果
      ·May modify the data type 可以修改的数据类型
      ·Can be nested 可以被嵌套
      ·Accept arguments which can be a column or an
        expression 接受参数可以是一个栏位或一个表达式
    -Single-Row Functions Types
      ·Character 字符型
         -Case-Manipulation functions 字符大小写处理的函数
          ㈠LOWER 全部小写
          ㈡UPPER 全部大写
          ㈢INITCAP 每个单词开始的第一个位置大写
             function             result
         lower('SQL Course')    sql course
         upper('SQL Course')    SQL COURSE
         initcap('SQL Course')  Sql Course 
         -Character-Manipulation functions 字符处理的函数
          ㈠CONCAT 连接输入的两个参数
          ㈡SUBSTR 基于第一个参数的值,索引从第二个参数开始
                   经过第三个参数长度的字符(索引基数为1)
          ㈢LENGTH 返回参数的长度
          ㈣INSTR 从参数一中索引,参数二中所对应的值,返回其索引
                  没有返回0        
          ㈤LPAD|RPAD 参数一如果不够参数二所指定的大小那么
                      就左(右)填充参数三所指定的值添满为止             
          ㈥TRIM 从参数二中截取字符参数一返回,一次只能截取一个
                 字符且只能够从参数两端的第一个开始截取
          ㈦REPLACE 把参数一中的参数二替换成参数三如果不含参数三
                    则把参数一中的参数二全部去掉
                function                   result
         CONCAT('Hello','world')           Helloworld
         SUBSTR('Helloworld',6,5)          world
         LENGTH('Helloworld')              10
         INSTR('Helloworld','w')           6
         LPAD(SAL,10,'!')                  !!!!!29000
         RLPAD(SAL,10,'!')                 29000!!!!!
         TRIM('h'from'helloworld')         elloworld
         REPLACE('helloworld','l','@')     he@@owor@d
      ·Number 数字型
         -ROUND:Rounds value to specified decimal
               ROUND(45.926,2)------>45.93
         -TRUNC:Truncates value to specified decimal
               TRUNC(45.926,2)------>45.92
         -MOD:Returns remainder of division
               MOD(1400,300)  ------>200
         注意:如果ROUND、TRUNC的第二个参数为负是意思就是从
              小数点往左取如ROUND(45.926,-1)------>50
      ·Date 日期型
         -SYSDATE:  当前时间
              sysdate---->12-2月 -07  
         -MONTHS_BETWEEN: 两个时间点间的相差的月
              MONTHS_BETWEEN(add_months(sysdate,6),sysdate)----->6
         -ADD_MONTHS: 在指定的时间点加入几个月
              ADD_MONTHS(sysdate,6)-------->12-8月 -07
         -NEXT_DAY: 返回指定时间点的下一个时间点
              NEXT_DAY(sysdate,'星期一')------>19-2月 -07
         -LAST_DAY: 返回指定时间点的月的最后一天
              LAST_DAY(sysdate)------>28-2月 -07
         -ROUND: 四舍五入
              ROUND(last_day(sysdate))----->01-3月 -07
              ROUND(sysdate,'month')---->01-2月 -07
              ROUND(add_months(sysdate,5),'year')---->01-1月 -08
              当ROUND(sysdate,'year')中月大于6时返回01-1月 -08
         -TRUNC: 截取
              TRUNC(last_day(sysdate))----->28-2月 -07
              TRUNC(sysdate,'month')----->01-2月 -07
              TRUNC(add_months(sysdate,5),'year')---->01-1月 -07
      ·Conversion 转换型
         -Implicit date type conversion 隐式数据类型的转换
                     From          To
              VERCHAR2 or CHAR     NUMBER
              VERCHAR2 or CHAR     DATE
              NUMBER               VERCHAR2
              DATE                 VERCHAR2
         -Explicit date type conversion
                    TO_NUMBER           TO_DATE
            NUMBER←=======→CHARACTER←=======→DATE
                    TO_CHAR             TO_CHAR  
            TO_CHAR: ...转换成字符
               TO_CHAR(sysdate,'YYYY-month-dd')----->2007-2月 -12
               TO_CHAR(sysdate,'YYYY-month-day')----->2007-2月 -星期一
               TO_CHAR(293.936,'$999.99')------>$293.94
               TO_CHAR(293.936,'L999.99')------>RMB293.94
            TO_NUMBER: ...转换成数字
               TO_NUMBER('1200')----->1200
            TO_DATE: ...转换成日期
               TO_DATE('07-2月 -12')-----07-2月 -12
      ·General 通用型
          -NVL(expr1,expr2):如果expr1,为null则expr2替换expr1
              NVL(ename,'Not Name')
                 -->如果字符型栏位ename为null则显示字符Not Name
              NVL(sal,0)
                 -->如果数字型栏位sal为null则显示数字0
              NVL(hiredate,'03-12月-81')
                 -->如果日期型栏位hiredate为null则显示日期03-12月-81
              注意:在NVL(hiredate...中如果您输入的日期格式不于hiredate
                   栏位日期格式相匹配会报ORA-01861错
例:
SQL> select ename,nvl(ename,'Not Name') from emp
  2  where empno>7899;

ENAME      NVL(ENAME,
---------- ----------
           Not Name
FORD       FORD
MILLER     MILLER
SQL>       
          -NVL2(expr1,expr2,expr3):如果expr1,为null则expr3替换expr1
                                  不为null则expr2替换expr1
例:
SQL> select ename,nvl2(ename,'Have Name','Not Name') from emp
  2  where empno>7899;

ENAME      NVL2(ENAM
---------- ---------
           Not Name
FORD       Have Name
MILLER     Have Name

SQL>
          -NULLIF(expr1,expr2):如果expr1,expr2相等则显
                             示为null,否则显示expr1
例:
SQL> select ename,job,nullif(length(ename),length(job)) from emp
  2  where empno<7600;

ENAME      JOB       NULLIF(LENGTH(ENAME),LENGTH(JOB))
---------- --------- ---------------------------------
SMITH      CLERK
ALLEN      SALESMAN                                  5
WARD       SALESMAN                                  4
JONES      MANAGER                                   5

SQL>
          -COALESCE(expr1,expr2......,exprn):从1开始直到n显示最早
                                             一个不为null的值
           注意:在COALESCE()函数里表达式的类型必需相同可以用to_char
               等转换如果类型不同则报ORA-00932错
例:
SQL>select ename,sal,comm,coalesce(ename,to_char(comm),to_char(sal)) coalesce from emp
  2  where empno>7888;

ENAME             SAL       COMM COALESCE
---------- ---------- ---------- ----------------------------------------
                  950            950
FORD             3000            FORD
MILLER           1300            MILLER

SQL>

      -Conditional Expression 条件表达式
        ·Provide the use of IF-THEN-ELSE logic within a
          SQL statement
        ·Use two methods:
           -CACE exptession
例:
SQL> select empno,ename,job,sal,
  2  case job when 'CLERK' then 1.1*sal
  3           when 'SALESMAN' then 1.3*sal
  4           when 'MANAGER' then 1.4*sal
  5  else sal end  "++SAL"
  6  from emp;

     EMPNO ENAME      JOB              SAL      ++SAL
---------- ---------- --------- ---------- ----------
      7369 SMITH      CLERK            800        880
      7499 ALLEN      SALESMAN        1600       2080
      7521 WARD       SALESMAN        1250       1625
      7566 JONES      MANAGER         2975       4165
      7654 MARTIN     SALESMAN        1250       1625
      7698 BLAKE      MANAGER         2850       3990
      7782 CLARK      MANAGER         2450       3430
      7788 SCOTT      ANALYST         3000       3000
      7839 KING       PRESIDENT       5000       5000
      7844 TURNER     SALESMAN        1500       1950
      7876 ADAMS      CLERK           1100       1210

     EMPNO ENAME      JOB              SAL      ++SAL
---------- ---------- --------- ---------- ----------
      7900            CLERK            950       1045
      7902 FORD       ANALYST         3000       3000
      7934 MILLER     CLERK           1300       1430

已选择14行。
           -DECODE function
例:
SQL> run
  1  select empno,ename,sal,job,
  2  decode(job,'CLERK',1.1*sal,
  3             'SALESMAN' ,1.15*sal,
  4             'MANAGER' ,1.3*sal,
  5         sal)
  6  "SAL++"
  7* from emp

     EMPNO ENAME             SAL JOB            SAL++
---------- ---------- ---------- --------- ----------
      7369 SMITH             800 CLERK            880
      7499 ALLEN            1600 SALESMAN        1840
      7521 WARD             1250 SALESMAN      1437.5
      7566 JONES            2975 MANAGER       3867.5
      7654 MARTIN           1250 SALESMAN      1437.5
      7698 BLAKE            2850 MANAGER         3705
      7782 CLARK            2450 MANAGER         3185
      7788 SCOTT            3000 ANALYST         3000
      7839 KING             5000 PRESIDENT       5000
      7844 TURNER           1500 SALESMAN        1725
      7876 ADAMS            1100 CLERK           1210

     EMPNO ENAME             SAL JOB            SAL++
---------- ---------- ---------- --------- ----------
      7900                   950 CLERK           1045
      7902 FORD             3000 ANALYST         3000
      7934 MILLER           1300 CLERK           1430

已选择14行。

 

                                                 summary
In this lesson ,you should have learned how to:
  ·Perform calculations on data using funtions
  ·Modify individual data items using funtions
  ·Manipulate output for groups of rows using
    functions
  ·Alter date formats for display using functions
  ·Convert column data types using functions
  ·Use NVL functions
  ·Use IF-THEN-ELSE logic