Đào tạo Oracle cơ bản - Giáo trình SQL và PL SQL

 này vào bảng message cột charcol1 (thử với các giá trị 7654, 7369, 7900, 7876) 
4. Đ−a vào vòng lặp v từ 1 đến 10 lệnh 
 UPDATE messages SET numcol2=100 
 WHERE numcol1 = v; 
nếu bất kỳ một lần update nào đó có số l−ợng row >1 thì exit khỏi vòng lặp. 
20 procedure và funtion 
20.1 Procedure 
Là một nhóm các lệnh thực hiện chức năng nào đó nhằm tăng khả năng xử lý, khả năng sử dụng các thủ tục 
chung, tăng tính bảo mật và an toàn dữ liệu, tiện ích trong phát triển. 
Cú pháp: 
Procedure : Là tên của procedure đ−ợc tạo. 
Argument : Gồm tên của danh sách các biến và kiểu của nó. 
IN : Chỉ định rằng bạn phải đ−a trị khi gọi procedure. 
OUT : Chỉ ra rằng Procedure sẽ trả lại trị cho biến tới môi tr−ờng gọi nó. 
IN OUT : Chỉ ra rằng bạn phải gán trị cho argument khi gọi procedure và procedure sẽ trả lại trị 
argument tới môi tr−ờng gọi. 
Nếu không ghi IN, OUT hoặc IN OUT thì ngầm định sẽ là IN 
Công ty cổ phần đầu t− phát triển công nghệ - FPT 
Giáo trình SQL và PL/SQL 
Đào tạo cơ bản: SQL và PL/SQL Trang 72 
Datatype : Là kiểu của argument, ở đây chỉ đ−ợc khai báo kiểu mà kgông đ−ợc khai báo các chiều dài 
argument. Ví dụ không đ−ợc khai báo argument là VARCHAR2(10) mà phải khai báo là VARCHAR2. 
Pl/sql_subprogram_body: Là phần thân của procedure đ−ợc viết bằng PL/SQL. 
Ví dụ: 
CREATE OR REPLACE PROCEDURE INS_DEPT(X NUMBER, Y VARCHAR2) IS 
 BEGIN 
 INSERT INTO DEPT(DEPTNO,DNAME) VALUES (X,Y); 
 END; 
Muốn thực hiện procedure tại SQL plus thự hiện dùng lệnh execute <ten(danh sách giá trị). Còn trong các 
thủ tục khác dùng lệnh gọi bình th−ờng 
SQL> execute ins_dept(55,’ New Name’); 
20.2 Function 
Cú pháp: 
Các tham giống nh− procedure nh−ng khác là sau khi gọi hàm trả lại trị 
Ví dụ: 
create or replace function get_dname( y number) return varchar2 is 
 m char(14); 
begin 
 select dname into m from dept where deptno=y; 
 if SQL%notfound then 
 m:='Khong thay'; 
 end if; 
 return(rtrim(m)); 
end; 
Để gọi hàm get_dname ta gọi trực tiếp hoặc thông qua các phép gán. 
Ví dụ: 
SQL> select * from dept where dname=get_dname(10); 
 DEPTNO DNAME LOC 
--------- -------------- ------------- 
 10 ACCOUNTING NEW YORK 
