MySQL学习笔记之存储过程的删除、创建、调用、修改、Create procedure

前言：存储过程的含义

• 存储过程和函数：类似于高级语言中的方法函数

• 好处：

  1、提高了代码的重用性
  2、简化操作

• 存储过程的含义：一组预先编译好的SQL语句的集合，理解成批处理语句，其好处具体说来如下所示：

  ​ 1、可提高代码的重用性
  ​ 2、简化操作
  ​ 3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

一、创建存储过程的语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体（一组合法的SQL语句）
END

注意：

• 1、参数列表包含3部分

  参数模式   参数名   参数类型
  如：IN stuname VARCHAR(20)
  
  ----------------------------
  
  参数模式：（3种）	
  IN:输入参数
  OUT:输出参数
  INOUT:既可以作为输入，也可以作为输出
  

• 2、如果存储过程体仅仅是一句话，可以省略 BEGIN END

• 3、存储过程中的每一句SQL语句的结尾要求必须加分号 ‘;’ 存储过程的结尾使用 DELIMITER 重新设置

  语法：

  DELIMITER 结束标记
  案例：
  DELIMITER $

  其中：只要设置了自定义的某个结束符，如’$’,那么在存储过程外部，’$‘将代替MySQL中的默认结束符’;‘行使功能，在存储过程的内部依旧用’;'表示结束

二、调用存储过程的语法

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

2.1 空参列表

案例1：编写存储过程实现插入girls.admin中5条记录（注意：在DOS命令行中尝试如下代码）

-- 设置结束符为 $
DELIMITER $
-- 创建存储过程
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,PASSWORD) VALUES('mike',0000),
('nike',0000),
('adidas',0000),
('anta',0000),
('LiNing',0000);
END $

-- 调用
CALL myp1()$

2.2带有in模式参数的过程

案例2：编写存储过程实现查询女生的男友信息

-- 设置结束符为 $
DELIMITER $
-- 创建存储过程
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	SELECT b.* 
	FROM beauty g LEFT JOIN boys b ON g.boyfriend_id = b.id
	WHERE g.name = beautyName;
END $

-- #调用
CALL myp2('苍老师')$

案例3：编写存储过程拥有两个输入参数，实现判断用户是否成功登录

-- 创建
CREATE PROCEDURE myp3(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 myp3('john','8888')$

2.3 带out参数的存储过程

案例4：编写存储过程有一个输出参数，实现根据女生名返回男友名

-- 设置结束符为 $
DELIMITER $
-- 创建存储过程
CREATE PROCEDURE myp4(IN girlName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT b.boyName INTO boyName
	FROM boys b INNER JOIN beauty g ON g.boyfriend_id = b.id
	WHERE g.name = girlName;
END $

#调用
SET @result$ --创建接收变量(自定义用户变量)
CALL myp4('柳岩',@result)$

案例5：编写存储过程有2个输出参数，实现根据女生名字返回男友名和魅力值

-- 设置结束符为 $
DELIMITER $
-- 创建存储过程
CREATE PROCEDURE myp5(IN girlName VARCHAR(20),OUT bName VARCHAR(20), OUT usercp INT)
BEGIN
	SELECT b.boyName,b.userCP INTO bName,usercp
	FROM beauty g INNER JOIN boys b ON g.boyfriend_id = b.id
	WHERE g.name = girlName;
END $

-- #创建变量
SET @bname = ''$
SET @usercp = 0$

-- #调用
CALL myp5('柳岩',@bname,@usercp)$

-- #打印变量
SELECT @bname,@usercp$

2.4.带有inout模式参数的存储过程

案例6：编写一个带有inout参数的存储过程，实现两个数翻倍

DELIMITER $
-- 创建存储过程
CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT)
BEGIN
	SET a = a*2; ##注意：变量的赋值操作set
	SET b = b*2;
END$

#调用
SET @num1 = 10$
SET @num2 = 20$
CALL myp6(@num1,@num2)$

2.5 案例

1.创建存储过程实现传入用户名和密码，插入到girls.admin表中

CREATE PROCEDURE test1(IN username VARCHAR(20), IN inpassword VARCHAR(20))
BEGIN
INSERT INTO girls.admin(username,PASSWORD) VALUES(username,inpassword);
END$

-- 调用
CALL test1('dzc','9999')$

2.创建存储过程实现输入两个女生的生日，返回大小

CREATE PROCEDURE test2(IN g1date DATE,IN g2date DATE,OUT res INT)
BEGIN
	SELECT DATEDIFF(g1date,g2date) INTO res;
END$

-- 调用
SET @res = ''$
CALL test2('1980-01-02','1999-09-09',@res)

3.创建存储过程实现传入女生编号，返回其姓名和电话

CREATE PROCEDURE test3(IN id INT,OUT gname VARCHAR(20),OUT gphone VARCHAR(21))
BEGIN
	SELECT g.name,g.phone INTO gname,gphone
	FROM beauty g
	WHERE g.id = id;
END $

SET @gname = ''$
SET @gphone= ''$

CALL test3(1,@gname,@gphone)$

三、存储过程的删除Drop

• 语法：

  DROP PROCEDURE 存储过程名;
  

  注意：存储过程的删除，每次只能删除一个

  -- 一次删除一个存储过程
  DROP PROCEDURE test3;
  

四、查看存储过程的信息

4.1 语法和注意

• 语法：

  show create procedure 存储过程名;
  
  -- 案例如下：
  SHOW CREATE PROCEDURE test1;
  

  注意：存储过程通常情况下不修改，如果需要修改，那么删除重新编写

4.2 练习：

1.创建存储过程，实现传入一个日期，返回格式为xx年xx月xx日

DELIMITER $
CREATE PROCEDURE test01(IN in_date DATE,OUT out_str VARCHAR(20))
BEGIN
	SELECT DATE_FORMAT(in_date,'%y年%m月%d日') INTO out_str;
END$

SET @str = ''$
CALL test01(NOW(),@str)

2.创建存储过程实现传入女神姓名，返回“女生 and 男生”格式的字符串

CREATE PROCEDURE test02(IN in_name VARCHAR(10),OUT out_str VARCHAR(20))
BEGIN
	SELECT CONCAT(in_name,' AND ',b.boyName) INTO out_str
	FROM beauty g INNER JOIN boys b ON g.boyfriend_id = b.id
	WHERE g.name = in_name;
END $

SET @out_str = '' $
CALL test02('柳岩',@out_str)$
SELECT @out_str$