Bài giảng Cơ sở dữ liệu nâng cao - Chương 5: Procedure, Function - Nguyễn Thị Mỹ Dung

 Bài giảng 
 CƠ SỞ DỮ LIỆU 
 NÂNG CAO 
 Số tc: 2; LT: 20; Btập: 10 
 GV: Nguyễn Thị Mỹ Dung 
 Khối lớp: Đại học L2 
Chương 5: Procedure, Function 1 
 NỘI DUNG MÔN HỌC 
 1 Chương 1. Tổng quan CSDL phân tán (4) 
 2 Chương 2. Tổng quan CSDL hướng đối tượng 
 3 Chương 3. Cơ bản về Oracle (4) 
4 Chương 4: Lập trình PL/SQL (4) 
 5 Chương 5: Procedure, Function (4) 
 6 Chương 6: Thiết kế đối tượng (6) 
 7 Chương 7: Truy vấn trong CSDL HĐT (4) 
Chương 5: Procedure, Function 2 
 Chương 5: Procedure, Function 
I. Procedure 
II. Function 
III. Bài tập thực hành 
 Chương 5: Procedure, Function 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ênthủtục [(dsáchthamsố)] IS 
 [khai báo biến] 
 BEGIN 
 [EXCEPTION ] 
 END; /*kết thúc thủ tục*/ 
Chương 5: Procedure, Function 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). 
 Chương 5: Procedure, Function 5 
 Procedure – khai báo (tt) 
 Cú pháp chi tiết: 
 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; 
Chương 5: Procedure, Function 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; 
Chương 5: Procedure, Function 7 
 Procedure (tt) 
 VD1: Tăng kinh phí cho đề tài DT004 
 CREATE OR REPLACE PROCEDURE Tang_kinhphi 
 IS 
 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'; 
Chương 5: Procedure, Function 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; 
Chương 5: Procedure, Function 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; 
Chương 5: Procedure, Function 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; 
Chương 5: Procedure, Function 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? 
Chương 5: Procedure, Function 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) 
 IS 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; 
Chương 5: Procedure, Function 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. 
 Chương 5: Procedure, Function 14 
 Function (tt) 
 2. Khai báo: 
 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; 
Chương 5: Procedure, Function 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; 
Chương 5: Procedure, Function 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'; 
 IS 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; 
Chương 5: Procedure, Function 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). 
 Chương 5: Procedure, Function 18 
 Function (tt) 
 VD2: Đếm số lượng đề tài 
 CREATE OR REPLACE FUNCTION SLDT (MA_DT IN 
DETAI.MADT%TYPE) 
 RETURN NUMBER IS 
 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; 
 Chương 5: Procedure, Function 19 
 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 cán bộ 
theo từng đơn vị? 
 3. Xây dựng thủ tục (hoặc khối lệnh) kiểm tra 
MACB có tồn tại không? 
 4. Xây dựng khối lệnh (hoặc thủ tục) kiểm tra 
MACV có tồn tại không? 
 5. Tạo hàm đếm số lượng cán bộ theo từng đơn 
vị. 
 6. Tạo thủ tục xoá chức vụ nếu không vi phạm 
ràng buộc về khoá và rỗng. 
 Chương 5: Procedure, Function 20 
 Tóm tắt chương 
 - Cách xây dựng thủ tục, hàm 
 - Thực hiện cài đặt thủ tục, hàm 
 - Thực thi thủ tục, hàm 
 - Xoá thủ tục, hàm đã xây dựng 
Chương 5: Procedure, Function 21