Cursor c динамическим sql, как сделать??? на Муркоде

Akina

Дата: 10.01.2013 13:49:19

Вот тебе пример. Разбирайся, всё элементарно.

mysql> CREATE TABLE t1 (id INT,data INT);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1(id, data) VALUES (2,6), (4,9);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2 (id INT,data INT);
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE VIEW v1 AS SELECT id, data FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER %%
mysql>
mysql> CREATE PROCEDURE dynamic_cursor()
    -> BEGIN
    ->   DECLARE done INT DEFAULT 0;
    ->   DECLARE a,b INT;
    ->   DECLARE dyn_cur CURSOR FOR SELECT id,data FROM v1;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    ->
    ->   DROP VIEW v1;
    ->   CREATE VIEW v1 AS SELECT id+1 id, data-1 data FROM t1;
    ->
    ->   OPEN dyn_cur;
    ->
    ->   REPEAT
    ->     FETCH dyn_cur INTO a, b;
    ->     IF NOT done THEN
    ->        IF b < a THEN
    ->           INSERT INTO t2 (id,data) VALUES (a,b);
    ->        ELSE
    ->           INSERT INTO t2 (id,data) VALUES (b,a);
    ->        END IF;
    ->     END IF;
    ->   UNTIL done END REPEAT;
    ->
    ->   CLOSE dyn_cur;
    -> END%%
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> SELECT * FROM t1; SELECT * FROM t2;
+------+------+
| id   | data |
+------+------+
|    2 |    6 |
|    4 |    9 |
+------+------+
2 rows in set (0.00 sec)

Empty set (0.00 sec)

mysql> CALL dynamic_cursor();
Query OK, 0 rows affected, 1 warning (0.13 sec)

mysql> SELECT * FROM t1; SELECT * FROM t2;
+------+------+
| id   | data |
+------+------+
|    2 |    6 |
|    4 |    9 |
+------+------+
2 rows in set (0.00 sec)

+------+------+
| id   | data |
+------+------+
|    5 |    3 |
|    8 |    5 |
+------+------+
2 rows in set (0.00 sec)