SQL学习笔记整理15（存储过程）

#存储过程和函数：类似于Java中的方法
/*
存储过程：一组预先编译好的sql语句的集合，理解成批处理语句
*/
#一、创建用法
/*
create procedure 存储过程名（参数列表）
begin
存储过程体
end

注意：
1.参数列表包括三部分：参数模式 参数名 参数类型
                     举例:in stuname varchar(20)
     参数模式：
        IN：该参数可以作为输入，即该存储过程需要调用方传入参数值
        OUT：该参数可以作为输出，即该参数可以作为返回值
        INOUT：该参数既可以作为输入，又可以作为输出

2.如果存储过程体只有一条sql语句，那么begin end可以去掉
存储过程的结尾可以使用DELIMITER重新设置：
DELIMITER 结束标记
举例：
DELIMITER $
*/
#二、调用语法  
/*
call 存储过程名（实参列表）
*/

#1.空参列表
#案例：插入到admin表里五条数据
SELECT * FROM admin;

DELIMITER $
CREATE PROCEDURE my_p()
BEGIN
    INSERT INTO admin(username,`password`) VALUES
    ('lucy',5555),
    ('daniel',1111),
    ('sunny',2222),
    ('jim',3333),
    ('zhangy',4444);
END $

#下面语句在命令提示符里执行
CALL my_p()$
SELECT * FROM admin$

#2.带in参数模式的存储过程
#案例1：创建存储过程实现通过女神名查询对应的男神信息
USE girls;
SELECT * FROM beauty;
SELECT * FROM boys;

DELIMITER $
CREATE PROCEDURE my_p2 (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 my_p2('赵丽颖')$

#案例2：创建存储过程实现显示用户是否登录成功
CREATE PROCEDURE myp3(IN username VARCHAR(10),IN PASSWORD VARCHAR(10))
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')$
CALL myp3('lucy','5555')$

#3.创建带out参数模式的存储过程
#案例：根据女神名返回对应的男神名
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
    SELECT bo.boyName INTO boyName
    FROM boys bo 
    JOIN beauty b 
    ON bo.id = b.boyfriend_id WHERE b.name = beautyName;
END $

CALL myp4('热巴',@bName)$
SELECT @bName$
#案例2：根据女神名查询对应男神名和魅力值
 CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),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=beautyName;
 END $
 
 CALL myp5('王语嫣',@bName,@usercp)$
 SELECT @bName,@usercp
 
#4.创建带INOUT参数模式的存储过程 
#案例1：输入a和b,将a和b翻倍返回
DELIMITER $
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 存储过程名
*/

#四、查看存储过程的结构
SHOW CREATE PROCEDURE myp5;

#案例1：将传入的日期按照"某某年某某月某某日"输出
DELIMITER $
CREATE PROCEDURE test_pro1(IN mydate DATETIME,OUT strdate VARCHAR(50))
BEGIN
    sele DATE_FORMAT(mydate,'%y年%m月%d日') INTO strdate;
END $

CALL test_pro1(NOW(),@strdate)$
SELECT @strdate$