目录

存储过程定义

存储过程的特性

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

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

loop循环

loop是死循环，需要手动退出循环，我们可以使用leave来退出循环

DELIMITER $
CREATE PROCEDURE looptest() 
BEGIN
DECLARE age VARCHAr(32) DEFAULT 1;
num_loop:LOOP
SELECT age;
IF age>=10
THEN LEAVE num_loop;
END IF;
SET age=age+1;
END LOOP num_loop;
END $
DELIMITER ;
CALL looptest();

repeat循环

DELIMITER //
CREATE PROCEDURE repeat01()
BEGIN
DECLARE a INT DEFAULT 1;
repeat_01:REPEAT
SELECT a;
SET a=a+1;
UNTIL a>=10
END REPEAT repeat_01;
END //
DELIMITER ;
CALL repeat01();

while循环

DELIMITER //
CREATE PROCEDURE while01()
BEGIN
DECLARE b INT DEFAULT 1;
while01:WHILE b<=10
DO SELECT b;
SET b=b+1;
END WHILE while01;
END //
DELIMITER ;
CALL while01();

游标

用游标得到某一个结果集，逐行处理数据。

DELIMITER //
CREATE PROCEDURE fetch01(IN l VARCHAr(32))
BEGIN
DECLARE a INT;
DECLARE b VARCHAr(32);
#声明游标cursor_name用来承接结果集
DECLARE cursor_name CURSOR FOR 
SELECT deptno,dname FROM dept WHERe loc=l;
OPEN cursor_name;
#取出结果集（有2个属性deptno，dname）分别用a，b承接
#每次fetch只能取出一行数据
FETCH cursor_name INTO a,b;
SELECt a,b;
FETCH cursor_name INTO a,b;
SELECT a,b;
CLOSE cursor_name;
END //
DELIMITER ;
CALL fetch01("二区");

handler句柄

注意：在语法中变量声明，游标声明，handler声明是有先后顺序的，否则创建存储过程出错

DELIMITER //
CREATE PROCEDURE fetch01(IN l VARCHAr(32))
BEGIN
DECLARE a INT;
DECLARE b VARCHAr(32);
#声明flag默认为true
DECLARE flag BOOLEAN DEFAULT TRUE;
DECLARE cursor_name CURSOR FOR 
SELECT deptno,dname FROM dept WHERe loc=l;
#hander句柄——当出现1329异常时会执行后面语句,声明句柄应在游标语法之后
#这里面的continue是执行后面语句后继续往下走的意思，如果执行完语句退出，则continue换为exit
DECLARE CONTINUE HANDLER FOR 1329 SET flag=FALSE;
OPEN cursor_name;
loop01:LOOP
FETCH cursor_name INTO a,b;
IF flag THEN SELECT a,b;
ELSE LEAVE loop01;
END IF;
END LOOP loop01;
CLOSE cursor_name;
END //
DELIMITER ;
CALL fetch01("二区");