VIEW, FUNCTION & STORE PROCEDURE VÀ TRIGGER TRONG MYSQL

database sử dụng trong ví dụ

View

View là gì

• View là các truy vấn SELECT được lưu lại như là một table và sau đó ta có thể truy vấn dữ liệu từ view như thực hiện với table.
• Một số lưu ý đối với câu lệnh SELECT trong view:
  • Không sử dụng biến local, user hay session
  • Những lệnh dịch sẵn cũng không thể sử dụng
  • Bảng tạm hay các view khác cũng không được sử dụng làm nguồn dữ liệu cho câu truy vấn này.
  • Không thể tạo trigger cho view.

Cú pháp

CREATE VIEW view_name AS
SELECT statement

Ví dụ:

CREATE VIEW address_people AS
SELECT
a.name AS name,
a.age AS age,
c.name AS province_name
FROM people a
JOIN district b ON a.district_id = b.id
JOIN  province c ON b.id_province = c.id

Ưu nhược điểm

• Ưu điểm

  • An ninh – Cho phép người quản trị CSDL (DBA) cung cấp cho người sử dụng chỉ những dữ liệu cần thiết
  • Đơn giản hóa – Các view cũng có thể sử dụng để ẩn và sử dụng lại các truy vấn phức tạp => rất tiện với những câu truy vấn còng kềnh dài hàng trang A4

• Nhược điểm:

  • Tốn thêm tài nguyên trên Server (memory, process)

Function và Store Procedure (hàm/thủ tục)

Function và Store Procedure là gì

• Là đoạn chương trình kịch bản (programming scripts) với các câu lệnh SQL nhúng (embedded SQL) được lưu dưới dạng đã được biên dịch và thi hành thực tiếp bởi MySQL server.
• SP cho phép lưu trữ các logic ứng dụng trên CSDL.

Cú pháp

• Tạo hàm/thủ tục

CREATE FUNCTION name ([parameterlist]) RETURNS datatype [options] sqlcode
CREATE PROCEDURE name ([parameterlist]) [options] sqlcode

Ví dụ:

DELIMITER $$
CREATE PROCEDURE count_people_with_age(IN age_in INT, OUT quantity INT)
BEGIN
    SELECT *
    FROM people
    WHERE age >= age_in;

    SELECT FOUND_ROWS() INTO quantity;
END

• DELIMITER $$: Giải phóng để cho phép sử dụng ‘;’ trong thủ tục
• Gán giá trị cho biến
  • Sử dụng SET hoặc SELECT INTO.
• Gọi thủ tục:

Call count_people_with_age (18,@people_count);
Select @ people _count;

• Xóa Hàm/Thủ tục:

DROP FUNCTION [IF EXISTS] name
DROP PROCEDURE [IF EXISTS] name

• Hiện thị thông tin

SHOW FUNCTION STATUS;
SHOW PROCEDURE STATUS;

SHOW FUNCTION STATUS LIKE 'repeat%';
SHOW PROCEDURE STATUS LIKE 'film%';
SHOW CREATE PROCEDURE 'name';

Một số cấu trúc điều khiển dùng trong Hàm, thủ tục:

• Mệnh đề IF THEN

IF condition THEN
    commands;
[ELSE IF condition THEN
    commands;]
[ELSE
    commands;]
END IF;

• Mệnh đề REPEAT UNTIL

[loopname:]
REPEAT commands;
UNTIL condition
END REPEAT
[loopname];

• Mệnh đề CASE

CASE expression
WHEN value1 THEN commands;
[WHEN value2 THEN commands;]
[ELSE commands;]
END CASE;

• Mệnh đề WHILE

[loopname:]
WHILE condition
DO commands;
END WHILE
[loopname];

Con trỏ (cursor) trong hàm/thủ tục

