Oracle PL/SQL Implicit Cursor Attributes

Implicit Cursors

PL/SQL implicitly declares a cursor for all SQL data manipulation statements on a set of   rows, including queries that return only one row

For queries that return more than one row, you can explicitly declare a cursor to process the rows individually

We can refer to the most recent implicit cursor by the name SQL

Implicit Cursors - Attributes

%FOUND Attribute: used to check whether a DML Statement has affected one or many rows

Until the DML or SELECT statement is executed, it yields NULL

Yields TRUE if the DML affects one or more rows, or a SELECT INTO statement returns one or more rows.

Otherwise yields FALSE.

%ISOPEN Attribute: always FALSE for Implicit Cursors

Yields TRUE if the cursor is open else returns FALSE. 

Oracle closes the SQL cursor automatically after executing its associated SQL statement. As a result, %ISOPEN always yields FALSE.

%NOTFOUND Attribute: used to check whether a DML statement has failed to change rows

%NOTFOUND is the logical opposite of %FOUND

Yields TRUE if the DML statement affects no rows, or a SELECT INTO statement returns no rows

Otherwise it yields FALSE

%ROWCOUNT Attribute: used to find out how many rows are affected so far

%ROWCOUNT yields the number of rows affected by the DML statement, or returned by a SELECT INTO statement

Yields 0 if the DML statement affected no rows, or a SELECT INTO statement returned no rows

Implicit Cursor: Example 1

         Using SQL%ROWCOUNT 

 DECLARE

   v_tot_rows   NUMBER (3);

BEGIN

   DELETE FROM emp

         WHERE deptno = 10;

   /* PL/SQL will use an implicit cursor to process the above statement */

   v_tot_rows := SQL%ROWCOUNT;

   /* Implicit Attribute %ROWCOUNT is used to find the number of  rows affected by the DELETE command */

   DBMS_OUTPUT.put_line('Total records deleted : ' || v_tot_rows);

END;

Implicit Cursor: Example 2

       Using SQL%FOUND

DECLARE

   v_empno   NUMBER (4) := &eno;

BEGIN

   DELETE FROM emp

         WHERE empno = v_empno;

   IF SQL%FOUND

   THEN

      /* Making use of %FOUND to find out whether at least one row was affected by the DELETE statement */

      DBMS_OUTPUT.put_line('Delete successful');

   END IF;

END;

Continue reading >>

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;

Continue reading >>

Oracle PL/SQL FOR LOOP

LOOP … END LOOP as well as WHILE LOOP … END LOOP, require explicit processing of the cursor with OPEN, FETCH and CLOSE statements

  

With CURSOR FOR LOOP, no opening, fetching, closing of Cursors is required

           Implicitly declares Record Type Variable

Syntax For Oracle PL/SQL FOR LOOP

FOR loop_counter IN [REVERSE] lowest_number..highest_numer

LOOP

   {...statements...}

END LOOP;

FOR LOOP Arguments 

loop_counter

    The loop counter variable.

REVERSE

    Optional. If specified, the loop counter will count in reverse.

lowest_number

    The starting value for loop_counter.

highest_number

    The ending value for loop_counter.

statements

    The statements of code to execute each pass through the loop.

 

Example1:

BEGIN

    FOR no IN 1 .. 3 LOOP

        DBMS_OUTPUT.PUT_LINE('Looping Count : ' || no);

      END LOOP;

END;

 

Output:

Looping Count: 1

Looping Count: 2

Looping Count: 3

 

Example2:

BEGIN

    FOR no IN REVERSE 3 .. 1 LOOP

        DBMS_OUTPUT.PUT_LINE('Looping Count: ' || no);

      END LOOP;

END;

Output

Looping Count: 3

Looping Count: 2

Looping Count: 1

 

Cursor FOR Loop Example3

DECLARE

   CURSOR emp_dept_cur

   IS

      SELECT empno, ename, sal, dept.deptno AS dno, dept.dname

        FROM emp, dept

       WHERE emp.deptno = dept.deptno;

BEGIN

   FOR v_rec IN emp_dept_cur

/* The loop variable v_rec is automatically declared and is allocated the same data type as the ROWTYPE of the cursor */

   LOOP

      DBMS_OUTPUT.put_line('Employee Number :' || v_rec.empno);

      DBMS_OUTPUT.put_line('Employee Name :' || v_rec.ename);

      DBMS_OUTPUT.put_line('Employee Salary    :' || v_rec.esal);

      DBMS_OUTPUT.put_line('Employee Deptno   :' || v_rec.dno);

      DBMS_OUTPUT.put_line('Emp Dept Name :' || v_rec.dname);

   END LOOP;

END;

Using INSERT inside the FOR Loop Example4:

CREATE TABLE mybonus(

  myeno NUMBER(4),

  bonusamt NUMBER(5)

);

 /

DECLARE

   bonus   REAL;

   CURSOR empcur

   IS

      SELECT empno, sal, comm

        FROM emp;

BEGIN

   FOR emprec IN empcur

   LOOP

      bonus := (emprec.sal * 0.05) + (emprec.comm * 0.25);

      INSERT INTO mybonus

           VALUES (emprec.empno, bonus);

   END LOOP;

   COMMIT;

END;

Continue reading >>