Oracle cursor Introduction

Oracle cursor Introduction

Oracle cursor Introduction

The PL/SQL cursor in Oracle stores the data queried in the database in the memory as a temporary table. The cursor can operate on the data stored in the memory, one or a group of data is returned, or one data is not returned. Although records and table types in PL/SQL can also be used to store data, it is not convenient to operate a group of data stored in the memory. The cursor is exactly the tool in this regard.

PL/SQL contains the implicit cursor and display cursor. The implicit cursor is used to process SELECT INTO and DML statements, while the display cursor is used to process multiple rows of data returned by the SELECT statement, the basic operations of a cursor include:

-Declared cursor
-Open the cursor.
-Extract cursor
-Close the cursor

Declared cursor

Define a cursor name to correspond to a query statement. The syntax is as follows:

CURSOR cursor_name [{parameter_name [IN] data_type [{: = | DEFAULT} value] [...]}] IS select_statement [for update [OF column [...] [NOWAIT] Description:

1. data_type specifies the data type for the input parameter, but cannot specify the precision or length

2. for update is used to lock all rows or partial columns of the cursor result set and the corresponding data in the table when the cursor data is used.

3. OF if the OF clause is not used, all columns OF the corresponding data row in the cursor result set and table are locked. If the OF clause is used, only the specified column is locked.

4. NOWAIT if the data row in the table is locked by a user, the for update operation of other users will wait until the user releases the lock of the Data row, if NOWAIT is used, other users will immediately return an error message when using the OPEN command to OPEN the cursor.

Open cursor

Oracle executes the query statement only after the cursor is opened. Its syntax is as follows:

OPEN cursor_name [(value,...)]; after the cursor is opened, the SELECT statement corresponding to the cursor is executed. The cursor is retrieved from the result set and saved to the defined variable. The syntax is as follows:

FETCH cursor_name INTO variable [,...]; among them, variable is a variable used to store a single row of data in the result set. You can select multiple common types of variables to receive column values in rows one by one. You can also use a variable of the % ROWTYPE type, or a custom record type variable that receives all column values in the row.

Close cursor

Close the cursor and release system resources. The syntax is as follows:

CLOSE cursor_name;