chapter12_数据库编程_2_游标

处理多行数据的操作要使用游标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 ;