储存过程中的游标使用-游标sql如何使用传入变量 - 漠_然

1.游标直接使用入参变量不生效的情况

1.1 作为表名和变量名不生效，不支持拼接

1.2 作为条件的值可以使用

2。解决游标动态获取入参- 通过创建视图view间接实现动态读取入参

实现思路：将变量转变了固定的；

　　　 a. 将动态传入的表名，转化为固定的视图里面

　　　b. 通过统一视图*（v_customer_region_view），接收不同表明需要查询的结果字段

 1 CREATE PROCEDURE do_modify_reion_data(
 2       TableName VARCHAR(68)-- 表名
 3  )
 4   BEGIN
 5      DECLARE vStr VARCHAR(8000);
 6       DECLARE vId int(10);
 7       DECLARE done INT;
 8       DECLARE indexPage INT;
 9  
10       -- 定义游标
11       DECLARE custCursor CURSOR
12       FOR
13       SELECT id FROM v_customer_region_view;
14       -- 定义结束标记
15      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
16      -- 游标查询sql，不支持入参变量直接拼接；
17      -- 通过拼接sql，创建视图，将入参传入的表名，间接固定；
18      -- 并且将sql执行，这个用到了创建视图 v_customer_region_view ，然后将视图作为游标查询的表
19      set @sql =concat("create view v_customer_region_view as select id from ", TableName," where id > 1");    
20  
21      -- 这个地方就是将拼接sql执行
22      DROP VIEW IF EXISTS v_customer_region_view;
23      PREPARE stmt1 FROM  @sql;
24                    EXECUTE stmt1 ;
25                    DEALLOCATE PREPARE stmt1;
26      
27       SET vStr = '';
28       SET indexPage = 0;
29  
30       -- 打开custCursor游标
31       OPEN custCursor;
32  
33       -- 循环vSerialNo
34       custLoop:LOOP
35       
36       -- 取游标中的数据
37       FETCH custCursor INTO vId;
38          IF done = 1 THEN
39              LEAVE custLoop;
40          END IF;
41          -- 批量处理100条
42          IF indexPage = 0 THEN
43              SET vStr = CONCAT('(',vStr, vId );
44              SET indexPage = indexPage +1;
45          ELSE  
46              SET vStr = CONCAT(vStr,", ", vId );
47              SET indexPage = indexPage +1;
48          END IF;
49          
50          -- 100条更新一次
51          IF indexPage = 100 THEN
52              SET vStr = CONCAT(vStr,')');
53              SET @sqlstr = CONCAT(
54              -- sql语句
55              "UPDATE ",  TableName, " SET name = 'admin' WHERE id in ",vStr
56              );
57               -- 日志打印
58              insert into temp_log select concat(' ',@sqlstr);
59              PREPARE stmt FROM @sqlstr;
60              EXECUTE stmt;
61              DEALLOCATE PREPARE stmt;
62              SET  indexPage = 0;
63          END IF;
64        
65       -- 关闭游标custCursor
66      END LOOP;
67      CLOSE custCursor;
68      IF indexPage >= 1 THEN
69              SET vStr = CONCAT(vStr,')');
70  
71              SET @sqlstr = CONCAT(
72              -- sql语句
73              "UPDATE ",  TableName, " SET name = 'admin' WHERE id in ",vStr
74              );
75               -- 日志打印
76              insert into temp_log select concat('myvar is ',@sqlstr);
77  
78              PREPARE stmt FROM @sqlstr;
79              EXECUTE stmt;
80              -- 释放数据资源
81              DEALLOCATE PREPARE stmt;
82          END IF;
83          -- 执行完删除视图
84          DROP VIEW IF EXISTS v_customer_region_view;
85   END;
86   
87   -- 日志表
88   
89   DROP TABLE IF EXISTS  `temp_log`;
90  CREATE TABLE `temp_log` (
91    `desc` varchar(400) NOT NULL DEFAULT '' COMMENT '存储过程日志'
92  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='存储过程日志';
93 游标使用入参表名

两层循环：

-- 过程
 DELIMITER &&
  CREATE PROCEDURE proc_updateStatus_JD(
    TableName VARCHAR(68)-- 表名
  )
   BEGIN
 DECLARE vNum INT;
 DECLARE vcutomerId VARCHAR(40);
 DECLARE vSerialNo VARCHAR(32);
 DECLARE vStr VARCHAR(232);
 DECLARE done INT;
 DECLARE done_cust INT;
 
 -- 定义游标
 DECLARE stuCursor CURSOR
 FOR
  SELECT customer_id FROM v_customer_id_view;
 
 -- 定义cust游标
 DECLARE custCursor CURSOR
 FOR
  SELECT id FROM v_table_id_view;
 
 -- 定义结束标记
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  -- TableName 无法动态传入，通过创建视图（v_customer_id_view）过度
     set @sql =concat("create view v_customer_id_view as select customer_id from ", TableName," WHERE channel_code = 'T002000' AND relation_type = '8888' AND tel_sign = 'T01' GROUP BY customer_id HAVING COUNT(1) > 1");
  -- insert into temp_log select concat( @sql);
      -- 这个地方就是将拼接sql执行
      DROP VIEW IF EXISTS v_customer_id_view;
      PREPARE stmt1 FROM  @sql;
                    EXECUTE stmt1 ;
                    DEALLOCATE PREPARE stmt1;
 
 -- 打开游标
 OPEN stuCursor;
 
 -- 循环vcutomerId
 stuLoop:LOOP
 -- 取游标中的数据
 FETCH stuCursor INTO vcutomerId;
 IF done = 1 THEN
 LEAVE stuLoop;
 END IF;
 
     set @sql =concat("create view v_table_id_view as select id from ", TableName," where customer_id = '",vcutomerId,"'  ORDER BY update_time DESC");
     --insert into temp_log select concat( @sql);
 
      -- 这个地方就是将拼接sql执行
      DROP VIEW IF EXISTS v_table_id_view;
      PREPARE stmt1 FROM  @sql;
                    EXECUTE stmt1 ;
                    DEALLOCATE PREPARE stmt1;
 SET vNum = 0;
 -- 打开custCursor游标
 OPEN custCursor;
 
 -- 循环vSerialNo
 custLoop:LOOP
 -- 取游标中的数据
 FETCH custCursor INTO vSerialNo;
 IF done = 1 THEN
 LEAVE custLoop;
 END IF;
 
 IF vNum > 0 THEN
 
   set @sql =concat("update  ", TableName," set tel_sign= 'T02' where id = '",vSerialNo,"';");
     --insert into temp_log select concat( @sql);
 
      -- 这个地方就是将拼接sql执行
      DROP VIEW IF EXISTS v_table_id_view;
      PREPARE stmt1 FROM  @sql;
                    EXECUTE stmt1 ;
                    DEALLOCATE PREPARE stmt1;
 END IF;
 SET vNum = vNum+1;
 -- 关闭游标custCursor
 END LOOP;
 CLOSE custCursor;
    SET done =0;
 END LOOP stuLoop;
 -- 关闭游标
 CLOSE stuCursor;
   END
 &&
 DELIMITER ;
 
 -- 调用过程
 CALL proc_updateStatus_JD('c_tel_info_01');
双层循环

双层源码

 1 -- 过程
 2 DELIMITER &&
 3  CREATE PROCEDURE proc_updateStatus_JD(
 4    TableName VARCHAR(68)-- 表名
 5  )
 6   BEGIN
 7 DECLARE vNum INT;
 8 DECLARE vcutomerId VARCHAR(40);
 9 DECLARE vSerialNo VARCHAR(32);
10 DECLARE vStr VARCHAR(232);
11 DECLARE done INT;
12 DECLARE done_cust INT;
13 
14 -- 定义游标
15 DECLARE stuCursor CURSOR
16 FOR
17  SELECT customer_id FROM v_customer_id_view;
18 
19 -- 定义cust游标
20 DECLARE custCursor CURSOR
21 FOR
22  SELECT id FROM v_table_id_view;
23 
24 -- 定义结束标记
25 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
26  -- TableName 无法动态传入，通过创建视图（v_customer_id_view）过度
27     set @sql =concat("create view v_customer_id_view as select customer_id from ", TableName," WHERE channel_code = 'T002000' AND relation_type = '8888' AND tel_sign = 'T01' GROUP BY customer_id HAVING COUNT(1) > 1");
28  -- insert into temp_log select concat( @sql);
29      -- 这个地方就是将拼接sql执行
30      DROP VIEW IF EXISTS v_customer_id_view;
31      PREPARE stmt1 FROM  @sql;
32                    EXECUTE stmt1 ;
33                    DEALLOCATE PREPARE stmt1;
34 
35 -- 打开游标
36 OPEN stuCursor;
37 
38 -- 循环vcutomerId
39 stuLoop:LOOP
40 -- 取游标中的数据
41 FETCH stuCursor INTO vcutomerId;
42 IF done = 1 THEN
43 LEAVE stuLoop;
44 END IF;
45 
46     set @sql =concat("create view v_table_id_view as select id from ", TableName," where customer_id = '",vcutomerId,"'  ORDER BY update_time DESC");
47     --insert into temp_log select concat( @sql);
48 
49      -- 这个地方就是将拼接sql执行
50      DROP VIEW IF EXISTS v_table_id_view;
51      PREPARE stmt1 FROM  @sql;
52                    EXECUTE stmt1 ;
53                    DEALLOCATE PREPARE stmt1;
54 SET vNum = 0;
55 -- 打开custCursor游标
56 OPEN custCursor;
57 
58 -- 循环vSerialNo
59 custLoop:LOOP
60 -- 取游标中的数据
61 FETCH custCursor INTO vSerialNo;
62 IF done = 1 THEN
63 LEAVE custLoop;
64 END IF;
65 
66 IF vNum > 0 THEN
67 
68   set @sql =concat("update  ", TableName," set tel_sign= 'T02' where id = '",vSerialNo,"';");
69     --insert into temp_log select concat( @sql);
70 
71      -- 这个地方就是将拼接sql执行
72      DROP VIEW IF EXISTS v_table_id_view;
73      PREPARE stmt1 FROM  @sql;
74                    EXECUTE stmt1 ;
75                    DEALLOCATE PREPARE stmt1;
76 END IF;
77 SET vNum = vNum+1;
78 -- 关闭游标custCursor
79 END LOOP;
80 CLOSE custCursor;
81    SET done =0;
82 END LOOP stuLoop;
83 -- 关闭游标
84 CLOSE stuCursor;
85   END
86 &&
87 DELIMITER ;
88 
89 -- 调用过程
90 CALL proc_updateStatus_JD('c_tel_info_01');

双层循环

CREATE PROCEDURE do_modify_reion_data(
      TableName VARCHAR(68)-- 表名
 )
  BEGIN
     DECLARE vStr VARCHAR(8000);
      DECLARE vId int(10);
      DECLARE done INT;
      DECLARE indexPage INT;
 
      -- 定义游标
      DECLARE custCursor CURSOR
      FOR
      SELECT id FROM v_customer_region_view;
      -- 定义结束标记
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
     -- 游标查询sql，不支持入参变量直接拼接；
     -- 通过拼接sql，创建视图，将入参传入的表名，间接固定；
     -- 并且将sql执行，这个用到了创建视图 v_customer_region_view ，然后将视图作为游标查询的表
     set @sql =concat("create view v_customer_region_view as select id from ", TableName," where id > 1");    
 
     -- 这个地方就是将拼接sql执行
     DROP VIEW IF EXISTS v_customer_region_view;
     PREPARE stmt1 FROM  @sql;
                   EXECUTE stmt1 ;
                   DEALLOCATE PREPARE stmt1;
     
      SET vStr = '';
      SET indexPage = 0;
 
      -- 打开custCursor游标
      OPEN custCursor;
 
      -- 循环vSerialNo
      custLoop:LOOP
      
      -- 取游标中的数据
      FETCH custCursor INTO vId;
         IF done = 1 THEN
             LEAVE custLoop;
         END IF;
         -- 批量处理100条
         IF indexPage = 0 THEN
             SET vStr = CONCAT('(',vStr, vId );
             SET indexPage = indexPage +1;
         ELSE  
             SET vStr = CONCAT(vStr,", ", vId );
             SET indexPage = indexPage +1;
         END IF;
         
         -- 100条更新一次
         IF indexPage = 100 THEN
             SET vStr = CONCAT(vStr,')');
             SET @sqlstr = CONCAT(
             -- sql语句
             "UPDATE ",  TableName, " SET name = 'admin' WHERE id in ",vStr
             );
              -- 日志打印
             insert into temp_log select concat(' ',@sqlstr);
             PREPARE stmt FROM @sqlstr;
             EXECUTE stmt;
             DEALLOCATE PREPARE stmt;
             SET  indexPage = 0;
         END IF;
       
      -- 关闭游标custCursor
     END LOOP;
     CLOSE custCursor;
     IF indexPage >= 1 THEN
             SET vStr = CONCAT(vStr,')');
 
             SET @sqlstr = CONCAT(
             -- sql语句
             "UPDATE ",  TableName, " SET name = 'admin' WHERE id in ",vStr
             );
              -- 日志打印
             insert into temp_log select concat('myvar is ',@sqlstr);
 
             PREPARE stmt FROM @sqlstr;
             EXECUTE stmt;
             -- 释放数据资源
             DEALLOCATE PREPARE stmt;
         END IF;
         -- 执行完删除视图
         DROP VIEW IF EXISTS v_customer_region_view;
  END;
  
  -- 日志表
  
  DROP TABLE IF EXISTS  `temp_log`;
 CREATE TABLE `temp_log` (
   `desc` varchar(400) NOT NULL DEFAULT '' COMMENT '存储过程日志'
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='存储过程日志';
游标使用入参表名

源码

发表于 2022-01-14 15:19 漠_然阅读(302) 评论(0) 编辑收藏举报