Oracle PL/SQL Cursor Attributes

Cursor Attributes

Explicit Cursor: Example -1

DECLARE

   -- Step 1: Cursor declaration

   CURSOR empcur

   IS

      SELECT empno, ename

        FROM emp;

   v_empno   emp.empno%TYPE;

   v_name    emp.ename%TYPE;

BEGIN

   OPEN empcur;                         -- Step2: Open the Cursor for a query

   LOOP

      -- Step 3: Fetch the values from table results into PL/SQL variables.

      FETCH empcur

       INTO v_empno, v_name;

      EXIT WHEN empcur%NOTFOUND;

      DBMS_OUTPUT.put_line(v_empno || ' ' || v_name);

   END LOOP;

   CLOSE empcur;                                   -- Step 4: Close the cursor

END;

Explicit Cursor: using %TYPE and RECORD

DECLARE

   TYPE t_emprec IS RECORD (

      ename   emp.ename%TYPE,

      sal     emp.sal%TYPE,

      job     emp.job%TYPE

   );

   r_emp   t_emprec;

   CURSOR c_emp

   IS

      SELECT ename, sal, job

        FROM emp;

BEGIN

   OPEN c_emp;

   LOOP

      FETCH c_emp

       INTO r_emp;

      EXIT WHEN c_emp%NOTFOUND;

      DBMS_OUTPUT.put_line(r_emp.ename || ', ' || r_emp.sal || ', '

                            || r_emp.job

                           );

   END LOOP;

   CLOSE c_emp;

END;