Bài giảng Cơ sở dữ liệu nâng cao - Chương 4: Lập trình PL/SQL - Nguyễn Thị Mỹ Dung

 
CHAR, VARCHAR2, DATE, LONG, PL/SQL cho phép 
như BOOLEAN. 
 Ghi chú: Ký hiệu := được sử dụng như là toán tử 
gán. 
 Chương 4: Lập trình PL/SQL 10 
 Kiểu dữ liệu - gán (tt) 
 - Gán biến và biểu thức: 
 biến := biểu thức; 
 Ví dụ: 
 x := UPPER ('Nguyen'); 
 y := 100; 
 kinhphi := kinhphi + kinhphi * 10/100; 
 Ví dụ: 
 kq BOOLEAN; --không có column kiểu boolean, 
 -- chỉ có kiểu dữ liệu Boolean trong PL/SQL 
 kq := kinhphi >20; 
 - Độ ưu tiên của toán tử: ** (phép lũy thừa), NOT, *, 
/, +, -, || (phép nối chuỗi), =, !=, , =, IS 
NULL, LIKE, BETWEEN, IN, AND, OR. 
Chương 4: Lập trình PL/SQL 11 
 Kiểu dữ liệu (tt) 
 3. Nhập xuất dữ liệu 
 - Lệnh xuất: 
 DBMS_OUTPUT.PUT_LINE ('Nội dung'); 
 Lưu ý: trước khi thực hiện lệnh xuất ta phải chạy 
lệnh SET SERVEROUTPUT ON 
 - Lệnh nhập: 2 cách để nhập giá trị cho biến 
  Biến thay thế &: dấu & đặt trước biến. Biến 
 được nhập giá trị lúc thực thi. 
  Biến thay thế &&: dấu && đặt trước biến. Giá trị 
 nhập vào được lưu trữ cho những lần sau. 
 Lưu ý: 
 Biến kiểu chuỗi, kiểu ngày đặt trong cặp dấu ' ' 
 VD: a char(5):= '&a'; 
 Chương 4: Lập trình PL/SQL 12 
 Kiểu dữ liệu – Nhập xuất (tt) 
 Ví dụ: 
 -- SET SERVEROUTPUT ON; 
 DECLARE 
 x number; 
 BEGIN 
 x:=&x; 
 DBMS_OUTPUT.PUT_LINE('Gia tri x ='); 
 DBMS_OUTPUT.PUT_LINE(x); 
 -- DBMS_OUTPUT.PUT_LINE ('Gia tri x = '|| 
 -- TO_CHAR(X)); 
 END; 
Chương 4: Lập trình PL/SQL 13 
 III. Kiểu dữ liệu tham chiếu 
 1. Tham chiếu kiểu %TYPE 
 Dùng để khai báo một biến mà nó tham chiếu đến 
một cột trong CSDL. 
 Khai báo: 
 %TYPE 
 --SET SERVEROUTPUT ON; 
 DECLARE 
 Kinhphi_nhonhat DETAI.Kinhphi%TYPE; 
 BEGIN 
 SELECT MIN(DT.Kinhphi) INTO Kinhphi_nhonhat 
 FROM DETAI DT; 
 DBMS_OUTPUT.PUT_LINE(Kinhphi_nhonhat); 
 END; 
Chương 4: Lập trình PL/SQL 14 
 Kiểu dữ liệu tham chiếu (tt) 
 2. Tham chiếu kiểu %ROWTYPE 
 Dùng để khai báo một biến mà nó tham chiếu đến 
một dòng trong cơ sở dữ liệu (Có cấu trúc như một 
dòng trong Table). 
 Khai báo: 
 %ROWTYPE 
 --SET SERVEROUTPUT ON; 
 DECLARE 
 row_detai DETAI%ROWTYPE; 
 BEGIN 
 SELECT * INTO row_detai 
 FROM detai WHERE MADT= 'DT001'; 
 DBMS_OUTPUT.PUT_LINE(row_detai.MADT || ' : ' ||
 row_detai.TENDT ); 
 END; 
Chương 4: Lập trình PL/SQL 15 
 Kiểu dữ liệu tham chiếu (tt) 
 3. Tham chiếu kiểu %TABLE 
 Dùng để khai báo một biến mà nó tham chiếu đến 
