›
›

Bài giảng Hệ quản trị cơ sơ dữ liệu Oracle - Chương 7: Ngôn ngữ PL/SQL - Ngô Thùy Linh

pdf 140 trang ngocly 3550
Download
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Hệ quản trị cơ sơ dữ liệu Oracle - Chương 7: Ngôn ngữ PL/SQL - Ngô Thùy Linh", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfbai_giang_he_quan_tri_co_so_du_lieu_oracle_chuong_7_ngon_ngu.pdf

Nội dung text: Bài giảng Hệ quản trị cơ sơ dữ liệu Oracle - Chương 7: Ngôn ngữ PL/SQL - Ngô Thùy Linh

  1. 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
  2. Procedural Language Extension to SQL Khoa HTTTQL – Học viện Ngân hàng
  3. Tài liệu tham khảo 7-3 Khoa HTTTQL – Học viện Ngân hàng
  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 7-4 Khoa HTTTQL – Học viện Ngân hàng
  5. 7-5 Khoa HTTTQL – Học viện Ngân hàng
  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ệ 7-6 Khoa HTTTQL – Học viện Ngân hàng
  7. Cách thực thi các lệnh PL/SQL PL/SQL Engine procedural Procedural PL/SQL Statement Block Executor SQL SQL Statement Executor Oracle Database Server 7-7 Khoa HTTTQL – Học viện Ngân hàng
  8. The PL/SQL Engine and Oracle Database 7-8 Khoa HTTTQL – Học viện Ngân hàng
  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 7-9 Khoa HTTTQL – Học viện Ngân hàng
  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à */ 7-10 Khoa HTTTQL – Học viện Ngân hàng
  11. Cấu trúc khối lệnh PL/SQL 7-11 Khoa HTTTQL – Học viện Ngân hàng
  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; 7-12 Khoa HTTTQL – Học viện Ngân hàng
  13. Các kiểu khối lệnh Anonymous Procedure Functions [DECLARE] PROCEDURE name FUNCTION name RETURN datatype IS IS BEGIN BEGIN BEGIN statements statements statements RETURN VALUE; [EXCEPTIONS] [EXCEPTIONS] [EXCEPTIONS] END; END; END; 7-13 Khoa HTTTQL – Học viện Ngân hàng
  14. 7-14 Khoa HTTTQL – Học viện Ngân hàng
  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ự) 7-15 Khoa HTTTQL – Học viện Ngân hàng
  16. Biến (variable) Biến là gì?  Đặ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 7-16 Khoa HTTTQL – Học viện Ngân hàng
  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 7-17 Khoa HTTTQL – Học viện Ngân hàng
  18. Khai báo biến Syntax variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; Notice that PL/SQL includes all Examples SQL types, and more Declare birthday DATE; age NUMBER(2) NOT NULL := 27; name VARCHAR2(13) := 'Levi'; magic CONSTANT NUMBER := 77; valid BOOLEAN NOT NULL := TRUE; 7-18 Khoa HTTTQL – Học viện Ngân hàng
  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 7-19 Khoa HTTTQL – Học viện Ngân hàng
  20. Các kiểu dữ liệu trong PL/SQL 7-20 Khoa HTTTQL – Học viện Ngân hàng
  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 7-21 Khoa HTTTQL – Học viện Ngân hàng
  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 7-22 Khoa HTTTQL – Học viện Ngân hàng
  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 7-23 Khoa HTTTQL – Học viện Ngân hàng
  24. Gán giá trị  Có thể gán giá trị theo 2 cách • Gán trực tiếp: variable_name := value ; • 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]; Chú ý: Toán tử gán trong PL/SQL là := Toán tử gán trong các lệnh SQL là = 7-24 Khoa HTTTQL – Học viện Ngân hàng
  25. Nhận và hiển thị giá trị biến (Kiểu số)  Cách 1: Ví dụ 1 7-25 Khoa HTTTQL – Học viện Ngân hàng
  26. Nhận và hiển thị giá trị biến (tt) (Kiểu số)  Cách 1: Ví dụ 2 7-26 Khoa HTTTQL – Học viện Ngân hàng
  27. Nhận và hiển thị giá trị biến (tt) (Kiểu số)  Cách 2 7-27 Khoa HTTTQL – Học viện Ngân hàng
  28. Nhận và hiển thị giá trị biến (tt) (Kiểu số)  Cách 3 7-28 Khoa HTTTQL – Học viện Ngân hàng
  29. Nhận và hiển thị giá trị biến (Kiểu ký tự)  Ví dụ 1 7-29 Khoa HTTTQL – Học viện Ngân hàng
  30. Nhận và hiển thị giá trị biến (Kiểu ký tự)  Ví dụ 2: 7-30 Khoa HTTTQL – Học viện Ngân hàng
  31. Nhận và hiển thị giá trị biến (Kiểu ký tự)  Ví dụ 3 7-31 Khoa HTTTQL – Học viện Ngân hàng
  32. Nhận và hiển thị giá trị biến (Kiểu ngày tháng)  Ví dụ 1 7-32 Khoa HTTTQL – Học viện Ngân hàng
  33. Nhận và hiển thị giá trị biến (Kiểu ngày tháng)  Ví dụ 2 7-33 Khoa HTTTQL – Học viện Ngân hàng
  34.  Ví dụ 3 7-34 Khoa HTTTQL – Học viện Ngân hàng
  35. 7-35 Khoa HTTTQL – Học viện Ngân hàng
  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ỏ 7-36 Khoa HTTTQL – Học viện Ngân hàng
  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 7-37 Khoa HTTTQL – Học viện Ngân hàng
  38.  Ví dụ: dữ liệu của bangcha anonymous block completed Khi chạy khối lệnh trên 0903-002-004 Nguoi Cha ma so 10 co so dien thoai la 0903-002-004 7-38 Khoa HTTTQL – Học viện Ngân hàng
  39. Lấy dữ liệu từ Database với lệnh SELECT (tt)  Số cán bộ ở Hà nội? 7-39 Khoa HTTTQL – Học viện Ngân hàng
  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. 7-40 Khoa HTTTQL – Học viện Ngân hàng
  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 7-41 Khoa HTTTQL – Học viện Ngân hàng
  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 7-42 Khoa HTTTQL – Học viện Ngân hàng
  43. Khai báo biến với %TYPE Examples Accessing column sname in table Sailors DECLARE v_sname Sailors.sname%TYPE; fav_boat VARCHAR2(30); my_fav_boat fav_boat%TYPE := 'Pinta'; Accessing another variable 7-43 Khoa HTTTQL – Học viện Ngân hàng
  44. Khai báo biến với %ROWTYPE Declare a variable with the type of a ROW of a table. Accessing table Reserves reserves_record Reserves%ROWTYPE; And how do we access the fields in reserves_record? reserves_record.sid:=9; Reserves_record.bid:=877; 7-44 Khoa HTTTQL – Học viện Ngân hàng
  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 7-45 Khoa HTTTQL – Học viện Ngân hàng
  46. Lấy dữ liệu trong PL/SQL 7-46 Khoa HTTTQL – Học viện Ngân hàng
  47. Lấy dữ liệu trong PL/SQL  Lấy ra thông tin cán bộ có mã số cb2 7-47 Khoa HTTTQL – Học viện Ngân hàng
  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 • DELETE INSERT UPDATE 7-48 Khoa HTTTQL – Học viện Ngân hàng
  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 7-49 Khoa HTTTQL – Học viện Ngân hàng
  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 7-50 Khoa HTTTQL – Học viện Ngân hàng
  51. Xóa dữ liệu  Xóa cán bộ có mã số là cb17 7-51 Khoa HTTTQL – Học viện Ngân hàng
  52. 7-52 Khoa HTTTQL – Học viện Ngân hàng
  53. Cấu trúc điều khiển  Rẽ nhánh • IF THEN ELSE • CASE WHEN  Lặp • Vòng lặp đơn giản • Vòng lặp WHILE • Vòng lặp FOR for loop • GOTO while 7-53 Khoa HTTTQL – Học viện Ngân hàng
  54. Rẽ nhánh IF THEN ELSE CASE WHEN IF condition THEN CASE selector statements; WHEN expression1 THEN result1 [ELSIF condition THEN WHEN expression2 THEN result2 statements;] [ELSE WHEN expressionN THEN resultN statements;] [ELSE resultN+1] END IF; END; 7-54 Khoa HTTTQL – Học viện Ngân hàng
  55. Các lệnh điều khiển luồng chương trình  Lệnh IF 7-55 Khoa HTTTQL – Học viện Ngân hàng
  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ẻ 7-56 Khoa HTTTQL – Học viện Ngân hàng
  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đ 7-57 Khoa HTTTQL – Học viện Ngân hàng
  58. Lệnh IF (tt) 7-58 Khoa HTTTQL – Học viện Ngân hàng
  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 7-59 Khoa HTTTQL – Học viện Ngân hàng
  60. 7-60 Khoa HTTTQL – Học viện Ngân hàng
  61. CASE-WHEN 7-61 Khoa HTTTQL – Học viện Ngân hàng
  62. Các lệnh điều khiển luồng chương trình  Điều khiển lặp • Vòng lặp cơ bản • Vòng lặp FOR • Vòng lặp WHILE 7-62 Khoa HTTTQL – Học viện Ngân hàng
  63. Ví dụ 1: Lệnh LOOP Result 7-63 Khoa HTTTQL – Học viện Ngân hàng
  64. Ví dụ 2: Lệnh LOOP Result 7-64 Khoa HTTTQL – Học viện Ngân hàng
  65. Loops: Simple Loop create table number_table (id NUMBER, num NUMBER); 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; 7-65 Khoa HTTTQL – Học viện Ngân hàng
  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 7-66 Khoa HTTTQL – Học viện Ngân hàng
  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; 7-67 Khoa HTTTQL – Học viện Ngân hàng
  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; 7-68 Khoa HTTTQL – Học viện Ngân hàng
  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; 7-69 Khoa HTTTQL – Học viện Ngân hàng
  70. 7-70 Khoa HTTTQL – Học viện Ngân hàng
  71. Cursor  Cursor? • Cursors are memory areas where Oracle executes SQL statements, stores processing information Select From Where SERVER RAM Active Data Set Cursor Current Row Contents of a Cursor 7-71 Khoa HTTTQL – Học viện Ngân hàng
  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 7-72 Khoa HTTTQL – Học viện Ngân hàng
  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 7-73 Khoa HTTTQL – Học viện Ngân hàng
  74. IMPLICIT CURSOR  Ví dụ 1: 7-74 Khoa HTTTQL – Học viện Ngân hàng
  75. number_table IMPLICIT CURSOR(tt)  Ví dụ 2 7-75 Khoa HTTTQL – Học viện Ngân hàng
  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 7-76 Khoa HTTTQL – Học viện Ngân hàng
  77. Làm việc với Explicit Cursor  Explicit Cursor NO YES? DECLARE OPEN FETCH EMPTY ? CLOSE •Release • Create a •Identify •Load the the named SQL the active current active area set row into set variables 7-77 Khoa HTTTQL – Học viện Ngân hàng
  78. Điều khiển Explicit Cursors (tt) 1 Open the cursor. Cursor pointer 2 Fetch a row. Cursor pointer Cursor 3 Close the cursor. pointer 7-78 Khoa HTTTQL – Học viện Ngân hàng
  79. Example RAD_VALS DECLARE Pi constant NUMBER(8,7) := 3.1415926; radius area NUMBER(14,2); CURSOR rad_cursor IS Rad_cursor 3 select * f from RAD_VALS; e 6 rad_val rad_cursor%ROWTYPE; t 8 BEGIN c OPEN rad_cursor; h FETCH rad_cursor INTO rad_val; Rad_val area:=pi*power(rad_val.radius,2); insert into AREAS values (rad_val.radius, area); AREAS CLOSE rad_cursor; Radius Area END; 3 28.27 7-79 Khoa HTTTQL – Học viện Ngân hàng
  80. Bảng AREAS 7-80 Khoa HTTTQL – Học viện Ngân hàng
  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 7-81 Khoa HTTTQL – Học viện Ngân hàng
  82. Explicit Cursor number_table  Ví dụ 1: Lấy id và num của bảng number_table và chèn vào bảng temp_list temp_list RESULT 7-82 Khoa HTTTQL – Học viện Ngân hàng
  83. Explicit Cursor number_table  Ví dụ 2: Lấy thông tin trong bảng number_table và in ra màn hình Result 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 7-83 Khoa HTTTQL – Học viện Ngân hàng
  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 7-84 Khoa HTTTQL – Học viện Ngân hàng
  85. 7-85 Khoa HTTTQL – Học viện Ngân hàng
  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 7-86 Khoa HTTTQL – Học viện Ngân hàng
  87. Module trong PL/SQL  Có 4 loại module trong PL/SQL • Procedures • Functions • Triggers • Packages 7-87 Khoa HTTTQL – Học viện Ngân hàng
  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 7-88 Khoa HTTTQL – Học viện Ngân hàng
  89. Thủ tục  Tạo thủ tục CREATE [OR REPLACE] PROCEDURE procedure_name [( parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)] IS|AS [declaration_section] BEGIN END ;  Thi hành thủ tục • EXECUTE [or EXEC] procedure_name; • procedure_name; 7-89 Khoa HTTTQL – Học viện Ngân hàng
  90. Thủ tục (tt)  3 kiểu tham số MODE DESCRIPTION USAGE IN Passes a value into the program Read-only Constants, literals, expressions value Cannot be changed within the program’s default mode OUT Passes a value back from the program Write-only Cannot assign default values value Must be a variable A value is assigned only if the program is successful IN OUT Passes values in and also sends values back Has to be a variable Chú ý: mặc định là kiểu IN 7-90 Khoa HTTTQL – Học viện Ngân hàng
  91. Thủ tục (tt)  Ví dụ 1: Tạo thủ tục tính tổng hai số 7-91 Khoa HTTTQL – Học viện Ngân hàng
  92. Thủ tục (tt)  Thi hành thủ tục:  Cách 1  Cách 2 7-92 Khoa HTTTQL – Học viện Ngân hàng
  93. Thủ tục (tt) Ví dụ 2 Table: my_log create or replace procedure num_logged logon_ who (person IN my_log.who%TYPE, num num OUT my_log.logon_num%TYPE) IS BEGIN Peter 3 select logon_num into num from my_log John 4 where who = person; END; Joe 2 7-93 Khoa HTTTQL – Học viện Ngân hàng
  94. Thủ tục (tt) 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; 7-94 Khoa HTTTQL – Học viện Ngân hàng
  95.  Ví dụ 3: Tạo thủ tục nhập thêm một hàng vào bảng my_log 7-95 Khoa HTTTQL – Học viện Ngân hàng
  96.  Ví dụ 3’’: Tạo thủ tục nhập thêm một hàng vào bảng my_log 7-96 Khoa HTTTQL – Học viện Ngân hàng
  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 ; 7-97 Khoa HTTTQL – Học viện Ngân hàng
  98. Hàm (tt)  Ví dụ 1: Tạo hàm để tính hiệu của hai số 7-98 Khoa HTTTQL – Học viện Ngân hàng
  99. Hàm (tt)  Sử dụng hàm  Cách 1  Cách 2 7-99 Khoa HTTTQL – Học viện Ngân hàng
  100. Hàm (tt) 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 create or replace function rating_message(rating IN NUMBER) return VARCHAR2 AS NOTE THAT YOU DON'T BEGIN SPECIFY THE SIZE IF rating > 7 THEN RETURN 'You are great'; ELSIF rating >= 5 THEN RETURN 'Not bad'; ELSE RETURN 'Pretty bad'; END IF; END; 7-100 Khoa HTTTQL – Học viện Ngân hàng
  101. Hàm (tt) Gọi hàm rating_Message set serveroutput on declare paulRate number:= 3; begin dbms_output.put_line(rating_Message(paulRate)); end; 7-101 Khoa HTTTQL – Học viện Ngân hàng
  102. Hủy bỏ và sửa thủ tục/hàm  Hủy DROP PROCEDURE tên_thủ_tục; DROP FUNCTION tên_hàm;  Sửa ALTER PROCEDURE tên_thủ_tục ALTER FUNCTION tên_hàm 7-102 Khoa HTTTQL – Học viện Ngân hàng
  103. So sánh thủ tục và hàm Thủ tục Hàm Thực hiện giống như thực Có thể được gọi giống như hiện các câu lệ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 Trả về một giá trị nhiều tham số Không chứa lệnh thay đổi dữ liệu 7-103 Khoa HTTTQL – Học viện Ngân hàng
  104. TRIGGER Khoa HTTTQL – Học viện Ngân hàng
  105. Trigger 1. Trigger là gì? 2. Trigger dùng để làm gì?  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 7-105 Khoa HTTTQL – Học viện Ngân hàng
  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 7-106 Khoa HTTTQL – Học viện Ngân hàng
  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 7-107 Khoa HTTTQL – Học viện Ngân hàng
  108. Phân loại trigger 7-108 Khoa HTTTQL – Học viện Ngân hàng
  109. Trigger  Tạo Trigger 7-109 Khoa HTTTQL – Học viện Ngân hàng
  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; 7-110 Khoa HTTTQL – Học viện Ngân hàng
  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; 7-111 Khoa HTTTQL – Học viện Ngân hàng
  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 7-112 Khoa HTTTQL – Học viện Ngân hàng
  113. Quản lý trigger  Thay đổi trạng thái ALTER TRIGGER trigger_name DISABLE | ENABLE; ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS;  Hủy trigger DROP TRIGGER trigger_name; 7-113 Khoa HTTTQL – Học viện Ngân hàng
  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 7-114 Khoa HTTTQL – Học viện Ngân hàng
  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 7-115 Khoa HTTTQL – Học viện Ngân hàng
  116. TRIGGER (tt) 7-116 Khoa HTTTQL – Học viện Ngân hàng
  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 7-117 Khoa HTTTQL – Học viện Ngân hàng
  118.  Kết quả khi chạy khối lệnh TEST  Kiểm tra lại thông tin của bảng my_log INSERTED UPDATED Joe was deleted 7-118 Khoa HTTTQL – Học viện Ngân hàng
  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 7-119 Khoa HTTTQL – Học viện Ngân hàng
  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 Dữ liệu cũ và mới sẽ được ghi vào bảng my_log_history Sử dụng OLD, NEW để lấy giá trị cũ và mới 7-120 Khoa HTTTQL – Học viện Ngân hàng
  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 7-121 Khoa HTTTQL – Học viện Ngân hàng
  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 7-122 Khoa HTTTQL – Học viện Ngân hàng
  123. TRIGGER (tt)  Tạo Trigger trên View 7-123 Khoa HTTTQL – Học viện Ngân hàng
  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 7-124 Khoa HTTTQL – Học viện Ngân hàng
  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 7-125 Khoa HTTTQL – Học viện Ngân hàng
  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 7-126 Khoa HTTTQL – Học viện Ngân hàng
  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??? 7-127 Khoa HTTTQL – Học viện Ngân hàng
  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 7-128 Khoa HTTTQL – Học viện Ngân hàng
  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 7-129 Khoa HTTTQL – Học viện Ngân hàng
  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. 7-130 Khoa HTTTQL – Học viện Ngân hàng
  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 7-131 Khoa HTTTQL – Học viện Ngân hàng
  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 7-132 Khoa HTTTQL – Học viện Ngân hàng
  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 7-133 Khoa HTTTQL – Học viện Ngân hàng
  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 7-134 Khoa HTTTQL – Học viện Ngân hàng
  135. TRIGGER (tt)  1) BEFORE UPDATE, Statement Level: 7-135 Khoa HTTTQL – Học viện Ngân hàng
  136. TRIGGER (tt)  2) BEFORE UPDATE, Row Level: 7-136 Khoa HTTTQL – Học viện Ngân hàng
  137. TRIGGER (tt)  3) AFTER UPDATE, Statement Level: 7-137 Khoa HTTTQL – Học viện Ngân hàng
  138. TRIGGER (tt)  4) AFTER UPDATE, Row Level: 7-138 Khoa HTTTQL – Học viện Ngân hàng
  139. TRIGGER (tt)  Thực hiện lệnh update trên bảng my_log 7-139 Khoa HTTTQL – Học viện Ngân hàng
  140. 7-140 Khoa HTTTQL – Học viện Ngân hàng