Bài giảng Hệ quản trị cơ sơ dữ liệu oracle - Chương 7: Ngôn ngữ PL/SQL - Ngô Thùy Linh

Khoa HTTTQL – Học viện Ngân hàng 
Chương 7 
NGÔN NGỮ PL/SQL 
Giảng viên: Ngô Thùy Linh 
Khoa HTTTQL – Học viện Ngân hàng 
Procedural Language Extension to SQL 
Khoa HTTTQL – Học viện Ngân hàng 7-3 
Tài liệu tham khảo 
Khoa HTTTQL – Học viện Ngân hàng 7-4 
Nội dung chính 
 Giới thiệu về ngôn ngữ PL/SQL 
 Cấu trúc khối lệnh của PL/SQL 
 Khai báo, phép gán, các phép toán 
 Cấu trúc điều khiển: điều kiện rẽ nhánh, lặp 
 Con trỏ 
 Hàm 
 Thủ tục 
 Package 
 Trigger 
Khoa HTTTQL – Học viện Ngân hàng 7-5 
Khoa HTTTQL – Học viện Ngân hàng 7-6 
PL/SQL là gì? 
• SQL: Structure Query Language 
• SQL chưa đủ mạnh để lập trình 
• PL/SQL: Procedural Language extensions for SQL 
• Điểm mạnh của PL/SQL: 
– Tích hợp cấu trúc hướng thủ tục vào SQL 
– Tăng hiệu năng xử lý 
– Module hóa chương trình 
– Khả chuyển 
– Có cơ chế xử lý ngoại lệ 
Khoa HTTTQL – Học viện Ngân hàng 7-7 
Cách thực thi các lệnh PL/SQL 
PL/SQL Engine 
Oracle Database Server 
SQL Statement 
Executor 
Procedural 
Statement 
Executor 
procedural 
SQL 
PL/SQL 
Block 
Khoa HTTTQL – Học viện Ngân hàng 7-8 
The PL/SQL Engine and Oracle Database 
Khoa HTTTQL – Học viện Ngân hàng 7-9 
Giới thiệu về ngôn ngữ PL/SQL 
 PL/SQL = SQL chuẩn + thành phần Oracle mở rộng 
 Đặc điểm 
• Đơn vị mã lệnh PL/SQL là dạng khối (Begin End). 
• Mỗi lệnh kết thúc bằng dấu ; 
• Các lệnh DDL, DCL không được sử dụng trong PL/SQL 
Khoa HTTTQL – Học viện Ngân hàng 7-10 
Cấu trúc khối lệnh PL/SQL 
 DECLARE – Optional 
• Variables, cursors, user – defined exceptions 
 BEGIN – Mandatory 
• SQL - statements 
• PL/SQL – statements 
 EXCEPTION – Optional 
• Actions to perform when errors occur 
 END; - Mandatory 
Chú thích một dòng bắt đầu bởi - - 
Chú thích nhiều dòng được đặt giữa /* và */ 
Khoa HTTTQL – Học viện Ngân hàng 7-11 
Cấu trúc khối lệnh PL/SQL 
Khoa HTTTQL – Học viện Ngân hàng 7-12 
Cấu trúc khối lệnh PL/SQL 
 Ví dụ: hiển thị dòng chữ chao cac ban !!! 
set serveroutput on 
 begin 
 dbms_output.put_line('chao cac ban !!!'); 
 end; 
Khoa HTTTQL – Học viện Ngân hàng 7-13 
Các kiểu khối lệnh 
[DECLARE] 
BEGIN 
 --statements 
[EXCEPTIONS] 
END; 
PROCEDURE name 
 IS 
BEGIN 
 --statements 
[EXCEPTIONS] 
END; 
FUNCTION name 
RETURN datatype 
 IS 
BEGIN 
 --statements 
 RETURN VALUE; 
[EXCEPTIONS] 
END; 
Anonymous Procedure Functions 
Khoa HTTTQL – Học viện Ngân hàng 7-14 
Khoa HTTTQL – Học viện Ngân hàng 7-15 
Làm việc với biến trong PL/SQL 
 Khai báo và khởi tạo biến trong Declare 
 Gán giá trị trong phần thực thi 
 Hai biến có thể cùng tên nếu chúng ở trong các 
khối lệnh khác nhau 
 Tên biến không nên trùng với tên bảng, tên cột 
trong cùng khối lệnh 
 Tên biến bắt đầu bởi ký tự (≤ 30 ký tự) 
Khoa HTTTQL – Học viện Ngân hàng 7-16 
Biến (variable) 
 Đặc điểm của biến: 
• Lưu trữ dữ liệu tạm thời 
• Cho phép sửa dữ liệu 
• Cho phép tái sử dụng 
Biến 
là gì? 
Khoa HTTTQL – Học viện Ngân hàng 7-17 
Quy tắc đặt tên biến 
 Bắt đầu bằng chữ cái 
 Có thể chứa cả số và chữ cái 
 Có thể chứa kí tự đặc biệt: dấu $, _, # (hạn chế 
dùng $) 
 Tối đa 30 kí tự 
 Không trùng với từ khóa mà Oracle sử dụng, ví 
dụ: varchar, table 
Khoa HTTTQL – Học viện Ngân hàng 7-18 
 Khai báo biến 
Syntax 
Examples 
variable_name [CONSTANT] datatype [NOT NULL] 
 [:= | DEFAULT expr]; 
