Giáo trình SQL và PL SQL part 6

Chia sẻ: Mr Yukogaru | Ngày: | Loại File: PDF | Số trang:7

Thêm vào BST

Báo xấu

107
lượt xem 40
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Kỹ thuật thực hiện; có thể định nghĩa quan hệ thừa kế trong câu hỏi bằng mệnh đề STAR WITH và connect by trong câu lệnh Select , mỗi mẫu tin là một node trong cây phân cấp. Cột giả LEVEL cho biết cấp của mẫu tin hay cấp của node trong quan hệ thừa kế.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Giáo trình SQL và PL SQL part 6

C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL SELECT * FROM emp WHERE sal = ANY (SELECT sal FROM emp WHERE deptno=30); SELECT * FROM emp WHERE sal >= ALL ( select distinct sal From emp Where deptno =30) Order by sal desc; SELECT ENAME, SAL, JOB, DEPTNO FROM EMP WHERE SAL > SOME ( SELECT DISTINCT SAL FROM EMP WHERE DEPTNO =30) ORDER BY SAL DESC; SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP E WHERE EXISTS ( SELECT EMPNO FROM EMP WHERE EMP.MGR = E.EMPNO); /T×m nh÷ng ng−êi cã nh©n viªn/ 8.2 Bµi tËp 9 CÊu tróc h×nh c©y 9.1 CÊu tróc h×nh c©y trong 1 table Trong mét table cña CSDL ORACLE cã thÓ hiÖn cÊu tróc h×nh c©y. VÝ dô trong b¶ng EMP cÊu tróc thÓ hiÖn cÊp ®é qu¶n lý. KING EMPNO = 7839 Mgr =7839 CLARK JONES BLAKE MILER SCOTT FORD ALLEN WARD MARTIN TUNNER JAMES ADAMS SMITH §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 40
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL • Root node lµ node cÊp cao nhÊt • Child node lµ node con hay kh«ng ph¶i lµ root node • Parent node lµ node cã node con • Leaf node lµ node kh«ng cã node con Level Level lµ mét cét gi¶ chøa cÊp ®é trong cÊu tróc h×nh c©y. VÝ dô. SELECT LEVEL, DEPTNO, EMPNO, ENAME, JOB, SAL FROM EMP CONNECT BY PRIOR EMPNO = MGR START WITH MGR is NULL; LEVEL DEPTNO EMPNO ENAME JOB SAL ---------- ---------- ---------- ---------- --------- ---------- 1 10 7839 KING PRESIDENT 5000 2 30 7698 BLAKE MANAGER 2850 3 30 7654 MARTIN SALESMAN 1250 3 30 7499 ALLEN SALESMAN 1600 3 30 7844 TURNER SALESMAN 1500 3 30 7900 JAMES CLERK 950 3 30 7521 WARD SALESMAN 1250 2 10 7782 CLARK MANAGER 2450 3 10 7934 MILLER CLERK 1300 2 20 7566 JONES MANAGER 2975 3 20 7902 FORD ANALYST 3000 4 20 7369 SMITH CLERK 800 3 20 7788 SCOTT SALEMAN 3300 4 20 7876 ADAMS CLERK 1100 9.2 Kü thuËt thùc hiÖn Cã thÓ ®Þnh nghÜa quan hÖ thõa kÕ trong c©u hái b»ng mÖnh ®Ò STAR WITH vµ CONNECT BY trong c©u lªnh SELECT, mçi mÇu tin lµ mét node trong c©y ph©n cÊp. Cét gi¶ LEVEL cho biÕt cÊp cña mÉu tin hay cÊp cña node trong quan hÖ thõa kÕ. Có ph¸p: SELECT [DISTINCT/ALL] [expr [c_ias]] FROM [table/view/snapshot] [t_alias] [WHERE condition] [START WITH condition CONNECT BY PRIOR condition] [GROUP BY expr] [HAVING condition] [UNION/UNION ALL/INTERSET/MINUS select command] [ORDER BY expr/position [DESC/ASC]] Trong ®ã: • START WITH §Æc t¶ ®iÓm ®Çu cña h×nh c©y. Kh«ng thÓ ®Ó column gi¶ level ë mÖnh ®Ó nµy. • CONNECT BY ChØ column trong mèi liªn hÖ t×nh c©y. • PRIOR §Þnh h−íng cÊu tróc. NÕu prior xuÊt hiÖn tr−íc mgr, Mgr sÏ ®−îc t×m tr−íc sau ®ã ®Õn empno, ®©y lµ h×nh c©y h−íng lªn. NÕu prior xuÊt hiÖn tr−íc empno, empno sÏ ®−îc t×m tr−íc sau ®ã ®Õn empno, ®©y lµ h×nh c©y h−íng xuèng. VÝ dô SELECT LEVEL, DEPTNO, EMPNO, ENAME, JOB, SAL FROM EMP CONNECT BY PRIOR MGR = EMPNO START WITH empno = 7876; §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 41
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL LEVEL DEPTNO EMPNO ENAME JOB SAL ---------- ---------- ---------- ---------- --------- ---------- 1 20 7876 ADAMS CLERK 1100 2 20 7788 SCOTT SALEMAN 3300 3 20 7566 JONES MANAGER 2975 4 10 7839 KING PRESIDENT 5000 MÖnh ®Ò WHERE trong cÊu tróc h×nh c©y: MÖnh ®Ò WHERE vµ CONNECT BY cã thÓ ®−îc dïng ®ång thêi trong cÊu tróc h×nh c©y. NÕu mÖnh ®Ò WHERE lo¹i trõ mét sè row cña cÊu tróc h×nh c©y th× chØ nh÷ng row ®ã ®−îc lo¹i trõ. NÕu ®iÒu kiÖn ®Æt trong mÖnh ®Ò CONNECT BY th× toµn bé nh¸nh cña row ®ã bÞ lo¹i trõ. V× dô 1. SELECT LEVEL, DEPTNO, EMPNO, ENAME, JOB, SAL FROM EMP WHERE ENAME != ‘SCOTT’ CONNECT BY PRIOR EMPNO = MGR START WITH MGR IS NULL; LEVEL DEPTNO EMPNO ENAME JOB SAL ---------- ---------- ---------- ---------- --------- ---------- 1 10 7839 KING PRESIDENT 5000 2 30 7698 BLAKE MANAGER 2850 3 30 7654 MARTIN SALESMAN 1250 3 30 7499 ALLEN SALESMAN 1600 3 30 7844 TURNER SALESMAN 1500 3 30 7900 JAMES CLERK 950 3 30 7521 WARD SALESMAN 1250 2 10 7782 CLARK MANAGER 2450 3 10 7934 MILLER CLERK 1300 2 20 7566 JONES MANAGER 2975 3 20 7902 FORD ANALYST 3000 4 20 7369 SMITH CLERK 800 4 20 7876 ADAMS CLERK 1100 2. SELECT LEVEL, DEPTNO, EMPNO, ENAME, JOB, SAL FROM EMP CONNECT BY PRIOR EMPNO = MGR AND ENAME != ‘SCOTT’ START WITH MGR IS NULL; LEVEL DEPTNO EMPNO ENAME JOB SAL ---------- ---------- ---------- ---------- --------- ---------- 1 10 7839 KING PRESIDENT 5000 2 30 7698 BLAKE MANAGER 2850 3 30 7654 MARTIN SALESMAN 1250 3 30 7499 ALLEN SALESMAN 1600 3 30 7844 TURNER SALESMAN 1500 3 30 7900 JAMES CLERK 950 3 30 7521 WARD SALESMAN 1250 2 10 7782 CLARK MANAGER 2450 3 10 7934 MILLER CLERK 1300 2 20 7566 JONES MANAGER 2975 3 20 7902 FORD ANALYST 3000 4 20 7369 SMITH CLERK 800 9.3 Bµi tËp 1. T×m tÊt c¶ c¸c nh©n viªn, ngµy gia nhËp c«ng ty, tªn nh©n viªn, tªn ng−êi gi¸m ®èc vµ ngµy gia nhËp c«ng ty cña ng−êi gi¸m ®èc Êy. EMP_NAME EMP_SAL MGR_NAME MGR_SAL ---------- ---------- ---------- ---------- BLAKE 2850 BLAKE 2850 MARTIN 1250 BLAKE 2850 ALLEN 1600 BLAKE 2850 TURNER 1500 BLAKE 2850 JAMES 950 BLAKE 2850 §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 42
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL WARD 1250 BLAKE 2850 CLARK 2450 CLARK 2450 MILLER 1300 CLARK 2450 JONES 2975 JONES 2975 FORD 3000 JONES 2975 SMITH 800 JONES 2975 SCOTT 3300 JONES 2975 ADAMS 1100 JONES 2975 13 rows selected. 2. T×m nh÷ng nh©n viªn kiÕm ®−îc l−¬ng cao nhÊt trong mçi lo¹i nghÒ nghiÖp. JOB MAX(SAL) --------- --------- ANALYST 3000 CLERK 1300 MANAGER 2975 PRESIDENT 5000 SALESMAN 1600 3. T×m møc l−¬ng cao nhÊt trong mçi phßng ban, s¾p xÕp theo thø tù phßng ban. ENAME JOB DEPTNO SAL ---------- --------- ---------- ---------- KING PRESIDENT 10 5000 SCOTT SALEMAN 20 3300 BLAKE MANAGER 30 2850 4. T×m nh©n viªn gia nhËp vµo phßng ban sím nhÊt ENAME HIREDATE DEPTNO ---------- ---------- ---------- CLARK 09-06-1981 10 SMITH 17-12-1980 20 ALLEN 20-02-1981 30 5. HiÓn thÞ nh÷ng nh©n viªn cã møc l−¬ng lín h¬n l−¬ng TB cña phßng ban mµ hä lµm viÖc. EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ---------- 7839 KING 5000 10 7566 JONES 2975 20 7902 FORD 3000 20 7788 SCOTT 3300 20 7698 BLAKE 2850 30 7499 ALLEN 1600 30 6. HiÓn thÞ tªn nh©n viªn, m· nh©n viªn, m· gi¸m ®èc, tªn gi¸m ®èc, phßng ban lµm viÖc cña gi¸m ®èc, møc l−¬ng cña gi¸m ®èc. EMP_NUMBER EMP_NAME EMP_SAL MGR_NUMBER MGR_NAME MGR_DEPT MGR_GRADE ---------- --------------- ---------- ---------- ---------- ---------- 7698 BLAKE 2850 7698 BLAKE 30 4 7654 MARTIN 1250 7698 BLAKE 30 4 7499 ALLEN 1600 7698 BLAKE 30 4 7844 TURNER 1500 7698 BLAKE 30 4 7900 JAMES 950 7698 BLAKE 30 4 7521 WARD 1250 7698 BLAKE 30 4 7782 CLARK 2450 7782 CLARK 10 4 7934 MILLER 1300 7782 CLARK 10 4 7566 JONES 2975 7566 JONES 20 4 7902 FORD 3000 7566 JONES 20 4 7369 SMITH 800 7566 JONES 20 4 7788 SCOTT 3300 7566 JONES 20 4 7876 ADAMS 1100 7566 JONES 20 4 13 rows selected. §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 43
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 10 Tæng kÕt vÒ lÖnh select CÊu tróc lÖnh SELECT [DISTINCT/ALL] [expr [c_ias]] FROM [table/view/snapshot] [t_alias] [WHERE condition] [START WITH condition CONNECT BY condition] [GROUP BY expr] [HAVING condition] [UNION/UNION ALL/INTERSET/MINUS select command] [ORDER BY expr/position [DESC/ASC]] [FOR UPDATE OF [column]] [NOTWAIT] C¸c thµnh phÇn trong c©u lÖnh SELECT DISTINCT ChØ chän 1 cho c¸c row gièng nhau trong kÕt qu¶ ALL KÕt xuÊt c¶ c¸c row gièng nhau * Chän tÊt c¶ c¸c column trong table, view... table.*, view.*, snapshot.* Chän tÊt c¶ c¸c cét trong table, view hay snapshot ®−îc chÞ ®Þnh expr Chän c¸c biÓu thøc c_alias Tªn cét trong kÕt qu¶ kÕt xuÊt. table, view, snapshot lµ Tªn table, view hay snapshot subquery C©u hái select con t_alias Tªn cho c¸c table WHERE Chän c¸c row tháa ®iÒu kiÖn trong mÖnh ®Ò WHERE START WITH,CONNECT BY Chän c¸c dßng trong thø tù thõa kÕ GROUP BY Nhãm c¸c dßng cã expr gièng nhau HAVING Chän nh÷ng nhãm tháa ®iÒu kiÖn mÖnh ®Ò HAVING UNION, UNION ALL, INTERSET, MINUS Cho kÕt qu¶ kÕt hîp c¸c to¸n tö tËp hîp ORDER BY XÕp thø t− c¸c row theo expr hay position (trong danh s¸ch select) ASC, DESC TrËt tù xu«i (mÆc nhiªn) hay ng−îc FOR UPDATE Khãa nh÷ng row ®−îc chän, cho biÕt b¹n cã ý ®Þnh xãa hay cËp nhËt c¸c row nµy NOTWAIT Tr¶ quyÒn ®iÒu khiÓn nÕu khi muèn lock row ®· bÞ lock bëi user kh¸c. 11 T¹o table 11.1 LÖnh t¹o b¶ng §Ó t¹o mét b¶ng míi dïng lÖnh CREATE TABLE, Có ph¸p nh− sau: CREATE TABLE tablename (column [datatype][DEFAULT expr][column_constraint]..) [table_constraint]) [PCTFREE integer][PCTUSED integer] [INITRANS integer][MAXTRANS integer] [TABLESPACE tablespace] [STORAGE storage_clause] [AS subquery] Trong ®ã: tablename : Tªn table cÇn t¹o column : Tªn column trong table [datatype] : KiÓu d÷ liÖu cña column [DEFAULT expr] : Gi¸ trÞ mÆc ®Þnh cña column trong tr−êng hîp NULL lµ expr [column_constraint] : Rµng buéc cña b¶n th©n column [table_constraint] : rµng buéc cña toµn b¶ng [PCTFREE integer] : % trèng [PCTUSED integer] : % sö dông [INITRANS integer] : Sè b¶n ghi khëi t¹o [MAXTRANS integer] : Sè b¶n ghi lín nhÊt §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 44
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL [TABLESPACE tablespace] : ChØ ®Þnh TABLESAPCE cho b¶ng [STORAGE storage_clause] : Ghi mÖnh ®Ò l−u tr÷, ®¬n vÞ mÆc ®Þnh lµ KB trong ®ã c¸c c¸c chän lùa lµ: INITIAL - dung l−îng khëi t¹o; NEXT - dung l−îng t¨ng tiÕp theo; MINEXTENTS - % më réng nhá nhÊt; MAXEXTENTS- % më réng lín nhÊt; PCTINCREASE - Tèc ®é t¨ng hµng n¨m. [AS subquery] : t¹o b¶ng cã cÊu tróc gièng mÖnh ®Ò truy vÊn VÝ dô 1 CREATE TABLE EMP (EMPNO NUMBER NOT NULL CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2(10) CONSTRAINT NN_ENAME NOT NULL CONSTRAINT UPPER_ENAME CHECK (ENAME = UPPER(ENAME)), JOB VARCHAR2(9), MGR NUMBER CONSTRAINT FK_MGR REFERENCES SCOTT.EMP(EMPNO), HIREDATE DATE DEFAULT SYSDATE, SAL NUMBER(10,2) CONSTRAINT CK_SAL CHECK(SAL>500), COMM NUMBER(9,0) DEFAULT NULL, DEPTNO NUMBER(2) CONSTRAINT NN_DEPTNO NOT NULL CONSTRAINT FK_DEPTNO REFERENCES SCOTT.DEPT(DEPTNO)) PCTFREE 5 PCTUSED 75 VÝ du 2 CREATE TABLE SALGRADE1 (GRADE NUMBER CONSTRAINT PK_SALGRADE PRIMARY KEY, LOSAL NUMBER, HISAL NUMBER) TABLESPACE USER STORAGE (INITIAL 6144 NEXT 6144 MINEXTENTS 1 MAXEXTENTS 5 PCTINCREASE 5) VÝ dô 3 CREATE TABLE DEPT10 AS SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE DEPTNO =10; VÝ dô 4 CREATE TABLE EMP_SAL (NAME, SALARY,GRADE) AS SELECT ENAME, SAL, GRADE FROM EMP, SALGARDE WHERE EMP.SAL BETWEEN LOSAL AND HISAL ; §Ó t¹o mét table míi, chóng ta cÇn ph¶i chuÈn bÞ mét sè th«ng tin sau: • Table ph¶i ®−îc chuÈn hãa. • Nh÷ng column mµ cho phÐp null nªn ®Þnh nghÜa sau ®Ó tiÕt kiÖm n¬i l−u tr÷. • Gép c¸c table l¹i nÕu cã thÓ. • ChØ ®Þnh c¸c th«ng sè pcfree vµ pctused • Cã thÓ chØ ®Þnh 2 th«ng sè initstran, maxtrans • Cã thÓ chØ ®Þnh tablespace cho table • Cã thÓ −íc l−îng kÝch th−íc table, vµ c¸c th«ng sè cho storage. TÝnh to¸n kÝch th−íc table (tham kh¶o): §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 45
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 1. TÝnh to¸n kho¶ng ®Üa cÇn thiÕt cho data block header. TÝnh theo c«ng thøc sau: BLOCK HEADER = (FIXED HEADER + VARIABLE TRANSACTION HEADER) + ( TABLE DIRECTORY + ROW DIRECTORY) Trong ®ã: fixed header = 57 bytes variable transaction header = 23*gi¸ trÞ cña th«ng sè instrans table directory =4 row directory = 2* sè l−îng row trong block. 2. TÝnh to¸n kho¶ng ®Üa trèng ®Ó chøa d÷ liÖu cña data block. TÝnh theo c«ng thøc sau: Kho¶ng ®Üa trèng ®Ó chøa data = (block size -total block header) - (block size -(fixed header+ variable transaction header))*(pctree/100) Cã thÓ biÕt block size b»ng c¸ch dïng lÖnh show parameters db_block_size. 3. TÝnh to¸n kho¶ng ®Üa trèng kÕt hîp b»ng gi¸ trÞ cña mçi row. 4. TÝnh to¸n kÝch th−íc trung b×nh cña row: KÝch th−íc trung b×nh cña row = row header +A+B+C A = Tæng chiÒu dµi cña c¸c cét 250 byte C = Kho¶ng ®Üa trèng kÕt hîp 5. QuyÕt ®Þnh sè row trung b×nh cho mét block: avg rows /block = available space/average row size 6. TÝnh to¸n sè l−îng block Block = sè row / sè row trung b×nh cho mét block 11.2 C¸c quy t¾c ®Æt tªn object • Tªn dµi tõ 1 ®Õn 30 ký tù, ngo¹i trõ tªn CSDL kh«ng qu¸ 8 ký tù vµ tªn liªn kÕt cã thÓ dµi ®Õn 128 ký tù • Tªn kh«ng chøa dÊu nh¸y (") • Kh«ng ph©n biÖt ch÷ hoa ch÷ th−êng • Tªn ph¶i b¾t ®Çu b»ng ký tù ch÷ trong bé ký tù cña CSDL • Tªn chØ cã thÓ chøa ký tù sè trong tËp ký tù cña CSDL. Cã thÓ dïng c¸c ký tù _, $, #. ORACLE kh«ng khuyÕn khÝch dïng c¸c ký tù $ vµ #. • Tªn kh«ng ®−îc trïng víi c¸c tõ ®· dïng bëi ORACLE (xemphu lôc 1) • Tªn kh«ng ®−îc c¸ch kho¶ng trèng • Tªn cã thÓ ®Æt trong cÆp dÊu nh¸y kÐp, khi ®ã tªn cã thÓ bao gåm c¸c ký tù bÊt kú, cã thÓ bao gåm kho¶ng trèng, cã thÓ dïng c¸c tõ khãa cña ORACLE, ph©n biÖt ch÷ hoa ch÷ th−êng. • Tªn ph¶i duy nhÊt trong "kh«ng gian tªn" nhÊt ®Þnh. C¸c object thuéc cïng kh«ng gian tªn ph¶i cã tªn kh¸c nhau. C¸c bÝ danh cña cét, bÝ danh b¶ng, tªn ng−êi sö dông, mËt khÈu mÆc dï kh«ng ph¶i lµ c¸c object hoÆc c¸c thµnh phÇn con cña object nh−ng còng ph¶i ®−îc ®Æt tªn theo c¸c quy t¾c trªn, ngo¹i trõ BÝ danh cét, bÝ danh b¶ng chØ tån t¹i khi thùc hiÖn c¸c lÖnh SQL vµ kh«ng ®−îc l−u tr÷ trong CSDL, do vËy kh«ng ¸p dông quy t¾c 9 vÒ kh«ng gian tªn. MËt khÈu kh«ng thuéc vÒ kh«ng gian tªn nµo vµ do ®ã còng kh«ng ¸p dông quy t¾c 9. Nªn ®Æt tªn theo mét quy t¾c ®Æt tªn thèng nhÊt §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 46