Cursor in PLSQL - Best Explanation

Let’s walk through the full process of creating a table, inserting data into it, and demonstrating the use of a cursor to fetch and display that data using PL/SQL.

1. Create the Table

We'll create a table named EMPLOYEES with three columns: EMPLOYEE_ID, FIRST_NAME, and LAST_NAME.

SQL Code to Create the Table:

CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER(5),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50)
);

1. Insert Data into the Table

Now, we'll insert some sample data into the EMPLOYEES table.

SQL Code to Insert Data:

INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
VALUES (101, 'John', 'Doe');

INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
VALUES (102, 'Jane', 'Smith');

INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
VALUES (103, 'Mark', 'Taylor');

INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME)
VALUES (104, 'Lucy', 'Williams');

1. Cursor Declaration and Demonstration

Now that the EMPLOYEES table is set up and has data, we’ll declare a cursor in PL/SQL to fetch and display the data.

PL/SQL Block:

DECLARE
-- Step 1: Declare a cursor
CURSOR emp_cursor IS
SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM EMPLOYEES;

-- Step 2: Create a record variable to store fetched data
emp_record EMPLOYEES%ROWTYPE;
BEGIN
-- Step 3: Open the cursor
OPEN emp_cursor;

-- Step 4: Fetch data from the cursor
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;

  -- Step 5: Process the fetched data
  DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.EMPLOYEE_ID ||
                       ', First Name: ' || emp_record.FIRST_NAME ||
                       ', Last Name: ' || emp_record.LAST_NAME);

END LOOP;

-- Step 6: Close the cursor
CLOSE emp_cursor;
END;

Explanation of the PL/SQL Block:

1. Cursor Declaration:
   We declare a cursor emp_cursor that fetches EMPLOYEE_ID, FIRST_NAME, and LAST_NAME from the EMPLOYEES table.

2. Record Declaration:
   We declare a record variable emp_record of type EMPLOYEES%ROWTYPE, which will store the result of each fetched row.

3. Cursor Operations:

Open the cursor to start fetching data.

Fetch each row of data into emp_record and process it inside a loop. The loop exits when there are no more rows to fetch (emp_cursor%NOTFOUND).

Close the cursor after all rows are processed.

Output:

Assuming the data inserted into the EMPLOYEES table, the output will be:

Employee ID: 101, First Name: John, Last Name: Doe
Employee ID: 102, First Name: Jane, Last Name: Smith
Employee ID: 103, First Name: Mark, Last Name: Taylor
Employee ID: 104, First Name: Lucy, Last Name: Williams

Final Notes:

Creating and inserting data into the EMPLOYEES table is done using standard SQL commands.

The PL/SQL block demonstrates how to declare, open, fetch, process, and close a cursor in a loop.

DBMS_OUTPUT.PUT_LINE is used to display the data fetched from the cursor.