Declare 
 birthday DATE; 
 age NUMBER(2) NOT NULL := 27; 
 name VARCHAR2(13) := 'Levi'; 
 magic CONSTANT NUMBER := 77; 
 valid BOOLEAN NOT NULL := TRUE; 
Notice that PL/SQL includes all 
SQL types, and more 
Khoa HTTTQL – Học viện Ngân hàng 7-19 
Các loại biến 
 Biến PL/SQL 
• Vô hướng (Scalar): Number (n,m) , Char (n) , 
Varchar2 (n), Boolean,... 
• Phức hợp (Composite) 
• Tham chiếu (Reference) 
• LOB (Large OBjects) 
 Biến Non-PL/SQL 
• Bind 
• Host 
Khoa HTTTQL – Học viện Ngân hàng 7-20 
Các kiểu dữ liệu trong PL/SQL 
Khoa HTTTQL – Học viện Ngân hàng 7-21 
INPUT / OUTPUT 
 Output 
• DBMS_OUTPUT.PUT_LINE (‘ .’) ; 
• Đây là một thủ tục nằm trong một gói được cung cấp 
sẵn của Oracle 
• Chú ý: Muốn lệnh này có hiệu lực thì phải chạy: 
• SET SERVEROUTPUT ON 
 Input 
• Sử dụng &: đặt trước tên biến, biến được nhập giá trị 
lúc thực thi câu lệnh SQL. Ví dụ x:=&x 
Khoa HTTTQL – Học viện Ngân hàng 7-22 
Kiểu dữ liệu 
 Kiểu ký tự: 
• Char(): 1byte 2000 byte 
• Varchar2(): 4000 byte 
• Nvarchar2(): 4000 byte 
 Kiểu số: 
• Number(p,s) 
• p is the precision (1 38) and s (-84 127) is the 
scale 
 Kiểu ngày tháng 
• Date: Jan 1, 4712 BC Dec 31, 9999 AD 
Khoa HTTTQL – Học viện Ngân hàng 7-23 
Kiểu dữ liệu (tt) 
 Kiểu số: Number(p,s) 
• Number: có thể lưu trữ bất cứ giá trị nào 
• Number(6): lưu trữ tối đa số có 6 chữ số 
• Number(5, 2): -999.99 999.99 
• Number(5, -2): số nguyên tối đa có (5-(-2)) chữ số 
và có 2 chữ số cuối là 0 
• Number(3, 8): -0.00000999 0.00000999 
8-3 3 
Khoa HTTTQL – Học viện Ngân hàng 7-24 
Gán giá trị 
 Có thể gán giá trị theo 2 cách 
• Gán trực tiếp: 
• Gán “gián tiếp”: lấy dữ liệu từ Database với lệnh sau 
SELECT column_name1,.. 
INTO variable_name1,... 
FROM table_name 
[WHERE condition]; 
variable_name := value ; 
Chú ý: Toán tử gán trong PL/SQL là := 
 Toán tử gán trong các lệnh SQL là = 
Khoa HTTTQL – Học viện Ngân hàng 7-25 
Nhận và hiển thị giá trị biến 
(Kiểu số) 
 Cách 1: Ví dụ 1 
Khoa HTTTQL – Học viện Ngân hàng 7-26 
Nhận và hiển thị giá trị biến (tt) 
(Kiểu số) 
 Cách 1: Ví dụ 2 
Khoa HTTTQL – Học viện Ngân hàng 7-27 
Nhận và hiển thị giá trị biến (tt) 
(Kiểu số) 
 Cách 2 
Khoa HTTTQL – Học viện Ngân hàng 7-28 
Nhận và hiển thị giá trị biến (tt) 
(Kiểu số) 
 Cách 3 
Khoa HTTTQL – Học viện Ngân hàng 7-29 
Nhận và hiển thị giá trị biến 
(Kiểu ký tự) 
 Ví dụ 1 
Khoa HTTTQL – Học viện Ngân hàng 7-30 
Nhận và hiển thị giá trị biến 
(Kiểu ký tự)... 
 Ví dụ 2: 
Khoa HTTTQL – Học viện Ngân hàng 7-31 
Nhận và hiển thị giá trị biến 
(Kiểu ký tự)... 
 Ví dụ 3 
Khoa HTTTQL – Học viện Ngân hàng 7-32 
 Ví dụ 1 
Nhận và hiển thị giá trị biến 
(Kiểu ngày tháng) 
Khoa HTTTQL – Học viện Ngân hàng 7-33 
 Ví dụ 2 
Nhận và hiển thị giá trị biến 
(Kiểu ngày tháng)... 
Khoa HTTTQL – Học viện Ngân hàng 7-34 
 Ví dụ 3 
Khoa HTTTQL – Học viện Ngân hàng 7-35 
Khoa HTTTQL – Học viện Ngân hàng 7-36 
Tương tác với Oracle Server 
 Lấy ra một dòng dữ liệu từ Database với câu lệnh 
SELECT 
 Thay đổi dữ liệu trong Database với các lệnh DML 
 Con trỏ 
Khoa HTTTQL – Học viện Ngân hàng 7-37 
Lấy dữ liệu từ Database với lệnh SELECT 
 Gán gián tiếp giá trị cho biến 