một bảng trong CSDL (Có cấu trúc như một Table). 
 Khai báo: 
 TYPE IS 
 TABLE OF [NOT NULL] 
 INDEX BY BINARY_INTEGER; 
 ; 
 Chương 4: Lập trình PL/SQL 16 
 Kiểu dữ liệu tham chiếu - table(tt) 
 Ví dụ: 
 --SET SERVEROUTPUT ON; 
 DECLARE 
 TYPE detai_type IS TABLE OF detai%ROWTYPE 
 INDEX BY BINARY_INTEGER; 
 bangdt detai_type; 
 BEGIN 
 SELECT * INTO bangdt(1) FROM DETAI 
 WHERE MADT = 'DT001 '; 
 DBMS_OUTPUT.PUT_LINE(bangdt(1).MADT || ' : ' || 
 bangdt(1).TENDT || ' : ' || bangdt(1).CHUNHIEM); 
 END; 
Chương 4: Lập trình PL/SQL 17 
 IV. Các mệnh đề 
 1. Mệnh đề Select 
 SELECT 
 INTO [cursor_var] 
 FROM BẢNG1, BẢNG2,.. 
 WHERE 
 DECLARE 
 vMASV SINHVIEN.MASV%TYPE; 
 vHOTENSV SINHVIEN.HOTENSV%TYPE; 
 BEGIN 
 SELECT MASV, HOTENSV INTO vMASV, vHOTENSV 
 FROM SINHVIEN WHERE MASV= ' SV002 ' ;
 DBMS_OUTPUT.PUT_LINE (vMASV|| ' : ' || vHOTENSV); 
 END; 
Chương 4: Lập trình PL/SQL 18 
 Các mệnh đề (tt) 
 2. Mệnh đề INSERT | UPDATE | DELETE 
 DECLARE 
 MAKH CHAR(2); 
 TENKH VARCHAR2(40); 
 BEGIN 
 MAKH := 'TT'; 
 TENKH := 'Su pham Toan - Tin'; 
 INSERT INTO KHOA (MAKH, TENKH) 
 VALUES (MAKH, TENKH); 
 EXCEPTION WHEN OTHERS THEN 
 DBMS_OUTPUT.PUT_LINE (SQLERRM); 
 END; 
Chương 4: Lập trình PL/SQL 19 
 V. Các cấu trúc điều khiển 
 1. Rẽ nhánh IF 
 Cú pháp: 
 IF THEN ; 
 ELSE 
 IF THEN ; 
 ELSE 
 ; 
 END IF; 
 END IF; 
Chương 4: Lập trình PL/SQL 20 
 Cấu trúc – if (tt) 
VD1: Xét số n là số chẵn hay lẻ? 
DECLARE 
 N INT; 
 TB VARCHAR2(20); 
BEGIN 
 N := &N; 
 IF MOD(N,2)=0 THEN TB :='SO CHAN'; 
 ELSE 
 IF MOD(N,2)=1 THEN TB := 'SO LE'; 
 END IF; 
 END IF; 
DBMS_OUTPUT.PUT_LINE(TB); 
END; 
Chương 4: Lập trình PL/SQL 21 
 Cấu trúc – if (tt) 
VD2: In ngày trong tuần tương ứng với số 
DECLARE 
 n INT; ngay VARCHAR2(8); 
 BEGIN 
 n := &n; 
 IF n=1 THEN ngay := 'Sunday'; 
 ELSIF n=2 THEN ngay :='Monday'; 
 ELSIF n=3 THEN ngay :='Tuesday'; 
 ELSIF n=4 THEN ngay :='Wedsday'; 
 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); 
END; 
 Chương 4: Lập trình PL/SQL 22 
 Các cấu trúc điều khiển (tt) 
 2. Lệnh lặp LOOP 
 Cú pháp: 
 LOOP 
 IF THEN 
 . 
 EXIT; 
 END IF; 
 END LOOP; 
