What is Explicit Cursor with examples? | Explicit Cursor examples

In my previous article I have given the examples and definition of implicit cursors. We require to process multiple rows at a same time. To process multiple rows at same time the cursors are useful. Just remember that every sql statement executed by Oracle server has associated and executed by cursor. If you want to process multiple rows returned by select statement then you can use the explicit cursor. We can see the explicit cursor with real life examples in industry.

What you find in this article?

1. What is explicit cursor?
2. What are explicit cursor attributes?
3. What are examples of explicit cursor?

What is Explicit Cursor?

The explicit cursor is programmer defined cursor which is used to process set of select statements together. Following are some functionalities :

1. This type of cursor is used to perform row by row processing
2. It will keep the track of the rows that is currently being processed.
3. Programmer can control explicit cursors easily.

What are attributes of explicit cursor?

As we have seen the attributes of implicit cursor we have few attributes to discuss about explicit cursor. In PL SQL interview questions you will asked the attributes of explicit cursor.

%ISOPEN :

The Boolean attributes evaluates the true if cursor is OPEN.

%NOTFOUND :

This Boolean attribute evaluates the true if most recent fetch does not returns results.

%FOUND :

The boolean attribute evaluates the true if most recent fetch returns row as result.

%ROWCOUNT :

It evaluates the total number of rows.

What is example of cursor step by step?

The programmers will define the explicit cursors step by step. In this section i would like to give you step by step process to define and execute the cursor.

Step 1 : Declaration of Cursor with example

1. Do not include the INTO clause in cursor declaration.
2. You can use ORDER BY clause to process the rows in order.

Declare a cursor syntax:

DECLARE CURSOR NAME_OF_CURSOR

IS

Select Statement with order by clause.

Example :

Declare Cursor C_Employee

as select First_name, Last_name, Salary from Hr.Employees;

— We are using HR Schema

Step 2 : Opening the Cursor

The OPEN statement of cursor will execute the query associated with the cursor. When you use the OPEN statement following will happen :

1. Dynamically allocates the memory for context area.
2. It parses the select statement
3. Binds the input variable
4. Identifies the active set . The active set is set of rows which satisfies search criteria.
5. It will position the pointer at first row.

Open Cursor :

Open Cursor_name;

Example :

Open C_Employee;

Step 3 :Fetching data from Cursor

The Fetch statement will fetch the rows from table once at a time. We can use the %NOTFOUND attribute to determine the entire active set is being retrieved.

FETCH statement performs :

1. It will read the data from current row.
2. Advances the pointer to next row

Fetch Syntax :

FETCH Cursor_name from Variable_names;

Step 4 : Close the Cursor

This is also the most important step where user can use the close cursor statement to release the context area and closing the cursor. You can reopen the cursor if required. You should make habit to close the cursor to free up the resources.

Close Cursor_name;

Real life examples of Cursor :

To process the records in HR Schema and employee table.

select * from HR. Employees

where department_id=30;

–create cursor to print employee_id, first_name for department_id =80

—Step 1 :

DECLARE
CURSOR C_Employees is
SELECT employee_id, first_name FROM HR.employees
where department_id=80;

v_empno employees.employee_id%type;
v_first_name employees.first_name%type;

–Step 2

BEGIN

–Step 3
OPEN C_Employees;

loop
fetch C_Employees into v_empno, v_first_name;
exit when c_emp_dept30%notfound;
dbms_output.put_line(v_empno||’ ‘||v_first_name); — the exit should before output
end loop;

–Step 4
close C_Employees;

End;

Example 2 : Example of Cursor Attributes ( Kindly use HR schema and Employees Table)

–using c_emp%notfound, c_emp%isopen,c_emp%rowcount
DECLARE
CURSOR C_Employee_Attributes is
SELECT employee_id,first_name FROM Hr.employees;
v_empno employees.employee_id%type;
v_first_name employees.first_name%type;

BEGIN
if C_Employee_Attributes%isopen then
null;
else
open C_Employee_Attributes;
end if;
dbms_output.put_line(‘the counter for cursor now is ‘||C_Employee_Attributes%rowcount);
loop
fetch C_Employee_Attributes into v_empno, v_first_name;
exit when C_Employee_Attributes%notfound or C_Employee_Attributes%rowcount>10 ;
dbms_output.put_line(v_empno||’ ‘||v_first_name);
end loop;
dbms_output.put_line(‘the counter for cursor now is ‘||C_Employee_Attributes%rowcount);
close C_Employee_Attributes;
END;

Example 3 : Example of cursor using for loop

— we will do the same example using the for loop cursor

DECLARE
CURSOR C_Parameter(v_dept number) –here we defined the parameter without size
is
SELECT employee_id id , first_name FROM Hr.employees
where department_id=v_dept;

BEGIN

dbms_output.put_line(‘dept 10 contains:’);
for i in C_Parameter(10)
loop
dbms_output.put_line(i.id||’ ‘||i.first_name);
end loop;

dbms_output.put_line(‘dept 20 contains:’);
for j in c_emp_dept (20)
loop

dbms_output.put_line(j.id||’ ‘||j.first_name);

end loop;
END;

These are few examples of cursor. You can use the cursors to process multiple rows in PL SQL. Kindly let me know if any questions or any comments for the article.