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.

%ROWCOUNT	The number of rows processed by a SQL statement.
%FOUND	TRUE if at least one row was processed.
%NOTFOUND	TRUE if no rows were processed.
%ISOPEN	TRUE 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:

%ISOPEN	It returns TRUE if cursor is open, and FALSE if it is not.
%FOUND	It returns TRUE if the previous FETCH returned a row and FALSE if it did not.
%NOTFOUND	It returns TRUE if the previous FETCH did not return a row and FALSE if it did.
%ROWCOUNT	It 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

Oracle SQL Joins

SQL JOINS are used to retrieve data from multiple tables. A SQL JOIN is performed whenever two or more tables are listed in a SQL statement.

There are 4 different types of SQL joins:

1. SQL INNER JOIN (sometimes called simple join)
2. SQL LEFT OUTER JOIN (sometimes called LEFT JOIN)
3. SQL RIGHT OUTER JOIN (sometimes called RIGHT JOIN)
4. SQL FULL OUTER JOIN (sometimes called FULL JOIN)

SQL INNER JOIN (simple join)

Chances are, you've already written a SQL statement that uses an SQL INNER JOIN. It is the most common type of SQL join. SQL INNER JOINS return all rows from multiple tables where the join condition is met.


Syntax

The syntax for the INNER JOIN in SQL is:

SELECT columns FROM table1 

INNER JOIN table2 

ON table1.column = table2.column;

Visual Illustration

In this visual diagram, the SQL INNER JOIN returns the shaded area:

The SQL INNER JOIN would return the records where table1 and table2 intersect.

SQL LEFT OUTER JOIN

Another type of join is called a LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
Syntax

The syntax for the LEFT OUTER JOIN in SQL is:

SELECT columns FROM table1 

LEFT [OUTER] JOIN table2 

ON table1.column = table2.column;


In some databases, the OUTER keyword is omitted and written simply as LEFT JOIN.

Visual Illustration

In this visual diagram, the SQL LEFT OUTER JOIN returns the shaded area:

The SQL LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.

SQL RIGHT OUTER JOIN

Another type of join is called a SQL RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
Syntax

The syntax for the RIGHT OUTER JOIN in SQL is:

SELECT columns FROM table1 

RIGHT [OUTER] JOIN table2 

ON table1.column = table2.column;


In some databases, the OUTER keyword is omitted and written simply as RIGHT JOIN.
Visual Illustration

In this visual diagram, the SQL RIGHT OUTER JOIN returns the shaded area:

The SQL RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2.

SQL FULL OUTER JOIN

Another type of join is called a SQL FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with NULL values in place where the join condition is not met.
Syntax

The syntax for the SQL FULL OUTER JOIN is:

SELECT columns FROM table1 

FULL [OUTER] JOIN table2 

ON table1.column = table2.column;


In some databases, the OUTER keyword is omitted and written simply as FULL JOIN.
Visual Illustration

In this visual diagram, the SQL FULL OUTER JOIN returns the shaded area:

The SQL FULL OUTER JOIN would return the all records from both table1 and table2.

Explicit vs. Implicit SQL Joins

The explicit join is easier to read and the implicit syntax is difficult to understand and more prone to errors. Moreover implicit syntax is now a day’s outdated.

SQL specifies two different syntactical ways to express joins: "explicit join notation" and "implicit join notation":

The "explicit join notation" uses the JOIN keyword to specify the table to join, and the ON keyword to specify the predicates for the join, as in the following example:

SELECT *
  FROM employee INNER JOIN department 
    ON employee.DepartmentID = department.DepartmentID;

The "implicit join notation" simply lists the tables for joining (in the FROM clause of the SELECT statement), using commas to separate them. Thus, it specifies a cross-join, and the WHERE clause may apply additional filter-predicates (which function comparably to the join-predicates in the explicit notation).

The following example shows a query which is equivalent to the one from the previous example, but this time written using the implicit join notation:

SELECT *  
FROM   employee, department 
WHERE  employee.DepartmentID = department.DepartmentID;

HOST Built-in Command in Oracle Forms

