–What is cursor:Â
cursor is defined as a private work area where the SQL statement(SELECT & DML) is executed.
—————————————————————————————
— select statement in PLSQL block
set serveroutput on;
clear screen;
declare
v_emp_salary hr.employees.salary%type;
begin
select salary into v_emp_salary from employees;– where employee_id=220;
dbms_output.put_line(‘The salary of the employee is ‘||v_emp_salary);
exception
when no_data_found then
dbms_output.put_line(‘No data found for this employee’);
when too_many_rows then
dbms_output.put_line(‘Many rows are returned from base table’);
end;
set SERVEROUTPUT ON
declare
v_salary employees.salary%type;Â Â — scalar variable
begin
select salary into v_salary from employees where employee_id =100;
dbms_output.put_line(‘The salary of the employee: ‘||v_salary);
end;
instead of using scalar variable,
————————————————————–
To get all values from base table we can go for 2 methods:Â
1. Composite variable
2. Cursor
–1. Composite variable:
========================
set SERVEROUTPUT ON
declare
type nt_salary_type is table of number(10);
nt_salary nt_salary_type := nt_salary_type();
begin
select salary bulk collect into nt_salary from employees;
for i in nt_salary.first..nt_salary.last
loop
dbms_output.put_line(nt_salary(i));
end loop;
end;
2. Cursor
=========
cursor is defined as a work area where the SQL statement is executed.
Two types of cursor:Â
================
1.Implicit cursor
2.explicit cursor
1. Implicit cursor
These are created by default when DML statements like Insert, Update and Delete statements are executed.
Implicit cursor is a session cursor that is created and managed by Oracle. Oracle Opens an implicit cursor everytime you run a DML or SELECT statement.
As a user we do not have a control on implicit cursor, but we can get information from its attributes.
Cursor Attributes:Â
================
cursorname%isopen
cursor_name%found
cursor_name%notfound
cursor_name%rowcount
–%FOUND Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open cursor, cursor_name%FOUND returns NULL. Afterward, it returns TRUE if the last fetch returned a row, or FALSE if the last fetch failed to return a row.
–%ISOPEN Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. If a cursor is open, cursor_name%ISOPEN returns TRUE; otherwise, it returns FALSE.
–%NOTFOUND Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. Before the first fetch from an open cursor, cursor_name%NOTFOUND returns NULL. Thereafter, it returns FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.
–%ROWCOUNT Attribute
A cursor attribute that can be appended to the name of a cursor or cursor variable. When a cursor is opened, %ROWCOUNT is zeroed. Before the first fetch, cursor_name%ROWCOUNT returns 0. Thereafter, it returns the number of rows fetched so far. The number is incremented if the latest fetch returned a row.
set SERVEROUTPUT ON;
clear screen;
begin
update customer set mobile_no=’+91-‘||mobile_no where cust_id=&cust_id;
if sql%notfound then
dbms_output.put_line(‘No – rows are updated’);
else
dbms_output.put_line(SQL%ROWCOUNT||’- rows updated’);
end if;
end;
/
–Explicit cursor is a user defined cursor
1) Declare the cursor
2) Open the cursor
3) Fetch the cursor
4) Close the cursor
–How to declare a cursor:Â
–========================
CURSOR cursor_name
IS
 SELECT_statement;
1) cursor C1Â Â Â Â Â Â Â Â — c1 is the cursor variable is select emp_name,salary from employees;
2) Open C1
3) Fetch cursor_name into variable1,variable2
4) Close c1
Example 1:Â
==========
declare
vemp_salary EMPLOYEES.salary%TYPE;
cursor c1 is select salary from employees;Â Â Â –Cursor declaration
begin
open c1;Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â — Open cursor
fetch c1 into vemp_salary;Â Â Â Â Â Â Â Â Â Â Â — Fetch value from cursor pointer
dbms_output.put_line(vemp_salary);
fetch c1 into vemp_salary;
dbms_output.put_line(vemp_salary);
fetch c1 into vemp_salary;
dbms_output.put_line(vemp_salary);
close c1;Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â — Close cursor
end;
Example 2:Â
==========
declare
vemp_salary EMPLOYEES.salary%TYPE;
cursor c1 is select salary from employees;Â Â Â –Cursor declaration
begin
open c1;Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â — Open cursor
loop
fetch c1 into vemp_salary;Â Â Â Â Â Â Â Â Â Â Â — Fetch value from cursor pointer
exit when c1%notfound;
dbms_output.put_line(vemp_salary);
end loop;
dbms_output.put_line(‘—–***********———–‘);
dbms_output.put_line(‘Total no of recored fetched from base table-‘||c1%rowcount);
close c1;Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â — Close cursor
end;
Example 3:Â
==========
declare
vemp_name EMPLOYEES.FIRST_NAME%TYPE;
vemp_salary EMPLOYEES.salary%TYPE;
cursor c1 is select first_name,salary from employees;
begin
open c1;
loop
fetch c1 into vemp_name,vemp_salary;
exit when c1%notfound;
dbms_output.put_line(vemp_name||vemp_salary);
end loop;
close c1;
end;
–The following PL/SQL block uses %ROWCOUNT to fetch the names and salaries of the five highest-paid employees:
DECLARE
  CURSOR c1 is
  SELECT last_name, employee_id, salary FROM employees
   ORDER BY salary DESC;  — start with highest-paid employee
  my_name employees.last_name%TYPE;
  my_empno employees.employee_id%TYPE;
  my_sal  employees.salary%TYPE;
