Oracle implicit cursors, display cursors, cursor loops, dynamic SELECT statements and dynamic cursors, exceptions, and so on ____oracle

The concept of cursors:
A cursor is a memory workspace of SQL that is defined by the system or user as a variable. The role of a cursor is to temporarily store blocks of data that are extracted from the database. In some cases, the data needs to be transferred from the table stored in the disk to the computer memory for processing, and the processing results are displayed or eventually written back to the database. This will increase the speed of data processing, otherwise frequent disk data exchange will reduce efficiency.
There are two types of cursors: an explicit cursor and an implicit cursor. The Select ... that is used in the aforementioned program. Into ... A query statement that extracts only one row of data from a database at a time, using an implicit cursor for this form of query and DML operations. However, if you want to extract more than one row of data, the programmer defines an explicit cursor and processes it through the statements related to the cursor. An explicit cursor corresponds to a SELECT statement that returns multiple rows of columns with the result.
Once the cursor is opened, the data is transferred from the database to the cursor variable, and the application then decomposes the required data from the cursor variable and processes it.
An implicit cursor
As mentioned earlier, DML operations and Single-line SELECT statements use implicit cursors, which are:
* Insert operation: INSERT.
* Update operation: Update.
* Delete operation: delete.
* Single-line query operation: SELECT ... Into ....
When a system uses an implicit cursor, it is possible to understand the state and result of the operation through the properties of the implicit cursor, and then the process of the control program. Implicit cursors can be accessed using the first name SQL, but note that the SQL cursor name always accesses only the cursor properties of the previous DML operation or the single row select operation. Therefore, you typically use the SQL cursor name to access the property immediately after the operation has just been done. There are four properties of the cursor, as shown below.
SQL code implicit cursor property return value type meaning Sql%rowcount integer represents the number of data rows that a DML statement successfully executed Sql%found the Boolean value to True represents the success of the INSERT, delete, update, or single row query operation Sql%no Tfound Boolean and Sql%found property return value opposite Sql%isopen Boolean DML is true during execution, false after end
Training 1 uses the attributes of an implicit cursor to determine whether the modification of an employee's salary is successful.
Step 1: Enter and run the following programs:
SQL code set serveroutput on BEGIN UPDATE emp set sal=sal+100 WHERE empno=1234; IF sql%found THEN dbms_output. Put_Line (' Successful revision of employee wages.           ');            COMMIT; ELSE Dbms_output. Put_Line (' Modify employee's salary failed.            ');            End IF; End;
The results of the operation are:
SQL code modification employee pay failed. The PL/SQL process has completed successfully.
Step 2: Change the employee number 1234 to 7788, and then repeat the above program:
The results of the operation are:
The SQL code successfully modifies employee wages. The PL/SQL process has completed successfully.
Note: In this case, the Sql%found property is used to determine the success of the modification and give the appropriate information.
An explicit cursor
Definition and operation of cursors
The use of cursors is divided into the following 4 steps.
1. declaring cursors
In the Declear section, the cursor is declared in the following format:
CURSOR cursor name [(parameter 1 data type [, Parameter 2 data type ...])]
is SELECT statement;
Parameters are optional, and the parameters you define can appear in the WHERE clause of the SELECT statement. If a parameter is defined, the corresponding actual parameters must be passed when the cursor is opened.
A SELECT statement is a query statement to a table or view, or even a federated query. You can take a where condition, an order by, or a GROUP BY clause, but you cannot use an into clause. You can use variables defined before you define a cursor in a SELECT statement.
2. Open cursor
In the executable section, open the cursor in the following format:
OPEN cursor name [(actual parameter 1[, actual parameter 2 ...])];
When a cursor is opened, the query result of the SELECT statement is routed to the cursor workspace.
3. Extract data
In the executable section, the data in the cursor workspace is taken to the variable in the following format. The fetch operation must be performed after the cursor is opened.
FETCH cursor name into variable name 1[, variable name 2 ...];
Or
FETCH cursor name into record variable;
Once the cursor is open, a pointer is over the data area, and the FETCH statement returns one row of the pointer's data at a time, and multiple rows need to be repeated, which can be implemented using a loop statement. The control loop can be performed by judging the properties of the cursor.
These two formats are described below:
Variable names in the first format are variables that are used to receive data from a cursor and need to be defined beforehand. The number and type of variables should be the same as the number and type of field variables in the SELECT statement.
The second format takes one row of data to a record variable at a time, and it is convenient to use%rowtype to define a record variable in advance, without having to define and use multiple variables separately.
The following methods are used to define a record variable:
Variable Name Table name | cursor name%rowtype;
The table must exist, and the cursor name must be defined first.
4. Close cursor
The close cursor name;
When an explicit cursor is opened, it must be explicitly closed. Once the cursor is closed, the resource occupied by the cursor is freed and the cursor becomes invalid and must be reopened for use.
The following is a simple exercise to use an explicit cursor.
"Training 1" uses cursors to extract the names and titles of 7788 employees in the EMP table.
SQL code