Description

• Executes an indicated operating system command.

Syntax

PROCEDURE HOST

(system_command_string VARCHAR2);

PROCEDURE HOST

(system_command_string VARCHAR2,

screen_action NUMBER);

Built-in Type unrestricted procedure

Enter Query Mode yes

Parameters

system_command_string  

       Specifies the system command you want to pass to your particular operating system.  

screen_action  

       Specifies one of the following constants:

No parameter

• Specifies that Oracle Forms will: clear the screen prompt the operator to return from the command

NO_PROMPT

• Specifies that Oracle Forms will:
• Clear the screen (does not prompt the operator to return from the command)

NO_SCREEN

Specifies that Oracle Forms will:

• Not clear the screen
• Not prompt the operator to return from the system command

(The HOST command should not send output to the screen when using the NO_SCREEN parameter.)

Usage notes

• Note that the command interpreter for Microsoft Windows 2000 and XP is cmd. Before using the HOST Built-in to run an external command, be sure to check for the operating system and pass the appropriate command string.

• The host command operates on the application server machine. Any screen output that it performs is not visible to the user of the application.

HOST Examples

/*** built-in: HOST**

Example: Execute an operating system command in a ** subprocess or subshell.

Uses the ** 'Get_Connect_Info' procedure from the **

GET_APPLICATION_PROPERTY example. */

PROCEDURE Mail_Warning( send_to VARCHAR2) IS

the_username VARCHAR2(40);

the_password VARCHAR2(40);

the_connect VARCHAR2(40);

the_command VARCHAR2(2000);

BEGIN

      /* ** Get Username, Password, Connect information */

      Get_Connect_Info(the_username,the_password,the_connect);

      /* ** Concatenate together the static text and values of **

      local variables to prepare the operating system command ** string. */

      the_command := 'orasend '|| ' to='||send_to|| ' std_warn.txt '|| ' subject="## LATE PAYMENT ##"'||

       ' user='||the_username|| ' password='||the_password|| ' connect='||the_connect; Message('Sending Message...', NO_ACKNOWLEDGE);

       Synchronize;

       /* ** Execute the command string as an O/S command The ** NO_SCREEN option tells forms not to clear the screen *

       * while we do our work at the O/S level "silently". */

       Host( the_command, NO_SCREEN );

       /* ** Check whether the command succeeded or not */

       IF NOT Form_Success THEN

            Message('Error -- Message not sent.');

       ELSE Message('Message Sent.');

       END IF;

END;

PL/SQL Cursors

Cursors

An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. We can provide a suitable name for the cursor.

General Syntax for creating a cursor is as given below:

CURSOR cursor_name IS select_statement;

There are four steps in using an Explicit Cursor.

• DECLARE the cursor in the declaration section.
• OPEN the cursor in the Execution Section.
• FETCH the data from cursor into PL/SQL variables or records in the Execution Section.
• CLOSE the cursor in the Execution Section before you end the PL/SQL Block.

How to access an Explicit Cursor?

These are the three steps in accessing the cursor.
1) Open the cursor.
2) Fetch the records in the cursor one at a time.
3) Close the cursor.

General Syntax to open a cursor is:

OPEN cursor_name;

General Syntax to fetch records from a cursor is:

FETCH cursor_name INTO record_name;

OR 

FETCH cursor_name INTO variable_list;

General Syntax to close a cursor is:

CLOSE cursor_name;

When a cursor is opened, the first row becomes the current row. When the data is fetched it is copied to the record or variables and the logical pointer moves to the next row and it becomes the current row. On every fetch statement, the pointer moves to the next row. If you want to fetch after the last row, the program will throw an error. When there is more than one row in a cursor we can use loops along with explicit cursor attributes to fetch all the records.

Points to remember while fetching a row:

· We can fetch the rows in a cursor to a PL/SQL Record or a list of variables created in the PL/SQL Block.
· If you are fetching a cursor to a PL/SQL Record, the record should have the same structure as the cursor.
· If you are fetching a cursor to a list of variables, the variables should be listed in the same order in the fetch statement as the columns are present in the cursor.

