Oracle cursor Introduction and use of __oracle

The

One   concept
cursor is a memory workspace for SQL, defined by the system or user as a variable. The role of a cursor is to temporarily store blocks of data that are extracted from the database. In some cases, the data needs to be transferred from the table stored in the disk to the computer memory for processing, and the processing results are displayed or eventually written back to the database. This will increase the speed of data processing, otherwise frequent disk data exchange will reduce efficiency. The
two   type
  Cursor type contains three types: implicit Cursor, explicit Cursor, and ref Cursor (Dynamic Cursor).
1. Implicit cursor:
1). For select ... Into ... Statement, you can only get a single piece of data from the database at a time, and for this type of DML SQL statement, it's an implicit cursor. Example: Select/update/insert/delete operation.
2) Function: You can understand the state and results of an operation by implicitly cusor properties to achieve the control of the process. The Cursor property contains the
Sql%rowcount integer representing the number of rows successfully executed by the DML statement
sql%found  The Boolean   value to True represents the success of the INSERT, delete, UPDATE, or single-line query operation
sql% The NotFound Boolean is the opposite of the return value of the Sql%found property
Sql%isopen Boolean DML is true during execution and false
3) implicit cursor is that the system automatically turns on and off cursor.
The following is a sample: 
set serveroutput on; Begin Update T_contract_master Set liability_state = 1 where Policy_code = ' 123456789 '; If Sql%found then Dbms_output.put_line (' The Policy is updated successfully. '); Commit else Dbms_output.put_line (' The policy is updated failed. '); End If; End / 

2. Explicit cursor:
(1) for fetching multiple rows of data from a database, you need to use an explicit cursor. An explicit Cursor property contains:
Cursor properties    return value type    meaning     semantic  
%rowcount   integral type   Get the number of rows returned by the FETCH statement  
%found  Boolean most recent FETCH statement returns a row of data is true, otherwise false  
%notfound   Boolean and% The Found property return value is the opposite  
%isopen boolean cursor is open for true, or false  

(2) for explicit cursors are divided into four steps:
 define Cursors---cursor  [ Cursor name]  is;
 Open Cursor---open  [Cursor Name];
 Operation Data---fetch  [Cursor name]
 Close cursor---Closed [Cursor name], this step must not be omitted.
(3) The following are three common explicit cursor usages.

Set serveroutput on; DECLARE---Define Cursor Cursor cur_policy is select Cm.policy_code, cm.applicant_id, Cm.period_prem,cm.bank_code, Cm.bank_account from t_contract_master cm where cm.liability_state = 2 and Cm.policy_type = 1 and Cm.policy_cate in (' 2 ', ' 3 ', ' 4 ') and RowNum < 5 order BY cm.policy_code Desc; Curpolicyinfo Cur_policy%rowtype;---define cursor variable Begin open cur_policy; ---open cursor Loop--deal with extraction data from DB Fetch cur_policy into Curpolicyinfo; Exit when Cur_policy%notfound; Dbms_output.put_line (Curpolicyinfo.policy_code); End Loop; Exception when others then close cur_policy; Dbms_output.put_line (SQLERRM); If Cur_policy%isopen then--close cursor close cur_policy; End If; End /------------------------Set serveroutput on; Declare Cursor cur_policy is select Cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from T _contract_master cm where cm.liability_state = 2 and Cm.policy_type = 1 and Cm.policy_cate in (' 2 ', ' 3 ', ' 4 ') and rownum ; 5 ORDER BY Cm.policy_code Desc; V_policycode T_contract_master.policy_code%type; V_applicantid T_contract_master.applicant_id%type; V_periodprem T_contract_master.period_prem%type; V_bankcode T_contract_master.bank_code%type; V_bankaccount T_contract_master.bank_account%type; Begin Open cur_policy; Loop Fetch cur_policy into V_policycode, V_applicantid, V_periodprem, V_bankcode, V_bankaccount; Exit when Cur_policy%notfound; Dbms_output.put_line (V_policycode); End Loop; Exception when others then close cur_policy; Dbms_output.put_line (SQLERRM); If Cur_policy%isopen then close cur_policy; End If; End /--------------------------------Set serveroutput on; Declare Cursor cur_policy is select Cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from T _contract_master cm where cm.liability_state = 2 and Cm.policy_type = 1 and Cm.policy_cate in (' 2 ', ' 3 ', ' 4 ') and rownum ; 5 ORDER BY Cm.policy_code Desc; The Begin for Rec_policy in Cur_policy Loop Dbms_output.put_line (Rec_policy.policy_code); End Loop; Exception when others then Dbms_output.put_line (SQLERRM); End /

3. REF Cursor (Dynamic cursor):
1 and implicit cursor, the difference between explicit cursor: REF cursor is the data result set that can be obtained by passing parameters during run time. The other two types of cursor, which are static, determine the data result set during compilation.
2) Use of REF cursor:
type [Cursor Type name] is ref Cursor
define a dynamic SQL statement
open cursor
---Fetch for operation data [Cursor name]
close Cursor
Here is a sample:

