You can retrieve the rows into the cursor using a query and then fetch the rows one at a time from the cursor.

General steps when using a cursor with simple LOOP:

1. Declare variables of compatible types to store the column values for a row.
2. Declare the cursor with a name and the query you want to execute.
3. Open the cursor from execute block.
4. Fetch the column values for rows from the cursor one row at a time, into the variables declared in Step 1, and do any processing required.
5. Close the cursor.

Steps 3-5 are combined into one step if you are using a FOR loop.

In step 1, while declaring variables, %TYPE may be used to get the type of a column, so that your variables will automatically be of the correct type:

DECLARE

emp_id_var emp.emp_id%TYPE;

emp_name_var emp.emp_name%TYPE;

You can declare a cursor as:

CURSOR emp_cursor IS

SELECT * FROM emp;

The above query is executed when you open the cursor from the execute block (BEGIN) as:

OPEN emp_cursor;

You can fetch rows one by one within a simple LOOP and may use %NOTFOUND to exit from the loop as:

LOOP

  FETCH emp_cursor

  INTO emp_id_var, emp_name_var;

  EXIT WHEN emp_cursor%NOTFOUND;

  -- any procesing

END LOOP

You can close a cursor as:

CLOSE emp_cursor;

Complete cursor program is:

DECLARE

emp_id_var emp.emp_id%TYPE;

emp_name_var emp.emp_name%TYPE;

CURSOR emp_cursor IS

SELECT * FROM emp;

BEGIN

OPEN emp_cursor;

LOOP

  FETCH emp_cursor

  INTO emp_id_var, emp_name_var;

  EXIT WHEN emp_cursor%NOTFOUND;

  -- any procesing

END LOOP;

CLOSE emp_cursor;

END;

When using a FOR LOOP, you don’t have to explicitly open or close the cursor, nor declare variables as with simple LOOP:

DECLARE

CURSOR emp_cursor IS

SELECT * FROM emp;

BEGIN

FOR empVar IN emp_cursor LOOP

DBMS_OUTPUT.PUT_LINE(

'emp id = ' || empVar.emp_id || 'wmp name = ' || empVar.emp_name

);

END LOOP;

END;

You can also declare a REF CURSOR type and then use OPEN-FOR statement to assign the cursor to a different query every time you open:

DECLARE

  TYPE emp_cursor_type IS

    REF CURSOR RETURN emp%ROWTYPE;

  emp_cursor emp_cursor_type;

  emp_var emp%ROWTYPE;

BEGIN

  OPEN emp_cursor FOR

  SELECT * FROM emp;

  LOOP

    FETCH emp_cursor INTO emp_var;

    EXIT WHEN emp_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(

    'emp id = ' || emp_var.emp_id || 'emp name = ' || emp_var.emp_name

    );

  END LOOP;

  CLOSE emp_cursor;

END;

Below statement declares a REF CUSRSOR and returns a row containing the various columns of the emp table:

TYPE emp_cursor_type IS

    REF CURSOR RETURN emp%ROWTYPE;

After this we use this user defined type (emp_cursor_type) to define an actual object(emp_cursor).  Then we declared an object to store columns from the products table (emp_var) which will be later used in the loop to hold a row. Then, within the BEGIN block, we assign a query to the cursor and open it using the OPEN-FOR statement.  

Unconstrained Cursors

The return type for a constrained cursor must match the columns in the query that is run by the cursor. An unconstrained cursor has no return type, and can therefore run any query.

DECLARE

  TYPE u_cursor_type IS

    REF CURSOR;

  u_cursor u_cursor_type;

  emp_var emp%ROWTYPE;

  student_var student%ROWTYPE;

BEGIN

  OPEN u_cursor FOR

  SELECT * FROM emp;

  LOOP

    FETCH u_cursor INTO emp_var;

    EXIT WHEN u_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(

    'emp id = ' || emp_var.emp_id || 'emp name = ' || emp_var.emp_name

    );

  END LOOP;

 

  OPEN u_cursor FOR

  SELECT * FROM student;

  LOOP

    FETCH u_cursor INTO student_var;

    EXIT WHEN u_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(

    'student id = ' || student_var.stud_id || 'student name = ' || student_var.stud_name

    );

  END LOOP;

  CLOSE u_cursor;

END;