Mysql 游标使用动态变量

从语法上来讲DECLARE cur CURSOR for语句必须写在SET @sqlstr前面，这就意味着不能通过先执行一条动态语句根据结果拼凑游标的内容，而且游标在定义的时候也没有参数的概念，而是将定义的结构完全当作一个字符串直接处理，不会做任何的处理，也就是静态游标吧。关于dynamic cursor的内容官方有解释http://dev.mysql.com/doc/refman/5.6/en/connector-odbc-usagenotes-functionality.html#connector-odbc-usagenotes-functionality-dynamic-cursor我从5.1找到5.6只有这一个地方讲dynamic cursor而且内容一模一样。

“Support for the dynamic cursor is provided in Connector/ODBC 3.51, but dynamic cursors are not enabled by default. You can enable this function within Windows by selecting the Enable Dynamic Cursor check box within the ODBC Data Source Administrator.On other platforms, you can enable the dynamic cursor by adding 32 to the OPTION value when creating the DSN. ”话说这个意思是使用ODBC的话就可以经过设置之后使用dynamic cursor，关键是我有JDBC，安装程序的时候再自带一个ODBC驱动的话，貌似不太现实，于是考虑其它方式。我总结一下解决这个问题有三种途径。

一、避免使用动态游标的可能性。首先使用所谓动态就是一段代码可复用与多种情况，于是每种情况都写一种代码就可避免，但是作为开发人员，基本没人会这样做。

二、拆分存储过程。假设存在表结构如下：

mysql> desc tree_test;
+———–+————-+——+—–+———+—————-+
| Field       | Type        | Null | Key | Default | Extra          |
+———–+————-+——+—–+———+—————-+
| id             | int(11)     | NO   | PRI | NULL    | auto_increment |
| name      | varchar(11) | NO   |     |              |                |
| parent_id | int(11)     | NO   |         | NULL    |                |
+———–+————-+——+—–+———+—————-+
3 rows in set (0.00 sec)

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

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就足够了。

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc2`()
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

这个就是执行的删除操作，基本的游标循环操作，话说这写法好像很有shell和python的韵味。proc1和proc2两个分别执行就可已，当然为了对外的友好，可以再写一个进行统一调用：

REATE DEFINER=`root`@`localhost` PROCEDURE `tree_test_query`(IN top int)

BEGIN

    call proc1(top);
    call proc2();

END

三、第三种是我首先想到的，后来发现原来一般情况下会首先想到第二种，于是我觉得我确实懒的不行了。第三种方法实际就是第二种方法的偷懒方式，把所有的都放在一个存储过程中实现，实际SQL如下：

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

评论

1 楼 wuyang528378 2015-08-17  

这样的方法的却能解决动态传参的问题，但是在并发访问时会有问题，多个线程操作同一个试图肯定有问题，所以视图的名字也需要动态变化保证不重复，这样一来，试图的名字也是一个变量，问题又回到原点了。