CREATE PROCEDURE 存储过程名(参数模式 参数名 参数类型,参数模式 参数名 参数类型,...) 
BEGIN （语句只有一条 begin end 可省略）
	合法有效的SQL语句;（分号）
	合法有效的SQL语句;
END

delimiter $
CREATE PROCEDURE pr1()
BEGIN
	INSERT INTO book(`name`, price, author) VALUES('012',23.56,'lzj');
	INSERT INTO book(`name`, price, author) VALUES('45',23.56,'lzj');
	INSERT INTO book(`name`, price, author) VALUES('12',23.56,'lzj');
	INSERT INTO book(`name`, price, author) VALUES('87',23.56,'lzj');
	INSERT INTO book(`name`, price, author) VALUES('32',23.56,'lzj');
END $

CALL name(var, var);

CALL pr1();

delimiter $
CREATE procedure pr2(IN bid INT)
BEGIN
	SELECT * FROM beauty WHERE boyfriend_id = bid;
END $




delimiter $
CREATE PROCEDURE pr3(IN username VARCHAR(20), IN `password` VARCHAR(20))
BEGIN
	DECLARE res INT;
	SELECT COUNT(*) INTO res FROM admin 
	WHERE admin.username = username AND admin.`password` = `password`;
	SELECT res;
END $

CALL pr2(12);

CALL pr3('john','8888');

delimiter $
CREATE PROCEDURE pr4(IN girl INT, OUT boy INT)
BEGIN
	SELECT beauty.boyfriend_id INTO boy FROM beauty WHERE beauty.id = girl;
END $

CALL pr4(1, @boy);
SELECT @boy;

delimiter $
CREATE PROCEDURE pr5(IN girlid INT, OUT boyid INT, OUT girlname VARCHAR(20))
BEGIN
	SELECT 
		beauty.boyfriend_id,  beauty.`name` 
	INTO 
		boyid, girlname
	FROM beauty WHERE beauty.id = girlid;
END $

CALL pr5(2, @boyid, @girlname);
SELECT  @boyid;
SELECT @girlname;

delimiter $
CREATE PROCEDURE pr6(INOUT a INT, INOUT b INT) 
BEGIN
	SET a = a*2;
	SET b = b*2;
END $

SET @i = 2;
SET @j = 4;
call pr6(@i, @j);
SELECT @i;
SELECT @j;

DROP PROCEDURE pr2;

show CREATE PROCEDURE pr3;