MYSQL存储过程、游标及嵌套游标的基本使用

拼搏现实的明天。 2023-07-04 14:11 2阅读 0赞

### 1.存储过程 ###

mysql中的存储过程，可以理解成 `C`、`JAVA`中的方法，也就是对一些逻辑操作，加以封装一层外壳。可以提高sql的复用。比如当我们需要初始化一个表的数据。下次再需要的时候，如果又一条一条的插入，会显的比较麻烦，所以我们可以对外包装一下。形成一个innserDate的存储过程。以后使用就直接调用即可。

### 2.游标 ###

游标其实就是在sql中，对多条结果集数据 进行遍历获取数据的一个机制。把结果集想象成一个数组，游标相当于数组角标，可以获取具体的数据。每当获取到一个数据之后，游标会移到未读取的数据上。**通常游标是在存储过程中使用**

### 3.存储过程的基本使用 ###

###### 3.1初始实验数据 ######

创建一个`lm_setlmt_log` 并插入一点实验数据

DROP TABLE IF EXISTS `lm_setlmt_log`;
    CREATE TABLE `lm_setlmt_log` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `loan_no` varchar(255) DEFAULT NULL,
      `LATEST_NUMBER` mediumint(9) DEFAULT NULL,
      `SETL_CREATE_DT` varchar(10) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of lm_setlmt_log
    -- ----------------------------
    INSERT INTO `lm_setlmt_log` VALUES ('1', '0909', '1', '2019-01-01');
    INSERT INTO `lm_setlmt_log` VALUES ('2', '0909', '2', '2019-02-01');
    INSERT INTO `lm_setlmt_log` VALUES ('3', '0909', '2', '2019-03-01');
    INSERT INTO `lm_setlmt_log` VALUES ('4', '0909', '3', '2019-04-01');
    INSERT INTO `lm_setlmt_log` VALUES ('5', '0909', '4', '2019-05-01');
    INSERT INTO `lm_setlmt_log` VALUES ('6', '0910', '1', '2018-02-01');
    INSERT INTO `lm_setlmt_log` VALUES ('7', '0910', '3', '2018-03-01');
    INSERT INTO `lm_setlmt_log` VALUES ('8', '0910', '2', '2018-04-01');
    INSERT INTO `lm_setlmt_log` VALUES ('9', '0910', '4', '2018-05-01');
    INSERT INTO `lm_setlmt_log` VALUES ('10', '0911', '1', '2017-02-01');
    INSERT INTO `lm_setlmt_log` VALUES ('11', '0911', '2', '2017-03-01');
    INSERT INTO `lm_setlmt_log` VALUES ('12', '0911', '3', '2017-04-01');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ_size_16_color_FFFFFF_t_70]

##### 3.2无参数的存储过程 #####

需求：将loan\_no为`0909`的数据展现出来

delimiter $$ -- 将结束符号 设置为$$ 不然过程体中的分号会被识别成结束
    drop procedure if exists one;  -- 如果存在则删除
     CREATE PROCEDURE one() -- 创建一个 name 为one的存储过程
    BEGIN -- 存储过程开始标志
    SELECT * FROM lm_setlmt_log where loan_no = '0910';
    END$$ -- 代表存储过程结束
    delimiter ;-- 还原结束符为;

**调用存储过程**

> call 存储过程名；

call one();

结果如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ_size_16_color_FFFFFF_t_70 1]  
这样我们每次需要`0909`数据的时候，就可以直接调用 call one()，那么如果我们又需要调用0909的数据时候，这个存储过程变无能为力，那这个存储过程可不可以有一个入参呢。作为我们每次查询的条。------答案是当然可以

##### 3.3 有参数的存储过程 #####

> 在存储过程的 括号内用IN 表示入参 如： （IN loan varchar(20)）

delimiter $$
    drop procedure if exists onePlus;  
     CREATE PROCEDURE onePlus(IN loanno varchar(20)) -- 创建一个带有入参的存储过程
    BEGIN 
    SELECT * FROM lm_setlmt_log where loan_no = loanno;
    END$$
    delimiter ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ_size_16_color_FFFFFF_t_70 2]

#### 4.游标的基本使用 ####

**需求**：将oan\_no为`0909`的数据的 LATEST\_NUMBER拼接起来。  
**思路**：这个时候我们需要有一个循环 然后循环的过程中 将数据拼接在一起，这个时候游标就起作用了

delimiter $$
    drop procedure if exists one;  
     CREATE PROCEDURE one()
    BEGIN
    -- 声明变量
     declare done int default false;
     declare totalStr varchar(50);  
     declare str int;
     -- 创建一个 name 为cur的游标
     declare cur cursor for SELECT LATEST_NUMBER as num FROM lm_setlmt_log where loan_no = '0910' ORDER BY LATEST_NUMBER;
    -- 当游标走到末尾 也就是遍历完成之后 将标志位设置成true
     declare continue HANDLER for not found set done = true;  
     set totalStr = '';
     -- 开启游标
     open cur;
     -- 进行循环
     read_loop:loop
     -- 将游标中是数据填充到 str当中
     fetch cur into str;
     if done then -- 如果到达末尾
        leave read_loop; --跳出循环相当于 break;
     end if;
     -- 逻辑操作 拼接字符串
     set totalStr = CONCAT(totalStr,str);
     -- 结束循环
     end loop;
     -- 关闭游标
     close cur;
     -- 展示数据
     select totalStr;
    END$$
    delimiter ;

![在这里插入图片描述][20200211215030126.png]

##### 4.1嵌套游标的使用 #####

嵌套游标就是在 begin end 中再嵌套一个 begin end;

BEGIN
      declare repeatOrderFlag int default false; -- 第一种情况 1,2,3,3,4 重复排序标志位
    	declare strByNum varchar(50); -- 第二种情况 1,3,2,4 排序不正确 时 返回的num拼接数据
      declare strByDate varchar(50); -- 第二种情况 1,3,2,4 排序不正确 时 返回的num拼接数据
    	declare loanNo varchar(50);
      declare OutFlag int default false;
      declare outCur cursor for SELECT DISTINCT LOAN_NO FROM lm_setlmt_log;
      declare continue HANDLER for not found set OutFlag = true; 
      open outCur;
      read_outloop:loop
      fetch outCur into loanNo;
    	if OutFlag then
    		leave read_outloop;
     end if;
    
    -- 嵌套游标
    
    BEGIN
    ....
    ....
    END;
    
    
    end loop;
    close outCur;
    END

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200211195115596.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200211200112406.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200211201216156.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzczMjk1NQ==,size_16,color_FFFFFF,t_70
[20200211215030126.png]: https://img-blog.csdnimg.cn/20200211215030126.png