SELECT tên_cột_1, tên_cột_2,  
INTO tên_biến_1, tên_biến_2, 
FROM tên_bảng 
[WHERE điều kiện]; 
 Mệnh đề INTO là bắt buộc 
 Truy vấn chỉ được trả về 1 và chỉ 1 dòng 
Khoa HTTTQL – Học viện Ngân hàng 7-38 
 Ví dụ: dữ liệu của bangcha 
anonymous block completed 
0903-002-004 
Nguoi Cha ma so 10 co so dien thoai la 0903-002-004 
Khi chạy khối lệnh trên 
Khoa HTTTQL – Học viện Ngân hàng 7-39 
 Số cán bộ ở Hà nội? 
Lấy dữ liệu từ Database với lệnh SELECT (tt) 
Khoa HTTTQL – Học viện Ngân hàng 7-40 
Phạm vi của biến 
 Biến địa phương (Local variables) 
• These are declared in a inner block and cannot be 
referenced by outside Blocks. 
 Biến toàn cục (Global variables) 
• These are declared in a outer block and can be 
referenced by its itself and by its inner blocks. 
Khoa HTTTQL – Học viện Ngân hàng 7-41 
Phép toán 
 Toán học: +, -, *, /, ** (lũy thừa mũ) 
 So sánh: =, , !=, ~=, , =, LIKE, 
BETWEEN, IN, IS NULL 
 Logic: AND, OR, NOT 
 Phép toán liên quan đến chuỗi 
Khoa HTTTQL – Học viện Ngân hàng 7-42 
Thuộc tính %TYPE, %ROWTYPE 
 %TYPE 
• Khai báo biến dựa vào: 
– Một định nghĩa cột trong Database 
– Một biến khác đã được định nghĩa 
• Đặt trước %TYPE 
– Tên của bảng và cột trong Database 
– Tên của biến đã được định nghĩa trước 
 % ROWTYPE 
• Dùng để khai báo một biến mà nó có cấu trúc như 
một dòng trong table 
Khoa HTTTQL – Học viện Ngân hàng 7-43 
Khai báo biến với %TYPE 
Examples 
DECLARE 
 v_sname Sailors.sname%TYPE; 
 fav_boat VARCHAR2(30); 
 my_fav_boat fav_boat%TYPE := 'Pinta'; 
... 
Accessing column sname in table Sailors 
Accessing another variable 
Khoa HTTTQL – Học viện Ngân hàng 7-44 
Khai báo biến với %ROWTYPE 
Declare a variable with the type of a ROW of a table. 
And how do we access the fields in reserves_record? 
reserves_record Reserves%ROWTYPE; 
reserves_record.sid:=9; 
Reserves_record.bid:=877; 
Accessing table 
Reserves 
Khoa HTTTQL – Học viện Ngân hàng 7-45 
Lấy dữ liệu trong PL/SQL 
 Ví dụ: In ra Tổng lương của tất cả các cán bộ, Họ 
tên, quê quán và lương của cán bộ có mã cb8 
Khoa HTTTQL – Học viện Ngân hàng 7-46 
Lấy dữ liệu trong PL/SQL 
 ... 
Khoa HTTTQL – Học viện Ngân hàng 7-47 
 Lấy ra thông tin cán bộ có mã số cb2 
Lấy dữ liệu trong PL/SQL 
 ... 
Khoa HTTTQL – Học viện Ngân hàng 7-48 
Thao tác dữ liệu sử dụng PL/SQL 
 Thay đổi dữ liệu trong bảng bằng các lệnh DML : 
• INSERT 
• UPDATE 
• DELETE 
INSERT 
UPDATE 
DELETE 
Khoa HTTTQL – Học viện Ngân hàng 7-49 
Chèn dữ liệu 
 Thêm cán bộ vào bảng cán bộ 
 Các cán bộ cùng làm đề tài mã số dt5 
Khoa HTTTQL – Học viện Ngân hàng 7-50 
Cập nhật dữ liệu 
 Tăng lương cho các nhân viên làm đề tài có mã số là dt5 
Khoa HTTTQL – Học viện Ngân hàng 7-51 
Xóa dữ liệu 
 Xóa cán bộ có mã số là cb17 
Khoa HTTTQL – Học viện Ngân hàng 7-52 
Khoa HTTTQL – Học viện Ngân hàng 7-53 
Cấu trúc điều khiển 
 Rẽ nhánh 
• IF THENELSE 
• CASEWHEN 
 Lặp 
• Vòng lặp đơn giản 
• Vòng lặp WHILE 
• Vòng lặp FOR 
• GOTO 
for 
loop 
while 
Khoa HTTTQL – Học viện Ngân hàng 7-54 
Rẽ nhánh 
IFTHENELSE CASEWHEN 
IF condition THEN 
 statements; 
[ELSIF condition THEN 
 statements;] 
[ELSE 
 statements;] 
END IF; 
CASE selector 
 WHEN expression1 THEN result1 
 WHEN expression2 THEN result2 
 ... 
 WHEN expressionN THEN resultN 
 [ELSE resultN+1] 
END; 
Khoa HTTTQL – Học viện Ngân hàng 7-55 
Các lệnh điều khiển luồng chương trình 
 Lệnh IF 
Khoa HTTTQL – Học viện Ngân hàng 7-56 
Lệnh IF 
 Ví dụ 1: Nhập n từ bàn phím (nhỏ hơn 1000), đưa 
