MySQL-存储过程、流程控制、游标

存储过程

存储过程概述

1.产生背景

• 开发过程总，经常会遇到重复使用某一功能的情况

2.解决办法

• MySQL引人了存储过程(Stored Procedure)这一技术

3.存储过程

• 存储过程就是一条或多条SQL语句的集合
• 存储过程可将一系列复杂操作封装成一个代码块，以便重复使用，从而减少工作量提升开发效率。

4.编译

• SQL语句需先编译然后执行。
• 存储过程将为了完成特定功能的SQL语句集，经编译后存储在数据库中。

4.使用

• 通过指定存储过程的名字通过传参的方式对其进行调用。
• 存储过程可看做是编程的函数，它允许以传参调用的访问方式。

存储过程入门案例

1.准备数据

• 创建数据库
• 创建学生表
• 插入数据

DROP TABLE IF EXISTS student;CREATE TABLE student (sid CHAR(6),sname VARCHAR(50),age INT,gender VARCHAR(50) DEFAULT 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');

2.编写存储过程

-- 改存储过程中依据指定年纪查询出符合条件的学生delimiter //create procedure procedureDemo01(in sage int)beginselect * from student where age>sage;end //delimiter ;

3.关键词解释

（1）DELIMITER //

• 声明当前段分隔符

• 让编译器把两个//之间的内容当做存储过程的代码

• MySQL 默认以 **；**为分隔符

  • 若没有声明分隔符，则编译器会把存储过程当成SQL语句进行处理，从而造成编译过程会报错

（2）DELIMITER ;

• 把分隔符还原为默认分隔符;

• DELIMITER也可以指定其他符号作为结束符

• 注意：DELIMITER与要设定的结束符之间一定要有一个空格，否则设定无效

（3）CREATE PROCEDURE 存储过程名称(参数)

• 创建存储过程

（4）BEGIN END

• 过程体

• 存储过程的过程体以BEGIN开始以END结束

4.调用存储过程

• call 存储过程名(参数);调用存储过程

call procedureDemo01(15);

5.删除存储过程

• DROP PROCEDURE 存储过程名;删除存储过程

drop procedure procedureDemo01;

存储过程的参数

• 存储过程定义中可传参，可不传参

1.传参分类

CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类型...])

2.IN

• 输入参数，表示该参数的值必须在调用存储过程时指定

-- findStudentByAge，该存储过程中有个IN参数，该参数为INT类型叫做sage delimiter //create procedure findStudentByAge(in sage int)beginselect * from student where age=sage;end //delimiter ;-- 调用存储过程call findStudentByAge(16);

3.用户变量

（1）语法

@var_name

（2）创建用户变量

SET 用户变量=初始值;

（3）查看用户变量值

SELECT 用户变量;

（4）注意

• 定义用户变量后可方便开发过程的代码编写，只要连接未关闭我们均可直接使用该变量。
• 当连接关闭时所有客户变量将自动释放。

4.OUT参数

• 表示输出参数，可在存储过程内改变该值并将其返回

-- 存储过程countStudent，该存储过程中有个OUT参数，该参数为INT类型叫做total。-- 存储体中将统计的结果利用`INTO`存入total中。-- 调用存储过程中将用户变量@number作为参数传入，然后利用`SELECT @number`参看结果。delimiter //create procedure countStudent(out total int)beginselect count(*) into total from student;end //delimiter ;set @number=0;call countStudent(@number);select @number;

5.INOUT参数

• 表示输入输出参数，可在调用存储过程时指定该参数并在存储体中改变该值并将其返回

-- 存储过程searchStudentGender，该存储过程中有个INOUT参数，该参数为VARCHAR(50)类型叫做message。-- message既当做输入参数又当做输出参数，即输入参数为学生的姓名lili返回的是学生的性别male。-- 调用存储过程时将初始值为lili的用户变量@info传入存储过程，调用存储过程结束后再次查询@info的值为male。 delimiter //create procedure queryStudentGender(inout message varchar(50))beginselect gender into message from student where sname=message;end //delimiter ;set @info='lili';call queryStudentGender(@info);select @info;

变量

1.使用场景

• 编写存储过程中有时需要使用变量保存数据处理过程中的值。

2.作用范围

• BEGIN…END

3.语法

DECLARE varName dataType [DEFAULT value];

• DECLARE：定义变量
• varName：局部变量的名称
• dataType：局部变量的类型
• DEFAULT value：变量默认值

