chapter12_数据库编程_2_游标

• 处理多行数据的操作要使用__游标cursor__

• 游标有__四个__基本操作：

  (1) 声明 DECLARE

  (2) 打开 OPEN

  (3) 读取数据 FETCH

  (4) 关闭 CLOSE

• 声明DECLARE

  (1) 语法

    DECLARE cursor_name CURSOR FOR select_statement
  

  (2) 用于定义游标的SELECT语句不能有INTO。The SELECT statement cannot have an INTO clause

  (3) 游标定义必须在Handler之前，但是要定义在变量或条件声明之后。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) 变量数量和SELECT的列数必须对应上。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) 如果游标移动到了最后一行，那么就该结束遍历的过程了，可以用一个Handler来处理。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) 如果游标没有显式关闭，它会在定义的BRGIN…END块结束的时候自动关闭。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 ;