SQL in a Nutshell

Name

OPEN

Synopsis

The OPEN command opens a server cursor created with a DECLARE CURSOR statement. MySQL does not support ANSI-style server-side cursors.

Vendor	Command
SQL Server	Supported
MySQL	Not supported
Oracle	Supported
PostgreSQL	Supported

SQL99 Syntax and Description

OPEN { cursor_name }

The cursor_name is the name of the cursor created with the DECLARE CURSOR command.

In addition to standard server cursors, Microsoft SQL Server allows global cursors to be declared (in the format OPEN GLOBAL cursor_name) that can be referenced by multiple users. Plus, Oracle allows parameters to be passed directly into the cursor when it is opened (in the format OPEN cursor_name parameter1 [,...n] ).

Example

The following example from Microsoft SQL Server opens a cursor and fetches all the rows. The same functionality in Oracle and PostgreSQL could be accomplished without the final DEALLOCATAE clause:

DECLARE employee_cursor CURSOR FOR
  SELECT lname, fname
  FROM pubs.dbo.authors
  WHERE lname LIKE 'K%'

OPEN employee_cursor

FETCH NEXT FROM employee_cursor

WHILE @@FETCH_STATUS = 0
BEGIN
  FETCH NEXT FROM Employee_Cursor
END

CLOSE employee_cursor

DEALLOCATE employee_cursor
-- DEALLOCATE is specific to Microsoft SQL Server and non-ANSI 
-- standard.