#存储过程和函数
/*
存储过程和函数,类似于java中的方法
特点:
1.提高代码的重用性
2.简化操作
3.减少编译次数,并且减少了和数据库服务器的连接次数,提高了效率
*/
#存储过程
/*
含义:一组预先编译好的sql语句的集合,理解成批处理语句

*/
#一、创建语法
/*
create procedure 存储过程名(参数列表)
begin
    存储过程体(方法体,一组合法的ql语句)
end
注意:
1.参数列表包含三部分
参数模式 参数名 参数类型
举例:
IN stuname Varchar(20)

参数模式:
iN     该参数可以作为输入,需要传入值
out     该参数可以作为输出,即可作为返回值,
INout     该参数既可以作输入又可以作输出

2.如果存储过程体仅一句话, begin end 可以省略
存储过程体的每条sql语句的结尾要求必须加分号

存储过程的结尾可以使用 delimiter 重新设置
语法:
delimiter 结束标记
案例:
delimiter $

*/
#二、调用语法
/*

call 存储过程名(实参列表);

*/


#1.空参列表
#案例:插入到admin表中的3条记录

USE girls;
SELECT * FROM admin;

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
    INSERT INTO admin(username,PASSWORD)
    VALUE('jj','1111'),
    ('gg','2222'),
    ('xx','3333');
END $

CALL myp1() $; 

#2.创建带in模式参数的存储过程

#案例1:创建存储过程实现,根据女神名,查询对应的男神信息
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
    SELECT bo.*
    FROM boys bo
    RIGHT JOIN beauty be ON bo.`id`=be.`boyfriend_id`
    WHERE be.name=beautyName;
END $

#调用柳岩的男友
CALL myp2('柳岩');

CALL myp2('热巴');

#案例2:创建存储过程实现,用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
    DECLARE result INT DEFAULT 0;#声明变量result
    SELECT COUNT(*) INTO result
    FROM admin
    WHERE admin.`username`=username
    AND admin.`password`=PASSWORD;
    
    SELECT IF(result>0,'成功','失败');#使用
END $

#调用
CALL myp3('张飞','8888');
CALL myp3('jj','1111');

#3.创建out模式参数的存储过程
#案例1:根据输入的女神名,返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boysName VARCHAR(20))
BEGIN
    SELECT bo.`boyName` INTO boysName
    FROM boys bo
    RIGHT JOIN beauty be ON bo.`id`=be.`boyfriend_id`
    WHERE be.name=beautyName;
END $
#用户变量的赋值为set = @,不是declare
SET @boName:='000'$
CALL myp4('热巴',@boName)$
SELECT @boName$

#4.创建带inout模式参数的存储过程
#案例1:传入a和b两个值,最后a和b都翻倍返回
DELIMITER $
CREATE PROCEDURE myp5(INOUT a INT,INOUT b INT)
BEGIN
    SET a=a*2;
    SET b=b*2;
END $

SET @m=1;
SET @n=2;
CALL myp5(@m,@n);
SELECT @m,@n;

DELIMITER ;
#三、删除存储过程
#语法:drop procedure 存储过程名
DROP PROCEDURE myp1;
DROP PROCEDURE myp2,myp3;#×不可以,

#四、查看存储过程的信息
DESC myp2;#×不可以
SHOW CREATE PROCEDURE  myp2;


#=============================Test=======================
#一、创建存储过程实现传入用户名和密码,插入到admin表中

