Bài giảng Hệ quản trị CSDL Oracle - Chương 2: Ngôn ngữ thủ tục PL/SQL

Su dung tham so out trong function'); 	y:=4; 	x:=HIEN_THI_NGAY1(y); 	dbms_output.put_line(y); 	dbms_output.put_line(x); end; * 7. Function – tham số IN OUT Ví dụ: thay ví dụ Function ở slide trước bằng một hàm khác (Hien_Thi_Ngay2) với tham số tô đỏ CREATE FUNCTION Hien_Thi_Ngay2 (n IN OUT NUMBER) RETURN CHAR AS …… Chạy block sau và cho nhận xét so với Function Hien_Thi_Ngay1 ở slide trước declare 	x char(30); 	y number; begin 	dbms_output.put_line('Su dung tham so in out trong function'); 	y:=4; 	x:=HIEN_THI_NGAY2(y); 	dbms_output.put_line(y); 	dbms_output.put_line(x); end; * Gọi hàm trong PL/SQL: - Đầu tiên khai báo biến có kiểu dữ liệu trùng với kiểu dữ liệu trị trả về của một hàm. Thực hiện lệnh sau: - Ví dụ: 	Declare 	x CHAR(20); 	BEGIN 	x:=Hien_Thi_Ngay(3); 	/*Tổng quát: biến:=Tên-hàm(danh sách đối số);*/ …. 	END; Lệnh xóa hàm: 	 DROP FUNCTION tên-hàm; 	(Function, Procedure, Trigger) 7. Khai báo hàm, thủ tục và ràng buộc (4) * 2. Khai báo Thủ tục (Procedure) Thủ tục là một chương trình con để thực hiện một hành động cụ thể nào đó. Hàm và thủ tục giống nhau, khác nhau ở chỗ hàm thì có mệnh đề RETURN. Cú pháp: 	CREATE [OR REPLACE] PROCEDURE tên-thủ tục [(parameter1 [, parameter2,…])] IS 	[khai báo biến] 	BEGIN 	 	[EXCEPTION ] 	END; /*kết thúc thủ tục*/ 	(Function, Procedure, Trigger) 7. Khai báo hàm, thủ tục và ràng buộc (5) * Từ khóa OR REPLACE để tự động xóa và tạo mới thủ tuc nếu tên thủ tục đó đã tồn tại. Ví dụ: 	CREATE OR REPLACE Hien_Thi_Ngay (m number) IS …. Không được dùng Varchar2(n) trong tham số truyền vào (parameter), kiểu dữ liệu tham số truyền vào phải là kiểu dữ liệu không ràng buộc. Parameter được thay bởi: 	tên-tham-số-truyền-vào [IN | OUT | IN OUT] kiểu-dữ-liệu [{ := | DEFAULT value}] 	(Function, Procedure, Trigger) 7. Khai báo hàm, thủ tục và ràng buộc (6) * Ví dụ: CREATE PROCEDURE Hien_Thi_Ngay (n NUMBER) IS ngay CHAR(15); BEGIN 	IF n =1 THEN 	ngay :='Sunday'; 	ELSIF n =2 THEN 	ngay :='Monday'; 	ELSIF n =3 THEN 	ngay :='Tuesday'; 	ELSIF n =4 THEN 	ngay :='Wednesday'; 	ELSIF n =5 THEN 	ngay :='Thursday'; 	ELSIF n =6 THEN 	ngay :='Friday'; 	ELSIF n =7 THEN 	ngay :='Saturday'; 	END IF; END; /* tương tự chạy Procedure với các tham số OUT, IN OUT*/ 	(Function, Procedure, Trigger) 7*. Khai báo hàm, thủ tục và ràng buộc (7) * Gọi thủ tục trong PL/SQL: - Ví dụ: 	Declare 	…. 	BEGIN 	Hien_Thi_Ngay(3); 	/*Tổng quát: Tên-hàm(danh sách tham số);*/ 	…. 	END; Gọi thủ tục từ SQL*Plus: SQL> EXECUTE Hien_Thi_Ngay(6) Cú pháp xóa thủ tục: DROP PROCEDURE tên-thủ-tục; 	(Function, Procedure, Trigger) 7. Khai báo hàm, thủ tục và ràng buộc (8) * Create Procedure Insert_EMP (v_EMPNO in varchar2, v_ENAME in varchar2, v_HIREDATE in date, v_MGR in varchar2, v_SAL in varchar2) As emp_cnt int; Begin select count(*) into emp_cnt from EMP where EMPNO = v_EMPNO; if ( emp_cnt=1) then 	 DBMS_Output.Put_line('Trung khoa chinh');/*tru`ng khoa chinh */ else savepoint Point_1; insert into EMP (EMPNO, ENAME,HIREDATE, MGR, SAL) values (v_EMPNO, v_ENAME,v_HIREDATE,v_MGR, v_SAL) ; if SQL%ROWCOUNT = 0 then DBMS_Output.Put_line('Xay ra loi giao tac');	/*loi khac*/ ROLLBACK to savepoint Point_1; end if; DBMS_Output.Put_line('Them nhan vien thanh cong') ; COMMIT ; end if; end; Ghi chú: Chạy 2 lệnh sau trong SQL*Plus để thấy kết quả xử lý của 2 trường hợp này. SQL> Exec Insert_EMP (‘7788', ‘Nguyen Van A','2 Feb 2006', '7788',1000) ;  trùng khóa chính 7788 SQL> Exec Insert_EMP (‘7789', ‘Nguyen Van A','2 Feb 2006', '7788',1000) ;  thêm mới nv 7789 	(Function, Procedure, Trigger) 7*. Khai báo hàm, thủ tục và ràng buộc (9) * 7*.Ví dụ:	 Procedure sử dụng tham số IN, OUT CREATE PROCEDURE P_Ngay (n IN NUMBER,m OUT NUMBER) IS ngay CHAR(15); BEGIN 	IF n =1 THEN 	ngay :='Sunday'; 	ELSIF n =2 THEN 	ngay :='Monday'; 	ELSIF n =3 THEN 	ngay :='Tuesday'; 	ELSIF n =4 THEN 	ngay :='Wednesday'; 	ELSIF n =5 THEN 	ngay :='Thursday'; 	ELSIF n =6 THEN 	ngay :='Friday'; 	ELSIF n =7 THEN 	ngay :='Saturday'; 	END IF; 	m:=n; 	dbms_output.put_line('Ngay truyen vao:' || ngay); END; * declare m number; begin P_Ngay(5,m); dbms_output.put_line('Tham so ra:' || m); end; declare a number; b number; begin a:=4; P_Ngay(a,b); dbms_output.put_line(a); dbms_output.put_line(b); end; declare m number; begin m:=7; P_Ngay(5,m); dbms_output.put_line('Tham so ra:' || m); end; declare m number; begin P_Ngay(5,7); /* cho nhan xet???? */ dbms_output.put_line('Tham so ra:'); end; 7*.Ví dụ:	 Procedure sử dụng tham số IN, OUT (tt) Chạy từng TH trong iSQL*Plus để thấy kết quả xử lý và cho nhận xét??? * 2. Khai báo ràng buộc (Trigger) Trigger được dùng để khai báo các ràng buộc toàn vẹn phức tạp mà không thể khai báo ở cấp talbe như ràng buộc NOT NULL, UNIQUE, PRIMARY KEY,.. CHECK. Cú pháp: 	CREATE [REPLACE] TRIGGER tên-trigger 	BEFORE|AFTER INSERT/DELETE/UPDATE ON tên-Table [REFERENCING [NEW AS ] [OLD AS ]] 	[FOR EACH ROW] 	DECLARE /*Tùy thuộc bài toán có khai báo biến hay ko*/ 	[khai báo biến] 	WHEN 	Block-của-PL/SQL 	(Function, Procedure, Trigger) 7. Khai báo hàm, thủ tục và ràng buộc (10) * Từ khóa REPLACE để tự động xóa và tạo mới trigger nếu trigger đó đã tồn tại. Ví dụ: REPLACE TRIGGER Tên-Trigger table_name để chỉ đến tên của table muốn tạo trigger. NEW chỉ giá trị dòng mới insert/update, OLD chỉ giá trị dòng mới xóa (delete). Note: In the trigger body, NEW and OLD must be preceded by a colon (":"), but in the WHEN clause, they do not have a preceding colon! INSERT | DELETE | UPDATE ứng với sự kiện tác động lên table để trigger tự động thi hành khi sự kiện đó xảy ra. AFTER chỉ rằng database trigger sẽ thi hành sau khi đã thực hiện sự kiện và BEFORE là để khai báo trigger sẽ thi hành trước khi thi hành sự kiện. Tùy chọn FOR EACH ROW để chỉ rằng trigger sẽ thi hành khi câu lệnh SQL tác động lên từng dòng. 	(Function, Procedure, Trigger) 7. Khai báo hàm, thủ tục và ràng buộc (11) * Chú ý khi tạo trigger: Phần thân trigger có thể chứa các lệnh DML, nhưng lệnh SELECT phải là SELECT INTO ngoại trừ lệnh SELECT khi khai báo cursor. DDL không được dùng trong phần thân của trigger. Không cho phép các lệnh quản lý giao tác (COMMIT, ROLLBACK, SAVEPOINT) trong phần thân của trigger. Nếu trigger gọi một chương trình con thì chương trình con đó không được chứa các lệnh quản lý giao tác. 	(Function, Procedure, Trigger) 7. Khai báo hàm, thủ tục và ràng buộc (12) * Thao tác trigger: DISABLE và ENABLE ALTER TRIGGER tên-trigger DISABLE; 	Để disable tất cả các trigger liên quan đến một table cụ thể, dùng lệnh: 	ALTER TABLE table_name DISABLE ALL TRIGGERS; Lệnh enable một trigger 	ALTER TRIGGER trigger_name ENABLE; Để enable tất cả các trigger liên quan đến một table cụ thể, dùng lệnh: 	ALTER TABLE table_name ENABLE ALL TRIGGERS; Cú pháp xóa trigger: DROP TRIGGER Tên-trigger; 	(Function, Procedure, Trigger) 7. Khai báo hàm, thủ tục và ràng buộc (13) * Create Trigger Tang_Bonus AFTER INSERT ON emp FOR EACH ROW declare 	v_sal EMP.SAL%TYPE; Begin 	if :new.SAL IS NOT NULL then 	/*trich 10% luong cua nguoi moi vao*/ 	/*Note: In the trigger body, NEW and OLD must be preceded by a colon (":"), but in the WHEN clause, they do not have a preceding colon! */ 	 	v_sal:= :new.Sal*10/100; 	/*bonus cho nguoi quan ly = 10% luong nguoi moi vao*/ 	insert into BONUS (empno, sal) values (:new.MGR,v_sal) ; 	End if; End; Ghi chú: Trước khi tạo Trigger, mở bảng BONUS của user SCOTT sửa lại cột Ename thành Empno và đổi kiểu dữ liệu tương ứng. Chạy lệnh sau: SQL> Exec Insert_EMP (‘7790', ‘Nguyen Van B','2 Feb 2006', '7788',1000) ;  thêm nhân viên mới ràng buộc được thực hiện  kết quả nhân viên 7788 được thêm bonus là 100 (table BONUS). 	(Function, Procedure, Trigger) 7*. Khai báo hàm, thủ tục và ràng buộc (14) * Aborting Triggers with Error The WHEN clause or body of the trigger can check for the violation of certain conditions and signal an error accordingly using the Oracle built-in function RAISE_APPLICATION_ERROR. The action that activated the trigger (insert, update, or delete) would be aborted. For example, the following trigger enforces the constraint Person.age >= 0: create table Person (age int); CREATE TRIGGER PersonCheckAge AFTER INSERT OR UPDATE OF age ON Person FOR EACH ROW BEGIN IF (:new.age < 0) THEN RAISE_APPLICATION_ERROR(-20000, 'no negative age allowed'); END IF; END; 	(Function, Procedure, Trigger) 7*. Khai báo hàm, thủ tục và ràng buộc (15) * Mutating Table Errors Sometimes you may find that Oracle reports a "mutating table error" when your trigger executes. This happens when the trigger is querying or modifying a "mutating table", which is either the table whose modification activated the trigger, or a table that might need to be updated because of a foreign key constraint with a CASCADE policy. To avoid mutating table errors: * A row-level trigger must not query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger.) * A statement-level trigger must not query or modify a mutating table if the trigger is fired as the result of a CASCADE delete. 7*. Trigger: Mutating Trigger (1) * Mutating Trigger Demo The insert into t1 firest the trigger which attempts to count the number of records in t1 ... which is ambiguous. CREATE TABLE t1 (x int); CREATE TABLE t2 (x int); INSERT INTO t1 VALUES (1); CREATE OR REPLACE TRIGGER t_trigger AFTER INSERT ON t1 FOR EACH ROW DECLARE 	i INTEGER; BEGIN 	SELECT COUNT(*) INTO i FROM t1; 	INSERT INTO t2 VALUES (i); END; Tạo trigger, sau đó chạy lệnh INSERT INTO t1 VALUES (2); cho nhận xét??? 7*. Trigger: Mutating Trigger (2) * Fix Mutating Trigger With Autonomous Transaction Count on t1 is performed as though a different user logged on and asked the question of t1 CREATE OR REPLACE TRIGGER t_trigger AFTER INSERT ON t1 FOR EACH ROW DECLARE 	PRAGMA AUTONOMOUS_TRANSACTION; 	i INTEGER; BEGIN 	SELECT COUNT(*) INTO i FROM t1; 	INSERT INTO t2 VALUES (i); 	COMMIT; END; Sửa lại Trigger trên, chạy lệnh INSERT INTO t1 VALUES (2); cho nhận xét??? 7*. Trigger: Mutating Trigger (3) SELECT COUNT(*) FROM t1; SELECT COUNT(*) FROM t2;