Oracle – PL-SQL – Introduction to Cursors

A cursor is basically a set of rows that we can access one at a time.

When we use the cursor, it means we have the select statement that returns the more than one row; we need to access the one row at a time.

Working of Cursors

Basically in the cursors, we retrieve or fetch the rows using SELECT statement into the cursors then retrieve or fetch the row from the cursor one by one. (i.e. fetch one row at a time)

Steps to use the Cursors

1. Declare variable to store the value when we fetch the values from the cursor
2. Declare the cursor with SELECT statement
3. Open the cursor
4. Fetch the value from cursor and store in the variable (as we declare the variable in steps 1)
5. Close the cursor

Example:

-- CREATE TABLE 

CREATE TABLE EMPLOYEE
(
    ID                NUMBER NOT NULL,
    FIRST_NAME        VARCHAR2(10 BYTE),
    LAST_NAME         VARCHAR2(10 BYTE)
);

-- INSERT SOME VALUES INTO TABLES 

INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (1,'VARINDER','SANDHU');

INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (2,'DINESH','SHARMA');

INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (3,'RANJOYT','SINGH');

INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (4,'VIKRAM','SINGH');

-- SEE THE VALUE IN THE TABLES 

SELECT * FROM EMPLOYEE;

-- CURSOR EXAMPLE 

DECLARE
        V_ID            EMPLOYEE.ID%TYPE;
        V_FIRSTNAME     EMPLOYEE.FIRST_NAME%TYPE;
        V_LASTNAME      EMPLOYEE.LAST_NAME%TYPE;

CURSOR CURSOR_EMPLOYEE IS
SELECT ID, FIRST_NAME, LAST_NAME FROM EMPLOYEE;

BEGIN

      OPEN CURSOR_EMPLOYEE;
      LOOP

      FETCH CURSOR_EMPLOYEE INTO V_ID, V_FIRSTNAME, V_LASTNAME;

      DBMS_OUTPUT.PUT_LINE(V_ID);
      DBMS_OUTPUT.PUT_LINE(V_FIRSTNAME);
      DBMS_OUTPUT.PUT_LINE(V_LASTNAME);

      EXIT WHEN CURSOR_EMPLOYEE%NOTFOUND;
      END LOOP;

    CLOSE CURSOR_EMPLOYEE;
 END;