MySQL存储过程之游标实战

最新推荐文章于 2023-05-11 21:50:16 发布
最新推荐文章于 2023-05-11 21:50:16 发布
阅读量462 收藏
点赞数
分类专栏： 工作记录 文章标签： mysql 存储过程
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yuanzhenwei521/article/details/73896188
版权

MySQL存储过程之游标实战

​ 博主日前在解决一个项目需求时，没有什么好的方法，于是就来学习存储过程了，之前也是接触过，奈何年少贪玩，竟是全部又还给了大学老师～苦不堪言呐～。

​ 先说一下业务需求吧，即要求查询出一套包含父子类别的数据，其结构是父类别有N条记录，并且要保证可以分页查询，与父类别对应的子类别要有N1条记录。该问题在之前的之前在度娘上面问过，然而无果。最近领导又提出了类似的需求，嗯。只能想办法了。

  1. 博主首先想到的就是left join，但是使用表连接的结果则是子类别的所以数据与父类别一直计算，这样再进行分页则获取不到正确的结果。

  2. 之后博主就在想在获取父级类别数据时，就获取N1条子类数据，之后对父级别数据进行排序。可是如果是这样的话，那就只能在业务中进行循环遍历，然后在循环中请求子级数据。很显然，一定不能这么做。对系统的性能影响太大了。

  3. 那怎么办呢，博主还想了一次性将所有数据全部查出来，在业务中进行数据的处理，当然这也不是一个高效的方法。

  4. 所以，博主想到了存储过程。

    存储过程

    ​ 什么是存储过程？百科说存储过程是一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。意思就是这是存储过程是在数据库先进行过处理（编译）的SQL语句集，执行效率要比T-SQL语句要高，所以在程序中，如果使用了存储过程在一定程度上能降低网络通信量，提高通信效率等，说白了，就是在数据库服务器上预先处理的一些语句，在调用时不用现编译，能提供系统效率。反正是一个好东西～

    ​ 那么该怎么用呢。其实博主也没有深究，只是为了解决这个问题而寻找的方案。再看看问题，理清一下思路：

    1.要查询父级列表。

    2.要将父级列表对应的子级列表中的N1条数据查询出来。

    3.父级列表要提供分页查询功能。

    好了，那我就可以先查询出父类结果集，然后在结果集中获取N1条对应的子级列表（类似方法2，只不过是在数据库服务器上进行的）。然后将查询中的结果集返回给客户端。这里就直接在写好的存储过程中进行注释式说明吧。

    
#删除已存在存储过程 selectWorkCase

DROP PROCEDURE IF EXISTS selectWorkCase;

#删除已存在的临时表 workcasetable

DROP TEMPORARY TABLE IF EXISTS workcasetable;

#定义结束标识符为两个";" 即输入两个;;才算结束

delimiter ;;

#创建一个存储过程 selectWorkCase,入参个数：4个，类型有序，分别为：int,varchar,int,int(自行对应业务场景)

CREATE PROCEDURE selectWorkCase (
    IN categoryId INT,
    IN categoryType VARCHAR (40),
    IN startPos INT,
    IN endPos INT
)

#开始实现存储过程，必须写(好比声明一个方法，实现该方法一样)

