Use of Oracle cursor

When Oracle Database executes a SQL statement, it stores the result set and processing information in an unnamedprivate SQ L area. A pointer to the unnamed area, called acursor, lets youretrieve the rows of the result set one at a time.Cursor AttributesReturn information about the state of the cursor. The cursor is a memory workspace of SQL that is defined by the system or user as a variable. Cursors are used to temporarily store chunks of data that are extracted from the database.

The cursor types include three types: Static cursors: Explicit (explicit) and implicit (implicit) cursors; ref cursors: A reference type, similar to a pointer.
1. An implicit cursor
1) Select ... Into ... Statement, DML statement, using an implicit cursor. In addition, another way to use the implicit cursor using a For loop.
Every time you run either a SQL DML statement or A/PLSELECTINTOstatement, PL/SQL Opens anImplicit cursor. You can get the information about the this cursor from its attributes, but you cannot control it. After the statement runs, the database closes the cursor; However, its attribute values remain available until another DML orSELECTINTOStatement runs.
2) The ability to understand the state and result of an operation through the properties of an implicit cusor. The properties of the cursor include:
Sql%rowcount integer represents the number of rows of data that the DML statement successfully ran
Sql%found Boolean value of True for INSERT, delete, update, or single row query operation succeeded
Sql%notfound Boolean is the opposite of the Sql%found property return value
Sql%isopen Boolean DML is true during operation and false after end
3) An implicit cursor is opened and closed by the system itself.
Like what:

Set serveroutput ondeclarebegin    Update employees set employee_name= ' Mike ' where employee_id=1001;  If Sql%found then      dbms_output.put_line (' Name is updated ');  else    dbms_output.put_line (' Name is not updated ');  End if;end;/

Set Serveroutput ondeclarebegin for    tableinfo in (SELECT * from User_tables) loop    dbms_output.put_line ( Tableinfo.table_name);  End Loop;exception when  others then    dbms_output.put_line (SQLERRM); end;/

2. An explicit cursor
PL/SQL also lets you declare explicit cursors. AnExplicit CursorHave a name and is associated with a query (SQLSELECTStatement)-usually one that returns multiple rows. After declaring a explicit cursor, you must open itOPENstatement), fetch rows one at a time from the result set (with theFETCHstatement), and close the cursor (with theCLOSEstatement). After closing the cursor, you can neither fetch records from the result set or see the cursor attribute values.
1) The properties of an explicit cursor include:
The properties of a cursor return value type meaning
%rowcount integer Gets the number of data rows returned by the FETCH statement
%found Boolean Recent FETCH statement returns a row of data is true, otherwise false
%notfound Boolean is the opposite of the%found property return value
%isopen Boolean cursor already open value is true, otherwise false

2) The use of an explicit cursor is divided into four steps:
A defines the cursor---cursor [cursor Name] is;
B Open Cursor---Open [cursor Name];
C Operation Data---Fetch [Cursor name]
D Close Cursor---Close [cursor Name]
Here are a few common ways to use explicit cursor.

Set serveroutput ondeclare cursor cur is select * from User_tables;      Tableinfo User_tables%rowtype;begin Open cur;    Loop fetch cur into tableinfo;    Exit when Cur%notfound;  Dbms_output.put_line (Tableinfo.table_name); End Loop;
Exception when others then Dbms_output.put_line (SQLERRM);
Close cur;end;/

Set serveroutput ondeclare  cursor cur is select * from User_tables;begin to    tableinfo in cur loop    dbms_output . Put_Line (tableinfo.table_name);  End Loop;exception when  others then    dbms_output.put_line (SQLERRM); end;/

You can also use the cursor with the parameters open.

Set serveroutput ondeclare cursor cur (tblname varchar2) is a select * from user_constraints where table_name=tblname;      Tableinfo user_constraints%rowtype;begin Open cur (' EMPLOYEES ');    Loop fetch cur into tableinfo;    Exit when Cur%notfound;  Dbms_output.put_line (Tableinfo.constraint_name); End Loop;
Exception when others then Dbms_output.put_line (SQLERRM);
Close cur;end;/

Set serveroutput ondeclare  cursor cur (tblname varchar2) is a select * from User_constraints where table_name=tblname;be Gin for  tableinfo in cur (' EMPLOYEES ') loop    dbms_output.put_line (tableinfo.constraint_name);  End Loop;exception when  others then    dbms_output.put_line (SQLERRM); end/

You can use the WHERE CURRENT OF clause to run an UPDATE or delete operation.

Set serveroutput ondeclare  cursor cur is select * FROM employees to Update;begin for    tableinfo on cur loop    up Date employees set salary=salary*1.1 where current of cur;  End Loop;  Commit;exception when  others then    dbms_output.put_line (SQLERRM); end;/

3. REF cursor (cursor Variables)
REF cursor has the ability to determine the query used by the cursor at execution time. With REF cursor, you can pass a result set between programs (open a cursor variable in a program and process the data in another program).
You can also use the REF CURSOR to implement bulk SQL to improve SQL performance.

REF CURSOR is divided into two types, strong ref CURSOR and Weak REF CURSOR.

A strong REF CURSOR type specifies a return type, which is the RECORD type of its cursor variables. The PL/SQL compiler does not allow your to use these strongly typed cursor variables for queries so return rows that is Not of the return type. Strong REF CURSOR types is less error-prone than weak ones, but weak ones is more flexible. Strong REF CURSOR: Specifies that the type of the Retrun type,cursor variable must be the same as the return type.

A weak REF CURSOR type does not specify a return type. The PL/SQL compiler accepts weakly typed cursor variables in any queries. Weak REF CURSOR types is interchangeable; Therefore, instead of creating weak REF CURSOR types, you can use the predefined type weak CURSOR type Sys_refcursor. Weak REF CURSOR: Does not specify a return type, and can match whatever type of CURSOR variable.