General Form of using an explicit cursor is:

 DECLARE

    variables;

    records;

    create a cursor;

 BEGIN

   OPEN cursor;

   FETCH cursor;

     process the records;

   CLOSE cursor;

 END;

Explicit Cursor, Lets Look at the example below

Example 1:

DECLARE 

   emp_rec emp_tbl%rowtype;

   CURSOR emp_cur IS 

   SELECT *

   FROM 

   WHERE salary > 10; 

BEGIN 

   OPEN emp_cur; 

   FETCH emp_cur INTO emp_rec; 

      dbms_output.put_line (emp_rec.first_name || '  ' ||                   emp_rec.last_name); 

   CLOSE emp_cur; 

 END; 

What are Explicit Cursor Attributes?

Oracle provides some attributes known as Explicit Cursor Attributes to control the data processing while using cursors. We use these attributes to avoid errors while accessing cursors through OPEN, FETCH and CLOSE Statements.

When does an error occur while accessing an explicit cursor?

a) When we try to open a cursor which is not closed in the previous operation.
b) When we try to fetch a cursor after the last operation.

These are the attributes available to check the status of an explicit cursor.

Attribute	Explanation
%ISOPEN	- Returns TRUE if the cursor is open, FALSE if the cursor is closed.
%FOUND	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Returns NULL if cursor is open, but fetch has not been executed. - Returns TRUE if a successful fetch has been executed. - Returns FALSE if no row was returned.
%NOTFOUND	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Return NULL if cursor is open, but fetch has not been executed. - Returns FALSE if a successful fetch has been executed. - Returns TRUE if no row was returned.
%ROWCOUNT	- Returns INVALID_CURSOR if cursor is declared, but not open; or if cursor has been closed. - Returns the number of rows fetched. - The ROWCOUNT attribute doesn't give the real row count until you have iterated through the entire cursor. In other words, you shouldn't rely on this attribute to tell you how many rows are in a cursor after it is opened.

Using Loops with Explicit Cursors:

Oracle provides three types of cursors namely SIMPLE LOOP, WHILE LOOP and FOR LOOP. These loops can be used to process multiple rows in the cursor. Here I will modify the same example for each loops to explain how to use loops with cursors.

Cursor with a Simple Loop:

DECLARE 

  CURSOR emp_cur IS 

  SELECT first_name, last_name, salary FROM emp_tbl; 

  emp_rec emp_cur%rowtype; 

BEGIN 

  IF NOT sales_cur%ISOPEN THEN OPEN sales_cur; 

  END IF; 

  LOOP 

     FETCH emp_cur INTO emp_rec; 

     EXIT WHEN emp_cur%NOTFOUND; 

     dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name 

     || ' ' ||emp_cur.salary); 

  END LOOP; 

  END; 

Cursor with a While Loop:

DECLARE 

 CURSOR emp_cur IS 

 SELECT first_name, last_name, salary FROM emp_tbl; 

 emp_rec emp_cur%rowtype; 

BEGIN 

  IF NOT sales_cur%ISOPEN THEN OPEN sales_cur; 

  END IF; 

  FETCH sales_cur INTO sales_rec;  

  WHILE sales_cur%FOUND THEN  

  LOOP 

    dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name 

    || ' ' ||emp_cur.salary); 

    FETCH sales_cur INTO sales_rec; 

  END LOOP; 

 END; 

Cursor with a FOR Loop:

DECLARE

 CURSOR emp_cur IS

 SELECT first_name, last_name, salary FROM emp_tbl;

 emp_rec emp_cur%rowtype;

BEGIN

 FOR emp_rec in sales_cur

 LOOP 

 dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name

   || ' ' ||emp_cur.salary); 

 END LOOP;

 END;

Cursor with parameters

The basic syntax for a cursor with parameters is:

CURSOR cursor_name (parameter_list)

IS

  SELECT_statement;

For example, you could define a cursor called c2 as below.

CURSOR c2 (subject_id_in IN varchar2)

IS

  SELECT course_number

  from courses_tbl

  where subject_id = subject_id_in;

