Bài giảng Hệ quản trị CSDL Oracle - Phần 4: Ngôn ngữ thủ tục PL/SQL

ợc lại, bằng 
FALSE khi lệnh fetch trả về ít nhất một dòng hoặc chưa fetch 
đến dòng cuối cùng. 
 Ví dụ: OPEN cur_first; 
 LOOP 
 FETCH cur_first INTO v_empno,v_sal; 
 EXIT WHEN cur_first%NOTFOUND; 
 END LOOP; 
 6/ SỬ DỤNG CON TRỎ (CURSOR) 
– Con trỏ tường minh – Thuộc tính 
43 
2.Thuộc tính %FOUND (đi kèm lệnh Fetch) 
 - Ngược với thuộc tính %NOTFOUND. 
 Ví dụ: 
 OPEN cur_first; 
 LOOP 
 FETCH cur_first INTO v_empno,v_sal; 
 IF cur_first%FOUND THEN 
 …………. 
 ELSE 
 CLOSE cur_first; 
 EXIT; 
 END IF; 
 END LOOP; 
 6/ SỬ DỤNG CON TRỎ (CURSOR) 
– Con trỏ tường minh – Thuộc tính 
44 
3.Thuộc tính %ROWCOUNT (đi kèm lệnh Fetch) 
 - Trả về số dòng con trỏ đã được FETCH. 
 Ví dụ: 
 OPEN cur_first; 
 LOOP 
 FETCH cur_first INTO v_empno,v_sal; 
 IF cur_first%ROWCOUNT=1000 THEN 
 EXIT; 
 END IF; 
 END LOOP; 
 6/ SỬ DỤNG CON TRỎ (CURSOR) 
– Con trỏ tường minh – Thuộc tính 
45 
4.Thuộc tính %ISOPEN (đi kèm lệnh Fetch) 
 - Trả về giá trị TRUE nếu con trỏ ở trạng thái mở và giá trị 
FALSE nếu con trỏ đã được đóng. 
 Ví dụ: 
 IF cur_first%ROWCOUNT=1000 THEN 
 FETCH cur_first INTO v_empno,v_sal; 
 ELSE 
 CLOSE cur_first; 
 END IF; 
 6/ SỬ DỤNG CON TRỎ (CURSOR) 
– Con trỏ tường minh có tham số 
46 
Con trỏ có tham số: 
 Một con trỏ có thể nhận tham số là tham trị. Các tham 
số không được dùng để trả về giá trị cho cursor. 
Ví dụ: 
 CURSOR cur_first (v_eno EMP.empno%TYPE) IS 
 SELECT empno, sal 
 FROM EMP 
 WHERE empno= v_eno; 
 Trong đó, v_eno là tham số của con trỏ. Khi thao tác 
với con trỏ có tham số thì ta phải gọi tên con trỏ kèm 
theo giá trị của tham số. 
 6/ SỬ DỤNG CON TRỎ (CURSOR) 
– Con trỏ tường minh – Ví dụ 
47 
Create Procedure Hien_Thi_Muc_Luong as 
 x EMP.empno%TYPE; 
 y EMP.sal%TYPE; 
 cursor nv is select empno, sal from emp; 
begin 
 open nv; 
 DBMS_OUTPUT.Put ('Ma nhan vien '); 
 DBMS_OUTPUT.Put ('Muc luong'); 
 loop 
 DBMS_OUTPUT.new_line; 
 fetch nv into x, y; 
 exit when nv%NotFound; 
 DBMS_OUTPUT.Put (x); DBMS_OUTPUT.Put (' '); 
 DBMS_OUTPUT.Put (y); 
 end loop; 
 DBMS_OUTPUT.new_line; 
 DBMS_OUTPUT.Put ('So records:'); 
 DBMS_OUTPUT.Put (nv%ROWCOUNT); 
 DBMS_OUTPUT.new_line; 
 close nv; 
end; 
 7/ KHAI BÁO HÀM, THỦ TỤC VÀ RÀNG BUỘC 
– Khai báo hàm (FUNCTION) 
48 
1. Khai báo Hàm (Function) 
 Hàm là một chương trình con có trả về giá trị. Hàm và thủ tục 