• MySQL hỗ trợ con trỏ trong hàm/thủ tục, trigger
• Con trỏ được sử dụng để duyệt các dòng kết quả trả lại bởi truy vấn, và xử lý từng dòng riêng lẻ
• Cú pháp Con trỏ:

DECLARE cursor_name CURSOR FOR SELECT_statement;
OPEN cursor_name;

• Lấy ra từng dòng và chuyển tới dòng kết quả tiếp theo sử dụng lệnh FETCH

FETCH cursor_name INTO variable list;
CLOSE cursor_name;

Xử lý lỗi thông qua Handlers

• Luôn có khả năng hàm/thủ tục gặp lỗi trong khi thi hành các lệnh SQL. MySQL cung cấp kỹ thuật xử lý lỗi thông qua handler
• Một handler cần định nghĩa sau khai báo các biến, con trỏ và điều kiện, nhưng trước các lệnh SQL
• Cú pháp Handler:

DECLARE type HANDLER FORcondition1, condition2, condition3, ... statement;

• type: CONTINUE hoặc EXIT
• condition(s): Các điều kiện mà handler sẽ được gọi : Not found, SqlException,..
• statement: Câu lệnh sẽ thi hành khi có điều kiện xảy ra

Ưu nhược điểm

• Ưu điểm
  • Giảm dư thừa mã chương trình: Các đoạn mã tương tự trong các ứng dụng như thêm, cập nhật có thể lưu ở phía CSDL
  • Bảo trì: Nếu có sự thay đổi trong CSDL, mã lệnh cần thay đổi có thể xác định trong các SP
  • An ninh CSDL tốt hơn: Trong các ứng dụng an ninh cao, với hàm/thủ tục có thể kiểm soát truy cập dữ liệu và đưa ra các qui định an ninh tập trung
• Nhược điểm
  • Thiếu tính khả chuyển (Lack of Portability)
    SP khó chuyển từ một DBMS sang một DBMS khác. Yêu cầu lập trình và kiểm thử lại đáng kể
  • Tải DB Server
    Sử dụng hàm/thủ tục nhiều có thể gây quá tải MySQL server
  • Hạn chế ngôn ngữ lập trình
    Lập trình SP không phong phú như các nền tảng phát triển khác như Java hay PHP
    Lưu ý: Các phiên bản tương lai của MySQL có thể cung cấp các giao diện cho phép tạo các hàm/thủ tục sử dụng các ngôn ngữ bên ngoài, ví dụ Java

Trigger

Trigger là gì

• Triggers là quá trình tự động thi hành các lệnhSQL hoặc hàm/thủ tục sau hoặc trước các lệnh INSERT, UPDATE, hoặc DELETE.
• Các ứng dụng có thể bao gồm: lưu lại thay đổi hoặc cập nhật dữ liệu các bảng khác.
• Trigger chạy sau mỗi câu lệnh cập nhật bảng do đó có thể thêm tải với CSDL

Cú pháp

• Tạo trigger:

CREATE TRIGGER name BEFORE | AFTER INSERT | UPDATE | DELETE ON tablename
FOR EACH ROW sql-code

• Xóa trigger:

DROP TRIGGER tablename.triggername

• Sửa, xem thông tin trigger:

ALTER TRIGGER, SHOW CREATE TRIGGER, hoặc SHOW TRIGGER STATUS

• Để hiển thị các trigger gắn với 1 bảng dữ liệu:

SELECT * FROM Information_Schema.Trigger
WHERE Trigger_schema = 'database_name' AND Event_object_table = 'table_name';

• Cú pháp lệnh bên trong tương tự hàm/thủ tục
• Trong trigger, mã lệnh có thể truy cập các cột của bản ghi hiện tại
  • OLD.columnname trả lại nội dung của bản ghi trước khi bị thay đổi hoặc xóa (UPDATE, DELETE)
  • NEW.columnname trả lại nội dung của bản ghi mới hoặc bản ghi thay thế (INSERT, UPDATE)