# 存储过程和函数
/*
存储过程和函数:类似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;
# 存储过程一般不修改