SQL Reference

OPEN

OPEN is a command used in SQL to execute a previously defined cursor. The cursor must be declared and associated with a select statement before the OPEN command can be used. It sets the cursor to the first row of the result set obtained from the select statement. Once the cursor is open, the rows can be fetched one at a time or all at once. The OPEN command must always be used before fetching rows from the result set.

Example

DECLARE @CustomerCursor CURSOR
DECLARE @CustomerID int, @CustomerName nvarchar(50)

SET @CustomerCursor = CURSOR FOR
SELECT CustomerID, CustomerName FROM Customers

OPEN @CustomerCursor
FETCH NEXT FROM @CustomerCursor INTO @CustomerID, @CustomerName

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT CAST(@CustomerID AS varchar(11)) + ' - ' + @CustomerName
    FETCH NEXT FROM @CustomerCursor INTO @CustomerID, @CustomerName
END

CLOSE @CustomerCursor
DEALLOCATE @CustomerCursor

Output

1 - John Doe
2 - Jane Smith
3 - Robert Williams
4 - Linda Johnson

Explanation

The OPEN statement in SQL is used to open a cursor, which is a database object to retrieve data from a result set one row at a time. The example packages the cursor declaration, opening, fetching data, and closing into a single transaction block. Each customer from the Customers table is printed out one at a time by their ID and name.

Example

DECLARE
  CURSOR employee_cursor IS
    SELECT first_name, last_name
    FROM employees
    WHERE department_id = 10;
  employee_rec employee_cursor%ROWTYPE;
BEGIN
  OPEN employee_cursor;
  FETCH employee_cursor INTO employee_rec;
  CLOSE employee_cursor;
END;
/

Output

PL/SQL procedure successfully completed.

Explanation

In the provided SQL script, an explicit cursor employee_cursor is declared, which holds the result set from the query that extracts the first_name and last_name from the ‘employees’ table for those employees who belong to department number 10.

The OPEN command initializes the cursor for fetching and makes it point to the first row in the result set. FETCH pull a row from the result set into a record, and finally, CLOSE shuts down the cursor. The output indicates the successful execution of the PL/SQL block.