存储过程是事先经过编译的一组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;