Đề tài Tìm hiểu về pl/sql

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 dòng trong bảng mà so khớp với điều kiện của Where. Con trỏ chỉ tới dòng đầu tiên. Cú pháp : OPEN Tên cursor | Tên cursor( danh sách biến); Ví dụ1: OPEN c_Dept; Ví dụ2: OPEN c_Dept(10); Tiếp theo, Fetch các dòng trong 1 Cursor. Fetch Cursor là lấy dữ liệu từ Cursor đó. Có 2 cách : FETCH Cursor_name INTO Hoặc : FETCH Cursor_name INTO VD: FETCH c_Dept INTO v_Dept; Lệnh FETCH rút mỗi lần 1 dòng dữ liệu và thực thi trong vòng lặp. Giá trị mỗi dòng lưu trong 1 biến tương ứng. Sau mỗi lần chạy FETCH, con trỏ chạy đến dòng kế tiếp và chạy đến khi tất cả dòng được FETCH. Câu lệnh FETCH ở sau cùng sẽ ko gán giá trị biến mà vẫn giữ giá trị trước của nó. VD. Thao tác sau cùng là đóng record khi mà tất cả các dòng trong Cursor đều đã lấy về và giải phóng tài nguyên của Cursor. Cú pháp : CLOSE Ví dụ: CLOSE c_Dept; Sử dụng các thuộc tính của Cursor : Bảng thuộc tính: Thuộc tính Cursor Cú pháp Ý nghĩa %NOTFOUND Cursor_name%NOTFOUND Thuộc tính kiểu Boolean trả ra TRUE nếu thao tác FETCH trước đó ko trả ra dòng nào và ngược lại. %FOUND Cursor_name%FOUND Thuộc tính kiểu Boolean trả ra TRUE nếu thao tác FETCH trước đó trả ra 1 dòng và FALSE nếu ngược lại. %ROWCOUNT Cursor_name%ROWCOUNT Trả về số lượng record fetch từ Cursor ở lúc đó. %ISOPEN Cursor_name%ISOPEN Thuộc tính kiểu Boolean trả ra TRUE nếu Cursor đang mở và FALSE nếu ngược lại. Các thuộc tính của Cursor có thể dùng với Cursor ngầm định với tiền tố SQL, VD: Select %ROWCOUNT. Lưu ý trong Cursor : Selectlist trong Cursor : Các biến PL/SQL hay các thành phần trong record phải khớp với danh sách cột trong lệnh SELECT; nghĩa là số biến hay số thành phần trong record = với số cột hay biểu thức trong danh sách cột của lệnh SELECT. Phạm vi của Cursor phải khai báo trong khối lệnh chính. Các biến, biểu thức , hàm có thể đưa vô danh sách phát biểu của SELECT Bí danh trong Cursor : Ta có thể đặt 1 tên khác cho cột/ biểu thức trong lệnh SELECT. Trong các cột của Cursor tường minh, bí danh (alias) được dùng tính toán khi Fetch trong 1 record được khai báo là %ROWTYPE cùng Cursor đó hoặc khi ta cần tham chiếu đến cột tính toan trong chương trình này. Bài tập Ví dụ : Ví dụ1: DECLARE -- Khai báo cursor để truy vấn dữ liệu CURSOR c_Emp IS SELECT * FROM emp WHERE dept_id = 10; -- Khai báo biến cursor tương ứng để chứa dòng dữ liệu v_Emp c_EMP%rowtype; BEGIN -- Mở cursor OPEN c_Emp; LOOP -- Lấy dòng dữ liệu từ cursor FETCH c_Emp INTO v_Emp; -- Thoát khỏi vòng lặp nếu đã lấy hết dữ liệu trong cursor EXIT WHEN c_Emp%notfound; -- Bổ sung dữ liệu vào Emp_ext theo dữ liệu lấy được từ cursor INSERT INTO Emp_ext (empno, ename, job) VALUES (v_Emp.empno, v_Emp.ename, v_Emp.job); END LOOP; -- Đóng cursor CLOSE c_Emp; END; Ví dụ 2: DECLARE -- Khai báo cursor, có cho phép cập nhật dữ liệu CURSOR c_Dept IS SELECT dname, loc FROM dept FOR UPDATE OF loc; -- Khai báo biến lưu trữ dữ liệu v_Dept c_Dept%ROWTYPE; v_sales_count NUMBER:=0; v_non_sales NUMBER:=0; BEGIN -- Mở cursor OPEN c_Dept; LOOP -- Lấy từng dòng dữ liệu của cursor để xử lý FETCH c_Dept INTO v_Dept; -- Thoát khỏi lệnh lặp nếu đã duyệt hết tất cả dữ liệu EXIT WHEN c_Dept %notfound; IF (v_Dept.dname = 'SALES')AND(v_Dept.loc!='DALLAS') THEN -- Cập nhật dữ liệu trên cursor UPDATE Dept SET loc='DALLAS' WHERE CURRENT OF c_Dept; -- Đếm số lượng bản ghi được cập nhật v_sales_count := sales_count + 1; ELSIF (v_dept.dname != 'SALES')AND(v_Dept.loc!='NEWYORK') THEN -- Cập nhật dữ liệu trên cursor UPDATE Dept SET loc = 'NEWYORK' WHERE CURRENT OF c_Dept; -- Đếm số lượng bản ghi được cập nhật v_non_sales := v_non_sales + 1; END IF; END LOOP; -- Đóng cursor CLOSE c_Dept; -- Lưu giữ các thông số vừa xác định vào bảng INSERT INTO counts (sales_set, non_sales_set) VALUES (v_sales_count, v_non_sales); -- Ghi nhận các thay đổi dữ liệu ở trên COMMIT; END; Cách dùng Cursor kết hợp với vòng lặp FOR và Cursor lồng nhau: Cursor trong vòng lặp For: Khi sử dụng, các bước mở, xử lý, đóng được thực hiện ngầm định; giúp khối lệnh đơn giản và dễ quản lý hơn. Nó chỉ ra trình tự các phát biểu được lặp lại đối với mỗi dòng được trả về từ Cursor và FETCH mỗi record từ Cursor.Cuối vòng lặp có thêm lệnh ENDLOOP nữa. VD: Create table_log (description varchar2 (250)) DECLARE CURSOR c_student IS SELECT student_id, last_name, first_name FROM student WHERE student_id < 110; BEGIN FOR r_student IN c_student LOOP INSERT INTO table_log VALUES (r_student.lastname) END LOOP; END Cursor lồng nhau: Thực ra là vòng lặp lồng nhau thôi. Nếu 1 Cursor cha có 2 Cursor con thì mỗi vòng lặp nó sẽ thực hiện mỗi Cursor con. SET SERVEROUTPUT ON DECLARE v_zip zipcode.zip%TYPE; v_student_flag CHAR; CURSOR c_zip IS SELECT zip, xity, state FROM zipcode WHERE state = 'CT'; CURSOR c_student IS SELECT first_name, last_name FROM student WHERE zip=v_zip; BEGIN FOR r_zip IN c_zip LOOP v_student_flag :='N'; v_zip:= r_zip.zip; DBMS_OUTPUT.PUT_LINE(CHR(10)); DBMS_OUTPUT.PUT_LINE('Students living in'|| r_zip.city); FOR r_student IN c_student LOOP DBMS_OUTPUT.PUT_LINE(r_student.first_name||''||r_student.last_name); v_student_flag:='Y'; END LOOP; IF v_student_flag = 'N'; THEN DBMS_OUTPUT.PUT_LINE('No student for this zipcode'); END IF; END LOOP; END; Thủ tục và hàm Thủ tục Các chương trình thường được module hóa. Module hóa là quá trình ta chia các khối mã nguồn lớn thành các khối (đơn vị) chức năng nhỏ hơn (các module), các khối này có thể gọi lẫn nhau. Việc làm này giúp dễ quản lí mã nguồn, tăng tính tái sử dụng các chức năng, dễ đọc và sẽ tăng độ tin cậy khi viết mã chương trình. Phần này sẽ giới thiệu module hóa chương trình dùng thủ tục. Định dạng chung của PL/SQL procedure như sau: PROCEDURE [schema.]name [( parameter [, parameter ...] ) ] [AUTHID DEFINER | CURRENT_USER] IS [declarations] BEGIN executable statements [ EXCEPTION exception handlers] END [name]; Trong đó: Schema: tên schema sở hữu thủ tục này, thông số này là tùy chọn. Mặc định nó là current user(người dùng hiện tại). Nếu khai báo phần schema này khác với user hiện tại thì user hiện tại này phải có quyền tạo thủ tục trong schema đó. Name: tên thủ tục, nằm ngay sau từ khóa procedure. Parameter: một danh sách các tham số dùng để gửi thông tin vào, ra khỏi thủ tục. Danh sách tham số này là tùy chọn. AUTHID clause: quyết định thủ tục sẽ được thực thi (1)dưới quyền của definer (người định nghĩa thủ tục hay còn gọi là owner – người sở hữu) hay (2)dưới quyền của current user (người dùng hiện tại). Trường hợp (1) được gọi là definer rights model, (2) được gọi là invoker rights model. Declarations: phần khai báo các định danh cục bộ cho thủ đục (khai báo biến). Nếu bạn không có bất kì khai báo nào thì phần này bỏ trống. Executable statements: Các phát biểu (các lệnh) mà thủ thục sẽ thực thi. Đây là phần thực thi của thủ tục, bắt buộc phải có ít nhất một lệnh trong phần này. Exception handlers: phần xử lý ngoại lệ (xử lý khi lỗi phát sinh). Nếu bạn không tường minh xử lý các ngoại lệ, bạn có thể bỏ qua từ khóa EXCEPTION. Ở mức độ tìm hiểu cách hoạt động của thủ tục thì ta sẽ chỉ chú ý đến phần tên thủ tục, danh sách tham số, khai báo và phần mã lệnh thực thị. Các phần khác thuộc vấn đề khác nên không xét ở đây. Ví dụ một thủ tục đơn giản: PROCEDURE Hello IS BEGIN SET SERVEROUTPUT ON; DBMS_OUTPUT.PUT_LINE(‘Hello world!’); END Hello; Thủ tục trên xuất ra dòng chữ Hello world!. Bạn chạy nó trong SQL*Plus. Tên thủ tục sau từ khóa END có thể bỏ qua, tuy nhiên thêm nó vào là một thói quen tốt giúp mã dễ đọc. Gọi một thủ tục Một thủ tục được gọi như một lệnh PL/SQL thông thường. Một lời gọi tới 1 thủ tục phải kết thúc bởi dấu chấm phẩy. Khối lệnh sau gọi thủ tục Hello: BEGIN /* Các lệnh khác … */ Hello; /* Các lệnh khác … */ END; Bạn cũng có thể gọi như sau: BEGIN /* … */ Hello(); /* … */ END; Lệnh RETURN Lệnh RETURN bắt buộc có trong 1 hàm bởi vì 1 hàm yêu cầu trả về 1 giá trị từ hàm đó ( nếu không sẽ gây ra 1 exception, hàm sẽ đề cập sau). PL/SQL cho phép ta dùng từ khóa RETURN cả trong thủ tục, và lệnh này không trả về bất cứ giá trị nào. Khi RETURN được gọi nó kết thúc thực thi của thủ tục, có thể có nhiều lệnh RETURN trong thủ tục. Bạn không thấy việc dùng RETURN nhiều trong thủ tục vì 1 lí do chính đáng. Dùng RETURN trong thủ tục thường dẫn tới mã nguồn mất tính cấu trúc bởi vì có ít nhất 2 đường thoát khỏi thủ tục, làm cho dòng thực thi trở nên khó hiểu và khó bảo trì. Hàm Cũng như thủ tục, hàm là khái niệm quan trọng trong xây dựng mã nguồn có tính module. Một hàm là một module sẽ trả về 1 giá trị. Không giống 1 lời gọi thủ tục là một lệnh thực thi độc lập, một lời gọi tới 1 hàm chỉ có thể tồn tại như 1 phần của 1 lệnh, ví dụ nó là 1 thành phần trong 1 biểu thức gán hay là tham số cho 1 thủ tục hay hàm khác… Nghĩa là bạn không thể gọi nó khơi khơi 1 mình như với gọi 1 thủ tục bời vì lí do nó có dữ liệu trả về và phải có 1 đích nhận dữ liệu trả về đó. Bởi vì một hàm trả về 1 giá trị, nên nó được bảo là có kiểu dữ liệu. Một hàm có thể được sử dụng thay cho 1 biểu thức trong 1 lệnh PL/SQL có cùng kiểu dữ liệu với hàm đó. Cấu trúc của 1 hàm khá giống như cấu trúc của một thủ tục, ngoại trừ có thêm lệnh RETURN. Cấu trúc tổng quát nhất như sau: FUNCTION [schema.]name [( parameter [, parameter ...] ) ] RETURN return_datatype [AUTHID DEFINER | CURRENT_USER] [DETERMINISTIC] [PARALLEL ENABLE ...] [PIPELINED] IS [declaration statements] BEGIN executable statements [EXCEPTION exception handler statements] END [ name ]; Trong đó các thành phần được mô tả như sau: schema: tên schema sở hữu hàm này, thông số này là tùy chọn. Mặc định nó là current user. Nếu khai báo phần schema này khác với user hiện tại thì user hiện tại này phải có quyền tạo hàm trong schema đó. name: Tên của hàm. parameters: Một danh sách tùy chọn các tham số truyền thông tin vào,ra khỏi hàm. return_datatype: Kiểu dữ liệu của giá trị được trả về từ hàm. Phần khai báo này là bắt buộc. AUTHID clause: Quyết định hàm sẽ được thi hành dưới quyền của definer (owner) của hàm hay được thi hành dưới quyền của user hiện tại. DETERMINISTIC clause: Một chỉ dẫn tối ưu, yêu cầu hệ thống sử dụng bản copy được lưu lại của giá trị kết quả trả về nếu giá trị này hiện diện thay vì phải gọi lại hàm 1 lần nữa. PARALLEL_ENDBALE clause: Một chỉ dẫn làm cho hàm có thể được thực thi song song khi được gọi bên trong 1 lệnh SELECT. PIPELINED clause: xác định các kết quả của table-function nên được trả ra lặp đi lặp lại thông qua lệnh PIPE ROW. declaration statements: phần khai báo biến của hàm. Nếu bạn không có bất cứ khai báo nào thì có thể bỏ trống. executable statements: các lệnh được thực thi bởi hàm. Phần này phải có ít nhất 1 lệnh, khôn được bỏ trống. exception handler statements: các phần xử lí ngoại lệ cho hàm, có thể bỏ trống. Ở mức độ tìm hiểu cách hoạt động của hàm thì ta sẽ chỉ chú ý đến phần tên hàm, danh sách tham số, khai báo và phần mã lệnh thực thị. Các phần khác thuộc vấn đề khác nên không xét ở đây. Xét ví dụ đơn giản sau: FUNCTION TinhTong2So(a IN NUMBER, b IN NUMBER RETURN NUMBER IS tong NUMBER; BEGIN tong:= a + b; RETURN tong; END TinhTong2So; Ví dụ trên nhận 2 tham số đầu vào kiểu NUMBER có nhiệm vụ tính tổng 2 số và trả về tổng này. Các biến tham số a,b và biến tong là các biến cục bộ trong hàm. Hàm trong PL/SQL khá giống với hàm trong các ngôn ngữ lập trình như C/C++. Kiểu dữ liệu trả về Một hàm PL/SQL có thể trả về hầu như bất kì kiểu dữ liệu nào của PL/SQL, từ vô hướng tới cấu trúc phức tạp như collections, objects, cursors và LOBs. Bạn không trả về 1 exception thông qua 1 hàm vì trong PL/SQL exception không có kiểu dữ liệu. Gọi một hàm Như đã đề cập, một hàm được gọi như là 1 phần trong một biểu thức vì hàm trả về giá trị. Nó không thể được gọi độc lập. Ví dụ: Gán giá trị mặc định cho biến bằng 1 lời gọi hàm: DECLARE var1 VARCHAR2(100) := SomeFunction(); Nếu một hàm không yêu cầu tham số thì để trống dấu đóng mở ngoặc hoặc bỏ luôn cũng được. Một hàm bắt buộc phải có giá trị trả về. Mặc dù không gây lỗi biên dịch nhưng sẽ gây ra một lỗi trong thời gian thực thi nếu không có lệnh RETURN 1 giá trị: ORA-06503: PL/SQL: Function returned without value Lệnh RETURN Một hàm bắt buộc phải có ít nhất một lệnh RETURN. Nó có thể có nhiều hơn 1 lệnh RETURN để trả về giá trị, khi bắt gặp 1 lệnh RETURN hàm kết thúc ngay lập tức. Từ khóa RETURN trong phần đầu khai báo hàm chỉ ra kiểu dữ liệu trả về, các lệnh RETURN trong thân hàm phải trả về kiểu dữ liệu tương thích với kiểu được khai báo. Lệnh RETURN có thể trả về bất cứ biểu thức nào có kiểu dữ liệu tương thích với kiểu được khai báo. Biểu thức này có thể bao gồm lời gọi tới hàm khác, các tính toán phức tạp; có thể có chuyển đổi kiểu nếu biểu thức trả về tương thích về kiểu với kiểu trả về. Ví dụ: RETURN 'buy me lunch'; RETURN POWER (max_salary, 5); Bạn còn có thể trả về các cấu trúc dữ liệu phức tạp như các thể hiện của kiểu đối tượng (object type instance), kiểu tập hợp (collection), và bản ghi (record). Một biểu thức trong khai báo RETURN sẽ được tính giá trị khi lệnh này được thi hành và trả về cho nơi gọi hàm. Tham số cho thủ tục và hàm Từ đầu giờ ta đã làm quen với thủ tục và hàm nhưng chưa xét chi tiết tới một khái niệm quan trọng là tham số của chúng. Phần này sẽ trình bày tham số của hàm và thủ tục. Khái niệm thao số rất giống với ở các ngôn ngữ lập trình khác như C/C++. Thủ tục và hàm đều có thể sử dụng các tham số để truyền thông tin đi và về giữa các module hay từ khối lệnh gọi. Khi xem xét tham số ta quan tâm: Số lượng tham số: quá nhiều tham số sẽ làm hạn chế khả năng dùng lại chương trình; với quá nhiều tham số, sẽ không ai muốn dùng chương trình của bạn vì nó quá lằng nhằng phức tạp. Tuy con số số tham số thì được quyết định bởi yêu cầu, nhưng có nhiều cách khác nhau để định nghĩa các tham số này (như là bọc nhiều tham số lại trong 1 bản ghi (record) đơn). Mode của tham số: bạn nên dùng kiểu tham số read-only, write-only, hay read-write ? (đề cập sau) Tên của các tham số: tên các tham số nên phản ánh đúng mục đích sử dụng tham số đó. Giá trị mặc định cho các tham số: Khi nào cần giá trị mặc định. PL/SQL đưa ra nhiều đặc điểm giúp bạn thiết lập tham số hiệu quả. Phần này sẽ bao quát tất cả các thành phần của việc định nghĩa tham số. Định nghĩa tham số Các tham số hình thức được định nghĩa trong phần danh sách tham số của hàm/thủ tục(người ta cũng gọi hàm/thủ tục là chương trình). Một định nghĩa tham số rất giống với cú pháp định nghĩa biến trong phần khai báo của khối PL/SQL. Có 2 điểm phân biệt: đầu tiên, một tham số có kiểu (mode)trong khi khai báo biến thì không; và thứ 2, một khai báo tham số thì không bị ràng buộc miền dữ liệu. Ví dụ khi khai báo biến có kiểu dữ liệu là chuỗi gồm tối đa 60 kí tự: DECLARE name VARCHAR2(60); Trong khi đó khai báo tham số cho 1 module (được hiểu là hàm hay thủ tục) thì phần giới hạn này bị bỏ đi: PROCEDURE display_name (name IN VARCHAR2) /*Tham số đặt trong dấu ngoặc đơn */ IS ... Ví dụ trên thủ tục nhận tham số là name có mode là IN và kiểu dữ liệu là VARCHAR2(không cần chỉ ra số lượng kí tự). Tham số hình thức và tham số thật (còn gọi là đối số truyền vào) Chúng ta cần phân biệt giữa 2 loại tham số: tham số thật hay còn gọi là đối số và tham số hình thức. Tham số hình thức là các tên tham số mà ta khai báo trong phần danh sách tham số của module. Tham số thật là các giá trị hay biểu thức truyền vào module khi ta thực hiện lời gọi module. Khái niệm này cũng giống trong C/C++. Ta kiểm tra ví dụ sau: FUNCTION tot_sales (company_id_in IN company.company_id%TYPE, status_in IN order.status_code%TYPE := NULL) RETURN std_types.dollar_amount; Ở ví dụ trên, tham số hình thức là: company_id_in và status_in. Các tham số hình thức này không tồn tại bên ngoài hàm, được coi là biến cục bộ trong hàm. Khi gọi hàm: new_sales := tot_sales (company_id, 'N'); Thì company_id và ‘N’ là các tham số (giá trị) thật truyền vào (đối số). Các tham số thật (đối số) có thể là các biểu thức bất kì và chúng được tính toán khi thực hiện gọi hàm. Kiểu dữ liệu của tham số thật (mang giá trị truyền vào) và tham số hình thức tương ứng phải tương thích. Và có thể có chuyển đổi kiểu trong vài trường hợp, PL/SQL làm điều này cho bạn. Tuy nhiên nên tránh việc chuyển kiểu ngầm này. Hãy sử dụng một hàm chuyển kiểu tường minh như TO_CHAR hay TO_DATE để cho biết chính xác kiểu dữ liệu nào bạn sẽ truyền vào module. Các kiểu (mode) tham số Kiểu tham số cho biết cách thức các tham số được sử dụng, có 3 kiểu: Kiểu Mô tả Sử dụng IN Read-only Giá trị truyền vào cho tham số hình thức có thể được dùng đọc nhưng không được thay đổi (chỉ đọc). OUT Write-only Module có thể ghi giá trị vào tham số nhưng không được đọc giá trị này.(chỉ ghi) IN OUT Read/write Cả đọc và ghi đều được. Khai báo kiểu truyền tham số ngay sau tên tham số hình thức và trước kiểu dữ liệu: PROCEDURE predict_activity (last_date_in IN DATE, task_desc_inout IN OUT VARCHAR2, next_date_out OUT DATE) Trong ví dụ trên tham số last_date_in, task_desc_inout, next_date_out sẽ trở thành các biến cục bộ trong thủ tục predict_activity. Last_date_in là biến chỉ được đọc giá trị, không cho phép thay đổi giá trị này, task_desc_inout là biến có thể đọc hay sửa giá trị tùy ý, next_date_out là biến chỉ có thể ghi giá trị để truyền ra ngoài, nó không dùng để đọc. IN Một tham số kiểu IN cho phép chúng ta truyền các giá trị vào trong module nhưng không cho thay đổi giá trị này. Nói cách khác mục đích của tham số IN giống như một biến hằng số, giá trị này không thể thay đổi bên trong module. Bạn không thể gán giá trị cho tham số IN hay sửa đổi giá trị của nó. IN là kiểu truyền tham số mặc định, nếu bạn không chỉ ra cụ thể kiểu truyền nào thì mặc định sẽ là kiểu IN, tuy nhiên bạn nên chỉ tường minh kiểu truyền để làm bật nên ý đồ sử dụng của mình. Các tham số IN có thể nhận giá trị mặc định ( phần giá trị mặc định sẽ được nói sau). Giá trị thực truyền vào cho tham số IN có thể là biến, hằng, một literal Một số nguyên 12 là một literal, một chuỗi ‘abcd’ là một literal… Literal là một giá trị cụ thể. , hoặc một biểu thức phức tạp. Ví dụ các lời gọi hợp lệ cho display_title: DECLARE happy_title CONSTANT VARCHAR2(30) := 'HAPPY BIRTHDAY'; changing_title VARCHAR2(30) := 'Happy Anniversary'; spc VARCHAR2(1) := CHR(32) -- ASCII code for a single space; BEGIN display_title ('Happy Birthday'); -- a literal display_title (happy_title); -- a constant changing_title := happy_title; display_title (changing_title); -- a variable display_title ('Happy' || spc || 'Birthday'); -- an expression display_title (INITCAP (happy_title)); -- another expression END; OUT Một tham số OUT ngược lại với tham số IN. Sử dụng tham số OUT để gửi giá trị ngược lại nơi gọi hàm/ thủ tục. Một tham số OUT giống như một giá trị RETURN trong một hàm, nhưng nó lại xuất hiện trong danh sách tham số, bạn có thể có bao nhiêu tham số OUT thì tùy. Bên trong chương trình, tham số OUT hành động như một biến không được khởi tạo giá trị ban đầu, thực sự tham số OUT không có giá trị cho tới khi chương trình kết thúc thành công (trừ trường hợp bạn yêu cầu sử dụng chỉ dẫn NOCOPY, khái niệm này sẽ trình bày sau). Trong suốt quá trình thực thi bạn có thể thực hiện nhiều lần gán giá trị cho tham số OUT, khi hàm/ thủ tục kết thúc thành công giá trị này sẽ được gửi lại vào tham số thật (đối số truyền vào). Một vài nguyên tắc: Bạn không thể bạn không thể dùng giá trị trong tham số OUT để gán giá trị cho biến khác. Bạn không thể cung cấp giá trị mặc định cho tham số OUT, bạn chỉ có thể gán giá trị cho nó bên trong hàm/thủ tục. Một giá trị gán cho tham số OUT bị thu hồi khi một lỗi xảy ra trong chương trình, nó chỉ hiện diện khi chương trình thực hiện thành công. Và dù chương trình của bạn có phần xử lí ngoại lệ thì giá trị tham số OUT vẫn bị thu hồi. Tức không được truyền ngược ra ngoài cho đối số. Một tham số thật sự tương ứng với một tham số hình thức OUT bắt buộc phải là một biến. Nó không thể là một hằng, literal hay biểu thức bởi vì PL/SQL sẽ dùng biến này chứa giá trị đưa ra ngoài của tham số OUT. IN OUT Với một tham số kiểu IN OUT, bạn có thể gửi giá trị vào trong chương trình và gửi lại giá trị ra ngoài. Tức nó có thể được đọc và được sửa đổi tùy ý bên trong module (hàm/ thủ tục). Giống như tham số OUT, tham số IN OUT có 2 giới hạn sau: Một tham số IN OUT không thể có giá trị mặc định. Tham số thật tương ứng phải là một biến. Mỗi kiểu truyền tùy theo mục đích sử dụng mà bạn phải lựa chọn cho hợp lí. Sự tương ứng giữa tham số thật(đối số) và tham số hình thức Làm thế nào để chương trình biết được tham số thật truyền vào ứng với tham số hình thức nào? Có 2 cách giúp PL/SQL xác định được điều này: Xác định bằng vị trí : các tham số hình thức được tương ứng với tham số thật gửi vào theo thứ tự của chúng. Xác định bằng cách chỉ rõ tên. Xác định sự tương ứng giữa đối số gửi vào và tham số nhận bằng vị trí Sự tương ứng này rất dễ hiểu: thứ tự các đối số gửi vào sẽ có thứ tự đúng với thứ tự các tham số hình thức được khai báo. PL/SQL kết hợp tham số hình thức thứ n với tham số thật thứ n được gửi vào, từng cặp từng cặp như vậy. Ví dụ: new_sales := tot_sales (order.company_id, 'N');/*Truyền tham số thật*/ FUNCTION tot_sales (company_id_in IN company.company_id%TYPE, status_in IN order.status_code%TYPE := NULL)/*Danh sách các tham số hình thức*/ … Ở ví dụ trên oder.compay_id ứng với company_id, ‘N’ ứng với status_in (theo thứ tự). Tuy nhiên status_in có giá trị mặc định nên bạn có thể bỏ qua đối số truyền cho nó, thứ tự các đối số còn lại giữ nguyên. Xác định bằng cách chỉ rõ tên tham số hình thức ứng với đối số nào Cú pháp như sau: formal_parameter_name => argument_value Cú pháp chỉ rõ tên giúp bạn tường minh gắn kết tham số hình thức với tham số thật (đối số truyền vào) ngay trong lời gọi hàm/ thủ tục. Bởi vì bạn đã cung cấp tên của tham số hình thức một cách tường minh, PL/SQL sẽ không cần dựa vào thứ tự của các tham số để nối kết cho phù hợp. Vì vậy, nếu ban dùng cách đặt tên này, bạn không cần phải liệt kê danh sách tham số trong lời gọi hàm đúng với thứ tự của danh sách tham số hình thức trong khai báo hàm/thủ tục. Bạn có thể gọi hàm tot_sales theo thứ tự như sau đểu được cả: new_sales := tot_sales (company_id_in => company_id, status_in =>'N'); /* tham số hình thức company_id_in sẽ nhận đối số truyền vào là company_id và status_in sẽ nhận ‘N’ */ new_sales := tot_sales (status_in =>'N', company_id_in => company_id); Một điều thú vị nữa là bạn có thể trộn lẫn cả 2 kiểu theo thứ tự và theo cách chỉ tên tường minh: new_sales := tot_sales (company_id, status_in =>'N'); Nếu bạn dùng cách trộn lẫn này, tuy nhiên, bạn phải liệt kê các tham số có thứ tự đứng trước tất cả các tham số đặt tên khác như ví dụ trên. Chỉ dẫn NOCOPY Có 2 kiểu truyền các giá trị tham số dành cho OUT và IN OUT: tham chiếu và tham trị. Hai khái niệm này rất giống với ngôn ngữ C/C++. Truyền tham chiếu: Khi một tham số thật được truyền tham chiếu, có nghĩa là một con trỏ tới tham số thật được truyền vào. Tham số hình thức nhận con trỏ này và chúng cùng tham chiếu/ trỏ tới cùng một vùng nhớ trong bộ nhớ lưu giá trị của tham số. Tham trị: Tham số hình thức sẽ sao chép giá trị của tham số thật truyền vào. Nếu chương trình kết thúc mà không có lỗi thì giá trị này được chép lại vào tham số thật, nếu có lỗi thì các thay đổi sẽ không bao giờ được sao chép lại ra ngoài cho tham số thật. Mặc định thì luật này được áp dụng như sau: Mode Kiểu truyền OUT Tham trị IN OUT Tham trị Việc đưa ra chỉ dẫn NOCOPY để giảm chi phí sao chép dữ liệu khi tham số truyền vào có cấu trúc lớn như 1 collection chẳng hạn. Vậy từ nay cách khai báo tham số có thể có cấu trúc như sau: parameter_name [ IN | IN OUT | OUT | IN OUT NOCOPY | OUT NOCOPY ] parameter_datatype Bạn chỉ có thể thêm NOCOPY với kiểu OUT hay IN OUT. Kiểu IN thì luôn luôn là truyền theo tham chiếu. Lưu ý: Tham số thật (đối số truyền vào) tương ứng với 1 tham số hình thức kiểu OUT có dùng chỉ dẫn NOCOPY sẽ bị gán giá trị thành NULL bất cứ khi nào module chứa tham số OUT đó được gọi. NOCOPY là một chỉ dẫn (hint) không phải là 1 lệnh. Trình biên dịch sẽ bỏ qua tác dụng của NOCOPY nếu nó không thể đáp ứng. Việc này thực hiện âm thầm mà ta không biết vì thế phải dự trù các tình huống NOCOPY bị trình biên dịch bỏ qua. Phần này được đề cập trong mục kế tiếp. Các hạn chế của NOCOPY Có một số tình huống mà dẫn đến trình dịch bỏ qua chỉ dẫn NOCOPY và sử dụng phương thức truyền mặc định là tham trị . Các tình huống này bao gồm: Tham số thật (đối số) là một phần tử của một mảng kết hợp (associative array) Bạn có thể dùng NOCOPY để truyền toàn bộ 1 mảng kết hợp chứ không được truyền một phần tử riêng lẻ trong mảng đó. Khi có ràng buộc toàn vẹn nào đó áp dụng lên tham số thật Các ràng buộc bao gồm: khai báo phạm vi giá trị (scale) cho một biến kiểu số, ràng buộc NOT NULL. Tuy nhiên bạn có thể truyền vào 1 biến kiểu chuỗi cho dù nó bị ràng buộc bởi kích thước (số kí tự). Khi đối số và tham số hình thức là các bản ghi (record). Một hoặc cả hai bản ghi được khai báo dùng %ROWTYPE hay %TYPE, và các ràng buộc trên các trường trong 2 bản ghi này khác nhau sẽ dẫn đến NOCOPY bị bỏ qua. Khi truyền đối số PL/SQL phải thực hiện một chuyển đổi kiểu ngầm định Khi phải chuyển đổi ngầm định như vậy thì NOCOPY sẽ bị bỏ qua. Thủ tục dùng chỉ dẫn NOCOPY là thủ tục ngoại (external procedure) Thủ tục ngoại là thủ tục nằm trong file DLL ( môi trường Windows) hay thư viện chia sẻ (Shared library trên Unix). Trong trường hợp này PL/SQL luôn truyền theo tham trị. Giá trị mặc định Xét ví dụ sau: PROCEDURE ABC (a IN NUMBER := 2, b OUT VARCHAR2 := NULL) Tham số a có giá trị mặc định là 2, b có giá trị mặc định là NULL. Khi gọi thủ tục ABC nếu bạn không truyền giá trị đối số cho các tham số này thì chúng sẽ lấy giá trị mặc định. Nếu bạn truyền tường minh thì nó sẽ lấy giá trị bạn truyền vào. Cú pháp khai báo bạn dễ dàng đoán ra. Bạn thêm vào giá trị mặc định như một phép gán đằng sau kiểu dữ liệu. Tuy nhiên còn 1 cách khác là dùng từ khóa DEFAULT và giá trị mặc định đi theo sau nó: PROCEDURE ABC (a IN NUMBER DEFAULT 2, b OUT VARCHAR2 DEFAULT NULL) Bây giờ bạn có thể gọi thủ tục đó như sau: ABC(33); //Gọi thủ tục ABC chỉ truyền giá trị cho tham số a ABC; //Lấy toàn bộ giá trị mặc định, không truyền tham số nào hết, trường hợp này bạn không thêm dấu (). Vậy nếu tôi muốn a lấy giá trị mặc định nhưng b thì tôi truyền giá trị vào thì phải làm sao. Bạn phải dùng cách chỉ tên tường minh cho các tham số nào cần truyền giá trị vào. ABC(b => ‘I love you’); //Gọi thủ tục ABC truyền giá trị cho tham số hình thức b. Nếu hàm ABC sửa lại như sau: PROCEDURE ABC (a IN NUMBER DEFAULT 2, x IN NUMBER, b OUT VARCHAR2 DEFAULT NULL) Bạn muốn lấy giá trị mặc định của a và b thì chỉ có cách là gọi hàm bằng cách chỉ tên. ABC(x => 555); //a, b sẽ nhận giá trị mặc định Lưu ý chỉ có tham số IN được quyền có giá trị mặc định, OUT và IN OUT thì không.