BEGIN
    #以下内容与业务有关，故不做过多解释，
    #定义一个变量，用于子查询
    DECLARE parentId INT ; 
    DECLARE parentType VARCHAR (40) ; 
    DECLARE parentName VARCHAR (40) ; 
    DECLARE parentImg VARCHAR (4000) ;
    DECLARE parentLevel int;
    #声明一个计数器变量
    DECLARE countNum int;  
    #声明一个变量，用于处理游标到达最后一行的情况
    DECLARE s INT DEFAULT 0 ; 
    #声明游标parentResult(parentResult是个多行结果集)其结果为sql语句(for 之后的语句)查询结果
    #其中使用了入参的参数(形参)
    DECLARE parentResult CURSOR FOR SELECT category_id, category_type, category_name,category_level,category_cover_img FROM t_category WHERE category_type = categoryType AND category_level = 1 AND parent_id = categoryId LIMIT startPos,endPos; 
    #设置一个终止标记，当发生下述异常之一时，设置s的值为1(但实际上博主没有这样用- -！！！)
    # SELECT INTO 语句或 INSERT 语句的子查询的结果为空表。
    # 在搜索的 UPDATE 或 DELETE 语句内标识的行数为零。
    # 在 FETCH 语句中引用的游标位置处于结果表最后一行之后。
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = 1 ; 
    #创建一个临时表,临时表数据结构为关系结构
    CREATE TEMPORARY TABLE IF NOT EXISTS workcasetable (
        `f_category_id` INT (11),
        `f_category_name` VARCHAR (20),
        `f_category_type` VARCHAR (40),
        `f_category_cover_img` VARCHAR (4000),
        `f_category_level` INT(11),
        `c_category_id` INT (11),
        `c_category_name` VARCHAR (20),
        `c_category_type` VARCHAR (40),
        `c_category_cover_img` VARCHAR (4000),
        `c_category_level` INT(11),
        `first_line` VARCHAR (40),
        `second_line` VARCHAR (40),
        `third_line` VARCHAR (40)
    ) ; 
    #在使用前先清空临时表
    TRUNCATE TABLE workcasetable ;
    #查询父级此次分页的个数，并将个数赋值给countNum
    select count(c.category_id) into countNum from (SELECT category_id FROM t_category WHERE category_type = categoryType AND category_level = 1 AND parent_id = categoryId LIMIT 0,8)c;
    #打开游标
    OPEN parentResult ; 
    #获取游标当前指针的记录，读取一行数据并传给变量parentId,parentType,parentName,parentLevel,parentImg;
    FETCH parentResult INTO parentId,parentType,parentName,parentLevel,parentImg;
    #开始循环，判断是否游标已经到达了最后作为循环条件 
    WHILE s <> 1 DO
        #每次循环将countNum的值-1，就好似for循环
        set countNum = countNum - 1;
        #当条件成立，设置跳出循环参数
        IF (countNum = 0) THEN
            set s = 1;
        #与IF对应,成对出现
        END IF;
        #开始游标嵌套
        begin
            #声明变量
            DECLARE childId INT ; 
            DECLARE childType VARCHAR (40) ; 
            DECLARE childName VARCHAR (40) ; 
            DECLARE childImg VARCHAR (4000) ;
            declare firstLine varchar(40);
            declare secondLine varchar(40);
            declare thirdLine varchar(40);  
            declare childLevel int;
            #声明一个计数器
            DECLARE childCountNum int; 
            declare childStopSign int default 0;
            # 声明一个子游标，其结果为sql语句(for 之后的语句)查询结果
            declare childResult cursor for select category_id,category_type,category_name,category_level,CATEGORY_COVER_IMG,FIRST_LINE,SECOND_LINE,THIRD_LINE from t_category
                WHERE category_type = categoryType AND category_level = 2 AND parent_id = parentId LIMIT 0,8; 
            #设置结束条件，博主依旧没有使用-，-
            DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET childStopSign = 1 ; 
            #赋值给计数器
            select count(category_id) into childCountNum FROM t_category WHERE category_type = categoryType AND category_level = 2 AND parent_id = parentId LIMIT 0,8;
            #打开游标
            open childResult;
            #获取游标当前指针的记录
            FETCH childResult INTO childId,childType,childName,childLevel,childImg,firstLine,secondLine,thirdLine;
            #循环子级结果集
            while childStopSign <> 1 do
                set childCountNum = childCountNum - 1;
                if(childCountNum = 0) then 
                    set childStopSign = 1;
                end if;
                #将每条子级记录添加到临时表
                INSERT INTO workcasetable (`f_category_id`,`f_category_type`, `f_category_name`, `f_category_cover_img`,`f_category_level`,`c_category_id`, `c_category_type`,`c_category_name`,`c_category_cover_img`,`c_category_level`,`first_line`,`second_line`,`third_line`)  VALUES(parentId,parentType,parentName,parentImg,parentLevel,childId,childType,childName,childLevel,childImg,firstLine,secondLine,thirdLine);
                #读取下游标一条
                FETCH childResult INTO childId,childType,childName,childLevel,childImg,firstLine,secondLine,thirdLine;
            #成对出现
            end while;
            #关闭子游标
            close childResult;
        #结束游标嵌套块
        end;
        #读取父级下一条记录
        FETCH parentResult INTO parentId,parentType,parentName,parentLevel,parentImg;
    END WHILE ; 
    #关闭游标
    CLOSE parentResult ; 
    #从临时表中获取数据
    SELECT `f_category_id`,`f_category_type`, `f_category_name`, `f_category_cover_img`,`f_category_level`,`c_category_id`, `c_category_type`,`c_category_name`,
    `c_category_cover_img`,`c_category_level`,`first_line`,`second_line`,`third_line`
    FROM workcasetable ;

