mysql存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

创建一个简单的存储过程：查询传入的参数

-- 将传入的变量var查询出来
CREATE PROCEDURE pro_showvar(IN var VARCHAR(50))
BEGIN
	SELECT var;
END;

-- 调用存储过程 CALL + 存储过程
CALL pro_showvar('a');

-- 将语句结束符改成$$
-- DELIMITER $$ 

​ 1、CREATE PROCEDURE 是创建存储过程的关键字
​ 2、pro_showvar是自定义的存储过程名称
​ 3、IN是参数的类型（存储过程三种参数：IN、OUT、INOUT）,var是自定义的参数名称，VARCHAR(50)是参数的类型（数据库类型）
​ 4、BEGIN 和 END是存储过程的开始和结束符

存储过程逻辑结构

CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名称 参数数据类型)
BEGIN
	逻辑代码部分...
END;

删除存储过程： DROP PROCEDURE IF EXISTS 存储过程名称

DROP PROCEDURE IF EXISTS out_param;

修改存储过程逻辑：存储过程不支持修改里面代码，需要用DROP删除，重新用CREATE PROCEDURE创建存储过程。

查看全部的存储过程

-- test是数据库名
show procedure status where db='test';

--另一种
select * from information_schema.routines where routine_schema='test';

参数类型

1、IN（输入参数）：传入参数，存储过程内部使用，方法的变量。传入的值可以是常量或变量。

-- in传参
CREATE PROCEDURE add_amount(IN param_id INT)
BEGIN
	-- param_id参数用来当做对象查询条件
	SELECT * FROM member where id = param_id;
END$$;

-- 调用
call add_amount6(17);

2、OUT（输出参数）：表示存储过程，向外部传出的值。传入的值只能是变量。

-- out传参
CREATE PROCEDURE out_param(OUT param INT) 
BEGIN
	SELECT param; -- param为null
	set param = 2; -- 存储过程修改输出参数OUT，会改变外部的值
	SELECT param; -- param为null,存储过程内部使用不了OUT传参
END;

set @out_param = 1;
-- out是向调用者输出参数，存储过程中的查询out参数都为null
call out_param(@out_param);
-- @out_param值被存储过程改为2
SELECT @out_param;

3、INOUT（输入输出参数）：集合了IN和OUT。传入的值只能是变量。

-- 输入输出传参 inout
CREATE PROCEDURE inout_param(INOUT param INT)
BEGIN
	SELECT param; -- param为传入变量的值
	set param = 2;
	SELECT param; -- param被改为2
END;

-- 设置变量
set @inout_param = 1;
-- 调用存储过程inout_param
call inout_param(@inout_param);
-- 查询，此时变量的值被改为2
SELECT @inout_param;

用户变量：用户变量名一般以@开头。

-- 定义用户变量@m
set @m = 'mmmm';
SELECT @m;

-- 在存储过程中定义的用户变量，外部也能访问，用户变量是全局的
CREATE PROCEDURE pro_var()
BEGIN
	set @a = 'aaa';
END;
-- 调动存储过程pro_var，里面定义了用户变量@a
call pro_var();
SELECT @a;

存储过程中条件控制语句

1、if-then-ELSE , 相当于if…else…

-- 创建存储过程。输入参数param，输出参数var1,如果param=1则输出var1等于'param=1'
-- 如果param=2则输出var1等于'param=2'，否则输出参数var1等于'param=Hello'
CREATE PROCEDURE pro_add(IN param INT,OUT var1 VARCHAR(50))
BEGIN
	IF param=1 THEN	-- if 后跟then
		set var1 = 'param=1';
	ELSEIF param=2 THEN
		set var1 = 'param=2';
	ELSE
		set var1 = concat('param=',param);
	END IF; -- if语句结束用END if
END;

--调用测试
set @hello = 'Hello';
CALL pro_add(7,@hello);
SELECT @hello;

2、case 语句，相当于switch…case…case…default

-- 创建存储过程pro_case，如果输入参数param为0则执行SELECT 0, 如果param为1则执行SELECT 1，否则执行SELECT param
CREATE PROCEDURE pro_case(IN param INT)
BEGIN
	CASE param
		WHEN 0 THEN
			SELECT 0;
		WHEN 1 THEN
			SELECT 1;
		ELSE
			SELECT param;
	END CASE; -- 结束用END CASE;
END;

-- 调用测试
CALL pro_case(5);

存储过程中循环语句

1、while , 相当于java中while循环

-- var1小于5，则执行SELECT var1
CREATE PROCEDURE pro_while(IN var1 INT)
BEGIN
	WHILE var1 < 5 DO
		SELECT var1;
		set var1 = var1 + 1;
	END WHILE; -- while结束
END;

--测试
CALL pro_while(2);

2、repeat···· end repeat，类似：do{}while(true)循环，do…while循环是条件为true执行，repeat是条件为false执行

-- 当var1 > 5为false时执行，跟do{}while()一样，总会执行一次
CREATE PROCEDURE pro_repeat(IN var1 INT)
BEGIN
	REPEAT
		SELECT var1;
		set var1 = var1 + 1;
	UNTIL var1 > 5  -- 如果var1>5是false，则继续执行
	END REPEAT;
END;

-- 测试
CALL pro_repeat(3);

3、loop ·····end loop，满足条件离开循环体

-- 如果var1>5,则跳出标签LOOP_LABLE标记的语句块
-- LOOP_LABLE标记，跟java中for循环while循环标记类似，跳出指定循环
CREATE PROCEDURE pro_loop(IN var1 INT)
BEGIN
	LOOP_LABLE:LOOP
		SELECT var1;
		set var1 = var1 + 1;
		IF var1 > 5 THEN
			LEAVE LOOP_LABLE; -- 如果var1>5,跳出标记LOOP_LABLE
		END IF;
	END LOOP;
END;

-- 测试
CALL pro_loop(3);

4、ITERATE迭代

-- ITERATE迭代，继续执行标记的语句块
DROP PROCEDURE pro_iterate;
CREATE PROCEDURE pro_iterate(IN var1 INT)
BEGIN
	LOOP_LABLE:LOOP
		IF var1<5 THEN	-- 当var1<5时，不会执行下面的代码
			SET var1 = var1 + 1;
			ITERATE LOOP_LABLE; -- 走到这里继续重头执行，直到没进入这个方法才会执行下面的代码
		END IF;
		SET var1 = var1 + 1;
		SELECT var1;
		IF var1 > 10 THEN
			 LEAVE LOOP_LABLE; -- var1>10结束，调出LOOP_LABLE标记的代码块
		END IF;
	END LOOP;
END;

-- 测试
call pro_iterate(1);