Question

Write step by step syntax to create, open and close cursor in PL/SQL.

Answer 1

Of course. Here is a step-by-step guide to the syntax for creating, opening, fetching from, and closing a cursor in PL/SQL, complete with a full, practical example.

---

Introduction: The Cursor Lifecycle

Working with an explicit cursor in PL/SQL follows a clear, four-step lifecycle. You must perform these steps in order to correctly process data row-by-row.

1. DECLARE (Create) the cursor.
2. OPEN the cursor to execute its query.
3. FETCH data from the cursor, row by row.
4. CLOSE the cursor to release resources.

---

Step 1: DECLARE (Create) the Cursor

This is the creation step. You give the cursor a name and associate it with a SELECT statement. This step takes place in the DECLARE section of a PL/SQL block.

• Purpose: To define the cursor and the result set it will work with.
• Syntax:
  `sql
  CURSOR cursor_name IS

  SELECT column1, column2, ...
  FROM table_name
  WHERE conditions;
  

  `

• Example:
  Let's declare a cursor to retrieve the names of all employees in department 50.
  `sql
  DECLARE

  CURSOR c_employees IS
      SELECT first_name, last_name
      FROM employees
      WHERE department_id = 50;
  

  `

---

Step 2: OPEN the Cursor

This step executes the SELECT statement associated with the cursor. It identifies all the rows that match the query (the "active set") and positions a pointer just before the first row.

• Purpose: To execute the query and prepare the result set for fetching.
• Syntax:
  `sql
  OPEN cursor_name;
  `
• Example:
  We open the c_employees cursor we just declared.
  `sql
  BEGIN

  OPEN c_employees;
  

  `

---

Step 3: FETCH Data from the Cursor

This is the most important step, where you retrieve data one row at a time. The FETCH statement reads the data from the current row into PL/SQL variables and then advances the cursor's pointer to the next row. This is almost always done inside a loop.

• Purpose: To retrieve the current row's data and move the pointer to the next row.

• Syntax:
  `sql
  FETCH cursor_name INTO variable1, variable2, ...;
  `
  Note: The number and data types of the variables must match the columns in the cursor's SELECT statement.

• Example:
  We fetch the employee data into two local variables, v_first_name and v_last_name. We use a loop to process all the rows.
  `sql
  LOOP

  -- Fetch the data into variables
  FETCH c_employees INTO v_first_name, v_last_name;
  
  -- Check if the last fetch returned a row. If not, exit the loop.
  EXIT WHEN c_employees%NOTFOUND;
  
  -- (Here you would process the data, e.g., print it)
  DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name);
  

  END LOOP;
  `
  The c_employees%NOTFOUND is a special cursor attribute that becomes TRUE when the last FETCH statement failed to find a row.

---

Step 4: CLOSE the Cursor

This final step releases the memory and resources used by the cursor's active set. It is good practice to always close a cursor once you are finished with it.

• Purpose: To deactivate the cursor and free up system resources.
• Syntax:
  `sql
  CLOSE cursor_name;
  `
• Example:
  After our loop is finished, we close the cursor.
  `sql
  CLOSE c_employees;
  `

---

Putting It All Together: A Complete Example

Here is a complete, executable PL/SQL block that demonstrates the full lifecycle.

`sql
-- Enable server output to see the results
SET SERVEROUTPUT ON;

-- The DECLARE section where we create variables and the cursor
DECLARE

-- Variables to hold the data fetched from the cursor.
-- Using %TYPE is a good practice to match the table's column types.
v_first_name employees.first_name%TYPE;
v_last_name  employees.last_name%TYPE;

-- Step 1: DECLARE (Create) the cursor
CURSOR c_employees IS
    SELECT first_name, last_name
    FROM employees
    WHERE department_id = 50;

-- The BEGIN section where we execute our logic
BEGIN

-- Step 2: OPEN the cursor
OPEN c_employees;

-- Start a loop to process each row
LOOP
    -- Step 3: FETCH the data from the cursor into our variables
    FETCH c_employees INTO v_first_name, v_last_name;

    -- Exit the loop if there are no more rows to fetch
    EXIT WHEN c_employees%NOTFOUND;

    -- Process the fetched data (in this case, just print it)
    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_first_name || ' ' || v_last_name);

END LOOP;

-- Step 4: CLOSE the cursor to release resources
CLOSE c_employees;

EXCEPTION

WHEN OTHERS THEN
    -- It's good practice to ensure the cursor is closed even if an error occurs
    IF c_employees%ISOPEN THEN
        CLOSE c_employees;
    END IF;
    DBMS_OUTPUT.PUT_LINE('An error occurred.');

END;
/
`