MySQL存储过程和函数

# 存储过程和函数

/*
存储过程和函数
好处：
1.提高代码的重用性
2.简化操作


*/

# 存储过程
/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1.提高代码的重用性
2.简化操作
3.减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率


*/

/*
# 一、创建语法
create procedure 存储过程名(参数列表)
begin
	存储过程体（一组合法的sql语句）
end

注意：
1.参数列表包含三部分
参数模式	参数名	参数类型
举例：
in stuName varchar(20)

参数模式：
in：该参数可以作为输入，也就是改参数需要调用方传入值
out：该参数可以作为输出，也就是改参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，也可以返回值

2.如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号，
存储过程的结尾可以使用delimiter重新设置
语法：
delimiter 结束标记
# 案例
delimiter $

# 二、调用语法
call 存储过程名(实参列表);


*/

# 1.空参列表
# 案例：插入到admin表中五条记录

SELECT * FROM admin;

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,PASSWORD)
	VALUES('join1','0000'),('join2','0000'),('tom','0000'),('rose','0000'),('jack','0000');
END $

# 调用
CALL myp1()
# sqlyog中运行不要加$ ，dos中运行必须要有$

# 2.创意带in模式参数的存储过程

# 案例1：创建存储过程实现 根据女神名，查询对应的男神信息
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys AS bo
	RIGHT JOIN beauty AS b
	ON bo.id=b.boyfriend_id
	WHERE b.name=beautyName;
END $

# 调用
CALL myp2('金星');

# 案例2：创造存储过程实现用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp3(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 myp3('张飞','8888');

# 3.创建带out模式的存储过程
# 案例1：根据女神名，返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyname VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys AS bo
	INNER JOIN beauty AS b
	ON bo.id=b.boyfriend_id
	WHERE b.name=beautyName;
END $

# 调用
SET @bNmae='NULL';
CALL myp4('金星',@bName);
SELECT @bName;

# 案例2：根据女神名，返回对应的男神魅力值
DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN
	SELECT bo.boyName ,bo.userCP INTO boyName, userCP
	FROM boys AS bo
	INNER JOIN beauty AS b
	ON bo.id=b.boyfriend_id
	WHERE b.name=beautyName;
END $

# 调用
CALL myp5('金星',@bName,@bCP);
SELECT @bName,@bCP;

# 4.创建带inout模式参数的存储过程
# 案例1：传入a和b两个值，最终a和b都翻倍并返回

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

# 调用
SET @m=10;
SET @n=20;
CALL myp6(@m,@n);
SELECT @m,@n;

# 二、删除存储过程
/*
语法：
drop procedure 存储过程名;
注意：一次只能删除一个
*/

DROP PROCEDURE myp1;

# 三、查看存储过程的信息
SHOW CREATE PROCEDURE myp2;