Chương 4: Lập trình PL/SQL 23 
 Cấu trúc điều khiển - Loop(tt) 
 Ví dụ: 
 declare 
 z number :=1; /*khởi tạo biến z*/ 
 BEGIN 
 LOOP 
 z :=z+3; /*tính biểu thức lặp*/ 
 DBMS_OUTPUT.PUT_LINE(z); 
 IF (z>=100) THEN 
 /*nếu thỏa điều kiện thoát khỏi vòng lặp*/ 
 exit; 
 End IF; 
 END LOOP; 
 END; 
Chương 4: Lập trình PL/SQL 24 
 Cấu trúc điều khiển (tt) 
 3. Lệnh lặp FORLOOP 
 Cú pháp: 
 FOR 
 IN giá-trị-khởi-tạo .. giá-trị-kết-thúc 
 LOOP 
 END LOOP; 
Chương 4: Lập trình PL/SQL 25 
 Cấu trúc điều khiển - For (tt) 
 Ví dụ: 
 declare 
 z number:=1; /*khởi tạo biến z*/ 
 i number; 
 BEGIN 
 FOR i IN 1 .. 10 LOOP 
 z :=z+3; /*tính biểu thức lặp*/ 
 DBMS_OUTPUT.PUT_LINE(z); 
 END LOOP; 
 END; 
Chương 4: Lập trình PL/SQL 26 
 Cấu trúc điều khiển – For (tt) 
 4. Lệnh lặp WHILE...LOOP 
 Cú pháp: 
 WHILE LOOP 
 END LOOP; 
 (Phân biệt giữa 3 lệnh Loop  End: Loop và 
ForLoop, WhileLoop: đều sử dụng lệnh Loop) 
 Chương 4: Lập trình PL/SQL 27 
 Cấu trúc điều khiển – While (tt) 
 Ví dụ: 
 declare 
 z number:=1; /*khởi tạo biến z*/ 
 i number; 
 BEGIN 
 WHILE (i<=10) LOOP 
 i:=i+1; 
 z :=z+3; /*tính biểu thức lặp*/ 
 DBMS_OUTPUT.PUT_LINE(z); 
 END LOOP; 
 END; 
Chương 4: Lập trình PL/SQL 28 
 Cấu trúc điều khiển (tt) 
 5. Sử dụng tên nhãn và lệnh GOTO 
 - Một tên nhãn được dùng để đặt tên cho một khối 
lệnh PL/SQL hoặc các câu lệnh bên trong khối. 
 - Tên nhãn được định nghĩa bằng cách sử dụng dấu 
nhọn >. 
 - Câu lệnh GOTO rẽ nhánh không điều kiện đến một 
nhãn. Khi thực hiện, câu lệnh GOTO thay đổi luồng điều 
khiển trong một khối để chuyển đến thực hiện lệnh nằm 
trong nhãn. 
 - GOTO không được phép trong một số trường hợp: 
  Từ một xử lý ngoại lệ vào trong khối hiện hành. 
  Nhảy ra ngoài chương trình con. 
Chương 4: Lập trình PL/SQL 29 
 Cấu trúc điều khiển – Goto (tt) 
 Ví dụ: 
 BEGIN 
 > 
 declare 
 Begin 
 GOTO inner_block 
 > --Tên nhãn 
 declare 
 Begin 
 End; /*End của >*/ 
 End; /*End của >*/ 
 END; 
Chương 4: Lập trình PL/SQL 30 
 Cấu trúc điều khiển (tt) 
 6. Xử lý ngoại lệ - EXCEPTION 
 Khi một lỗi phát sinh, một ngoại lệ được đưa ra, 
việc thực hiện chương trình bình thường được dừng 
lại và điều khiển được chuyển tới khối PL/SQL chứa 
phần xử lý ngoại lệ. 
 Những ngoại lệ bên trong được sinh ra một cách 
tiềm ẩn (không tường minh, implicit), trái lại những 
ngoại lệ do người dùng định nghĩa được sinh ra một 
cách tường minh (explicit) bằng cách sử dụng câu 
lệnh RAISE. 
 VD: Nếu chia một số cho zero, một ngoại lệ do 
Oracle định nghĩa trước (ví dụ: ZERO_DIVIDE) sẽ tự 
động sinh ra. 
 Chương 4: Lập trình PL/SQL 31 
 Cấu trúc điều khiển – Exception (tt) 
