Học Oracle cơ bản (Bài 3)

Hµm trªn hµng ®¬n Néi dung M« t¶ c¸c hµm cã s½n trong SQL Sö dông hµm trªn DATE,NUMBER vµ CHARACTER trong c©u lÖnh SELECT M« t¶ c¸c hµm chuyÓn ®æi Hµm SQL Function Function performs action Hai kiÓu hµm trong SQL Functions Single-row functions Multiple-row functions Hµm trªn hµng ®¬n §iÒu biÕn d÷ liÖu ChÊp nhËn tham sè ®Çu vµo vµ tr¶ vÒ gi¸ trÞ duy nhÊt Thùc thi trªn mçi hµng tr¶ vÒ kÕt qu¶ cho mçi hµng Cã thÓ söa kiÓu Cã thÓ lång c¸c hµm function_name (column|expression, [arg1, arg2,...]) Hµm trªn hµng ®¬n Conversion Character Number Date General Single-row functions Hµm trªn kÝ tù Character functions LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR LPAD Case conversion functions Character manipulation functions Function Result Hµm chuyÓn ®æi kÝ tù ChuyÓn ®æi kÝ tù trong chuçi LOWER('SQL Course') UPPER('SQL Course') INITCAP('SQL Course') sql course SQL COURSE Sql Course Hµm chuyÓn ®æi kÝ tù HiÓn thÞ employee number, name, and department number cho employee Blake. SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE ename = 'blake'; no rows selected CONCAT('Good', 'String') SUBSTR('String',1,3) LENGTH('String') INSTR('String', 'r') LPAD(sal,10,'*') GoodString Str 6 3 ******5000 Function Result Hµm ®iÒu biÕn d÷ liÖu kÝ tù Hµm ®iÒu biÕn kÝ tù : Sö dông hµm ®iÒu biÕn chuçi kÝ tù SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename), 2 INSTR(ename, 'A') 3 FROM emp 4 WHERE SUBSTR(job,1,5) = 'SALES'; ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A') ---------- ------------------- ------------- ---------------- MARTIN MARTINSALESMAN 6 2 ALLEN ALLENSALESMAN 5 1 TURNER TURNERSALESMAN 6 0 WARD WARDSALESMAN 4 2 Hµm thao t¸c trªn kiÓu sè - 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(1600, 300) 100 Sö dông hµm ROUND SQL> SELECT ROUND(45.923,2), ROUND(45.923,0), 2 ROUND(45.923,-1) 3 FROM DUAL; ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1) --------------- -------------- ----------------- 45.92 46 50 SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), 2 TRUNC(45.923,-1) 3 FROM DUAL; TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1) --------------- ------------- --------------- 45.92 45 40 Sö dông hµm TRUNC Sö dông hµm MOD Cho kÕt qu¶ lµ sè d­ cña phÐp chia SQL> SELECT ename, sal, comm, MOD(sal, comm) 2 FROM emp 3 WHERE job = 'SALESMAN'; ENAME SAL COMM MOD(SAL,COMM) ---------- --------- --------- ------------- MARTIN 1250 1400 1250 ALLEN 1600 300 100 TURNER 1500 0 1500 WARD 1250 500 250 Lµm viÖc víi kiÓu DATE Oracle l­u kiÓu DATE d­íi d¹ng sè bªn trong cã ®Þnh d¹ng: century, year, month, day, hours, minutes, seconds. §inh d¹ng mÆc ®Þnh cña DATE lµ: DD-MON-YY. SYSDATE lµ hµm tr¶ vÒ ngµy hÖ thèng. DUAL lµ mét b¶ng dummy ®­îc sö dông ®Ó xem SYSDATE. PhÐp to¸n sè häc víi DATE Céng hay trõ mét sè víi DATE cho ra kÕt qu¶ lµ kiÓu DATE Trõ hai gi¸ trÞ kiÓu DATE cho kÕt qu¶ lµ sè ngµy gi÷a hai ngµy ®ã Céng giê vµo mét ngµy b»ng c¸ch chia sè giê cho 24. Sö dông phÐp to¸n sè häc víi kiÓu DATE SQL> SELECT ename, (SYSDATE-hiredate)/7 WEEKS 2 FROM emp 3 WHERE deptno = 10; ENAME WEEKS ---------- --------- KING 830.93709 CLARK 853.93709 MILLER 821.36566 Hµm trªn kiÓu DATE Number of monthsbetween two dates MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY ROUND TRUNC Add calendar months to date Next day of the date specified Last day of the month Round date Truncate date Function Description MONTHS_BETWEEN ('01-SEP-95','11-JAN-94') Sö dông hµm trªn DATE ADD_MONTHS ('11-JAN-94',6) NEXT_DAY ('01-SEP-95','FRIDAY') LAST_DAY('01-SEP-95') 19.6774194 '11-JUL-94' '08-SEP-95' '30-SEP-95' Sö dông hµm trªn DATE Hµm chuyÓn d÷ liÖu Implicit datatype conversion Explicit datatype conversion Datatype conversion ChuyÓn ®æi tù ®éng Trong qu¸ tr×nh g¸n, Oracle tù ®éng chuyÓn ®æi d÷ liÖu nh­ sau: VARCHAR2 or CHAR From To VARCHAR2 or CHAR NUMBER DATE NUMBER DATE VARCHAR2 VARCHAR2 ChuyÓn ®æi tù ®éng Trong qu¸ tr×nh g¸n, Orclae tù ®éng chuyÓn ®æi d÷ liÖu nh­ sau: VARCHAR2 or CHAR From To VARCHAR2 or CHAR NUMBER DATE ChuyÓn ®æi d÷ liÖu b¾t buéc NUMBER CHARACTER TO_CHAR Hµm TO_CHAR víi kiÓu DATE §Þnh d¹ng date: Ph¶i ®Æt trong dÊu nh¸y ®¬n vµ ph©n biÖt ch÷ hoa ch÷ th­êng Cã thÓ bao gåm bÊt cø thµnh tè ®Þnh d¹ng hîp lÖ Cã thªm fm ®Ó lo¹i bá kho¶ng trèng Ph©n c¸ch víi date bëi dÊu ph¶y TO_CHAR(date, 'fmt') YYYY C¸c thµnh tè trong ®Þnh d¹ng DATE YEAR MM MONTH DY DAY Full year in numbers Year spelled out Two-digit value for month Three-letter abbreviation of the day of the week Full name of the day Full name of the month C¸c thµnh tè trong ®Þnh d¹ng DATE Time lµ mét phÇn trong DATE Thªm mét chuçi kÝ tù b»ng c¸ch ®Æt vµo trong dÊu nguÆc kÐp Sö dông hµm víi DATE SQL> SELECT ename, 2 TO_CHAR(hiredate, 'fmDD Month YYYY') HIREDATE 3 FROM emp; ENAME HIREDATE ---------- ----------------- KING 17 November 1981 BLAKE 1 May 1981 CLARK 9 June 1981 JONES 2 April 1981 MARTIN 28 September 1981 ALLEN 20 February 1981 ... 14 rows selected. Hµm TO_CHAR víi kiÓu sè Sö dông ®Þnh d¹ng ®Ó hiÖn thÞ sè theo kiÓu chuçi kÝ tù TO_CHAR(number, 'fmt') 9 0 $ L . , Represents a number Forces a zero to be displayed Places a floating dollar sign Uses the floating local currency symbol Prints a decimal point Prints a thousand indicator Sö dông hµm TO_CHAR víi d÷ liÖu kiÓu sè SQL> SELECT TO_CHAR(sal,'$99,999') SALARY 2 FROM emp 3 WHERE ename = 'SCOTT'; SALARY -------- $3,000 Hµm TO_NUMBER vµ TO_DATE ChuyÓn ®æi mét chuçi kÝ tù ra kiÓu sè víi hµm TO_NUMBER TO_NUMBER(char[, 'fmt']) ChuyÓn ®æi mét chuçi kÝ tù thµnh kiÓu date dïng hµm TO_DATE TO_DATE(char[, 'fmt']) Hµm NVL ChuyÓn ®æi gi¸ trÞ NULL ra mét gi¸ trÞ thùc KiÓu d÷ liÖu cã thÓ ¸p dông lµ DATE,CHARACTER,NUMBER kiÓu d÷ liÖu : NVL(comm,0) NVL(hiredate,'01-JAN-97') NVL(job,'No Job Yet') SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0) 2 FROM emp; Sö dông hµm NVL ENAME SAL COMM (SAL*12)+NVL(COMM,0) ---------- --------- --------- -------------------- KING 5000 60000 BLAKE 2850 34200 CLARK 2450 29400 JONES 2975 35700 MARTIN 1250 1400 16400 ALLEN 1600 300 19500 ... 14 rows selected. Hµm DECODE Hµm nµy ho¹t ®éng nh­ c©u lÖnh CASE hay IF-THEN-ELSE DECODE(col/expression, search1, result1 [, search2, result2,...,] [, default]) Sö dông hµm DECODE SQL> SELECT job, sal, 2 DECODE(job, 'ANALYST', SAL*1.1, 3 'CLERK', SAL*1.15, 4 'MANAGER', SAL*1.20, 5 SAL) 6 REVISED_SALARY 7 FROM emp; JOB SAL REVISED_SALARY --------- --------- -------------- PRESIDENT 5000 5000 MANAGER 2850 3420 MANAGER 2450 2940 ... 14 rows selected. Sö dông hµm DECODE SQL> SELECT ename, sal, 2 DECODE(TRUNC(sal/1000, 0), 3 0, 0.00, 4 1, 0.09, 5 2, 0.20, 6 3, 0.30, 7 4, 0.40, 8 5, 0.42, 9 6, 0.44, 10 0.45) TAX_RATE 11 FROM emp 12 WHERE deptno = 30; HiÓn thÞ tû lÖ thuÕ cho c¸c nh©n viªn trong phßng 30 Sö dông mÖnh ®Ò case CASE Statement SELECT ENAME, EXTRACT(YEAR FROM HIREDATE) AS YEAR_OF_HIRE, (CASE EXTRACT(YEAR FROM HIREDATE) WHEN 2002 THEN 'NEW HIRE' WHEN 1997 THEN 'FIVE YEARS SERVICE' WHEN 1992 THEN 'TEN YEARS SERVICE' ELSE 'NO AWARD THIS YEAR' END ) AS AWARD FROM EMP; CASE Expression SELECT ENAME, SAL, (CASE WHEN JOB = ‘DBA’ THEN SAL * 1.5 WHEN HIREDATE SELECT ename, 2 NVL(TO_CHAR(mgr),'No Manager') 3 FROM emp 4 WHERE mgr IS NULL; ENAME NVL(TO_CHAR(MGR),'NOMANAGER') ---------- ----------------------------- KING No Manager Tæng KÕt Sö dông hµm ®Ó: TÝnh to¸n trªn d÷ liÖu Söa ch÷a d÷ liÖu §iÒu biÕn d÷ liÖu ®Çu ra Thay ®æi kiÓu ®Þnh d¹ng ChuyÓn ®æi d÷ liÖu