Bài giảng Hệ quản trị cơ sở dữ liệu (Oracle) - Chương 5: Procedure, Trigger, Function - Nguyễn Thị Mỹ Dung

 Bài giảng 
HQT Cơ sở dữ liệu 
 (ORACLE) 
 Số tc: 3; LT: 25; Btập: 20 
 GV: Nguyễn Thị Mỹ Dung 
 Khối lớp: Đại học L2 
 1 
 NỘI DUNG MÔN HỌC 
 1 Chương 1: Tổng quan về Oracle (2) 
 2 Chương 2: Cơ bản về Oracle (2) 
 3 Chương 3: Truy vấn SQL (12) 
4 Chương 4: Lập trình PL/SQL (9) 
 5 Chương 5: Procedure, Trigger, Function (12) 
 6 Chương 6: Quản trị người dùng (4) 
 7 Chương 7: Kết nối CSDL với Oracle (4) 
 2 
 Ch5: Procedure, Function, Trigger 
I. Procedure 
II. Function 
III. Trigger 
IV. Bài tập thực hành 
 3 
 I. Procedure 
 1. Giới thiệu 
 Thủ tục là một chương trình con để thực hiện một hành 
động cụ thể nào đó để tăng khả năng xử lý. 
 Có thể sử dụng nhiều lần, có tính bảo mật và an toàn dữ 
liệu cao. 
 Không có giá trị trả về. 
 2. Khai báo 
 CREATE [OR REPLACE] 
 PROCEDURE tên-thủ tục [(dsáchthamsố)] IS 
 [khai báo biến] 
 BEGIN 
 [EXCEPTION ] 
 END; /*kết thúc thủ tục*/ 
 4 
 Procedure – khai báo (tt) 
 - Từ khóa OR REPLACE để tự động xóa và tạo 
mới thủ tục nếu tên thủ tục đó đã tồn tại. 
 Ví dụ: 
 CREATE OR REPLACE 
 HienThiNgay (m number) IS . 
 - Không được dùng Varchar2(n) trong tham số 
truyền vào, 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. 
 - Không thể áp dụng cho các điều kiện thực hiện 
trên nhóm (mệnh đề GROUP). 
 - 
 5 
 Procedure – khai báo (tt) 
