Adsense Ad

Thursday 30 March 2017

PL/SQL: Implicit Cursor & Explicit Cursor


Implicit cursors

Implicit cursors are automatically created and used by Oracle every time you issue a Select statement in PL/SQL. If you use an implicit cursor, Oracle will perform the open, fetches, and close for you automatically. Implicit cursors are used in statements that return only one row. If the SQL statement returns more than one row, an error will occur.

The Oracle server implicitly opens a cursor to process each SQL statement not associated with an explicitly declared cursor. PL/SQL allows you to refer to the most recent implicit cursor as the SQL cursor.

For a long time there have been debates over the relative merits of implicit cursors and explicit cursors. The short answer is that implicit cursors are faster and result in much neater code so there are very few cases where you need to resort to explicit cursor.


The process of an implicit cursor is as follows:


  1. Whenever an SQL statement is executed, any given PL/SQL block issues an implicit cursor, as long as an explicit cursor does not exist for that SQL statement.
  2. A cursor is automatically associated with every DML statement (UPDATE, DELETE, and INSERT).
  3. All UPDATE and DELETE statements have cursors those recognize the set of rows that will be affected by the operation.
  4. An INSERT statement requires a place to accept the data that is to be inserted in the database; the implicit cursor fulfills this need.
  5. The most recently opened cursor is called the “SQL%” Cursor.

The implicit cursor is used to process INSERT, UPDATE, DELETE, and SELECT INTO statements. Oracle automatically performs the OPEN, FETCH, and CLOSE operations, during the processing of an implicit cursor.

Example 1 of an Implicit cursors

In the following PL/SQL code block, the select statement makes use of an implicit cursor:

Begin
Update emp Where 1=2;
Dbms_output.put_line (sql%rowcount ||’ ‘|| ‘ rows are affected by the update statement’);
End;
SELECT SUM (sal) INTO TOTAL
FROM emp
WHERE depno = 10;

Another Example of an Implicit cursor

The following single-row query calculates and returns the total salary for a department. PL/SQL creates an implicit cursor for this statement:

SELECT SUM (salary) INTO department_total
FROM employee
WHERE department_number = 10;


PL/SQL provides some attributes, which allow you to evaluate what happened when the implicit cursor was last used. You can use these attributes in PL/SQL statements like functions but you cannot use then within SQL statements.

%ROWCOUNTThe number of rows processed by a SQL statement.
%FOUNDTRUE if at least one row was processed.
%NOTFOUNDTRUE if no rows were processed.
%ISOPENTRUE if cursor is open or FALSE if cursor has not been opened or has been closed. Only used with explicit cursors.


An Example of PL/SQL Attribute

DECLARE
rows_deleted NUMBER;
BEGIN
DELETE * FROM emp;
rows_deleted := SQL%ROWCOUNT;
END;


The implicit cursor has the following drawbacks:
  • It is less efficient than an explicit cursor.
  • It is more vulnerable to data errors.
  • It gives you less programmatic control.


Explicit Cursors

Programmers create explicit cursors, and with these you can do operations on a set of rows, which can be processed one by one. You use explicit cursors when you are sure that the SQL statement will return more than one row. You have to declare an explicit cursor in the declare section at the beginning of the PL/SQL block.


Use explicit cursors to individually process each row returned by a multiple-row SELECT statement.

Explicit cursor functions:
  • Can process beyond the first row returned by the query, row by row
  • Keep track of which row is currently being processed
  • Allow the programmer to manually control explicit cursors in the PL/SQL block
Once you declare your cursor, the explicit cursor will go through these steps:

Declare: This clause initializes the cursor into memory.
Open: The previously declared cursor is now open and memory is allotted.
Fetch: The previously declared and opened cursor can now access data;
Close: The previously declared, opened, and fetched cursor is closed, which also releases memory allocation.

Below is a small example of an Explicit cursor:

SQL> set serveroutput on;
SQL> Declare
2 Cursor empcursor(empn in number)
3 Is select * from emp where empno=empn;
4
5 empvar emp%rowtype;
6 Begin
7 Dbms_output.put_line('Getting records for employee no. 7521');
8 Open empcursor(7521);
9 Loop
10 Fetch empcursor into empvar;
11 Exit when empcursor%notfound;
12 Dbms_output.put_line('emp name : ' || empvar.ename);
13 Dbms_output.put_line('emp salary : ' || empvar.sal);
14 End loop;
15 Close empcursor;
16 End;
17 /
Getting records for employee no. 7521
emp name : WARD
emp salary : 1250

PL/SQL procedure successfully completed.


Explicit Cursor Attributes

Here are the main cursor attributes:

%ISOPENIt returns TRUE if cursor is open, and FALSE if it is not.
%FOUNDIt returns TRUE if the previous FETCH returned a row and FALSE if it did not.
%NOTFOUNDIt returns TRUE if the previous FETCH did not return a row and FALSE if it did.
%ROWCOUNTIt gives you the number of rows the cursor fetched so far.


Few more examples of Explicit Cursors:

Example 1 of an Explicit Cursor:

An example to retrieve the first 10 employees one by one.

SET SERVEROUTPUT ON
DECLARE
v_empno employees.employee_id%TYPE;
v_ename employees.last_name%TYPE;
CURSOR emp_cursor IS
SELECT employee_id, last_name
FROM employees;
BEGIN
OPEN emp_cursor;
FOR i IN 1..10 LOOP
FETCH emp_cursor INTO v_empno, v_ename;
DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_empno)
||' '|| v_ename);
END LOOP;
Close emp_cursor
END ;

Example 2 of an Explicit Cursor:

DECLARE

CURSOR csr_ac (p_name VARCHAR2) IS
SELECT empno, name, sal
FROM employee
WHERE name LIKE '%p_name%';