ra thông báo n là số chẵn hay lẻ 
Khoa HTTTQL – Học viện Ngân hàng 7-57 
Lệnh IF (tt) 
 Ví dụ 2: tăng thêm lương cho cán bộ có mã số cb1 là 
200000đ nếu thưởng tết âm lịch lớn hơn tổng thưởng của tết 
dương lịch và thưởng ngày 30/4. Ngược lại tăng thêm 
400000đ 
Khoa HTTTQL – Học viện Ngân hàng 7-58 
Lệnh IF (tt) 
Khoa HTTTQL – Học viện Ngân hàng 7-59 
Lệnh IF (tt) 
 Ví dụ 3: Nhập một ký tự từ bàn phím, nếu đó là A thì đưa 
ra thông báo: Tuyet voi, là B thì: Rat tot, là C thì: Tot, là D 
thì: Binh thuong. Các ký tự khác thì: Chua co thong tin.. 
Khoa HTTTQL – Học viện Ngân hàng 7-60 
Khoa HTTTQL – Học viện Ngân hàng 7-61 
CASE-WHEN 
Khoa HTTTQL – Học viện Ngân hàng 7-62 
 Điều khiển lặp 
• Vòng lặp cơ bản 
• Vòng lặp FOR 
• Vòng lặp WHILE 
Các lệnh điều khiển luồng chương trình 
Khoa HTTTQL – Học viện Ngân hàng 7-63 
Ví dụ 1: Lệnh LOOP 
Result 
Khoa HTTTQL – Học viện Ngân hàng 7-64 
Ví dụ 2: Lệnh LOOP 
Result 
Khoa HTTTQL – Học viện Ngân hàng 7-65 
Loops: Simple Loop 
DECLARE 
 v_id number_table.id%TYPE := 100; 
 v_num NUMBER:=1; 
BEGIN 
 LOOP 
 INSERT INTO number_table(id,num) 
 VALUES(v_id,v_num); 
 v_num := v_num + 1; 
 EXIT WHEN v_num > 10; 
 END LOOP; 
END; 
create table number_table 
 (id NUMBER, 
 num NUMBER); 
Khoa HTTTQL – Học viện Ngân hàng 7-66 
Loops: FOR Loop 
DECLARE 
 v_id number_table.id%TYPE:=101; 
 v_num number_table.num%TYPE; 
BEGIN 
 FOR v_num IN 1..10 LOOP 
 INSERT INTO number_table(id,num) 
 VALUES(v_id, v_num); 
 END LOOP; 
END; 
Notice that v_num is incremented automatically 
Khoa HTTTQL – Học viện Ngân hàng 7-67 
Loops: WHILE Loop - 1 
DECLARE 
 TEN number:=10; 
 v_id number_table.id%TYPE:=102; 
 v_num number_table.num%TYPE:=1; 
BEGIN 
 WHILE v_num <= TEN LOOP 
 INSERT INTO number_table(id,num) 
 VALUES(v_id, v_num); 
 v_num := v_num + 1; 
 END LOOP; 
END; 
Khoa HTTTQL – Học viện Ngân hàng 7-68 
Loops: WHILE Loop - 2 
ACCEPT v_id PROMPT 'Nhap gia tri ma id : ' 
ACCEPT nums PROMPT 'Nhap so luong ban ghi: ' 
declare 
 v_num number_table.num%TYPE :=1; 
begin 
 while v_num < &nums loop 
 insert into number_table(id,num) 
 VALUES(&v_id, v_num); 
 v_num := v_num + 1; 
 end loop; 
end; 
Khoa HTTTQL – Học viện Ngân hàng 7-69 
Loops: WHILE Loop - 3 
declare 
 v_iidd number; numss number; 
 v_num number_table.num%TYPE :=1; 
begin 
 v_iidd:=&v_iidd; numss:=&numss; 
 while v_num < numss loop 
 insert into number_table(id,num) 
 values(v_iidd, v_num); 
 v_num := v_num + 1; 
 end loop; 
end; 
Khoa HTTTQL – Học viện Ngân hàng 7-70 
Khoa HTTTQL – Học viện Ngân hàng 7-71 
Cursor 
 Cursor? 
• Cursors are memory areas where Oracle executes 
SQL statements, stores processing information 
 Select  
From 
Where 
SERVER RAM 
Active Data Set 
Contents of a Cursor 
Cursor Current Row 
Khoa HTTTQL – Học viện Ngân hàng 7-72 
Cursor 
 When? 
• We create a Cursor (is a variable) when we want to 
go over a result of a query. 
 Cursors have two important features: 
• Cursors allow you to fetch and process rows 
returned by a SELECT statement one row at a time. 
• A cursor is named so that it can be referenced. 
Loại cursor Mô tả 
Implicit Implicit cursor được khai báo một cách ngầm 
định bởi PL/SQL cho các lệnh DML và lệnh 
SELECT chỉ trả về 1 dòng 
Explicit Dùng với các truy vấn trả về nhiều hơn một 
dòng. Explicit cursor được khai báo và đặt tên 
bởi lập trình viên và thao tác qua các lệnh nhất 
định trong phần thực thi của khối lệnh 
Khoa HTTTQL – Học viện Ngân hàng 7-73 
Con trỏ không tường minh 
(IMPLICIT CURSOR) 
 Là con trỏ PL/SQL tự động sinh ra khi gặp câu 
