chapter12_数据库编程_2_游标

最新推荐文章于 2020-02-19 11:30:46 发布

原创最新推荐文章于 2020-02-19 11:30:46 发布 · 74 阅读

0 ·

CC 4.0 BY-SA版权

本文详细介绍了MySQL中游标的使用方法，包括声明、打开、读取数据和关闭四个基本操作。通过具体示例展示了如何利用游标进行多行数据处理，如声明游标、设置条件处理、循环读取及插入数据等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

处理多行数据的操作要使用游标cursor
游标有四个基本操作：

(1) 声明 DECLARE

(2) 打开 OPEN

(3) 读取数据 FETCH

(4) 关闭 CLOSE
声明DECLARE

(1) 语法
```
  DECLARE cursor_name CURSOR FOR select_statement
```
(2) The SELECT statement cannot have an INTO clause

(3) Cursor declarations must appear before handler declarations and after variable and condition declarations

(4) A stored program may contain multiple cursor declarations, but each cursor declared in a given block must have a unique name
打开OPEN
```
  OPEN cursor_name
```
读取数据FETCH

(1) 语法
```
  FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
```
(2) This statement fetches the next row for the SELECT statement associated with the specified cursor (which must be open), and advances the cursor pointer

(3) If a row exists, the fetched columns are stored in the named variables. The number of columns retrieved by the SELECT statement must match the number of output variables specified in the FETCH statement.

(4) If no more rows are available, a No Data condition occurs with SQLSTATE value '02000'. To detect this condition, you can set up a handler for it (or for a NOT FOUND condition)

(5) Be aware that another operation, such as a SELECT or another FETCH, may also cause the handler to execute by raising the same condition. If it is necessary to distinguish which operation raised the condition, place the operation within its own BEGIN ... END block so that it can be associated with its own handler
关闭CLOSE

(1) 语法
```
   CLOSE cursor_name
```
(2) If not closed explicitly, a cursor is closed at the end of the BEGIN ... END block in which it was declared

示例

  CREATE PROCEDURE curdemo()

  BEGIN
      DECLARE done INT DEFAULT FALSE;
      DECLARE a CHAR(16);
      DECLARE b, c INT;
      
      DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
      DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
      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.t3 VALUES (a,b);
          ELSE
              INSERT INTO test.t3 VALUES (a,c);
          END IF;
      END LOOP;

      CLOSE cur1;
      CLOSE cur2;
  END;

MYSQL使用游标示例

procedure_curdemo.sql

  USE temp;

  CREATE TABLE t1 (
      id int(11) DEFAULT NULL,
      data int(11) DEFAULT NULL
  );

  CREATE TABLE t2 (
      i int(11) DEFAULT NULL
  );

  CREATE TABLE t3 (
      i int(11) DEFAULT NULL,
      j int(11) DEFAULT NULL
  );

  DELIMITER //

  CREATE PROCEDURE curdemo()

  BEGIN
      DECLARE done INT DEFAULT FALSE;
      DECLARE a CHAR(16);
      DECLARE b, c INT;
      
      DECLARE cur1 CURSOR FOR SELECT id,data FROM temp.t1;
      DECLARE cur2 CURSOR FOR SELECT i FROM temp.t2;
      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 temp.t3 VALUES (a,b);
          ELSE
              INSERT INTO temp.t3 VALUES (a,c);
          END IF;
      END LOOP;

      CLOSE cur1;
      CLOSE cur2;
  END;//

  DELIMITER ;