Use of Oracle display cursors and cursor for loop _oracle

Here's how to use an implicit cursor, and under what circumstances, to use a display cursor:

1. Query returns a single line of records → implicit cursors;

2. Query returns multiple-line records and process-by-row → explicit cursors

--Display Cursor properties

Declare
CURSOR cur_emp is a SELECT * from EMP;
Row_emp Cur_emp%rowtype;
BEGIN
OPEN cur_emp;
FETCH cur_emp into row_emp;
While Cur_emp%found
loop
dbms_output.put_line (row_emp.empno| | ' ----'|| Row_emp.ename);
FETCH cur_emp into row_emp;
End LOOP;
Close cur_emp;
End;

--Modify data with explicit cursors (raise 1000 for all department managers)

DECLARE
CURSOR emp_cur is
SELECT empno,ename,sal from emp WHERE job= ' MANAGER ' for UPDATE;
Emp_row Emp_cur%rowtype;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur into Emp_row;
IF emp_cur%notfound THEN
EXIT;
ELSE
UPDATE emp SET sal=sal+1000 WHERE Current of emp_cur;
End IF;
End LOOP;
COMMIT;
Close emp_cur;
End;

Attention

1. Oracle throws a invalid_cursor error (ORA-01001) If the cursor property is used before or after it is turned on;

2. If the result set is empty after the first fetch,%found=false,%notfound=true,%rowcount=0;

3, if the bulk COLLECT is used, then the%rowcount value may not be 0 or 1, and in fact he returns the number of rows extracted to the associated collection.

--Cursor for loop (pay 1000 for all department managers)

DECLARE
CURSOR emp_cur is
SELECT empno,ename,sal from emp WHERE job= ' MANAGER ' for UPDATE;
BEGIN for
Emp_row in emp_cur
LOOP
UPDATE emp SET sal=sal-1000 WHERE Current of emp_cur;
End LOOP;
COMMIT;
End;

--we can see that the cursor for loop does a good job of simplifying the development of cursors, we don't need open, fetch, and close statements, we don't need to detect the last record with the%found attribute, all of which Oracle implicitly helped us.

--Give the manager a 5000 raise, the other 1000.

DECLARE
CURSOR emp_cur is
SELECT * from EMP for UPDATE;
BEGIN for
 Emp_row in emp_cur
 LOOP
 IF emp_row.job= ' MANAGER ' THEN
 UPDATE emp SET sal=sal+5000 WHERE Current of Emp_cur;
 ELSE
 UPDATE emp SET sal=sal+1000 WHERE Current of emp_cur;
 End IF;
 End LOOP;
 End;

Here's an introduction to Oracle Cursor cursor simple use

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

1, cursor cursor use

/* Simple cursor cursor 
 *students table has a Name field, you can switch to other tables test/ 
-Define 
Declare- 
 define cursors and assign values (is cannot be used separately from cursor) 
 cursor Stus_cur is select * from students; 
 --Define RowType 
 cur_stu students%rowtype; 
 /* Start 
 -Open cursor 
 open stus_cur; 
  --loop cyclic 
  loop 
  --Cyclic 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); 
  --Ends loops end loop 
  ; 
 --Closes the cursor close 
 stus_cur; 
 /* End Execution */ 
 

Execution results

Sql> declare 
  --defines cursors and assigns values (is cannot be used separately from cursor) 
  cursor The stus_cur is select * from students; 
  --Define RowType 
  cur_stu students%rowtype; 
  /* Start 
  -Open cursor 
  open stus_cur; 
   --loop cyclic 
   loop 
   --Cyclic 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); 
   --Ends loops end loop 
   ; 
  --Closes the cursor close 
  stus_cur; 
  /* End Execution * * 
 ; 
 / 
 
Yang 
Guo Jing 
pay commissar 
Liuzhifei 
River Wind as 
I do let the 
Fox Chong 
Wei a smile Zhang Mowgli 
Dawn 
Sheson 
Little Dragon Girl 
Ouyang Fung 

2, sys_refcursor cursor use

 
 * * Cursor name: sys_refcursor 
 * Special attention to the assignment method: 
 for * with duplicate content not in the narration/ 
declare 
 stu_cur sys_refcursor; 
 Stuone Students%rowtype; 
  
 Begin 
  -This assignment is for the 
  open stu_cur for the 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; 
 

Execution results

sql> * 
  * cursor Name: sys_refcursor 
  * Special attention to assignment method: 
  for * with duplicate content not in narration/ 
 declare 
  stu_cur sys_refcursor ; 
  Stuone Students%rowtype; 
  Begin 
  -This assignment is for the 
  open stu_cur for the 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; 
 / 
Yang over the protection of Little Dragon female 
Guo Jing 18 palm 
pay Commissar to see the 
story-book Liuzhifei programming write code 
Jiang Feng programming code as long as 
I can cultivate the 
martial spirit of the 
Fox rush to do c28/> Wei a smile to absorb people snow 
Zhang Mowgli Practice 
Dawn Bath 
Sheson lifelong study of Dragon Sword 
female cultivation of the heart 

To supplement a cyclic condition

DECLARE 
 stu_cur sys_refcursor; 
 Stuone Students%rowtype; 
 Begin 
  Open Stu_cur for select * from students; 
  Fetch stu_cur into Stuone; 
  --Pay special attention to the change of the cyclic condition 
  --This condition is found in the loop- 
  -the same while 
  Stu_cur%found loop Dbms_output.put_line as the last NotFound 
  ( stuone.name| | ' '|| Stuone.hobby); 
  Fetch stu_cur into Stuone; 
  End Loop; 
 

--ordinary fetch into

/* Common Mode 
* 
/DECLARE CURSOR 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; 

--efficient bulk Collect

/* Efficient BULK collect for*/DECLARE CURSOR myemp_cur is select * from Myemp; 
Type Myemp_tab is table of Myemp%rowtype; 
Myemp_rd Myemp_tab; 
 Begin open Myemp_cur; 
 Loop fetch myemp_cur Bulk collect into myemp_rd limit 20; For i in 1..myemp_rd.count loop dbms_output.put_line (' Name: ' | | 
 MYEMP_RD (i). ename); 
 End Loop; 
 Exit when Myemp_cur%notfound; 
End Loop; End