11-存储过程和函数

# 存储过程和函数

/*
存储过程和函数：类似Java中的方法

好处：
1. 提高代码重用性
2. 简化操作
*/


# 存储过程
/*
含义：一组预先编译好的SQL语句集合

好处：
1. 提高代码重用性
2. 简化操作
3. 减少编译次数
4. 减少数据库服务器的连接次数，提高效率
*/

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

# 注意
1. 参数列表包含三部分：参数模式 参数名 参数类型
2. 参数模式有 IN,OUT,INOUT三种
IN:该参数可以作为输入，也就是说该参数需要调用方传入值
OUT:该参数可以作为输出，也就是该参数可以作为返回值
INOUT:该参数既可以作为输入，也可以作为输出，急需要传入值也可以返回值
3. 如果存储过程体中仅有一句话，BEGIN END 可以省略
4. 存储过程体中每条SQL语句结尾要求必须加分号
5. 存储过程的结尾可以用DELIMITER重新配置
语法：
DELIMITER 结束标记


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



# 空参的存储过程
# 案例：插入到admin表中三条记录
DROP TABLE IF EXISTS admin;
CREATE TABLE admin(
		id INT PRIMARY KEY auto_increment,
		username VARCHAR(20),
		password VARCHAR(15)
);
SELECT * FROM admin;
# 题目完成
DELIMITER $  # 修改终止符号，只能在DOS窗口里应用，图形化界面无效，修改后，后续都需要用这个符号作为终止符
CREATE PROCEDURE p1()
BEGIN
	INSERT INTO admin 
	VALUES(NULL,'Leslie','0000'),(NULL,'JJ','0111'),(NULL,'Lily','0011');
END $
# 调用函数(在DOS窗口中用CALL p1()$调用)
CALL p1();



# 创建带IN类型参数的存储过程
# 案例：根据女友姓名，查询男友信息
USE girls;
CREATE PROCEDURE p2(IN girlName VARCHAR(20))
BEGIN 
		SELECT bo.*
		FROM boys bo
		RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
		WHERE b.`name`=girlName;
END 
# 调用
CALL p2('小昭');

# 案例2：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE p3(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
		# 定义变量用于输出结果
		DECLARE result INT DEFAULT 0;
		# 检查登录
		SELECT COUNT(*) INTO result
		FROM admin
		WHERE username=admin.username
		AND password=admin.`password`;
		# 输出结果
		SELECT IF(result>0,'成功','失败') 结果;
END
# 调用
CALL p3('john','8888');




# 创建待out模式的存储
# 实际就是其他语言的返回值，Mysql支持多个out模式即多个返回值

# 根据女生名返回男生姓名
USE girls;
CREATE PROCEDURE p4(IN girlName 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`=girlName;
END
# 调用
CALL p4('小昭',@boyName);
SELECT @boyName;


# 案例2：根据女士姓名，返回男士姓名和数值（一次返回多个值）
CREATE PROCEDURE p5(IN girlName 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`=girlName;
END

# 调用
CALL p5('小昭',@boyName,@boyCP);
SELECT @boyName,@boyCP;



# 带inout模式的存储模式
# 传入a和b两个值，最终a和b都被双倍返回
CREATE PROCEDURE p6(INOUT a INT ,INOUT b INT)
BEGIN 
		SET a=a*2;
		SET b=b*2;
END

SET @aNum=10;
SET @bNum=6;
CALL p6(@aNum,@bNum);
SELECT @aNum,@bNum;



# 存储过程的删除
DROP PROCEDURE p2;

# 查看存储过程结构信息
SHOW CREATE PROCEDURE p3;

# 存储过程一般不修改

# 函数

/*

存储过程与函数区别：
存储过程可以有任意个返回值（零或多个），适合于批量插入或修改
函数有且仅有一个返回值，适合处理数据后返回一个结果

*/

# 创建语法

CREATE FUNCTION 方法名(参数列表) RETURNS 返回类型
BEGIN 
		函数体
END
/*
参数列表包含两部分：参数名  参数类型

函数体：肯定有return语句
如果return语句不会报错，但没有实际效果

函数体中只有一句话时，BEGIN END可以省略

在DOS窗口下仍然使用DELIMITER设置终止符
*/

# 调用语法,执行函数体内所有语句，并最终显示返回值
SELECT 函数名(参数列表);

# 案例：无参有返回值，返回公司员工格式
use myemployees;
CREATE FUNCTION f1() RETURNS INT
BEGIN
		DECLARE c INT ; # 声明返回值变量
		SELECT count(*) INTO c  # 赋值
		FROM employees;
		RETURN c; # 返回值
END


SELECT f1() result;

# 案例二：含参有返回，根据员工名，返回对应工资
CREATE FUNCTION f2(username VARCHAR(20)) RETURNS INT
BEGIN 
		DECLARE s INT;
		SELECT salary INTO s
		FROM employees
		WHERE last_name=username;
		RETURN s;
END

SELECT f2('Chen') salary;


# 查看函数
SHOW CREATE FUNCTION f1;
# 删除函数
DROP FUNCTION f1;