程式師世界 >> 數據庫知識 >> Oracle數據庫 >> 關於Oracle數據庫 >> Oracle學習筆記4--單行函數

Oracle學習筆記4--單行函數

編輯：關於Oracle數據庫

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

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

　　單行函數分類：

　　字符函數

　　數值函數

　　日期函數

　　轉換函數

　　通用函數

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

　　1)大小寫空值函數：

　　LOWER()

　　UPPER()

　　INITCAP()

1 2 3 4 5 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()函數，來保證查詢的有效性。

1 2 3 4 5 6 7 8 9 10 11 SQL> select * from emp where lower(ename) = 'king' ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7839 KING PRESIDENT 1981/11/17 5000.00 10 SQL> 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 函數。

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

1 2 3 INITCAP(ENAME) -------------- King

　　字符控制函數：

　　concat()

　　substr()

　　length()

　　replace()

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

1 2 3 4 5 6 7 8 9 10 11 SQL> select concat('Hello ' , 'world' ) from dual ; CONCAT('HELLO','WORLD') ----------------------- Hello world SQL> 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還支持倒著截取字符串的方式：

1 2 3 4 5 6 7 SQL> select ename ,substr(ename ,-3 ) from emp where deptno = 10 ; ENAME SUBSTR(ENAME,-3) ---------- ---------------- CLARK ARK KING ING MILLER LER

　　數值函數：

　　round()：四捨五入函數

　　trunc()：截斷小數位函數

　　mod() ：取余函數

　　例：

1 2 3 4 5 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。

　　例：

1 2 3 4 5 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(),直接進行相應的操作，不會執行四捨五入。

　　例：

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

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

　　日期函數：

　　關於日期的一些操作：

　　日期-數字 = 日期

　　日期+數字 = 日期

　　日期-日期 = 天數

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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 表示的當前的時間。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 例：下一個星期三的日期。 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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表示以本地的語言進行金額的顯示。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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() 字符串變為數字的函數

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

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

　　例：to_date() 將一個字符串變為date型的數據。

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

　　通用函數：

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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

　　例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 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

　　查詢結果為：