mysql 存储过程使用临时表 mysql存储过程cursor

MySQL Cursor在存储过程中的使用

游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

declares a cursor and associates it with a SELECT statement that retrieves the rows to be traversed by the cursor.

MySQL supports cursors inside stored programs. The syntax is as in embedded SQL. Cursors have these properties:

• Asensitive: The server may or may not make a copy of its result table
• Read only: Not updatable
• Nonscrollable: Can be traversed only in one direction and cannot skip rows

新建以下表

create table t3(
    id int not null,
    num_t int not null,
    primary key (id)
);

create table t4(
    id int not null,
    num_tt int not null,
    primary key (id)
);


insert into t3 (id,num_t) values(1,2),(3,4),(5,6),(6,2),(7,4),(8,6);
insert into t4 (id,num_tt) values(1,2),(3,4),(5,6),(9,2),(12,4),(76,6);

create table t5(
    id int not null,
    num_ttt int not null,
    primary key (id)
);

新建一个存储过程，并在存储过程中使用游标对数据集进行遍历，并操作数据，如下：

mysql> DELIMITER //
mysql>
mysql> CREATE PROCEDURE curdemo()
    -> BEGIN
    ->   DECLARE done INT DEFAULT FALSE;
    ->   DECLARE a, b, c INT;
    ->   DECLARE cur1 CURSOR FOR SELECT id,num_t FROM test.t3;
    ->   DECLARE cur2 CURSOR FOR SELECT num_tt FROM test.t4;
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    ->
    ->   OPEN cur1;
    ->   OPEN cur2;
    ->
    ->   read_loop: LOOP
    ->     FETCH cur1 INTO a, b;
    ->     FETCH cur2 INTO c;
    ->     IF done THEN
    ->       LEAVE read_loop;
    ->     END IF;
    ->     IF b < c THEN
    ->       INSERT INTO test.t5(id,num_ttt) VALUES (a,b);
    ->     ELSE
    ->       INSERT INTO test.t5(id,num_ttt) VALUES (a,c);
    ->     END IF;
    ->   END LOOP;
    ->
    ->   CLOSE cur1;
    ->   CLOSE cur2;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> CALL curdemo();
Query OK, 0 rows affected (0.55 sec)

mysql> select * from t5;
+----+---------+
| id | num_ttt |
+----+---------+
|  1 |       2 |
|  3 |       4 |
|  5 |       6 |
|  6 |       2 |
|  7 |       4 |
|  8 |       6 |
+----+---------+
6 rows in set (0.06 sec)

mysql>

通过代码，你可以看到在存储过程中通过游标进行数据集的遍历。