Oracle Cursors and Loops

The cursor (cursor)--the cursor is a data buffer that the system opens for the user, and holds the execution result of the SQL statement. Each cursor area has a name that allows the user to retrieve the record from the cursor in one of the SQL statements and assign it to the main variable for further processing in the main language.

1. Implicit cursors: Cursors automatically created by Oracle servers, in which Oracle automatically declares implicit cursors in all DML and select statements issued in the PL/SQL program.

2. Explicit cursors: Custom cursors, in order to handle a set of records returned by the SELECT statement, you need to declare and process an explicit cursor in the PL/SQL program.

2.1. Using an explicit cursor to process data requires 4 steps:

1 Declaration cursor: CURSOR Cursor_name[parameter][return] is select_statement

2 opening cursor: Open Cursor_name[value]

3) Retrieving data: Fetch cursor_name into {vatiable_list | record_variable}

4 closing cursor: Close cursor_name

3. Cycle

3.1, for ... Loop Looping structure

Declare

CURSOR Merchandise_cursor is

SELECT * FROM table WHERE condition

BEGIN

for R in Merchandise_cursor Loop

Dbms_output.put (r.column| | ");

Dbms_output.put_line (R.column);

End LOOP;

End;

3.2, loop cycle structure--unconditional circulation

LOOP

--statements--

EXIT when CONDITION

End LOOP;

Note: If no Exit statement is specified, the loop runs; for the loop to work, you must provide an exit when clause with a condition that can be judged to be true at some point.

For example

DECLARE

I number:=1;

BEGIN

LOOP

Dbms_output.put_line (I | | ' The square number is ' | | I*i);

i:=i+1;

EXIT when i>10;

End LOOP;

End;

3.3. While-loop Circulation structure

While condition

LOOP

statements;

End LOOP;

Attention:

(1) When using a cursor for loop, you cannot use an open statement, a FETCH statement, or a close statement, or an error occurs.

(2) When using the properties of an implicit cursor, you need to precede the property with SQL, because the default cursor name is SQL when an implicit cursor is created by Oracle.

(3) When using a cursor, you cannot use a length constraint, such as number (4), VARCHAR (10), and so on, when specifying a data type.

(4) The control variable in the for loop can only be used within a loop and cannot use the loop control variable outside the loop.