Chương 9 Ngôn ngữ thủ tục - PL/SQL

Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 1 Chương 9 NGÔN NGỮ THỦ TỤC - PL/SQL - Lý thuyết: 6 tiết - Thực hành: 6 tiết Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 2 9.1 Giới thiệu PL/SQL 9.2 Con trỏ (Cursor) 9.3 Thủ tục và hàm 9.4 Quản lý đối tượng lớn NGÔN NGỮ THỦ TỤC – PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 3 - Mở rộng của SQL - Mã PL/SQL lưu trữ trực tiếp trong csdl - Giao tiếp với csdl  cho phép tích hợp lệnh SQL và các ngôn ngữ lập trình.  sử dụng cả trong cơ sở dữ liệu và lập trình ứng dụng.  dùng để kết hợp các lệnh xử lý chuyển tác.  hỗ trợ nhiều thủ tục hơn SQL.  đơn vị mã lệnh PL/SQL là dạng khối (Begin… End). Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 4 Khai báo biến Identifier [CONSTANT] datatype [NOT NULL] [:=| DEFAULT expr] Ví dụ: V_Hiredate DATE; V_DeptNo NUMBER(3) NOT NULL := 10; V_Loc VARCHAR2(13) := ‘Atlanta’; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 5 - Khai báo khối [DECLARE Khai báo biến, con trỏ,…] BEGIN - Lệnh SQL; - Lệnh PL/SQL; [EXCEPTION Xử lý ngoại lệ xảy ra;] END; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 6 DECLARE v VARCHAR2(5); BEGIN SELECT Ename INTO v FROM Emp; EXCEPTION WHEN others THEN Message(SQLERRM); END; Giới thiệu PL/SQL Ví dụ: Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 7 Ví dụ: Lấy dữ liệu cột DeptId và cột Loc của phòng SALES trong bảng Dept đưa vào hai biến v_deptno và v_loc. Nếu có lỗi xuất hiện thì xuất dòng thông báo mã lỗi ra màn hình. Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 8 DECLARE v_deptno NUMBER(2); v_loc VARCHAR2(15); BEGIN SELECT DeptId, Loc INTO v_deptno, v_loc FROM Dept WHERE Dname = 'SALES'; EXCEPTION WHEN others THEN Message(SQLERRM); END; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 9 Thuộc tính %Type - %Type sử dụng khi lưu trư˜ giá trị nhận được từ một bảng - đặt %Type ngay sau tên bảng và tên cột identifier Table.column%Type; Ví dụ: DECLARE v_deptno Dept.DeptNo%Type; v_loc VARCHAR2(15); v_name Dept.Dname%Type; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 10 - Một số chú ý  PL/SQL không hỗ trợ các kiểu dữ liệu mảng.  Biến phải được khái báo trước khi tham chiếu đến.  Kiểu dữ liệu trong phần khai báo có thể có hoặc không.  Trong một khối lệnh các biến không được trùng tên.  Tên biến không được trùng với tên cột của bảng dư˜ liệu sử dụng trong khối.  Khởi tạo biến phải chỉ rõ như NOT NULL, CONSTANT.  Khai báo mỗi biến trên 1 hàng.  Khởi tạo giá trị cho biến bằng cách sử dụng phép gán (:=) hoặc từ khóa DEFAULT Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 11 Các kiểu dữ liệu thông dụng Giới thiệu PL/SQL CHAR [(max_lenght)] VARCHAR2 (max_lenght) LONG LONG RAW NUMBER[(precision, scale)] BINARY_INTEGER PLS_INTEGER BOOLEAN DATE TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 12 Khối lồng nhau và phạm vi biến Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 13 - Các phép toán  Logic  Toán học  Ghép nối (Concatenation)  Số mũ (**)  Dùng dấu ngoặc đơn để điều khiển thứ tự ưu tiên. Ví dụ: Tăng biến đếm cho vòng lặp. v_count := v_count + 1; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 14 - Câu lệnh SQL trong PL/SQL Một số điểm cần lưu ý  Cần phần biệt từ khóa END của khối lệnh và END của câu lệnh điều khiển chuyển tác.  PL/SQL không hô˜ trợ trực tiếp các câu lệnh ngôn ngư˜ định nghĩa dư˜ liệu (DDL) như CREATE TABLE, ALTER TABLE, DROP TABLE.  PL/SQL không hô˜ trợ các câu lệnh ngôn ngư˜ điều khiển dư˜ liệu (DCL) như GRANT, REVOKE. Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 15 - Câu lệnh SELECT trong PL/SQL SELECT select_list INTO {variable [, variable]…| record_name} FROM table [WHERE condition]; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 16 - Câu lệnh SELECT trong PL/SQL Ví dụ: Lấy dư˜ liệu cột DeptId và cột Loc của phòng SALES trong bảng Dept. DECLARE v_deptno NUMBER(4); v_loc_id Dept.loc_id%Type; BEGIN SELECT Deptid, Locid INTO v_deptno, v_loc_id FROMDept WHERE Dname=‘Sales’; … END; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 17 - Chèn dữ liệu Ví dụ: Thêm một nhân viên vào trong cơ sở dữ liệu. … BEGIN INSERT INTO Emp (Empid, Fname, Email, Hireday, Jobid, Salary) VALUES (emp_seq.NEXTVAL, ‘Ruth’, ‘Roes@dsc.com’, sysdate, ‘SALES’, 4000); END; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 18 - Cập nhật dữ liệu Ví dụ: Tăng lương thêm $100 cho những nhân viên có mã công việc là ST_CLERKS. DECLARE v_sal_increase Emp.Salary%TYPE := 100; BEGIN UPDATE Emp SET Salary = Salary + v_sal_increase WHERE Jobid = ‘ST_CLERK’; END; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 19 - Xóa dư˜ liệu Ví dụ: Xóa những hàng có Job_id là ST_CLERKS. DECLARE v_name Emp.Job_id%TYPE := ‘ST_CLERKS’; BEGIN DELETE FROM Emp WHERE Job_id = v_name; END; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 20 - Câu lệnh kết hợp Ví dụ: Chèn hoặc cập nhật dữ liệu trong bảng copy_emp phù hợp với bảng emp. DECLARE v_empno emp.Emp_id%TYPE:=100; BEGIN MERGE INTO copy_emp c USING Emp e ON (c.Emp_id=v_empno) WHEN MATCHED THEN UPDATE SET c.Emp_id=e.Emp_id, c.Ename=e.Ename c.Job_id=e.Job_id, c.salary=e.salary WHEN NOT MATCHED THEN INSERT VALUES(e.Empid, e.fname, e.Hireday, e.Jobid, e.salary, e.manageid); END; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 21 - Cấu trúc điều khiển (Tương tự C++) Ví dụ: Gán một trong các chuỗi ‘Excellent’, ‘Very good’, ‘Good’ và ‘No such grade’ cho biến v_app dựa vào biến v_grade. DECLARE v_grade CHAR(1):=UPPER(&grade); v_app VARCHAR2(20); BEGIN v_app:= CASE v_grade WHEN ‘A’ THEN ‘Excellent’ WHEN ‘B’ THEN ‘Very good’ WHEN ‘C’ THEN ‘Good’ ELSE ‘No such grade’ END; END; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 22 - Lệnh lặp không định trước LOOP statements; EXIT WHEN condition; END LOOP; - Lệnh lặp WHILE WHILE condition LOOP statements; END LOOP; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 23 - Lệnh lặp FOR FOR counter IN [REVERSE] from … to LOOP statements; END LOOP; Ví dụ: Chèn thêm 3 hàng mới vào bảng Locations Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 24 - Lệnh lặp FOR FOR counter IN [REVERSE] from … to LOOP statements; END LOOP; Ví dụ: Chèn thêm 3 hàng mới vào bảng Locations Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 25 DECLARE v_country_id locations.Country_id%TYPE:=‘CA’; v_locatin_id locations.Loc_id%TYPE; v_city locations.City%TYPE:=‘Montreal’; BEGIN SELECT MAX(Loc_id) INTO v_location_id FROM locations WHERE Country_id=v_contry_id; FOR i IN 1..3 LOOP INSERT INTO locations(Loc_id, City, Country_id) VALUES((vlocation_id + i),v_city, v_country_id); v_counter:=v_counter+1; END LOOP; END; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 26 - Kiểu dữ liệu hợp Kiểu dữ liệu Table Kiểu dữ liệu Record Thuộc tính %ROWTYPE Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 27 - Kiểu dữ liệu Table TYPE typename IS TABLE OF {column_type| variable%Type | table.column%Type} [NOT NULL] | table.%RowType [INDEX BY BINARY_INTEGER]; variable typename; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 28 - Kiểu dữ liệu Table Ví dụ Khai báo kiểu dữ liệu TABLE có tên t_name và t_date, sau đó khai báo hai biến v_name và v_date DECLARE TYPE t_name IS TABLE OF Emp.Fname%TYPE INDEX BY BINARY_INTEGER; v_name t_Name; BEGIN v_name(1):=’SALES’; END; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 29 - Kiểu dữ liệu Table Ví dụ Khai báo kiểu dữ liệu TABLE có tên t_name và t_date, sau đó khai báo hai biến v_name và v_date DECLARE TYPE t_date IS TABLE OF DATE (Number) INDEX BY BINARY_INTEGER; v_date t_date; BEGIN v_date(8):=SYSDATE+7; END; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 30 - Kiểu dữ liệu Record TYPE type_name IS RECORD (field1 field_type| variable%Type | table.column%Type| table%RowType [NOT NULL{:=|DEFAULT} exrp], … (fieldn field_type| variable%Type | table.column%Type| table%RowType [NOT NULL{:=|DEFAULT} exrp]; variable typename_Record; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 31 - Kiểu dữ liệu Record Ví dụ: Khai báo kiểu dữ liệu RECORD tên t_emp và khai báo biến v_emp kiểu t_emp. TYPE t_Emp IS RECORD (empno number(4) not null, ename char(10), job char(9), deptno number(2) not null); v_Emp t_Emp; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 32 - Thuộc tính %ROWTYPE Khai báo kiểu dữ liệu RECORD dựa trên một tập hợp các cột của các bảng hay khung nhìn variablename reference%ROWTYPE; Ví dụ: v_dept dept%ROWTYPE; Tham chiếu đến biến: Variablename.field_name; Ví dụ: V_dept.Dname:=‘CAMARON’; Giới thiệu PL/SQL Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 33 - Tổng quan về con trỏ (Cursor) - Nâng cao về con trỏ Con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 34 - Kiểu biến có cấu trúc chứa một lệnh SQL được xử lý - Mỗi dòng dữ liệu này được định vị bởi một con trỏ Con trỏ tường minh  Lệnh select được đặt tên  Thực hiện các thao tác xử lý dữ liệu quan trọng Con trỏ ngầm  địa chỉ không đặt tên của một lệnh SQL được xử lý bởi Oracle và cơ chế thực thi PL/SQL Tổng quan về con trỏ Khai báo -> mở con trỏ -> xử lý -> đóng con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 35 - Khai báo con trỏ CURSOR cursorname[variable_list] IS select_statement; Ví dụ: Khai báo biến con trỏ lưu trữ dữ liệu cột DeptId và Dname trong bảng Dept. DECLARE CURSOR c_Dept IS SELECT DeptId, Dname FROM dept BEGIN … Tổng quan về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 36 - Mở con trỏ OPEN cursorname | cursorname(variable_list); Ví dụ: Mở con trỏ c_dept. OPEN c_Dept; Lệnh OPEN thực hiện các tháo tác sau: 1. Cấp vùng nhớ động để chứa thông tin. 2. Phân tích câu lệnh select. 3. Chấp nhận các biến đầu vào. 4. Kích hoạt con trỏ. 5. Định vị con trỏ ngay dòng đầu tiên. Tổng quan về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 37 - Lấy dữ liệu FETCH CursorName INTO variable_list; Ví dụ: Đọc con trỏ c_dept và đưa dữ liệu vào biến v_dept. FETCH c_Dept INTO v_Dept; Quá trình thực hiện lệnh FETCH như sau: 1. Đọc dòng hiện tại và đưa dữ liệu vào các biến PL/SQL. 2. Dịch chuyển con trỏ đến dòng tiếp theo Tổng quan về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 38 - Đóng con trỏ CLOSE cursorName; Ví dụ: CLOSE c_Dept; - Một số thuộc tính của con trỏ  %isopen trả về giá trị True nếu con trỏ đang mở.  %notfound lệnh fetch hiện thời không trả về hàng nào.  %found lệnh fetch hiện thời trả về một hàng.  %rowcount số hàng đã được thực hiện bằng lệnh fetch. Tổng quan về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 39 Ví dụ: Dùng biến con trỏ cập nhật dữ liệu vào bảng Emp_ext. DECLARE CURSOR c_Emp IS SELECT * FROM emp WHERE Deptid = 10; v_Emp c_EMP%rowtype; BEGIN OPEN c_Emp; LOOP FETCH c_Emp INTO v_Emp; EXIT WHEN c_Emp%notfound; INSERT INTO Emp_ext(EmpNo, Ename, Job) VALUES(v_Emp.empid, v_Emp.fname, v_Emp.job); END LOOP; CLOSE c_Emp; END; Tổng quan về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 40 Ví dụ: Dùng biến con trỏ in DS NV thuộc phòng 10. DECLARE CURSOR c_Emp IS SELECT * FROM emp WHERE Deptid = 10; v_Emp c_EMP%rowtype; BEGIN OPEN c_Emp; LOOP FETCH c_Emp INTO v_Emp; EXIT WHEN c_Emp%notfound; DBMS_OUTPUT.OUTLINE(v_Emp.EmpId || “,” ||v_emp.Fname ||”,”||v_emp.Job); END LOOP; CLOSE c_Emp; END; Tổng quan về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 41 - Con trỏ với tham số  mềm dẻo hơn,  mở và đóng con trỏ nhiều lần trong một khối lệnh,  trả về một tập giá trị khác nhau tại mỗi thời điểm. CURSOR cursor_name [(parameter_name datatype,…)] IS select_statement; OPEN cursor_name (parameter_value,…); Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 42 Ví dụ: Khai báo con trỏ emp_cursor hai tham số p_deptno và p_job DECLARE CURSOR emp_cursor(p_deptno Number, p_job Varchar2) IS SELECT Empid, fname FROM emp WHERE Deptid = p_deptno AND Jobid=p_job; BEGIN OPEN emp_cursor (80, ‘SA_REP’); … CLOSE emp_cursor; OPEN emp_cursor (60, ‘IT_PROG’); … END; Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 43 - Mệnh đề FOR UPDATE khóa các dòng dữ liệu trước khi cập nhật hay xóa SELECT select_list FROM FOR UPDATE [OF column_reference] [NOWAIT]; NOWAIT Trả về lỗi nếu các dòng bị khóa bởi phiên làm việc khác Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 44 - Mệnh đề FOR UPDATE Ví dụ: Tìm những nhân viên làm việc trong phòng 80 và cập nhật lương của họ. DECLARE CURSOR emp_cursor IS SELECT Empid, fname, Dname FROM emp, dept WHERE emp.Deptid = dept.Deptid AND emp.Deptid = 80 FOR UPDATE OF Salary NOWAIT; … Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 45 - Mệnh đề WHERE CURRENT OF cho phép cập nhật hay xóa dòng hiện tại mà không cần đến thuộc tính ROWID WHERE CURRENT OF cursor; Ví dụ: Tăng 10% lương cho những người thuộc bộ phận có mã số 60 và mức lương thấp hơn 5000. Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 46 DECLARE CURSOR sal_cursor IS SELECT e.Deptid, Empid, fname, Salary FROM emp e, dept d WHERE d.Deptid = e.Deptid AND d.Deptid = 60 FOR UPDATE OF Salary NOWAIT; BEGIN FOR emp_record IN sal_cursor LOOP IF emp_record.Salary < 5000 THEN UPDATE emp SET Salary = emp_record.Salary * 1.1 WHERE CURRENT OF sal_cursor; END IF; END LOOP; END; Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 47 - Con trỏ với truy vấn con chứa câu lệnh truy vấn con trong câu lệnh truy vấn của con trỏ. Ví dụ DECLARE CURSOR my_cursor IS SELECT t1.Deptid, t1.Dname, t2.staff FROM dept t1, (SELECT Deptid, COUNT(*) AS staff) FROM emp GROUP BY Deptid) t2 WHERE t1.Deptid = t2.Deptid AND t2.staff >= 3; Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 48 Xử lý ngoại lệ (Exception - bẩy lỗi) một định danh xuất hiện khi có lỗi xảy ra hoặc do ta khai báo  định nghĩa trước (predefined exception),  người dùng định nghĩa (user-defined exception)  tiền xử lý (pragma exception) Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 49 - Bẫy ngoại lệ EXCEPTION WHEN exception1 [OR exception2…] THEN statements; [WHEN exception3 [OR exception4…] THEN statements; …] [WHEN OTHERS THEN statements; …] Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 50 - Bẫy ngoại lệ Ví dụ: Lấy dữ liệu cột fname trong bảng Emp đưa vào biến v_name. Nếu có lỗi xuất hiện thì xuất dòng thông báo lỗi ra màn hình. DECLARE v_name VARCHAR2(5); BEGIN SELECT Ename INTO v_name FROM Emp; EXCEPTION WHEN others THEN Message(SQLERRM); END; Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 51 - Một số nguyên tắc bẫy ngoại lệ  Từ khóa Exception bắt đầu phần bẫy và xử lý ngoại lệ.  Cho phép có nhiều bẫy ngoại lệ trong một chương trình.  Chỉ có một ngoại lệ được xử lí trước khi kết thúc khối lệnh.  Mệnh đề WHEN OTHERS nằm cuối cùng. Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 52 - Một số ngoại lệ thường gặp  NO_DATA_FOUND Không có dữ liệu.  TOO_MANY_ROWS Quá nhiều dòng.  INVALID_CURSOR Con trỏ không hợp lệ.  ZERO_DIVIDE Chia 0.  DUP_VAL_ON_INDEX Trùng lặp dữ liệu trường chỉ mục. Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 53 - Ngoại lệ định nghĩa trước  Oracle xây dựng sẵn  được sử dụng để xử lý những tình huống chung BEGIN … EXCEPTION WHEN NO_DATA_FOUND THEN statement1; WHEN TOO_MANY_ROWS THEN statement1; WHEN OTHERS THEN statement1; END; Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 54 - Hàm bẫy ngoại lệ  SQLCODE: Trả về giá trị số là mã lỗi.  SQLERRM: Trả về thông báo kết hợp với mã lỗi Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 55 DECLARE v_error_code NUMBER; v_error_message VARCHAR2(255); BEGIN … EXCEPTION … WHEN OTHERS THEN ROLLBACK; v_error_code : = SQLCODE; v_error_message := SQLERRM; INSERT INTO errors VALUES(v_error_code, v_error_message); END; Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 56 Ngoại lệ do người dùng định nghĩa Nâng cao về con trỏ Phần khai báo Declare Phần thực thi Raise Phần bẫy và xử lí ngoại lệ Reference - Phần khai báo exception EXCEPTION; - Phần thực thi RAISE exception; Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 57 Ví dụ: Định nghĩa một ngoại lệ để bắt lỗi không có dữ liệu. DECLARE e_invalid_dept EXCEPTION; BEGIN UPDATE Dept SET Dname=’SALES’ WHERE Deptid=30; IF SQL%NOTFOUND THEN RAISE e_invalid_dept; END IF; EXCEPTION WHEN e_invalid_dept THEN DBMS_OUTPUT.PUTLINE(‘No Department id.’); END; Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 58 - Ngoại lệ tiền xử lý  mã hoá một số ngoại lệ kết hợp với lỗi.  xử lý các lỗi chưa được xử lý rõ ràng.  Sử dụng từ khóa PRAGMA để khai báo. Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 59 DECLARE e_no_row EXCEPTION; e_intergrity EXCEPTION; PRAGMA EXCEPTION_INIT (e_intergrity, -2292); BEGIN UPDATE Dept SET Dname=’SALES’ WHERE Deptid=30; IF SQL%NOTFOUND THEN RAISE e_no_row; END IF; EXCEPTION WHEN e_intergrity THEN DBMS_OUTPUT.PUTLINE(‘No Intergrity.’); WHEN e_no_row THEN DBMS_OUTPUT.PUTLINE(‘No Department id.’); END; Nâng cao về con trỏ Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 60 - Tổng quan về chương trình con - Thủ tục - Hàm - Trigger - Gói Thủ tục và hàm Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 61 Một nhóm các lệnh thực hiện một chức năng nào đó  Nâng cao hiệu suất  Nâng cao khả năng bảo trì  Tăng tính bảo mật và toàn vẹn của dữ liệu Chương trình con Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 62  được lưu giữ ngay trong cơ sở dữ liệu như một đối tượng csdl (Store procedure)  được biên dịch thành dạng p-code khi lưu giữ Một thủ tục có:  tiêu đề,  phần khai báo,  phần thực thi  phần tùy chọn xử lý ngoại lệ. Thủ tục Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 63 - Tạo thủ tục CREATE [OR REPLACE] PROCEDURE proc_name [(argument1 [mode1] datatype1, argument2 [mode2] datatype2, ...)] IS | AS BEGIN PL/SQL Block; END Proc_name; Thủ tục Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 64 CREATE OR REPLACE PROCEDURE change_sal (p_Percentage IN number, p_Error OUT varchar2) IS v_user_exp Exception; BEGIN IF p_Percentage < 0 THEN RAISE v_user_exp; END IF; UPDATE Emp SET sal = sal*p_Percentage/100; EXCEPTION WHEN v_user_exp THEN p_Error := ‘Error: Percentage less than 0’; RETURN; WHEN others THEN p_Error := ‘Error: ‘ || SQLERRM; END change_sal; Thủ tục Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 65 Sau khi tạo một thủ tục, chúng ta có thể sử dụng thủ tục đó trong chương trình. Ví dụ: Gọi thực hiện thủ tục change_sal với p_Percentage=15. Change_sal(15, v_err); Thủ tục Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 66 - Huỷ bỏ thủ tục DROP PROCEDURE procedure_name; Ví dụ: DROP PROCEDURE change_sal; Thủ tục Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 67 trả về một giá trị ngay tại lời gọi của nó. - Tạo hàm CREATE [OR REPLACE] FUNCTION func_name [(argument1 [mode1] datatype1, argument2 [mode2] datatype2, ...)] RETURN datatype IS | AS BEGIN PL/SQL Block; END; Hàm Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 68 Ví dụ: CREATE FUNCTION get_sal (p_Empid IN number) RETURN Number IS BEGIN SELECT sal FROM emp WHERE emp_id = p_Empid; RETURN null; EXCEPTION WHEN others THEN RETURN ‘Lỗi: ‘ || SQLERRM; END; Hàm Chương 9. Ngôn ngữ thủ tục –PL/SQL 9 - 69 - Gọi hàm Ví dụ: DECLARE v_Sal number; BEGIN v