SQL> select get_dname(20) from dual; 
GET_DNAME(20) 
-------------------------------------------------------- 
Công ty cổ phần đầu t− phát triển công nghệ - FPT 
Giáo trình SQL và PL/SQL 
Đào tạo cơ bản: SQL và PL/SQL Trang 73 
RESEARCH 
20.3 Bài tập 
1. Viết hàm lấy tên giám đốc theo biến empno đ−ợc nhập vào, 
2. Viết thủ tục nhập thông tin vào bảng message các tr−ờng 
numcol1: Mã phòng 
charcol1: tên phòng ban 
numcol2: tổng l−ơng của phòng 
3. Viết thủ tục dùng cursor; lấy số liệu về n ng−ời (n là một biến đ−ợc đ−a vào từ man hình) có mức l−ơng 
cao nhất trong bảng emp đ−a vào bảng top_sal với các giá trị t−ơng ứng trong num=empno, name = ename, 
salary = sal). Bảng top_sal có cấu trúc nh− sau: 
NUM NUMBER(4) 
NAME VARCHAR2(25) 
SALARY NUMBER(11,2) 
21 pakage 
21.1 Package 
Là tập hợp của các đối t−ợng gồm các procedure, function, variable, constant, cursor và các exception. 
Việc tạo các package cho phép tăng khả năng mềm dẻo, tăng tính bảo mật, tạo sự thuận lợi trong việc quản 
lý hệ thống đồng thời tăng hiệu suất xử lý của hệ thống. 
Để tạo package thực hiện nh− sau: 
Để tạo package body thực hiện nh− sau: 
Với các releases tr−ớc đây của PL/SQL việc gọi các functions chỉ có thể đ−ợc thực hiện bằng các lệnh của 
procedure, nh−ng giờ thì các lời gọi này có thể xuất hiện trong câu lệnh SQL giống nh− lệnh procedure. Điều 
này có nghĩa là ta có thể sử dụng các functions giống nh− các built-in SQL functions. Bằng các mở rộng SQL 
ta có thể tập hợp phân tích ngay bên trong Oracle Server mà ta không cần lấy dữ liệu vào trong ứng dụng 
điều này làm tăng tính độc lập của cơ sở dữ liệu. 
Tuy nhiên để có thể gọi đ−ợc từ SQL thì các function phải đảm bảo chắc chắn việc kiểm soát kết quả. Với 
các standalone functions thì Oracle có thể thực hiện điều này bằng việc kiểm tra function body. Tuy nhiên với 
body của package là ẩn cho nên các packaged functions ta phải sử dụng pragma 
RESTRICT_REFERENCES để đảm bảo luật này. 
Công ty cổ phần đầu t− phát triển công nghệ - FPT 
Giáo trình SQL và PL/SQL 
Đào tạo cơ bản: SQL và PL/SQL Trang 74 
Pragma Restrict_references(, , ...) 
Trong đó: 
WNDS: Write no database state 
RNDS: Read no database state 
WNPS: Write no package state 
RNPS: Read no package state 
Ví dụ: 
create or replace package vidu is 
 function get_dname( y number) return varchar2; 
 Pragma Resctrict_references(get_dname, WNDS, WNPS); 
 Procedure ins_dept (x number, y varchar2); 
end vidu; 
create or replace package body vidu is 
 function get_dname( y number) return varchar2 is 
 m char(14); 
 begin 
 select dname into m from dept where deptno=y; 
 if SQL%notfound then 
 m:='Khong thay'; 
 end if; 
 return(rtrim(m)); 
 end; 
 procedure ins_dept(x number, y varchar2) is 
 begin 
 insert into dept(deptno,dname) values (x,y); 
 end; 
end vidu; 
Để gọi ta thực hiện nh− sau: 
SQL> execute vidu.ins_dept(70,'Vi du'); 
22 database trigger 
22.1 Database Trigger 
Một Database Trigger đ−ợc tạo và l−u trữ trong PL/SQL block t−ơng ứng với table. Nó đ−ợc tự động gọi đến 
khi có sự truy nhập đến table t−ơng ứng với các hành động định nghĩa. 
Để tạo một triger ta gõ theo có pháp sau: 
Công ty cổ phần đầu t− phát triển công nghệ - FPT 
Giáo trình SQL và PL/SQL 
Đào tạo cơ bản: SQL và PL/SQL Trang 75 
Create or replace : lệnh tạo hoặc tạo lại trigger nếu nó đã tồn tại. 
Before : Chỉ ra rằng trigger sẽ đ−ợc thực hiện tr−ớc khi thực hiện lệnh. 
Affter : Chỉ ra rằng trigger sẽ đ−ợc thực hiện sau lệnh gọi tới nó. 
Delete, Insert, Update of on : trigger sẽ đ−ợc gọi khi có các hành động t−ơng ứng trên 
từng column của bảng. 
Referencing : Chỉ tên quan hệ tới các trị cũ (OLD) và mới (NEW) của row . 
For each row : Trigger thực hiện t−ơng ứng với mỗi row có điều kiện ứng với mệnh đề trong 
WHEN. 
Pl/sql_block : Là khối lệnh PL/SQL thực hiện các xử lý theo mong muốn. 
Ví dụ: 
create or replace trigger t_dname before insert or update of dname on dept 
for each row when (new.dname is null) 
begin 
 if (:new.dname is null) then 
 :new.dname:='No Name'; 
 end if; 
end ; 
22.2 Bài tập 
1. Viết trigger để khi nhập số liệu vào bảng emp thì nó cũng nhập số liệu vào bảng emp1 với điều kiện cấu 
trúc bảng emp1 có empno, ename, job, dname 
2. Thêm 1 cột vào bảng DEP tên là SUMSAL. Viết trigger để cột SUMSAL luôn chứa tổng l−ơng của phòng 
ban đó ( dữ liệu lấy t−ơng ứng từ bảng emp) 
3. Lập 1 bảng tên là BACKUP có cấu trúc giống bảng EMP sao cho mỗi bản ghi trong emp bị xoá sẽ l−u 
sang backup 
4. ứng với các theo tác insert, update, delete trên bảng emp, l−u lại các theo tác đó vào bảng message, với 
dữ liệu t−ơng ứng charcol1 = tên thao tác, datecol2 = Ngày giờ thực hiện. 
Công ty cổ phần đầu t− phát triển công nghệ - FPT 
Giáo trình SQL và PL/SQL 
Đào tạo cơ bản: SQL và PL/SQL Trang 76 
23 error handing 
Error handing là lỗi xuất hiện trong khối lệnh PL/SQL, tất cả các lỗi này sẽ chạy về phần EXCEPTION trong 
khối lệnh để xử lý. 
Khối lệnh PL/SQL gồm các thành phần 
DECLARE /Không bắt buộc/ 
 Định nghĩa các biến 
