Introduction and use of cursor in Oracle

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:
Set serveroutput on; </P> <p> begin <br/> Update t_contract_master set liability_state = 1 where policy_code = '20140901 '; </P> <p> If SQL % found then <br/> dbms_output.put_line ('the policy is updated successfully. '); <br/> commit; <br/> else <br/> dbms_output.put_line ('the policy is updated failed. '); <br/> end if; </P> <p> end; </P> <p>/

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.

Set serveroutput on; </P> <p> declare <br/> --- define cursor <br/> cursor cur_policy is <br/> select cm. policy_code, CM. applicant_id, CM. period_prem, CM. bank_code, CM. bank_account <br/> from t_contract_master cm <br/> where cm. liability_state = 2 <br/> and CM. policy_type = 1 <br/> and CM. policy_cate in ('2', '3', '4') <br/> and rownum <5 <br/> order by cm. policy_code DESC; <br/> curpolicyinfo cur_policy % rowtype; --- defines the cursor variable <br/> begin <br/> open cur_policy; --- Open cursor <br/> loop <br/> -- deal with extraction data from DB <br/> fetch cur_policy into curpolicyinfo; <br/> exit when cur_policy % notfound; </P> <p> dbms_output.put_line (curpolicyinfo. policy_code); <br/> end loop; <br/> exception <br/> when others then <br/> close cur_policy; <br/> dbms_output.put_line (sqlerrm ); </P> <p> If cur_policy % isopen then <br/> -- close cursor <br/> close cur_policy; <br/> end if; <br/> end; </P> <p>/<br/> ---------------------- </P> <p> set serveroutput on; </P> <p> declare <br/> cursor cur_policy is <br/> select cm. policy_code, CM. applicant_id, CM. period_prem, CM. bank_code, CM. bank_account <br/> from t_contract_master cm <br/> where cm. liability_state = 2 <br/> and CM. policy_type = 1 <br/> and CM. policy_cate in ('2', '3', '4') <br/> and rownum <5 <br/> order by cm. policy_code DESC; <br/> v_policycode percent % type; <br/> v_applicantid percent % type; <br/> v_periodprem percent % type; <br/> v_bankcode t_contract_master.bank_code % type; <br/> v_bankaccount t_contract_master.bank_account % type; <br/> begin <br/> open cur_policy; <br/> loop <br/> fetch cur_policy into v_policycode, <br/> v_applicantid, <br/> v_periodprem, <br/> v_bankcode, <br/> v_bankaccount; <br/> exit when cur_policy % notfound; </P> <p> dbms_output.put_line (v_policycode); <br/> end loop; <br/> exception <br/> when others then <br/> close cur_policy; <br/> dbms_output.put_line (sqlerrm); </P> <p> If cur_policy % isopen then <br/> close cur_policy; <br/> end if; <br/> end; <br/>/<br/> -------------------------------- <br/> set serveroutput on; </P> <p> declare <br/> cursor cur_policy is <br/> select cm. policy_code, CM. applicant_id, CM. period_prem, CM. bank_code, CM. bank_account <br/> from t_contract_master cm <br/> where cm. liability_state = 2 <br/> and CM. policy_type = 1 <br/> and CM. policy_cate in ('2', '3', '4') <br/> and rownum <5 <br/> order by cm. policy_code DESC; <br/> begin <br/> for rec_policy in cur_policy loop <br/> dbms_output.put_line (rec_policy.policy_code); <br/> end loop; <br/> exception <br/> when others then <br/> dbms_output.put_line (sqlerrm); </P> <p> end; </P> <p>/

 

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:

Set serveroutput on; </P> <p> declare <br/> --- define cursor type name <br/> type cur_type is ref cursor; <br/> cur_policy cur_type; <br/> sqlstr varchar2 (500); <br/> rec_policy t_contract_master % rowtype; <br/> begin <br/> --- define dynamic SQL <br/> sqlstr: = 'select cm. policy_code, CM. applicant_id, CM. period_prem, CM. bank_code, CM. bank_account from t_contract_master cm <br/> where cm. liability_state = 2 <br/> and CM. policy_type = 1 <br/> and CM. policy_cate in (2, 3, 4) <br/> and rownum <5 <br/> order by cm. policy_code DESC '; <br/> --- Open cursor <br/> open cur_policy for sqlstr; <br/> loop <br/> fetch cur_policy into rec_policy.policy_code, expires, expires, rec_policy.bank_code, rec_policy.bank_account; <br/> exit when cur_policy % notfound; </P> <p> dbms_output.put_line ('policy _ code: '| rec_policy.policy_code ); </P> <p> end loop; <br/> close cur_policy; </P> <p> end; <br/>/

