Giới thiệu về PL/SQL-
1. Kết cấu một PL/SQL
Declare
Định nghĩa các đối tượng PLSQL có dùng ở trong khối.
BEGIN
Các tác vụ thực hiện
[ EXCEPTION
Xử lý các lỗi do các tác vụ thực thi gây ra.
]
END;
•
Ví dụ:
Declare
v_qty_on_hand NUMBER(5);
Begin
SELECT quantity into v_qty_on_hand
FROM Inventory
WHERE product=‘TENNIS’;
IF v_qty_on_hand >0 THEN
UPDATE Inventory
SET quantity = quantity -1
WHERE product =‘TENNIS’
INSERT INTO Purchase_record
VALUES(‘Tennis purchased’,Sysdate);
ELSE
INSERT INTO Purchase_record
VALUES(‘Out of Tennis’,Sysdate);
END IF;
Commit;
EXCEPTION
When no_data_found then – nếu không tìm thấy hàng nào trong SELECT

INSERT INTO error_table
VALUES (‘Product TENNIS not found’);
End;
Lưu ý
- Sau các từ khóa DECLARE, BEGIN, EXCEPTION không được dùng dấu chấm phẩy(;)
- Sau từ khóa END và các câu lệnh PL/SQL cần có dấu chấm phẩy(;)
- Các ghi chú trên nhiều hàng dùng kí hiệu /* */ , trên một hàng dùng ‘ ‘
- Các hằng kí tự hay ngày cần phải để trong dấu nháy đơn(‘)
2. Cấu trúc khối:
Các khối có thể lồng nhau bên trong khối đầu tiên
Trang 1
BEGIN
action in main, outer block
DECLARE
object declarations
BEGIN
action of inner block
EXCEPTION
what to do if error occur in inner block
END;
further actions of main block
END;
3. Tầm vực:
DECLARE
x,y NUMBER;
BEGIN
DECLARE scope of x,y in outer block
x NUMBER;
BEGIN scope of x in inner block
….

END;
END;
3. Kiểu dữ liệu:
Kiểu số:
NUMBER: độ chính xác đến 38 kí số
NUMBER(precision,scale) : vd NUMBER(7,2): gồm 2 số thập phân. Vd: 3.67
Kiểu chuỗi:
VARCHAR2,CHAR
KIỂU Luận lý:
BOOLEAN: True|False
Kiểu ngày:DATE
4. Khai báo biến
vd: v_count NUMBER not null :=0;
v_salary NUMBER(9,2);
v_annsal NUMBER(9,2):=month_sal*12;
v_message VARCHAR2(50):=’Hello,World!”
v_married BOOLEAN:=FALSE;
v_today DATE:=SYSDATE;
Cú pháp: identified datatype[(precision,scale)] [NOT NULL] [:=expression];
Trang 2
5. Khai báo hằng
ví dụ:
pi CONSTANT NUMBER(9,5):= 3.14167
Cú pháp: identified CONSTANT datatype[(precision,scale)] [NOT NULL] :=expression;
Thực thi một khối pl/sql bằng lệnh RUN hoặc dấu gạch chéo(/)
6. Implicit cursor(Kiểm tra kết quả của các lệnh SQL)
Cursor là một vùng nhớ được tạo ra để các lệnh SQL được phân rã và thi hành. Các lệnh SQL dùng trong phần thi hành
của một khối sẽ tuân theo một implicit cursor có danh định là ‘SQL’. PL/SQL cung cấp một vài thuộc tính để cho phép
đánh gía vấn đề đã xảy ra sau khi implicit cursor được dùng:
- SQL%ROWCOUNT : Số hàng đã được xử lý bởi câu lệnh SQL( giá trị nguyên)

