Cursor in Oracle Database

A total of two cursors are described for an efficient use of cursors cursor ,sys_refcursor, bulk collect

1, cursor cursor use

[SQL]View Plain copy print?

2. / * Simple cursor cursor
4. There is a name field in the *students table, you can change the other table test
6. */
8. --Definition
10. declare
12. --Define cursor and assign value (is cannot be used separately from cursor)
14. cursor stus_cur is select * from students;
16. --Define RowType
18. Cur_stu Students%rowtype;
20. / * Start execution * /
22. begin
24. --Open cursor
26. Open stus_cur;
28. --loop cycle
30. Loop
32. --Cycle conditions
34. exit when stus_cur%notfound;
36. --Cursor value assignment to RowType
38. fetch stus_cur into cur_stu;
40. --Output
42. dbms_output.put_line (cur_stu. Name);
44. --end loop
46. end loop;
48. --close cursor
50. close stus_cur;
52. / * End execution * /
54. end;

/* Simple cursor Cursor *students table contains the Name field, you can change the other table test */--definition Declare--Define the cursor and assign the value (is cannot be used separately from the cursor) cursor stus_cur is select * from S tudents; --Define RowType cur_stu Students%rowtype; /* Start execution */BEGIN   -Open cursor   open stus_cur;      --loop loop      Loops         --loop condition        exit when Stus_cur%notfound;        --The value of the cursor is assigned to RowType        fetch stus_cur into Cur_stu;        --Output        dbms_output.put_line (cur_stu.name);      --end loop        ;    --Closing the cursor close     stus_cur;  /* Finish execution */end;

Execution Results

[SQL]View Plain copy print?

