Cursors--

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.

Please see this link to know if Implicit cursors are fast or Explicit cursors.

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;

RSS Feed

Oracle PL/SQL Cursor For Loops

Cursor For Loop

The following procedure is followed in most of the situations in PL/SQL:

1. Open a cursor
2. Start a loop
3. Fetch the cursor
4. Check whether rows are returned
5. Process
6. Close the loop
7. Close the cursor

Cursor For Loop allows us to simplify this procedure by letting PL/SQL do most of the things for us.

You can simplify coding by using a Cursor For Loop instead of the OPEN, FETCH, and CLOSE statements.

A Cursor For Loop implicitly declares its loop index as a record that represents a row fetched from the database.

Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and then closes the cursor when all rows have been processed.

Cursor For Loop Example

DECLARE CURSOR c1 IS
SELECT ename, sal, deptno
FROM emp;
...
BEGIN
FOR emp_rec IN c1 LOOP
...
salary_total := salary_total +
emp_rec.sal;
END LOOP;

Another example of Cursor For Loop



Let us rewrite the example (used in Cursors with Parameters) again using Cursor For Loop

Oracle PL/SQL Cursors With Parameters

Cursors With Parameters
We can pass parameters into a cursor and use them in the query.

We can only pass values to the cursor; and cannot pass values out of the cursor through parameters.

Only the datatype of the parameter is defined, not its length.

Optionally, we can also give a default value for the parameter, which will take effect if no value is passed to the cursor.

Cursors With Parameters Example

The following cursor prints department number and name in one line followed by employees working in that department (name and salary) and total salary.

DECLARE
CURSOR cur_dept IS SELECT * FROM dept ORDER BY deptno;
CURSOR cur_emp (par_dept VARCHAR2) IS
SELECT ename, salary
FROM emp
WHERE deptno = par_dept
ORDER BY ename;

r_dept DEPT%ROWTYPE;
var_ename EMP.ENAME%TYPE;
var_salary EMP.SALARY%TYPE;
var_tot_salary NUMBER (10,2);

BEGIN
OPEN cur_dept;
LOOP

FETCH cur_dept INTO r_dept;
EXIT WHEN cur_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('Department : ' || r_dept.deptno || ' - '|| r_dept.dname);
var_tot_salary := 0;
OPEN cur_emp (r_dept.deptno);
LOOP
FETCH cur_emp INTO var_ename, var_salary;
EXIT WHEN cur_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('Name: ' ||var_ename || ' Salary:'||var_salary);
var_tot_salary := var_tot_salary + var_salary;
END LOOP;
CLOSE cur_emp;
DBMS_OUTPUT.PUT_LINE ('Total Salary for Dept: ' || var_tot_salary);

END LOOP;
CLOSE cur_dept;
END;
/

Important points to keep in mind for parameters in cursors are:

• The mode of the parameters can only be IN.
• Cursor becomes more reusable with Cursor parameters.
• Default values can be assigned to Cursor parameters.
• The scope of the cursor parameters is local to the cursor.

REF Cursors

A REF CURSOR or cursor variable is just a reference or a handle to a static cursor. It allows a user to pass this "reference to the same cursor" among all the programs that need access to the cursor. Cursor variables give you easy access to centralized data retrieval.

There are two types of cursor variables:

Strong REF Cursor and
Weak REF Cursor


What is the difference between Cursor and REF Cursor, and when would you appropriately use each of these?

Technically, under the covers, at the most "basic level", they are the same.

A "normal" PL/SQL cursor is static in definition.

Ref cursors may be dynamically opened or opened based on logic.

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;
end;
/

• Given that block of code - you see perhaps the most "salient" difference - no matter how many times you run that block - cursor C will always be select * from dual. The ref cursor can be anything.
• Another difference is a ref cursor can be returned to a client. A PL/SQL cursor cannot be returned to a client.
• Another difference is a cursor can be global - a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function).
• Another difference is a ref cursor can be passed from subroutine to subroutine - a cursor cannot be.
• Another difference is that static SQL (not using a ref cursor) is much more efficient then using ref cursors and that use of ref cursors should be limited to.
• returning result sets to clients
• when there is NO other efficient/effective means of achieving the goal. That is, you want to use static SQL (with implicit cursors really) first and use a ref cursor only when you absolutely have to.

A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result. The advantage that a ref cursor has over a plain cursor is that is can be passed as a variable to a procedure or a function. The ref cursor can be assigned to other ref cursor variables. This is a powerful capability in that the cursor can be opened, then passed to another block for processing, then returned to the original block to be closed. The cursor variable can also be returned by a function and assigned to another variable. The ref cursor variable is not a cursor, but a variable that points to a cursor. Before assigning a cursor variable, a cursor type must be defined.

