[Dan JIU Jian] Oracle knowledge point sorting (7) database common objects: Cursor and oraclecursor

[Dan JIU Jian] Oracle knowledge point sorting (7) database common objects: Cursor and oraclecursor
Link navigation for this series:

[Lone sword] Oracle knowledge point sorting (1) Table space and users

[Gu JIU Jian] Oracle knowledge point sorting (2) database connection

[Gu JIU Jian] Oracle knowledge point sorting (3) Import and Export

[Dan JIU Jian] Oracle knowledge point sorting (4) SQL statement DML and DDL

[Dan JIU Jian] Oracle knowledge point sorting (5) Table and View of common database objects

[Lone sword] Oracle knowledge point sorting (6) Procedure, function, and Sequence of common database objects

[Dan JIU Jian] Oracle knowledge point sorting (7) database Common Object Cursor

[Lone sword] Oracle knowledge point sorting (8) Common exceptions

[Lone sword] Oracle knowledge point sorting (9) package of common database objects

[Gu JIU Jian] Oracle knowledge point sorting (10) % type and % rowtype and common functions

5.6. cursor operation

The usage scenarios of cursors include:

A) display cursor: In function or procedure, used to obtain some values for traversal.

B) Dynamic Cursor: in procedure, used to return query results

5.6.1. Display cursor: Create a cursor in the code segment, function, and procdeure for Value

• Implicit cursor

1 declare 2 cursor myCursor is 3 select name from person; 4 vNames varchar2 (128); 5 begin 6 -- traverse cursor 7 for c in myCursor loop -- implicitly Open and Close cursor 8 vNames: = vNames | ',' | c; -- can contain complex logic 9 end loop; 10 end;

• Display cursor

1 declare 2 cursor myCursor is -- defines the cursor 3 select * from person; 4 my_c myCursor % rowtype -- defines the cursor variable 5 begin 6 open myCursor; 7 loop 8 fetch myCursor into my_c; 9 exit when c % notfound; 10/* code here */-- write complex logic 11 end loop; 12 13 Exception 14 when others then 15 close myCursor; 16 17 18 if myCursor % isopen then19 close myCursor; 20 end;

The two columns mentioned above are relatively simple. Below is an example of the online excerpt display cursor, which is very detailed:

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.

The following are three common explicit Cursor usage.
1) Set serveroutput on;

1 declare 2 --- define Cursor 3 Cursor cur_policy is 4 select cm. policy_code, cm. applicant_id, cm. period_prem, cm. bank_code, cm. bank_account 5 from t_contract_master cm 6 where cm. liability_state = 2 7 and cm. policy_type = 1 8 and cm. policy_cate in ('2', '3', '4') 9 and rownum <5 10 order by cm. policy_code desc; 11 curPolicyInfo cur_policy % rowtype; --- defines the cursor variable 12 Begin 13 open cur_policy; --- open cursor 14 Loop 15 -- deal with extraction data from DB 16 Fetch cur_policy into curPolicyInfo; 17 Exit when cur_policy % notfound; 18 19 Dbms_Output.put_line (curPolicyInfo. policy_code); 20 end loop; 21 Exception 22 when others then 23 close cur_policy; 24 Dbms_Output.put_line (Sqlerrm); 25 26 if cur_policy % isopen then 27 -- close cursor 28 close cur_policy; 29 end if; 30 end; 31 32/

2) Set serveroutput on;

 1 declare  2 　　Cursor cur_policy is  3 　　　　select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account  4 　　　　　　from t_contract_master cm  5 　　　　　　where cm.liability_state = 2  6 　　　　　　　　and cm.policy_type = 1  7 　　　　　　　　and cm.policy_cate in ('2','3','4')  8 　　　　　　　　and rownum < 5  9 　　　　　　order by cm.policy_code desc; 10 　　v_policyCode t_contract_master.policy_code%type; 11 　　v_applicantId t_contract_master.applicant_id%type; 12 　　v_periodPrem t_contract_master.period_prem%type; 13 　　v_bankCode t_contract_master.bank_code%type; 14 　　v_bankAccount t_contract_master.bank_account%type; 15 Begin 16 　　open cur_policy; 17 　　Loop 18 　　　　Fetch cur_policy into v_policyCode, 19 　　　　　　　　　　　　　　　　v_applicantId, 20 　　　　　　　　　　　　　　　　v_periodPrem, 21 　　　　　　　　　　　　　　　　v_bankCode, 22 　　　　　　　　　　　　　　　　v_bankAccount; 23 　　　　Exit when cur_policy%notfound; 24 25 　　　　Dbms_Output.put_line(v_policyCode); 26 　　end loop; 27 　　Exception 28　　　　when others then 29 　　　　　　close cur_policy; 30 　　　　Dbms_Output.put_line(Sqlerrm); 31 32 　　if cur_policy%isopen then 33 　　　　close cur_policy; 34 　　end if; 35 end; 36 /

3) Set serveroutput on;

 1 declare  2 　　Cursor cur_policy is  3 　　　　select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account  4 　　　　　　　　from t_contract_master cm  5 　　　　　　　　where cm.liability_state = 2  6 　　　　　　　　　　and cm.policy_type = 1  7 　　　　　　　　　　and cm.policy_cate in ('2','3','4')  8 　　　　　　　　　　and rownum < 5  9 　　　　　　　　order by cm.policy_code desc; 10 Begin 11 　　For rec_Policy in cur_policy loop 12 　　　　Dbms_Output.put_line(rec_policy.policy_code); 13 　　end loop; 14 　　Exception 15 　　　　when others then 16 　　　　Dbms_Output.put_line(Sqlerrm); 17 18 end; 19 20 / 

5.6.2. Dynamic cursor: Use cursor in procdeure to return query results

What is the difference between explicit Cursor and implicit Cursor:

• Ref Cursor is a data result set that can be obtained by passing parameters during running.
• The other two Cursor types (implicit Cursor and display Cursor) are static, and the data result set is determined during compilation.

5.6.2.1 define a Dynamic Cursor

1 Declare 2 --- define cursor type name 3 type cur_type is ref cursor; 4 cur_policy cur_type; 5 sqlStr varchar2 (500); 6 rec_policy t_contract_master % rowtype; 7 begin 8 --- define dynamic SQL 9 sqlStr: = 'select cm. policy_code, cm. applicant_id, cm. period_prem, cm. bank_code,
Cm. bank_account from t_contract_master cm 10 where cm. liability_state = 2 11 and cm. policy_type = 1 12 and cm. policy_cate in (2, 3, 4) 13 and rownum <5 14 order by cm. policy_code desc '; 15 --- Open Cursor 16 open cur_policy for sqlStr; 17 loop 18 fetch cur_policy into rec_policy.policy_code, rec_policy.applicant_id, rec_policy.period_prem,
Rec_policy.bank_code, rec_policy.bank_account; 19 exit when cur_policy % notfound; 20 21 response ('policy _ code: '| rec_policy.policy_code); 22 23 end loop; 24 close cur_policy; 25 26 end; 27/

In addition, when defining a package, you can define the Dynamic Cursor type in the header and use it in the package body, for example, in procedure, to define the return parameter type. For details, see the operation of package.