Introduction to Cursors for Oracle

Introduction to Cursors for Oracle

Oracle中的PL/SQL的游标是指把数据库中查询出来的数据以临时表的形式存放在内存中，游标可以对存储在内存中的数据进行操作，返回一条或者一组数据，或者一条数据也不返回。PL/SQL中的记录和表类型虽然也能用来存储数据，但对一组存储在内存中的数据进行操作，还是不太方便，游标恰好是这方面的工具。PL/SQL包含隐含游标和显示游标，其中隐含游标用于处理SELECT INTO和DML语句，而显示游标则专门用于处理SELECT语句返回的多行数据，游标的基本操作有：- 声明游标- 打开游标- 提取游标- 关闭游标

declaring cursors

Define a cursor name to correspond to a query statement with the following syntax:

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 when using cursor data, lock the cursor result set with all rows or parts of the data corresponding to the table
3. Of if the of clause is not used, all columns that lock the cursor result set against the corresponding data row in the table are locked, and only the specified columns if the of clause is used
4. NOWAIT If the data row in the table is locked by a user, the for update operation for the other user will wait until the user releases the lock on the data row, and if NOWAIT is used, the other user returns an error message immediately when the cursor is opened using the Open command.

Open cursor

Only after the cursor is opened does Oracle execute a query statement whose syntax:

OPEN cursor_name [(value,...)];

When the cursor is opened, the corresponding SELECT statement for the cursor is executed, retrieving a single row of data from the result set and saving it to the defined variable, with the following syntax:

FETCH cursor_name INTO variable [,...];

Where variable is a variable used to store single-row data in the result set, you can select multiple normal types of variables, one-to-one receive column values in rows, or you can use a variable of type%rowtype, or a custom record type variable, to receive all column values in a row.

Close Cursors

Close the cursor and release the system resources with the following syntax:

CLOSE cursor_name;

Introduction to Cursors for Oracle