lệnh SELECT hoặc DML 
 User chỉ có thể lấy thông tin của con trỏ 
• SQL%ISOPEN: Trả về FALSE 
• SQL%FOUND: Trả về NULL/TRUE/ FALSE 
• SQL%NOTFOUND: Trả về NULL/TRUE/ FALSE 
• SQL%ROWCOUNT: Trả về NULL, số lượng bản ghi 
tác động bởi DML hoặc SELECT 
Khoa HTTTQL – Học viện Ngân hàng 7-74 
IMPLICIT CURSOR 
 Ví dụ 1: 
Khoa HTTTQL – Học viện Ngân hàng 7-75 
IMPLICIT CURSOR(tt) 
 Ví dụ 2 
number_table 
Khoa HTTTQL – Học viện Ngân hàng 7-76 
Con trỏ tường minh 
(Explicit Cursor) 
 Explicit Cursor 
• Được sử dụng để xử lý từng dòng trả về từ câu lệnh 
SELECT trả về nhiều dòng 
• Chương trình PL/SQL cho phép mở cursor, xử lý 
các dòng trả về, đóng cursor lại. Cursor đánh dấu vị 
trí hiện tại trong active set 
• Chức năng: 
– Xử lý lần lượt từng dòng trả về bởi câu lệnh truy vấn trả 
về nhiều dòng 
– Luôn trỏ tới dòng đang được xử lý 
– Cho phép lập trình viên điều khiển trong khối lệnh 
PL/SQL 
Khoa HTTTQL – Học viện Ngân hàng 7-77 
Làm việc với Explicit Cursor 
 Explicit Cursor 
DECLARE OPEN FETCH EMPTY ? CLOSE 
• Create a 
named SQL 
area 
YES? 
NO 
•Identify 
the active 
set 
•Load the 
current 
row into 
variables 
•Release 
the 
active 
set 
Khoa HTTTQL – Học viện Ngân hàng 7-78 
Điều khiển Explicit Cursors (tt) 
Fetch a row. 
Close the cursor. 
Cursor 
pointer 
 Open the cursor. 1 
2 
3 
Cursor 
pointer 
Cursor 
pointer 
Khoa HTTTQL – Học viện Ngân hàng 7-79 
Example 
DECLARE 
 Pi constant NUMBER(8,7) := 3.1415926; 
 area NUMBER(14,2); 
 CURSOR rad_cursor IS 
 select * 
 from RAD_VALS; 
 rad_val rad_cursor%ROWTYPE; 
BEGIN 
 OPEN rad_cursor; 
 FETCH rad_cursor INTO rad_val; 
 area:=pi*power(rad_val.radius,2); 
 insert into AREAS values (rad_val.radius, area); 
 CLOSE rad_cursor; 
END; 
radius 
3 
6 
8 
Rad_cursor 
f
e
t
c
h 
Rad_val 
Radius Area 
AREAS 
3 28.27 
RAD_VALS 
Khoa HTTTQL – Học viện Ngân hàng 7-80 
Bảng AREAS 
Khoa HTTTQL – Học viện Ngân hàng 7-81 
Các thuộc tính của Explicit Cursor 
Chứa thông tin về cursor: 
Thuộc tính Kiểu Mô tả 
%ISOPEN Boolean Là TRUE nếu cursor đã được mở 
%NOTFOUND Boolean Là TRUE nếu lệnh lấy dữ liệu gần 
đây nhất không trả về dòng 
%FOUND Boolean Là TRUE nếu lệnh lấy dữ liệu gần 
đây nhất trả về dữ liệu 
%ROWCOUNT Number Là tổng số dòng dữ liệu đã trả về 
thời điểm hiện tại 
Khoa HTTTQL – Học viện Ngân hàng 7-82 
 Explicit Cursor 
 Ví dụ 1: Lấy id và num của bảng number_table 
và chèn vào bảng temp_list 
number_table 
RESULT 
temp_list 
Khoa HTTTQL – Học viện Ngân hàng 7-83 
Explicit Cursor 
 Ví dụ 2: Lấy thông tin trong bảng number_table 
và in ra màn hình 
Result 
number_table 
Chú ý: vòng lặp FOR 
1. Open, Fetch, Close Cursor thực hiện ngầm định 
2. Biến lấy giá trị của Cursor cũng được khai báo 
ngầm định 
Khoa HTTTQL – Học viện Ngân hàng 7-84 
Explicit Cursor 
 Ví dụ 3: cho bảng table_num 
 Hãy in ra các dòng có mã số từ 3 đến 7 
Khoa HTTTQL – Học viện Ngân hàng 7-85 
Khoa HTTTQL – Học viện Ngân hàng 7-86 
Mục đích 
 Mô đun hóa việc viết chương trình ứng dụng: 
• Nhóm các lệnh có liên hệ locgic trong một khối 
• Các khối lệnh có thể lồng nhau 
• Chia một vấn đề phức tạp thành một tập các mô 
đun nhỏ, rõ ràng, dễ quản lý và thực hiện chúng với 
các khối lệnh 
• Đặt các mã lệnh PL/SQL vào trong các thư viện 
dùng chung giữa các ứng dụng Oracle Developer 
hay lưu chúng trong Oracle Server 
Khoa HTTTQL – Học viện Ngân hàng 7-87 
Module trong PL/SQL 
 Có 4 loại module trong PL/SQL 
