MySQL Stored Procedure Cursors

First, create a cursor

Cursors are created with the Declare statement. As shown in the following example:

CREATE PROCEDURE test2 () Begin    cursortest cursor    for    selectfrom  allintersection;end;

Second, open and close cursors

• Open cursor
  

  cursortest;  

• Close Cursors
  

  cursortest;  

  Close releases all internal memory and resources used by the cursor, so it should be closed when each cursor is no longer needed. After a cursor has been closed, it cannot be used if it is not re-opened. However, the declared cursor does not need to be declared again, and opening it is possible with the open statement.

Third, using cursor data

After a cursor is opened, you can use the FETCH statement to access each of its rows separately. The FETCH statement specifies what data is retrieved (the required columns) and where the retrieved data is stored. It also moves forward the inner row pointer in the cursor so that the next FETCH statement retrieves the next row (the same row is not repeated).

CREATE PROCEDURE test3 () Begin     int;            -- Declare a local variable  declare cursorTest3        cursor-- declares a    cursor for       Select ID      from allintersection;    Open cursorTest3;           -- Open cursor    fetch cursorTest3 into o              ; -- get intersectionname    close cursorTest3  ; -- close the cursor end;

Where fetch is used to retrieve the Intersectionname column of the current row (which will automatically start with the first row) into a locally declared variable named O. Do any processing on the Retrieved data section

Four, the type example

CREATE PROCEDURE test4 () BEGIN declare done Booleandefault 0; Declare oint; --declares a local variable declare CURSORTEST4 cursor--declaring a cursor for      SelectID fromallintersection; DECLAREContinueHandler forSQLState'02000' SetDone=1;          Open cursorTest4; --Open Cursor--traverse all rows repeat fetch CURSORTEST4 into o; --get intersectionname until done end repeat; --end Loop Close cursorTest4; --close the cursor end;

MySQL Stored Procedure Cursors