›
›

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

Download
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)  
Chương 2. Tổng quan CSDL hướng đối tượng  
2
3
Chương 3. Cơ bản về Oracle (4)  
Chương 4: Lập trình PL/SQL (4)  
Chương 5: Procedure, Function (4)  
4
5
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  
<khối lệnh>  
[EXCEPTION <xử lý ngoại lệ>]  
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  
[(<thamsố> [IN | OUT | IN OUT] <kiểudữliệu>  
[,<thamsố> [IN | OUT | IN OUT] <kiểudữliệu>  
[DEFAULT <default_value>]) ]  
IS  
[<tênbiến> < kiểudữliệu >  
[NULL | NOT NULL] [DEFAULT <default_value>] ;]  
BEGIN  
[<khốilệnh>;] -- Đây là khối lệnh PL/SQL trong chương trình  
[EXCEPTION --Phần ngoại lệ (nếu có)  
WHEN <tênngoạilệ> THEN  
[<khốilệnh>;] ]  
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%ROWCOUNT<>0 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  
[(<thamsố1> [IN] <kiểudữliệu>  
[,<thamsố1> [IN] <kiểudữliệu>[DEFAULT <default_value>]) ]  
RETURN <kiểudữliệu> IS  
[<Tênbiến><kiểudữliệu>  
[NULL | NOT NULL] [DEFAULT <default_value>] ;]  
BEGIN  
[<Khốilệnh>;] -- Đây là khối lệnh PL/SQL trong chương trình  
RETURN <giátrịtrảvề>;  
[EXCEPTION --Phần ngoại lệ (nếu có)  
WHEN <Tênngoạilệ> THEN  
[<Khốilệnh>;] ]  
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  
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;  
tương ứng với số  
CREATE FUNCTION  
F_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';  
RETURN ngay;  
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ải về để xem bản đầy đủ
pdf 21 trang yennguyen 18260 Free
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Cơ sở dữ liệu nâng cao - Chương 5: Procedure, Function - Nguyễn Thị Mỹ Dung", để tải tài liệu gốc về máy hãy click vào nút Download ở trên

File đính kèm:

  • pdfbai_giang_co_so_du_lieu_nang_cao_chuong_5_procedure_function.pdf