目录

存储过程定义

存储过程的特性

关于存储过程的创建及删除和查看语句 

dept表

创建简单存储过程

变量的声明和赋值

局部变量 

用户变量

into赋值

入参出参

入参

出参

流程控制

if判断

case分支

循环干预

loop循环

repeat循环

while循环

游标

handler句柄

存储过程定义

存储过程是数据库中的一个重要对象，其可以封装sql语句集，可以用来完成一些较为复杂的业务逻辑并且可以入参出参类似于java中方法的书写。（创建时会先编译后保存，用户后续调用不需要再次编译）

存储过程的特性

1. 有输入输出参数，可以声明变量，有if/else/case/while等控制语句，通过编写存储过程可以实现更复杂的逻辑功能
2. 函数的普通特性：模块化、封装、代码复用
3. 速度快，只有首次执行需要经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤

关于存储过程的创建及删除和查看语句 

创建存储过程：

删除存储过程：drop procedure 存储过程名称；

查看存储过程信息：show create procedure 存储过程名称； 

注意：存储过程名称不带（）

dept表

创建简单存储过程

#定义结束符$表示执行到$结束（当然，结束符可以任意）
DELIMITER $
#创建存储过程
CREATE PROCEDURE hello_produce()
BEGIN
#返回的结果为select后面的变量（当然可以为多个，多个用逗号隔开）
SELECT dname,loc FROM dept;
END $
DELIMITER ;
#调用存储过程
CALL hello_produce();

变量的声明和赋值

关于变量

1. 存储过程的变量是由作用域的，作用域在begin和end之间
2. 需要在多个begin和end块之间传值则可以在一层块内使用局部变量
3. 传参变量是全局的，可以在多个块内起作用

局部变量 

DELIMITER $
CREATE PROCEDURE var01() 
BEGIN
#声明varchar类型的变量为li
DECLARE li VARCHAR(32) DEFAULT "lili";
#为声明的变量赋值
SET li="lila";
#查询该变量
SELECT li;
END $ 
DELIMITER ;
#调用存储过程
CALL var01();

用户变量

（用户自定义，当前会话【连接】有效，类比java成员变量）

局部变量需要声明方可使用，全局变量不需要声明即可使用

DELIMITER $
CREATE PROCEDURE var02() 
BEGIN
#全局变量直接赋值
SET @nickname="cjc";
END $
DELIMITER ;
CALL var02();
SELECT @nickname;

into赋值

DELIMITER $
CREATE PROCEDURE var03() 
BEGIN
#将查询到的值赋给用户变量@deprnum（这样的select得不到返回数据）
SELECT deptno INTO @deptnum FROM dept WHERE deptno=2;
END $
DELIMITER ;
CALL var03();
#查询用户变量@deprnum
SELECT @deptnum;

入参出参

语法：in（入参）/out（出参）/inout（可作为出/入参） 参数名 参数类型

入参

DELIMITER $
CREATE PROCEDURE param01(IN nickname VARCHAR(32)) 
BEGIN
#用用户变量接受入参
SET @nkname=nickname;
END $
DELIMITER ;
CALL param01("hello");
#查询用户变量@nkname
SELECT @nkname;

出参

DELIMITER $
CREATE PROCEDURE input(IN loc VARCHAR(23),INOUT deptno INT) 
BEGIN
SELECT dept.`deptno` INTO @deptno FROM dept WHERE dept.`loc`=loc;
END $
DELIMITER ;
CALL input("一区",@deptno);
SELECT @deptno;

流程控制

if判断

DELIMITER $
CREATE PROCEDURE iftest(IN deptno INT) 
BEGIN
IF (deptno=1) THEN SELECT "1";
ELSEIF (deptno=2) THEN SELECT "2";
ELSE SELECT "3";
END IF;
END $
DELIMITER ;
CALL iftest(3);

case分支

#1
DELIMITER $
CREATE PROCEDURE casetest(IN deptno INT) 
BEGIN
CASE deptno
WHEN 1 THEN SELECT "1";
WHEN 2 THEN SELECT "2";
ELSE SELECT "3";
END CASE;
END $
DELIMITER ;
CALL casetest(2);

#2
DELIMITER $
CREATE PROCEDURE casetest(IN deptno INT) 
BEGIN
CASE 
WHEN deptno=1 THEN SELECT "1";
WHEN deptno=2 THEN SELECT "2";
ELSE SELECT "3";
END CASE;
END $
DELIMITER ;
CALL casetest(2);

循环干预

退出与继续循环（与java的比较）

• leave label —— break
• iterate label —— continue