• Procedures 
• Functions 
• Triggers 
• Packages 
Khoa HTTTQL – Học viện Ngân hàng 7-88 
Hàm và thủ tục 
 Là các khối lệnh PL/SQL được đặt tên 
 Gọi chung là chương trình con 
 Có cấu trúc giống khối lệnh vô danh 
 Lưu trữ trong CSDL dưới dạng p-code 
Khoa HTTTQL – Học viện Ngân hàng 7-89 
CREATE [OR REPLACE] PROCEDURE procedure_name 
 [( parameter1 [mode1] datatype1, 
 parameter2 [mode2] datatype2, 
 . . .)] 
IS|AS 
 [declaration_section] 
BEGIN 
END ; 
Thủ tục 
 Thi hành thủ tục 
• EXECUTE [or EXEC] procedure_name; 
• procedure_name; 
 Tạo thủ tục 
Khoa HTTTQL – Học viện Ngân hàng 7-90 
Thủ tục (tt) 
 3 kiểu tham số 
MODE DESCRIPTION USAGE 
IN Passes a value into the program 
Constants, literals, expressions 
Cannot be changed within the program’s default 
mode 
Read-only 
value 
OUT Passes a value back from the program 
Cannot assign default values 
Must be a variable 
A value is assigned only if the program is 
successful 
Write-only 
value 
IN OUT Passes values in and also sends values back Has to be a 
variable 
Chú ý: mặc định là kiểu IN 
Khoa HTTTQL – Học viện Ngân hàng 7-91 
Thủ tục (tt) 
 Ví dụ 1: Tạo thủ tục tính tổng hai số 
Khoa HTTTQL – Học viện Ngân hàng 7-92 
Thủ tục (tt) 
 Thi hành thủ tục: 
 Cách 1 
 Cách 2 
Khoa HTTTQL – Học viện Ngân hàng 7-93 
create or replace procedure 
num_logged 
(person IN my_log.who%TYPE, 
 num OUT my_log.logon_num%TYPE) 
IS 
BEGIN 
 select logon_num 
 into num 
 from my_log 
 where who = person; 
END; 
Thủ tục (tt) 
logon_ 
num 
who 
3 Peter 
4 John 
2 Joe 
Table: my_log 
Ví dụ 2 
Khoa HTTTQL – Học viện Ngân hàng 7-94 
set serveroutput on 
declare 
 howmany my_log.logon_num%TYPE; 
