Introduction of the concept of cursors (implicit cursors are commonly used, but only an internal mechanism)

There are two types of cursors: implicit and explicit. PL/SQL declares an implicit cursor for all SQL data operations, including query operations that only return one record..

In fact, Oracle's implicit cursor is used for every query operation in PL/SQL.

 

In addition, we can use a cursor to process a query result explicitly:

Declare
CursorC1Is
SelectEmpno, ename, job
FromEMP
WhereDeptno = 20;

 

A row set returned by a multi-row query is called a result set ). Its size is the number of rows that meet our query conditions. As shown in, an explicit cursor "points to" the record of the current row, which allows our program to process a record each time.

 

In fact, a cursor is equivalent to a pointer pointing to a result set. It occupies possession of multiple field records for storage and processing.

Great advantages.

 

Multi-line query is processed like a file. For example, a COBOL program opens a file, processes records, and closes the file. Similarly, a PL/SQL program opens a cursor, processes the queried rows, and closes the cursor. Just as the file pointer can mark the current position in the open file, the cursor can mark the current position of the structure set.

We can use the open, fetch, and close statements to control the cursor. Open is used to open the cursor and point the cursor to the first row of the result set, fetch retrieves information about the current row and moves the cursor to the next row. When the last row is also processed, close closes the cursor.

 

Cursor For Loop

In most cases, explicit cursors can be used to replace open, fetch, and close statements with a simple for loop cursor. First, the cursor for loop implicitly declares a loop index (Loop
Index) variable. Next, it will open the cursor, repeatedly retrieve data from the result set, and place the data in various fields of the cyclic index. When all rows are processed, the cursor is closed. In the following example, the cursor for loop implicitly declares an emp_rec record:

 

Declare
CursorC1Is
SelectEname, Sal, hiredate, deptno
FromEMP;
...
Begin
ForEmp_recInC1Loop
...
Salary_total: = salary_total + emp_rec.sal;
End Loop;
End;

 

To use every field in each queried row, we can use dot notation, which acts like a domain selector.