Set serveroutput on; Declare---Define cursor type name type Cur_type is REF CURSOR; Cur_policy Cur_type; Sqlstr VARCHAR2 (500); Rec_policy T_contract_master%rowtype; Begin---Define dynamic SQL sqlstr: = ' Select Cm.policy_code, cm.applicant_id, Cm.period_prem,cm.bank_code,cm.bank_account From T_contract_master cm where cm.liability_state = 2 and Cm.policy_type = 1 and Cm.policy_cate in (2,3,4) and rownum ; 5 ORDER BY cm.policy_code Desc '; ---Open Cursor open cur_policy for sqlstr; Loop fetch cur_policy into Rec_policy.policy_code, rec_policy.applicant_id, REC_POLICY.PERIOD_PREM,REC_POLICY.BANK_ Code,rec_policy.bank_account; Exit when Cur_policy%notfound; Dbms_output.put_line (' Policy_code: ' | | Rec_policy.policy_code); End Loop; Close Cur_policy; End /

4. Common exception
1. Error name error code error meaning 2. Cursor_already_open ora_06511 attempted to open a cursor 3 that is already open. Invalid_cursor ora_01001 attempted to use a cursor 4 that is not open. Dup_val_on_index ora_00001 saves duplicate values to a column with a unique index constraint of 5. Zero_divide ora_01476 Division error 6 that has a divisor of zero. Invalid_number ora_01722 attempted to convert a numeric value of invalid characters to 7. Rowtype_mismatch ora_06504 The primary variable and the cursor type are incompatible with 8. Error 9 Value_error ora_06502 conversion, truncation, or arithmetic operation. Too_many_rows ora_01422 SELECT ... Into ... Statement returns more than one row of data 10. No_data_found ora_01403 SELECT ... Into ... Statement has no data to return 11. Timeout error 12 occurred while Timeout_on_resource ora_00051 waiting for resource. Transaction_backed_out ora_00060 due to deadlock, commit failed 13. Memory error 14 occurred in Storage_Error ora_06500. Program_error ora_06501 occurs pl/sql internal error 15. Not_logged_on Ora_01012 attempted to manipulate a disconnected database 16. Login_denied ora_01017 supplied an invalid username or password at connection time

Attaching: Using Cursors

