Introduction to Oracle Cursor

Concept 1
A cursor is a memory Workspace of SQL, which is defined by the system or user as a variable. The cursor is used to temporarily store data blocks extracted from the database. In some cases, you need to transfer the data from the table stored in the disk to the computer memory for processing, and finally display the processing results or write them back to the database. In this way, the data processing speed will increase; otherwise, frequent disk data exchanges will reduce the efficiency.
Type 2
There are three types of Cursor: Implicit Cursor, explicit Cursor, and Ref Cursor (Dynamic Cursor ).
1. Implicit Cursor:
1). For Select... INTO... Statement. Only one piece of data can be obtained from the database at a time. For this type of DML SQL statement, it is an implicit Cursor. For example, Select/Update/Insert/Delete.
2) Role: You can use the implicit Cusor attribute to understand the operation status and results, so as to control the process. Cursor attributes include:
SQL % ROWCOUNT integer indicates the number of rows successfully executed by the DML statement
If the Boolean value of SQL % FOUND is TRUE, insertion, deletion, update, or single-row query is successful.
The return value of SQL % NOTFOUND is the opposite of that of SQL % FOUND.
SQL % ISOPEN Boolean DML is true during execution and false after execution
3) Implicit Cursor is automatically enabled and disabled by the system.
Below is a Sample:
Java code
Set Serveroutput on;
 
Begin
Update t_contract_master set liability_state = 1 where policy_code = '20140901 ';

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;

Run in PL/SQL:
Java code
SQL>

The policy is updated failed.

PL/SQL procedure successfully completed

2. Explicit Cursor:
(1) Explicit Cursor is required for extracting multiple rows of data from the database. Explicit Cursor attributes include:
Type of the attribute return value of the cursor
% ROWCOUNT integer to obtain the number of data rows returned by the FETCH statement
The latest FETCH statement of the % FOUND Boolean Type Returns a row of data, which is true. Otherwise, the return value is false.
The return value of % NOTFOUND is the opposite of that of % FOUND.
% ISOPEN: the value of the Boolean cursor that has been opened is true. Otherwise, the value is false.

(2) There are four steps to apply an explicit cursor:

Define the Cursor --- Cursor [Cursor Name] IS;

Open the Cursor --- Open [Cursor Name];

Operation data --- Fetch [Cursor name]

Close the Cursor --- Close [Cursor Name]. This Step cannot be omitted.
(3) The following are three common explicit Cursor usage methods.
1)
Java code
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; --- defines the 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;
 
/

2)
Java code
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;
/

3)
Java code
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;
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;
 
/

Run pl/SQL:

Java code
SQL>

8780203932
8780203227
8780203218
8771289268

PL/SQL procedure successfully completed

3. Ref Cursor (Dynamic Cursor ):
1) The difference between explicit Cursor and implicit Cursor: Ref Cursor can obtain the data result set by passing parameters during running. The other two Cursor types are static, and the data result set is determined during compilation.
2) Use of Ref cursor:

Type [Cursor type name] is ref cursor

Define dynamic SQL statements

Open cursor

Operation data --- Fetch [Cursor name]

Close Cursor
Below is a Sample:

Java code
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 exceptions

Java code
Incorrect name: the error code is incorrect.
CURSOR_ALREADY_OPEN ORA_06511
INVALID_CURSOR ORA_01001 tries to use an unopened cursor
DUP_VAL_ON_INDEX ORA_00001 Save the duplicate value to the column with the unique index Constraint
ZERO_DIVIDE ORA_01476 Division Error with zero Division
INVALID_NUMBER ORA_01722
ROWTYPE_MISMATCH ORA_06504 the main variable is incompatible with the cursor type
VALUE_ERROR ORA_06502 conversion, truncation, or arithmetic operation Error
TOO_MANY_ROWS ORA_01422 SELECT... INTO... Statement returns more than one row of data
NO_DATA_FOUND ORA_01403 SELECT... INTO... No data returned by the statement
TIMEOUT_ON_RESOURCE ORA_00051 timeout error while waiting for resources
TRANSACTION_BACKED_OUT ORA_00060 failed to submit due to deadlock
STORAGE_ERROR ORA_06500 memory error
A pl/SQL internal error occurs in PROGRAM_ERROR ORA_06501.
NOT_LOGGED_ON ORA_01012 attempted to operate the database not connected
LOGIN_DENIED ORA_01017 the user name or password is invalid during connection.