type author_cursor is ref cursor;

This "REF CURSOR" is a weak typed cursor variable because it does not define the datatype the cursor will return. Below is the same cursor that is strongly typed.

type author_cursor is ref cursor
returning author%rowtype;

By strongly typing the cursor variable, you define what the cursor can return. If a strongly typed cursor returns something other that its return type, a "ROWTYPE_MISMATCH" exception is raised. A strongly typed cursor type is less flexible but less prone to programming errors. The PL/SQL compiler will verify that the "FETCH clause" has the correct variable/record for the cursor return type at compile time.

Once the cursor type is defined, the actual variable can be defined as the cursor type.

c1 author_cursor;

Now c1 is a variable of a cursor type. It is opened using a SQL statement.

open c1 for select * from authors;

Now c1 has all the attributes of the actual cursor. As with any cursor it is important to close the cursor as soon as you have completed processing.

SQL> declare
2 type auth_cursor is ref cursor;
3 c1 auth_cursor;
4 r_c1 author%rowtype;
5 begin
6 open c1 for select * from author;
7 fetch c1 into r_c1;
8 if c1%isopen then
9 dbms_output.put_line('The Cursor is open.');
10 end if;
11 dbms_output.put_line('Row Count is '||c1%rowcount);
12 close c1;
13 if not c1%isopen then
14 dbms_output.put_line('The Cursor is closed.');
15 end if;
16 end;
17 /


The Cursor is open.
Row Count is 1
The Cursor is closed.

Here some of the cursor attributes are used to process the cursor. Notice that the record used to hold a fetched cursor row is defined as an author table "%rowtype". Even though the example cursor variable is defined, the record can not use it because the cursor variable return type is not defined. The example below attempts to create a cursor %rowtype variable for processing.

SQL> declare
2 type auth_cursor is ref cursor;
3 c1 auth_cursor;
4 r_c1 c1%rowtype;
5 begin
6 null;
7 end;
8 /
r_c1 c1%rowtype;
*
ERROR at line 4:
ORA-06550: line 4, column 8:
PLS-00320: the declaration of the type of this
expression is incomplete or malformed
ORA-06550: line 4, column 8:
PL/SQL: Item ignored

However, a strongly typed cursor can use the cursor variable to define the cursor record.

SQL> declare
2 type auth_cursor is ref cursor return author%rowtype;
3 c1 auth_cursor;
4 r_c1 c1%rowtype;
5 begin
6 null;
7 end;
8 /

In this example, the auth_cursor type returns an author%rowtype. Because this is defined in line 2, the record defined in line 4 can now use it.

Cursor variables that are weakly typed can be defined to return any values. In the example below, the cursor variable c1 is defined as three different statements.

SQL> declare
2 type auth_cursor is ref cursor;
3 c1 auth_cursor;
4 r_c1 author%rowtype;
5 r2_c1 book%rowtype;
6 r3_c1 number;
7 begin
8 open c1 for select * from author;
9 fetch c1 into r_c1;
10 close c1;
11 open c1 for select * from book;
12 fetch c1 into r2_c1;
13 close c1;
14 open c1 for select sum(quantity)
15 from store join sales using (store_key)
16 group by store_name;
17 fetch c1 into r3_c1;
18 close c1;
19 end;
20 /

Although the block does not do anything but open and close the cursor variable, it does demonstrate that weakly typed variables can be defined differently each time the variable is opened. But what happens when a cursor variable is defined with a SQL statement but returns unexpected values? Below is an example of defining a cursor variable of one type and the record as another. The cursor variable is returning all columns in the book table, but the receiving variable is defined as a record of columns from the author table.

SQL> declare
2 type auth_cursor is ref cursor;
3 c1 auth_cursor;
4 r_c1 author%rowtype;
5 begin
6 open c1 for select * from book;
7 fetch c1 into r_c1;
8 close c1;
9 end;
10 /
declare
*
ERROR at line 1:
ORA-06504: PL/SQL: Return types of Result Set
variables or query do not match
ORA-06512: at line 7

Notice that the error message is pointing to the DECLARE clause. That is because the block successfully compiled and only threw the error when it was executed. The PL/SQL compiler can not catch the error because the cursor type did not define a return type. By changing the definition of the cursor type to a strongly typed definition, the compiler will catch this error when the code is compiled.

