存储过程
代码语言：javascript
复制# 存储过程和函数
/*
存储过程和函数：类似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;
# 存储过程一般不修改
函数
代码语言：javascript
复制# 函数
/*
存储过程与函数区别：
存储过程可以有任意个返回值（零或多个），适合于批量插入或修改
函数有且仅有一个返回值，适合处理数据后返回一个结果
*/
# 创建语法
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;
