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

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

Thêm vào BST

Báo xấu

56
lượt xem 45
download

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

Cursor định nghĩa : là kiểu biến có cấu trúc, cho phép ta xử lý dữ liệu gồm nhiều dòng, số dòng ohuj thuộc vào câu lệnh query sau nó . Trong quá trình xử lý ZCursor như là một con trỏ vị trí của now đang xử lý..............

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 10

C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 19 cursor 19.1 §Þnh nghÜa Cursor: lµ kiÓu biÕn cã cÊu tróc, cho phÐp ta xö lý d÷ liÖu gåm nhiÒu dßng. Sè dßng phô thuéc vµo c©u lÖnh query sau nã. Trong qu¸ tr×nh xö lý cursor nh− lµ mét con trá vÞ trÝ cña row ®ang xö lý C¸c b−íc sö dông biÕn cursor: Khai b¸o -> më -> lÊy d÷ liÖu ®Ó xö lý -> ®ãng Khai b¸o cursor is : Më vïng d÷ liÖu cÊt tr÷ th«ng tin xö lý. vd: cursor x is select deptno from dept where deptno=10; vd: cursor x(b number) is select * from dept where deptno>b; Më cursor open vd: open x; vd: open x(10); LÊy d÷ liÖu Fetch into Vd: fetch x into b; §ãng cursor Close vd: Close x; C¸c thuéc tÝnh %isopen : tr¶ l¹i gi¸ trÞ True nÕu cursor ®ang më %notfound : tr¶ l¹i gi¸ trÞ True nÕu lÖnh fetch hiÖn thêi tr¶ l¹i kh«ng cã row %found : tr¶ l¹i gi¸ tri true cho ®Õn khi fetch kh«ng cßn row nµo %rowcount : tr¶ l¹i sè row ®· ®−îc thùc hiÖn b»ng lÖnh fetch VÝ dô1: declare cursor v_a is select * from emp; m v_a%rowtype; begin open v_a; loop fetch v_a into m; insert into t_thu(empno, ename,job) values (m.empno,m.ename, m.job); exit when v_a%notfound; end loop; close v_a; end; vÝ dô 2: DECLARE CURSOR c1 IS SELECT dname, loc FROM dept FOR UPDATE OF loc; dept_rec c1%ROWTYPE; sales_count NUMBER:=0; non_sales NUMBER:=0; §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 68
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL BEGIN OPEN c1; LOOP FETCH c1 INTO dept_rec; EXIT WHEN c1%NOTFOUND; IF dept_rec.dname = 'SALES' AND dept_rec.loc!='DALLAS' THEN UPDATE dept SET loc='DALLAS' WHERE CURRENT OF c1; sales_count:=sales_count+1; ELSIF dept rec.dname!='SALES' AND dept_rec.loc!='NEWYORK' THEN UPDATE dept SET loc ='NEWYORK' WHERE CURRENT OF c1; non_sales:=non_sales+1; END IF; END LOOP; CLOSE c1; INSERT INTO counts (sales_set, non_sales_set) VALUES (sales_count, non_sales); COMMIT; END; 19.2 KiÓu d÷ liÖu Table vµ Record KiÓu d÷ liÖu Table Có ph¸p: TYPE type_name IS TABLE OF datatype [NOT NULL] INDEX BY BINARY_INTEGER; var type_name; VÝ dô TYPE NAME IS TABLE OF EMP.ENAME%TYPE; First_name NAME; Last_name NAME; KiÓu d÷ liÖu Record Có ph¸p TYPE type_name IS RECORD OF (Col1 datatype [NOT NULL{:=|DEFAULT} expr], (Col2 datatype [NOT NULL{:=|DEFAULT} expr]...); var type_name; VÝ dô §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 69
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL TYPE emp_rec IS RECORD OF empno number(4) not null, ename char(10), job char(9), mgr number(4), hiredate date default sysdate, sal number(7,2), comm number(7,2), deptno number(2) not null ); Emp_record emp_rec; 19.3 Sao kiÓu d÷ liÖu B¶n ghi trong PL/SQL. lµ mét biÕn cã thÓ gi÷ nhiÒu gi¸ trÞ vµ lµ mét tËp hîp c¸c biÕn t−¬ng øng víi c¸c tr−êng trong table. §Ó ®Þnh nghÜa kiÓu d÷ liÖu b¶n ghi. Var varref%ROWTYPE Trong ®ã Var : biÕn b¶n ghi Varref : Tªn b¶ng VÝ dô: X emp%ROWTYPE; §Ó truy nhËp ®Õn c¸c tr−êng trong d÷ liÖu b¶n ghi dïng gièng nh− trong 1 row. VÝ dô x.empno, x.sal... §Ó sao kiÓu d÷ liÖu cña mét biÕn nµo ®ã. X salgade%TYPE X sÏ cã kiÓu d÷ liÖu gièng biÕn salgrade. 19.4 C©u lÖnh SELECT... INTO... trong PL/SQL Có ph¸p SELECT col1, col2... INTO var1, var2... [cursor_var] FROM table1, table2... [WHERE condition1, condition2... ] [GROUP BY col1, col2 ...] [HAVING condition1, condition2...] [FOR UPDATE]; trong ®ã: INTO var1, var2... [cursor_var] ®Ó ®−a gi¸ trÞ trong table vµo trong c¸c biÕn ( cã thÓ lµ biÕn cursor ). VÝ dô: SELECT deptno, loc INTO v_deptno, v_loc FROM dept WHERE dname = ‘SALES’; 19.5 Bµi tËp 1. ViÕt ®o¹n ch−¬ng tr×nh t×m kiÕm c¸c hµng trong b¶ng EMP víi biÕn ®−îc ®−a tõ ngoµi vµo lµ &1 d¹ng JOb_type(emp.job%type)vµ ®−a ra th«ng b¸o thÝch hîp vµo b¶ng MESSAGES. §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 70
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 2. ViÕt ®o¹n ch−¬ng tr×nh ghi d÷ liÖu vµo b¶ng MESSAGES víi cét NUMCOL1 mang gi¸ trÞ lµ 1 nÕu lµ row 1 ®−îc Insert, 2 nÕu row 2 ®−îc Insert.... Kh«ng ®−îc Insert nh÷ng row cã gi¸ trÞ lµ 6 hoÆc 8, tho¸t khái vßng lÆp insert sau gi¸ trÞ 10. Commit sau vßng lÆp. 3. LiÖt kª c¸c cét ENAME, HIREDATE, SAL Víi ®iÒu kiÖn EMPNO b»ng gi¸ trÞ biÕn &EMPLOYEE_NO ®−îc ®−a vµo, sau ®ã kiÓm tra - Cã ph¶i møc l−¬ng lín h¬n 1200 - Tªn nh©n viªn cã ph¶i cã chøa ch÷ T - ngµy gia nhËp c¬ quan cã ph¶i lµ th¸ng 10 (DEC) vµ ®−a gi¸ trÞ kiÓm tra nµy vµo b¶ng message cét charcol1 (thö víi c¸c gi¸ trÞ 7654, 7369, 7900, 7876) 4. §−a vµo vßng lÆp v tõ 1 ®Õn 10 lÖnh UPDATE messages SET numcol2=100 WHERE numcol1 = v; nÕu bÊt kú mét lÇn update nµo ®ã cã sè l−îng row >1 th× exit khái vßng lÆp. 20 procedure vµ funtion 20.1 Procedure Lµ mét nhãm c¸c lÖnh thùc hiÖn chøc n¨ng nµo ®ã nh»m t¨ng kh¶ n¨ng xö lý, kh¶ n¨ng sö dông c¸c thñ tôc chung, t¨ng tÝnh b¶o mËt vµ an toµn d÷ liÖu, tiÖn Ých trong ph¸t triÓn. Có ph¸p: Procedure : Lµ tªn cña procedure ®−îc t¹o. Argument : Gåm tªn cña danh s¸ch c¸c biÕn vµ kiÓu cña nã. IN : ChØ ®Þnh r»ng b¹n ph¶i ®−a trÞ khi gäi procedure. OUT : ChØ ra r»ng Procedure sÏ tr¶ l¹i trÞ cho biÕn tíi m«i tr−êng gäi nã. IN OUT : ChØ ra r»ng b¹n ph¶i g¸n trÞ cho argument khi gäi procedure vµ procedure sÏ tr¶ l¹i trÞ argument tíi m«i tr−êng gäi. NÕu kh«ng ghi IN, OUT hoÆc IN OUT th× ngÇm ®Þnh sÏ lµ IN §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 71
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL Datatype : Lµ kiÓu cña argument, ë ®©y chØ ®−îc khai b¸o kiÓu mµ kg«ng ®−îc khai b¸o c¸c chiÒu dµi argument. VÝ dô kh«ng ®−îc khai b¸o argument lµ VARCHAR2(10) mµ ph¶i khai b¸o lµ VARCHAR2. Pl/sql_subprogram_body: Lµ phÇn th©n cña procedure ®−îc viÕt b»ng PL/SQL. VÝ dô: CREATE OR REPLACE PROCEDURE INS_DEPT(X NUMBER, Y VARCHAR2) IS BEGIN INSERT INTO DEPT(DEPTNO,DNAME) VALUES (X,Y); END; Muèn thùc hiÖn procedure t¹i SQL plus thù hiÖn dïng lÖnh execute execute ins_dept(55,’ New Name’); 20.2 Function Có ph¸p: C¸c tham gièng nh− procedure nh−ng kh¸c lµ sau khi gäi hµm tr¶ l¹i trÞ VÝ dô: create or replace function get_dname( y number) return varchar2 is m char(14); begin select dname into m from dept where deptno=y; if SQL%notfound then m:='Khong thay'; end if; return(rtrim(m)); end; §Ó gäi hµm get_dname ta gäi trùc tiÕp hoÆc th«ng qua c¸c phÐp g¸n. VÝ dô: SQL> select * from dept where dname=get_dname(10); DEPTNO DNAME LOC --------- -------------- ------------- 10 ACCOUNTING NEW YORK SQL> select get_dname(20) from dual; GET_DNAME(20) -------------------------------------------------------- §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 72
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL RESEARCH 20.3 Bµi tËp 1. ViÕt hµm lÊy tªn gi¸m ®èc theo biÕn empno ®−îc nhËp vµo, 2. ViÕt thñ tôc nhËp th«ng tin vµo b¶ng message c¸c tr−êng numcol1: M· phßng charcol1: tªn phßng ban numcol2: tæng l−¬ng cña phßng 3. ViÕt thñ tôc dïng cursor; lÊy sè liÖu vÒ n ng−êi (n lµ mét biÕn ®−îc ®−a vµo tõ man h×nh) cã møc l−¬ng cao nhÊt trong b¶ng emp ®−a vµo b¶ng top_sal víi c¸c gi¸ trÞ t−¬ng øng trong num=empno, name = ename, salary = sal). B¶ng top_sal cã cÊu tróc nh− sau: NUM NUMBER(4) NAME VARCHAR2(25) SALARY NUMBER(11,2) 21 pakage 21.1 Package Lµ tËp hîp cña c¸c ®èi t−îng gåm c¸c procedure, function, variable, constant, cursor vµ c¸c exception. ViÖc t¹o c¸c package cho phÐp t¨ng kh¶ n¨ng mÒm dÎo, t¨ng tÝnh b¶o mËt, t¹o sù thuËn lîi trong viÖc qu¶n lý hÖ thèng ®ång thêi t¨ng hiÖu suÊt xö lý cña hÖ thèng. §Ó t¹o package thùc hiÖn nh− sau: §Ó t¹o package body thùc hiÖn nh− sau: Víi c¸c releases tr−íc ®©y cña PL/SQL viÖc gäi c¸c functions chØ cã thÓ ®−îc thùc hiÖn b»ng c¸c lÖnh cña procedure, nh−ng giê th× c¸c lêi gäi nµy cã thÓ xuÊt hiÖn trong c©u lÖnh SQL gièng nh− lÖnh procedure. §iÒu nµy cã nghÜa lµ ta cã thÓ sö dông c¸c functions gièng nh− c¸c built-in SQL functions. B»ng c¸c më réng SQL ta cã thÓ tËp hîp ph©n tÝch ngay bªn trong Oracle Server mµ ta kh«ng cÇn lÊy d÷ liÖu vµo trong øng dông ®iÒu nµy lµm t¨ng tÝnh ®éc lËp cña c¬ së d÷ liÖu. Tuy nhiªn ®Ó cã thÓ gäi ®−îc tõ SQL th× c¸c function ph¶i ®¶m b¶o ch¾c ch¾n viÖc kiÓm so¸t kÕt qu¶. Víi c¸c standalone functions th× Oracle cã thÓ thùc hiÖn ®iÒu nµy b»ng viÖc kiÓm tra function body. Tuy nhiªn víi body cña package lµ Èn cho nªn c¸c packaged functions ta ph¶i sö dông pragma RESTRICT_REFERENCES ®Ó ®¶m b¶o luËt nµy. §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 73
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL Pragma Restrict_references(, , ...) Trong ®ã: WNDS: Write no database state RNDS: Read no database state WNPS: Write no package state RNPS: Read no package state VÝ dô: create or replace package vidu is function get_dname( y number) return varchar2; Pragma Resctrict_references(get_dname, WNDS, WNPS); Procedure ins_dept (x number, y varchar2); end vidu; create or replace package body vidu is function get_dname( y number) return varchar2 is m char(14); begin select dname into m from dept where deptno=y; if SQL%notfound then m:='Khong thay'; end if; return(rtrim(m)); end; procedure ins_dept(x number, y varchar2) is begin insert into dept(deptno,dname) values (x,y); end; end vidu; §Ó gäi ta thùc hiÖn nh− sau: SQL> execute vidu.ins_dept(70,'Vi du'); 22 database trigger 22.1 Database Trigger Mét Database Trigger ®−îc t¹o vµ l−u tr÷ trong PL/SQL block t−¬ng øng víi table. Nã ®−îc tù ®éng gäi ®Õn khi cã sù truy nhËp ®Õn table t−¬ng øng víi c¸c hµnh ®éng ®Þnh nghÜa. §Ó t¹o mét triger ta gâ theo cã ph¸p sau: §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 74
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL Create or replace : lÖnh t¹o hoÆc t¹o l¹i trigger nÕu nã ®· tån t¹i. Before : ChØ ra r»ng trigger sÏ ®−îc thùc hiÖn tr−íc khi thùc hiÖn lÖnh. Affter : ChØ ra r»ng trigger sÏ ®−îc thùc hiÖn sau lÖnh gäi tíi nã. Delete, Insert, Update of on : trigger sÏ ®−îc gäi khi cã c¸c hµnh ®éng t−¬ng øng trªn tõng column cña b¶ng. Referencing : ChØ tªn quan hÖ tíi c¸c trÞ cò (OLD) vµ míi (NEW) cña row . For each row : Trigger thùc hiÖn t−¬ng øng víi mçi row cã ®iÒu kiÖn øng víi mÖnh ®Ò trong WHEN. Pl/sql_block : Lµ khèi lÖnh PL/SQL thùc hiÖn c¸c xö lý theo mong muèn. VÝ dô: create or replace trigger t_dname before insert or update of dname on dept for each row when (new.dname is null) begin if (:new.dname is null) then :new.dname:='No Name'; end if; end ; 22.2 Bµi tËp 1. ViÕt trigger ®Ó khi nhËp sè liÖu vµo b¶ng emp th× nã còng nhËp sè liÖu vµo b¶ng emp1 víi ®iÒu kiÖn cÊu tróc b¶ng emp1 cã empno, ename, job, dname 2. Thªm 1 cét vµo b¶ng DEP tªn lµ SUMSAL. ViÕt trigger ®Ó cét SUMSAL lu«n chøa tæng l−¬ng cña phßng ban ®ã ( d÷ liÖu lÊy t−¬ng øng tõ b¶ng emp) 3. LËp 1 b¶ng tªn lµ BACKUP cã cÊu tróc gièng b¶ng EMP sao cho mçi b¶n ghi trong emp bÞ xo¸ sÏ l−u sang backup 4. øng víi c¸c theo t¸c insert, update, delete trªn b¶ng emp, l−u l¹i c¸c theo t¸c ®ã vµo b¶ng message, víi d÷ liÖu t−¬ng øng charcol1 = tªn thao t¸c, datecol2 = Ngµy giê thùc hiÖn. §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 75
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 23 error handing Error handing lµ lçi xuÊt hiÖn trong khèi lÖnh PL/SQL, tÊt c¶ c¸c lçi nµy sÏ ch¹y vÒ phÇn EXCEPTION trong khèi lÖnh ®Ó xö lý. Khèi lÖnh PL/SQL gåm c¸c thµnh phÇn DECLARE /Kh«ng b¾t buéc/ §Þnh nghÜa c¸c biÕn BEGIN §o¹n lÖnh; EXCEPTION /Kh«ng b¾t buéc/ Hµnh ®ång nÕu lçi xuÊt hiÖn; END; Có ph¸p thùc hiÖn c¸c EXCEPTION; EXCEPTION WHEN exception1 [OR exception1. . .] THEN Xö lý; . . . [WHEN exception3 [OR exception4. . .] THEN Xö lý; . . .] [WHEN OTHERS THEN Xö lý; . . .] Trong ®ã: exception : tªn lçi n WHEN OTHERS : dïng ®Ó xö lý c¸c tr−êng hîp lçi kh¸c §iÒu kiÖn kÝch ho¹t exception Cã 2 nhãm exception: • C¸c exception cña b¶n th©n Oracle nh−: NO_DATA_FOUND, FOUND, TOO_MANY_ROW ... • C¸c exception do ng−êi sö dông khai b¸o C¸c exception hÖ thèng tù ®éng bÞ kÝch ho¹t trong c¸c tr−êng hîp nhÊt ®Þnh. C¸c exception ng−êi sö dông ®Þnh nghÜa ph¶i tù kÝch ho¹t, vÝ dô RAISE exception_identifier; Mét sè exception hay dïng cña b¶n th©n Oracle: Tªn M· lçi M« t¶ NO_DATA_FOUND ORA_01403 C©u lÖnh SELECT INTO kh«ng tr¶ vÒ row nµo TOO_MANY_ROW ORA_01422 C©u lÖnh SELECT INTO kh«ng tr¶ vÒ lín h¬n 1 row INVALID_CURSOR ORA_01001 Lçi xö lý CURSOR ZERO_DIVIDE ORA_01476 Lçi chia cho 0 §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 76
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL DUP_VAL_ON_INDEX ORA_00001 Lçi gi¸ trÞ bÞ trïng l¾p trong mét UNI QUE INDEX VÝ dô Xo¸ nh÷ng nh©n viªn trong b¶ng emp nÕu t¹i phßng nh©n viªn ®ã lµm viÖc chØ cã mét nh©n viªn; trong Procedure buider PROCEDURE DELEMP (V_EMP IN EMP.EMPNO%TYPE) IS V_ID EMP.EMPNO%TYPE; BEGIN SELECT EMPNO INTO V_ID FROM EMP WHERE EMPNO = V_EMP; DELETE FROM EMP WHERE EMPNO = V_EMP; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN ROLLBACK; TEXT_IO.PUT_LINE(TO_CHAR(V_EMP)||'KHONG CO'); WHEN TOO_MANY_ROWS THEN ROLLBACK; TEXT_IO.PUT_LINE('CO LOI DU LIEU TRONG BANG EMP'); WHEN OTHERS THEN ROLLBACK; TEXT_IO.PUT_LINE('CO LOI KHAC TRONG BANG EMP'); END; Gäi ch¹y delemp(7364); C¸c exception do ng−êi sö dông ®Þnh nghÜa Khai b¸o exception identifier EXCEPTION; VÝ dô: DECLARE credit_exceeded EXCEPTION; BEGIN IF stock_ordered > credit_limit THEN RAISE credit_exceeded; END IF .... EXCEPTION WHEN credit_exceeded THEN .... END; §Æt tªn cho c¸c exception hÖ thèng Mçi exception hÖ thèng ®−îc g¸n mét sè x¸c ®Þnh, cã thÓ ®Æt tªn cho c¸c exception ®Ó dÔ sö dông h¬n. PRAGMA EXCEPTION_INIT (exception_identifier, number) §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 77
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL VÝ dô DECLARE fetch_failed EXCEPTION; PRAGMA EXCEPTION_INIT (fetch_failed, -1002); BEGIN .... EXCEPTION WHEN fetch_failed THEN .... END; 23.1 Bµi tËp 1. Dïng EXCEPTION b¾t lçi chÆt h¬n cho c¸c bµi tËp tõ phÇn 19-22. §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 78