#结束存储过程

END;;

#将结束标志设置默认值

delimiter ;

    ​ 有关存储过程基本使用方法，请自行查找,本例中用到了，但是没有细说。有疑问请直接联系博主～SQLState详解

叶云轩
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL数据库实操教程(19)——详解存储过程及游标使用
谷哥的小弟
08-04 2269
在开发过程中，我们经常会遇到重复使用某一功能的情况。为此，MySQL引人了存储过程(Stored Procedure)这一技术。所谓存储过程指的就是一条或多条SQL语句的集合。存储过程可以将一些列复杂操作封装成一个代码块，以便重复使用，从而极大地减少数据库开发人的工作量提升开发效率。SQL语句需要先编译然后执行，而存储过程可将为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字通过传参的方式对其进行调用。存储过程可看做是编程的函数，它允许以传参调用的访问方式。
存储过程游标实例
yuanyuan214365的博客
01-31 368
1、游标实例 BEGIN DECLARE a VARCHAR(255); DECLARE b VARCHAR(255); DECLARE i INT DEFAULT 0; DECLARE done INT DEFAULT FALSE; DECLARE rs CURSOR FOR select domain,MIN(id) from web_url_black
数据库实验四：存储过程及游标
Moonee_
11-05 1301
关于存储过程、函数、游标的工作原理、定义及操作方法。
mysql 游标的简单_MySQL游标的简单实践
weixin_36002897的博客
02-27 113
mysql> delimiter $$mysql> create procedure number_of_players(-> 　　out pnumber int)-> begin-> 　　declare a_playerno int;-> 　　declare found bool default true;　　循环控制变量，其值为false时循环结束->...
mysql存储过程_游标_项目练习
03-22
在“mysql存储过程_游标_项目练习”中，我们可能需要完成以下任务： 1. 创建存储过程：编写SQL语句来定义一个存储过程，包括输入参数（如果需要的话）、处理逻辑以及返回值（可选）。 2. 使用游标：在存储过程中...
PostgreSQL存储过程用法实战详解
12-16
在PostgreSQL中，存储过程是一种预编译的SQL语句集合，可以用来执行复杂的数据库操作。它们提高了代码的可重用性，降低了网络流量，并且提供了更好的安全性。本文通过一个具体的例子来阐述如何创建和使用PostgreSQL...
MYSQL实战，小白找工作必备
03-20
"MYSQL实战，小白找工作必备"这个主题涵盖了数据库基础、SQL查询语言、表设计、索引优化、事务处理、存储过程、视图以及备份恢复等多个关键领域。 1. **数据库基础**：了解数据库的基本概念，如关系型数据库、实体...
微软SQL和MySQL数据库实战训练营
11-16
在微软SQL Server中，除了标准的SQL语法，还会涉及一些特有的功能，如Transact-SQL (T-SQL)，它是SQL Server的扩展，包含更多用于数据库管理和编程的元素，如存储过程、触发器和游标。而在MySQL中，学员会学习到如...
Python + MySQL 0基础从入门到精通 MySQL数据库实战精讲教程课件
最新发布
05-02
在本教程中，我们将深入探讨如何使用Python与MySQL数据库进行交互，从零开始学习MySQL数据库的基础知识，并通过实战演练提升技能。以下是一些关键的知识点： 1. **MySQL数据库入门**： - MySQL是一个开源的关系型...
数据库实验 | 第5关：使用游标的存储过程
ndzyh的博客
04-18 2635
jdxx数据表有四个字段，分别是省份(sf)、城市(cs)、区县(qxmc)、街道(name)。例如，查询天心区(qxmc)的所有字段的值结果如图所示。tjdq(in sf varchar(10)) 输入省份的名称，将该省份所有城市的所有地区的 乡x、镇z和街道jd和其他的个数插入到数据表dqtj中。 dqtj数据表已经建立在数据库中，有城市(cs)，区县名称(qxmc)、县（x)、街道（jd)、镇(z)、其他(qt)六个字段。分别存储指定省份的城市、区县名称和县、街道、镇、其他的个数。 以湖南省为参数，调
MYSQL存储过程实践以及游标的使用
capricornce的博客
07-29 420
mysql的存储过程以及游标的使用
存储过程，游标 的一个实例
iteye_16428的博客
03-26 92
  存储过程，游标 的一个实例 begin       declare stop_flag int DEFAULT 0;#声明一个标记，当游标状态为最后一条记录时，修改该变量   declare productName VARCHAR (100); declare productUrl VARCHAR (100); declare productImgUrl VARCHAR (10...
