Oracle學習筆記 4

使用函數可以完成一系列的操作。資料庫之間的不同，在於對函數的支援上是不一樣。

本次筆記學習函數。函數分為單行函數和多行函數，此處重點學習單行函數。

單行函數分類：

字元函數

數值函數

日期函數                 

轉換函式        

通用函數

字元函數：又分為大小寫控制函數和字元控制函數

1）大小寫空值函數：

LOWER()

UPPER()

INITCAP()

SQL> select lower('SQL COURSE') , upper('sql course') , initcap('SQL course') from dual ; LOWER('SQLCOURSE') UPPER('SQLCOURSE') INITCAP('SQLCOURSE')------------------ ------------------ --------------------sql course         SQL COURSE         Sql Course

 

通過上面的查詢結果，看出：

Lower() 函數的作用是：將字元轉換為小寫。

Upper()函數的作用是：將字元轉換為大寫。

Initcap()函數的作用是：將字元的首字母轉換為大寫。

例：查詢名字是king的僱員資訊 ,由於不確定資料庫中名字的大小寫形式，我們引入lower函數和 upper()函數，來保證查詢的有效性。

SQL> select * from emp  where lower(ename) = 'king' ; EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7839 KING       PRESIDENT       1981/11/17    5000.00               10SQL> select * from emp  where ename = upper('king' ); EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7839 KING       PRESIDENT       1981/11/17    5000.00               10

例：將查詢結果字元的首字母轉換為大寫形式，使用initcap 函數。

SQL> select initcap(ename) from emp where ename = 'KING';

 INITCAP(ENAME)--------------King

字元控制函數：

concat()

substr()

length()

replace()

例：字串除了使用‘||’串連之外，還可以使用concat() 函數進行串連 。

SQL> select concat('Hello ' , 'world'  ) from  dual ; CONCAT('HELLO','WORLD')-----------------------Hello worldSQL> select substr('hello' , 1 , 2) ,length('world') , replace('sql' , 'q' , 'X') from dual ; SUBSTR('HELLO',1,2) LENGTH('WORLD') REPLACE('SQL','Q','X')------------------- --------------- ----------------------he                   5 sXl

上例中我們可以看到 substr()的作用是截取字串，在字串的後面跟兩個參數，第一個參數表示的是要截取的

起始位置，第二個參數表示的是截取的個數，length()函數返回的是字串的長度。replace() 字串後面跟兩

個參數，第一個參數表示要替換的字母，第二個參數表示將第一個字母換為第二個字母。

 

另外Oracle還支援倒著截取字串的方式：

SQL> select ename ,substr(ename ,-3 ) from emp  where deptno = 10 ; ENAME      SUBSTR(ENAME,-3)---------- ----------------CLARK      ARKKING       INGMILLER     LER

數值函數：

round()：四捨五入函數

trunc()：截斷小數位函數

mod() ：取餘函數

例：

SQL> select round(234.12 , 2) , round(234.324) , round(234.25 , -2) from dual; ROUND(234.12,2) ROUND(234.324) ROUND(234.25,-2)--------------- -------------- ----------------    234.12            234              200

後面的參數 2 表示保留小數點後兩位，如果不寫，預設為零，-2 表示對小數點前面的數進行四捨五入ROUND(234.25,-2) , 4 捨去，3 捨去得到200。

例：

SQL> select trunc(234.12 , 2) , trunc(234.324) , trunc(248.25 , -2) from dual; TRUNC(234.12,2) TRUNC(234.324) TRUNC(248.25,-2)--------------- -------------- ----------------         234.12            234              200

trunc(234.12 , 2)制定保留小數的位元； trunc(234.324)直接捨去小數 , trunc(248.25 , -2)，捨去小數點前面的兩位。

小結：trunc() 和 round()函數的區別是trunc(),直接進行相應的操作，不會執行四捨五入。

例：

SQL> select mod(10 , 3) from dual ;  MOD(10,3)----------         1

使用mod()函數進行求餘的操作。

日期函數：

關於日期的一些操作：

    日期-數字 = 日期

    日期+數字 = 日期

    日期-日期 = 天數

    日期之間不可以做加法的運算。

例：估計員工入職的月數，sysdate 表示的是當前的時間。

SQL> select empno , ename , round((sysdate - hiredate)/30) days from emp ; EMPNO ENAME            DAYS----- ---------- ---------- 7369 SMITH             406 7499 ALLEN             403 7521 WARD              403 7566 JONES             402 7654 MARTIN            396 7698 BLAKE             401 7782 CLARK             400 7788 SCOTT             328 7839 KING              394 7844 TURNER            397 7876 ADAMS             327 7900 JAMES             394 7902 FORD              394 7934 MILLER            392 14 rows selected

其他日期函數：

   MONTHS_BETWEEN()：求出給定日期範圍的月數。

   ADD_MONTHS()：在制定日期上加上指定的月數，求出之後的日期。

   NEXT_DAY()：下一個的今天是那一個日期。

   LAST_DAY()：求出給定日期的最後一天日期。

例：查詢連個日期之間相差的月數，sysdate 表示的當前的時間。

