Oracle cursors three kinds of loops ____oracle

First define cursors and variables

CURSOR C1 is SELECT ename,ejob from emp WHERE deptno=10;

V_name VARCHAR2 (10);

V_job VARCHAR2 (10);

First: Using loop loops
Open C1;
Loop
Fetch C1 into v_name,v_job;
Exit when C1%notfound;
......
End Loop
Colse C1;
It is important to note that the exit when statement must be immediately followed by the fetch. Must avoid redundant data processing.
and the processing logic needs to follow the exit when.
Remember to close the cursor after the loop completes.

Second: Use a while loop.

Open C1;

Fetch C1 into v_name,v_job;
While C1%found loop
......
Fetch C1 into v_name,v_job;
End Loop;
Close C1;

Note that two FETCH statements are used here.

The first FETCH statement assigns the first row of the cursor to the related variable, and the second FETCH statement is to have the cursor point to the next record.

If there is no second statement, it is a dead loop, and the code inside the loop is executed non-stop.

In summary, using a while to iterate over a cursor is the most complex method.

Third Kind: For loop

For V_pos in C1 loop
V_name:= V_pos.ename;
V_job:= V_pos.ejob;
03...
04.end Loop;
A For loop is a relatively simple and practical method.
First, it will automatically open and close cursors. Resolves the annoyance of your forgetting to turn on or off the cursor.
Other, automatically defines a record type and declares the variable of that type, and automatically fetch the data into this variable.
We need to be aware that v_pos this variable does not need to be declared outside the loop, without having to specify a data type for it.
It should be a record type, and the specific structure is determined by the cursor.
The scope of this variable is only in the loop body.
It's OK to think of V_pos as a record variable if you want to get a value that's like calling a record.
such as V_pos.pos_type
This shows that the For loop is the best way to loop a cursor. efficient, concise and safe.

Four. Use subqueries directly in the cursor for loop

BEGIN

For Emp_record in

(SELECT ename,sal from EMP) LOOP

Dbms_output.put_line (Emp_record.ename);

End LOOP;

End;