Định nghĩa ngoại lệ: 
DECLARE 
/*nếu là Block ngoài cùng của Function hoặc Procedure thì không dùng 
 Declare*/ 
 loi_ngoai_le EXCEPTION; 
 . 
BEGIN 
 IF then 
 RAISE loi_ngoai_le; /*bật ngoại lệ*/ 
 END IF ; 
EXCEPTION 
 WHEN loi_ngoai_le then 
 .. 
 WHEN OTHERS then 
 . 
END; 
 Chương 4: Lập trình PL/SQL 32 
 Cấu trúc điều khiển – Exception (tt) 
 Ví dụ: (ta quy định trả về 1 nếu trùng mã số, trả về 2: bình thường) 
Create Function Test_Exception (maso number) return number As 
 trung_ma_so EXCEPTION; /*khai báo 1 ngoại lệ tên “trung_ma_so”*/ 
BEGIN 
 IF maso=„A100‟ then 
 RAISE trung_ma_so; 
 /*đây là ngoại lệ tường minh bắt ngoại lệ bằng từ khóa RAISE*/ 
 ELSE 
 return 2; 
 END IF ; 
EXCEPTION 
 WHEN trung_ma_so then 
 return 1; /*đã có mã số này rồi*/ 
 WHEN OTHERS then 
 /*sử dụng từ khóa OTHERS cho các lỗi khác past_due, việc sử dụng OTHERS 
 đảm bảo không có ngoại lệ nào sẽ không được xử lý*/ 
 return 0; /*lỗi phát sinh*/ END; 
Chương 4: Lập trình PL/SQL 33 
 Cấu trúc điều khiển – Exception (tt) 
 Các lỗi ngoại lệ do Oracle định nghĩa 
Ngoại lệ Điều kiện khi ngoại lệ xảy ra 
CURSOR_ALREADY_OPEN Mở một cursor, mà cursor đó đã ở trạng thái đang 
 mở. 
DUP_VAL_ON_INDEX Khi có thao tác INSERT hoặc UPDATE vi phạm 
 ràng buộc UNIQUE. 
INVALID_CURSOR Mở một cursor chưa tạo hoặc đóng một cursor mà 
 nó chưa được mở. 
INVALID_NUMBER Lỗi chuyển kiểu dữ liệu từ string sang kiểu 
 number. 
LOGIN_DENIED Đăng nhập sai username/password. 
NO_DATA_FOUND Câu lệnh SELECT INTO không trả về dòng nào. 
NOT_LOGGED_ON Một chương trình PL/SQL cần thao tác đến CSDL 
 Oracle nhưng lại chưa đăng nhập vào CSDL. 
PROGRAM_ERROR Một số lỗi chương trình, ví dụ một hàm (function) 
 không chứa mệnh đề RETURN trả về giá trị. 
Chương 4: Lập trình PL/SQL 34 
 Cấu trúc điều khiển – Exception (tt) 
 Các lỗi ngoại lệ do Oracle định nghĩa (tt) 
Ngoại lệ Điều kiện khi ngoại lệ xảy ra 
STORAGE_ERROR Lỗi bộ nhớ 
TIMEOUT_ON_RESOURCE Lỗi timeout xảy ra khi Oracle đang chờ tài nguyên 
TOO_MANY_ROWS Câu lệnh SELECT INTO trả về nhiều hơn một 
 dòng 
VALUE_ERROR Lỗi chuyển kiểu dữ liệu hoặc thao tác vi phạm 
 ràng buộc toàn vẹn (RBTV) 
ZERO_DIVIDE Lỗi chia một số cho zero 
Chương 4: Lập trình PL/SQL 35 
 Cấu trúc điều khiển – Exception (tt) 
 Ví dụ ngoại lệ ZERO_DIVIDE do Oracle định nghĩa: 
 Create Function Test_Exception (so number) 
 return number As 
 x number(4,2); 
 Begin 
 x:=100/so; Chạy lệnh sau trong 
 return 1; SQL*Plus để thấy kết quả xử 
 lý 2 trường hợp khác nhau: 
 EXCEPTION 
 Select Test_Exception (5) 
 WHEN ZERO_DIVIDE then From Dual; 
 /*lỗi do Oracle định nghĩa*/ /*Dual là table tạm*/ 
 return 0; /*Lỗi chia zero*/ Hoặc: 
 END; Select Test_Exception (0) 
 From Dual; 
