Một thủ tục trong MySql có cú pháp như sau :
DELIMITER {custom delimiter}
CREATE PROCEDURE {procedureName}([optional parameters])
BEGIN
// procedure body...
// procedure body...
END
{custom delimiter}Ví dụ
Ta có bảng customer như sau :
Table "customer"
Column | Type | Modifiers
------------+-------------+-----------
id | integer | not null
address | varchar(64) |
first_name | varchar(32) |
last_name | varchar(32) |
email | varchar(64) |
Indexes:
"customer_pkey" PRIMARY KEY, btree (id)
Ta muốn tạo một bảng temp_customer có cấu trúc giống hệt bảng customer; và bảng này có dữ liệu lấy từ bảng customer :
DROP TABLE IF EXISTS temp_customer;
CREATE TABLE temp_customer
(
id binary(16) not null primary key,
address varchar(64),
first_name varchar(32),
last_name varchar(32),
email varchar(64)
);
Ta định nghĩa một thủ tục như sau :
DROP PROCEDURE IF EXISTS ADD_TEMP_CUSTOMER;
DELIMITER $$
CREATE PROCEDURE ADD_TEMP_CUSTOMER()
BEGIN
DECLARE retrieved_address varchar(64);
DECLARE retrieved_first_name varchar(32);
DECLARE retrieved_last_name varchar(32);
DECLARE retrieved_email varchar(64);
DECLARE new_id binary(16);
DECLARE num_row int;
DECLARE finished INTEGER DEFAULT 0;
DECLARE customer_cursor CURSOR FOR
SELECT address, first_name, last_name, email FROM customer;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN customer_cursor;
insert_loop:
LOOP
FETCH customer_cursor INTO retrieved_address, retrieved_first_name, retrieved_last_name, retrieved_email;
IF finished THEN
LEAVE insert_loop;
END IF;
SET @temp_count = (SELECT count(*) FROM temp_customer);
SELECT count(*) into num_row FROM temp_customer;
IF num_row = 0 THEN
SET new_id = UUID_TO_BIN(UUID());
INSERT IGNORE INTO temp_customer(id, address, first_name, last_name, email)
VALUES (new_id, retrieved_address, retrieved_first_name, retrieved_last_name, retrieved_email);
END IF;
END LOOP;
CLOSE customer_cursor;
END $$
DELIMITER ;
CALL ADD_TEMP_CUSTOMER();
DROP PROCEDURE IF EXISTS ADD_TEMP_CUSTOMER;
Giải thích : thủ tục này sẽ dùng một cursor để đọc hết bảng customer và lấy thông tin từ đây điền vào bảng temp_customer
Chú ý để có thể thực thi thủ tục, user phải có quyền thực thi nó, ta có thể gán quyền ấy như sau :
GRANT CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON *.* TO 'toan'@'127.0.0.1';
FLUSH PRIVILEGES;
Hoặc có thể gán mọi quyền như sau :
GRANT ALL PRIVILEGES ON *.* TO 'toan'@'127.0.0.1' WITH GRANT OPTION;
FLUSH PRIVILEGES;