4. Common exceptions
1. the error code is incorrectly classified <br/> 2. cursor_already_open ora_06511 tries to open an opened cursor. <br/> 3. invalid_cursor ora_01001 tries to use a cursor that is not opened <br/> 4. dup_val_on_index ora_00001 Save the duplicate value to the column with the unique index constraint <br/> 5. zero_divide ora_01476 Division Error with Zero Divisor <br/> 6. invalid_number ora_01722 attempts to convert invalid characters. <br/> 7. rowtype_mismatch ora_06504 the main variable is incompatible with the cursor type <br/> 8. value_error ora_06502 conversion, truncation, or arithmetic operation error <br/> 9. too_many_rows ora_01422 select... Into... Statement returns more than one row of Data <br/> 10. no_data_found ora_01403 select... Into... No data returned by the statement <br/> 11. timeout_on_resource ora_00051 timeout error while waiting for resources <br/> 12. transaction_backed_out ora_00060 failed to submit due to deadlock <br/> 13. storage_error ora_06500 memory error <br/> 14. program_error ora_06501 internal PL/SQL error <br/> 15. not_logged_on ora_01012 attempt to operate the database that is not connected <br/> 16. login_denied ora_01017 the user name or password is invalid during connection.

 

Appendix: Use a cursor

<Br/> 15-1: Use scalar variables to receive cursor data <br/> declare <br/> cursor emp_cursor is <br/> select ename, job, sal from EMP where deptno = & DNO; <br/> v_ename EMP. ename % type; <br/> v_sal EMP. sal % type; <br/> v_job EMP. job % type; <br/> begin <br/> open emp_cursor; <br/> loop <br/> fetch emp_cursor into v_ename, v_job, v_sal; <br/> exit when emp_cursor % notfound; <br/> dbms_output.put_line ('name: '| v_ename |', position: '| v_job |', salary: '| v_sal); <br/> end loop; <br/> close emp_cursor; <br/> end; <br/>/<br/> 15-2: use PL/SQL to record the variable to receive cursor data <br/> declare <br/> cursor emp_cursor is <br/> select ename, Sal from EMP order by Sal DESC; <br/> emp_record emp_cursor % rowtype; <br/> begin <br/> open emp_cursor; <br/> loop <br/> fetch emp_cursor into emp_record; <br/> exit when emp_cursor % notfound or emp_cursor % rowcount> & N; <br/> dbms_output.put_line ('name: '| emp_record.ename | <br/>', salary: '| emp_record.sal); <br/> end loop; <br/> close emp_cursor; <br/> end; <br/>/<br/> 15-3: use PL/SQL collection variables to receive cursor data <br/> declare <br/> cursor emp_cursor is select ename, Sal from EMP <br/> where lower (job) = lower ('& job'); <br/> type emp_table_type is table of emp_cursor % rowtype <br/> index by binary_integer; <br/> emp_table emp_table_type; <br/> I int; <br/> begin <br/> open emp_cursor; <br/> loop <br/> I: = emp_cursor % rowcount + 1; <br/> fetch emp_cursor into emp_table (I); <br/> exit when emp_cursor % notfound; <br/> dbms_output.put_line ('name: '| emp_table (I ). ename | <br/> ', salary:' | emp_table (I ). sal); <br/> end loop; <br/> close emp_cursor; <br/> end; <br/>/<br/> 15-4: reference a defined cursor in a cursor for loop <br/> declare <br/> cursor emp_cursor is select ename, hiredate from EMP <br/> order by hiredate DESC; <br/> begin <br/> for emp_record in emp_cursor loop <br/> dbms_output.put_line ('name: '| emp_record.ename <br/> |', working date: '| emp_record.hiredate); <br/> exit when emp_cursor % rowcount = & N; <br/> end loop; <br/> end; <br/>/<br/> 15-5: directly reference the subquery In the cursor for loop <br/> begin <br/> for emp_record in (select ename, hiredate, rownum from EMP <br/> order by hiredate) loop <br/> dbms_output.put_line ('name: '| emp_record.ename <br/> |', working date: '| emp_record.hiredate); <br/> exit when emp_record.rownum = & N; <br/> end loop; <br/> end; <br/>/<br/> 15-6: parameter cursor <br/> declare <br/> cursor emp_cursor (DNO number) is <br/> select ename, job from EMP where deptno = DNO; <br/> begin <br/> for emp_record in emp_cursor (& DNO) loop <br/> dbms_output.put_line ('Name: '| emp_record.ename <br/> |', position: '| emp_record.job); <br/> end loop; <br/> end; <br/>/<br/> 15-7: update the cursor row <br/> declare <br/> cursor emp_cursor is <br/> select ename, Sal, deptno from EMP for update; <br/> dno int: = & No; <br/> begin <br/> for emp_record in emp_cursor loop <br/> If emp_record.deptno = DNO then <br/> dbms_output.put_line ('Name: '| emp_record.ename <br/> |', original salary: '| emp_record.sal); <br/> Update EMP set sal = Sal * 1.1 where current of emp_cursor; <br/> end if; <br/> end loop; <br/> end; <br/>/<br/> 15-8: delete a cursor row <br/> declare <br/> cursor emp_cursor is <br/> select ename from EMP for update; <br/> name varchar2 (10 ): = lower ('& name'); <br/> begin <br/> for emp_record in emp_cursor loop <br/> if lower (emp_record.ename) = Name then <br/> Delete from EMP where current of emp_cursor; <br/> else <br/> dbms_output.put_line ('name: '| emp_record.ename ); <br/> end if; <br/> end loop; <br/> end; <br/>/<br/> 15-9: use the of clause to add a row share lock to a specific table. <br/> declare <br/> cursor emp_cursor is <br/> select. dname, B. ename from dept a join EMP B <br/> On. deptno = B. deptno <br/> for update of B. deptno; <br/> name varchar2 (10): = lower ('& name '); <br/> begin <br/> for emp_record in emp_cursor loop <br/> if lower (emp_record.dname) = Name then <br/> dbms_output.put_line ('Name: '| emp_record.ename); <br/> Delete from EMP where current of emp_cursor; <br/> end if; <br/> end loop; <br/> end; <br/>/<br/> 15-10: Use a cursor variable without a return type <br/> declare <br/> type ref_cursor_type is ref cursor; <br/> ref_cursor ref_cursor_type; <br/> V1 number (6); <br/> V2 varchar2 (10 ); <br/> begin <br/> open ref_cursor for <br/> Select & col1 col1, & col2 col2 from & table where & cond; <br/> loop <br/> fetch ref_cursor into V1, V2; <br/> exit when ref_cursor % notfound; <br/> dbms_output.put_line ('col1 = '| V1 |', col2 = '| V2); <br/> end loop; <br/> close ref_cursor; <br/> end; <br/>/<br/> 15-11: use a cursor variable with a return type <br/> declare <br/> type emp_cursor_type is ref cursor return EMP % rowtype; <br/> emp_cursor emp_cursor_type; <br/> emp_record EMP % rowtype; <br/> begin <br/> open emp_cursor for select * from EMP <br/> where deptno = & DNO; <br/> loop <br/> fetch emp_cursor into emp_record; <br/> exit when emp_cursor % notfound; <br/> dbms_output.put_line ('Name: '| emp_record.ename | <br/>', salary: '| emp_record.sal); <br/> end loop; <br/> close emp_cursor; <br/> end; <br/>/<br/> 15-12: Use fetch... bulk collect extracts all data <br/> declare <br/> cursor emp_cursor is <br/> select * from EMP where lower (job) = lower ('& job '); <br/> type emp_table_type is table of EMP % rowtype; <br/> emp_table emp_table_type; <br/> begin <br/> open emp_cursor; <br/> fetch emp_cursor bulk collect into emp_table; <br/> close emp_cursor; <br/> for I in 1 .. emp_table.count loop <br/> dbms_output.put_line ('name: '| emp_table (I ). ename <br/> | ', salary:' | emp_table (I ). sal); <br/> end loop; <br/> end; <br/>/<br/> 15-13: use the limit clause to limit the number of extracted rows <br/> declare <br/> cursor emp_cursor is select * from EMP; <br/> type emp_array_type is varray (5) of EMP % rowtype; <br/> emp_array emp_array_type; <br/> begin <br/> open emp_cursor; <br/> loop <br/> fetch emp_cursor bulk collect into emp_array Limit & rows; <br/> for I in 1 .. emp_array.count loop <br/> dbms_output.put_line ('name: '| emp_array (I ). ename <br/> | ', salary:' | emp_array (I ). sal); <br/> end loop; <br/> exit when emp_cursor % notfound; <br/> end loop; <br/> close emp_cursor; <br/> end; <br/>/<br/> 15-14: Use the cursor expression <br/> declare <br/> cursor dept_cursor (no number) is <br/> select. dname, cursor (select * from EMP <br/> where deptno =. deptno) <br/> from dept a where. deptno = no; <br/> type ref_cursor_type is ref cursor; <br/> emp_cursor ref_cursor_type; <br/> emp_record EMP % rowtype; <br/> v_dname Dept. dname % type; <br/> begin <br/> open dept_cursor (& DNO); <br/> loop <br/> fetch dept_cursor into v_dname, emp_cursor; <br/> exit when dept_cursor % notfound; <br/> dbms_output.put_line ('department name: '| v_dname); <br/> loop <br/> fetch emp_cursor into emp_record; <br/> exit when emp_cursor % notfound; <br/> dbms_output.put_line ('---- employee name:' | emp_record.ename <br/> | ', position: '| emp_record.job); <br/> end loop; <br/> close dept_cursor; <br/> end; <br/>/