（1）变量赋值或修改变量值

SET varName = value;

（2）示例一

delimiter //create procedure varTest01(in number1 int)begin-- 声明变量declare number2 int;-- 声明变量resultdeclare result int;-- 为变量number2赋值set number2 = 99;-- 为变量result赋值set result = number1 + number2;-- 显示resultselect result;end //delimiter ;-- 调用存储过程call varTest01(1);

（2）示例二

-- 若存在存储过程findStudent,将之删除drop procedure if exists findStudent;delimiter //create procedure findStudent(in studentID char(6))begindeclare studentName varchar(59);declare studentGender varchar(50);-- 将查询结果保存至变量studentName和studentGender中select sname,gender into studentName,studentGender from student where sid=studentID;-- 显示studentName和studentGender中保存的值select studentName,studentGender;end //delimiter ;-- 调用存储过程call findStudent('S_1001');

流程控制

1.流程控制概述

• 流程控制语句用于将多个SQL语句划分或组合成符合业务逻辑的代码块。

2.流程控制语句

• IF语句
• CASE语句
• LOOP语句
• WHILE语句
• LEAVE语句
• ITERATE语句
• REPEAT语句

3.IF

（1）IF概述

• IF语句包含多个条件判断，根据结果为TRUE、FALSE执行语句。
• IF语句于编程语言中学得if、else if、else类似

（2）语法

IF expr_condition THEN statement_list    [ELSE expr_condtion THEN statement_list] ...    [ELSE statement_list]END IF

（3）示例代码

drop procedure if exists ifTest;delimiter //create procedure ifTest(in num int)begindeclare result varchar(20);if num < 0 then set result = 'negative number';elseif num = 0 thenset result = 'number is zero';else set result = 'positive number';end if;select result;end //delimiter;-- 调用存储过程call ifTest(-1);

4.CASE 语句

（1）CASE概述

• CASE 是另一个条件判断的雨具，改雨具有两种语法格式

（2）CASE语法格式一

CASE case_expr    WHEN  value THEN statement_list    [WHEN value THEN statement_list] ...    [ELSE statement_list]END CASE;

（3）CASE语法格式一示例

DROP PROCEDUREIFEXISTS testCase01;delimiter //create procedure testCase01(in num int)begindeclare result varchar(20);case numwhen num>0 set result = 'num is 正数';when num=0 set result = 'num is zero';else set result = 'num is 负数';end case;select result;end //delimiter ;call testCase01(1);

（4）CASE语法格式二

CASEWHEN expr_condition THEN statement_list[WHEN expr_condition THEN statement_list] ...    [ELSE statement_list]END CASE;

（5）CASE语法格式二示例

delimiter //create procedure testCase02(in num int)begindeclare result varchar(20);casewhen num > 0 then set result = 'positive number';when num = 0 then set result = 'number is zero';else set result = 'positive number';end case;select result;end //delimiter ;call testCase02(1);

5.LOOP语句

（1）LOOP语句概述

• LOOP 是一种循环语句，用来重复执行某些语句。
• 在执行过程中可使用LEAVE语句跳出循环，也可以嵌套IF等判断语句。

（2）LOOP语法

[loop_label:] LOOP    statement_listEND LOOP [loop_label]

• loop_label：标识标注名称，该参数可省略

（3）LOOP语句示例

delimiter //create procedure testLoop(in start1 int ,in end1 int)begindeclare sumResult int default 0;add_loop: loopset sumResult = sumResult + start1;set start1 = start1 + 1;if start1 > end1 then leave add_loop; end if; end loop add_loop; select sumResult;end //delimiter ;call testLoop(0,100);

6.ITERATE

（1）ITERATE概述

• 表示再次循环，该语句作用是将执行顺序转到语句段开头处再执行，它与编程语言的continue非常类似；

（2）ITERATE 语法

ITERATE label

（3）示例

DROP PROCEDURE IF EXISTS testITERATE; DELIMITER // CREATE PROCEDURE testITERATE(IN start1 INT,IN end1 INT) BEGIN DECLARE sumResult INT DEFAULT 0; add_loop: LOOP     SET sumResult=sumResult+start1;     SET start1=start1+1;    IF start1 <= end1 THEN         ITERATE  add_loop;     ELSE LEAVE add_loop;     END IF; END LOOP add_loop; SELECT sumResult; END //DELIMITER ;call testITERATE(0,100);

7.REPEAT

（1）REPEAT概述

