MySQL进阶18- 存储过程- 创建语句-参数模式(in/out/inout-对应三个例子) -调用语法-delimiter 结束标记'$'- 删除/查看/修改-三个练习

/* MySQL-进阶18 
存储过程 和 函数
      存储过程和函数:类似于java中的方法
   好处:
    1.提高代码的重用性
    2.简化操作
   
*/
#存储过程
/*
    含义: 一组已经预见编译好的SQL语句的集合, 理解成批处理语句;
    好处: 减少操作次数,减少了编译次数,减少了和服务器的连接次数,提高了效率
        
*/
/*
#一: 创建语句
create procedure 存储过程名(参数列表)
begin
    存储过程体(一组合法的SQL语法)
end

注意:
1.参数列表包含三个部分 : 参数模式 参数名 参数类型
举例:
in stuname varchar(20)

参数模式: 
in : 该参数可以作为输入, 也就是该参数需要调用方 传入值
out : 该参数可以作为输出,也就是该参数可以作为返回值
inout : 该参数既可以作为输入也可以作为输出,也就是该参数既需要传入值,又需要返回值

2.如果存储过程体只有一句话,begin 和 end 可以省略;
    存储过程体中的每条sql 语句的结尾要求必须加分号;
    存储过程的结尾可以使用 delimiter 重新设置;
    语法:
        delimiter 结束标记
    案例:
        delimiter $
#二:调用语法
    call 存储过程名(实参列表);

#二 :  删除存储过程
#语法:drop procedure 存储过程名 ,不支持删除多个
DROP PROCEDURE `myp1`;

#三 : 查看 存储过程的信息
    #错误写法:DESC myp2;
SHOW CREATE PROCEDURE myp2;  #......................

#四: 修改 ,无法修改内部语句

---

*/

  #1.空参列表
    #案例:插入到admin 表中五条记录
SELECT * FROM admin;

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN 
  INSERT INTO admin(`username`,`password`) 
  VALUES('a1','1111'),('a2','2222'),('a3','33333'),('a4','444444'),('a5','5555');
  END $

    #调用
CALL myp1() $;

  #2:创建带有in 模式参数的存储过程 
     #案例 : 创建存储过程实现 根据女生名,查询对应的男生信息
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(50))
BEGIN 
  SELECT bo.*
  FROM boys bo
  RIGHT JOIN beauty b 
  ON bo.id = b.boyfriend_id
  WHERE b.name = beautyName;
END $
    #调用 ,必须紧紧挨着'END $'
CALL myp2('柳岩') $;

   #案例2: 创建存储过程实现,用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp3(IN `uname` VARCHAR(10),IN psw VARCHAR(10))   
BEGIN
    DECLARE result VARCHAR(20) DEFAULT ''; #声明局部变量并进行初始化
    SELECT COUNT(*) INTO result  #赋值
    FROM admin
    WHERE username = uname AND admin.`password`=psw;
    
    
    SELECT result;
END $   
CALL    myp3('lyt','6666') $;   #返回1
#call    myp3('lyt','66066') $;  #返回0

    #3-2:(使用IF 语句)创建存储过程实现,用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp4(IN `uname` VARCHAR(10),IN psw VARCHAR(10))   
BEGIN
    DECLARE result INT DEFAULT 0; #声明局部变量并进行初始化
    SELECT COUNT(*) INTO result  #利用into 赋值
    FROM admin
    WHERE username = uname AND admin.`password`=psw;
    
    SELECT IF(result>0,'成功','失败'); #使用
END $   
CALL    myp4('lyt','66066') $;  #返回 '失败'
# CALL    myp4('lyt','6666') $;  #返回 '成功'

#3:创建带有out 模式参数的存储过程 
    #案例1:根据女神名,返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp5(IN in_name VARCHAR(50),OUT boyName VARCHAR(20))
BEGIN
    SELECT bo.boyName INTO boyName  #赋值,重名时按照就近原则
    FROM boys bo
    INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
    WHERE b.name = in_name;
END $
    #set @bname $  ,定义一个用户变量
CALL myp5('柳岩',@bname) $
SELECT @bname $;           #鹿晗

    #案例2:根据女神名,返回对应的男神名 和男神魅力值
DELIMITER $
CREATE PROCEDURE myp6(IN in_name VARCHAR(50),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
    SELECT bo.boyName ,bo.userCP INTO boyName,userCP
        FROM boys bo
    INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
    WHERE b.name = in_name;
END $
    #set @bname $   ,定义一个用户变量
CALL myp6('柳岩',@bname,@usercp) $
SELECT @bname,@usercp $;           #鹿晗 ,800

##4.创建带inout 模式参数的存储过程
    #案例1: 传入a 和 b两个值,最终a和b都翻倍并返回; 注意需要传入的需要是用户变量
DELIMITER $
CREATE PROCEDURE myp7(INOUT a INT,INOUT b INT )
BEGIN
    SET a=a+a;
    SET b=b+b;
END $  

CALL myp7(@a,@b) $
SELECT @a,@b ;  #24 26

SET @a=12;
SET @b=13;

#二 :  删除存储过程
#语法:drop procedure 存储过程名 ,不支持删除多个
DROP PROCEDURE `myp1`;

#三 : 查看 存储过程的信息
    #错误写法:DESC myp2;
SHOW CREATE PROCEDURE myp2;  #......................

#四: 修改 ,无法修改内部语句
#------------------------------------------
COMMIT;

---

#五: 练习1-2:

    ##练习1
    #test1 : 创建一个存储过程实现传入一个日期,格式化为xx年xx月xx日 并返回
DELIMITER $
CREATE PROCEDURE test_pro4(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
    SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
END $

CALL test_pro4(@mydate,@strDate) $;

SET @mydate=NOW();
SET @strDate='';
SELECT @mydate,@strDate;   #2018-11-01 17:01:41,18年11月01日

      ##练习2
   #test2 : 创建一个存储过程或者函数实现传入女神名称,返回 女神 and 男神 名字加在一块的字符串
    #如传入:小昭 ;返回:小昭 and 张无忌
COMMIT;
DROP PROCEDURE test_pro6;
    
DELIMITER $
CREATE PROCEDURE test_pro7(IN s1 VARCHAR(50),OUT s2 VARCHAR(50))
BEGIN
    SELECT CONCAT(s1,' and ',IFNULL(boys.`boyName`),'null')
    FROM boys
    INNER JOIN beauty b
    ON b.`boyfriend_id`=boys.`id`
    WHERE b.name=s1;
END $
#set @result1='' 可以省略!!

CALL test_pro7('热巴',@result1) $      #返回 : 热巴 and 鹿晗
SELECT @result1;

    #六: 创建存储过程或者函数 ,根据传入的目录条数和起始索引(反了!),查询beauty表的记录
DELIMITER $
CREATE PROCEDURE test_pro8(IN size INT,IN startIndex INT)
BEGIN
    SELECT * FROM beauty 
    LIMIT startIndex,size;
END $ 

CALL test_pro8(3,5) $