Oracle cursor Loop "Go"

Tag: OS uses AR for data code SP C type

There are several uses for Oracle cursor loops, as described below.

Define cursors and variables first

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

V_name VARCHAR2 (10);

V_job VARCHAR2 (10);

The first type: Using loop loop 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 immediately follow the fetch. Unnecessary data processing will be avoided. and the processing logic needs to follow the exit when. Remember to close the cursor when the loop is finished.

Second: Use the 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 makes 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 continuously.

In summary, using while to loop through cursors is the most complex method.

Third type: For loop

For V_pos in C1 loop v_name:= v_pos.ename; V_job:= V_pos.ejob; 03 ..... 04.end Loop; Visible for loop is a relatively simple and practical method. First, it will automatically open and close cursors. Resolves the annoyance that you forgot to turn the cursor on or off. Other, automatically defines a record type and declares the type of the variable, and automatically fetch the data into this variable. We need to note that v_pos this variable does not need to be declared outside of the loop, it is not necessary 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. Think of V_pos as a record variable, if you want to get a value just like a record call. As V_pos.pos_type shows, the For loop is the best way to loop a cursor. efficient, concise and safe.

Oracle cursor Loop "Go"