Oracle cursor Basics

What is a cursor:

A cursor is a swimming cursor.

Description in the database language: the cursor is the entity mapped to the position of a row of data in the result set. With the cursor, users can access any row of data in the result set, after placing the cursor on a row, you can operate on the row, such as extracting data from the current row.

Cursor category:

Explicit and implicit cursors

Four steps are required to display the cursor:

1. Declare a cursor

Cursor mycur (vartype number) is

Select emp_no, emp_zc from cus_emp_basic

Where com_no = vartype;

2. Open the cursor

Open mycur (000627) Note: 000627: Parameter

3. Read data

Fetch mycur into varno, varprice;

4. Close the cursor

Close mycur;

Cursor attributes:

Oracle cursor has four attributes: % isopen, % found, % notfound, % rowcount

% Isopen: determines whether the cursor is opened. If % isopen is enabled, it is equal to true. Otherwise, it is equal to false.

% Found % notfound: determines whether the row where the cursor is located is valid. If the row is valid, % foundd is equal to true; otherwise, false.

% Rowcount returns the number of rows read by the cursor until the current position.

 

Oracle cursor Loop
First, Use loop
Open c_postype;
Loop
Fetch c_postype into v_postype, v_description;
Exit when c_postype % notfound;
......
End Loop
Colse c_postype;
Note that the exit when statement must be followed by fetch. Unnecessary data processing is inevitable.
The processing logic must follow exit when. This requires caution.
Remember to close the cursor after the loop ends.

The second method uses the while loop.

Open c_postype;

Fetch c_postype into v_postype, v_description;
While c_postype % found Loop
......
Fetch c_postype into v_postype, v_description;
End loop;
Close c_postype;

We know that after a cursor is opened, a fetch statement must be executed to make the cursor attribute take effect. Therefore, when a while loop is used, a fetch action must be performed before the loop.
In addition, the data processing action must be placed before the fetch method in the loop body. The fetch method of the circulating body should be placed at the end. Otherwise, it will be processed once more. Be very careful about this.
In short, using while to process cursors cyclically is the most complicated method.

Third For Loop

For v_pos in c_postype Loop
V_postype: = v_pos.pos_type;
V_description: = v_pos.description;
...
End loop;
It can be seen that the for loop is a simple and practical method.
First, it automatically opens and closes the cursor. It solves the problem of forgetting to open or close the cursor.
Others, a record type and a variable that declares the type are automatically defined, and the data is automatically fetch to this variable.
We need to note that the v_pos variable does not need to be declared outside the loop, and no data type needs to be specified 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 obtain a value, you can just get it like a call record.
Such as v_pos.pos_type
It can be seen that the for loop is the best way to loop the cursor. Efficient, concise, and secure.
Unfortunately, what we often see is the first method. So now we have to change this habit.

 Comprehensive Example 1

 Declare   Cursor Goods_cursor --  Create a cursor   Is  Select   *   From Goods Where Goodsid <   5  ; Type goods_tab  Is   Table   Of Goods % Rowtype; --  Declare the index table data type Cur_goods_cursor goods_tab; --  Declare cyclic Variables  Begin   Open  Goods_cursor; Loop  Fetch Goods_cursor Bulk Collect Into Cur_goods_cursor limit 3 ; --  3 to the index table        For I In   1 ... Cur_goods_cursor. Count Loop--  Cyclic index table Dbms_output.put_line (cur_goods_cursor (I). goodsid |  '  -  '  | Cur_goods_cursor (I). goodsname |  '  -  '  |  Cur_goods_cursor (I). remark );  End  Loop; Exit   When Goods_cursor %  Notfound;  End  Loop;  Close  Goods_cursor;  End ;

Comprehensive Example 2

 Create   Or   Replace   Procedure Sp_testdemo Is    Temp  Varchar2 ( 200  );  Cursor C_user Is      Select Osname From  Tbaccessos;  Begin    Open  C_user; Loop  Fetch  C_user  Into   Temp ;  If C_user % Notfound Then  Dbms_output.put_line (  '  No data in the cursor  '  );  Else  Dbms_output.put_line (  '  The  '   | C_user % Rowcount   |   '  Data entries output  '  );  End   If  ;  Exit   When C_user %  Notfound; dbms_output.put_line (  Temp   | CHR ( 13 ));  End  Loop;  Close  C_user;  End ;