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

Hµm Thao T¸c Trªn Mét Hµng Tæng Quan VÒ Bµi Häc Sau bµi häc nµy, b¹n cã thÓ: M« t¶ c¸c hµm cã s½n trong SQL Sö dông c¸c hµm thao t¸c trªn kÝ tù, date vµ sè trong c©u lÖnh SELECT. M« t¶ c¸c hµm chuyÓn (Convert) d÷ liÖu. Hµm Trong SQL Function Function performs action Hai KiÓu Hµm Trong SQL Functions Single-row functions Multiple-row functions Hµm Trªn Mét Hµng §¬n §iÒu biÕn mét ®¬n vÞ d÷ liÖu ChÊp nhËn c¸c tham sè vµ tr¶ vÒ kÕt qu¶. T¸c ®éng trªn tõng hµng d÷ liÖu Tr¶ vÒ kÕt qu¶ cho tõng hµng Cã thÓ söa ch÷a kiÓu d÷ liÖu C¸c hµm cã thÓ lång vµo nhau function_name (column|expression, [arg1, arg2,...]) C¸c Hµm Trªn Mét Hµng §¬n Conversion Character Number Date General Single-row functions Hµm 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 §Þnh D¹ng Ch÷ §æi ®Þnh d¹ng c¸c chuçi kÝ tù LOWER('SQL Course') UPPER('SQL Course') INITCAP('SQL Course') sql course SQL COURSE Sql Course Sö Dông Hµm §Þnh D¹ng KÝ Tù HiÓn thÞ empno, ename, deptno tõ b¶ng emp víi ename= 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 Chuçi KÝ Tù §iÒu biÕn chuçi 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 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 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 D÷ LiÖu Date Oracle l­u tr÷ d÷ liÖu kiÓu date d­íi d¹ng sè: century, year, month, day, hours, minutes, seconds. §Þnh d¹ng mÆc ®Þnh cña kiÓu date lµ: DD-MM-YY SYSDATE lµ hµm tr¶ thêi gian hÖ thèng DUAL lµ b¶ng ¶o cho phÐp query ra ngµy giê hÖ thèng PhÐp To¸n Sè Häc Víi Date Céng hay trõ mét sè vµo tr­êng date kÕt qu¶ tr¶ vÒ sÏ lµ kiÓu date Trõ hai tr­êng date 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 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 C¸c 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 Cho KiÓu 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 Cho KiÓu Date Hµm ChuyÓn §æi Implicit datatype conversion Explicit datatype conversion Datatype conversion Hµm ChuyÓn §æi Tù §éng Oracle cã thÓ tù ®éng chuyÓn ®æi c¸c kiÓu sau trong qu¸ tr×nh g¸n gi¸ trÞ: VARCHAR2 From To VARCHAR2 NUMBER DATE NUMBER DATE VARCHAR2 VARCHAR2 Hµm ChuyÓn §æi Tù §éng Oracle cã thÓ chuyÓn ®æi kiÓu d÷ liÖu sau tù ®éng khi ®¸nh gi¸ biÓu thøc: VARCHAR2 From To VARCHAR2 NUMBER DATE ChuyÓn §æi D÷ LiÖu B¾t Buéc NUMBER CHARACTER TO_CHAR Sö Dông Hµm TO_CHAR Víi D÷ LiÖu KiÓu Date Yªu cÇu ®Þnh d¹ng : CÇn ®­îc ®ãng trong dÊu nh¸y ®¬n vµ ph©n biÖt ch÷ hoa ch÷ th­êng Cã thÓ thªm c¸c yÕu tè ®Þnh d¹ng hîp lÖ Cã thªm yÕu tè fm ®Ó lo¹i bá kho¶ng trèng vµ s« 0 ë ®Çu §­îc ph©n biÖt víi d÷ liÖu date bëi dÊu ph¶y TO_CHAR(date, 'fmt') YYYY §Þnh D¹ng Ngµy Th¸ng 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 Thµnh PhÇn Trong §Þnh D¹ng KiÓu DATE §Þnh d¹ng thêi gian Thªm chuçi kÝ tù vµo trong ®Þnh d¹ng: ®Þnh d¹ng spelling Sö Dông Hµm TO_CHAR Víi KiÓu 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 hµm TO_CHAR víi ®Þnh d¹ng ®Ó biÓu thÞ mét sè d­íi d¹ng kÝ tù TO_CHAR(number, 'fmt') 9 0 $ L . , BiÓu diÔn mét sè HiÖn sè 0 §Æt dÊu dollar Sö dông biÓu t­îng tiÒn tÖ ®Þa ph­¬ng In dÊu phÇn thËp ph©n In chØ sè phÇn tõng ngh×n Hµm TO_CHAR Víi 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 §Þnh d¹ng mét chuçi sang d¹ng sè sö dông hµm TO_NUMBER TO_NUMBER(char[, 'fmt']) TO_DATE(char[, 'fmt']) - §Þnh d¹ng mét chuçi sang d¹ng date sö dông hµm TO_DATE Hµm NVL ChuyÓn mét gi¸ trÞ NULL thµnh mét gi¸ trÞ thùc KiÎu gi¸ trÞ cã thÓ lµ sè, kÝ tù hay date. KiÓu ph¶i ®Þnh d¹ng nh­ sau: 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 KiÓm tra ®iÒu kiÖn gièng nh­ c©u lÖnh CASE hay IF THEN 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 employe trong phßng 30 Lång C¸c Hµm C¸c hµm trªn hµng ®¬n cã thÓ lång nhau víi bÊt cø møc nµo. F3(F2(F1(col,arg1),arg2),arg3) Step 1 = Result 1 Step 2 = Result 2 Step 3 = Result 3 C¸c Hµm lång nhau SQL> 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ãm t¾t Giíi thiÖu c¸c hµm thùc hiÖn trªn hµng C¸c hµm cã s½n Cã thÓ x©y dùng c¸c hµm míi