After declaring a cursor variable, you must open it for a specific query (with the open for statement), fetch rows one at A time from the result set (with the FETCH statement), and then either close the cursor (with the close statement) or open It for another specific query (with the OPEN for statement). Opening the cursor variable for another query closes it for the previous query. After closing a cursor variable for a specific query, you can neither fetch records from the result set of that query nor See the cursor attribute values for that query.

Use of REF cursor:
1) Type [cursor Type name] is ref Cursor
2) Open cursor for ...
3) Fetch [Cursor name]
4) Close Cursor
Like what:

STEP1:

Create or Replace package TEST as  type Employees_refcursor_type is REF CURSOR return employees%rowtype;  Procedure Employees_loop (employees_cur in employees_refcursor_type); end test;/

STEP2:

Create or replace package body TEST as  procedure Employees_loop (Employees_cur on Employees_refcursor_type)    is EMP Employees%rowtype;  Begin    Loop      fetch employees_cur into EMP;      Exit when Employees_cur%notfound;      Dbms_output.put_line (emp.employee_id);    End Loop;  End Employees_loop;end test;/

STEP3:

Set Serveroutput ondeclare  emprefcur test.employees_refcursor_type;begin for  i in 10..20 loop    Dbms_ Output.put_line (' Department id= ' | | i);    Open Emprefcur for SELECT * FROM Employees where department_id=i;    Test.employees_loop (emprefcur);  End Loop;exception when  others then    dbms_output.put_line (SQLERRM);      Close emprefcur;end;/

Another example is the REF CURSOR as the output parameter.

Create or Replace procedure Get_emps (deptno in int, cur out sys_refcursor) asbegin  Open cur for select * from EMP wher E Emp.deptno = deptno;end;declare  cur sys_refcursor;  Rec Emp%rowtype;begin  Get_emps (ten, cur);  Loop    fetch cur into rec;    Dbms_output.put_line (rec.ename);    Exit when Cur%notfound;  End Loop;  Close cur;end;

4. BULK SQL
Use the ForAll and bulk collect clauses. Using bulk SQL can reduce the communication overhead between plsql engine and SQL engine and improve performance.
The PL/SQL features that comprise bulkFORALLStatement and theBULKCOLLECTClause. TheFORALLStatement sends DML statements from PL/SQL to the batches rather than one at a time. TheBULKCOLLECTClause returns results from SQL to PL/SQL in batches rather than one at a time. If a query or DML statement affects four or more database rows, then bulk SQL can significantly improve performance.
1. To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a/PL FORALL statement instead o f a loop construct. Accelerates the operation of the INSERT, UPDATE, DELETE statement, which is to replace the loop statement with the ForAll statement.
2. To-speed up SELECT statements, include the BULK COLLECT into clause in the SELECT statement instead of using into. Speed up select and replace into with bulk COLLECT into.

Sql> CREATE TABLE Employees_tmp as select First_Name, last_name, salary from employees where 0=1;

Set Serveroutput ondeclare  cursor employees_cur (depid employees.department_id%type) is select First_Name, Last_ Name, salary from employees where department_id=depid;  Type Employee_table_type is table of Employees_cur%rowtype index by Pls_integer;  Employee_table employee_table_type;begin  Open employees_cur (+);  Fetch employees_cur bulk collect into employee_table;  Close employees_cur;    For i in 1..employee_table.count loop    dbms_output.put_line (employee_table (i). first_name | | "| | Employee_table (i). last_name | | ', ' | | Employee_table (i). salary);  End Loop;    ForAll i in Employee_table.first. Employee_table.last    INSERT INTO employees_tmp values (employee_table (i). first_name, Employee_table (i). Last_ Name, employee_table (i). salary);  commit;end;/

5. Properties of Cursors
Each explicit cursor has four properties:%found,%isopen,%notfound, and%rowcount.
They can all return useful information related to the result of a static or dynamic SQL statement running.

To handle SQL data manipulation statements, Oracle opens an implicit cursor named SQL.
Its properties return information about an INSERT, UPDATE, delete, or single-row select that was recently run.

6. Dynamic Performance Table V$open_cursor
V$open_cursor is a view of Oracle providing a view of the cursor that has been opened for each session.
Inside the v$open_cursor we can see the cursor that is currently open and the cached cursor within the PGA.
After 11g Release 2, the Cursor_type column was added to the V$open_cursor view. Contains the following values:
SESSION CURSOR CACHED
PL/SQL CURSOR CACHED
OPEN
Open-recursive
DICTIONARY LOOKUP CURSOR CACHED
BUNDLE DICTIONARY LOOKUP CACHED
The following statement is available to understand the number of cursor actually open.
Select SUM (a.value), B.namefrom v$sesstat A, v$statname bwhere a.statistic# = B.statistic#and b.name = ' opened Cursors cur Rent ' GROUP by b.name;

Oracle offers 2 of parameters.
1) open_cursors, defines the maximum number of cursors that can be opened for each session. Can be queried by select * from v$parameter where name = ' open_cursors ' or show parameter open_cursors.
2) session_cached_cursors This parameter limits the length of the cursor cache list in the PGA session, and the session cursor cache list is a bidirectional LRU (Least recently Used) list, when a session is about to close a cursor, assuming that the cursor has a parse count of more than 3 times, the cursor will be added to the session cursor cache The MRU side of the list. When a session is going to parse a SQL, it will first go to the PGA to search for the session cursor cache list, assuming that the cursor is removed from the list, The cursor is then added to the MRU side when it is closed. Session_cached_cursor provides high-speed soft-analysis capabilities that provide higher performance than soft parse.

Use of Oracle cursor