MySQL: OPEN Statement

This tutorial explains how to use the OPEN statement to open a cursor in MySQL with syntax and examples.

Description

Once you've declared your cursor in MySQL, the next step is to use the OPEN statement to open the cursor.

Syntax

The syntax to open a cursor using the OPEN statement in MySQL is:

OPEN cursor_name;

Parameters or Arguments

cursor_name

The name of the cursor that you wish to open.

Example

For example, you could open a cursor called c1 in MySQL with the following command:

OPEN c1;

Below is a function that demonstrates how to open a cursor.

DELIMITER //

CREATE FUNCTION FindSiteID ( name_in VARCHAR(50) )
RETURNS INT

BEGIN

   DECLARE done INT DEFAULT FALSE;
   DECLARE siteID INT DEFAULT 0;

   DECLARE c1 CURSOR FOR
     SELECT site_id
     FROM sites
     WHERE site_name = name_in;

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

   OPEN c1;
   FETCH c1 INTO siteID;

   CLOSE c1;

   RETURN siteID;

END; //

DELIMITER ;