Nhập môn DB2 Express – C part 8

Chia sẻ: NsduwDHUW Hdue | Ngày: | Loại File: PDF | Số trang:21

Thêm vào BST

Báo xấu

69
lượt xem 12
download

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

Nh p môn H qu n tr c s d li u DB2 147 M t ng d ng c n xác nh “s b ” s hàng trong m t b ng. Hi u su t r t là quan tr&ng. Con tr" c yêu c u m c cô l#p n nh v i ngo i l c a m t câu l nh SQL: SELECT COUNT(*) FROM tab1 WITH UR V i SQL nhúng, các m c thi t l#p các m c c t th i gian ch y. c t t i th i i m óng...

Chủ đề:

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

Lưu

Nội dung Text: Nhập môn DB2 Express – C part 8

Nh p môn H qu n tr c s d li u DB2 147 M t ng d ng c n xác nh “s b ” s hàng trong m t b ng. Hi u su t r t là quan tr&ng. Con tr" c yêu c u m c cô l#p n nh v i ngo i l c a m t câu l nh SQL: SELECT COUNT(*) FROM tab1 WITH UR V i SQL nhúng, các m c thi t l#p c t t i th i i m óng gói, còn v i SQL ng các m c c t th i gian ch y. Vi c ch&n s d ng m c cô l#p nào ph thu c vào ng d ng c a các b n. N u ng d ng c a các b n không yêu c u có nh ng giá tr nh ví d , ch&n cô l#p UR. N u ng d ng c a các b n yêu c u i u khi n r t ch t trên d li u làm vi c v i nó, ch&n cô l#p RR. 13.5 Khóa leo thang M i khóa do DB2 t o ra s, s d ng l ng b nh nào ó. Khi b t i u cho r ng nó t t h n là m t khóa trên toàn b b ng, thay vì khóa nhi u hàng, s leo thang khóa xu t hi n. Hình 13.9 minh h&a cho i u này: Hình 13.9 – Khóa leo thang Có hai tham s c u hình c s d li u chính liên quan n khóa leo thang: • LOCKIST - S l ng c a b nh (trong trang 4k) d tr qu n lý nh ng khóa cho m&i ng d ng c n i. S m c nh là n m m i l n trang 4K (200K) trên Windows. • MAXLOCKS - Ph n tr m c c i c a toàn b danh sách khóa cho m t ng d ng. M c nh là 22%. B i v#y, n u nh ng giá tr ng m nh c s d ng, khóa leo thang xu t hi n khi m t ng d ng n yêu c u h n 44K trong t ng b nh khóa (200K * 22% = 44K). N u s leo thang khóa xu t hi n th ng xuyên v i s thi t l#p này, t ng giá tr c a LOCKIST và MAXLOCKS. Khóa leo thang không t t cho hi u n ng nh ng l i gi m s xung t. T p nh#t ký tri u ch ng c a DB2 có th c dùng xác nh li u có
Nh p môn H qu n tr c s d li u DB2 148 ph i s leo thang khóa ang xu t hi n. (db2diag.log, th ng t p này n m trong th m c: C:\Program Files\IBM\SQLLIB\DB2) 13.6 Ki m soát khóa B n có th theo dõi nh ng s s d ng khóa b ng nh ch p nhanh khóa ng d ng DB2. / b#t nh ng nh ch p nhanh cho khóa, ta dùng l nh: UPDATE MONITOR SWICTHES USING LOCK ON Sau khi c cho phép, thông tin theo dõi s, c t#p h p. / thu c m t báo cáo c a khóa vào m t th i gian nh t nh, dùng l nh sau: GET SNAPSHOT FOR LOCKS FOR APPLICATION AGENT ID Hình 13.9: 8nh ch p nhanh k t qu c a m t ví d s d ng ng d ng khóa Application Lock Snapshot Snapshot timestamp = 11- 05-2002 00:09:08.672586 Application handle =9 Application ID = *LOCAL.DB2.00B9C5050843 Sequence number = 0001 Application name = db2bp.exe Authorization ID = ADMINISTRATOR Application status = UOW Waiting Status change time = Not Collected Application code page = 1252 Locks held =4 Total wait time (ms) =0 List Of Locks Lock Name = 0x05000700048001000000000052 Lock Attributes = 0x00000000 Release Flags = 0x40000000 Lock Count = 255 Hold Count =0 Lock Object Name = 98308 Object Type = Row Tablespace Name = TEST4K Table Schema = ADMINISTRATOR Table Name = T2 Mode =X Figue 13.9 – 0nh ch p nhanh khóa c a các ng d ng 13.7 Ch khóa Khi hai ho c nhi u các ng d ng c n th c hi n m t phép toán trên cùng m t i t ng, m t trong s ó có th ph i i nh#n c khóa. Theo m c nh m t ng d ng s,
Nh p môn H qu n tr c s d li u DB2 149 i vô h n. Th i gian ch i m t ng d ng c a m t khóa c ki m soát b i tham s c u hình c s d li u. Giá tr ng m nh c a tham s này là -1 ( i vô h n). Thanh ghi CURENT LOCK TIMEOUT c n c dùng t th i gian ch khóa cho m t k t n i ã cho. Theo m c nh, thanh ghi này c t giá tr c a LOCKTIMEOUT. S d ng SET LOCK TIMEOUT thay i giá tr c a nó. M t khi giá tr c a thanh ghi ã c t cho m t k t n i, nó s, t+n t i qua m&i giao d ch. Ví d : SET LOCK TIMEOUT=WAIT n 13.8 V n khóa ph thu c và phát hi n M t s b t c (deadlock) xu t hi n khi hai ho c nhi u các ng d ng c n i t i cùng c s d li u. S ch i ch a bao gi c gi i quy t b i vì m i ng d ng ang gi m t tài nguyên mà nó ph c v cho nh ng nhu c u khác nhau. Trong thi t k ng d ng, nh ng s b t c luôn là m t v n chi m nhi u th i gian. Hình 13.10: Minh h&a m t tình hu ng b t c. Hình 13.10 - K$ch b n s b t c Trong hình 13.10, ng i A gi c b t nho khô và gi s s, không cho phép ng i khác dùng cho n khi ông ta có s a. M t khác, ng i B gi c s a, và s, không cho phép ng i khác cho n khi ông ta có b t nho khô. B i v#y, chúng ta có m t tình hu ng b t c. / mô ph"ng m t tình hu ng b t c DB2, theo nh ng b c sau ây: 1. M hai c a s so n th o l nh c a DB2 (chúng tôi s, g&i là "CLP1" và "CLP2" t ng ng). Chúng i di n cho hai ng d ng khác nhau k t n i t i c s d li u. 2. T* CLP1 th c hi n các l nh sau: db2 connect to sample db2 +c update employee set firstnme = ‘Mary’ where empno = ‘000050’ / u tiên, chúng ta ang k t n i t i c s d li u có tên SAMPLE, và sau ó th c hi n m t l nh c#p nh#t hàng trên b ng employee làm v i "empno= 50000". L a
Nh p môn H qu n tr c s d li u DB2 150 ch&n "+c " trong câu l nh ch- ra r ng, chúng tôi không mu n c a s l nh c a DB2 t ng cam k t l nh này. Chúng tai ang c ý làm i u này sao cho chúng ta gi c khóa. 3. T* CLP2 th c hi n nh ng l nh sau: db2 connect to sample db2 +c update employee set firstnme = ‘Tom’ where empno = ‘000030’ 4 c a s c a CLP2, nó i di n cho ng d ng th hai, chúng ta c.ng n i vào c s d li u SAMPLE, nh ng l i c#p nh#t m t hàng khác b ng employee. 4. L nh t* CLP1: db2 +c select firstnme from employee where empno = ‘000030’ Sau khi nh n Enter th c hi n câu l nh SELECT trên, câu l nh SELECT này có v2 s, treo. Nó th#t s là không ph i là treo, mà là i khóa c a hàng này tr v do CLP2 ã gi trong b c 3. T i i m này, n u CLOCKTIMEOUT ã c l i v i giá tr ng m nh c a nó là -1, ng d ng CLP1 i mãi mãi. 5. L nh t* CLP2: db2 +c select firstnme from employee where empno = ‘000050’ B ng vi c ánh l nh SELECT trên, bây gi chúng ta ang t o ra m t s b t c. Phát bi u SELECT này c.ng có v2 treo, th c ra nó ang i khóa CLP1 ang gi tr v . Trong k ch b n b t c trên, DB2 s, ki m tra tham s c u hình c s d li u DLCHKTIME. Tham s này s, t kho ng th i gian ki m tra cho nh ng s b t c. Ch!ng h n, giá tr c a tham s này c t t i 10 giây, DB2 s, s d ng m t gi i thu#t bên trong xác nh giao d ch nào c n ph i quay lui (h+i x ), và m t giao d ch nào s, c ti p t c. N u b n ang g p nhi u tình hu ng b t c, b n c n ph i tái ki m tra nh ng giao d ch hi n h u xem xem có th t ch c l i c không. 13.9 Truy c p ng th i và th c ti n khóa t t nh t Sau ây là m t s m7o nh" truy c#p +ng th i và th c ti0n khóa t t nh t: Gi nh ng giao d ch càng ng n càng t t. /i u này có th t c b ng cách phát 1. hành th ng xuyên nh ng l nh COMMIT (th#m chí cho nh ng giao d ch ch- &c) khi ng d ng c a b n cho phép. Ch- ghi nh#t ký thông tin giao d ch khi có yêu c u. 2. T y d li u ã s d ng nhanh chóng: 3. ALTER TABLE ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE Th c hi n nh ng thay i d li u theo lô/nhóm. Ch!ng h n 4. DELETE FROM ( SELECT *FROM tedwas.t1 WHERE c1 = … FETCH FIRST s3000 ROWS ONLY)
Nh p môn H qu n tr c s d li u DB2 151 5. S d ng nh ng c tính t ng tranh c a nh ng công c chuy n i d li u trong DB2. 6. / t tham s m c LOCKTIMEOUT cho c s d li u (kho ng gi a 30-120 giây). B n *ng nên cho giá tr ng m nh c a nó là -1. B n có th c.ng s d ng khóa gi i h n th i gian (timeout) trên trên t*ng phiên làm vi c. 7. Không khôi ph c nhi u d li u h n c n thi t. Ch!ng h n, s d ng m nh FETCH FIRST n ROWS ONLY trong câu l nh SELECT.
Nh p môn H qu n tr c s d li u DB2 152 PH1N 3: H2C DB2 – PHÁT TRI3N 4NG D5NG Trong ph n này, chúng ta s, th o lu#n v các i t ng c a c s d li u n m t ng d i c a ng d ng nh th t c l u (stored procedure), hàm do ng i dùng nh ngh'a và b%y s ki n (trigger). R ng b n có th h&c c cách l#p trình s d ng DB2 nh m t máy ch d li u b ng nhi u ngôn ng khác nhau b ng cách kh o sát các ng d ng m %u i kèm trong th m c SQLLIB\samples khi chúng ta cài t m áy ch DB2. Hình d i ây là m t s ch ng trình m%u vi t b ng Java, s d ng DB2 trên n n W indows. M t s ch ng trình Java m%u i kèm DB2.
Nh p môn H qu n tr c s d li u DB2 153 14 Ch ng 14 – Các th t c SQL PL Trong ch ng này chúng ta s, th o lu#n v “th t c l u” (stored procedure). Th t c l u là m t i t ng ng d ng c s d li u, có th óng gói các câu l nh SQL và các nghi p v . L u tr h p lí m t ph n ng d ng trong c s d li u cho phép c i ti n hi u su t th c hi n nh gi m kh n ng k7t ng truy n m ng. H n n a, các th t c cho phép l u tr t#p trung t i m t n i nào ó các ng d ng khác c.ng có th s d ng c. Th t c l u trong DB2 có th vi t b ng SQL PL, C/C++, Java, Cobol, các ngôn ng c h tr CLR (Common Language Runtime) và OLE. Trong ch ng này, chúng ta s, t#p trung vào SQL PL vì tính ph bi n và n gi n c a nó. Hình 14.1 mô t cách ho t ng c a stored procedure. ng truy n m ng b6ng th t c. Hình 14.1 - Gi m k.t 4 góc trên bên trái c a hình, ta th y m t vài câu l nh SQL c th c thi l n l t h t cái này n cái kia. M i câu SQL c g i t* máy khách n máy ch , và máy ch tr k t qu v l i máy khách. N u nhi u câu SQL c th c thi theo ki u ó thì l ng d li u truy n trên m ng s, t ng lên. Tuy nhiên, góc d i ph i, ta th y th t c l u “myproc” c l u trên máy ch ch a nh ng câu SQL t ng t trên; và sau ó t i máy khách (bên phía trái), l nh CALL c dùng g&i th c hi n th t c. Cách th hai g&i th c
Nh p môn H qu n tr c s d li u DB2 154 hi n m t th t c hi u qu h n vì ch- có m t l nh g&i c truy n trên m ng và m t k t qu tr v cho máy khách. Th t c l u c.ng r t h u ích trong m c ích b o m#t c s d li u vì ng i s d ng s, c phân c p các quy n th c thi ho c ch- c xem các th t c; i u ó giúp cho h th ng c che ch n và không cho ng i dùng l y thông tin n u h& không có quy n. Kh n ng này là hoàn toàn kh thi vì ng i dùng không òi h"i nh ng quy n t ng minh trên các b ng ho c khung nhìn (view) trong th t c mà h& th c thi; h& ch- c n cc p quy n th c hi n các th t c ó. 14.1 DB2 Developer Workbench DB2 Developer Workbench (DWB) là công c c xây d ng trên n n t ng Eclipse, ph c v cho vi c phát tri n các th t c, hàm, truy v n XML, ng d ng SQLJ,… DWB n nh m t hình nh riêng r, (nó không ph i là m t ph n trong gói cài t DB2) nh ng hoàn toàn mi0n phí. Có th t i DWB v t* trang “Download” trên a ch- http://ibm.com/db2/express. Hình 14.2 Giao di n c a DWB. Hình 14.2 – DB2 Developer Workbench 14.1.2 T o m t th t c l u trong DWB / t o m t th t c b ng Java hay SQL PL, hãy làm theo các b c sau. L u ý r ng không th t o c các th t c c vi t b ng các ngôn ng khác trong DWB. B c 1: T o m t d án DWB (project) T* menu, ch&n File -> New -> Project và ch&n Data Development Project. Xem hình 14.3 d i ây.
Nh p môn H qu n tr c s d li u DB2 155 Hình 14.3 – Data development project T*ng b c làm theo h ng d%n nh#p tên d án, ch&n c s d li u b n mu n s d ng và xác nh rõ th m c JDK (cái nào m c nh th ng là úng). B c 2: T o m t th t c Sau khi t o xong d án, phía bên ph i c a c a s (data perspective) s, xu t hi n d án m i v*a t o. Trong hình 14.4 b n s, th y d án m i t o trong tình tr ng m r ng.
Nh p môn H qu n tr c s d li u DB2 156 Hình 14.4 – D án “ myProject” Hình 14.4 Cho th y các th m c khác nhau trong d án c a b n. Khi mu n t o m t th t c, b n nh n chu t ph i vào th m c th t c và ch&n New -> Stored Procedure. Hoàn thành các thông tin c yêu c u trong t*ng b c t o m i m t th t c nh : th t c ó k t h p v i d án nào, tên và ngôn ng vi t th t c (l u ý r ng ch- có SQL PL và Java c h tr trong DWB) và các câu l nh SQL dùng trong th t c. M c nh, DWB s, cung c p cho b n câu l nh SQL m%u ví d . T i ây b n có th nh n Finish và th t c s, c t o ra và trong ó có s d ng o n mã m%u và các câu l nh SQL trong ví d c cung c p trên. Xem hình 14.5
Nh p môn H qu n tr c s d li u DB2 157 Hình 14.5 – M t th t c m%u Trong hình 14.5, o n mã m%u cho th t c “PROCEDURE1” c phát sinh. B n có th thay th o n mã ó b ng o n mã riêng c a b n. / n gi n chúng ta s, dùng các o n mã m%u trên ti p t c kh o sát quy n sách này. B c 3: Biên d$ch m t th t c Khi m t th t c ã c t o, b n biên d ch và tri n khai b ng cách nh p ph i vào nó trong khung bên trái và ch&n Deploy. Hình 14.6 minh ho cho b c này.
Nh p môn H qu n tr c s d li u DB2 158 Hình 14.6 – Biên d$ch m t th t c B c 4: Th c thi m t th t c Khi m t th t c ã c biên d ch, b n có th th c thi nó b ng cách nh p ph i chu t vào nó và ch&n Run. K t qu s, xu t hi n trong khung “Result” góc d i ph i / th c thi m t th t c b ng dòng l nh, b n có th s d ng câu l nh CALL 14.2 M t s khái ni m v th t c SQL PL: Nh ng th t c l u v i ngôn ng th t c r t d0 t o và h&c. Trong DB2 th t c có hi u su t t t nh t. Th t c l u SQL PL (hay nói t t là “Th t c SQL”) là tiêu i m c a ch ng này. 14.2.1 C u trúc c a th t c Cú pháp c a th t c CREATE PROCEDURE proc_name [({tham s tùy ch n})] [thu c tính tùy ch n c a th t c] Trong ó là m t câu l nh ho c m t t#p các câu l nh c nhóm b i c u trúc BEGIN [ATOMIC]……………[END] 14.2.2 Nh ng thu c tính tùy ch n c a th t c Sau ây là 1 vài thu c tính tùy ch&n c a th t c: • LANGUAGE SQL Thu c tính này ch- ra ngôn ng mà th t c s, s d ng. LANGUAGE SQL là giá tr m c nh. V i nh ng ngôn ng khác nh JAVA hay C thì ta s d ng t ng ng thu c tính LANGUAGE JAVA hay LANGUAGE C
Nh p môn H qu n tr c s d li u DB2 159 • RESULT SETS Thu c tính này c n thi t n u th t c c a b n s, tr v t#p h p k t qu n • SPECIFIC my_unique_name /ây là tên duy nh t c gán cho th t c. M t th t c có th c n p è, ngh'a là, nhi u th t c l u tr có th có cùng m t tên, nh ng v i s l ng tham s khác nhau. B ng cách s d ng t* khóa SPECIFIC b n có th g n m t tên duy nh t v i nh ng th t c này, và i u này t o ra s d0 dàng cho vi c qu n lí các th t c l u tr . Ví d , xóa m t th t c s d ng t* khóa SPECIFIC b n có th d ng câu l nh: DROP SPECIFIC PROCEDURE. N u t* khóa SPECIFIC không c s d ng thì v i câu l nh xóa th t c l u tr nh trên, DB2 bi t c th t c nào c n c xóa (vì có r t nhi u th t c có tên gi ng nhau c ghi è) thì b n ph i ch- ra danh sách tham s i kèm c a th t c ó. 14.2.3 Các lo i tham s Có 3 lo i tham s trong th t c l u tr SQL PL: • IN - Tham s vào • OUT - Tham s ra • INOUT - Tham s v*a vào và ra Ví d : CREATE PROCEDURE proc(IN p1 INT, OUT p2 INT, INOUT p3 INT) Khi g&i th t c, t t c các tham s c n ph i c a vào trong câu l nh CALL. Ví d , g&i th t c trên b n ph i g&i nh sau: CALL proc (10, ?, 4) D u h"i (?) c dùng cho các tham s tr v c a câu l nh CALL. /ây là m t ví d khác c a th t c l u mà b n có th th : CREATE PROCEDURE P2 ( IN v_p1 INT, INOUT v_p2 INT, OUT v_p3 INT) LANGUAGE SQL SPECIFIC myP2 BEGIN -- my second SQL procedure SET v_p2 = v_p2 + v_p1; SET v_p3 = v_p1; END / g&i th t c này t* dòng l nh, ta dùng câu l nh: CALL P2 (3,4,?) 14.2.4 Chú thích trong th t c SQL PL Có hai cách chú thích trong th t c SQL PL: • S d ng 2 d u g ch ngang (--), ví d : -- ây là 1 chú thích theo ki u SQL
Nh p môn H qu n tr c s d li u DB2 160 • S d ng nh d ng nh ngôn ng l#p trình C, ví d : /* ây là 1 chú thích d ng C */ 14.2.5 Câu l nh ghép M t câu l nh ghép trong th t c là m t câu l nh trong ó ch a r t nhi u l nh th t c và các câu l nh SQL c bao b i t* khóa BEGIN và END. Khi t* khóa ATOMIC cs d ng theo sau t* khóa BEGIN, câu l nh ghép c xem nh là m t n v , ngh'a là, t t c các ch- th hay câu l nh trong câu l nh ghép ph i hoàn thành câu l nh ghép c hoàn thành. N u có m t câu l nh th t b i, t t c s, c tr v tr ng thái ban u (roll back). Hình 14.7 cho ta 1 minh h&a v câu l nh ghép. Hình 14.7 – Câu l nh ghép 14.2.6 Khai báo bi n / khai báo bi n, s d ng câu l nh DECLARE nh sau: DECLARE var_name [DEFAULT value]; /ây là m t vài ví d : DECLARE temp1 SMALLINT DEFAULT 0; DECLARE temp2 INTEGER DEFAULT 10; DECLARE temp3 DECIMAL(10,2) DEFAULT 100.10; DECLARE temp4 REAL DEFAULT 10.1; DECLARE temp5 DOUBLE DEFAULT 10000.1001; DECLARE temp6 BIGINT DEFAULT 10000; DECLARE temp7 CHAR(10) DEFAULT 'yes'; DECLARE temp8 VARCHAR(10) DEFAULT 'hello'; DECLARE temp9 DATE DEFAULT '1998-12-25'; DECLARE temp10 TIME DEFAULT '1:50 PM'; DECLARE temp11 TIMESTAMP DEFAULT '2001-01-05-12.00.00'; DECLARE temp12 CLOB(2G); DECLARE temp13 BLOB(2G); 14.2.7 Câu l nh gán / gán m t giá tr cho m t bi n, s d ng câu l nh SET. Ví d : SET total = 100;
Nh p môn H qu n tr c s d li u DB2 161 Câu l nh trên c.ng t ng ng v i câu l nh: VALUES(100) INTO total; Ngoài ra, b t kì bi n nào c.ng có th c gán v i giá tr NULL SET total = NULL M t bi u th c i u ki n s, c thi t l#p n u có nhi u h n m t dòng thì ch- l y dòng u tiên trong b ng SET total = (select sum(c1) from T1); SET first_val = (select c1 from T1 fetch first 1 row only) B n c.ng có th gán bi n v i các thu c tính ngoài c a c s d li u SET sch = CURRENT_SCHEMA; 14.3 Các con tr+ (cursor) M t con tr" là m t t#p h p ch a k t qu c a câu l nh SELECT. Cú pháp khai báo, m , chuy n giá tr (fetch) và óng con tr" nh sau: DECLARE CURSOR [WITH RETURN < ích tr v >] ; OPEN ; FETCH < tên con tr > INTO ; CLOSE < tên con tr >; Khi m t con tr" c khai báo, m nh W ITH RETURN có th cs d ng v i nh ng giá tr sau: • CLIENT: K t qu s, c tr v cho ng d ng máy khách • CALLER: K t qu s, c tr v cho máy khách ho c th t c l u ã g&i nó. /ây là m t ví d c a th t c s d ng con tr": CREATE PROCEDURE set() DYNAMIC RESULT SETS 1 LANGUAGE SQL BEGIN DECLARE cur CURSOR WITH RETURN TO CLIENT FOR SELECT name, dept, job FROM staff WHERE salary > 20000; OPEN cur; END 14.4 *i u khi n lu ng Nh trong r t nhi u ngôn ng khác, SQL PL có r t nhi u câu l nh có th c dùng i u khi n lu+ng th c hi n m c logic. D i ây là m t vài câu l nh c h tr : CASE (ch&n ra m t nhánh th c thi (tìm ki m n gi n)) IF FOR (th c thi m i dòng trong b ng)
Nh p môn H qu n tr c s d li u DB2 162 WHILE ITERATE (bu c ph i th c hi n vòng l p ti p theo. T ng t v i câu l nh CONTINUE trong C) LEAVE (r i kh"i m t kh i hay vòng l p) LOOP (l p vô h n nh) REPEAT GOTO RETURN CALL (g&i th t c) 14.5 G i th t c Nh ng o n mã sau ây s, ch- ra cách g&i m t th t c b ng các ngôn ng l#p trình khác nhau. G i th t c t& ng d ng CLI/ODBC SQLCHAR *stmt = (SQLCHAR *) “CALL MEDIAN_RESULT _SET( ? )” ; SQLDOUBLE sal = 20000.0; /*Ch ra parameter marker trong stmt */ SQLINTEGER salind = 0; /*Ch nh giá tr (variable) cho sal */ sqlrc = SQLPrepare(hstmt, stmt, SQL_NTS); sqlrc = SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_DOUBLE, SQL_DOUBLE, 0, 0, &sal, 0, &salind); SQLExecute(hstmt); if (salind == SQL_NULL_DATA) printf(“Median Salary = NULL\n”); else printf(“Median Salary = %.2f\n\n”, sal); /* L y t p k t qu u tiên */ sqlrc = StmtResultPrint(hstmt); if (sqlrc == SQL_SUCCESS) { /* Có 1 k t qu khác */ sqlrc = SQLMoreResult(hstmt); } Xem chi ti t t p ví d c a DB2: sqllib/samples/sqlproc/rsultset.c G i th t c t& ng d ng VB.NET Try ‘T o m t DB2 Command ch y th t c Dim procName As String = “TRUNC_DEMO” Dim cmd As DB2Command = conn.CreateCommand() Dim parm As DB2Parameter cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = procName ‘ ng ký các tham s u ra cho DB2Command parm = cmd.Parameters.Add(“v_lastname”, DB2Type.VarChar) parm.Direction = ParameterDirection.Output parm = cmd.Parameter.Add(“v_msg”, DB2Type.VarChar) parm.Direction = ParameterDirection.Output ‘ G i l i th tc
Nh p môn H qu n tr c s d li u DB2 163 Dim reader As DB2DataReader = cmd.ExecuteReader Catch myException As DB2Exception DB2ExceptionHandler(myException) Catch UnhandledExceptionHandler() End Try G i th t c t& ng d ng Java try { // K t n i CSDL m u String url = “jdbc:db2:sample”; con = DriverManager.getConnection(url); CallableStatement cs = con.prepareCall(“CALL trunc_demo(?, ?)”); // ng ký tham s u ra callStmt.registerOutParameter(1, Types.VARCHAR); callStmt.registerOutParameter(2, Types.VARCHAR); cs.execute(); con.close(); } catch (Exception e) { /* x lý ngo i l ây */ } 14.6 L i và x" lý i u ki n Trong DB2, các t* khóa SQLCODE và SQLSTATE c dùng xác nh s thành công hay th t b i trong vi c th c thi m t câu l nh SQL. Nh ng t* khóa này c n c khai báo t ng minh trong ph n ngoài cùng c a th t c nh sau: DECLARE SQLSTATE CHAR[5]; DECLARE SQLCODE INT; DB2 s, thi t l#p giá tr m t cách t ng cho nh ng t* khóa trên sau m i thao tác trên SQL (SQL operation). / i v i SQLCODE, giá tr c thi t l#p nh sau: • = 0, thành công • > 0, thành công nh ng có c nh báo • < 0, không thành công • = 100, không tìm th y d li u (ví d nh : câu l nh FETCH không tr v d li u) / i v i SQLSTATE, các giá tr c a nó nh sau: • Thành công: SQLSTATE ‘00000’ • Không th y: SQLSTATE ‘02000’ • C nh báo: SQLSTATE ‘01XXXX’ • Tr ng h p c bi t: các giá tr khác SQLCODE là m t c t cho c s d li u, chi ti t h n c SQLSTATE. SQLSTATE là tiêu chu n trong các h qu n tr cho c s d li u nh ng t ng quát h n. Nhi u
Nh p môn H qu n tr c s d li u DB2 164 SQLCODE có th trùng v i SQLSTATE. SQLCODE và SQLSTATE s, c nói rõ h n Ph l c A, X lý s c . M t i u ki n có th c t o ra b ng b t kì câu l nh SQL nào và s, trùng v i m t SQLSTATE. Ví d , m t i u ki n c th nh SQLSTATE ‘01004’ c t o ra khi có m t giá tr c làm tròn s trong quá trình thi hành câu l nh SQL. Thay vì dùng SQLSTATE ‘01004’ ki m tra i u ki n này, m t tên g&i có th c gán. Trong ví d c th này, tên “trunc” có th c gán cho i u ki n SQLSTATE ‘01004’ nh sau. DECLARE trunc CONDITION FOR SQLSTATE ‘01004’ Các i u ki n khác ã c nh ngh'a tr c: • SQLWARNING • SQLEXCEPTION • NOT FOUND X" lý i u ki n / x lý m t i u ki n, b n có th t o ra m t trình x lý i u ki n, và ph i ch- rõ các y u t sau: • /i u ki n mà nó ph i x lý • N i ph c h+i l i quá trình th c thi (d a vào ki u c a trình x lý: CONTINUE, EXIT or UNDO) • Hành ng c thi hành x lý i u ki n. Nh ng hành ng này có th là b t c câu l nh nào, bao g+m c các c u trúc i u khi n. N u m t i u ki n SQLEXCEPTION c t o ra, và không có trình x lý nào, th t c s, k t thúc và tr v cho máy khách m t thông báo l i. Các lo i trình x" lý Có 3 lo i trình x lý: CONTINUE – X lý này dùng ch- ra r ng, sau khi ngo i l x y ra, và trình x lý ã x lý i u ki n, dòng x lý s, TI9P T:C i n câu l nh ti p theo sau câu l nh v*a c t o ra. EXIT – x lý này dùng ch- ra r ng, sau khi ngo i l x y ra, và trình x lý ã x lý i u ki n, dòng x lý s, i n cu i th t c. UNDO – x lý này c dùng ch- ra r ng, sau khi ngo i l x y ra, và trình x lý ã x lý i u ki n, dòng x lý s, i n cu i th t c, và s, không làm gì i ho c khôi ph c b t kì câu l nh nào ã c th c thi (roll back). Hình 14.8 minh h&a s khác nhau gi a các trình x lý i u ki n và hành vi c a nó
Nh p môn H qu n tr c s d li u DB2 165 Hình 14.8 – Ki u c a các trình x" lý i u ki n 14.7 SQL ng Trong SQL ng, trái ng c v i SQL t'nh, toàn b câu l nh SQL không c nh#n bi t trong th i gian ch y. Ví d n u col1 và tabname là các bi n trong câu l nh này, chúng ta s, th c hi n v i SQL ng nh sau: ‘SELECT ‘ || col1 || ‘FROM ‘ || tabname; SQL ng c.ng c ngh dùng cho DDL tránh các v n ph thu c và sai sót khi óng gói. Nó c.ng c yêu c u khi th c hi n quy. SQL ng có th c th c thi theo hai cách: • Dùng l nh EXECUTE IMMEDIATE – ây là ý t ng cho vi c th c thi th t c SQL n. • Dùng l nh PREPARE song song v i l nh EXECUTE – ý t ng này c dùng th c thi a th t c SQL. /o n mã sau a ra m t ví d v SQL ng s d ng c hai cách trên. Ví d này gi s r ng có m t b ng T2 ã c t o nh sau: CREATE TABLE T2 (c1 INT, c2 INT) CREATE PROCEDURE dyn1 (IN value1 INT, INT value2 INT) SPECIFIC dyn1 BEGIN DECLARE stmt varchar(255); DECLARE st STATEMENT; SET stmt = ‘INSERT INTO T2 VALUE (?, ?)’; PREPARE st FROM stmt; EXECUTE st USING value1, value1; EXECUTE st USING value2, value2; SET stmt = INSERT INTO T2 VALUES (9, 9)’; EXECUTE IMMEDIATE stmt; END
Nh p môn H qu n tr c s d li u DB2 166 15 Ch ng 15 – Hàm n i tuy n, hàm ng i dùng $nh ngh-a, các hàm x" ký các s ki n b%y (Inline SQL PL, UDF, Triggers) Trong ch ng này, chúng ta s, xem xét v các hàm n i tuy n SQL các it ng khác c a CSDL nh hàm do ng i dùng nh ngh'a (UDF) và các b%y s ki n. 15.1 Hàm n i tuy n SQL Trong ch ng 14, chúng ta ã th o lu#n v vi c t o các th t c l u s d ng ngôn ng SQL PL. Ngôn ng SQL PL này c.ng có th c s d ng n i tuy n, ngh'a là có có th ng m t mình, không n m trong m t th t c nào c . Các l nh SQL PL c s d ng trong các UDF và hàm b%y s ki n c.ng là n i tuy n vì các mã l nh ã c thêm vào mã l nh c a các UDF và hàm b%y s ki n, b n thân nó ã là các SQL ng. SQL PL n i tuy n ch- h tr m t t#p con trong toàn b các l nh SQL PL, sau ây là các t* khóa c a SQL PL n i tuy n: DECLARE SET CASE FOR GET DIAGNOSTICS GOTO IF RETURN SIGNAL WHILE ITERATE LEAVE /ây là nh ng t* khóa không n m trong SQL PL n i tuy n: ALLOCATE CURSOR ASSOCIATE LOCATORS DECLARE DECLARE ...HANDLER PREPARE EXECUTE EXECUTE IMMEDIATE LOOP REPEAT RESIGNAL CALL COMMIT/ROLLBACK