Bài giảng Cơ sở dữ liệu nâng cao: Chương 5

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)

4

Chương 4: Lập trình PL/SQL (4)

Chương 5: Procedure, Function (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.

PROCEDURE tênthủtục [(dsáchthamsố)] IS [khai báo biến]

[EXCEPTION ]

Không có giá trị trả về. 2. Khai báo CREATE [OR REPLACE] BEGIN 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.

HienThiNgay (m number) IS ….

Ví dụ: CREATE OR REPLACE - 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

[ < kiểudữliệu >

[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)

….

Tênthủtục(danhsáchthamsố);*/ ….

3. Gọi thủ tục - Cú pháp gọi trong PL/SQL: Declare BEGIN 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;

KINHPHI = kp_new

UPDATE DETAI SET WHERE MADT='DT004';

Chương 5: Procedure, Function 8

Procedure – VD1 (tt)

IF SQL%ROWCOUNT<>0 THEN

('De tai DT004 duoc tang');

DBMS_OUTPUT.PUT_LINE

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)

(n IN NUMBER,m OUT NUMBER) IS

ngay CHAR(15);

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;

dbms_output.put_line('Ngay truyen vao:' || ngay);

VD2: Thủ tục in ra ngày tương ứng với số truyền vào CREATE PROCEDURE P_Ngay BEGIN m:=n; 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)

so1 NUMBER; so2 NUMBER;

so1:=4;

--Truyền cả hai tham số DECLARE BEGIN 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)

v_NAMSINH in INT, v_QUEQUAN in varchar2, v_HOCLUC in FLOAT)

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, 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:

x CHAR(20);

x:=F_Ngay(3); /*Tổng quát: biến:=Tênhàm(danhsáchđốisố);*/

Declare BEGIN …. 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ố

ngay :='Wednesday';

F_Ngay (n NUMBER)

ngay :='Thursday';

ngay CHAR(15);

ELSIF n =4 THEN ELSIF n =5 THEN ELSIF n =6 THEN ngay :='Friday'; ELSIF n =7 THEN ngay :='Saturday'; END IF; RETURN ngay; END;

CREATE FUNCTION RETURN CHAR IS BEGIN IF n =1 THEN ngay :='Sunday'; ELSIF n =2 THEN ngay :='Monday'; ELSIF n =3 THEN ngay :='Tuesday';

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

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

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