Oracle cursor usage 2

DML statement results

After a DML statement is executed, the results of the DML statement are stored in four cursor attributes, which are used to control the program flow or understand the program status. When running a DML statement, PL/SQL opens a built-in cursor and processes the result. The cursor is an area in the memory that maintains the query result. The cursor is opened when running the DML statement and closed after completion. Only SQL % found, SQL % notfound, and SQL % rowcount attributes are used for implicit cursors. SQL % found, SQL % notfound is a Boolean value, and SQL % rowcount is an integer.

　　SQL % found and SQL % notfound

Before executing any DML statement, the values of SQL % found and SQL % notfound are null. After executing the DML statement, the attribute values of SQL % found will be:

. True: insert

. True: delete and update. At least one row is deleted or updated.

. True: select into returns at least one row.

When SQL % found is true, SQL % notfound is false.

SQL % rowcount

Before executing any DML statement, the SQL % rowcount value is null. For select into statements, if the execution is successful, the SQL % rowcount value is 1. If the execution fails, the SQL % rowcount value is 0, and an exception no_data_found is generated.

SQL % isopen

SQL % isopen is a Boolean value. If the cursor is opened, it is true. If the cursor is closed, it is false. for implicit cursors, SQL % isopen is always false. This is because the implicit cursors are opened when DML statements are executed and are immediately closed at the end.

　　Transaction control statement

A transaction is a logical unit of work that can contain one or more DML statements. Transaction Control helps you ensure data consistency. If any DML statement in the transaction control logic unit fails, the entire transaction will be rolled back. In PL/SQL, you can use commit, rollback, savepoint, and SET transaction statements explicitly.

The commit statement terminates the transaction, permanently stores changes to the database, releases all locks, and rollback terminates the current transaction to release all locks, but does not save any changes to the database. The savepoint is used to set the intermediate point, when a transaction calls too many database operations, the intermediate point is very useful. Set transaction is used to set transaction attributes, such as read-write and isolation level.

　　Explicit cursor

When a query returns more than one row, an explicit cursor is required. You cannot use the select into statement. PL/SQL manages implicit cursors. When the query starts, the implicit cursor is opened. When the query ends, the implicit cursor is automatically closed. The explicit cursor is declared in the declaration part of the PL/SQL block. It is opened in the execution part or Exception Handling part, data is retrieved, and disabled.

　　Use cursor

Here we need to make a declaration. The cursor we refer to usually refers to an explicit cursor. Therefore, we have not specified any specific cursor from now on. To use a cursor in a program, you must first declare the cursor.

　　Declared cursor

Syntax:

Cursor cursor_name is select_statement;

In PL/SQL, the cursor name is an un-declared variable and cannot be assigned a value to the cursor name or used in an expression.

Example:

Delcare

Cursor c_emp is select empno, ename, salary

From EMP

Where salary> 2000

Order by ename;

........

Begin

In the definition of a cursor, a SELECT statement does not have to have a table that can be a view. You can also select columns from multiple tables or views, or even use * to select all columns.

　　Open cursor

Before using the values in the cursor, you should first open the cursor and open the cursor to initialize query processing. The syntax for opening a cursor is:

Open cursor_name

Cursor_name is the cursor name defined in the Declaration section.

Example:

Open c_emp;

Close cursor

Syntax:

Close cursor_name

Example:

Close c_emp;

　　Extract data from cursor

Use the FETCH Command to obtain a row of data from the cursor. After each data extraction, the cursor points to the next row of the result set. Syntax:

Fetch cursor_name into variable [, variable,...]

For each column of the cursor defined by select, The fetch Variable list should have a variable corresponding to it, and the variable type should be the same.

Example:

Set serveriutput on

Declare

V_ename EMP. ename % type;

V_salary EMP. Salary % type;

Cursor c_emp is select ename, salary from EMP;

Begin

Open c_emp;

Fetch c_emp into v_ename, v_salary;

Dbms_output.put_line ('salary of employee' | v_ename | 'ais '| v_salary );

Fetch c_emp into v_ename, v_salary;

Dbms_output.put_line ('salary of employee' | v_ename | 'ais '| v_salary );

Fetch c_emp into v_ename, v_salary;

Dbms_output.put_line ('salary of employee' | v_ename | 'ais '| v_salary );

Close c_emp;

End

This code is undoubtedly very troublesome. If multiple rows return results, you can use the loop and use the cursor attribute as the condition for ending the loop to extract data in this way, the readability and conciseness of the program are greatly improved. Next we will re-write the following program in a loop:

Set serveriutput on

Declare

V_ename EMP. ename % type;

V_salary EMP. Salary % type;

Cursor c_emp is select ename, salary from EMP;

Begin

Open c_emp;

Loop

Fetch c_emp into v_ename, v_salary;

Exit when c_emp % notfound;

Dbms_output.put_line ('salary of employee' | v_ename | 'ais '| v_salary );

End