存储过程和函数

事先经过编译并存储在数据库中的一段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()
BEGININSERT 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))
BEGINSELECT bo.*FROM boys boRIGHT 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))
BEGINDECLARE result VARCHAR(20) DEFAULT ''; #声明并初始化SELECT COUNT(*)FROM adminWHERE admin.username = usernameAND 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))
BEGINSELECT bo.boyName INTO boyNameFROM `boys` boINNER 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)
BEGINSELECT bo.boyName,bo.`userCP` INTO boyName,userCPFROM `boys` boINNER 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 )
BEGINSET 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
BEGINDECLARE 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
BEGINSET @sal=0; #定义用户变量SELECT salary INTO @sal  #赋值FROM employeesWHERE last_name=empName;RETURN @sal;
END $# 调用
SELECT myf2('kochhar')$

4、查看函数

SHOW CREATE FUNCTION myf1;

5、删除函数

drop function myf1;