15-1: Receive cursor data using scalar variables DECLARE CURSOR emp_cursor is SELECT ename,job,sal from emp WHERE deptno=&dno; V_ename Emp.ename%type; V_sal Emp.sal%type; V_job Emp.job%type; BEGIN OPEN Emp_cursor; LOOP FETCH emp_cursor into v_ename,v_job,v_sal; EXIT when Emp_cursor%notfound; Dbms_output.put_line (' Name: ' | | v_ename| | ', post: ' | | v_job| | ', wages: ' | | V_sal); End LOOP; Close emp_cursor; End; /15-2: Receive cursor data using the PL/SQL record variable DECLARE CURSOR emp_cursor is SELECT ename,sal from emp order by Sal DESC; Emp_record Emp_cursor%rowtype; BEGIN OPEN Emp_cursor; LOOP FETCH emp_cursor into Emp_record; EXIT when Emp_cursor%notfound OR emp_cursor%rowcount>&n; Dbms_output.put_line (' Name: ' | | emp_record.ename| | ', Wages: ' | | Emp_record.sal); End LOOP; Close emp_cursor; End; /15-3: Receive cursor data using Pl/sql collection variables DECLARE CURSOR emp_cursor is SELECT ename,sal from emp WHERE lower (Job) =lower (' &job '); TYPE Emp_table_type is table of Emp_cursor%rowtype INDEX by Binary_integer; Emp_table Emp_table_type; I INT; BEGIN OPEN Emp_cursor; LOOP I:=emp_cursor%rowcount+1; FETCH emp_cursor into emp_table (i); EXIT when Emp_cursor%notfound; Dbms_output.put_line (' Name: ' | | Emp_table (i). ename| | ', Wages: ' | | Emp_table (i). Sal); End LOOP; Close emp_cursor; End; /15-4: Reference the defined cursor in the cursor for loop DECLARE CURSOR Emp_cursor is SELECT ename,hiredate from EMP to HireDate DESC; BEGIN for Emp_record in Emp_cursor LOOP dbms_output.put_line (' Name: ' | | Emp_record.ename | | ', work date: ' | | Emp_record.hiredate); EXIT when emp_cursor%rowcount=&n; End LOOP; End; /15-5: Direct reference in the cursor for loop to the subquery BEGIN to Emp_record in (SELECT ename,hiredate,rownum from emp to hiredate) loop dbms_output . Put_Line (' Name: ' | | Emp_record.ename | | ', work date: ' | | Emp_record.hiredate); EXIT when emp_record.rownum=&n; End LOOP; End; /15-6: Parameter cursor DECLARE CURSOR emp_cursor (DNO number) is SELECT ename,job from emp WHERE Deptno=dno; BEGIN for Emp_record in Emp_cursor (&dno) LOOP dbms_output.put_line (' Name: ' | | Emp_record.ename | | ', post: ' | | Emp_record.job); End LOOP; End; /15-7: Update Cursor row DECLARE CURSOR emp_cursor is SELECT ename,sal,Deptno from EMP for UPDATE; DNO int:=&no; BEGIN for Emp_record in Emp_cursor LOOP IF emp_record.deptno=dno THEN dbms_output.put_line (' Name: ' | | Emp_record.ename | | ', original salary: ' | | Emp_record.sal); UPDATE EMP SET sal=sal*1.1 WHERE Current of emp_cursor; End IF; End LOOP; End; /15-8: Delete cursor row DECLARE CURSOR emp_cursor is SELECT ename from EMP for UPDATE; Name VARCHAR2: =lower (' &name '); BEGIN for Emp_record in Emp_cursor LOOP IF Lower (emp_record.ename) =name THEN DELETE from emp WHERE Current of Emp_cursor; ELSE dbms_output.put_line (' Name: ' | | Emp_record.ename); End IF; End LOOP; End; /15-9: Use the By clause to add a row to the shared lock on a particular table. DECLARE CURSOR Emp_cursor is SELECT a.dname,b.ename to dept a JOIN EMP B on A.deptno=b.deptno for UPDATE of B.deptno; Name VARCHAR2: =lower (' &name '); BEGIN for Emp_record in Emp_cursor LOOP IF LOWER (emp_record.dname) =name THEN (' Name: ' | | Emp_record.ename); DELETE from EMP WHERE Current of Emp_cursor; End IF; End LOOP; End; /15-10: Use a cursor variable with no return type DECLARE type Ref_cursor_type is REF CURSOR; Ref_cursor Ref_cursor_type; V1 number (6); V2 VARCHAR2 (10); BEGIN OPEN ref_cursor for SELECT &col1 col1,&col2 col2 from &table WHERE &cond; LOOP FETCH ref_cursor into V1,v2; EXIT when Ref_cursor%notfound; Dbms_output.put_line (' col1= ' | | v1| | ', col2= ' | | v2); End LOOP; Close ref_cursor; End; /15-11: Use a cursor variable with a return type DECLARE type emp_cursor_type be REF cursor return emp%rowtype; Emp_cursor Emp_cursor_type; Emp_record Emp%rowtype; BEGIN OPEN emp_cursor for SELECT * from EMP WHERE deptno=&dno; LOOP FETCH emp_cursor into Emp_record; EXIT when Emp_cursor%notfound; Dbms_output.put_line (' Name: ' | | emp_record.ename| | ', Wages: ' | | Emp_record.sal); End LOOP; Close emp_cursor; End; /15-12: Extract all data using Fetch...bulk collect DECLARE CURSOR emp_cursor is SELECT * from emp WHERE LOWER (Job) =lower (' &job '); TYPE Emp_table_type is table of Emp%rowtype; Emp_table Emp_table_type; BEGIN OPEN Emp_cursor; FETCH emp_cursor BULK COLLECT into emp_table; Close emp_cursor; For I in 1. Emp_tablE.count LOOP dbms_output.put_line (' Name: ' | | Emp_table (i). ename | | ', wages: ' | | Emp_table (i). Sal); End LOOP; End; /15-13: Use the limit clause to restrict the extraction of rows DECLARE CURSOR emp_cursor is SELECT * from EMP; TYPE Emp_array_type is Varray (5) of Emp%rowtype; Emp_array Emp_array_type; BEGIN OPEN Emp_cursor; LOOP FETCH emp_cursor BULK COLLECT into Emp_array LIMIT &rows; For I in 1..emp_array. COUNT LOOP dbms_output.put_line (' Name: ' | | Emp_array (i). ename | | ', wages: ' | | Emp_array (i). Sal); End LOOP; EXIT when Emp_cursor%notfound; End LOOP; Close emp_cursor; End; /15-14: Use CURSOR expression DECLARE CURSOR dept_cursor (no number) is select A.dname,cursor (SELECT * from EMP WHERE Deptno=a.deptno ) from dept a WHERE A.deptno=no; TYPE Ref_cursor_type is REF CURSOR; Emp_cursor Ref_cursor_type; Emp_record Emp%rowtype; V_dname Dept.dname%type; BEGIN OPEN dept_cursor (&dno); LOOP FETCH dept_cursor into v_dname,emp_cursor; EXIT when Dept_cursor%notfound; Dbms_output.put_line (' Department Name: ' | | V_dname); LOOP FETCH emp_cursor into Emp_record; EXIT when EMP_CURsor%notfound; Dbms_output.put_line ('----Employee Name: ' | | Emp_record.ename | | ', post: ' | | Emp_record.job); End LOOP; End LOOP; Close dept_cursor; End; /