- SQL%FOUND: TRUE nếu có ít nhất 1 hàng đã được xử lý, ngược lại là FALSE.
- SQL%NOTFOUND: TRUE nếu không có hàng nào được xử lý, ngược lại là FALSE.
Vd
DECLARE
rows_deleted NUMBER;
BEGIN
DELETE FROM DEPT WHERE deptno=40;
rows_deleted:=SQL%ROWCOUNT;
dbms_output.put_line(‘So hang duoc xoa la: ‘||rows_deleted);
END;
7. Thuộc tính %TYPE, mênh đề INTO:
Cú pháp: identified tablename.column%TYPE;
Kiểu và kích thước sẽ được xác định khi khối được biên dịch và sẽ tương thích với cột tương ứng.
Ngay char(8)
Ngay em.hiredate%type;
Char(14)
Mênh đề INTO nằm giữa SELECT và FROM.
8. Các loại EXCEPTION cơ bản trong PL/SQL
Tên Exception Lỗi Oracle
DUP_VAL_ON_INDEX ORA-0001
INVALID_CURSOR ORA-1001
INVALID_NUMBER ORA-1722
LOGIN_DINIED ORA-1017
NO_DATA_FOUND ORA-1403
TOO_MANY_ROWS ORA-1422
Trang 3
STORAGE_ERROR ORA-6500
VALUES_ERROR ORA-6502
ZERO_DIVIDE ORA-1476
CURSOR_ALREADY_OPEN ORA-6511

OTHERS : các trường hợp khác
EX: WHEN OTHERS THEN
XU LY: RAISE_APPLICATION_ERROR(-20002,’MESSAGE’);
Vd:
BEGIN
INSERT INTO DEPT(DEPTNO,DEPTNAME) VALUES(50,’CLEANING’);
INSERT INTO DEPT(DEPTNO,DEPTNAME) VALUES(50,’CLEANING’);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
Rollback;
WHEN OTHERS THEN
Raise_application_error(-20002,’Loi roi ba kon!’) ;
END;
Các hàm bẫy lỗi:
- SQLCODE: Trả về mã lỗi của exception đó.Nếu dùng o ngoài EXCEPTION thì mã trả ra là 0.
- SQLERRM : Trả về toàn bộ câu thông báo lỗi và mã lỗi
Vd: v_error_message:= SQLERRM;
v_error_code:= SQLCODE;
9. Các cấu trúc điều khiển:
- Câu lệnh IF: IF THEN ELSEIF
- Câu lệnh GOTO: <<label_name>>, GOTO label_name
- Vòng lặp cơ bản: LOOP…END LOOP;
- Vòng lặp FOR: vd: FOR n IN 1 10 LOOP….END LOOP;
FOR n REVERSE 10 1 LOOP …END LOOP;
- Vòng lặp WHILE: vd: WHILE (condition) LOOP… END LOOP;
- Câu lênh EXIT: EXIT [loop-label] [WHEN condition];
10. %ROWTYPE: khai báo một record dựa trên một tập các cột cùa bảng hay view
cú pháp: indentifier reference%ROWTYPE;
ex: emp_rec EMP%ROWTYPE; (empno,ename,hiredate,sal,comm)
emp_rec

empno number
ename varchar2
…
Trang 4
emp_rec.empno
emp_rec VIE_NAME%ROWTYPE;
11. Explicit Cursors:
Explicit cursor có thể điều khiển qua 4 tác vụ riêng lẻ sau:
*DECLARE: Định tên của cursor và cấu trúc cuả query thực thi trong nó. Tại thời điểm này, query sẽ được phân tích
nhưng chưa thi hành.
*OPEN: Thi hành query, ràng buộc các biến có tham khảo đến. Các hàng trả về bởi query gọI là ‘active set’ và sẳn sàng
cho việc lấy dữ liệu.
*FETCH: Lấy dữ liệu từ hàng hiện tại vào các biến. Hàng hiện tại là hàng mà cursor đang chỉ đến.Mổi lần FETCH,
cursor di chuyển con trỏ đến hàng kế tiếp trên active set, như vậy mỗi một lệnh FETCH sẽ truy cập đến các hàng khác
nhau trong query.
*CLOSE: Hủy bỏ tập các hàng đang làm việc được sinh ra bởi lệnh OPEN cuối cùng của cursor. Có thể OPEN lại được
và như vậy sẽ có tập hàng làm việc mới hơn.
Có 4 thuộc tính để biết các thông tin về cursor. Khi dùng thì phải đặt tên cursor trứơc các thuộc tính này:
%FOUND: Có giá trị TRUE nếu lệnh FETCH gần nhất từ cursor lấy được 1 hàng từ active set, ngược lại sẽ là FALSE.
%NOTFOUND: Nguợc lại vói FOUND.
%ROWCOUNT: Trả về số hàng đã FETCH được từ active set tính đến hiện tại
%ISOPEN: TRUE nếu cursor đang mở, FALSE nếu cursor đã đóng hoặc chưa được mở trong khối.
DECLARE
OPEN cursor_name[(argument list)]
FETCH
CLOSE
CURSOR C2 IS
Query_expression
Ex: cursor c2 is
Select ename, hiredate

