Bài th c hành s 4ự ố
PL/SQL (1)
Tóm t t n i dung:ắ ộ
Khái ni m PL/SQLệ
Các v n đ liên quan đn ki u d li u trong PL/SQLấ ề ế ể ữ ệ
H ng và Bi nằ ế
C u trúc kh i PL/SQLấ ố
Các câu l nh đi u khi nệ ề ể
I. PL/SQL là gì ?
PL/SQL (PL : Procedural Language – Ngôn ng Th t c) là m t m r ng c a SQL, k t h pữ ủ ụ ộ ở ộ ủ ế ợ
vào trong đó r t nhi u đc tính c a các ngôn ng l p trình g n đây. Nó cho phép các thao tácấ ề ặ ủ ữ ậ ầ
d li u và các câu l nh query SQL bao g m các đo n mã có c u trúc kh i và tính th t cữ ệ ệ ồ ạ ầ ố ủ ụ
(block-structure and procedural unit of code), làm cho PL/SQL thành m t ngôn ng x lý giaoộ ữ ử
d ch m nh m .ị ạ ẽ
Chương Trình Đào Tạo Từ Xa KH & KT Máy Tính – Đại học Bách Khoa TP.HCM
II. Các l nh SQL trong PL/SQLệ
PL/SQL cung c p m t s câu l nh th t c cho vi c thao tác và ki m tra d li u, th ngấ ộ ố ệ ủ ụ ệ ể ữ ệ ườ
không c n ph i dính dáng v i các l nh SQL. Dù v y, khi c n l y th ng tin t CSDLầ ả ớ ệ ậ ầ ấ ố ừ
ho c thay đi trên CSDL thì nên dùng SQL.ặ ổ
PL/SQL h tr t t cho đa s các l nh DML và các l nh đi u khi n giao d ch trong SQL.ỗ ợ ố ố ệ ệ ề ể ị
Ngoài ra, các câu l nh SELECT có th dùng đ gán các giá tr query t 1 hàng trong b ngệ ể ể ị ừ ả
cho các bi n.ế
M t s đi m l u ý:ộ ố ể ư
M t kh i PL/SQL không ph i là m t đn v giao d ch (transaction unit) – các l nhộ ố ả ộ ơ ị ị ệ
COMMIT và ROLLBACK là đc l p v i các kh i nh ng có th n m trong nó.ộ ậ ớ ố ư ể ằ
M i câu l nh SQL c n ph i k t thúc b i d u ch m ph y.ỗ ệ ầ ả ế ở ấ ấ ẩ
Câu l nh SELECT có th dùng đ gán các giá tr query t 1 hàng trong b ng cho cácệ ể ể ị ừ ả
bi n.ế
Các câu l nh SELECT mà không tr l i ệ ả ạ đúng m t hàngộ s gây ra m t l i c n ph iẽ ộ ỗ ầ ả
gi i quy t (th ng là ph i dùng ph ng pháp x lý ngo i l ho c cursor).ả ế ườ ả ươ ử ạ ệ ặ
Các l nh DDL không dùng đc trong PL/SQL. Ví d :ệ ượ ụ
-T t c các l nh b t đu b ngấ ả ệ ắ ầ ằ ALTER, CREATE, DROP, FLASHBACK
-Các l nh qu n lý quy n: GRANT, REVOKEệ ả ề
-Các l nh audit: AUDIT, NOAUDITệ
(và còn nhi u l nh khác)ề ệ
Các l nh DML có th x lý nhi u hàng (multiple rows).ệ ể ử ề
III. Ki u d li uể ữ ệ
PL/SQL h tr r t nhi u ki u d li u đ có th khai báo các bi n và các h ng. Có thỗ ợ ấ ề ể ữ ệ ể ể ế ằ ể
gán m t giá tr ban đu cho các bi n khi khai báo bi n và có th thay đi các giá tr c aộ ị ầ ế ế ể ổ ị ủ
chúng thông qua các phát bi u gán v sau trong kh i. Các h ng là các danh hi uể ề ố ằ ệ
(identifier) l u gi m t giá tr c đnh và giá tr này ph i đc gán cho h ng khi h ngư ữ ộ ị ố ị ị ả ượ ằ ằ
đc khai báo.ượ
Các ki u d li u:ể ữ ệ
Chương Trình Đào Tạo Từ Xa KH & KT Máy Tính – Đại học Bách Khoa TP.HCM
D li u s : ữ ệ ố NUMBER
Ví d : ụNUMBER(7,2)
Nghĩa là có 7 ký s trong đó có 2 ký s sau d u th p phân. N u ta không khai báo đố ố ấ ậ ế ộ
chính xác là 2 nh câu l nh trên thì đ chính xác m c đnh là 38 ký s .ư ệ ộ ặ ị ố
D li u lu n lí: ữ ệ ậ BOOLEAN
D li u ngày tháng: ữ ệ DATE
D li u chu i:ữ ệ ỗ
Chương Trình Đào Tạo Từ Xa KH & KT Máy Tính – Đại học Bách Khoa TP.HCM
VARCHAR2 L u tr các d li u ký t có chi u dài thay đi. Chi u dài m cư ữ ữ ệ ự ề ổ ề ặ
đnh là 1 ký t . Chi u dài t i đa là 32767. Ví d : ị ự ề ố ụ VARCHAR2(30)
CHAR PL/SQL Version 1: gi ng nh VARCHAR2 nh ng chi u dàiố ư ư ề
t i đa là 255.ố
PL/SQL Version 2: chu i các ký t chi u dài c đnh dài t i đaỗ ự ề ố ị ố
là 32767 byte. Khi so sánh hai chu i v i nhau thì các ký tỗ ớ ự
tr ng s đc thêm vào.ố ẽ ượ
Chú ý: Khi so sánh 2 chu i CHAR trong PL/SQL Version 1 thìỗ
hai chu i này không đc thêm vào các ký t tr ng, ví d m tỗ ượ ự ố ụ ộ
bi n ki u CHAR ch a ‘FRED’ thì khác v i m t bi n ki uế ể ứ ớ ộ ế ể
CHAR ch a ‘FRED ’.ứ
IV. Khai báo bi n và h ngế ằ
1. Khai báo các bi nế
Các bi n PL/SQL có th đc khai báo và có th đc gán m t giá trế ể ượ ể ượ ộ ị
ban đu trong ph n DECLARE c a kh i. Các bi n khác đc tham kh o đn trongầ ầ ủ ố ế ượ ả ế
ph n khai báo thì chúng ph i đc khai báo trong m t phát bi u tr c đó.ầ ả ượ ở ộ ể ướ
Cú pháp:
identifier datatype [(precision, scale)] [NOT NULL] [ := expression];
trong đó
identifier - tên bi nế
datatype - ki u d li u c a bi nể ữ ệ ủ ế
precision - chi u dài c a bi n (s ký s c a ph n nguyên và ph n th pề ủ ế ố ố ủ ầ ầ ậ
phân)
scale - s s l (s ký s c a ph n th p phân)ố ố ẻ ố ố ủ ầ ậ
N u không gán giá tr ban đu cho bi n thì bi n s ch a giá tr NULLế ị ầ ế ế ẽ ứ ị
cho đn khi gán giá tr m i. Ràng bu c NOT NULL không đc dùng trong tr ngế ị ớ ộ ượ ườ
h p này.ợ
Ví d :ụ
Chương Trình Đào Tạo Từ Xa KH & KT Máy Tính – Đại học Bách Khoa TP.HCM
v_count NUMBER NOT NULL := 0;
v_saraly NUMBER(7,2);
v_annsal NUMBER(9,2) := month_sal * 12;
-- month_sal phải tồn tại trước
postcost CHAR(7);
surname VARCHAR2(25) := ‘Skywalker’;
v_message VARCHAR2(80) := ‘Data is wrong !’;
married BOOLEAN := FALSE;
today DATE := SYSDATE;
Không nên đt tên c a bi n trùng tên v i các tên c t c a b ng đcặ ủ ế ớ ộ ủ ả ượ
dùng trong kh i. N u các bi n trong các phát bi u SQL có cùng tên v i tên c t, thìố ế ế ể ớ ộ
Oracle xem tên này là tên c t (mà không ph i là tên bi n).ộ ả ế
Ví d :ụ
DECLARE
bonus NUMBER(8,2);
emp_id NUMBER(6) := 100;
BEGIN
SELECT salary * 0.10 INTO bonus FROM employees
WHERE employee_id = emp_id;
END;
2. Khai báo h ngằ
Cú pháp:
identifier CONSTANT datatype [(precision,scale)] := expression;
Ví d :ụ
pi CONSTANT NUMBER(9,5) := 3.14159;
vat CONSTANT NUMBER(4,2) := 17.5;
Chú ý: Có th dung t khóa %TYPE đ dùng cùng ki u v i c t đcề ừ ể ể ớ ộ ượ
ch đnh trong 1 table. ỉ ị Ví d :ụ bi n product_type s có cùng ki u v i c t price c aế ẽ ể ớ ộ ủ
b ng products:ả
product_price products.price%TYPE;
Chương Trình Đào Tạo Từ Xa KH & KT Máy Tính – Đại học Bách Khoa TP.HCM