SQL中存储过程

CREATE PROCEDURE 存储过程名(参数列表) BEGIN 存储过程体(一组合法的SQL语句) END

CALL 存储过程名(实参列表);

-- 空参存储过程声明
DELIMITER $
CREATE PROCEDURE mypo()
BEGIN
INSERT INTO admin(username,`password`) VALUES
('jery','12345'),('Tom','12345'),('Jerry','12345'),
('Jack','12345'),('John','12345'),('Bob','12345');
END $
-- 空参存储过程调用
CALL mypo() $
-- 带IN模式的存储过程
DELIMITER &
CREATE PROCEDURE mypo2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.* FROM boys bo RIGHT JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name = beautyName;
END &
-- 带IN模式的存储过程调用
CALL mypo2('张飞')&
-- 多个参数的存储过程
DELIMITER $
CREATE PROCEDURE mypo3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;
SELECT COUNT(*) INTO result FROM admin WHERE admin.username = username AND admin.password = PASSWORD;
SELECT IF(result > 0,'成功','失败');
END $
-- 多个参数存储过程的调用
CALL mypo3('张无忌','666666')$
-- 带OUT模式的存储过程
DELIMITER $
CREATE PROCEDURE mypo5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName FROM boys bo INNER JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name = beautyName;
END $
-- 带OUT模式的存储过程调用
CALL mypo5('无极',@bName)$
SELECT @bName$
-- 多个参数的存储过程
DELIMITER $
CREATE PROCEDURE mypo6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
SELECT bo.boyName,bo.userCP INTO boyName,userCP FROM boys bo INNER JOIN beauty b ON bo.id = b.boyfriend_id WHERE b.name = beautyName;
END $
-- 多个参数存储过程的调用
CALL mypo6('无极',@bName,@userCP)$
SELECT @bName,@userCP$
-- 带INOUT模式的存储过程
DELIMITER $
CREATE PROCEDURE mypo8(INOUT a INT,INOUT b INT)
BEGIN
SET a = a*2;
SET b = b*3;
END $
-- 带INOUT模式的存储过程的调用
SET @m=10$
SET @n=20$
CALL mypo8(@m,@n)$
SELECT @m,@n$

DROP PROCEDURE 存储过程名;   
DROP PROCEDURE mypo8;

SHOW CREATE PROCEDURE 存储过程名;
SHOW CREATE PROCEDURE mypo2;