giống nhau, chỉ khác nhau ở chỗ hàm thì có mệnh đề RETURN. 
 Cú pháp: 
 CREATE [OR REPLACE] FUNCTION tên-hàm [(argument1 
[, argument2,…])] RETURN datatype 
 IS 
 [khai báo biến] 
 BEGIN 
 [EXCEPTION ] 
 END; /*kết thúc hàm*/ 
 7/ KHAI BÁO HÀM, THỦ TỤC VÀ RÀNG BUỘC 
– Khai báo hàm (FUNCTION) 
49 
 Datatype có thể là Number, Char hoặc Varchar2,…. 
 Từ khóa OR REPLACE để tự động xóa và tạo mới hàm nếu tên 
hàm đó đã tồn tại. 
- Ví dụ: 
 CREATE OR REPLACE Hien_Thi_Ngay (m number) 
RETURN VARCHAR IS …. 
 Không được dùng Varchar2(n) trong trị trả về (RETURN) lẫn 
trong đối số truyền vào (argument), kiểu dữ liệu trong đối số 
truyền vào và trong trị trả về phải là kiểu dữ liệu không ràng 
buộc. 
 Argument được thay bởi: 
 tên-đối-số-truyền-vào [IN | OUT | IN OUT] kiểu-dữ-liệu [{ := | 
DEFAULT value}] 
 7/ KHAI BÁO HÀM, THỦ TỤC VÀ RÀNG BUỘC 
– Khai báo hàm (FUNCTION) – Ví dụ 
50 
CREATE FUNCTION Hien_Thi_Ngay (n NUMBER) RETURN CHAR 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; 
 RETURN ngay; 
END; 
 7/ KHAI BÁO HÀM, THỦ TỤC VÀ RÀNG BUỘC 
– Khai báo hàm (FUNCTION) –Gọi hàm 
51 
 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; 
 7/ KHAI BÁO HÀM, THỦ TỤC VÀ RÀNG BUỘC 
– Khai báo thủ tục (PROCEDURE) 
52 
 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 
 a 
 [EXCEPTION ] 
 END; /*kết thúc thủ tục*/ 
 7/ KHAI BÁO HÀM, THỦ TỤC VÀ RÀNG BUỘC 
– Khai báo thủ tục (PROCEDURE) 
53 
 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}] 
 7/ KHAI BÁO HÀM, THỦ TỤC VÀ RÀNG BUỘC 
– Khai báo thủ tục (PROCEDURE)- Ví dụ 1 
54 
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; 
 7/ KHAI BÁO HÀM, THỦ TỤC VÀ RÀNG BUỘC 
– Khai báo thủ tục (PROCEDURE)- Gọi thủ tục 
55 
 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; 
 7/ KHAI BÁO HÀM, THỦ TỤC VÀ RÀNG BUỘC 
– Khai báo thủ tục (PROCEDURE)- Ví dụ 2 
56 
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 
 7/ KHAI BÁO HÀM, THỦ TỤC VÀ RÀNG BUỘC 
– Khai báo ràng buộc (TRIGGER) 
57 
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 [OR REPLACE] TRIGGER tên-trigger 
 BEFORE|AFTER INSERT|DELETE|UPDATE ON tên-Table 
 [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 
 7/ KHAI BÁO HÀM, THỦ TỤC VÀ RÀNG BUỘC 
– Khai báo ràng buộc (TRIGGER) 
58 
 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. 
 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. 
 7/ KHAI BÁO HÀM, THỦ TỤC VÀ RÀNG BUỘC 
– Khai báo ràng buộc (TRIGGER) 
59 
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. 
 7/ KHAI BÁO HÀM, THỦ TỤC VÀ RÀNG BUỘC 
– Khai báo ràng buộc (TRIGGER) 
60 
 Thao tác trigger: DISABLE và ENABLE: 
 Lệnh disable một trigger 
 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; 
 7/ KHAI BÁO HÀM, THỦ TỤC VÀ RÀNG BUỘC 
– Khai báo ràng buộc (TRIGGER) 
61 
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*/ 
 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). 
:new: là bảng tạm dùng 
để lưu dữ liệu tạm thời 
trong khi giao tác Insert 
thực hiện 
:old: là bảng tạm dùng để 
lưu dữ liệu tạm thời trong 
khi giao tác 
Update,Delete thực hiện