存储过程和函数
事先经过编译并存储在数据库中的一段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、空参列表
SELECT * FROM admin;
#1、空参列表
#案例:插入到admin表中5条记录
SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`)
VALUES('john1','0000'),('lily','1111'),('tom','2222'),('jack','3333'),('rose','4444');
END $
#调用
CALL myp1()$
#查看
SELECT * FROM admin$;
2、in模式参数列表
#2、创建带in模式参数的存储过程
#案例:创建存储过程实现,根据女神名,查询对应的男神信息
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON bo.id=b.boyfriend_id #因为有的没对应,所以用外连接
WHERE b.name = beautyName;
END $
#调用
CALL myp2('柳岩')$
SET NAMES gbk;
#2、创建带in模式参数的存储过程
#案例:创建存储过程实现,用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result VARCHAR(20) DEFAULT ''; #声明并初始化
SELECT COUNT(*)
FROM admin
WHERE admin.username = username
AND admin.password =PASSWORD;
SELECT IF(result>0,'成功','失败'); # 使用
END $
#调用
CALL myp3('张飞','8888')$
SET NAMES gbk$;
3、out模式参数列表
#2、创建带out模式参数的存储过程
#案例1:根据女神名,返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM `boys` bo
INNER JOIN `beauty` b ON b. `id`=bo.`id`
WHERE b.name=beautyName;
END $
#调用
CALL myp3('小昭',@bName)$
SELECT @bName$
SET NAMES gbk$;
#案例2:根据女神名,返回对应的男神名和男神魅力值
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` bo
INNER JOIN `beauty` b ON b. `id`=bo.`id`
WHERE b.name=beautyName;
END $
#调用
CALL myp3('小昭',@bName,@usercp)$
SELECT @bName,@usercp$
4、inout模式参数列表
#2、创建带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 存储过程名
三、查看存储过程的信息
DESC myp2; # 不允许
SHOW CREATE PROCEDURE myp2;
四、函数
存储过程可以由0个返回,也可以有多个返回,适用于批量的插入和更新;
函数有且仅有1个返回,适用于处理数据后返回一个结果。
1、创建语法
delimiter $
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
注意事项:
1、参数列表 包含两个部分:
参数名 参数类型
2、函数体:
肯定有return语句,如果return没有放在函数体的最后虽然不报错,但是没意义,不建议使用。
3、函数体中仅有一句话,可以省略begin end
4、使用delimiter语句设置结束标记;
2、调用语法
select 函数名(参数列表)
3、案例:
#1、无参数返回
#返回公司的员工个数
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0; #定义局部变量(也可以定义用户变量)
SELECT COUNT(*) INTO c #赋值
FROM employees;
RETURN c;
END $
# 调用
SELECT myf1()$
#2、有参数返回
#根据员工名返回它的工资
DELIMITER $
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0; #定义用户变量
SELECT salary INTO @sal #赋值
FROM employees
WHERE last_name=empName;
RETURN @sal;
END $
# 调用
SELECT myf2('kochhar')$
4、查看函数
SHOW CREATE FUNCTION myf1;
5、删除函数
drop function myf1;