Sử dụng view, trigger và procedure trong mysql

1. Bảng ảo View

• View là bảng ảo giúp giới hạn truy cập một số cột dòng trên các bảng dữ liệu.

• Views đảm bảo tính bảo mật bằng các giới hạn truy cập sau:

  • Các hàng trên một bảng
  • Các cột trên một bảng
  • Các hàng và cột trên một bảng
  • Các hàng giữa các bảng sử dụng phép joins

• Thuận lợi khi sử dụng view

  • Cung cấp dữ liệu cần thiết cho người dùng
  • Che dấu đi sự phức tạp của dữ liệu
  • Tổ chức dữ liệu từ nhiều tài nguyên không đồng nhất
  • Giảm kích cỡ của đối tượng

• Cú pháp tạo view

CREATE VIEW ViewName
AS
//Query

Query là một truy vấn mysql, lưu ý khi sử dụng câu lệnh select:

o Không sử dụng biến local, user hay session

o Những lệnh dịch sẵn cũng không thể sử dụng

o Bảng tạm hay các bảng ảo 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.

o Không thể tạo trigger cho bảng ảo.

• Ví dụ tạo view

CREATE VIEW SalePerOrder
   AS
   SELECT orderNumber,
   SUM  (quantityOrdered * priceEach) total
   FROM orderDetails
   GROUP by orderNumber
   ORDER BY total DESC

Sử dụng view:

SELECT total
   FROM salePerOrder
   WHERE orderNumber = 1000

• Ưu nhược điểm của view

1. Ư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
2. Nhược điểm
   • Không thể chứa mệnh ñề COMPUTE hoặc COMPUTE BY
   • Vì tạo ra bảng tạm nên view sẽ làm tốn tài nguyên, chậm hệ thống.
   • Không thể chứa mệnh ñề ORDER BY trừ khi có TOP n
   • Không thể chứa mệnh ñề INTO
   • Không thể chứa các table tạm
   • Có tối đa 1024 cột

• Xóa view

Có thể xóa view từ database sử dụng cú pháp:

 DROP VIEW view_name

2. Trigger

• Khái niệm về trigger

Trigger là một đối tượng được định danh trong CSDL và được gắn chặt với một sự kiện xảy ra trên một bảng nào đó (điều này có nghĩa là nó sẽ được tự động thực thi khi xảy ra một sự kiện trên một bảng). Các sự kiện này bao gồm: chèn (Insert), xóa (Delete) hay cập nhật (Update) một bảng.

• Lợi ích khi sử dụng trigger

Bởi vì trigger được thực thi tự động khi xuất hiện một hành động thay đổi trong bảng, nên người ta có thể ứng dụng trigger để tạo ra các công việc tự động thay cho việc phải làm thủ công bằng tay như: kiểm tra dữ liệu, đồng bộ hóa dữ liệu, đảm bảo các mối quan hệ giữa các bảng...

• Cách sử dụng

1.Tạo trigger

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

2.Xóa trigger

DROP TRIGGER tablename.trigger_name

3.Sửa, xem thông tin trigger:

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

4.Để hiển thị các trigger:

• Hiển thị tất cả các trigger

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

• Xem nội dung trigger

SELECT * FROM Information_Schema.Trigger
WHERE Trigger_schema = 'database_name' AND
Trigger_name = 'trigger_name';

• Xem tất cả trigger của một bảng dữ liệu

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

3. Stored procedure

• Khái niệm về stored procedure

Stored Procedure là một tập hợp các câu lệnh SQL dùng để thực thi một nhiệm vụ nhất định. Nó hoạt động giống như một hàm trong các ngôn ngữ lập trình khác.

Stored procedure là một khái niệm khá phổ biến và được hầu hết các hệ quản trị cơ sở dữ liệu (DBMS) hỗ trợ, tuy nhiên không phải tất cả đều hỗ trợ Stored Procedure.

• Cú pháp tạo stored procedure

DELIMITER //
BEGIN
CREATE PROCEDURE  stored_procedure_name
/*** SQL for stored procedure ***/
END //
DELIMITER ;

Chú ý: chúng ta sử dụng delimiter và khi bắt đầu và kết thúc nội dung của một Stored Procedure. Dilimeter là một chuỗi các ký tự tùy ý chúng không có ý nghĩa gì đặc biệt và được MySQL dùng để đánh dấu sự bắt đầu và kết thúc của một Stored Procedure. Ở trên chúng ta sử dụng chuỗi ký tự để làm delimiter

Trong khối BEGIN-END là nội dung của stored procedure. Nội dung của câu query có thể sử dụng các hàm xử lý chuỗi, câu lệnh if else, lệnh switch case và những hàm hỗ trợ khác như replace, count, concat…

• Sử dụng:

CALL stored_procedure_name;

• Ưu điểm:

  • Tăng hiệu suất xử lý của ứng dụng web, vì chúng được lưu trong hệ quản trị cơ sở dữ liệu chỉ cần gọi lại là có thể sử dụng.
  • Giúp giảm thời gian giao tiếp giữa các ứng dụng với hệ quản trị MYSQL, bởi thay vì gửi nhiều câu lệnh dài thì ta chỉ cần gọi tới một thủ tục và trong thủ tục này sẽ thực hiện nhiều câu lệnh SQL.
  • Dễ sử dụng và ứng dụng dễ nhìn hơn, nghĩa là khi ta đã định nghĩa một Stored thì công việc của chúng ta chỉ quan tâm đến tên thủ tục, các tham số truyền vào.
  • Vì nó được thực hiện trong MySQL nên nó còn liên quan đến quyền user trong MySQL, phải cấp quyền cho user mới có thể sử dụng.

• Nhược điểm:

  • Tạo ra quá nhiều Procedure thì hệ quản trị sẽ sử dụng bộ nhớ để lưu trữ các thủ tục này khá nhiều.
  • Quá nhiều xử lý trong một thủ tục thì CPU sẽ làm việc nặng hơn.
  • Nếu sử dụng thủ tục thì sẽ rất khó bảo trì và nâng cấp phát triển ứng dụng về sau.