From emp
Where empno= v_empno;
CURSOR VOI THAM SO:
Các tham số cho phép các giá trị được chuyển vào trong cursor khi nó mở ra và dùng trong cursor khi nó thi hành.
CURSOR c1[(paramater1 datatype,paramater2 datatype,…)] IS
query_expression
Các kiểu dữ liệu của tham số gióng như các biến khác nhưng không cung cấp kích thước và tỉ lệ(size,scale).Các tên các
tham số là để tham khảo trong biểu thức query của cursor.
Trang 5
vd: CURSOR C1(param1 NUMBER, param2 CHAR) IS
SELECT ename,sal,hiredate
FROM EMP
WHERE deptno=param1
AND job=param2;
Mo cursor
OPEN c1(30,’CLERK’);
CÁC VÒNG LẶP CUROR FOR:
PLSQL cung cấp một kiểu đặc biết của vòng lặp FOR để xử lý các hàng trả về trong một explicit cursor. Trong mỗi vòng
lặp cursor FOR, một cursor đã khai báo sẽ được mở ra, lấy dữ liệu và đóng lại một cách tự động khi tát cả các hàng đã
được xử lý xong.Mỗi bước lặp lấy môt hàng từ active set vào trong record. Vòng lặp sẽ tự kết thúc tự động khi bước lặp
cuối cùng lấy xong hàng cuối cùng của active set.
Một cấu trúc vòng lặp tự động:
- Mở cursor
- Lấy một hàng tại mỗi bước lặp
- Thoát khi tất cả các hàng đã được xử lý
- Đóng cursor.
Cấu trúc: FOR record-identifier IN cursor-identifier [(parameters)]
LOOP
xử lý hàng hiện tại
END LOOP;

FOR I IN 1 10 LOOP
END LOOP
DECLARE
CURSOR c1(param1 emp.hiredate%type) IS
SELECT ename,job FROM EMP WHERE hiredate < param1;
BEGIN
open for
FOR emp_rec IN c1(TO_DATE('23/1/1981','DD/MM/YYYY')) LOOP
dbms_output.put_line(emp_rec.ename);
END LOOP;
END;
12. Một vài điểm cần ghi nhớ các lệnh SQL trong PL/SQL:
- Mỗi câu lệnh SQL cần phải kết thúc bởi dấu ‘;’
Trang 6
- Các lệnh DDL không dùng được trong PL/SQL ->DYNAMIC SQL
- Các câu lệnh SELECT dùng trong một khối PL/SQL như là một câu lệnh thực thi được và được gọi là lệnh SQL nhúng.
Áp dụng quy tắc: Query cần trả về một và chỉ một hàng, ngược lại sẽ gây ra lỗi.
Khi lệnh SELECT không trả về hàng nào hoặc quá nhiều hàng thì sẽ gây ra lỗi:
+ ORA-01403 No data found.
+ ORA-01422 Exact fetch returns more than requested number of rows.
- Các lệnh DML có thể xử lý nhiều hàng.
Xem vi du:
DECLARE
empno NUMBER(4):= 7788;
BEGIN
UPDATE EMP SET sal=9000
WHERE empno=empno; KHONG CO TAC DUNG
END;
Khai bao record kieu con tro



CURSORS va RECORDS
Vd:
DECLARE
CURSOR C1 IS
SELECT EMPNO,ENAME,SAL,HIREDATE
FROM EMP
WHERE DEPTNO=’20’
AND JOB= ‘CLERK’
Cach 1:
emp_rec c1%ROWTYPE; note: table|vie_name|cursor_name%rowtype
BEGIN
OPEN C1;
FETCH C1 INTO emp_rec;
IF emp_rec.sal <15000 THEN …
…
CLOSE C1;
END;
Cach 2 :
v_empno EMP.empno%TYPE;
v_ename EMP.ename%TYPE;
v_sal EMP.sal%TYPE;
v_hirdate EMP.hiredate%TYPE;
Trang 7
BEGIN
OPEN C1;
FETCH C1 INTO v_empno,v_ename,v_sal,v_hiredate;
IF v_sal <15000 THEN……
…….
CLOSE C1;

END;

Trang 8