mysql 第12章存储过程

2015-10-24

例1：管理员工的存储过程定义

delimiter //
CREATE PROCEDURE employee_add(p_name char(64),p_email char(64),p_password char(64))
MODIFIES SQL DATA
BEGIN
DECLARE l_pass char(64);
SET l_pass = PASSWORD(p_password);
INSERT INTO employee(name,email,password)
VALUES(p_name,p_email,l_pass);
END //

CREATE PROCEDURE employee_passwd(p_email char(64),p_password char(64))
MODIFIES SQL DATA
BEGIN
DECLARE l_pass char(64);
SET l_pass = PASSWORD(p_password);
UPDATE employee SET `password` = l_pass WHERE email = p_email;
END //

CREATE PROCEDURE employee_del(p_name char(64))
MODIFIES SQL DATA
BEGIN
DELETE FROM employee WHERE name = p_name;
END //

delimiter ;

例2：调用存储过程

CALL employee_add('chuck','chuck@example.com','abrakadabra');

例3：获取员工姓名的存储函数

delimiter //
CREATE FUNCTION employee_email(p_name char(64))
RETURNS char(64)
DETERMINISTIC
BEGIN
DECLARE l_email char(64);
SELECT email INTO l_email FROM employee WHERE name = p_name;
RETURN l_email;
END //
delimiter ;

例4：使用存储函数

CREATE TABLE collected (name char(32),email char(64));

INSERT INTO collected(name,email) VALUES('chuck',employee_email('chuck'));

SELECT employee_email('chuck');

例5：更新表的存储函数

CREATE TABLE log_msg (log_id int auto_increment PRIMARY key,msg text);

delimiter //
CREATE FUNCTION log_message(msg text)
RETURNS INT
DETERMINISTIC
BEGIN
INSERT INTO log_msg(msg) VALUES(msg);
RETURN LAST_INSERT_ID();
END //
delimiter ;

SELECT log_message('Just a test');