2. sql> declare
4. 2 --Define cursor and assign value (is cannot be used separately from cursor)
6. 3 cursor stus_cur is select * from students;
8. 4 --Define RowType
10. 5 cur_stu Students%rowtype;
12. 6/* Start execution * /
14. 7 begin
16. 8 --Open cursor
18. 9 Open stus_cur;
20. Ten --loop cycle
22. Loop
24. -- cyclic conditions
26. exit when stus_cur%notfound;
28. -- cursor value is assigned to RowType
30. fetch stus_cur into cur_stu;
32. --Output
34. dbms_output.put_line (cur_stu. Name);
36. - -end cycle
38. end loop;
40. - -Close the cursor
42. close stus_cur;
44. 22/* End execution * /
46. The end;
48. /
52. Yang too
54. Guo Jing
56. pay the Commissar .
58. Liuzhifei
60. Jiang Feng
62. let me do it.
64. Ren Ying
66. make the fox rush
68. Wei a Smile
70. Zhang Mowgli
72. Dawn
74. Sheson
76. Little Dragon Girl
78. Ouyang Fung
80. Ouyang Fung

Sql> declare  2   --Define a cursor and assign a value (is cannot be used separately from the cursor)  3   cursor Stus_cur is a SELECT * from students;  4   --Define RowType  5   cur_stu students%rowtype;  6   /* Start execution */  7   begin  8     --Open cursor  9     open stus_cur;        --loop Loop        one Loop 12          --Cycle condition of          exit when Stus_cur%notfound;-          -the cursor value is assigned to RowType          fetch stus_cur into Cur_stu;          --Output          dbms_output.put_line (cur_stu.name);--        end loop        ;      Close     Stus_ Cur    */* End execution *   /finish;  /Yang over Guo Jingxian commissar Liuzhifei Jiang Feng Ren ying to make Fox Chong Wei smile Zhang Mowgli Dawn Shesson female Ouyang Fung Ouyang Fung

2.sys_refcursor cursor Use

[SQL]View Plain copy print?

2. /*
4. * Cursor Name: Sys_refcursor
6. * Pay special attention to the assignment method: for
8. * With the above repeating content is not in the narrative
10. */
12. declare
14. stu_cur sys_refcursor;
16. Stuone Students%rowtype;
20. begin
22. --This sentence is assigned
24. Open stu_cur for select * from students;
26. --fetch assignment to RowType
28. fetch stu_cur into stuone;
32. Loop
34. dbms_output.put_line (stuone. Name| | "' || Stuone.hobby);
36. fetch stu_cur into stuone;
38. exit when stu_cur%notfound;
40. end loop;
42. end;

/* Cursor Name: Sys_refcursor * Pay special attention to the assignment method: for * with the repetition of the content is not described */declare   stu_cur sys_refcursor;   Stuone Students%rowtype;      Begin     -This sentence is assigned in the form of     open stu_cur for select * from students;     --fetch assignment to RowType     fetch stu_cur into Stuone;          Loop        dbms_output.put_line (stuone.name| | ' ' | | Stuone.hobby);       Fetch stu_cur into Stuone;       Exit when Stu_cur%notfound;     End Loop;   End

Execution Results

[SQL]View Plain copy print?

2. sql>/*
4. 2 * Cursor name: Sys_refcursor
6. 3 * Pay special attention to the assignment method: for
8. 4 * With above repeating content not in narration
10. 5 * /
12. 6 Declare
14. 7 stu_cur sys_refcursor;
16. 8 Stuone Students%rowtype;
18. 9
20. ten begin
22. one --the value of this sentence for
24. Open stu_cur for SELECT * from students;
26. --fetch assignment to RowType
28. fetch stu_cur into stuone;
30. the
32. Loop
34. dbms_output.put_line (stuone. Name| | "' || Stuone.hobby);
36. fetch stu_cur into stuone;
38. exit when stu_cur%notfound;
40. end loop;
42. The end;
44. /
48. Yang over protect the little Dragon girl
50. Guo Jing cultivation of descending dragon 18 Palm
52. pay the Commissar to see picture
54. Liuzhifei Programming Write code
56. Jiang Feng Programming code writing
58. Let me do the work of practicing.
60. Ren Ying sightseeing Tour
62. To make the fox rush the man.
64. Wei a smile to suck up people snow
66. Zhang Mowgli Practice
68. Dawn Bath
70. Sheson lifelong study of dragon Slayer Sword
72. Dragon girl cultivation herself Heart Sutra
74. Ouyang Fung See picture

sql>/*  2   * cursor name: sys_refcursor  3   * Pay special attention to the assignment method:  for 4   * with the above repeating content is not in narrative  5 */  6  DECLARE  7     stu_cur sys_refcursor;  8     stuone Students%rowtype;  9     begin one       -this sentence is assigned for the       open stu_cur for select * from students;       --fetch to RowType 14< C20/>fetch stu_cur into Stuone;       Loop         dbms_output.put_line (stuone.name| | ' ' | | Stuone.hobby);         fetch stu_cur into Stuone, exit when         Stu_cur%notfound;       end loop;     end;  /Yang over protect dragon girl Guo Jing Cultivation of the Dragon 18 to pay the Commissar to see picture Liuzhifei programming code to write code Jiang Wind program to write codes for any of my line of cultivation of the sword to make a visit to the Fox rushed to the Knight Righteousness Wei a smile suction people snow Zhang Mowgli practice Dawn Bath Sheson lifelong research Dragon knife long female cultivation herself heart by Ouyang Fung See picture

supplement a cyclic condition

[SQL]View Plain copy print?

2. declare
6. stu_cur sys_refcursor;
8. Stuone Students%rowtype;
12. begin
14. Open stu_cur for select * from Students
16. fetch stu_cur into stuone;
18. --pay special attention to changes in cycle conditions
20. --This condition is found in the loop
22. --different from the previous NotFound
24. While Stu_cur%found Loop
26. dbms_output.put_line (stuone. Name| | "' || Stuone.hobby);
28. fetch stu_cur into stuone;
30. end loop;
32. end;

DECLARE      stu_cur sys_refcursor;   Stuone Students%rowtype;      Begin     Open Stu_cur for select * from students;     Fetch stu_cur into Stuone;     --pay particular attention to the change in cyclic conditions     -this condition is found in the loop     -unlike the previous notfound while     Stu_cur%found loop        dbms_output.put_line ( stuone.name| | ' ' | | Stuone.hobby);       Fetch stu_cur into Stuone;     End Loop;   End

--normal fetch into

[SQL]View Plain copy print?

1. /* Normal way */
2. declare
3. cursor myemp_cur is Select * from myemp;
5. v_myemp myemp%rowtype;
7. begin
8. open m Yemp_cur;
9. fetch myemp_cur into v_myemp;
10. while Myemp_cur%found loop
11. dbms_output.put_line (v_myemp.ename);
12. fetch myemp_cur into v_myemp;
13. end loop;
14. end ;

/* Normal way */declarecursor myemp_cur is select * from Myemp;v_myemp myemp%rowtype;begin  open myemp_cur;  Fetch myemp_cur into v_myemp;   While Myemp_cur%found Loop    dbms_output.put_line (v_myemp.ename);    Fetch myemp_cur into v_myemp;  End Loop;end;

--efficient bulk collect

[SQL]View Plain copy print?

2. / * Efficient bulk collect for* /
4. declare
6. cursor myemp_cur
8. is select * from Myemp;
12. type Myemp_tab is table of myemp%rowtype;
16. myemp_rd Myemp_tab;
20. begin
22. Open myemp_cur;
24. Loop
26. fetch myemp_cur bulk collect into myemp_rd limit;
28. For i in 1..myemp_rd. Count Loop
30. dbms_output.put_line (' name: '| | MYEMP_RD (i). ename);
32. end loop;
34. exit when myemp_cur%notfound;
38. end loop;
40. end;

Cursor in Oracle Database