mysql 存储过程

/*
 存储过程和函数，类似于java中的方法
 优点：
   1. 提高了代码的重用性
   2. 简化操作
*/

#存储过程
/*
 含义：
    一组预先编译好SQL语句的集合，理解成批处理语句
    
 优点：
    提高代码的重用性
    简化操作
    减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
 
*/

# 一：创建
  CREATE PROCEDURE 存储过程名(参数列表)
  BEGIN
     存储过程体
  END
  注意事项：
  1. 参数列表包含三部分
  参数模式 参数名 参数类型
  举例：
  IN stuname VARCHAR(20)
  
  参数模式：
  IN: 该参数可以作为输入，也就是该参数需要调用方传入值
  OUT:该参数可以作为输出，也就是该参数可以作为返回值
  INOUT: 该参数既可以作为输入，又可以作为输出，也即，该参数即需要传入值，又可以返回值
  
  2. 如果存储过程体仅仅只有一句话，BEGIN END可以省略
  3. 存储过程体中的每条SQL语句的结尾要求必须加分号
     存储过程的结尾可以使用DELIMITER重新设置
     
  DELIMITER 结束标记
  
  案例
  DELIMITER $

# 二：调用
  CALL 存储过程名(实参列表);
  
 # 空参的存储过程
 # 插入admin表5条记录
 SELECT * FROM admin;
 
DELIMITER $
CREATE PROCEDURE mypl()
BEGIN
   INSERT INTO admin(username,PASSWORD) 
   VALUES('john','0000'),('j','0000'),('jo','0000'),('joh','0000'),('john1','0000');
END $
 
CALL mypl()$
 

# 带 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('柳岩')$

#案例：创建存储过程实现，用户是否登陆成功
CREATE PROCEDURE myp7(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 myp7('张飞','0000')$

# 带 out 模型参数的存储类型
# 根据女生名，返回对应的男神名
CREATE PROCEDURE myp8(IN beautyname VARCHAR(20), 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 = beautyname;
END$

SET @boyname$
CALL myp8('小昭',@bname)$
SELECT @bname$

# 根据
CREATE PROCEDURE myp10(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 myp10('王语嫣',@bname,@usercp)$
SELECT @bname,@usercp$

# 带 inout 模型参数的存储类型
# 传入a,b,最终a，b翻倍并返回

CREATE PROCEDURE myp11(INOUT a INT, INOUT b INT)
BEGIN
   SET a = a*2;
   SET b = b*2;
END$

SET @m=10$
SET @n=10$
CALL myp11(@m,@n)$
SELECT @m,@n$

# 插入数据
CREATE PROCEDURE myp14(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
BEGIN
   INSERT INTO admin(username,PASSWORD)
   VALUES(username,PASSWORD);
END$
CALL myp14('jprocedure','123456')$
SELECT * FROM admin$

#创建存储过程，实现传入女神编号，返回女神名称和女神电话
CREATE PROCEDURE my15(IN id INT, OUT NAME VARCHAR(20), OUT phone VARCHAR(20))
BEGIN 
  SELECT b.name,b.phone INTO NAME,phone
  FROM beauty b
  WHERE b.id=id;
END$
SET @m=10$
CALL my15(@m,@name,@phone)$
SELECT @name,@phone$

#创建存储过程函数实现输入两个女生生日，返回大小
CREATE PROCEDURE my16(IN birth1 DATETIME, IN birth2 DATETIME, OUT result INT)
BEGIN 
  SELECT DATEDIFF(birth1,birth2) INTO result;
END$

SET @r=0$
CALL my16('2012-01-02','2012-01-02',@r)$

#二、 删除存储过程
DROP PROCEDURE 存储过程名

DROP PROCEDURE mypl;

# 三、查看存储过程的信息
SHOW CREATE PROCEDURE myp9;

#案例
# 创建存储过程或函数实现传入一个日期，格式化成XX年XX月XX日
CREATE PROCEDURE myp17(IN dt DATETIME, OUT result VARCHAR(20))
BEGIN
  # select concat(YEAR(dt),'年',month(dt),'月',day(dt),'日') into result; 
   SELECT DATE_FORMAT(dt,'%y年%m月%d日') INTO result;
END $

CALL myp17('2012-01-02',@r)$
# 创建存储过程或函数实现传入女神名称，返回女生 and 男神
CREATE PROCEDURE myp18(IN beautyname VARCHAR(20), OUT str VARCHAR(20))
BEGIN
  SELECT CONCAT(beautyname,' and ', IFNULL(boyname,'null')) INTO str
  FROM boys bo
  RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
  WHERE b.name = beautyname;
END $

CALL myp18('柳岩',@r)$
SELECT @r$

# 创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录

CREATE PROCEDURE my19(IN startindex INT,IN size INT)
BEGIN 
   SELECT * FROM beauty LIMIT startindex,size;
END $
CALL my19(3,5)$