Đồ án Tìm hiểu về PL/SQL

hát biểu CASE cho phép bạn chỉ rõ một bộ lọc để xác định nhóm hành động nào được thực hiện. Một phát biểu CASE tìm kiếm không có bộ lọc, thay vào đó nó có các điều kiện tìm kiếm. Các điều kiện này được tình theo thứ tự để xác định nhóm hành động nào để thực hiện. Phát Biểu Case: Một phát biệu CASE có cấu trúc như sau: CASE SELECTOR WHEN Expression 1 Then Statement 1; WHEN Expression 2 Then Statement 2; … WHEN Expression n Then Statement n; ELSE Statement n+1; END CASE; Từ khóa CASE đánh dấu điểm bắt đầu của phát biểu CASE. SELECTOR là một bộ lọc xác định mệnh đề WHEN nào được thực thi. Mỗi mệnh đề WHEN có một biểu thức (Expression) và một hay nhiều phát biểu có thể thực thi được gắn với nó. Mệnh đề ELSE là tùy chọn và giống như mệnh đề ELSE trong cấu trúc IF – THEN – ELSE. END CASE xác định điểm kết thúc của phả biểu CASE. Sơ đồ của phát biểu CASE được được minh họa trong hình sau: Lưu ý là bộ lọc (SELECTOR) chỉ được tính một lần. Các mệnh đề WHEN được tính tuần tự. GIá trị của một Expression được so sánh với giá trị của bộ lọc. Nếu chúng bằng nhau, các phát biểu được gắn với mệnh đề WHEN đó được thực thi và các mệnh đề WHEN sau không được tính. Nếu không có biểu thức so khớp với giá trị của bộ lọc thì mệnh đề ELSE sẽ được thực thi. Ta cùng xem lại ví dụ sau: DECLARE v_num NUMBER := &sv_user_num; BEGIN --Kiểm tra nếu số được người sử dụng cung cấp là số chẳn IF MOD(v_num,2) = 0 THEN DBMS_OUTPUT.PUT_LINE( v_num|| ‘la so chan’); ELSE DBMS_OUTPUT.PUT_LINE( v_num|| ‘la so le’); END IF; DBMS_OUTPUT.PUT_LINE(‘Hoan Thanh’); END; Xem xét một phiên bản mới của ví dụ này. Phiên bản này sử dụng phát biểu CASE thay cho phát biểu IF – THEN – ELSE: DECLARE v_num NUMBER :=&sv_user_num; v_num_flag NUMBER; BEGIN V_num_flag := MOD(v_num,2); --Kiem tra xem nguoi dung co nhap vao so chan hay khong CASE v_num_flag WHEN 0 THEN DBMS_OUTPUT.PUT_LINE(v_num||’la mot so chan’); ELSE DBMS_OUTPUT.PUT_LINE(v_num||’la mot so le’); END CASE; DBMS_OUTPUT.PUT_LINE(v_num||’Hoan thanh’); END; Trong ví dụ này, một biến mới v_num_flag được sử dụng như là một bộ lọc cho phát biểu CASE. Nếu hàm MOD trả về 0, thì số đó là số chẳn. Ngược lại là số lẻ. Nếu v_num được gán cho giá trị 7, ví dụ này cho ra kết quả như sau: Enter value for sv_user_num: 7 old 2: v_num NUMBER := &sv_user_num; new 2: v_num NUMBER := 7; 7 la mot so le Hoan thanh PL/SQL procedure successfully completed Phát Biểu Case Tìm Kiếm: Một phát biểu CASE tìm kiếm có các điều kiện tìm kiếm được đánh giá cho giá trị Boolean là TRUE, FALSE hoặc NULL. Khi một điều kiện tìm kiếm được đánh giá là TRUE thì nhóm lệnh gắn với điều kiện đó được thực thi.Phát biểu này có cấu trúc như sau CASE SELECTOR WHEN Điều kiện tìm kiếm 1 Then Phát biểu 1; WHEN Điều kiện tìm kiếm 2 Then Phát biểu 2; … WHEN Điều kiện tìm kiếm n Then Phát biểu n; ELSE Phát biểu n+1; END CASE; Khi điều kiện tìm kiếm được đánh giá là TRUE điều khiển được chuyển tới các thiết bị được gắn với nó. Nếu không có điều kiện tìm kiếm nào được đánh giá là TRUE, thì các phát biểu gắn với mệnh đề ELSE sẽ được thực thi. Sơ đồ của cấu trúc phát biểu CASE tìm kiếm được minh họa bằng hình sau: Xem phiên bản của ví dụ trước đã được hiệu chỉnh: DECLARE v_num NUMBER = &sv_user_num; BEGIN --Kiem tra xem nguoi dung co nhap vao so chan hay khong CASE WHEN MOD(v_num,2) = 0 THEN DBMS_OUTPUT.PUT_LINE(v_num||’la mot so chan’); ELSE DBMS_OUTPUT.PUT_LINE(v_num||’la mot so le’); END CASE; DBMS_OUTPUT.PUT_LINE(v_num||’Hoan thanh’); END; Lưu ý rằng ví dụ này hầu như giống với ví dụ trước. Trong ví dụ trước, biến v_num_flag được sử dụng như một bộ lọc và kết quả của hàm MOD được gắn với biến đó. Sau đó giá trị của bộ lọc được đem đi so sánh với các giá trị của biểu thức. Trong ví dụ này bạn đang sử dụng phát biểu CASE tìm kiếm, vì không có sự hiện diện của bộ lọc nên biến v_num được sử dụng cho các điều kiện tìm kiếm, vì thế không cần khai báo biến v_num_flag. Ví dụ này cho cùng một kết quả đối cùng một giá trị ban nảy ta nhập vào biến v_num: Enter value for sv_user_num: 7 old 2: v_num NUMBER := &sv_user_num; new 2: v_num NUMBER := 7; 7 la mot so le Hoan thanh PL/SQL procedure successfully completed Phân Biệt Case và Case Tìm Kiếm: Bạn đã thấy rằng phát biểu CASE tìm kiếm không có bộ lọc, ngoài ra các mệnh đề WHEN của nó chưa các điều kiện tìm kiếm mà kết quả trả về giá trị BOOLEAN giống như phát biểu IF. Hãy xem đoạn mã lệnh mà bạn đã thấy trong ví dụ trước DECLARE v_num NUMBER :=&sv_user_num; v_num_flag NUMBER; BEGIN V_num_flag := MOD(v_num,2); --Kiem tra xem nguoi dung co nhap vao so chan hay khong CASE v_num_flag WHEN 0 THEN DBMS_OUTPUT.PUT_LINE(v_num||’la mot so chan’); … Và: DECLARE v_num NUMBER = &sv_user_num; BEGIN --Kiem tra xem nguoi dung co nhap vao so chan hay khong CASE WHEN MOD(v_num,2) = 0 THEN DBMS_OUTPUT.PUT_LINE(v_num||’la mot so chan’); … Trong đoạn mã đầu tiên v_num_flag là một bộ lọc, nó là một biến PL/SQL được định nghĩa là kiểu NUMBER. Bởi vì giá trị của một biểu thức được so sánh với giá trị của bộ lọc, do đó giá trị của Expression phải trả về cùng kiểu dữ liệu với bộ lọc. Biểu thức ‘0’ chứa một số nên kiểu dữ liệu của nó cũng là số. Trong đoạn mã lệnh thứ 2, một biểu thức tìm kiếm trả về kết quả TRUE hay FALSE giống như các điều kiện của phát biểu IF. Kế tiếp, xem một ví dụ về phát biểu CASE gây ra một lỗi bởi vì kiểu dữ liệu do biểu thức trả về không cùng kiểu dữ liệu với bộ lọc. Ví dụ: DECLARE v_num NUMBER :=&sv_num; v_num_flag NUMBER; BEGIN CASE v_num_flag WHEN MOD(v_num,2) = 0 THEN DBMS_OUTPUT.PUT_LINE(v_num||’la mot so chan’); ELSE DBMS_OUTPUT.PUT_LINE(v_num||’la mot so le’); END CASE; DBMS_OUTPUT.PUT_LINE(v_num||’Hoan thanh’); END; Trong ví dụ này biến v_num_flag được định nghĩa có kiểu dữ liệu NUMBER. Tuy nhiên, kết quả của mỗi biểu thức trả về lại là BOOLEAN . Với kết quả như vậy, ví dụ trên gây ra lỗi như sau: Enter value for sv_num: 7 old 2: v_num NUMBER := &sv_num; new 2: v_num NUMBER := 7; CASE v_num_flag * ERROR at line 5: ORA-06550: line 5, column 9: PLS-00615: type mismatch found at ‘V_NUM_FLAG’ between CASE operand and WHEN operands ORA-06550: line 5, column 4: PL/SQL Statement ignored Xem một phiên bản đã được hiệu chỉnh, trong đó biến v_num_flag được định nghĩa là một biến kiểu BOOLEAN: DECLARE v_num NUMBER :=&sv_num; v_num_flag BOOLEAN; BEGIN CASE v_num_flag WHEN MOD(v_num,2) = 0 THEN DBMS_OUTPUT.PUT_LINE(v_num||’la mot so chan’); ELSE DBMS_OUTPUT.PUT_LINE(v_num||’la mot so le’); END CASE; DBMS_OUTPUT.PUT_LINE(v_num||’Hoan thanh’); END; Nếu biến v_num được gán giá trị 7, ví dụ này cho ra kết quả sau: Enter value for sv_user_num: 7 old 2: v_num NUMBER := &sv_user_num; new 2: v_num NUMBER := 7; 7 la mot so le Hoan thanh PL/SQL procedure successfully completed Mới nhìn thoán qua thì kết quả giống như những gì bạn mong muốn. Tuy nhiên, xem xét kết xuất của ví dụ này khi giá trị 4 được gán cho biến v_num : Enter value for sv_user_num: 4 old 2: v_num NUMBER := &sv_user_num; new 2: v_num NUMBER := 4; 4 la mot so le Hoan thanh PL/SQL procedure successfully completed Lưu ý ở lần chạy thứ 2 này, ví dụ này đưa ra kết quả không đúng mặc dù nó không sinh ra bất kỳ lỗi nào. Khi giá trị 4 được gán cho biến v_num, biểu thức: MOD(v_num,2) = 0 Trả về kết quả TRUE và nó được so sánh với biến v_num_flag. Tuy nhiên biến v_num_flag không được tạo bất kỳ giá trị ban đầu nào, vì thế nó mang giá trị NULL. Bởi vì NULL không bằng TRUE, phát biểu gắn với phần ELSE được thực thi. CASE EXPESSION: CASE EXPRESSION có cấu trúc hầu như giống với phát biểu CASE. Vì thế nó cũng có hai dạng CASE và CASE tim kiếm. Xem một ví dụ của phát biểu CASE được sử dụng trong chương này. Ví dụ: DECLARE v_num NUMBER :=&sv_user_num; v_num_flag NUMBER; BEGIN v_num_flag := MOD(v_num,2); --Kiem tra xem nguoi dung co nhap so chan hay khong CASE v_num_flag WHEN 0 THEN DBMS_OUTPUT.PUT_LINE(v_num||’la mot so chan’); ELSE DBMS_OUTPUT.PUT_LINE(v_num||’la mot so le’); END CASE; DBMS_OUTPUT.PUT_LINE(v_num||’Hoan thanh’); END; Xem phiên bản mới về ví dụ này phiên bản này sử dụng CASE EXPRESSION thay vì CASE: DECLARE v_num NUMBER :=&sv_user_num; v_num_flag NUMBER; v_result VARCHAR2(30); BEGIN v_num_flag := MOD(v_num,2); v_result := CASE v_num_flag WHEN 0 THEN vnum||’la mot so chan’ ELSE v_num||’la mot so le’ END; DBMS_OUTPUT.PUT_LINE(‘v_result’); DBMS_OUTPUT.PUT_LINE(v_num||’Hoan thanh’); END; Trong ví dụ này có một biến mới : v_result. Được sử dụng để chứa giá trị từ CASE EXPRESSION trả về. Nếu v_num được gán cho giá trị 8, ví dụ này xuất ra kết quả như sau: Enter value for sv_user_num: 8 old 2: v_num NUMBER := &sv_user_num; new 2: v_num NUMBER := 8; 8 la mot so chan Hoan thanh PL/SQL procedure successfully completed Một điều quan trọng cần lưu ý là có một khác biệt về cú pháp giữa hai phát biểu CASE và CASE EXPRESSION. Hãy xem bảng so sánh sau: Phát biểu CASE CASE EXPRESSION CASE v_num_flag WHEN 0 THEN DBMS_OUTPUT.PUT_LINE(v_num||’la mot so chan’); ELSE DBMS_OUTPUT.PUT_LINE(v_num||’la mot so le’); END CASE; CASE v_num_flag WHEN 0 THEN v_num||’la mot so chan’ ELSE v_num||’la mot so le’ END; Trong phát biểu CASE mỗi mệnh đề WHEN và ELSE chứa một phát biểu có thể thực thi được Mỗi phát biểu có thể thực thi được kết thúc bằng một dấu “;”. Trong CASE EXPRESSION các mệnh đề WHEN và ELSE chứa một biểu thức nhưng không kết thúc bằng dấu “;”. Chỉ có một dấu “;” hiện diện sau từ khóa END, đó là nơi kết thúc của CASE EXPESSION với CASE là END CASE. Kế tiếp xem một phiên bản khác của ví dụ trước sử dụng CASE EXPRESSION tìm kiếm: DECLARE v_num NUMBER :=&sv_user_num; v_result VARCHAR2(30); BEGIN v_result := CASE v_num_flag WHEN MOD(v_num,2) = 0 THEN vnum||’la mot so chan’ ELSE v_num||’la mot so le’ END; DBMS_OUTPUT.PUT_LINE(‘v_result’); DBMS_OUTPUT.PUT_LINE(v_num||’Hoan thanh’); END; Trước đây bạn đã học là CASE EXPRESSION trả về một giá trị đơn sau đó nó được gán cho một biến. Trong ví dụ bạn đã thấy trước đây, thao tác gán này được thực hiện thông qua phép toán gán “:=”. Nhắc lại là có thể cũng có cách gán một giá trị cho một biến PL/SQL thông qua phát biểu SELECT INTO. Xem ví dụ sau, phát biểu CASE EXPRESSION sử dụng phát biểu SELECT INTO: DECLARE v_course_no NUMBER; v_description VARCHAR2(50); v_prereq VARCHAR2(35); BEGIN SELECT course_no description, CASE WHEN prerequisite IS NULL THEN ’Khong co mon hoc tien quyet’ ELSE TO_CHAR(prerequisite) END prerequisite INTO v_course_no v_description,v_prereq FROM course WHERE course_no=20; DBMS_OUTPUT.PUT_LINE(‘Course: ’||v_course_no); DBMS_OUTPUT.PUT_LINE(‘Description: ’||v_description); DBMS_OUTPUT.PUT_LINE(‘Prerequisite: ’||v_prereq); END Trong ví dụ này bạn đang trình bày số hiệu môn học, tên môn học, và môn học tiên quyết của môn học trên màn hình. Hơn nữa nếu một môn học không có môn tiên quyết, một thông báo thể hiện điều này sẽ xuất hiện trên màn hình. Để kết quả như mong muốn, một CASE EXPRESSION được sử dụng như một trong các cột trong phát biểu SELECT INTO. Giá trị của nó được gán cho biến v_prereq. Lưu ý là không có dấu “;” sau từ END của CASE EXPRESSION. Ví dụ này cho ra kết quả sau: Coures: 20 Description: Gioi thieu ve May Tinh Prerequisite: khong co mon hoc tien quyet PL/SQL procedure successfully completed Môn học 20 không có môn học tiên quyết, với kết quả này điều kiện tìm kiếm: WHEN prerequisite IS NULL THEN Được đánh giá là TRUE và giá trị ‘khong co mon hoc tien quyet’ được gắn cho biến v_prereq. Một điều quan trọng cần lưu ý là tại sao hàm TO_CHAR được sử dụng trong mệnh đề ELSE của CASE EXPRESSION. CASE WHEN prerequisite IS NULL THEN ’Khong co mon hoc tien quyet’ ELSE TO_CHAR(prerequisite) END Một CASE EXPRESSION trả về giá trị đơn do đó nó thuộc về một kiểu dữ liệu đơn. Vì vậy, có một điều quan trọng là bất kỳ phần nào của CASE EXPRESSION được thực thi, nó luôn trả về cùng kiểu dữ liệu. Trong CASE EXPRESSION trước mệnh đề WHEN trả về kiểu dữ liệu VARCHAR2. Mệnh đề ELSE trả về giá trị của cột PREREQUISITE trong bảng COURSE. Cột này được định nghĩa là kiểu dữ liệu NUMBER, vì thế nó cần chuyển thành kiểu dữ liệu chuỗi. Khi hàm TO_CHAR không được sử dụng, CASE EXPRESSION gây ra lỗi như sau: ELSE prerequisite * ERROR at line 8: ORA-06550: line 9, column 19: PL/SQL : ORA-00932: inconsistent datatypes ORA-06550: line 6, column 4: PL/SQL Statement ignored NULL IF Và COALESCE: Các hàm NULLIF và COALESCE được định nghĩa bởi chuẩn ANSI 1999 là phát biểu CASE tóm tắt. Cà 2 hàm này được sử dụng như một dạng của CASE EXPRESSION. Hàm NULLIF Hàm NULLIF so sánh 2 biểu thức. Nếu chúng bằng nhau, hàm này trả về NULL. Ngược lại nó trả về giá trị của biểu thức đầu tiên, hàm NULL IF có cấu trúc như sau: NULLIF (biểu thức 1, biểu thức 2) Nếu biểu thức 1 bằng biểu thứ 2 thì NULLIF trả về giá trị NULL. Ngược lại, NULLIF trả về biểu thức 1. Lưu ý là hàm NULLIF ngược với hàm NVL. Nếu biểu thức đầu tiên là NULL, NVL trả về biểu thức 2. Nếu biểu thức đầu tiên khác NULL, NVL trả về biểu thức đầu tiên. Hàm NULLIF giống với CASE EXPRESSION sau: CASE WHEN expression1=expression2 THEN NULL ELSE expression1 END Xem ví dụ sau của NULLIF: DECLARE v_num NUMBER =&sv_user_num v_remainder NUMBER; BEGIN --tinh toan so du va neu no la 0 tra ve NULL v_remainder :=NULLIF(MOD(v_num,2),0); DBMS_OUTPUT.PUT_LINE(‘v_remainder: ’||v_remainder); END; Ví dụ này có một vài điều giống với ví dụ mà bạn đã thấy trước đây trong chương này, một giá trị đước gán cho biến v_num trong lúc chạy chương trình. Kế tiếp giá trị này được chia cho 2 và số dư của nó được so sánh với 0 thông qua hàm NULLIF. Nếu số dư bằng 0 hàm NULLIF trả về NULL, ngược lại nó trả về số dư đó. Giá trị được trả về bởi hàm NULLIF được lưu trử trong biến v_remainder và được thể hiên trên màn hình thông qua phát biểu DBMS_OUTPUT.PUT_LINE. Khi chạy ví dụ này cho ra kết quả như sau. Đối với lần chạy đầu tiên, 5 được gán cho biến v_num: Enter value for sv_user_num: 5 old 2: v_num NUMBER := &sv_user_num; new 2: v_num NUMBER := 5; v_remainder: 1 PL/SQL procedure successfully completed Đối với lần chạy thứ 2, 4 được gán cho giá trị v_num: Enter value for sv_user_num: 4 old 2: v_num NUMBER := &sv_user_num; new 2: v_num NUMBER := 4; v_remainder: PL/SQL procedure successfully completed Trong lần chạy thứ nhất, 5 không chia hết cho 2 và hàm NULLIF trả về giá trị là số dư. Trong lần chạy thứ 2, 4 chia hết cho 2 và hàm NULLIF trả về NULL như là giá trị của số dư. Hàm NULLIF có một giới hạn, bạn không thể gán một chứ NULL cho biểu thức 1. Xem kết xuất khác của ví dụ này. Khi chạy, biến v_num được gán cho giá trị NULL: Enter value for sv_user_num: NULL old 2: v_num NUMBER := &sv_user_num; new 2: v_num NUMBER := NULL; v_remainder: PL/SQL procedure successfully completed Khi NULL được gán cho biến v_num, cả 2 hàm MOD và NULLIF trả về NULL. Ví dụ này không gây ra một lỗi nào, bởi vì chữ NULL được gán cho biến v_num và nó không được sử dụng cho biểu thức đầu tiên trong hàm NULLIF. Kế tiếp, một phiên bản hiệu chỉnh của ví dụ trước: DECLARE v_remainder NUMBER; BEGIN --tinh toan so du va neu no la 0 tra ve NULL v_remainder :=NULLIF(NULL,0); DBMS_OUTPUT.PUT_LINE(‘v_remainder: ’||v_remainder); END; Ở phiên bản trước, hàm MOD được sử dụng như biểu thức 1. Trong phiên bản này, chữ NULL thay thể cho hàm MOD và hệ quả là ví dụ này gây ra lỗi như sau: v_remainder :=NULLIF(NULL,0); ERROR at line 5: ORA-06550: line 5, column 26: PLS-00619: the first operand in the NULLIF expression must not be NULL ORA-06550: line 5, column 4: PL/SQL: Statement ignored Hàm COALESCE Hàm COALESCE so sánh mỗi biểu thức với NULL từ danh sách các biểu thức và trả về giá trị của biểu thức không NULL đầu tiên. Hàm COALESCE có cấu trúc như sau: COALESCE(expression1, expression2,…,expressionN) Nếu biểu thức 1 bằng NULL thì biểu thức 2 được tính. Nếu biểu thức 2 khônng bằng NULL thì hàm này trả về biểu thức 2. Nếu biểu thức 2 cũng bằng NULL thì biểu thức kế tiếp được tính, nếu tất cả biểu thức đều bằng NULL thì hàm này trả về NULL. Lưu ý là hàm COALESCE giống như một hàm NVL lồng nhau: NVL(expression1, NVL(expression2,NVL(expression…))) Hàm COALESECE cũng có thể được sử dụng như một lựa chọn đối với CASE EXPRESSION Ví dụ COALESCE (exprssion1, expression2) Giống với CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END Nếu có nhiều hơn 2 biểu thức để tính ,thì COALESCE (exprssion1, expression2,…,expressionN) Giống với CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE COALESCE (expression2,.., expressionN) END Xem ví dụ sau của hàm COALESCE: Ví dụ: SELECT e.student_id, e.section_id, e.final_grade, g.numeric_grade, COALESCE (e.final_grade, g.numeric_grade,0) grade FROM enrollment e, grade g WHERE e.student_id = g.student_id AND e.section_id = g.section_id AND e.student_id = 102 AND g.grade_type_code = ‘FI’ Phát biểu SELECT trên trả về kết quả như sau: Student_id Section_id Final_grade Numeric_grade Grade 102 86 85 85 102 89 92 92 92 Giá trị Grade bằng với giá trị Numeric_Grade trong dòng đầu tiên. Hàm COALESCE so sánh giá trị Final_Grade với NULL. Nếu nó là NULL thì giá trị Numeric_Grade được so sánh với NULL. Bởi vì giá trị NUMERIC_GRADE không bằng NULL, hàm COALSECE trả về giá trị của NUMERIC_GRADE. Giá trị GRADE bằng với giá trị FINAL_GRADE trong dòng thứ 2. Hàm COALESCE trả về giá trị của FINAL_GRADE, bởi vì nó không bằng NULL Hàm COALESCE trình bày trong ví dụ trước giống với phát biểu NVL hay giống như CASE EXPRESSION sau: NVL(e.final_grade, NVL(g.numeric_grade,0)) CASE WHEN e.final_grade IS NOT NULL THEN e.final_grade ELSE COALESCE (g.numeric_grade,0) END Hàm COALESCE có giới hạn sau: ít nhất một trong những biểu thức không chứa chữ NULL. Xem xét ví dụ sau và kết xuất của nó: Ví dụ: SELECT COALESCE(NULL, 3, 8) FROM DUAL; COALESCE(NULL,3 ,8) 3 Kế tiếp, xem phiên bản đã được hiệu chỉnh về phát biểu SELECT và lỗi cú pháp do nó tạo ra: Ví dụ: SELECT COALESCE (NULL, NULL, NULL) FROM DUAL; SELECT COALESCE (NULL, NULL, NULL) * ERROR at line 1: ORA-00938: not enough agruments for function Phát biểu SELECT này gây ra lỗi cú pháp, bởi vì tất cả các biểu thức trong hàm COALESCE đều chứa chữ NULL. Điều khiển lặp: Chương trình là sự kết hợp các chỉ thị cần được thực thi lặp lại => Vòng lặp là những điều kiện thuận lợi cho phép 1 tập các chỉ thị được thực hiện lặp lại. Có 4 loại vòng lặp: Vòng lặp đơn giản. Vòng lặp While. Vòng lặp For. Vòng lặp For dựa trên 1 cursor. Vòng lặp đơn giản: LOOP Phát biểu 1; Phát biểu 2; … END LOOP; - Các phát biểu được thực thi và điều khiển từ cuối lên đầu vòng lặp. - Các phát biểu liên tiếp sẽ được thực thi vô thời hạn => Vòng lặp vô hạn => Cần có điều kiện thoát: EXIT và EXIT WHEN EXIT LOOP Phát biểu 1; Phát biểu 2; IF Điều kiện THEN EXIT; END IF; END LOOP; Phát biểu 3; Nếu điều kiện EXIT là TRUE => Chuyển tới phát biểu đầu tiên sau END LOOP. EXIT WHEN LOOP Phát biểu 1; Phát biểu 2; EXIT WHEN Điều kiện; END LOOP; Phát biểu 3; Nếu điều kiện EXIT WHEN là TRUE=> Chuyển tới phát biểu đầu tiên sau END LOOP. Vòng lặp WHILE WHILELOOP Phát biểu 1; Phát biểu 2; … Phát biểu N; END LOOP; * Kết thúc sớm vòng lặp: WHILE Điều kiện kiểm tra LOOP Phát biểu 1; Phát biểu 2; IF Điều kiện thoát THEN EXIT END IF; END LOOP; Phát biểu 3; Hoặc WHILE Điều kiện kiểm tra LOOP Phát biểu 1; Phát biểu 2; EXIT WHEN Điều kiện thoát END LOOP; Phát biểu 3; Nếu điều kiện thoát là TRUE trước khi điều kiện kiểm tra vòng lặp là FALSE => Vòng lặp kết thúc sớm. Nếu điều kiện thoát là FALSE trước khi điều kiện kiểm tra vòng lặp là TRUE => Vòng lặp không kết thúc sớm. Vòng lặp FOR dạng số FORloop_counterIN [REVERSE]lower_limit..upper_limitLOOP Phát biểu 1; Phát biểu 2; … Phát biểu N; END LOOP; * Kết thúc vòng lặp sớm FORloop_counterIN lower_limit..upper_limitLOOP Phát biểu 1; Phát biểu 2; IFTHEN EXIT; END IF; END LOOP; Phát biểu 3; Hoặc FORloop_counterIN lower_limit..upper_limitLOOP Phát biểu 1; Phát biểu 2; EXIT WHEN Exit_condition END LOOP; - Nếu điều kiện thoát là TRUE trước khi biến đếm vòng lặp đặt tới giá trị kết thúc => Vòng lặp FOR bị ngừng sớm. - Nếu biến đếm vòng lặp đặt tới giá trị kết thức trước khi điều kiện thoát là TRUE =>Không có kết thúc sớm vòng lặp Các vòng lặp lồng nhau Bất kì loại nào trong 3 loại vòng lặp trên đều có thể lồng vào nhau Để dễ phân biệt các vòng lặp => Đặt tên nhãn ngay trước khi bắt đầu vòng lặp > Tên nhãn của vòng lặp được thêm vào sau mỗi phát biểu END LOOP sẽ làm việc đọc dễ dàng hơn, cho biết vòng lặp nào kết thúc. Cursor và các thao tác trên Cursor : Định nghĩa : Cursor là vùng bộ nhớ cho phép ta cấp phát bộ nhớ và truy cập thông tin được trích từ 1 phát biểu SQL. Ở đây ta sẽ nói về cách khai báo 1 Cursor tường minh cho phép người dùng sử lý nhiều dòng do 1 truy vấn trả về và cho phép người dùng viết lệnh để xử lý từng dòng mỗi lần. Thao tác trên Cursor: Cursor là 1 tên hay 1 con trỏ chỉ đến 1 vùng nhớ. Có 2 tính năng quan trọng gồm: Cursor cho phép fetch (lấy dữ liệu về) và xử lý các dòng trả về bởi lệnh Select, mỗi lần xử lý 1 dòng. Mỗi Cursor được đặt tên để có thể tham chiếu tới qua tên này. Có 2 loại Các loại Cursor : Cursor ngầm định : được tự động khai báo bởi Oracle khi thực thi 1 phát biểu SQL. Người dùng ko nhận ra được điều này. Cursor tường minh: dùng cho truy vấn nào trả về nhiều hơn 1 dòng. Người lập trình khai báo Cursor trong khối lệnh PL/SQL và xử lý tuần tự từng dòng dữ liệu khi nó được trả về bởi Cursor đó. Các bước sử dụng biến cursor: Khai báo Cursor --> mở Cursor --> lấy dữ liệu để xử lý --> đóng Cursor. Tìm hiểu các loại Cursor: Cursor ngầm định : Nguyên tắc thực thi của Cursor ngầm định là : Nếu ko có Cursor tường minh trong phát biểu SQL thì bất kì khối lệnh PL/SQL nào cùng dùng 1 Cursor ngầm định khi mà 1 phát biểu SQL được thực thi. 1 Cursor được gắn kèm với mỗi phát biểu : Update, Delete, Insert. Tất cả lệnh Update và Delete có Cursor tương ứng với tập các dòng sẽ bị ảnh hưởng bởi thao tác đó. Còn Insert thì Cursor ngầm định sẽ giúp tạo ra nơi chứa dữ liệu được chèn vào CSDL. Cursor được mở gần đây nhất là ‘SQL%’. Cursor ngầm định dùng để xử lý lệnh Insert, Update, Delete, Select into. Khi xử lý Cursor ngầm định, Oracle tự động thực hiện các thao tác Open, Fetch, Close. Chú ý 1 Cursor ngầm định ko cho biết có bao nhiêu dòng bị ảnh hưởng bởi thao tác cập nhật. SQL%ROWCOUNT trả về số dòng được cập nhật. VD: Oracle kết hợp tự động với Cursor ngầm định trong Select Into và chuyển giá trị vào biến v_first_name và v_last_name.Khi xong Select Into, Cursor ngầm định tự đóng lại. Cursor tường minh : Cursor tường minh có lợi thế hơn Cursor ngầm định ở chỗ là có khả năng bẫy lỗi dữ liệu. Các bước thao tác với Cursor tường minh : Khai báo Cursor Mở Cursor (cấp phát bộ nhớ cho Cursor ). Lấy dữ liệu về từ Cursor. Cursor được mở và được nhận dữ liệu Đóng Cursor (giải phóng bộ nhớ). Khai báo 1 Cursor : Là định nghĩa tên Cursor gắn với lệnh Select. Nên đặt tên Cursor bắt đầu là chữ ‘c’. Dạng : Cursor IS lệnh Select Lệnh truy vấn; Ví dụ1: CURSOR c_Dept IS SELECT deptno, dname FROM dept WHERE deptno>10; Ví dụ 2: CURSOR c_Dept(p_Deptno NUMBER) IS SELECT deptno, dname FROM dept WHERE deptno>10; Ví dụ 3: DECLARE Cursor C_MyCursor IS Select * From ZipCode Where State = ‘NY’; ……. Chú ý là Cursor phải được khai báo trước khi tham chiếu. Record : Là 1 cấu trúc tập hợp dữ liệu (> 1 phần tử). Nôm na có thể coi record ứng với 1 dòng trong bảng dữ liệu. PL/SQL có 3 loại Record :Table-base (dựa trên bảng dữ liệu);Cursor-base (dựa trên Cursor ); Người dùng định nghĩa. Table-base + Cursor-base: Table-base là dạng Record được rút từ các cột trong bảng. Cursor base là record mà cấu trúc so khớp với các thành phần của Cursor được định nghĩa trước đó (Cursor phải được khai báo trước đó). Để tạo 1 record thuộc 1 dạng này, ta dùng thuộc tính %ROWTYPE. % ROWTYPE. VD. SET SERVEROUTPUT ON DECLARE vr_student student% ROWTYPE BEGIN SELECT *INTO vr_student FROM student WHERE student_id=156; DBMS_OUTPUT.PUTLINE(vr_student.first_name ||’’ ||vr_student.last_name|| ‘has an ID of 156 ’); EXCEPTION WHEN no_data_found THEN RAISE_APPLICATION_ERROR (-2001, ‘The Student’|| ‘is not in the database’); END Biến vr_student là biến record ở bảng Student. Khi tham chiếu đến record, dùng cú pháp: record_name.item_name Xử lý 1 Cursor tường minh : Để điều khiển 1 Cursor tường mình ta phải mở nó (Open Cursor) với 4 bước : Xem xét các biến trong Where . Dựa theo giá trị các biến, các biến chỉ được xem xét vào thời điểm Open Cursor. PL/SQL nhận dạng dữ liệu là tất cả các