Hướng dẫn tạo function trong mysql

Đã đăng vào Mar 19th, 2015 12:14 p.m. 5 phút đọc

database sử dụng trong ví dụ

View

• 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.

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 đ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]

• 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.

• 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ệ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];

• 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;

• 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 đ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

• 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

• 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]

All rights reserved