BEGIN
  OPEN c1;
  LOOP
   FETCH c1 INTO my_name, my_empno, my_sal;
   EXIT WHEN (c1%ROWCOUNT > 5) OR (c1%NOTFOUND);
   dbms_output.put_line(‘Employee ‘ || my_name || ‘ (‘ || my_empno || ‘) makes ‘ || my_sal);
  END LOOP;
  CLOSE c1;
END;
/
The GV$OPEN_CURSOR (or the V$OPEN_CURSOR) view shows all the cursors that each user session has currently opened and parsed, or cached.
You can issue the following query to identify the sessions with a high number of opened and parsed or cached cursors.
— Execute in ADMIN schema
select * from v$open_cursor where user_name=’HR’;
SQL> select saddr, sid, user_name, address,hash_value,sql_id, sql_text
from gv$open_cursor where sid in (select sid from v$open_cursor
group by sid having count(*) > &threshold);
— how many cursor we can open in a session?
select * from v$parameter where name=’open_cursors’;
——————————————-
CURSOR WITH RETURN CLAUSE
CURSOR cursor_name
RETURN field%ROWTYPE
IS
  SELECT_statement;
——————————————–
cursor FOR loop:
===============
Syntax:
FOR record_index in cursor_name
LOOP
  {…statements…}
END LOOP;
————–Eg——————–
Declare
cursor c1 is select first_name,salary from employees;
begin
for record in c1Â Â Â Â Â Â Â Â Â Â Â Â Â –record is a composite data type here
loop
dbms_output.put_line(record.first_name||record.salary);
end loop;
end;
ex1:
select emp_name, job_id,salry for particular dept using cursor for loop;
********************************************************************************
— Cursor for LOOP
DECLARE
cursor c1(no number) is select * from employees
where department_id = no;
tmp employees%rowtype;
BEGIN
FOR tmp IN c1(30) LOOP
dbms_output.put_line(‘EMP_No:Â Â ‘||tmp.employee_id);
dbms_output.put_line(‘EMP_Name:Â ‘||tmp.first_name);
dbms_output.put_line(‘EMP_Dept:Â ‘||tmp.department_id);
dbms_output.put_line(‘EMP_Salary:’||tmp.salary);
  dbms_output.put_line(‘ ‘);
END Loop;
–CLOSE c1;
END;
——————————————
CURSOR WITH PARAMETERS:
CURSOR cursor_name (parameter_list)
IS
 SELECT_statement;