MySQL存储过程，触发器，游标实例
qq_44174346的博客
07-19 406
1.创建表。
存储过程+嵌套游标结合使用的实例
penguinDada的BLOG
10-30 1577
create or replace procedure START_ETL is cursor procs is select object_name as pn from user_procedures where object_name like 'ETL%'; proc procs%rowtype; begin for proc in procs loop execute immediate 'begin '||proc.pn||'; end;'; -- dbms_ou
深入学习MYSQL-使用存储过程（游标）
qq_43259860的博客
05-11 400
非常重要的是存储过程中局部变量必须在游标之前定义，而句柄必须在游标后定义，它指定了游标到某个状态可以做一个操作，通常用来结束循环。注：mysql 中的变量都必须以@开始，这里就是调用存储过程给这三个变量复制，后面就可以进行使用。注：当我们完成存储过程的创建后我们需要还原分割符号为;输入delimiter;综上：存储过程的功能非常的强大，灵活的应用需要在实战中不断积累心得。再写一个等价的例子，这个例子比较符合编程习惯，循环用while。再看一个例子我们用存储过程来建一个表并把现有的数据进行插入。
sqlserver存储过程简单游标示例
bcbobo21cn的专栏
01-31 1392
sqlserver存储过程游标
mysql游标的经典例子_mysql中游标的使用案例详解（学习笔记）
weixin_42638178的博客
01-19 1051
1.游标是啥玩意？简单的说：游标(cursor)就是游动的标识，啥意思呢，通俗的这么说，一条sql取出对应n条结果资源的接口/句柄，就是游标，沿着游标可以一次取出一行。我给大家准备一张图：2.怎么使用游标？//1.声明/定义一个游标declare 声明;declare 游标名 cursor for select_statement;//2.打开一个游标open 打开;open 游标名//3.取值f...
MySQL游标深度解析与实战示例
3. 游标实战案例 案例中展示了两个存储过程，一个没有使用游标，直接返回所有分类信息；另一个使用游标，将分类信息逐行取出并存储到变量中。使用游标可以方便地对数据进行处理，比如更新、插入或其他复杂的业务逻辑...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值