Shulou(Shulou.com)06/01 Report--

This article is translated from some chapters of the official document, removes the examples from the original text, and adds a few examples at the end. Interested friends can click on the link at the end of the article to read the official documents.

I. definition of cursors

Cursors are pointers to a dedicated SQL area that stores information about processing specific SELECT or DML statements. The cursors explained in this chapter are session cursors. The session cursor exists in the session until the end of the session. Cursors created and managed by PL/SQL are called implicit cursors, and cursors created and managed by users are called explicit cursors. You can get information about any session cursor through the properties of the cursor. By querying the dynamic performance view V$OPEN_CURSOR, you can list cursors that are currently open and parsed.

Implicit Vernier

An implicit cursor is generated each time a select or DML operation is performed. The user cannot control the implicit cursor, but the cursor information can be obtained through the properties of the cursor.

The syntax for implicit cursor attributes is SQLattribute (so implicit cursors are also often called SQL cursors). SQLattribute always points to the recently run select or DML statement. If such a statement has not been run recently, the property returns NULL.

When the statement ends, the implicit cursor closes, but its properties are retained for execution by another select or DML statement. The result of a recent statement run may belong to a different value, and if you want to keep the property value for later use, you can save it in a local variable. In addition, other operations, such as calls to subroutines, may change property values before you test.

Implicit cursors have the following properties:

Property value indicates that the SQL%ISOPENFALSE implicit cursor is always closed after the end of the statement associated with it, so it always returns FALSE

SQL%FOUNDNULL did not perform a select or DML operation

The TRUEselect statement returns one or more rows or the DML operation affects one or more rows

Other conditions of FALSE

When SQL%NOTFOUNDNULL does not perform select or DML operations, it is useless in select into. If there is no value, an error of no_data_found will be reported; when there is a value, validation is meaningless. The TRUEselect statement returns one or more rows or the DML operation affects one or more rows

Other conditions of FALSE

When SQL%ROWCOUNTNULL does not perform select or DML operations, too_many_data error is reported when multiple rows are returned in select into, and 1. 0 is returned. The value returned indicates how many rows are returned by select and how many rows are affected by the DML operation

SQL%BULK_ROWCOUNT value

After the FORALL statement completes, get the number of rows affected by each DML statement from the implicit cursor property SQL%BULK_ROWCOUNT. SQL%BULK_EXCEPTIONS

After the FORALL statement is completed, if an exception is generated during execution, the exception data is saved in the SQL%BULK_EXCEPTIONS.

Explicit Vernier

Explicit cursors are created and managed by the user. Before using an explicit cursor, the user must declare and define the cursor name and associate it with the query (typically, the query returns multiple rows). The user can then process the query result set in the following ways:

Use the open command to open cursors, fetch to get rows, and close to close cursors

Unlike implicit cursors, cursors or cursor variables can be displayed by name reference. Therefore, explicit cursors or cursor variables are called named cursors.

3.1 declare and define explicit cursors

Users can first declare an explicit cursor and then define it in the same block, subroutine, or package, or declare and define it at the same time.

Declare only cursors, with the following syntax:

CURSOR cursor_name [parameter_list] RETURN return_type

Declare cursors and define the syntax of cursors:

CURSOR cursor_name [parameter_list] [RETURN return_type] IS select_statement

Examples of declaring explicit cursors:

DECLARE CURSOR C1 RETURN departments%ROWTYPE;-statement C1

CURSOR c2 IS SELECT employee_id, job_id, salary-declare and define c2

FROM employees

WHERE salary > 2000

CURSOR c1 RETURN departments%ROWTYPE IS-define C1 mahogany-repeat return type

SELECT * FROM departments

WHERE department_id = 110,

CURSOR c3 RETURN locations%ROWTYPE;-statement c3

CURSOR c3 IS-- define c3,-- ignore return type

SELECT * FROM locations

WHERE country_id = 'JP'

BEGIN NULL

END; /

3.2 Open and close cursors

After an explicit cursor is declared and defined, you can open it using the open statement; you use the close statement to close an open explicit cursor, allowing its resources to be reused. After you close a cursor, you cannot extract a record or reference its properties from its result set.

The user can reopen a cursor that has been closed. The cursor must be closed between reopening. Otherwise, PL/SQL will throw a predefined exception CURSOR_ALREADY_OPEN.

3.3 use explicit cursors to get data

After you open an explicit cursor, you can use the fetch statement to get the result set. The basic syntax of a FETCH statement that returns a row is:

FETCH cursor_name INTO into_clause