• 表示带判断条件的循环过程
• 每次语句执行完毕之后，会对条件表达式进行判断，若表达式为TRUE则循环结束，否则重复执行。
• REPEAT类似于do…while。

（2）REPEAT语法

[repeat_label:] REPEAT    statement_listUNTIL expr_condtionEND REPEAT [repeat_label]

（3）REPEAT语法实例

 DELIMITER // CREATE PROCEDURE testREPEAT(IN start INT,IN end INT) BEGIN DECLARE sumResult INT DEFAULT 0; REPEAT     SET sumResult = sumResult + start;     SET start=start+1;UNTIL start > end END REPEAT;SELECT sumResult;END //DELIMITER ;call testREPEAT(0,100);

8.WHILE

（1）WHILE概述

• 用于带判断条件的循环过程。
• 与REPEAT不同，WHILE语句先判断表达式，为真则执行循环内的语句，否则退出循环。
• WHILE与编程语言的while类似。

（2）WHILE语法格式

[while_label:] WHILE expr_condition DO    statement_listEND WHILE [while_label]

（3）WHILE语法示例

DROP PROCEDURE IF EXISTS testWHILE;)DELIMITER //CREATE PROCEDURE testWHILE(IN start INT,IN end INT) BEGIN DECLARE sumResult INT DEFAULT 0; WHILE  start <= end DO     SET sumResult = sumResult + start;     SET start=start+1; END WHILE; SELECT sumResult; END //DELIMITER ;call testWHILE(0,100);

游标（cursor)

1.游标概述

• 游标提供一种对 从表中检索出的数据进行操作的灵活手段。
• 本质而言，游标实际上是一种能 从包括多条数据记录的结果集中每次提取一条记录的机制。
• 例如，存储过程中查询语句可能会返回多条记录或大量记录，此时就需要游标来逐条读取查询结果集中的记录。

2.游标使用步骤

• 定义游标
• 打开游标
• 使用游标
• 关闭游标
• 释放游标

（1）定义游标

declare 有标明 cursor for select_statement;

（2）打开游标

open 游标名;

（3）使用游标

declare 变量1 与对应列值相同的数据类型declare 变量2 与对应列值相同的数据类型declare 变量3 与对应列值相同的数据类型fetch next from 游标 [into 变量名1,变量名2,变量名3,...]

• declare：声明变量用于存储查询出来的数据；
• fetch：获取数据并保存至 declare 声明的变量中；
• fetch next：获取下一行数据
• 注意：初始状态中，游标默认指向数据集的第一行数据之前。故，在使用游标时应该执行fetch next 操作让其指向第一行数据。

（4）关闭游标

close 游标名;

（5）释放游标

deallocate 游标名;

3.游标示例一

delimiter //create procedure cursorTest01()begin-- 声明与对应列类型相同的4个变量declare studentID char(6);declare studentName varchar(50);declare studentAge int;declare studentGender varchar(50);-- 定义游标studentCursordeclare studentCursor cursor for select * from student;-- 打开游标open studentCursor;-- 使用游标fetch next from studentCursor into studentID,studentName,studentAge,studentGender;-- 显示结果select studentID,studentName,studentAge,studentGender;-- 关闭游标close studentCursor;end //delimiter ;-- 调用存储过程call cursorTest01();

• 在存储过程中，查询后得到的结果为表中所有数据。
• 在此，利用 fetch next 将结果集中的第一条数据的sid,sname,age,gender对应地保存至 studentID，studentName，studentName，studentAge，studentGender变量中。

4.游标示例二

drop procedure if exists cursorTest02;delimiter //create procedure cursorTest02()begin-- 声明与对应列类型相同的4个变量declare studentID char(6);declare studentName varchar(50);declare studentAge int;declare studentGender varchar(50);-- 声明计数器declare count int default 0;declare total int default 0;-- 定义游标studentCursordeclare studentCursor cursor for select * from student;set total = (select count(*) from student);-- 打开游标open studentCursor;-- 使用游标-- 利用 REPEAT 语句循环取出结果集中的数据REPEATfetch next from studentCursor into studentID,studentName,studentAge,studentGender;-- 显示结果select studentID,studentName,studentAge,studentGender;set count = count + 1;until count = totalend repeat;-- 关闭游标close studentCursor;end //delimiter ;-- 调用存储过程call cursorTest02();

来源地址：https://blog.csdn.net/weixin_53903929/article/details/132586107