Giáo trình Hệ quản trị cơ sở dữ liệu Oracle - Chương 7: Ngôn ngữ PL/SQL - Ngô Thị 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