The result set of this cursor is all course_numbers whose subject_id matches the subject_id passed to the cursor via the parameter.

Cursor with return clause

The basic syntax for a cursor with a return clause is:

CURSOR cursor_name

RETURN field%ROWTYPE

IS

   SELECT_statement;

For example, you could define a cursor called c3 as below.

CURSOR c3

RETURN courses_tbl%ROWTYPE

IS

   SELECT *

   from courses_tbl

   where subject = 'Mathematics';

The result set of this cursor is all columns from the course_tbl where the subject is Mathematics.

SELECT FOR UPDATE Statement

The Select For Update statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.

The syntax for the Select For Update is:

CURSOR cursor_name

IS

   select_statement

   FOR UPDATE [of column_list] [NOWAIT];

For example, you could use the Select For Update statement as follows:

CURSOR c1

IS

  SELECT course_number, instructor

  from courses_tbl

  FOR UPDATE of instructor;

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Ofstatement.

WHERE CURRENT OF Statement

If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Ofstatement.

The syntax for the Where Current Of statement is either:

UPDATE table_name

  SET set_clause

  WHERE CURRENT OF cursor_name;

OR

DELETE FROM table_name

WHERE CURRENT OF cursor_name;

The Where Current Of statement allows you to update or delete the record that was last fetched by the cursor.

Updating using the WHERE CURRENT OF Statement

Here is an example where we are updating records using the Where Current Of Statement:

CREATE OR REPLACE Function FindCourse

   ( name_in IN varchar2 )

   RETURN number

IS

   cnumber number;

   CURSOR c1

   IS

     SELECT course_number

     from courses_tbl

     where course_name = name_in

     FOR UPDATE of instructor;

BEGIN

   open c1;

   fetch c1 into cnumber;

   if c1%notfound then

      cnumber := 9999;   else

      UPDATE courses_tbl

        SET instructor = 'SMITH'

        WHERE CURRENT OF c1; COMMIT;

   end if;   close c1; RETURN cnumber; END;

Deleting using the WHERE CURRENT OF Statement

Here is an example where we are deleting records using the Where Current Of Statement:

CREATE OR REPLACE Function FindCourse

   ( name_in IN varchar2 )

   RETURN number

IS

   cnumber number;

   CURSOR c1

   IS

     SELECT course_number

     from courses_tbl

     where course_name = name_in

     FOR UPDATE of instructor;

BEGIN

   open c1;   fetch c1 into cnumber;

   if c1%notfound then

      cnumber := 9999;   else

      DELETE FROM courses_tbl

        WHERE CURRENT OF c1;

      COMMIT;   end if;   close c1;

RETURN cnumber; END;

Cursor within a cursor

create or replace procedure MULTIPLE_CURSORS_PROC is

   v_owner varchar2(40);

   v_table_name varchar2(40);

   v_column_name varchar2(100);

      /* First cursor */

   cursor get_tables is

     select distinct tbl.owner, tbl.table_name

     from all_tables tbl

     where tbl.owner = 'SYSTEM';

        /* Second cursor */

   cursor get_columns is

     select distinct col.column_name

     from all_tab_columns col

     where col.owner = v_owner

     and col.table_name = v_table_name;

        begin

      -- Open first cursor

   open get_tables;

   loop

      fetch get_tables into v_owner, v_table_name;

            -- Open second cursor

      open get_columns;

      loop

         fetch get_columns into v_column_name;

      end loop;

            close get_columns;

         end loop;

      close get_tables;

   EXCEPTION

   WHEN OTHERS THEN

      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

end MULTIPLE_CURSORS_PROC;

declare

cursor cd is select * from dept;

cursor ce is select * from emp;

   begin

for i in cd loop

dbms_output.put_line(i.deptno||' '||i.dname);

for j in ce loop

if i.deptno = j.deptno then

dbms_output.put_line(j.ename||' '||j.job);

end if;

end loop;

end loop;

end ;

/

REF CURSOR

•          A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result.

