Đã đăng vào thg 5 1, 2016 6:31 SA 4 phút đọc
Stored Procedure là gì?
- Tương tự như khái niệm
function
trong các ngôn ngữ lập trình.Stored Procedure
được tạo ra nhằm thực hiện các lệnh của mysql theo một nhóm việc cụ thể thay vì thực hiện từng thao tác [insert,update,delete]. - Trong mysql thì
Stored Procedure
chỉ được hỗ trợ từ phiên bản 5 trở đi.
Ưu điểm của Stored Procedure
- Stored Procedure làm tăng hiệu xuất sử lý giữ liệu
- Làm giảm thời gian giao tiếp giữa ứng dụng với hệ quản trị cơ sở giữ liệu. thay vì gửi từng câu truy vấn thì nay sẽ chỉ gửi một Stored Procedure.
- module hóa ứng dụng: do Stored Procedure có thể lưu lại và sử dụng lại nhiều lần. điều này sẽ làm cho việc maintenance trở lên dễ dàng hơn.
- nâng cao tính bảo mật dữ liệu: việc thao tác với database thông qua stored procedure mà không cho thao tác trực tiếp với database sẽ làm giảm các cuộc tấn công thông qua sql injection
Nhược điểm của Stored Procedure
Nếu bạn 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. Ngoài ra nếu bạn thực hiện quá nhiều xử lý trong mỗi thủ tục thì đồng nghĩa với việc CPU sẽ làm việc nặng hơn, điều này không tốt chút nào.
Nếu sử dụng thủ tục thì sẽ rất khó phát triển trong ứng dụng, gây khó khăn ở mức logic business.
MYSQL thì không có tool hỗ trợ Debug Store.
Để phát triển ứng dụng thì bạn phải đòi hỏi có một kỹ năng nhất định mới có thể maintenance được.
Các kiểu Stored Procedure
Chủ yếu có 2 loại:
System stored procedures :
Thủ tục mà những người sử dụng chỉ có quyền thực hiện, không được phép thay đổi.Các tác vụ quản trị bao gồm : liệt kê, thêm, cập nhập, xóa.
- Kí hiệu : sp_..., xp_...
- Để thực thi 1 SP, ta sử dụng lệnh EXEC. VD: EXEC sp_databases [liệt kê danh sách các database có trong SQL server]
- Được chia thành các nhóm sau:
- SP dùng để liệt kê thông tin [liệt kê danh sách database ,...]
- SP dùng để trình bày thông tin [trình bày thông tin table, ...]
- SP dùng để thêm, xóa, cập nhập thông tin [đổi mật khẩu, ....]
User - Defined Stored Procedures:
Thủ tục do người dùng tạo và thực hiện.
- Là các SP do người dùng tạo.
- Thủ tục khi chạy sẽ theo 3 bước :
- Resolution: xử lý truy vấn, đọc và kiểm tra lỗi chính tả.
- Optimization : tối ưu hóa truy vấn.
- Compilation
- Một số quy tăc khi tạo SP:
- Mỗi thủ tục chỉ nên làm 1 nhiệm vụ
- Các lệnh CREATE TABLE và CREATE INDEX có thể được tạo và tham chiếu ngay trong cùng 1 thủ tục.
- Một thủ tục không thể tạo/xóa rồi lại tham chiếu đến thủ tục đấy.
- Các thủ tục có thể tham chiếu đến các bảng tạm thời.
- Có thể tham chiếu đến các đối tượng từ các CSDL khác và server từ xa. ....
Tạo Stored Procedure
Có thể sử dụng 1 trong các cách sau:
- Sử dụng SQL Server Management Studio [SSMS] Tạo bằng giao diện SSMS sẽ phát sinh script tạo Store Procedure sẵn, ta chỉ cần thêm nội dung vào trong thân hàm
- Transact-SQL Tạo procedure bằng Editor Query Cú pháp đơn giản:
CREATE PROCEDURE .
AS
Ví dụ:
CREATE PROCEDURE Users_GetUserInfo
@login nvarchar[30]=null
AS
SELECT * from users
WHERE ISNULL[@login,login]=login
Để thực thi precedure bằng script sử dụng lệnh EXECUTE:
EXECUTE procedure_name
hoặc
EXEC procedure_name
Kết luận
Procedure ít được sử dụng trong các website vì nó còn có nhiều nhược điểm. Vì vậy khi phát triển ứng dụng nên xem xét kĩ trước khi sử dụng .
All rights reserved
I. MySQL: Stored Procedure là gì?
Stored Procedure được định nghĩa như một tập các khai báo sql được lưu trữ ngay trong cơ sở dữ liệu [database] và sau đó, được triệu gọi bởi một program, một trigger hay thậm chí là một stored procedure khác.
Stored Procedured cho phép truyền tham số.
Ưu điểm của Stored Proccedure trong Mysql
Stored procedure làm tăng khả năng thực thi của ứng dụng.
Sau khi được tạo, stored procedure sẽ được biên dịch [compile] và lưu trữ ngay trong database. Lẽ dĩ nhiên, nó sẽ chạy nhanh hơn là một lệnh sql chưa compile được gửi trực tiếp từ ứng dụng.
Stored procedure làm giảm lưu lượng thông tin giao tiếp giữa ứng dụng và database server, thay vì gửi những câu lệnh sql chưa complile dài , ứng dụng chỉ việc gửi tên của stored procedure và lấy lại kết quả.
Những nhược điểm của Stored Procudure
- Nếu bạn 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.
- Ngoài ra nếu bạn thực hiện quá nhiều xử lý trong mỗi thủ tục thì đồng nghĩa với việc CPU sẽ làm việc nặng hơn.
- Stored procedure chỉ chứa đựng các khai báo sql, vì vậy rất khó có thể viết 1 procedure nhằm thực hiện các thao tác xử lý phức tạp như các ngôn ngữ khác làm được ở tần ứng dụng như php,c#...
- Một số hệ quản trị CSDL có những tool hỗ trợ Debug Store nhưng MYSQL thì không có.
Tạo Mysql Stored Procedure đầu tiên
DELIMITER $$
CREATE PROCEDURE procedureName[]
BEGIN
SELECT * FROM products;
END; $$
DELIMITER
Giải thích:
DELIMITER $$ dùng để phân cách bộ nhớ lưu trữ thủ tục Cache và mở ra một ô lưu trữ mới.
CREATE PROCEDURE procedureName[] dùng để khai báo tạo một Procedure mới, trong đó procedureName chính là tên thủ tục còn hai từ đầu là từ khóa.
**BEGIN và END; $$ ** dùng để khai báo bắt đầu của Procedure và kết thúc Procedure
**DELIMITER ; ** đóng lại ô lưu trữ
Sau đó chạy câu SQL này và nó báo thành công tức là bạn đã tạo mới một thủ tục với tên procedureName
Cách gọi procedure
CALL procedureName[];
** Sửa procedure **
Trong Mysql không cung cấp lệnh sửa Stored nên thông thường chúng ta sẽ chạy lệnh tạo mới.
Lệnh Drop để xóa đi Procedure đó và tạo lại:
DELIMITER $$
DROP PROCEDURE IF EXISTS `procedureName`$$
CREATE PROCEDURE procedureName[]
BEGIN
SELECT * FROM products;
END; $$
DELIMITER
** Phân quyền cho procedure **
Khi bạn dùng với quyền User nào thì Store đó sẽ có quyền thực hiện trong phạm vi của User đó.
Ví dụ bạn không có quyền edit mà bạn tạo mới một Procedure Edit thì khi chạy sẽ bị báo lỗi . Chính vì vậy thông thường khi edit bạn phải thêm người định nghĩa nó như sau:
DELIMITER $$
DROP PROCEDURE IF EXISTS `procedureName`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE procedureName[]
BEGIN
SELECT * FROM products;
END; $$
DELIMITER
DEFINER=root
@localhost
chính là tên người đã tạo.
III. Biến [variable] trong MYSQL Stored Procedure
Khai báo biếnDECLARE variable_name datatype[size] DEFAULT default_value
Giải thích:
DECLARE: là từ khóa tạo biến variable_name: là tên biến datatype[size]: là kiểu dữ liệu của biến và kích thước của nó DEFAULT default_value: là gán giá trị mặc định cho biến
Gán giá trị cho biếnSET variable_name = 'value';
Ví dụ: Định nghĩa biến age và gán giá trị 20 cho nó.
DECLARE a INT[11] DEFAULT 0
SET a = 10
Gán giá trị thông qua lệnh SELECT
DECLARE total INT DEFAULT 0
SELECT COUNT[*] INTO total FROM products
Câu lệnh trên nó sẽ thực hiện câu truy vấn SQL đếm tổng số record và sau đó gán vào biến total.
Nếu như bạn định nghĩa một biến giữa BEGIN và END thì đó ta gọi là biến cục bộ của Procedure. Bạn có thể định nghĩa nhiều biến trong một Procedure.
DELIMITER $$
DROP PROCEDURE IF EXISTS total $$
CREATE PROCEDURE total[]
BEGIN
DECLARE a INT [11] DEFAULT 0;
DECLARE b INT [11] DEFAULT 0;
DECLARE result INT [11] DEFAULT 0;
SET a = 1;
SET b = 2;
SET result = a + b;
SELECT result;
END; $$
DELIMITER;
IV Truyền tham số vào Mysql Stored Procedure
Trong MYSQL thì sẽ tồn tại ba loại Tham số đó là tham số IN, tham số OUT và tham số INOUT
IN: Đây là chế độ mặc định [nghĩa là nếu bạn không định nghĩa loại nào thì nó sẽ hiểu là IN].
OUT: Chế độ này nếu như trong Procedure có tác động thay đổi thì nó sẽ thay đổi theo. Nhưng có điều đặc biệt là dù trước khi truyền vào mà bạn gán giá trị cho biến đó thì vẫn sẽ không nhận được vì mặc định nó luôn hiểu giá trị truyền vào là NULL.
INOUT: Đây là sự kết hợp giữa IN và OUT. Nghĩa là có thể gán giá trị trước và có thể bị thay đổi nếu trong Procedure có tác động.
Table Products demo:
DELIMITER $$
DROP PROCEDURE IF EXISTS getById $$
CREATE PROCEDURE getProductId[
IN id INT[11],
IN title VARCHAR[255]
]
BEGIN
/*Code*/
END; $$
DELIMITER;
**Tham số loại IN **
DELIMITER $$
DROP PROCEDURE IF EXISTS getProId $$
CREATE PROCEDURE getProId[IN idpro INT[11]]
BEGIN
SELECT * FROM products WHERE id = idpro;
END; $$
DELIMITER;
Kết quả:
Tham số loại OUT trong Mysql Stored Procedure
Khi truyền tham số dạng OUT mục đích là lấy dữ liệu trong Proedure và sử dụng ở bên ngoài.
Khi truyền tham số vào dạng OUT phải có chữ @ đằng trước biến
Hoạt động giống tham chiếu nên biến truyền vào dạng OUT không cần định nghĩa trước, chính vì vậy khởi đầu nó có giá trị NULL
VD:
DELIMITER $$
DROP PROCEDURE IF EXISTS Title $$
CREATE PROCEDURE Title[OUT title VARCHAR[255]]
BEGIN
SET title = 'Title 2';
END; $$
DELIMITER;
Tham số dạng INOUT
INOUT là sự kết hợp giữa IN và OUT, nghĩa là:
DELIMITER $$
DROP PROCEDURE IF EXISTS counter $$
CREATE PROCEDURE counter[INOUT number INT[11]]
BEGIN
SET number = number + 1;
END; $$
DELIMITER;
Sử dụng:
SET @counter = 1;
CALL counter[@counter];
SELECT @counter;
V: Câu lệnh if else trong MYSQLIF if_expression THEN
commands
ELSEIF elseif_expression THEN
commands
ELSE
commands
END IF;
Tham khảo IF ELSE Mệnh đề if cho phép bạn tạo luồng xử lý rẻ nhánh, nếu đúng thì thực thi và ngược lại mệnh đề sai thì nó sẽ không thực thi.
Kết Luận: Bạn có thể thấy cách một Stored Procedure hoạt động không khác gì một hàm trong lập trình. Như vậy bạn đã học được cách tạo một Stored Procedure trong MySQL.
Link Tham khảo: mysql
mysqltutorial