Chương 4: Lập trình PL/SQL 36 
 Cấu trúc điều khiển – Exception (tt) 
 Ví dụ ngoại lệ NO_DATA_FOUND do Oracle định nghĩa: 
 DECLARE 
 Sinhvien_Row SINHVIEN%ROWTYPE; 
 Masv_t Char(5); 
 BEGIN 
 Masv_t := 'A2345'; 
 select * into Sinhvien_Row 
 from SINHVIEN where masv = masv_t; 
 DBMS_OUTPUT.PUT_LINE(Sinhvien_Row.HotenSV); 
 EXCEPTION 
 WHEN NO_DATA_FOUND then 
 DBMS_OUTPUT.PUT_LINE('khong co sv nay'); 
 END; 
Chương 4: Lập trình PL/SQL 37 
 VI. Kiểu con trỏ 
 1. Giới thiệu 
 Con trỏ (cursor) là một đối tượng liên kết với một 
tập dữ liệu và cho phép người lập trình làm việc với 
từng dòng của tập dữ liệu đó. 
 Cho phép xử lý dữ liệu gồm nhiều dòng. Số dòng 
được lấy ra xử lý phụ thuộc vào lệnh xử lý sau đó. 
 Trong xử lý, các thao tác sẽ tác động lên từng 
mẩu tin của dữ liệu đã được nạp vào con trỏ. 
 Có hai loại con trỏ: con trỏ đuợc khai báo tường 
minh (explicit cursor) và con trỏ không được khai 
báo tường minh (hay còn gọi là con trỏ tiềm ẩn 
(implicit cursor)). 
 Chương 4: Lập trình PL/SQL 38 
 Con trỏ (tt) 
 Con trỏ tiềm ẩn: một lệnh SQL được xử lý bởi Oracle và 
không được đặt tên bởi người sử dụng. 
 Các lệnh SQL được thực hiện trong một con trỏ tiềm ẩn 
bao gồm UPDATE, INSERT, DELETE. 
 Ví dụ: 
 Khối lệnh 
 . 
 INSERT INTO KHOA (MAKH, TENKH) 
 VALUES (‘TH’, ‘Tin hoc’) 
 Con trỏ tường minh: Là con trỏ được đặt tên bởi người 
sử dụng (câu SELECT được đặt tên). 
 Ví dụ: 
 CURSOR c_kh IS 
 SELECT MAKH, TENKH FROM KHOA 
Chương 4: Lập trình PL/SQL 39 
 Con trỏ (tt) 
 2. Khai báo cursor (khai báo con trỏ) 
 Các bước thực hiện trên con trỏ: 
 Khai báo -> mở cursor -> lấy dữ liệu để xử lý 
 -> đóng cursor 
 Cú pháp: 
 --Khai báo con trỏ 
 CURSOR tên_cursor IS Câu_SELECT; 
 --Mở con trỏ 
 OPEN [ [( )] ]; 
 --Lấy dữ liệu 
 FETCH INTO ; 
 --Ðóng con trỏ 
 CLOSE ; 
Chương 4: Lập trình PL/SQL 40 
 Con trỏ (tt) 
 3. Thuộc tính con trỏ tường minh 
 Mọi con trỏ khai báo tường minh đều có 4 thuộc 
tính: %NOTFOUND, %FOUND, %ROWCOUNT, 
%ISOPEN. Các thuộc tính này được thêm vào sau 
phần tên của con trỏ. 
Thuộc tính Diễn giải 
%ISOPEN TRUE nếu con trỏ đang mở, ngược lại trả 
 về FALSE. 
%NOTFOUND Trả về TRUE khi đã fetch đến dòng cuối 
 cùng của con trỏ, ngược lại bằng FALSE. 
