mysql动态游标

傷城~ 2022-04-10 11:29 325阅读 0赞

### 一、游标的使用 ###

DROP PROCEDURE IF EXISTS myCURSOR;
    CREATE PROCEDURE myCURSOR ()
    BEGIN
    DECLARE user_name VARCHAR(32);
    
    
    /* 声明游标 */
    DECLARE my_cursor CURSOR FOR select nickname from users;
    
    
    /* 打开游标 */
    OPEN my_cursor;
    sale_loop: LOOP
    FETCH my_cursor INTO user_name;
    select user_name;
    END LOOP;
    -- 关闭游标
    close my_cursor;
    end
    
    
    call myCURSOR;

### 二、动态游标 ###

分析:

从语法上来讲DECLARE cur CURSOR for语句必须写在SET @sqlstr前面，这就意味着不能通过先执行一条动态语句根据结果拼凑游标的内容，而且游标在定义的时候也没有参数的概念，而是将定义的结构完全当作一个字符串直接处理，不会做任何的处理，也就是静态游标吧.

处理方式:

分析:

现在的问题就是将不确定值传给游标，简单的游标定义如下DECLARE cur CURSOR for SELECT \* FROM TABLE。这里看到好多人说法是如果在for语句后面使用内置方法方法的话，方法参数可以是你传入的值，也就是说后面的SQL语句是支持方法调用的。没测试，那天测试了再补充吧。就拿后面的 select 语句来说这个语句里面都是确定的值，但是有一个我们可以控制的参数就是表名。表这里可以看作是一个临时的数据集合，如果我们可以控制里面的值，在游标OPEN之后读这个“动态的集合”，于是实现这个有临时表和视图两种途径。在这里我选择视图。

-- 第一种方法
    CREATE DEFINER=`root`@`localhost` PROCEDURE `proc1`(IN top int)  
        BEGIN  
      
        DROP VIEW IF EXISTS tree_test_view;  
        SET @sqlstr = "CREATE VIEW tree_test_view as ";  
        SET @sqlstr = CONCAT(@sqlstr , "SELECT id FROM tree_test WHERE parent_id = 0 LIMIT ", top);  
      
        PREPARE stmt FROM @sqlstr;  
        EXECUTE stmt;  
        DEALLOCATE PREPARE stmt;  
      
    END  
    /**
    第一个功能很简单，根据条件获得一个ID集合，并把集合存在tree_test_view这个视图中，这里直接在SQL编辑器写的代码，直接运行需要添加存储过程存在判断和DELIMITER转义换行符。现在我们已经获得需要要删除的树的ID集合，因为已只有两级的数，所有只需要知道根的id就可以直接删除整棵树，如果是多级的话就需要进行递归删除，当然前提是需要知道根ID，并且只知道根ID就足够了。
    **/
    -- ====================================================================================
    BEGIN  
      
        DECLARE done INT DEFAULT 0;  
      
        DECLARE temp_id INT;  
        DECLARE cur CURSOR for( SELECT id from tree_test_view);  
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
      
        OPEN cur;  
        FETCH cur INTO temp_id;  
        WHILE done <> 1 DO  
        delete tree_test WHERE id = temp_id or parent_id = temp_id;  
        FETCH cur INTO temp_id;  
        END WHILE;  
        CLOSE cur;  
    END  
    -- ===================================================================================
    CREATE DEFINER=`root`@`localhost` PROCEDURE `tree_test_query`(IN top int)  
    BEGIN  
      
        DECLARE done INT DEFAULT 0;  
      
        DECLARE temp_id INT;  
        DECLARE cur CURSOR for( SELECT id from tree_test_view);  
      
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
      
        DROP VIEW IF EXISTS tree_test_view;  
      
        SET @sqlstr = "CREATE VIEW tree_test_view as ";  
        SET @sqlstr = CONCAT(@sqlstr , "SELECT id FROM tree_test WHERE parent_id = 0 LIMIT ", top);  
      
        PREPARE stmt FROM @sqlstr;  
        EXECUTE stmt;  
        DEALLOCATE PREPARE stmt;  
      
        OPEN cur;  
        FETCH cur INTO temp_id;  
        WHILE done <> 1 DO  
        DELETE FROM tree_test WHERE id = temp_id or parent_id = temp_id;  
        FETCH cur INTO temp_id;  
        END WHILE;  
        CLOSE cur;  
    END