"Translate" Oracle Cursors detailed description

This article is a partial translation of the official document, removing examples from the original text and adding a few examples at the end. Interested friends can click the link at the end of the article to read the official documentation.

First, the definition of a cursor

A cursor is a pointer to a private SQL region that stores information about the processing of a particular select or DML statement. The cursor explained in this chapter is a session cursor. A session cursor exists in the session straight to the end of the conversation. Cursors created and managed by PL/SQL are called implicit cursors, and cursors created and managed by the user are called explicit cursors. You can get information about any session cursor through the properties of the cursor. By querying dynamic performance View v$open_cursor, you can list cursors that are currently open and resolved.

Second, implicit cursors

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

The syntax for an implicit cursor property is Sqlattribute (so an implicit cursor is also often called a SQL cursor). Sqlattribute always points to the most recently run Select or DML statement. If you have not recently run such a statement, the property returns NULL.

When the statement runs at the end, the implicit cursor is closed, but its properties are persisted to another select or DML statement execution. The results of the most recent statement run may be of different values, and if you want to preserve the property values for later use, you can save them in a local variable. In addition, other operations, such as subroutine calls, may change the value of the property before you test it.

The attributes of an implicit cursor are as follows:

Property	Value	Description	Note
Sql%isopen	FALSE	An implicit cursor is always closed after the statement associated with it has ended, so it always returns false
Sql%found	Null	No select or DML operations were performed
	TRUE	The SELECT statement returns one or more rows or the DML operation affects one or more rows
	FALSE	Other conditions
Sql%notfound	Null	No select or DML operations were performed	It is useless in select INTO, no_data_found error is reported when there is no value, and validation is meaningless when there is a value.
	TRUE	The SELECT statement returns one or more rows or the DML operation affects one or more rows
	FALSE	Other conditions
Sql%rowcount	Null	No select or DML operations were performed	A too_many_data error is reported when multiple rows are returned in select INTO, and 1 is returned.
	Numerical	The returned value indicates how many rows the select returned and how many rows were affected by the DML operation
Sql%bulk_rowcount	Numerical		After the ForAll statement is complete, the number of rows affected by each DML statement is obtained from the implicit cursor property sql%bulk_rowcount.
Sql%bulk_exceptions			After the ForAll statement is complete, exception data is saved in Sql%bulk_exceptions if an exception is generated during execution.

Three, an explicit cursor

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

Open the cursor using the Open command, fetch the row using the fetching, and close the cursor with close;

Unlike an implicit cursor, you can display a cursor or a cursor variable by name reference. Therefore, an explicit cursor or cursor variable is called a named cursor.

3.1 Declaring and defining an explicit cursor

A user 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.

Only the cursor is declared with the following syntax:

CURSOR cursor_name [parameter_list] RETURN return_type;

Declares a cursor and defines the syntax for the cursor:

CURSOR cursor_name [parameter_list] [RETURN return_type] is select_statement;

Example of declaring an explicit cursor:

DECLARE CURSOR C1 RETURN departments%rowtype; --Statement C1

CURSOR C2 is SELECT employee_id, job_id, salary--declaration and definition C2

From Employees

WHERE Salary > 2000;

CURSOR C1 return Departments%rowtype is--Define C1,--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 Opening and closing cursors

After you declare and define an explicit cursor, you can open it by using the Open statement, which allows you to reuse its resources by closing an open explicit cursor with the close statement. After you close a cursor, you cannot extract records from its result set or reference its properties.

The user can reopen a cursor that has been closed. The cursor must be closed between re-opening. Otherwise, PL/SQL throws a pre-defined exception Cursor_already_open.

3.3 Fetching data using an explicit cursor

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

FETCH cursor_name into Into_clause

Into_clause is a variable list 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 properties can be used to declare variables and records for use in a fetch statement. The FETCH statement retrieves the current row of the result set, stores the row's column values in a variable or record, and moves the cursor forward to the next line. Typically, a FETCH statement is used in a loop statement to exit when the fetch statement runs out of time. To detect this exit condition, use the cursor property%notfound. PL/SQL does not throw an exception when the FETCH statement does not return any rows.

3.4 Variables in an explicit cursor query

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 those values when identifying the result set. Changing the value of a variable later does not change the result set. "When you define a cursor, 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 (an expression), the column must have an alias if any of the following conditions are true:

1. Use cursors to read records using the%rowtype declaration.

2. Need to reference virtual columns

3.6 Display cursors that accept parameters

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

3.7 Displaying the properties of a cursor

%isopen TRUE: Cursor open state; FALSE: Other state.

%found NULL: Show cursor Open but not yet get first row

TRUE: A row was returned from the most recent fetch from an explicit cursor

FALSE: Other conditions

%notfound NULL: Show cursor Open but not yet get first row

FALSE: The most recent fetch from an explicit cursor returns a row

TRUE: Other conditions

%rowcount: The number of rows is obtained after an explicit cursor is opened but is zero before the first fetch.

Iv. examples

4.1 Get two examples of table names in tabs

Method One: Load the result set in the record

Declare

--Record variables for table rows

Cursor Mycur is a select * from tabs;

Cur_result Tabs%rowtype;

Begin

Open mycur;--Opening cursors

Loop

Fetch mycur into cur_result;--get data

Exit when mycur%notfound;--exit loop

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

End Loop;

Close mycur;--Closing Cursors

End

Method Two: Load the result set in a variable

Declare

--Variable

Cursor Mycur is a select table_name from tabs;

V_tablename Tabs.table_name%type;

Begin

Open mycur;--Opening cursors

Loop

Fetch mycur into v_tablename;--get data

Exit when mycur%notfound;--exit loop

Dbms_output.put_line (' The table name is ' | | V_tablename);

End Loop;

Close mycur;--Closing Cursors

End

Method Three: Load the result set in the row%type of the cursor type

Declare

--Record variables for table rows

Cursor Mycur is a select table_name from tabs;

Cur_result Mycur%rowtype;

Begin

Open mycur;--Opening cursors

Loop

Fetch mycur into cur_result;--get data

Exit when mycur%notfound;--exit loop

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

End Loop;

Close mycur;--Closing Cursors

End

4.2 Dynamic Cursors

The Declaration and definition of a static cursor must be executed in the same block, the dynamic cursor can declare the cursor before begin, and the SQL statement is defined when the cursor is opened, that is, the cursor can be opened using open cursor_name for sql_statement. Sql_statement can be a static SQL statement, or it can be a dynamic SQL statement.

Example one: a 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;--opening cursors

Loop

Fetch mycur into cur_result;--get data

Exit when mycur%notfound;--exit loop

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

End Loop;

Close mycur;--Closing Cursors

End

Example two: a 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 v_sql;--opening cursors

Loop

Fetch mycur into cur_result;--get data

Exit when mycur%notfound;--exit loop

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

End Loop;

Close mycur;--Closing Cursors

End

V. Other Notes

5.1 Official documentation for Oracle Cursors

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

This article is from the "three countries Cold jokes" blog, please be sure to keep this source http://myhwj.blog.51cto.com/9763975/1869631

"Translate" Oracle Cursors detailed description