%FOUND Ngược lại với %NotFound 
%ROWCOUNT Trả về số dòng của con trỏ đã được fetch. 
Chương 4: Lập trình PL/SQL 41 
 Con trỏ (tt) 
 Ví dụ 1 con trỏ: in danh sách đề tài 
 DECLARE 
 CURSOR C_DETAI IS --1. Khai báo con trỏ 
 SELECT MADT, TENDT, CHUNHIEM, KINHPHI 
 FROM DETAI; 
 V_DETAI C_DETAI%ROWTYPE; --Khai báo biến con trỏ truy cập 
 BEGIN --từng mẩu tin 
 OPEN C_DETAI; --2. Mở con trỏ 
 DBMS_OUTPUT.PUT_LINE('MADT | TENDT | CHUNHIEM| KINHPHI'); 
 LOOP 
 FETCH C_DETAI INTO V_DETAI; --3. Lấy mẩu con trỏ 
 EXIT WHEN C_DETAI%NOTFOUND; --Thoát khỏi vòng lặp khi ctrỏ rỗng 
 DBMS_OUTPUT.PUT_LINE(V_DETAI.MADT || '|' 
 || V_DETAI.TENDT || '|' 
 || V_DETAI.CHUNHIEM || '|' 
 || V_DETAI.KINHPHI); 
 END LOOP; 
 CLOSE C_DETAI; --4. Đóng con trỏ 
 END; 
Chương 4: Lập trình PL/SQL 42 
 Con trỏ (tt) 
 Vd2: Thuộc tính %ROWCOUNT 
 UPDATE DETAI 
 SET KINHPHI=30 
 WHERE MADT='DT003'; 
 IF SQL%ROWCOUNT >0 THEN 
 DBMS_OUTPUT.PUT_LINE('KINH PHI MOI'); 
 END IF; 
 Vd3: Thuộc tính %NOTFOUNT 
 DELETE FROM DETAI 
 WHERE MADT=‘DT222'; 
 IF SQL%NOTFOUND THEN 
 DBMS_OUTPUT.PUT_LINE ('Khong co de tai 
 DT222'); 
 END IF; 
Chương 4: Lập trình PL/SQL 43 
 Con trỏ (tt) 
 SELECT FOR UPDATE 
 Cho phép khóa các mẫu tin đang truy xuất để tiến 
hành xử lý. 
 Các mẫu tin tự động bị khóa khi mở Cursor và khóa 
khi được COMMIT/ ROLLBACK 
 Cú pháp: SELECT  FROM 
 FOR UPDATE [OF ]; 
 WHERE CURRENT OF 
 Tiếp nối mệnh đề FOR UPDATE, Chỉ định câu lệnh 
update/delete thay đổi dữ liệu trên những dòng của 
cursor hiện hành có định nghĩa FOR UPDATE. 
 Cú pháp: UPDATE [| DELETE ] 
 WHERE CURRENT OF 
Chương 4: Lập trình PL/SQL 44 
 Con trỏ (tt) 
 VD minh hoạ: 
 DECLARE 
 CURSOR C_SINHVIEN IS 
 SELECT MASV, HOTENSV 
 FROM SINHVIEN 
 FOR UPDATE OF HOTENSV; 
 --- 
 UPDATE SINHVIEN 
 SET HOTENSV = ‘Nguyen Van An’ 
 WHERE CURRENT OF C_SINHVIEN 
 --- 
Chương 4: Lập trình PL/SQL 45 
 VII. Bài tập 
A. Viết chương trình tính tổng từ 1 n, với n nhập vào. 
B. Viết khối PL/SQL cho phép: 
 1. Hiển thị họ tên cán bộ, chức vụ và hệ số, với mã số cán bộ 
 được nhập từ bàn phím 
 2. Tăng hệ số cho những cán bộ: 
 • Nếu có chức vụ thì tăng 0.5 
 • Ngược lại thì không tăng. 
 3. Xuất thông báo khi không có cán bộ có hệ số trên 4.0 
Chương 4: Lập trình PL/SQL 46 
 Tóm tắt chương 
 - Tìm hiểu cấu trúc chương trình PL/SQL 
 - Các mệnh đề trong PL/SQL 
 - Cấu trúc điều khiển IF 
 - Cấu trúc vòng lặp loop, for, while 
 - Lập trình con trỏ PL/SQL 
Chương 4: Lập trình PL/SQL 47