Oracle Cursors Syntax Summary __oracle

A Oracle's Cursors Concept:
Cursors: Used to query a database, get a pointer to a collection of records (the result set), which allows the developer to access a row of result sets at a time and manipulate each result set.

Two Oracle's Cursors Classification:
1. Static cursors:
is divided into explicit and implicit cursors.
2. REF CURSOR:
is a reference type, similar to a pointer.

Three Oracle's Cursors Details:
1. An explicit cursor:
cursor cursor name (parameter) [return value type] is
SELECT statement
Life cycle:
A. Opening a cursor (open)
Parsing, binding ... Data is not retrieved from the database
B. Fetching records from cursors (FETCH into)
Executes the query, returning the result set. A local variable is typically defined as a buffer for fetching data from a cursor.
C. Closing cursors (Close)
The cursor processing is complete and the user cannot fetch rows from the cursor. You can also reopen it.
Options: Parameters and return types
Set Serveroutput on
Declare
Cursor Emp_cur (P_deptid in number) is
SELECT * FROM Employees where department_id = P_deptid;
L_emp Employees%rowtype;
Begin
Dbms_output.put_line (' Getting employees from department 30 ');
Open Emp_cur (30);
Loop
Fetch emp_cur into l_emp;
Exit when Emp_cur%notfound;
Dbms_output.put_line (' Employee id ' | | | l_emp.employee_id | | ' is ');
Dbms_output.put_line (L_emp.first_name | | ' ' || L_emp.last_name);
End Loop;
Close emp_cur;
Dbms_output.put_line (' Getting employees from department 90 ');
Open Emp_cur (90);
Loop
Fetch emp_cur into l_emp;
Exit when Emp_cur%notfound;
Dbms_output.put_line (' Employee id ' | | | l_emp.employee_id | | ' is ');
Dbms_output.put_line (L_emp.first_name | | ' ' || L_emp.last_name);
End Loop;
Close emp_cur;
End
/

2. An implicit cursor:
There are two types of cursor variables that need not be explicitly established:
A. Using the DML language in Pl/sql, using an implicit cursor named SQL provided by Oracle
B.cursor for Loop, for Loop statement
A. For example:
Declare
Begin
Update departments set Department_name=department_name;
--where 1=2;

Dbms_output.put_line (' Update ' | | sql%rowcount | | ' Records ');
End
/

B. Examples:
Declare
Begin
For My_dept_rec in (select Department_name, department_id from departments)
Loop
Dbms_output.put_line (my_dept_rec.department_id | | ' : ' || My_dept_rec.department_name);
End Loop;
End
/

C. For example:
Single Select
Declare
L_empno EMP. Employee_id%type;
--L_ename Emp.ename%type;
Begin
Select employee_id
Into L_empno
from EMP;
--where rownum = 1;
Dbms_output.put_line (L_empno);
End
/

Gets a value using into, only one row is returned.

Cursor Properties:
%found: When the variable finally gets the record from the cursor, the record is found in the result set.
%notfound: When the variable finally gets the record from the cursor, no records are found in the result set.
%rowcount: The number of records that have been fetched from the cursor at the current moment.
%isopen: Open.
Declare
Cursor Emps is
Select * FROM Employees where rownum<6 order by 1;

EMP Employees%rowtype;
Row number: = 1;
Begin
Open Emps;
Fetch emps into EMP;

Loop
If Emps%found Then
Dbms_output.put_line (' Looping over record ' | | row| | ' Of ' | | Emps%rowcount);
Fetch emps into EMP;
Row: = row + 1;
Elsif Emps%notfound Then
Exit; ---exit loop, not IF
End If;
End Loop;

If Emps%isopen Then
Close Emps;
End If;
End;
/

The difference between an explicit and an implicit cursor:
Use implicit cursors as much as possible to avoid writing additional cursor control code (Declaration, open, fetch, close), or to declare variables to hold data obtained from the cursor.

3. REF cursor cursor:
A dynamic cursor that determines the query used by the cursor at run time. Classification:
1. Strongly typed (limited) REF CURSOR, specify return type
2. Weak type (unrestricted) REF CURSOR, which does not specify a return type, can get any result set.
TYPE Ref_cursor_name is REF CURSOR [return return_type]

Declare
Type refcur_t is REF CURSOR;

Type emp_refcur_t is ref CURSOR return employee%rowtype;
Begin
Null;
End;
/

Strong type Examples:
Declare
--Declaring record types
Type Emp_job_rec is record (
employee_id number,
Employee_Name VARCHAR2 (50),
Job_title VARCHAR2 (30)
);
--Declares ref CURSOR, the return value is the record type
Type Emp_job_refcur_type is REF CURSOR
return emp_job_rec;
--Defines a variable for a REF CURSOR cursor
Emp_refcur Emp_job_refcur_type;
Emp_job Emp_job_rec;
Begin
Open Emp_refcur for
Select e.employee_id,
E.first_name | | ' ' || E.last_name "Employee_Name",
J.job_title
From Employees E, Jobs J
where e.job_id = j.job_id and RowNum < one order by 1;
Fetch emp_refcur into emp_job;
While Emp_refcur%found loop
Dbms_output.put_line (Emp_job.employee_name | | ' s job is ');
Dbms_output.put_line (Emp_job.job_title);
Fetch emp_refcur into emp_job;
End Loop;
End
/

This article is derived from the original PHP Information link: http://www.phpq.net/oracle/oracle-cursors-syntax.html