An into_clause is a list of variables or a single record variable. For each column returned by the query, the variable list or record must have a corresponding type-compatible variable or field. The% TYPE and% ROWTYPE attributes can be used to declare variables and records for use in FETCH statements. The FETCH statement retrieves the current row of the result set, stores the column value of that row in a variable or record, and moves the cursor forward to the next row. Typically, the fetch statement is used in the LOOP statement and exits when the FETCH statement finishes using the row. To detect this exit condition, use the cursor property% NOTFOUND. Because PL/SQL does not throw an exception when the FETCH statement returns no rows.

3.4 variables in explicit cursor queries

An explicit cursor query can reference any variable in its scope. When an explicit cursor is opened, PL / SQL evaluates any variables in the query and uses these values when identifying the result set. Changing the value of the variable later does not change the result set. [when a cursor is defined, the result set in the cursor is determined, and if you want to change the result set, you must close the cursor and reopen the cursor]

3.5 when an explicit cursor column requires an alias

When an explicit cursor query contains a virtual column (expression), the column must have an alias if any of the following conditions are met:

1. Use cursors to read records declared with% ROWTYPE.

2. Virtual columns need to be referenced

3.6 display cursors that accept parameters

The user can create an explicit cursor with formal parameters and then pass different actual parameters to the cursor each time the cursor is opened. In a cursor query, you can use formal cursor parameters anywhere you can use constants. Outside of the cursor query, the user cannot reference formal cursor parameters.

3.7 display the properties of the cursor

% ISOPEN TRUE: cursor open state; FALSE: other state.

% FOUND NULL: shows that the cursor is open but has not got the first line yet

TRUE: returns a row from the last extraction of an explicit cursor

FALSE: other situations

% NOTFOUND NULL: shows that the cursor is open but has not got the first line yet

FALSE: returns a row from the last extraction of an explicit cursor

TRUE: other situations

% ROWCOUNT: zero after the explicit cursor is opened but before the first fetch; otherwise, get the number of rows.

IV. Examples

4.1 two examples of getting table names in tabs

Method 1: pack the result set in the record

Declare

-- record variables for table rows

Cursor mycur is select * from tabs

Cur_result tabs%rowtype

Begin

Open mycur;-- opens the cursor

Loop

Fetch mycur into cur_result;-- acquires data

Exit when mycur%notfound;-- exits the loop

Dbms_output.put_line ('The table name is' | | cur_result.table_name)

End loop

Close mycur;-- closes the cursor

End

Method 2: pack the result set in variables

Declare

-- variable

Cursor mycur is select table_name from tabs

V_tablename tabs.table_name%type

Begin

Open mycur;-- opens the cursor

Loop

Fetch mycur into vault tablenametra-get data

Exit when mycur%notfound;-- exits the loop

Dbms_output.put_line ('The tablename is' | | v_tablename)

End loop

Close mycur;-- closes the cursor

End

Method 3: pack the result set in a cursor-type row%type

Declare

-- record variables for table rows

Cursor mycur is select table_name from tabs

Cur_result mycur%rowtype

Begin

Open mycur;-- opens the cursor

Loop

Fetch mycur into cur_result;-- acquires data

Exit when mycur%notfound;-- exits the loop

Dbms_output.put_line ('The table name is' | | cur_result.table_name)

End loop

Close mycur;-- closes the cursor

End

4.2 dynamic cursor

Static cursors must be declared and defined in the same block. Dynamic cursors can declare cursors before begin and define sql statements when cursors are opened, that is, cursors can be opened in the form of open cursor_name for sql_statement. Sql_statement can be either a static SQL statement or a dynamic SQL statement.

Example 1: static SQL statement

Declare

-- record variables for table rows

Type cursor_type is ref cursor

Mycur cursor_type

Cur_result tabs%rowtype

Begin

Open mycur for select * from tabs;-- open the cursor

Loop

Fetch mycur into cur_result;-- acquires data

Exit when mycur%notfound;-- exits the loop

Dbms_output.put_line ('The table name is' | | cur_result.table_name)

End loop

Close mycur;-- closes the cursor

End

Example 2: dynamic SQL statement

Declare

Type cursor_type is ref cursor

Mycur cursor_type

Cur_result tabs%rowtype

V_sql varchar2 (1000)

Begin

V_sql:='select * from tabs'

Open mycur for vastsql-open the cursor

Loop

Fetch mycur into cur_result;-- acquires data

Exit when mycur%notfound;-- exits the loop

Dbms_output.put_line ('The table name is' | | cur_result.table_name)

End loop

Close mycur;-- closes the cursor

End

5. Other instructions

5.1 official documentation for Oracle cursors

Http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#LNPLS00602

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.