存储过程，我一直比较反感这个，所以能不用尽量不会去用，但是最近遇到一个问题就是有个查询由于过于复杂，在数据量很大时候处理速度非常缓慢，甚至卡死。于是开始写存储过程，还是mysql的。话说mysql的存储过程只在学校写过，以后都没写，于是开始写，但是发现mysql竟然不支持动态游标。

win下面是5.1，linux也是5.1具体小版本是不是一样既不太清楚了，从语法上来讲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驱动的话，貌似不太现实，于是考虑其它方式。我总结一下解决这个问题有三种途径。

一、避免使用动态游标的可能性。首先使用所谓动态就是一段代码可复用与多种情况，于是每种情况都写一种代码就可避免，但是作为开发人员，基本没人会这样做。
二、拆分存储过程。假设存在表结构如下：

1
2
3
4
5
6
7
8
9
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)

鉴于编辑器字体和实际网页字体存在差异，所以上图。

表结构非常简单，id自增长主键，name，parent_id，作用是构建一棵树结构即parent_id指向另外一条记录的id形成一个从属关系。初始化数据如下。

根据id和parent_id的关系判断从属结构，当parent_id为0表示跟节点，于是这里有三棵树(这里讨论树结构只有两级节点)。现在的需求是删除前N棵树。于是可以看出，删除操作是一个对集合的操作，就需要游标，并且集合元素还是不确定。现在的问题就是将不确定值传给游标，简单的游标定义如下DECLARE cur CURSOR for SELECT * FROM TABLE。这里看到好多人说法是如果在for语句后面使用内置方法方法的话，方法参数可以是你传入的值，也就是说后面的SQL语句是支持方法调用的。没测试，那天测试了再补充吧。就拿后面的 select 语句来说这个语句里面都是确定的值，但是有一个我们可以控制的参数就是表名。表这里可以看作是一个临时的数据集合，如果我们可以控制里面的值，在游标OPEN之后读这个”动态的集”，于是实现这个有临时表和视图两种途径。在这里我选择视图。现在创建三个PROCEDURE：

1
2
3
4
5
6
7
8
9
10
11
12
13
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就足够了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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两个分别执行就可已，当然为了对外的友好，可以再写一个进行统一调用：

1
2
3
4
5
6
7
8
REATE DEFINER=`root`@`localhost` PROCEDURE `tree_test_query`(IN top int)

BEGIN 

    call proc1(top);
    call proc2();

END

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

当然这里为了测试方便使用select代替delete操作。以便更直观的看出结果。使用select替换的情况下的执行结果：

看结果就很直观，由于数据库默认升序所以视图里面的ID可以保证是前top。实际SQL如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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

就是这样，通过一个临时表或者视图间接的实现所谓的动态游标，但是还是总觉得有更完美的实现方式，于是我在继续寻找。