Cú pháp đầy đủ: 
CREATE [OR REPLACE] PROCEDURE procedure_name 
[( [IN | OUT | IN OUT] 
[, [IN | OUT | IN OUT] 
 [DEFAULT ]) ] 
IS 
 [ 
 [NULL | NOT NULL] [DEFAULT ] ;] 
BEGIN 
 [;] -- Đây là khối lệnh PL/SQL trong chương trình 
 [EXCEPTION --Phần ngoại lệ (nếu có) 
 WHEN THEN 
 [;] ] 
END; 
 6 
 Procedure (tt) 
3. Gọi thủ tục 
 - Cú pháp gọi trong PL/SQL: 
 Declare 
 . 
 BEGIN 
 Tênthủtục(danhsáchthamsố);*/ 
 . 
 END; 
- Cú pháp gọi thủ tục từ SQL*Plus: 
 SQL> EXECUTE Tênthủtục(danhsáchthamsố) 
4. Xóa thủ tục 
 DROP PROCEDURE tênthủtục; 
 7 
 Procedure (tt) 
VD1: Tăng kinh phí cho đề tài DT004 
CREATE OR REPLACE PROCEDURE Tang_kinhphi 
AS 
 kp_old INT; 
 kp_new INT; 
BEGIN 
 SELECT KINHPHI INTO kp_old 
 FROM DETAI WHERE MADT='DT004'; 
 IF SQL%FOUND THEN kp_new := kp_old + 
 kp_old*10/100; 
 UPDATE DETAI SET 
 KINHPHI = kp_new 
 WHERE MADT='DT004'; 
 8 
 Procedure – VD1 (tt) 
 IF SQL%ROWCOUNT0 THEN 
 DBMS_OUTPUT.PUT_LINE 
 ('De tai DT004 duoc tang'); 
 END IF; 
 END IF; 
 EXCEPTION 
 WHEN NO_DATA_FOUND THEN 
 DBMS_OUTPUT.PUT_LINE 
 ('Khong tim thay de tai nay!'); 
 END; 
 Thực thi thủ tục: 
 Begin TANG_kinhphi; end; 
 9 
 Procedure – VD (tt) 
VD2: Thủ tục in ra ngày tương ứng với số truyền vào 
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; 
 10 
 Procedure – VD2 (tt) 
--Chạy thủ tục với tham số ra 
DECLARE 
 M NUMBER; 
BEGIN 
 P_NGAY(5,M); 
 DBMS_OUTPUT.PUT_LINE('THAM SO RA:'|| M); 
END; 
-- Truyền trực tiếp tham số ra 
DECLARE 
 M NUMBER; 
BEGIN 
 P_NGAY(5,7); /* Cho nhan xet???? */ 
 DBMS_OUTPUT.PUT_LINE('THAM SO RA:'|| M); 
END; 
 11 
 Procedure – VD2 (tt) 
--Truyền cả hai tham số 
DECLARE 
 so1 NUMBER; 
 so2 NUMBER; 
BEGIN 
 so1:=4; 
 P_NGAY(so1,so2); 
 DBMS_OUTPUT.PUT_LINE(so1); 
 DBMS_OUTPUT.PUT_LINE(so2); 
END; 
--Kết quả in ra? 
 12 
 Procedure – VD (tt) 
VD3: Tạo thủ tục kiểm tra khoá chính khi thêm sinh viên 
Create Procedure THEM_SINHVIEN (v_MASV in CHAR, v_HOTENSV in varchar2, 
 v_NAMSINH in INT, v_QUEQUAN in varchar2, v_HOCLUC in FLOAT) 
As DEM int; 
Begin 
 select count(*) into DEM from SINHVIEN where MASV = v_MASV; 
 if ( DEM=1) then 
 DBMS_Output.Put_line('Trung khoa chinh'); /*tru`ng khoa chinh */ 
 else 
 savepoint Point_1; 
 insert into SINHVIEN (MASV, HOTENSV,NAMSINH, QUEQUAN, HOCLUC) 
 values (v_MASV, v_HOTENSV,v_NAMSINH,v_QUEQUAN, v_HOCLUC) ; 
 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; 
--Run Procedure 
BEGIN THEM_SINHVIEN ('SV008', 'Nguyen Van An',1995, 'Can Tho', 7.5); END; 
 13 
 II. Function 
 1. Giới thiệu 
 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. 
 Tham số sử dụng trong hàm chỉ có thể là loại IN, 
không chấp nhận giá trị OUT hay giá trị IN OUT. 
 Không cho phép hàm trả về kiểu dữ liệu như 
RECORD, TABLE. 
 14 
 Function (tt) 
2. Khai báo Cú pháp: 
CREATE [OR REPLACE] FUNCTION Tênhàm 
[( [IN] 
[, [IN] [DEFAULT ]) ] 
RETURN IS 
 [ 
 [NULL | NOT NULL] [DEFAULT ] ;] 
BEGIN 
 [;] -- Đây là khối lệnh PL/SQL trong chương trình 
 RETURN ; 
 [EXCEPTION --Phần ngoại lệ (nếu có) 
 WHEN THEN 
 [;] ] 
END; 
 15 
 Function (tt) 
 3. 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 như sau: 
 Declare 
 x CHAR(20); 
 BEGIN 
 x:=F_Ngay(3); 
 /*Tổng quát: biến:=Tênhàm(danhsáchđốisố);*/ 
 . 
 END; 
 4. Lệnh xóa hàm 
 DROP FUNCTION Tênhàm; 
 16 
 Function (tt) 
 VD1: Hàm hiển thị Ngày 
tương ứng với số ELSIF n =4 THEN 
 CREATE FUNCTION ngay :='Wednesday'; 
 F_Ngay (n NUMBER) ELSIF n =5 THEN 
 RETURN CHAR ngay :='Thursday'; 
 AS ELSIF n =6 THEN 
 ngay CHAR(15); 
 BEGIN ngay :='Friday'; 
 IF n =1 THEN ELSIF n =7 THEN 
 ngay :='Sunday'; ngay :='Saturday'; 
 ELSIF n =2 THEN END IF; 
 ngay :='Monday'; RETURN ngay; 
 ELSIF n =3 THEN 
 ngay :='Tuesday'; END; 
 17 
 Function (tt) 
 --Thực thi Hàm 
 DECLARE 
 X CHAR(30); 
 BEGIN 
 X:=F_NGAY(4); 
 DBMS_OUTPUT.PUT_LINE(X); 
 END; 
 Ghi chú: thay ví dụ1 (Function) bằng một hàm 
khác (F_Ngay1) với tham số IN, OUT (tương tự như 
Procedure). 
 18 
 Function (tt) 
 VD2: Đếm số lượng đề tài 
 CREATE OR REPLACE FUNCTION SLDT (MA_DT IN 
DETAI.MADT%TYPE) 
 RETURN NUMBER AS 
 Soluong NUMBER; 
 BEGIN 
 SELECT COUNT(MADT) INTO Soluong FROM DETAI 
 WHERE MADT=MA_DT; 
 RETURN Soluong; 
 END; 
 -- Run Function 
 DECLARE 
 X NUMBER; Y VARCHAR2(5):='DT004'; 
 BEGIN 
 X:=SLDT(Y); 
 DBMS_OUTPUT.PUT_LINE(X); 
 END; 
 19 
 III. Trigger 
 1. Giới thiệu 
 - 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 thực hiện ngầm định ngay khi thực hiện 
lệnh SQL như INSERT, DELETE, UPDATE nhằm 
đảm bảo các quy tắc logic phức tạp của dữ liệu. 
 - Chỉ sử dụng khi cần thiết vì có thể gây rối với 
các hệ thống lớn. 
 - Không cho phép các lệnh quản lý giao tác 
(COMMIT, ROLLBACK, SAVE POINT) bên trong 
thân trigger. 
 20 
 Trigger (tt) 
 2. Các thành phần 
 - BEFORE TRIGGER: Trigger được kích hoạt trước 
khi thực hiện câu lệnh 
 - AFTER TRIGGER: trigger được kích hoạt sau khi 
thực hiện câu lệnh 
 - INSTEAD OF TRIGGER: trigger cho phép người 
dùng thay đổi một cách trong suốt dữ liệu của một số 
view 
 - 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. 
 - NEW chỉ giá trị dòng mới trong insert | update, OLD 
chỉ giá trị dòng mới xóa trong delete. 
 - 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. 
 21 
 Trigger (tt) 
3. Cú pháp 
CREATE [OR REPLACE] TRIGGER Têntrigger 
BEFORE | AFTER INSERT | DELETE | UPDATE 
ON tên-Table 
[REFERENCING [NEW AS ] 
 [OLD AS ]] 
[FOR EACH ROW] 
DECLARE /*Có khi có khai báo biến*/ 
 [khai báo biến] 
WHEN --Điều kiện ràng buộc trigger 
BEGIN 
 Block-của-PL/SQL --Khối lệnh PL/SQL 
END; 
 22 
 Trigger (tt) 
 4. Thao tác với Trigger 
 Cho phép/ không cho phép kích hoạt Trigger: 
 ALTER TRIGGER têntrigger DISABLE | ENABLE; 
 Không cho phép kích hoạt tất cả các Trigger trên 
bảng: 
 ALTER TABLE table_name DISABLE | ENABLE 
 ALL TRIGGERS; 
 5. Xóa trigger 
 DROP TRIGGER Têntrigger; 
 23 
 Trigger (tt) 
 VD1: Tạo trigger ràng buộc khi thêm một khách 
hàng thì hiển thị thông báo “Đã thêm thành công” 
 CREATE OR REPLACE TRIGGER THEMSV 
 AFTER INSERT ON SINHVIEN 
 FOR EACH ROW 
 BEGIN 
 DBMS_OUTPUT.PUT_LINE('DA THEM THANH CONG'); 
 END; 
 --Kiem tra TRIGGER 
 INSERT INTO SINHVIEN 
 VALUES ('SV100', 'NGUYEN HO TRUC ANH', 1995, 
 'TIEN GIANG', 7.2); 
 24 
 Trigger (tt) 
 VD2: Tạo trigger ràng buộc khi thêm một đề tài mới thì thêm sinh viên 
SV001 thực hiện đề tài này ở Trà Vinh với kết quả là 7. 
 CREATE OR REPLACE TRIGGER TRG_THEMDETAI 
 AFTER INSERT ON DETAI 
 FOR EACH ROW 
 DECLARE 
 V_MADT SVDT.MADT%TYPE; 
 V_MASV SVDT.MASV%TYPE:='SV001'; 
 V_NOIAD SVDT.NOIAD%TYPE:='TRA VINH'; 
 V_KQ SVDT.KETQUA%TYPE:=7; 
 BEGIN 
 IF :NEW.MADT IS NOT NULL THEN 
 V_MADT:=:NEW.MADT; 
 INSERT INTO SVDT (MASV, MADT, NOIAD, KETQUA) 
 VALUES (V_MASV, V_MADT, V_NOIAD, V_KQ); 
 DBMS_OUTPUT.PUT_LINE('DA THEM THANH CONG'); 
 END IF; 
 END; 
 25 
 Trigger – VD (tt) 
 VD: Kiểm tra năm sinh nhập vào có hợp lệ? 
 CREATE TRIGGER KiemtraNS 
 AFTER INSERT OR UPDATE OF Namsinh 
 ON SINHVIEN 
 FOR EACH ROW 
 BEGIN 
 IF (:new.Namsinh < 0) THEN 
 RAISE_APPLICATION_ERROR(-20000, 
'Nam sinh khong hop le!'); 
 END IF; 
 END; 
 26 
 IV. Bài tập 
 1. Xây dựng khối lệnh xuất bảng cửu chương. 
 2. Xây dựng khối lệnh liệt kê danh sách sinh viên 
thực hiện theo từng đề tài? 
 3. Xây dựng khối lệnh kiểm tra MASV có tồn tại 
không? 
 4. Xây dựng khối lệnh kiểm tra MADT có tồn tại 
không? 
 5. Tạo Trigger khi thêm một đề tài mới thì hiển thị 
họ tên của sinh viên thực hiện đề tài đó. 
 6. Tạo Trigger ràng buộc khi thêm một sinh viên 
mới thì cập nhật tự động trị thêm đề tài do sinh viên 
đó thực hiện. 
 27 
 Bài tập (tt) 
 7. Tạo Trigger ràng buộc khi xoá một sinh viên thì 
xoá toàn bộ các dữ liệu có liên quan đến sinh viên 
này. 
 8. Tạo Trigger ràng buộc khi cập nhật MaDT sẽ 
cập nhập dữ liệu liên quan đến đề tài. 
 9. Tạo Trigger ràng buộc khi thêm hoặc cập nhật 
kinh phí cho đề tài phải lớn hơn 0. 
 28 
 Tóm tắt chương 
- Cách xây dựng thủ tục, hàm, trigger 
- Thực hiện cài đặt thủ tục, hàm, trigger 
 29