存储过程和函数
存储过程和函数，类似于java中的方法
好处：
1、提高代码的重用性
2、简化操作
存储过程
含义：一组预先编译好的SQL语句的集合，理解为批处理语句
好处：
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
一、创建语法
create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法有效的SQL语句)
end
注意：
1、参数列表包含三部分
参数模式、参数名、参数类型
举例：
IN stuname VARCHAR(20)
参数模式：
IN:该参数可以作为输入，也就是说该参数需要调用方传入值
OUT：该参数可以作为输出，也就是该参数可以作为返回值
INOUT：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
2、如果存储过程体仅仅只有一句话，begin end 可以省略
3、存储过程体中的每条SQL语句的结尾要求必须加分号
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
例：delimiter $
二、调用语法
call 存储过程名(实参列表);
1.空参列表
案例：插入到admin表中五条记录
DELIMITER $
CREATE or replace PROCEDURE myp1 ()
BEGIN
INSERT INTO admin ( username, `password` )
VALUES( 'john1', '0000' ),( 'lily', '0000' ),('rose','0000' ),('jack','0000' ),('tom','0000' ) ;
END $
#调用
CALL myp1 ()$
SELECT * FROM admin$2.创建带in模式参数的存储过程
案例1：创建存储过程实现 根据女神名，查询对应的男神信息
CREATE PROCEDURE myp2 (IN beautyName VARCHAR ( 20 ))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON = b.boyfriend_id
WHERE = beautyName;
END $
#调用
CALL myp2('小昭')$案例2：创建存储过程实现，用户是否登录成功
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#声明并初始化
SELECT count(*) from admin #赋值
where admin.username=username
and admin.password=password;
SELECT IF(result>0,'成功','失败');#使用
END $
#调用
CALL myp3('张飞','8888')$3.创建带out 模式的存储过程
案例1：根据女神名返回对应的男神名
CREATE PROCEDURE myp4(IN beautyname VARCHAR(20),OUT boyname VARCHAR(20))
BEGIN
SELECT boys.boyname INTO boyname
from boys
INNER JOIN beauty on =beauty.boyfriend_id
WHERE =beautyname;
END $
#调用
SET @bName$
CALL myp4('小昭',@bName)$
SELECT @bName$案例2：根据女神名，返回对应的男神名和男神魅力值
CREATE PROCEDURE myp5(IN beautyname VARCHAR(20,OUT boyname VARCHAR(20),OUT usercp INT）
BEGIN
SELECT boys.boyname,boys.userCP into boyname,usercp
from boys
INNER JOIN beauty on =beauty.boyfriend_id
WHERE =beautyname;
END $
#调用
CALL myp4('小昭',@bname,@usercp)$
SELECT @bname,@usercp$4.创建带inout模式参数的存储过程
案例1：传入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 p1;四、查看存储过程的信息
SHOW CREATE PROCEDURE myp1;练习：
1、创建存储过程实现传入一个日期，格式化成××年××月××日并返回
CREATE PROCEDURE test_pro1(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
END;
CALL test_pro1(NOW(),@str);
SELECT @str;2、创建存储过程实现传入女神名称，返回：女神 AND 男神 格式的字符串
如：传入：小昭 / 柳岩
返回： 小昭 AND 张无忌/ 柳岩 AND Null
CREATE PROCEDURE test_pro2(IN beautyname VARCHAR(20),OUT strname VARCHAR(50))
BEGIN
SELECT CONCAT(beautyname,' AND ',IFNULL(bo.boyName,'Null'))
FROM boys bo
RIGHT JOIN beauty b on b.boyfriend_id=
WHERE =beautyname;
END;
CALL test_pro2('柳岩',@strname);
SELECT @strname;3、创建存储过程，根据传入的条目数和起始索引，查询beauty表的记录
CREATE PROCEDURE test_pro3(IN startindex INT,IN size INT)
BEGIN
SELECT * FROM beauty LIMIT startindex,size;
END;
CALL test_pro3(5,3);函数
含义：一组预先编译好的SQL语句的集合，理解为批处理语句
好处：
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
和存储过程的区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入，批量更新
函数：有且仅有1个返回，适合做处理数据后返回一个结果
一、创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END注意：
1.参数列表包含两部分：
参数名 参数类型
2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体最后也不报错，但不建议
return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用delimiter 语句设置结束标记
二、调用语法
案例演示
1.无参有返回
案例1：返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0; #定义局部变量
SELECT COUNT(*) INTO c #赋值
FROM employees;
RETURN c;
END;
SELECT myf1();2.有参有返回
案例1：根据员工名，返回他的工资
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');案例2：根据部门名，返回该部门的平均工资
CREATE FUNCTION myf3(deptname VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @ag_sal=0;
SELECT AVG(Salary) INTO @ag_sal
FROM employees e
INNER JOIN departments d ON e.department_id=d.department_id
WHERE d.department_name=deptname;
RETURN @ag_sal;
END;
SELECT myf3('IT');3.查看函数
SHOW CREATE FUNCTION myf3;4.删除函数
DROP FUNCTION myf3;练习：
创建函数，实现传入两个float，返回二者之和
CREATE FUNCTION test_fun1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE sum FLOAT DEFAULT 0;
SET sum=num1+num2;
RETURN sum;
END;
SELECT test_fun1(1,2);
