Cursors

Giriş

Açıklaması şöyle

Properties of MySQL Cursors

- Non-Scrollable: You can only iterate through rows in one direction. You can't skip a row; you can't jump to a row; you can't go back to a row.

- Read-only: You can't update or delete rows using cursors.

- Asensitive: MySQL cursors point to the underlying data. It runs faster than an insensitive cursor. Insensitive cursors point to a snapshot of the underlying data, making it slower than the asenstive cursors.

Açıklaması şöyle

1. Creating a MySQL Cursor

To create a MySQL cursor, you'll need to work with the DECLARE, OPEN, FETCH, and CLOSE statements.

2. The Declare Statement

The DECLARE statement can declare variables, cursors, and handlers. There is a sequence of declarations that needs to be adhered to:

Variables

Cursors

Handlers

You must first declare at least one variable to use later with the FETCH statement later on.

DECLARE <variable_name>  <variable_type>

When declaring the cursor(s), you must attach a SELECT statement. Any valid SELECT statement will work. You also must declare at least one cursor.

DECLARE <cursor_name> CURSOR FOR <select_statement>

You also have to declare a NOT FOUND handler. When the cursor iterates and reaches the last row, it raises a condition that will be handled with the NOT FOUND handler. You can also declare other handlers depending on your needs. For example:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

3. The Open Statement

The OPEN statement initializes the result from the DECLARE cursor statement.

OPEN <cursor_name>

4. The Fetch Statement

The FETCH statement works as an iterator. It fetches the next row from the rows associated with the SELECT statement in the cursor declaration.

FETCH <cursor_name> INTO <variable_list>

The <variable_list> is one or more variables from the variables declared earlier.

FETCH <cursor_name> INTO a, b, c

If the next row exists, the variables store it; otherwise, a No Data condition with SQLSTATE of '02000' occurs. You can use the NOT FOUND handler to deal with this SQLSTATE.

5. The Close Statement

This statement closes the cursor opened in the `OPEN` statement.

CLOSE <cursor_name>

Örnek

Şöyle yaparız

DELIMITER $$ CREATE PROCEDURE cursordemo(INOUT average_goals FLOAT)

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE matches INT DEFAULT(0);
DECLARE goals INT DEFAULT(0);
DECLARE half_time_goals INT;
DECLARE team_cursor CURSOR FOR SELECT HTHG FROM epl.football WHERE (FTR = "H");
    
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN team_cursor;
teams_loop:

LOOP
  FETCH team_cursor INTO half_time_goals;
  IF done THEN LEAVE teams_loop;
  END IF;

  SET goals = goals + half_time_goals;
  SET matches = matches + 1;

END LOOP teams_loop;

SET average_goals = goals / matches;
CLOSE team_cursor;

END 
$$ DELIMITER;