存储过程
含义：一组预先编译好的sql语句，可以理解成批处理语句，有点类似于函数
优点：
提高代码重用性，
简化操作，
减少编译次数和数据库服务器的连接次数，提高效率
一、创建语法
create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法的sql语句)
end
注意：
1.参数列表包含三部分
参数模式 参数名 参数类型
举例：
in fun_name varchar(20)
参数模式
in：输入，需要调用方传入值
out：输出，可以作为返回值
inout：既可以作为输入又可以作为输出，既要传入值又可以返回值
2.加入存储过程体只有一句话，begin end 可以省略
存储过程体中的结尾必须加分号，存储过程体的结尾用delimiter重新设置
语法：
delimiter 结束标志
举例：delimiter $
二、调用方法：
CALL 存储过程名(实参列表)
in : call myp(值) call myp(@name)
call myp(@name)$
1.空参列表
案例：向gils库中的admin表插入5条数据
SELECT * FROM admin; #id is AUTO_INCREMENT
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin (username,`password`)
VALUES('a','111'),('b','112'),('c','113'),
('d','114'),('e','115'),('f','116');
END $
CALL myp1()$
注意结束符号的使用
插入后的结果
2.创建带in参数的存储过程
案例：创建存储过程，根据女神名查询对应的男神的信息
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('杨紫')$
cmd中设置字符集，显示中文。
案例：创建存储过程，用户是否登录（admin里存在即登录成功，查询个数>=1）
CREATE PROCEDURE myp3(IN username VARCHAR(20), IN `password` VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#变量的声明与初始化
SELECT COUNT(*) INTO result #赋值
FROM admin
WHERE admin.`username`=username #加表的前缀以区分，或者起别名
AND admin.`password`=`password`;
SELECT IF(result>0,'已登录','未登录');#变量的使用
END$
CALL myp3('张飞','0000')$
if函数的使用
3.带out参数的存储过程
案例：根据女神名，返回对应的男神名
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT girlName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT b.name ,bo.boyName INTO girlName,boyName
FROM beauty b JOIN boys bo
ON b.boyfriend_id =bo.id
WHERE b.name=beautyName;
END$
SET @bName$
CALL myp5('李沁',@bName)$
#或者 直接使用用户变量@bName
CALL myp5('杨紫',@bName,@boName)$
SELECT @bName,@boName$
多个out 的形式
4.带inout参数法存储过程
案例：传入a，b，将a，b翻倍并返回
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 存储过程名
DROP PROCEDURE myp4;
四、查看存储过程信息
SHOW CREATE PROCEDURE myp5;
不能修改存储过程中间的sql语句，想修改的话，直接删掉重新建。
五、案例练习
1.创建存储过程或者函数，传入日期，返回xx年xx月xx日
CREATE PROCEDURE test_myp1(IN mydate DATETIME, OUT date_str VARCHAR(50))
BEGIN
SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO date_str;
END$
CALL test_myp1(NOW(),@str_date)$
SELECT @str_date$
2.根据女神名，返回'女神名 and 男神名'，如果女神没有对应的男神，返回 '女神名 and null'
与之前的多输出进行对比，看看区别与联系，concat的使用，ifnull的使用
DROP PROCEDURE test_myp2$
CREATE PROCEDURE test_myp2(IN beautyName VARCHAR(20),OUT str VARCHAR(50))
BEGIN
SELECT CONCAT(beautyName ,' and ',IFNULL(bo.boyName,'null')) INTO str
FROM beauty b LEFT JOIN boys bo
ON b.boyfriend_id =bo.id
WHERE b.name=beautyName;
END$
CALL test_myp2('苍老师',@result)$
SELECT @result$
3.根据传入的条目数和起始索引，查询beauty表的记录
CREATE PROCEDURE test_myp3( IN start_index INT,IN size INT )
BEGIN
SELECT * FROM beauty LIMIT start_index,size;
END$
CALL test_myp3(3,5)$
limit的使用，起始索引，显示条数；
函数
含义与优点与存储过程相同
区别：
存储过程：可以有0个返回值也可以有多个返回值，适合批量插入，更新
函数：有且只有1个返回值，适合处理数据后返回一个结果
一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
注意：
1.参数列表包含两部分：参数名 参数类型
2.函数体：肯定要有return语句，如果ruturn语句没有放在函数体最后也不报错，但是不建议
3.函数仅有一条语句，begin end 可以省略
4.使用delimiter 设置结束标记
二、调用函数
SELECT 函数名(参数列表)
三、案例演示
1.无参数
返回员工个数
USE `myemployees`$
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;
SELECT COUNT(*) INTO c
FROM `employees`;
RETURN c;
END$
SELECT myf1()$
2.有参数
根据员工名返回他的工资
CREATE FUNCTION myf2(employee_name VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0;
SELECT salary INTO @sal
FROM `employees`
WHERE `last_name`=employee_name ;
RETURN @sal;
END$
SELECT myf2('Kochhar')$
3.根据部门名返回该部门的平均工资
DROP FUNCTION myf3$
CREATE FUNCTION myf3(department_name VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE result DOUBLE ;
SELECT AVG(salary) INTO result
FROM employees e JOIN `departments` d
ON e.`department_id`=d.`department_id`
WHERE d.`department_name`=department_name;
RETURN result;
END$
SELECT myf3('Exe')$
三、查看与删除
SHOW CREATE FUNCTION myf3;
DROP FUNCTION myf3;