BEGIN 
Đoạn lệnh; 
EXCEPTION /Không bắt buộc/ 
 Hành đồng nếu lỗi xuất hiện; 
END; 
Cú pháp thực hiện các EXCEPTION; 
EXCEPTION 
 WHEN exception1 [OR exception1. . .] THEN 
 Xử lý; 
 . . . 
 [WHEN exception3 [OR exception4. . .] THEN 
 Xử lý; 
. . .] 
[WHEN OTHERS THEN 
 Xử lý; 
. . .] 
Trong đó: 
exception : tên lỗi n 
WHEN OTHERS : dùng để xử lý các tr−ờng hợp lỗi khác 
Điều kiện kích hoạt exception 
Có 2 nhóm exception: 
• Các exception của bản thân Oracle nh−: NO_DATA_FOUND, FOUND, TOO_MANY_ROW ... 
• Các exception do ng−ời sử dụng khai báo 
Các exception hệ thống tự động bị kích hoạt trong các tr−ờng hợp nhất định. Các exception ng−ời sử dụng 
định nghĩa phải tự kích hoạt, ví dụ 
RAISE exception_identifier; 
Một số exception hay dùng của bản thân Oracle: 
Tên Mã lỗi Mô tả 
NO_DATA_FOUND ORA_01403 Câu lệnh SELECT INTO không trả về row nào 
TOO_MANY_ROW ORA_01422 Câu lệnh SELECT INTO không trả về lớn hơn 1 
row 
INVALID_CURSOR ORA_01001 Lỗi xử lý CURSOR 
ZERO_DIVIDE ORA_01476 Lỗi chia cho 0 
Công ty cổ phần đầu t− phát triển công nghệ - FPT 
Giáo trình SQL và PL/SQL 
Đào tạo cơ bản: SQL và PL/SQL Trang 77 
DUP_VAL_ON_INDEX ORA_00001 Lỗi giá trị bị trùng lắp trong một UNI QUE INDEX 
Ví dụ Xoá những nhân viên trong bảng emp nếu tại phòng nhân viên đó làm việc chỉ có một nhân viên; trong 
Procedure buider 
PROCEDURE DELEMP 
(V_EMP IN EMP.EMPNO%TYPE) IS 
V_ID EMP.EMPNO%TYPE; 
BEGIN 
 SELECT EMPNO 
 INTO V_ID 
 FROM EMP 
WHERE EMPNO = V_EMP; 
DELETE FROM EMP 
WHERE EMPNO = V_EMP; 
COMMIT; 
EXCEPTION 
 WHEN NO_DATA_FOUND THEN 
 ROLLBACK; 
TEXT_IO.PUT_LINE(TO_CHAR(V_EMP)||'KHONG CO'); 
WHEN TOO_MANY_ROWS THEN 
 ROLLBACK; 
TEXT_IO.PUT_LINE('CO LOI DU LIEU TRONG BANG EMP'); 
WHEN OTHERS THEN 
 ROLLBACK; 
TEXT_IO.PUT_LINE('CO LOI KHAC TRONG BANG EMP'); 
END; 
Gọi chạy delemp(7364); 
Các exception do ng−ời sử dụng định nghĩa 
Khai báo exception 
identifier EXCEPTION; 
Ví dụ: 
DECLARE 
 credit_exceeded EXCEPTION; 
BEGIN 
 IF stock_ordered > credit_limit THEN 
 RAISE credit_exceeded; 
 END IF 
 .... 
 EXCEPTION 
 WHEN credit_exceeded THEN .... 
END; 
Đặt tên cho các exception hệ thống 
Mỗi exception hệ thống đ−ợc gán một số xác định, có thể đặt tên cho các exception để dễ sử dụng hơn. 
PRAGMA EXCEPTION_INIT (exception_identifier, number) 
Công ty cổ phần đầu t− phát triển công nghệ - FPT 
Giáo trình SQL và PL/SQL 
Đào tạo cơ bản: SQL và PL/SQL Trang 78 
Ví dụ 
DECLARE 
 fetch_failed EXCEPTION; 
 PRAGMA EXCEPTION_INIT (fetch_failed, -1002); 
BEGIN 
 .... 
 EXCEPTION 
 WHEN fetch_failed THEN .... 
END; 
23.1 Bài tập 
1. Dùng EXCEPTION bắt lỗi chặt hơn cho các bài tập từ phần 19-22.