存储过程和函数
含义: 一组预先编译好的 SQL 语句的集合, 理解成批处理语句
1, 提高代码的重用性
2, 简化操作
3, 减少了编译次数并且减少了和数据库服务器的连接次数, 提高了效率
区别:
存储过程: 可以有 0 个返回, 也可以有多个返回, 适合做批量插入, 批量更新
存储函数: 有且仅有 1 个返回, 适合做处理数据后返回一个结果
存储过程
语法
创建过程
语法:
CREATE PROCEDURE 存储过程名 (参数列表)
BEGIN
存储过程体 (一组合法的 SQL 语句)
END
说明:
1, 参数列表包含三部分
参数模式 参数名 参数类型
举例:
IN name VARCHAR(20)
参数模式:
IN: 输入参数
OUT: 输出参数
INOUT: 输入输出参数
2, 存储过程体中仅有一句话, 则可以省略 BEGIN END
3, 存储过程体中的每条 sql 语句的结尾要求必须加分号
4, 存储过程的结尾可以使用 DELIMITER 设置结束标记
语法:
DELIMITER 结束标记
举例:
DELIMITER $
调用过程
语法:
CALL 存储过程名 (实参列表);
说明:
调用 IN 模式参数:
CALL 存储过程名 (@变量);
调用 OUT 模式参数:
SET @变量;
CALL 存储过程名 (@变量);
SELECT @变量;
调用 INOUT 模式参数:
SET @变量 = 值;
CALL 存储过程名 (@变量);
SELECT @变量;
查看过程
语法:
SHOW CREATE PROCEDURE 存储过程名;
删除过程
语法:
DROP PROCEDURE 存储过程名;
案例
1, 创建带 IN 模式参数的存储过程
- # 设置结束标记
- DELIMITER $
- # 创建
- CREATE PROCEDURE pro1(IN username VARCHAR(20), IN password VARCHAR(20))
- BEGIN
- DECLARE result INT DEFAULT 0; # 声明并初始化
- SELECT COUNT(*) INTO result # 赋值
- FROM `user` u WHERE u.username = username
- AND u.password = password;
- SELECT IF(result>0,'登录成功','登录失败'); # 使用
- END $
- # 调用
- CALL pro1('admin', 'admin')$
2, 创建带 OUT 模式参数的存储过程
- # 设置结束标记
- DELIMITER $
- # 创建
- CREATE PROCEDURE pro2(IN id INT, OUT username VARCHAR(20), OUT password VARCHAR(20))
- BEGIN
- SELECT u.username,u.password INTO username,password
- FROM `user` u WHERE u.id = id;
- END $
- # 调用
- CALL pro2(1001, @username, @password)$
- SELECT @username,@password$
3, 创建带 INOUT 模式参数的存储过程
- # 设置结束标记
- DELIMITER $
- # 创建
- CREATE PROCEDURE pro3(INOUT a INT ,INOUT b INT)
- BEGIN
- SET a = a*2;
- SET b = b*2;
- END $
- # 调用
- SET @m=10$
- SET @n=20$
- CALL pro3(@m,@n)$
- SELECT @m,@n$
存储函数
语法
创建函数
语法:
CREATE FUNCTION 函数名 (参数列表) RETURNS 返回类型
BEGIN
函数体
END
说明:
1, 参数列表包含两部分
参数名 参数类型
2, 函数体必须有 RETURN 语句, 如果没有会报错
3, 函数体中仅有一句话, 则可以省略 BEGIN END
4, 函数体中的每条 sql 语句的结尾要求必须加分号
5, 函数的结尾可以使用 DELIMITER 设置结束标记
语法:
DELIMITER 结束标记
举例:
DELIMITER $
调用函数
语法:
SELECT 函数名 (参数列表)
查看函数
语法:
SHOW CREATE FUNCTION 函数名;
删除函数
语法:
DROP FUNCTION 函数名;
案例
1, 无参返回
- # 设置结束标记
- DELIMITER $
- # 创建
- CREATE FUNCTION fun1() RETURNS INT
- BEGIN
- DECLARE c INT DEFAULT 0;
- SELECT COUNT(*) INTO c
- FROM `user`;
- RETURN c;
- END $
- # 调用
- SELECT fun1()$
2, 有参返回
- # 设置结束标记
- DELIMITER $
- # 创建
- CREATE FUNCTION fun2(username VARCHAR(20)) RETURNS VARCHAR(20)
- BEGIN
- SET @password='123';
- SELECT u.password INTO @password
- FROM `user` u WHERE u.username = username;
- RETURN @password;
- END $
- # 调用
- SELECT fun2('admin')$
来源: http://www.bubuko.com/infodetail-2689694.html