1. Toy模板网首页
  2. > Toy博客

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

7月前 作者：咸鱼不咸鱼 分类：Toy博客 阅读(32) 违法举报

这篇具有很好参考价值的文章主要介绍了SQL-存储过程、流程控制、游标。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

存储过程

存储过程概述

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)
begin
	select * from student where age>sage;
end //
delimiter ;

3.关键词解释

（1）DELIMITER //

  • 声明当前段分隔符

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

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

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

（2）DELIMITER ;

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

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

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

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

  • 创建存储过程

（4）BEGIN END

  • 过程体

  • 存储过程的过程体以BEGIN开始以END结束文章来源地址https://www.toymoban.com/news/detail-685403.html

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)
begin
	select * 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)
begin
	select 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))
begin
	select 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;
	-- 声明变量result
	declare result int;
	-- 为变量number2赋值
	set number2 = 99;
	-- 为变量result赋值
	set result = number1 + number2;
	-- 显示result
	select result;
end //
delimiter ;
-- 调用存储过程
call varTest01(1);

（2）示例二

-- 若存在存储过程findStudent,将之删除
drop procedure if exists findStudent;
delimiter //
create procedure findStudent(in studentID char(6))
begin
	declare 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)
begin
	declare result varchar(20);
	if num < 0 then 
		set result = 'negative number';
	elseif num = 0 then
		set 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 PROCEDURE
IF
	EXISTS testCase01;
delimiter //
create procedure testCase01(in num int)
begin
	declare result varchar(20);
	case num
		when 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语法格式二

CASE
	WHEN 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)
begin
	declare result varchar(20);
	case
		when 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_list
END LOOP [loop_label]
  • loop_label：标识标注名称，该参数可省略

（3）LOOP语句示例

delimiter //
create procedure testLoop(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
			 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_list
UNTIL expr_condtion
END 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_list
END 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);
	-- 定义游标studentCursor
	declare 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;
	-- 定义游标studentCursor
	declare studentCursor cursor for select * from student;
	set total = (select count(*) from student);
	-- 打开游标
	open studentCursor;
	-- 使用游标
	-- 利用 REPEAT 语句循环取出结果集中的数据
	REPEAT
		fetch next from studentCursor into studentID,studentName,studentAge,studentGender;
		-- 显示结果
		select studentID,studentName,studentAge,studentGender;
		set count = count + 1;
		until count = total
	end repeat;
	-- 关闭游标
	close studentCursor;
end //
delimiter ;
-- 调用存储过程
call cursorTest02();

到了这里，关于SQL-存储过程、流程控制、游标的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

  • SQL Server判断数据库、表、列、视图、存储过程、函数是否存在

    在写一些业务逻辑相对复杂点的存储过程的时候，经常会用到临时表或者数据表作为临时结果的保存。但每次在作表是否存在的判断时，往往想不起完整的SQL写法。因此，记录一些常用的数据库对象是否存在的判断方法，可以达到快速查找的目的。正是：好记性不如烂笔头。

    2024年02月02日
    浏览(49)

  • 【SQL Server】数据库开发指南（七）MS-SQL存储过程全面解析：种类、优点和创建方法详解

    本系列博文还在更新中，收录在专栏：#MS-SQL Server 专栏中。 本系列文章列表如下： 【SQL Server】 Linux 运维下对 SQL Server 进行安装、升级、回滚、卸载操作 【SQL Server】数据库开发指南（一）数据库设计的核心概念和基本步骤 【SQL Server】数据库开发指南（二）MSSQL数据库开发对

    2024年02月07日
    浏览(50)
  • 【MySQL】探索MySQL存储过程的魔力，初学者的数据库编程秘笈（内含实战SQL脚本）

    【MySQL】探索MySQL存储过程的魔力，初学者的数据库编程秘笈（内含实战SQL脚本）

    🧑‍💻作者名称：DaenCode 🎤作者简介：啥技术都喜欢捣鼓捣鼓，喜欢分享技术、经验、生活。 😎人生感悟：尝尽人生百味，方知世间冷暖。 📖所属专栏：重温MySQL MySQL存储过程作为一种服务器端的 数据库编程方式 ，提供了高效、可重用的方法来执行相对复杂的数据库操

    2024年02月15日
    浏览(48)

  • sql server 截断和收缩所有用户数据库日志--用游标循环所有正常状态的用户数据库

    在服务器运维活动中，我们经常需要做一项工具就是将好多年的用户数据库日志文件截断并收缩为最小，以节省大量的磁盘空间。当数据库只有一两个时可以手动操作，但数据库数量众多时，就需要采用sql脚本，批量化执行这个过程。 本人写了一段这样的脚本。并且经过验证

    2024年02月06日
    浏览(37)
  • 【SQL Server】数据库开发指南（八）高级数据处理技术 MS-SQL 事务、异常和游标的深入研究

    【SQL Server】数据库开发指南（八）高级数据处理技术 MS-SQL 事务、异常和游标的深入研究

    本系列博文还在更新中，收录在专栏：#MS-SQL Server 专栏中。 本系列文章列表如下： 【SQL Server】 Linux 运维下对 SQL Server 进行安装、升级、回滚、卸载操作 【SQL Server】数据库开发指南（一）数据库设计的核心概念和基本步骤 【SQL Server】数据库开发指南（二）MSSQL数据库开发对

    2024年02月07日
    浏览(64)

  • 数据库: 存储过程

    sql server begin end用法: SQL Server中的BEGIN END用法是用于定义一个代码块，这个代码块可以包含多个SQL语句，BEGIN END通常用于控制流程语句，例如IF语句、WHILE语句、TRY CATCH语句等。在BEGIN END代码块中，可以使用变量、函数、存储过程等SQL Server的元素。BEGINEND的语法如下: BEGIN SQL语

    2024年02月09日
    浏览(31)
  • 【MySQL 数据库】9、存储过程

    【MySQL 数据库】9、存储过程

    🌱 存储过程是事先经过编译并存储在数据库中的 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，可以提高数据处理效率 🌱 存储过程思想上很简单：就是数据库 SQL 语言层面的代码封装与重用 🍃 【封装，复用

    2024年02月08日
    浏览(34)
  • 数据库（MySQL）的存储过程

    数据库（MySQL）的存储过程

    存储过程是事先经过编译并存储在数据库中的一段SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。 存储过程思想上很简单，就是数据库SQL 语言层面的代码封装与重用。 特点

    2024年02月10日
    浏览(33)

  • Oracle数据库创建存储过程

    下面是一个迁移数据库数据的存储过程： 存储过程中用到的 while 循环逻辑：  

    2024年02月11日
    浏览(33)
  • 数据库实验6 存储过程实验

    数据库实验6 存储过程实验

    前言：游标的mysql代码不懂写，所以没有运行结果 实验6.1 存储过程实验 1.实验目的 掌握数据库 PL/SQL 编程语言，以及数据库存储过程的设计和使用方法。 2.实验内容和要求 存储过程定义,存储过程运行,存储过程更名,存储过程删除,存储过程的参数传递。掌握PL/SQL编程语言和编

    2024年02月04日
    浏览(40)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包