SQL> declare
2 type auth_cursor is ref cursor
3 return book%rowtype;
4 c1 auth_cursor;
5 r_c1 author%rowtype;
6 begin
7 open c1 for select * from book;
8 fetch c1 into r_c1;
9 close c1;
10 end;
11 /

fetch c1 into r_c1;
*
ERROR at line 8:
ORA-06550: line 8, column 5:
PLS-00394: wrong number of values in the INTO
list of a FETCH statement
ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored

Now the compiler catches the error. So far the examples have been using the cursor variables as regular cursors. The real advantage of using a cursor variable is the ability to pass it as a parameter. In the example below a local function is used to open a cursor called c1 and return it.
The block body calls the function to assign the cursor to the cursor variable c2. The block body then processes the cursor and closes it.

SQL> declare
2 type auth_cursor is ref cursor
return author%rowtype;
3 c2 auth_cursor;
4 r_c2 c2%rowtype;
5
6 function get_auth return auth_cursor
7 is
8 c1 auth_cursor;
9 begin
10 open c1 for select * from author;
11 return c1;
12 end;
13
14 begin
15 c2 := get_auth;
16 loop
17 fetch c2 into r_c2;
18 exit when c2%notfound;
19 dbms_output.put_line(initcap(r_c2.author_last_name));
20 end loop;
21 close c2;
22 end;
23 /

Jones
Hester
Weaton
Jackie
Withers
Petty
Clark
Mee
Shagger
Smith

Line 2 defines the cursor type. Lines 3 and 4 define the cursor and return record used in the body. Line 6 declares a local function called get_auth that returns an auth_cursor type. Inside this local function, cursor c1 is defined as an auth_cursor type, opened and returned to the calling code. The function is actually executed on line 15 when c2 is assigned the return value of the get_auth function. The cursor c2 is processed and finally closed on line 21. Note that c1 opened the cursor and c2 closed it. This is an important point.

The example contains only ONE cursor. When c2 is assign the value of c1, both variables point to the same cursor. Remember that c1 and c2 are variables that point to or reference the actual cursor.

The same basic example is shown below except, the output is generated by a local procedure. Note that the procedure print_name gets passed the cursor c2 and then processes it. It then passes the cursor backup to be closed by the body of the PL/SQL block.

SQL> declare
2 type auth_cursor is ref cursor
return author%rowtype;
3 c2 auth_cursor;
4 r_c2 c2%rowtype;
5
6 function get_auth return auth_cursor
7 is
8 c1 auth_cursor;
9 begin
10 open c1 for select * from author;
11 return c1;
12 end;
13
14 procedure print_name (c3 in out auth_cursor)
15 as
16 r_c3 author%rowtype;
17 begin
18 loop
19 fetch c3 into r_c3;
20 exit when c3%notfound;
21 dbms_output.put_line(initcap(r_c3.author_last_name));
22 end loop;
23 end;
24
25 begin
26 c2 := get_auth;
27 print_name(c2);
28 close c2;
29 end;
30 /

Jones
Hester
Weaton
Jeckle
Withers
Petty
Clark
Mee
Shagger
Smith

There are three items you should note about this PL/SQL block. First, the body is pretty simple to understand.

Get the authors, print the names, and close the cursor. Second, you can pass cursor variables to procedures and functions, and functions can return the variables. Lastly, it can become confusing about when a cursor variable should be closed.

WHERE CURRENT OF & FOR UPDATE

The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.

The WHERE CURRENT OF clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted. We must declare the cursor with the FOR UPDATE clause to use this feature.

Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated.

When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks. Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.

Oracle provides the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of a transaction.

The syntax of using the WHERE CURRENT OF clause in UPDATE and DELETE statements follows:

WHERE [CURRENT OF cursor_name | search_condition]

The following example opens a cursor for employees and updates the commission, if there is no commission assigned based on the salary level.



The FOR UPDATE clause in the SELECT statement can only be specified in the top level; subqueries cannot have this clause.

Another Example of WHERE CURRENT OF

Go through all Maths students and set all GPA’s under 4.0 to 4.0!

DECLARE
thisStudent Student%ROWTYPE;

CURSOR Maths_Student IS
SELECT * FROM Student WHERE SID IN
(SELECT SID FROM Take WHERE CID = ’CS145’)
FOR UPDATE;

BEGIN
OPEN Maths_Student;
LOOP
FETCH Maths_Student INTO thisStudent;
EXIT WHEN (Maths_Student%NOTFOUND);
IF (thisStudent.GPA < 4.0) THEN
UPDATE Student SET GPA = 4.0
WHERE CURRENT OF Maths_Student;
END IF;
END LOOP;

CLOSE Maths_Student;
END;
.
RUN;