Giới thiệu về PL/SQL

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...
Nội dung trích xuất từ tài liệu:
Giới thiệu về PL/SQL 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 1BEGIN --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 blockEND;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.67Kiểu chuỗi:VARCHAR2,CHARKIỂU Luận lý:BOOLEAN: True|FalseKiểu ngày:DATE4. Khai báo biếnvd: 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 25. Khai báo hằngví dụ: pi CONSTANT NUMBER(9,5):= 3.14167Cú 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 thihà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 để chophé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) TRUE nếu có ít nhất 1 hàng đã được xử lý, ngược lại là FALSE. - SQL%FOUND: - 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 Lỗi Oracle Tên Exception 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ácEX: 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: , 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 viewcú pháp: indentifier reference%ROWTYPE;ex: emp_rec EMP%ROWTYPE; (empno,ename,hiredate,sal,comm)emp_rec empno number ename varchar2… Trang 4emp_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 ...