•          To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. You can access this area through an explicit cursor, which names the work area, or through a cursor variable, which points to the work area. To create cursor variables, you define a REF CURSOR type, and then declare cursor variables of that type.

Syntax of the REF Cursor

Define a REF Cursor TYPE:

   TYPE ref_type_name IS REF CURSOR

    [RETURN {

             cursor_name%ROWTYPE           

            |ref_cursor_name%ROWTYPE

            |record_name%TYPE

            |record_type_name

            |db_table_name%ROWTYPE

            }

    ];

RETURN
specifies the data type of a cursor variable return value. You can use the %ROWTYPE attribute in the RETURN clause to provide a record type that represents a row in a database table or a row from a cursor or strongly typed cursor variable. You can use the %TYPE attribute to provide the datatype of a previously declared record.

Ø  cursor_name
An explicit cursor previously declared within the current scope.

Ø  ref_cursor_name
An ref cursor previously declared within the current scope.

Ø  record_name
A user-defined record previously declared within the current scope.

Ø  record_type_name

A user-defined record type that was defined using the data type specifies RECORD.

Ø  db_table_name
A database table or view, which must be accessible when the declaration is elaborated.

Ø  %ROWTYPEA record type that represents a row in a database table or a row fetched from a cursor or strongly typed cursor variable. Fields in the record and corresponding columns in the row have the same names and datatypes.

Ø  %TYPE
Provides the datatype of a previously declared user-defined record.

Ø  type_name
A user-defined cursor variable type that was defined as a REF CURSOR.

Cursor_variable_declaration:

    cursor_variable_name ref_type_name;

OPEN a REF cursor...

OPEN cursor_variable_name

 FOR select_statement;

/*To be sure it's not open already:*/

IF NOT cursor_variable_name%ISOPEN THEN

   OPEN cursor_variable_name FOR select_statement;

END IF;

Types of REF CURSOR

Strongly Typed: A REF CURSOR that specifies a specific return type

DECLARE

TYPE    EmpCurTyp IS REF CURSOR

RETURN  emp%ROWTYPE; -- strongly typed ref cursor

cursor1 EmpCurTyp;

BEGIN

NULL;

END;

Weakly Typed:  A REF CURSOR that does not specify the return type  

DECLARE

TYPE    EmpCurTyp IS REF CURSOR  -- Weakly typed ref cursor

cursor1 EmpCurTyp;

BEGIN

NULL;

END;

Three statements to control a cursor variable

·      OPEN-FOR

·      FETCH

·      CLOSE

1.     OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.

2.     PL/SQL makes sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement.

Simple Example:

DECLARE

 TYPE    EmpCurTyp IS REF CURSOR

 RETURN  emp%ROWTYPE; -- strong cursor

 emp1    EmpCurTyp;

 PROCEDURE process_emp_cv (emp_cv IN EmpCurTyp) IS

  person emp%ROWTYPE;

 BEGIN

     DBMS_OUTPUT.PUT_LINE('-----');

     DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');

 LOOP

     FETCH emp_cv INTO person;

     EXIT WHEN emp_cv%NOTFOUND;

     DBMS_OUTPUT.PUT_LINE('Name = ' || person.ENAME ||' ' || person.JOB);

 END LOOP;

 END;

BEGIN

   OPEN  emp1

   FOR   SELECT *

         FROM emp

         WHERE ROWNUM < 11;

         process_emp_cv(emp1);

   CLOSE emp1;

   OPEN  emp1

   FOR   SELECT *

         FROM emp

         WHERE ENAME LIKE 'R%';

         process_emp_cv(emp1);

   CLOSE emp1;

END;

Difference between REF CURSOR and CURSOR with Example:

DECLARE

   TYPE rc IS REF CURSOR;

   CURSOR c IS SELECT * FROM dual;

   l_cursor rc;

BEGIN

       IF   (to_char(SYSDATE,'dd') = 30) THEN

            OPEN l_cursor FOR SELECT * FROM emp;

                 

      ELSIF (to_char(SYSDATE,'dd') = 29) THEN

             OPEN l_cursor FOR SELECT * FROM dept;

                   

      ELSE

             OPEN l_cursor FOR SELECT * FROM dual;

      END IF;

      OPEN c;

         -----

             /* some manipulation here */

         -----

      CLOSE c;

