当前位置： 首页 > news >正文

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

news 2025/6/29 14:27:34

存储过程

存储过程概述

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_exprWHEN  value THEN statement_list[WHEN value THEN statement_list] ...[ELSE statement_list]
END CASE;

（3）CASE语法格式一示例

DROP PROCEDURE
IFEXISTS 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:] LOOPstatement_list
END 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 thenleave 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)BEGINDECLARE sumResult INT DEFAULT 0;add_loop: LOOPSET sumResult=sumResult+start1;SET start1=start1+1;IF start1 <= end1 THENITERATE  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:] REPEATstatement_list
UNTIL expr_condtion
END REPEAT [repeat_label]

（3）REPEAT语法实例

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

8.WHILE

（1）WHILE概述

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

（2）WHILE语法格式

[while_label:] WHILE expr_condition DOstatement_list
END WHILE [while_label]

（3）WHILE语法示例


DROP PROCEDURE IF EXISTS testWHILE;)
DELIMITER //
CREATE PROCEDURE testWHILE(IN start INT,IN end INT)BEGINDECLARE sumResult INT DEFAULT 0;WHILE  start <= end DOSET 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();
http://www.lryc.cn/news/152471.html

相关文章：

  • JavaScript的数组和字典的用法
  • 中断和异常
  • 【python】实现积分
  • 微信仿H5支付
  • 数据结构——栈
  • 组件化开发之如何封装组件-react
  • 大数据HBase学习圣经：一本书实现HBase学习自由
  • Leetcode110. 平衡二叉树
  • Swift的NSClassFromString转换
  • linux上vim编辑器设置
  • SpringCloudAlibaba OpenFeign整合及详解
  • Mysql--技术文档--MVCC(Multi-Version Concurrency Control | 多版本并发控制)
  • 全网都在用的nnUNet V2版本改进了啥，怎么安装？（一）
  • iOS开发Swift-4-IBAction,group,音乐播放器-木琴App
  • 【linux】pid 文件的作用ing
  • K8s简介之什么是K8s
  • 说说Flink双流join
  • I2C与I3C的对比
  • 睿趣科技：抖音开小店大概多久可以做起来
  • CCF-CSP 26次 第三题【角色授权】
  • Ansible学习笔记11
  • Vue中如何为Echarts统计图设置数据
  • 力扣141. 环形链表
  • 4.1 链式栈StackT
  • 算法练习（10）：牛客在线编程10 贪心算法
  • Java8新特性1——函数式接口lambda表达式
  • 文本标注技术方案（NLP标注工具）
  • 03-使用一个不可变对象作为key，红黑树怎么比较大小？
  • 2021江苏省赛热身赛 C Magic Rabbit(数形结合)
  • AES加密(2)：AES代码实现解析