MySQL存储过程

存储过程是事先经过编译的一组SQL语句的集合，作用代码封装与复用。

一、基本语法

• 创建

CREATE PROCEDURE 存储过程名称（[参数]）
BEGIN
	--SQL语句
END;

• 调用

CALL 名称（[参数]）

• 查看

SELECT * FROM information_schama.ROUTINES WHERE ROUTIN_SCHEMA = 'itcast';

• 删除

DROP PROCEDURE 名称;

二、变量和参数

1. 系统变量：分为全局变量和会话变量

• 查看系统变量

SHOW [SESSION | GLOBAL] VARIABLES;
SHOW [SESSION | GLOBAL] VARIABLES LIKE '';
SELECT @@[SESSION | GLOBAL] 系统变量名;  --查看指定变量的值

• 设置系统变量

SET [SESSION | GLOBAL] 系统变量名=值;
SET @@[SESSION | GLOBAL] 系统变量名=值;

2. 用户定义变量：不用提前声明，直接用“@变量名”使用就可以，作用域为当前连接。

• 赋值

SET @变量名 ：= "";
SELECT @变量名 ：= "";
SELECT 字段名 INTO @变量名 FROM 表名;

• 使用

SELECT @变量名;

3. 局部变量：访问之前需要用DECLARE提前声明, 可用作存储过程内的局部变量和输入参数，范围在BEGIN… END之间。

• 声明

DECLARE 变量名 变量类型 [DEFAULT ...]

• 赋值

SET 变量名 ：= "";
SELECT 字段名 INTO 变量名 FROM 表名;

参数

类型	含义	备注
IN	输入	默认
OUT	输出
INOUT	输入或输出

三、判断与循环

• if 条件判断

IF 条件1 THEN
	...
ELSEIF 条件2 THEN
	...
ELSE
	...
END IF;

• case

--语法1
CASE case_value
	WHEN search_value1 THEN statement_list1
	[WHEN search_value2 THEN statement_list2]...
	[ELSE statement_list]
END CASE;

--语法2
CASE 
	WHEN search_condition1 THEN statement_list1
	[WHEN search_condition1 THEN statement_list2]...
	[ELSE statement_list]
END CASE;

• while：有条件循环

WHILE 条件 DO
	SQL
END WHILE;

• repeat：有条件循环，满足条件时退出

REPEAT
	SQL
	UNTIL 条件
END REPEAT;

• loop：简单循环，可配合LEAVE（退出循环）、ITERATE（跳过当前循环剩下的语句，直接进入下一次循环）使用

[begin_label:] LOOP
	SQL
END LOOP[end_label];

LEAVE label;
ITERATE label; 

• handler 条件处理程序：定义在控制结构中遇到问题相应的处理步骤

DECLARE handle_action HANDLER FOR condition_value...statement;
handle_action
	CONTINUE
	EXIT
condition_value
	SQLSTATE 状态码;  --如02000
	SQLWARNNING  --01开头的SQLSTATE码的简写
	NOT FOUND  --02开头的SQLSTATE码的简写
	SQLEXCEPTION  --其他SQLSTATE码的简写

四、游标

游标用来存储查询结果集的数据类型，在存储过程和函数中可以使用其对结果集进行循环处理。

• 声明

DECLARE 游标名称 CURSOR FOR 查询语句;

• 打开

OPEN 游标名称;

• 获取记录

FETCH 游标名称 INTO 变量[,变量];

• 关闭

CLOSE 游标名称;

案例： 根据传入的usage,查询用户表tb_usert中，所有的用户年龄<= usage 的用户姓名(name)和专业(profession)，并将用户的姓名和专业插入到所创建的一张新表（id,name,profession）中。

--逻辑：
--1.声明游标，查询结果集
--2.准备：创建表结构
--3.打开游标
--4.获取游标中的记录
--5.插入到新表
--6.关闭游标
CREATE PROCEDURE p(IN uage INT)
BEGIN
	--1.声明游标，查询结果集
	DECLARE uname VARCHAR(100);
	DECLARE upro VARCHAR(100);
	DECLARE u_cursor CURSOR FOR SELECT name,profession FROM tb_user WHERE age<=uage;
	--条件处理程序
	DECLARE EXIT HANDLER FORSQLSTATE '02000' CLOSE u_cursor;
	
	--2.准备：创建表结构
	DROP TABLE IF EXISTS tb_user_pro;
	CREAT TABLE IF NOT EXISTS tb_user_pro(
		id INT PRIMARY KEY AUTO_INCREMENT,
		name VARCHAR(100),
		profession VARCHAR(100)
	);

	--3.打开游标
	OPEN u_cursor;
	WHILE TRUE DO
		--4.获取游标中的记录
		FETCH u_cursor INTO uname,upro;
		--5.插入到新表
		INSERT INTO tb_user_pro VALUES(NULL,uname,upro);
	END WHILE;

	--6.关闭游标
	CLOSE u_cursor;
END;