DELIMITER $
CREATE PROCEDURE emp1(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
    INSERT INTO admin(admin.username,admin.password) 
    VALUES(username,PASSWORD);
END $

CALL emp1("内裤","11111")$


#二、创建存储过程实现传入女神编号,返回女神名称和女神电话
DELIMITER $
CREATE PROCEDURE emp2(IN beid INT,OUT beName VARCHAR(20),OUT bePhone VARCHAR(20))
BEGIN
    SELECT be.name,be.phone INTO beName,bePhone
    FROM beauty be
    WHERE be.id=beid;
END $

CALL emp2(3,@bName,@bPhone)$
SELECT @bName,@bPhone;

#三、创建存储存储过程或函数实现传入两个女神生日,返回相差天数大小

DELIMITER $
CREATE PROCEDURE emp3(IN beBorndate1 DATETIME,IN beBorndate2 DATETIME,OUT result INT)
BEGIN
    SELECT DATEDIFF(beBorndate1,beBorndate2) INTO result;
END $

SET @be1='1988-02-03';
SET @be2='1988-03-03';
CALL emp3(@be1,@be2,@res);
SELECT @res;

#四、创建存储过程或函数实现传入一个日期,格式化成xx年xx月xx日并返回

DELIMITER $
CREATE PROCEDURE emp4(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
    SELECT DATE_FORMAT(mydate,'%Y年%m月%d日') INTO strDate;
END $

SET @date='1988-02-03';
CALL emp4(@date,@strDa);
SELECT @strDa;

#五、创建存储过程或函数实现传入女神名称,返回:女神 and 男神  格式的字符串


DELIMITER $
CREATE PROCEDURE emp5(IN BeName VARCHAR(20),OUT strName VARCHAR(50))
BEGIN
    SELECT CONCAT(Bename,' and ',IFNULL(bo.boyName,'null')) INTO strName
    FROM beauty be
    LEFT JOIN boys bo
    ON be.`boyfriend_id`=bo.`id`
    WHERE be.name=BeName;
END $


CALL emp5('柳岩',@str);
SELECT @str;


#六、创建存储过程或函数,根据传入的条目数和起始索引,查询beauty表的记录
DELIMITER $
CREATE PROCEDURE emp6(IN num INT,IN startnum INT)
BEGIN
    SELECT *
    FROM beauty
    LIMIT startnum,num;
END $

CALL emp6(8,3)$


#=========================Test end========================

#函数
/*
特点:(与存储过程相同)
1.提高代码的重用性
2.简化操作
3.减少编译次数,并且减少了和数据库服务器的连接次数,提高了效率

区别:
存储过程:可以有0个返回或多个返回,适合做批量插入,批量更新
函数:有且仅有一个返回,适合做处理数据后返回一个结果 

*/

#1、创建语法:
/*
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END

注意:
1.参数列表 包含两部分:  参数名和参数类型
2.函数体,肯定要有return语句,不建议将return放在非最后一句,虽然不报错
3.函数体只有一句话可以省略begin end
4.使用delimiter语句设置结束标记
*/

#2、调用语法
/*
SELECT 函数名(参数列表)
*/

#==================案例===============================
#1.无参输入有返回
#案例:返回公司员工个数
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
    DECLARE countnum INT DEFAULT 0;
    SELECT COUNT(*) INTO countnum
    FROM employees;
    RETURN countnum;
END $

SELECT myf1()$

#2.有参有返回
#案例:根据员工名返回工资
DELIMITER $
CREATE FUNCTION myf2(emName VARCHAR(20)) RETURNS INT
BEGIN
    DECLARE sal INT DEFAULT 0;
    SELECT salary INTO sal
    FROM employees
    WHERE last_name=emName;
    RETURN sal;
END $

SELECT myf2('Kochhar')$

#案例2:根据部门名,返回该部门的平均工资
CREATE FUNCTION myf3(depName VARCHAR(20)) RETURNS INT
BEGIN
    DECLARE avgsal INT DEFAULT 0;
    SELECT AVG(salary) INTO avgsal
    FROM employees e
    JOIN departments d ON e.`department_id`=d.`department_id`
    WHERE d.`department_name`=depName;
    RETURN avgsal;
END $

SELECT myf3('Hum')$

#三、查看函数

SHOW CREATE FUNCTION myf3;

#四、删除函数
DROP FUNCTION myf3;

#案例
#一、创建函数,实现传入两个float,返回二者之和
DELIMITER $
CREATE FUNCTION myf4(a FLOAT,b FLOAT) RETURNS FLOAT
BEGIN
    DECLARE SUM FLOAT DEFAULT 0;
    SET SUM:=a+b;
    RETURN SUM;
END $

SELECT myf4(1.1,2.54554554)$