Tips for batch fetch in Oracle.

In general, there are not many chances to use fetch in batches, but we 'd better get familiar with this feature provided by Oracle, and maybe it will be used at any time.

 

Below isCodeFragment: Declare Cursor C1 is select * From t_depart; V_depart t_depart % rowtype; Type v_code_type is table of t_0000.depart_code % type; V_code v_code_type; Type v_name_type is table of t_0000.depart_name % type; V_name v_name_type; Begin Open C1; Fetch C1 bulk collect into v_code, v_name; For I in 1 .. v_code.count Loop Dbms_output.put_line (v_code (I) | v_name (I )); End loop; Close C1; End;

 

Through the example above, we can find that if there are many columns, defining a set for each column seems complicated. You can combine the set with % rowtype to simplify the process.Program!

 

Declare Cursor C1 is select * From t_depart; Type v_depart_type is table of t_depart % rowtype; V_depart v_depart_type; Begin Open C1; Fetch C1 bulk collect into v_depart; For I in 1 .. v_).count Loop Dbms_output.put_line (v_depart (I). depart_code | V_depart (I). depart_name ); End loop; Close C1; End;

 

When outputting results, you can use both the Count attribute of the Set and the first and last attributes. When referencing content of the % rowtype, You Need To note v_depart (I ). depart_code, rather than v_pai.depart_code (I), is of course not written in this way, even if it makes sense.

Declare Cursor C1 is select * From t_depart; Type v_depart_type is table of t_depart % rowtype; V_depart v_depart_type; Begin Open C1; Fetch C1 bulk collect into v_depart; For I in v_mongo.first... v_mongo.last Loop Dbms_output.put_line (v_depart (I). depart_code | V_depart (I). depart_name ); End loop; Close C1; End;