SQL> select empno , ename , months_between(sysdate , hiredate) days from emp ; EMPNO ENAME            DAYS----- ---------- ---------- 7369 SMITH      399.772141 7499 ALLEN      397.675367 7521 WARD       397.610850 7566 JONES      396.256012 7654 MARTIN     390.417302 7698 BLAKE      395.288270 7782 CLARK             394 7788 SCOTT      323.707625 7839 KING       388.772141 7844 TURNER     391.062463 7876 ADAMS      322.578592 7900 JAMES      388.223754 7902 FORD       388.223754 7934 MILLER     386.578592 14 rows selected

例：當前日期加上四個月之後的時間。SQL> select add_months(sysdate , 4) from dual ; ADD_MONTHS(SYSDATE,4)---------------------2014/8/9 22:29:57

例：下一個星期三的日期。SQL> select next_day(sysdate , '星期三') from dual ; NEXT_DAY(SYSDATE,'星期三')--------------------------2014/4/16 22:31:23例：本月的最後一天的日期。SQL> select last_day(sysdate) from  dual ; LAST_DAY(SYSDATE)-----------------2014/4/30 22:32:0

轉換函式：

   to_char（）：轉換為字串

   to_number():轉換成數字

   to_date():轉換成日期

三者之間的相互轉化關係：

               <-- to_date           --> to_number

       date =================char ===================number

               --> to_char           <-- to_char

SQL> select empno , ename ,to_char( hiredate , 'yyyy-mm-dd') from emp ; EMPNO ENAME      TO_CHAR(HIREDATE,'YYYY-MM-DD')----- ---------- ------------------------------ 7369 SMITH      1980-12-17 7499 ALLEN      1981-02-20 7521 WARD       1981-02-22 7566 JONES      1981-04-02 7654 MARTIN     1981-09-28 7698 BLAKE      1981-05-01 7782 CLARK      1981-06-09 7788 SCOTT      1987-04-19 7839 KING       1981-11-17 7844 TURNER     1981-09-08 7876 ADAMS      1987-05-23 7900 JAMES      1981-12-03 7902 FORD       1981-12-03 7934 MILLER     1982-01-23 14 rows selected

轉換日期的格式。年：y  ，年是四位元字表示 yyyy ，  月：m 兩位元字 表示 mm ， 日： d 兩位元字表示 dd 。 

 

數字轉換為字元。使用一些符號對數字進行分割，增加可讀性。可以使用‘$’和‘L’表示出地區。‘$’表示美元 ，L ： local表示以本地的語言進行金額的顯示。

SQL> select sal , to_char(sal , '$99,999') from emp ;       SAL TO_CHAR(SAL,'$99,999')--------- ----------------------   800.00     $800  1600.00   $1,600  1250.00   $1,250  2975.00   $2,975  1250.00   $1,250  2850.00   $2,850  2450.00   $2,450  3000.00   $3,000  5000.00   $5,000  1500.00   $1,500  1100.00   $1,100   950.00     $950  3000.00   $3,000  1300.00   $1,300 14 rows selected

 

例：to_number() 字串變為數位函數

 

SQL> select to_number('34')+46 from dual; TO_NUMBER('34')+46------------------                80

將字串變為數字，然後進行數位運算。

例：to_date() 將一個字串變為date型的資料。

SQL> select to_date('2014-4-9' ,'yyyy/mm/dd') dates from dual ; DATES-----------2014/4/9

通用函數：

   NVL() :將一個指定的null值變為指定的內容。空值是無法參與運算的所以把空值替換為 0 。

SQL> select empno ,(nvl(comm , 0 )+sal)*12 annual from emp ; EMPNO     ANNUAL----- ---------- 7369       9600 7499      22800 7521      21000 7566      35700 7654      31800 7698      34200 7782      29400 7788      36000 7839      60000 7844      18000 7876      13200 7900      11400 7902      36000 7934      15600 14 rows selected

decode() 函數，類似與java中的if… else if …  else

例：

SQL> select decode(1 , 1 ,'數值1' , 2 ,'數值2' , 3  , '數值3') from dual ; DECODE(1,1,'數值1',2,'數值2',3------------------------------數值1 SQL> select decode(2 , 1 ,'數值1' , 2 ,'數值2' , 3  , '數值3') from dual ; DECODE(2,1,'數值1',2,'數值2',3------------------------------數值2 SQL> select decode(3 , 1 ,'數值1' , 2 ,'數值2' , 3  , '數值3') from dual ; DECODE(3,1,'數值1',2,'數值2',3------------------------------數值3

   通過上面三個查詢的結果，我們不難發現，decode()函數的作用和用法。第一個參數為待匹配的值，之後參數兩兩一組，一組中第一個字元與帶匹配字元比較，相同則輸出第二個字元。

case()的用法與decode()基本相同：

文法格式為：

   selectename ,caseename

              when'KING'then'Hello'

              when'FORD'then'Word'

              when'MILLER'then'HELLOWORD'

              elseename                    

              end name

              fromemp

              wheredeptno =10

查詢結果為：

    ENAME      NAME

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

    CLARK      CLARK

    KING       Hello

    MILLER     HELLOWORD