END;

Comparisons

1.       Cursor C will always be select * from dual.   The ref cursor can be anything.

2.       Cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function).

3.       Ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.

Usage Restrictions

The following are restrictions on cursor variable usage.

1.       Comparison operators cannot be used to test cursor variables for equality, inequality, null, or not   null.

2.       Null cannot be assigned to a cursor variable.

3.       The value of a cursor variable cannot be stored in a database column.

4.       Static cursors and cursor variables are not interchangeable. For example, a static cursor cannot be used in an OPEN FOR statement.

Cursor with variable in an "IN CLAUSE"

Question: I'm trying to use a variable in an IN CLAUSE.

Assumptions & declarations

1.    Ref_cursor is of type REF CURSOR declared in Package

2.    I will to pass a comma separated Numbers as a string

3.    This should be used in the query in the IN Clause

4.    Execute the Query and Return the Output as REF Cursor

Something similar to the following:

Create or Replace Function func_name (inNumbers in Varchar2)

   Return PackageName.ref_cursor

As

   out_cursor PackageName.Ref_cursor;

Begin

   Open out_cursor

   For Select * from Table_name

   where column_name in (inNumbers);

   Return out_cursor;

End;

I seem to be getting an error when I try the code above. How can I use a variable in an IN CLAUSE?

Answer: Unfortunately, there is no easy way to use a variable in an IN CLAUSE if the variable contains a list of items. We can, however, suggest two alternative options:

Option #1

Instead of creating a string variable that contains a list of numbers, you could try storing each value in a separate variable. For example:

Create or Replace Function func_name

   Return PackageName.ref_cursor

As

   out_cursor PackageName.Ref_cursor;

   v1 varchar(2);

   v2 varchar(2);

   v3 varchar(2);

Begin

   v1 := '1';

   v2 := '2';

   v3 := '3';

   Open out_cursor

   For Select * from Table_name

   where column_name in (v1, v2, v3);

   Return out_cursor;

End;

Option #2

You could try storing your values in a table. Then use a sub-select to retrieve the values.

For example:

Create or Replace Function func_name

   Return PackageName.ref_cursor

As

   out_cursor PackageName.Ref_cursor;

Begin

   Open out_cursor

   For Select * from Table_name

   where column_name in (select values from list_table);

   Return out_cursor;

End;

In this example, we've stored our list in a table called list_table.

Oracle Bulk Collect

One method of fetching data is an Oracle bulk collect. With Oracle bulk collect, the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection.  During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine.  When rows are retrieved using Oracle bulk collect, they are retrieved with only two context switches.  The larger the number of rows you would like to collect with Oracle bulk collect, the more performance improvement you will see using an Oracle bulk collect.

Starting in Oracle10g, an Oracle bulk collect may be performed by the  the PL/SQL engine for you. The PL/SQL engine may automatically use Oracle bulk collect to collect 100 rows at a time because of a cursor loop.

This use of Oracle bulk collect allows your code to process rows without having to setup and execute the Oracle bulk collect operation.   The result of this use of Oracle bulk collect is that bulk collecting 75 rows may not provide you with much of a benefit, but using Oracle bulk collect to collect large numbers of rows (many hundreds) will provide increased performance. 

 Bulk collect is easy to use.  First, define the collection or collections that will be collected using the Oracle bulk collect.  Next, define the cursor to retrieve the data in the Oracle bulk collect. Finally, bulk collect the data into the collections. 

In database versions 9iR2 and later, you can bulk collect into records:

declare
 type sales_tab is table of sales%rowtype;
 t_sal sales_tab;
 begin
 select * bulk collect into t_sal from sales;
 dbms_output.put_line(t_sal.count);
 end;
 /

100

For Bulk Collect examples:

http://psoug.org/reference/array_processing.html

http://www.oracle-base.com/articles/9i/bulk-binds-and-record-processing-9i.php