BEGIN

FOR rec_ac IN csr_ac ('LE')
LOOP
DBMS_OUTPUT.PUT_LINE(rec_ac.empno || ' ' ||rec_ac.name || ' '||v_sal);
END LOOP ;

CLOSE csr_ac;

END;

Example 3 of an Explicit Cursor:

Another way of writing the above code, is to use the basic loop and the SQL%NOTFOUND cursor, as shown in the following.

DECLARE
CURSOR csr_ac (p_name VARCHAR2) IS
SELECT empno, ename, sal
FROM emp

WHERE ename LIKE '%SMITH%';

v_a emp.empno%TYPE;
v_b emp.ename%TYPE;
v_c emp.sal%TYPE;

BEGIN
OPEN csr_ac('');
LOOP
FETCH csr_ac INTO v_a, v_b, v_c;
EXIT WHEN csr_ac%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_a || ' ' || v_b || ' '||v_c);

END LOOP;
CLOSE csr_ac;
END;


Implicit vs. Explicit Cursors

For a long time there have been debates over the relative merits of implicit and explicit cursors.  The short answer is that implicit cursors are faster and result in much neater code so there are very few cases where you need to resort to explicit cursors.
The cursor_comparison.sql script creates a procedure that compares the performance difference between the two approaches by performing multiple queries against the dual table.

cursor_comparison.sql
CREATE OR REPLACE PROCEDURE cursor_comparison AS
  l_loops  NUMBER := 10000;
  l_dummy  dual.dummy%TYPE;
  l_start  NUMBER;
  CURSOR c_dual IS
    SELECT dummy
    FROM dual;
BEGIN
  -- Time explicit cursor.
  l_start := DBMS_UTILITY.get_time;
  FOR i IN 1 .. l_loops LOOP
    OPEN  c_dual;
    FETCH c_dual
    INTO  l_dummy;
    CLOSE c_dual;
  END LOOP;
  DBMS_OUTPUT.put_line('Explicit: ' ||
                       (DBMS_UTILITY.get_time - l_start));
  -- Time implicit cursor.
  l_start := DBMS_UTILITY.get_time;
  FOR i IN 1 .. l_loops LOOP
    SELECT dummy
    INTO   l_dummy
    FROM   dual;
  END LOOP;
  DBMS_OUTPUT.put_line('Implicit: ' ||
                       (DBMS_UTILITY.get_time - l_start));
END cursor_comparison;
/
SHOW ERRORS

The output from the procedure clearly demonstrates that implicit cursors are faster than explicit cursors.

SQL> SET SERVEROUTPUT ON
SQL> EXEC cursor_comparison;
Explicit: 203
Implicit: 162
PL/SQL procedure successfully completed.

The interesting thing is that the implicit cursor is not only faster, but it is actually doing more work, since it includes a NO_DATA_FOUND and a TOO_MANY_ROWS exception check.  To make them equivalent we should actually code the explicit cursor like that shown in the true_cursor_comparison.sql script.

true_cursor_comparison.sql
CREATE OR REPLACE PROCEDURE true_cursor_comparison AS
  l_loops  NUMBER := 10000;
  l_dummy  dual.dummy%TYPE;
  l_start  NUMBER;
  CURSOR c_dual IS
    SELECT dummy
    FROM dual;
BEGIN
  -- Time explicit cursor.
  l_start := DBMS_UTILITY.get_time;
  FOR i IN 1 .. l_loops LOOP
    OPEN  c_dual;
    FETCH c_dual
    INTO  l_dummy;
    IF (c_dual%NOTFOUND) THEN
      RAISE NO_DATA_FOUND;
    END IF; 
    FETCH c_dual
    INTO l_dummy;
    IF (c_dual%FOUND) THEN
      RAISE TOO_MANY_ROWS;
    END IF;
    CLOSE c_dual;
  END LOOP;
  DBMS_OUTPUT.put_line('Explicit: ' ||
                       (DBMS_UTILITY.get_time - l_start));
  -- Time implicit cursor.
  l_start := DBMS_UTILITY.get_time;
  FOR i IN 1 .. l_loops LOOP
    SELECT dummy
    INTO   l_dummy
    FROM   dual;
  END LOOP;
  DBMS_OUTPUT.put_line('Implicit: ' ||
                       (DBMS_UTILITY.get_time - l_start));
END true_cursor_comparison;
/
SHOW ERRORS

The output from this procedure shows an even greater speed discrepancy.

SQL> SET SERVEROUTPUT ON
SQL> EXEC true_cursor_comparison;
Explicit: 264
Implicit: 162
PL/SQL procedure successfully completed.

Since both the cursors are now doing the same amount of work why is there a speed difference?  The answer is simply the volume of code being used.  PL/SQL is an interpreted language so every extra line of code adds to the total processing time.  As a rule of thumb, make the code as compact as possible without making it unsupportable.

One may then ask if native compilation would remove this discrepancy.  That question can be answered very easily.

SQL> ALTER SESSION SET plsql_compiler_flags = 'NATIVE';
Session altered.
SQL> ALTER PROCEDURE true_cursor_comparison COMPILE;
Procedure altered.
SQL> ALTER SESSION SET plsql_compiler_flags = 'INTERPRETED';
Session altered.
SQL> SET SERVEROUTPUT ON
SQL> EXEC true_cursor_comparison;
Explicit: 263
Implicit: 160
PL/SQL procedure successfully completed.

Native compilation will be dealt with in more depth. This shows that there is still a speed difference between the two cursor types, so even when natively compiled the rule of "less code is faster" still holds true.  In the next section will explore the impact of placing blocks of code within loop structures
By at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)