begin 
 num_logged('John',howmany); 
 dbms_output.put_line('John log 
 on..'||howmany||' times'); 
end; 
Thủ tục (tt) 
Khoa HTTTQL – Học viện Ngân hàng 7-95 
 Ví dụ 3: Tạo thủ tục nhập thêm một hàng vào bảng my_log 
Khoa HTTTQL – Học viện Ngân hàng 7-96 
 Ví dụ 3’’: Tạo thủ tục nhập thêm một hàng vào bảng my_log 
Khoa HTTTQL – Học viện Ngân hàng 7-97 
Hàm 
 Tạo hàm 
CREATE [OR REPLACE] FUNCTION 
function_name 
 [(parameter1 [IN] datatype1, 
 parameter2 [IN] datatype2, 
 . . .)] 
RETURN datatype 
IS|AS 
 [declaration_section] 
BEGIN 
 ... 
 RETURN (return_value); 
END ; 
Khoa HTTTQL – Học viện Ngân hàng 7-98 
Hàm (tt) 
 Ví dụ 1: Tạo hàm để tính hiệu của hai số 
Khoa HTTTQL – Học viện Ngân hàng 7-99 
Hàm (tt) 
 Sử dụng hàm 
 Cách 1 
 Cách 2 
Khoa HTTTQL – Học viện Ngân hàng 7-100 
create or replace function 
rating_message(rating IN NUMBER) 
return VARCHAR2 
AS 
BEGIN 
 IF rating > 7 THEN 
 RETURN 'You are great'; 
 ELSIF rating >= 5 THEN 
 RETURN 'Not bad'; 
 ELSE 
 RETURN 'Pretty bad'; 
 END IF; 
END; 
Hàm (tt) 
NOTE THAT YOU DON'T 
SPECIFY THE SIZE 
Ví dụ 2: Tạo hàm hiển thị ra thông báo tùy theo giá trị số được đưa vào 
Khoa HTTTQL – Học viện Ngân hàng 7-101 
set serveroutput on 
declare 
 paulRate number:= 3; 
begin 
dbms_output.put_line(rating_Message(paulRate)); 
end; 
Hàm (tt) 
Gọi hàm rating_Message 
Khoa HTTTQL – Học viện Ngân hàng 7-102 
Hủy bỏ và sửa thủ tục/hàm 
 Hủy 
 Sửa 
DROP PROCEDURE tên_thủ_tục; 
DROP FUNCTION tên_hàm; 
ALTER PROCEDURE tên_thủ_tục  
ALTER FUNCTION tên_hàm  
Khoa HTTTQL – Học viện Ngân hàng 7-103 
So sánh thủ tục và hàm 
Thủ tục Hàm 
Thực hiện giống như thực 
hiện các câu lệnh 
Có thể được gọi giống như 
một phần của lệnh PL/SQL 
Không có kiểu giá trị trả về Có chứa giá trị trả về 
Có thể trả về một hoặc 
nhiều tham số 
Trả về một giá trị 
Không chứa lệnh thay đổi 
dữ liệu 
Khoa HTTTQL – Học viện Ngân hàng 
TRIGGER 
Khoa HTTTQL – Học viện Ngân hàng 7-105 
1. Trigger là gì? 
Trigger 
 Là một thủ tục được thực hiện ngầm định ngay khi 
thực hiện lệnh SQL nhằm đảm bảo các quy tắc logic 
phức tạp của dữ liệu. 
 Các loại trigger: 
• DDL trigger 
• DML trigger 
• Compound trigger 
• Instead-of trigger 
• System/database trigger 
2. Trigger dùng để làm gì? 
Khoa HTTTQL – Học viện Ngân hàng 7-106 
Chú ý khi sử dụng trigger 
 Chú ý khi sử dụng trigger: 
• Chỉ sử dụng trigger với các thao tác trọng tâm 
• Không sử dụng trigger cho trường hợp có thể sử dụng 
constraint 
• Trigger có thể gây khó khăn cho việc bảo trì và phát 
triển hệ thống lớn 
 Chỉ sử dụng trigger khi thật cần thiết 
Khoa HTTTQL – Học viện Ngân hàng 7-107 
Phân loại trigger DML 
 Phân theo thời gian thực hiện 
• BEFORE 
• AFTER 
 Phân loại theo loại câu lệnh kích hoạt 
• INSERT 
• UPDATE 
• DELETE 
 Phân loại theo số lần kích hoạt 
• Mức câu lệnh 
• Mức dòng 
Khoa HTTTQL – Học viện Ngân hàng 7-108 
Phân loại trigger 
Khoa HTTTQL – Học viện Ngân hàng 7-109 
Trigger 
 Tạo Trigger 
Khoa HTTTQL – Học viện Ngân hàng 7-110 
Tạo trigger 
 Mức câu lệnh: sự kiện xảy ra mỗi khi câu lệnh sql 
được thực hiện 
CREATE [OR REPLACE] TRIGGER 
trigger_name 
timing event1 [OR event2 OR event3] 
ON table_name 
BEGIN 
PL/SQL Block; 
END; 
Khoa HTTTQL – Học viện Ngân hàng 7-111 
Tạo trigger 
 Mức dòng: sự kiện được thi hành mỗi khi update, 
delete, insert dữ liệu trên từng dòng 
CREATE [OR REPLACE] TRIGGER trigger_name 
timing event1 [OR event2 OR event3] 
ON table_name 
[REFERENCING OLD AS old | NEW AS new] 
FOR EACH ROW 
[WHEN condition] 
BEGIN 
PL/SQL Block; 
END; 
Khoa HTTTQL – Học viện Ngân hàng 7-112 
Instead-of trigger 
 Cú pháp viết như trigger DML 
 Chỉ được dùng cho view 
 Luôn luôn là trigger mức hàng (row) 
 Có thể đọc giá trị NEW, OLD nhưng không thể 
thay đổi 
Khoa HTTTQL – Học viện Ngân hàng 7-113 
Quản lý trigger 
 Thay đổi trạng thái 
 Hủy trigger 
ALTER TRIGGER trigger_name DISABLE | ENABLE; 
ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS; 
DROP TRIGGER trigger_name; 
Khoa HTTTQL – Học viện Ngân hàng 7-114 
CHÚ Ý KHI TẠO TRIGGER 
 Không được phép tạo trigger với USER là SYS 
 Các bước thực hiện: 
• Tạo tablespace nếu cần 
• Tạo user mới, cấp quyền: 
– CONNECT 
– CREATE ANY TABLE 
– INSERT... 
• Với USER SYS, cấp: 
– ALTER USER LINHNT QUOTA 100M ON TBSPTEST 
– GRANT UNLIMITED TABLESPACE TO LINHNT 
– GRANT CREATE TRIGGER TO LINHNT; 
• Vào USER LINHNT để tạo bảng, nhập dữ liệu, tạo trigger 
Khoa HTTTQL – Học viện Ngân hàng 7-115 
TRIGGER(tt) 
 Ví dụ 1: Tạo trigger hiển thị ra thông báo mỗi khi có sự 
thay đổi trên bảng my_log hay thông báo khi nhập và xóa dữ 
liệu của bảng này 
Khoa HTTTQL – Học viện Ngân hàng 7-116 
TRIGGER (tt) 
Khoa HTTTQL – Học viện Ngân hàng 7-117 
TRIGGER (tt) 
 Kiểm tra sự thực thi của trigger T_test_my_log. 
Dữ liệu hiện tại của bảng my_log 
TEST 
Khoa HTTTQL – Học viện Ngân hàng 7-118 
 Kiểm tra lại thông tin của bảng my_log... 
INSERTED 
UPDATED 
Joe was deleted 
 Kết quả khi chạy khối lệnh TEST 
Khoa HTTTQL – Học viện Ngân hàng 7-119 
TRIGGER(tt) 
 Ví dụ 2: Tạo trigger để trước khi có sự thay đổi dữ liệu 
trên bảng my_log thì dữ liệu trước và sau khi thay đổi được 
ghi vào bảng khác là my_log_history 
 Bước 1: Trước khi tạo trigger, Tạo bảng my_log_history 
Khoa HTTTQL – Học viện Ngân hàng 7-120 
 Lưu ý: để lấy được dữ liệu cũ và mới: PHẢI tạo trigger 
Ở mức dòng (FOR EACH ROW) 
Bước 2: Tạo trigger 
Sử dụng 
OLD, NEW 
để lấy giá trị 
cũ và mới 
Dữ liệu cũ và 
mới sẽ được ghi 
vào bảng 
my_log_history 
Khoa HTTTQL – Học viện Ngân hàng 7-121 
TRIGGER (tt) 
 Bước 3: kiểm tra sự thực thi của trigger 
 Dữ liệu của bảng my_log trước khi thay đổi 
 Cập nhật lại số lần truy nhập của Trang 
Khoa HTTTQL – Học viện Ngân hàng 7-122 
TRIGGER (tt) 
 Bước 4: Kiểm tra lại bảng my_log 
 Số lần truy nhập của Trang đã được 
thay đổi 
 Kiểm tra xem bảng my_log_history đã 
có dữ liệu chưa 
Khoa HTTTQL – Học viện Ngân hàng 7-123 
TRIGGER (tt) 
 Tạo Trigger trên View 
Khoa HTTTQL – Học viện Ngân hàng 7-124 
TRIGGER (tt) 
 Trigger trên View 
 Ví dụ 3: Tạo View sau: 
Chú ý: trước khi tạo trigger trên view, phải vào user SYS để 
cấp quyền tạo trigger cho user LINHNT 
Khoa HTTTQL – Học viện Ngân hàng 7-125 
TRIGGER (tt) 
 Trigger trên View 
 Ví dụ 3 (tt): tạo trigger hiển thị thông báo khi nhập 
dữ liệu cho my_log_view 
Khoa HTTTQL – Học viện Ngân hàng 7-126 
TRIGGER (tt) 
 Trigger trên View 
 Ví dụ 3 (tt): kiểm tra dữ liệu hiện tại của 
my_log_view 
 Nhập một bản ghi mới vào my_log_view 
Khoa HTTTQL – Học viện Ngân hàng 7-127 
TRIGGER (tt) 
 Ví dụ 3(tt): kết quả khi chạy khối lệnh nhập dữ liệu trên 
 Kiểm tra lại xem my_log_view đã có thêm dữ liệu mới chưa? 
KHÔNG có bản ghi mới??? 
Khoa HTTTQL – Học viện Ngân hàng 7-128 
TRIGGER (tt) 
 Trigger trên View 
 Ví dụ 4: tạo trigger hiển thị giá trị trước và sau khi 
được thay đổi trên my_log_view 
Khoa HTTTQL – Học viện Ngân hàng 7-129 
TRIGGER (tt) 
 Trigger trên View 
 Ví dụ 4: kiểm tra sự thực thi của trigger 
 Thay đổi dữ liệu của my_log_view 
Khoa HTTTQL – Học viện Ngân hàng 7-130 
TRIGGER (tt) 
 Trigger Exception 
• Dùng để ngăn chặn các hành động không hợp lệ tác động 
vào database 
• Ví dụ 5: Bảng person có cấu trúc và dữ liệu như sau: 
Hãy tạo trigger hiển thị thông báo “can not change date of birth” 
khi ai đó cố tình muốn thay đổi ngày sinh của người nào đó 
trong bảng dữ liệu trên. 
Khoa HTTTQL – Học viện Ngân hàng 7-131 
TRIGGER (tt) 
 Trigger Exception (tt) 
• Tạo trigger cảnh báo không cho phép thay đổi dữ liệu trên 
trường birth_day của bảng person 
Khoa HTTTQL – Học viện Ngân hàng 7-132 
TRIGGER (tt) 
 Trigger Exception (tt) 
• Kiểm tra sự thực thi của trigger, khi thay đổi dữ liệu 
của bảng person 
Khoa HTTTQL – Học viện Ngân hàng 7-133 
TRIGGER (tt) 
 Thứ tự thực hiện các trigger trong chương trình 
• 1) BEFORE statement trigger 
• 2) BEFORE row level trigger 
• 3) AFTER row level trigger 
• 4) AFTER statement level trigger 
Khoa HTTTQL – Học viện Ngân hàng 7-134 
TRIGGER (tt) 
 Ví dụ 6: Kiểm tra thứ tự thực hiện của trigger khi 
chương trình có nhiều trigger. Tạo 4 trigger được thực thi 
khi có sự kiện UPDATE xảy ra đối với bảng my_log, nhật 
ký hoạt động sẽ được ghi vào bảng my_log_check 
 Trước tiên, tạo bảng my_log_check 
Khoa HTTTQL – Học viện Ngân hàng 7-135 
TRIGGER (tt) 
 1) BEFORE UPDATE, Statement Level: 
Khoa HTTTQL – Học viện Ngân hàng 7-136 
TRIGGER (tt) 
 2) BEFORE UPDATE, Row Level: 
Khoa HTTTQL – Học viện Ngân hàng 7-137 
TRIGGER (tt) 
 3) AFTER UPDATE, Statement Level: 
Khoa HTTTQL – Học viện Ngân hàng 7-138 
TRIGGER (tt) 
 4) AFTER UPDATE, Row Level: 
Khoa HTTTQL – Học viện Ngân hàng 7-139 
TRIGGER (tt) 
 Thực hiện lệnh update trên bảng my_log 
Khoa HTTTQL – Học viện Ngân hàng 7-140