declare
vemp_name EMPLOYEES.FIRST_NAME%TYPE;
vemp_salary EMPLOYEES.salary%TYPE;
cursor c_dept30 is select first_name,salary from employees where department_id=30;
cursor c_dept60 is select first_name,salary from employees where department_id=60;
begin
open c_dept30;
loop
fetch c_dept30 into vemp_name,vemp_salary;
exit when c_dept30%notfound;
dbms_output.put_line(vemp_name||vemp_salary);
end loop;
close c_dept30;
open c_dept60;
loop
fetch c_dept60 into vemp_name,vemp_salary;
exit when c_dept60%notfound;
dbms_output.put_line(vemp_name||vemp_salary);
end loop;
close c_dept60;
end;
Declare
cursor c1(prm_dept_no number) is select salary from employees where department_id=prm_dept_no;
v_salary number(10);
begin
open c1(30);
dbms_output.put_line(‘—-This is the data for department_id 30—-‘);
loop
fetch c1 into v_salary;
exit when c1%notfound;
dbms_output.put_line(v_salary);
end loop;
close c1;
open c1(60);
dbms_output.put_line(‘—-This is the data for department_id 60—-‘);
loop
fetch c1 into v_salary;
exit when c1%notfound;
dbms_output.put_line(v_salary);
end loop;
close c1;
end;
Declare
cursor c1(cdept_no number) is select first_name,salary from employees where department_id=cdept_no;
begin
for record in c1(30)
loop
dbms_output.put_line(record.first_name||record.salary);
end loop;
end;
Declare
cursor c1_emp is select salary from employees;
cursor c2_dept is select department_name from departments;
v_salary number(10);
v_department_name varchar2(100);
begin
open c1_emp;
dbms_output.put_line(‘—-This is the data for Employees table—-‘);
loop
fetch c1_emp into v_salary;
exit when c1_emp%notfound;
dbms_output.put_line(v_salary);
end loop;
close c1_emp;
open c2_dept;
dbms_output.put_line(‘—-This is the data for departments table—-‘);
loop
fetch c2_dept into v_department_name;
exit when c2_dept%notfound;
dbms_output.put_line(v_department_name);
end loop;
close c2_dept;
end;
— REF Cursor
A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value.
A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time. One can use a Ref Cursor as target of an assignment, and it can be passed as parameter to other program units.
–Ref Cursors are opened with an ‘OPEN FOR’ statement. In most other ways they behave similar to normal cursors.
A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a REF CURSOR is a pointer or a handle to a result set on the database. REF CURSOR s are represented through the OracleRefCursor ODP.NET class.
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.
To declare a cursor variable, we can use the REF CURSOR is the data type.
–What is the syntax for Declaring Ref Cursor?
DECLARE
 TYPE [cursor_variable_name] IS REF CURSOR [RETURN (return_type)];
PL/SQL has two forms of REF CURSOR types:Â
1. Strong typed REF CURSOR — Any Ref Cursor which has a fixed return type is called a Strong Ref Cursor.
2. Weak typed REF CURSORÂ — weak ref cursors are those which do not have any return type.
The following shows an example of a strong REF CURSOR.
DECLARE
  TYPE customer_t IS REF CURSOR RETURN customer%ROWTYPE;
  c_customer customer_t;
This form of cursor variable called strong typed REF CURSOR because the cursor variable is always associated with a specific record structure, or type
And here is an example of a weak typed REF CURSOR declaration that is not associated with any specific structure
DECLARE
  TYPE customer_t IS REF CURSOR;
  c_customer customer_t;
Since weak Ref Cursors do not have any fixed return type thus they are open to all SELECT statements. And this makes them one of the most used Ref Cursors in PL/SQL.
— Weak typed REF CURSOR
declare
type ref_cursor IS REF CURSOR ;
rc_employees_list ref_cursor;
v_first_name varchar2(100);
v_dept_name varchar2(100);
begin
dbms_output.put_line(‘– This is Employees details–‘);
open rc_employees_list for select first_name from employees;
loop
fetch rc_employees_list into v_first_name;
exit when rc_employees_list%notfound;
dbms_output.put_line(v_first_name);
end loop;
dbms_output.put_line(‘The total employees are: ‘||rc_employees_list%rowcount);
close rc_employees_list;
dbms_output.put_line(‘– This is department details–‘);
open rc_employees_list for select department_name from departments;
loop
fetch rc_employees_list into v_dept_name;
exit when rc_employees_list%notfound;
dbms_output.put_line(v_dept_name);
end loop;
dbms_output.put_line(‘The total departments are: ‘||rc_employees_list%rowcount);
close rc_employees_list;
end;
— Strong typed REF CURSOR
declare
type ref_cursor is REF cursor return employees%rowtype;
rc_employees_list ref_cursor;
v_emp_details hr.employees%rowtype;
begin
dbms_output.put_line(‘These are employees details’);
open rc_employees_list for select * from employees;
loop
fetch rc_employees_list into v_emp_details;
exit when rc_employees_list%notfound;
dbms_output.put_line(‘The employee name: ‘||v_emp_details.first_name);
dbms_output.put_line(‘The employee phone_number: ‘||v_emp_details.phone_number);
end loop;
dbms_output.put_line(‘The total employees are: ‘||rc_employees_list%rowcount);
close rc_employees_list;
end;
