How to comprehensively parse the midstream mark usage in a database

A cursor is a data buffer provided by the system for users to store the execution results of SQL statements. Defined by the system or user as a variable. You can use SQL statements to obtain records from the cursor one by one and assign them to the primary variables for further processing. The primary language is record-oriented, A group of primary variables can only store one record at a time. In some cases, you need to transfer the data from the table that is stored in the disk to the computer memory for processing, display the processing result or write it back to the database. In this way, the data processing speed will increase; otherwise, frequent disk data exchanges will reduce the efficiency. To describe the cursor in the database language, it is the entity mapped to the position of a row of data in the result set. With the cursor, you can access any row of data in the result set, after placing the cursor on a row, you can perform operations on the row's data, such as extracting data from the current row.

There are two types of cursors: explicit cursors and implicit cursors. SELECT... Used in the program... INTO... A query statement can only extract one row of data from the database at a time. For this form of query and trigger operations, the system uses an implicit cursor. However, to extract multiple rows of data, the programmer must define an explicit cursor and process it through statements related to the cursor. An explicit cursor corresponds to a SELECT statement that returns multiple rows and multiple columns.

Once the cursor is opened, the data is transmitted from the database to the cursor variable, and then the application splits the required data from the cursor variable for processing. The following is a detailed example of explicit and implicit cursors.

　　Explicit cursor:

1. Define a cursor

CURSOR CUR_NAME P1, P2 ,...

IS

SELECT statement

--------------------

A. the SELECT statement cannot be a select... INTO... statement.

B. The cursor can have parameters used to participate in the WHERE condition in the SELECT statement.

2 OPEN cursor: OPEN CUR_NAME P1, P2 ,...

A. If there are parameters, see the actual parameters passed to the SELECT statement first.

B. Execute the SELECT statement. The result will exist in the cursor and can be controlled using the cursor name.

3. obtain data: The FETCH CUR_NAME INTO variable;

A. FETCH starts from the first row of data. Each FETCH row of data automatically jumps to the next row until the last row of data is FETCH.

B. if common variables are used to obtain data, the variables should be defined based on the fields in the cursor structure.

In this case, the variable list and the field list in the cursor are consistent in type, order, and number.

C. You can also use record variables to retrieve data. "record variable cursor name % ROWTYPE ";

At this point, the structure of the record variable is exactly the same as that of the cursor. (Note that it is best not to use the table name here ).

4. CLOSE the cursor: CLOSE CUR_NAME;

For example, you can query the name, position, and salary of 7788 of employees.

　　

DECLARE V_ENAME EMP. ENAME % TYPE; V_JOB EMP. JOB % TYPE; ----------------------- -- 1. CURSOR CUR_EMP IS Select ename, job from emp where empno = 7788; BEGIN -- 2. OPEN CUR_EMP; -- 3. FETCH CUR_EMP INTO V_ENAME, V_JOB; DBMS_OUTPUT.PUT_LINE (V_ENAME | ''| V_JOB ); -- 4. CLOSE CUR_EMP; END;

When the system uses an implicit cursor, you can use the attributes of the implicit cursor to understand the operation status and results, and then control the process of the program. Implicit cursors can be accessed using the name SQL, but note that the SQL cursname can only access the cursor attributes of the previous processing operation or single-row SELECT operation. Therefore, the SQL cursor name is used to access attributes immediately after the operation is completed. There are four types of cursor attributes: SQL % ISOPEN, SQL % FOUND, SQL % NOTFOUND, and SQL % ROWCOUNT.

The type returned by SQL % ISOPEN is Boolean and determines whether the cursor is opened. If % ISOPEN is enabled, the return value is true. Otherwise, the return value is false, indicating that the cursor is true during execution and false after execution.

The return value of SQL % NOTFOUND is Boolean and determines whether the row where the cursor is located is valid. If the row is valid, % FOUNDD is equal to true; otherwise, false is equal to the return value of % FOUND.

The return value of SQL % FOUND is of the boolean type. If the value is TRUE, the deletion update or single row query operation is successful.

The Return Value Type of SQL % ROWCOUNT is integer. The number of records read by the cursor until the current position is returned, that is, the number of successfully executed data rows.

The record is not stored in the database. It is stored in the memory space like a variable. When using the record, you must first define the record structure and then declare the record variable. PL/SQL records can be viewed as user-defined data types.

The cursor for loop shows a quick way to display the cursor. It uses the for loop to read the row data in the result set in sequence. When the form loop starts, the cursor is automatically opened (no open is required ), the system automatically reads the data of the current row of the cursor once every cycle (fetch is not required). When you exit the for loop, the cursor is automatically closed (close is not required ). When you use a cursor for loop, you cannot use open statements, fetch statements, or close statements. Otherwise, an error occurs.

　　

Set serveroutput on; Declare Cursor mycur (vartype number) is Select emp_no, emp_zc from cus_emp_basic Where com_no = vartype; Begin For person in mycur (000627) loop Dbms_output.put_line ('No.:' | person. emp_no | ', address:' | person. emp_zc ); End loop; End;

The usage of an explicit cursor consists of four steps:

1. Declare a cursor

The DECLEAR part declares the cursor in the following format:

CURSOR name parameter 1 data type...

Is select statement;

Parameters are optional. The defined parameters can appear in the WHERE clause of the SELECT statement. If a parameter is defined, the actual parameter must be passed when the cursor is opened.

A select statement is a query statement for a table or view, or even a joint query. The WHERE condition, order by, group by clause can be included, but the INTO clause cannot be used. Variables defined before the cursor definition can be used in SELECT statements.

Example:

　　

DELCARE CURSOR C_EMP is select empno, ename, salary FROM emp WHERE salary> 1500 Order by ename; ........ BEGIN

In the definition of a cursor, the SELECT statement does not have to be a view or SELECT from multiple tables or views.

　　2. Open the cursor

Before using the values in the cursor, you should first open the cursor and open the cursor to initialize query processing. Open the cursor syntax

OPEN cursor name Actual parameter 1 ,...;

When the cursor is opened, the query results of the SELECT statement are transmitted to the cursor workspace.

Example:

OPEN C_EMP;

　　3. extract data

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

FETCH cursor_name INTO variable ,..

For each column of the cursor defined by SELECT, The FETCH Variable list should have a variable corresponding to the variable.

In the executable section, the data in the cursor workspace is retrieved to the variable in the following format. The extraction operation must be performed after the cursor is opened.

FETCH cursor name INTO variable name;

Or

FETCH cursor name INTO record variable;

After a cursor is opened, a pointer points to the data zone. The FETCH statement returns the data of a row pointed to by the pointer at a time. To return multiple rows, repeated execution is required. You can use a loop statement to implement this. The control loop can be performed by judging the attributes of the cursor.

The two formats are described below:

The variable name in the first format is the variable used to receive data from the cursor, which needs to be defined in advance. The number and type of variables must be the same as the number and type of field variables in the SELECT statement.

In the second format, when a row of data is retrieved into the record variable at a time, you need to use % ROWTYPE to define the record variable in advance. This method is more convenient to use and does not need to define or use multiple variables separately.

The method for defining record variables is as follows:

Variable name table name | cursor name % ROWTYPE;

The table must exist and the cursor name must be defined first.

　　4. Close the cursor

CLOSE cursor name;

Example:

CLOSE C_EMP;

After an explicit cursor is opened, it must be closed explicitly. Once the cursor is closed, the resources occupied by the cursor are released. The cursor becomes invalid and must be re-opened before it can be used.

Oracle supports dynamic SELECT statements and dynamic cursors. The dynamic method greatly extends the programming capability.

For SELECT statements whose query results are one row, you can use the dynamic generation of query statement strings to generate and execute them temporarily during the program execution phase. The syntax is as follows:

Execute immediate query statement string into variable;

The cursor defined in the variable declaration part is static and cannot be modified during the program running. Although different data can be obtained through parameter transmission, there are still many limitations. By using dynamic cursors, you can generate a query statement as the cursor at any time during the program running stage. To use a dynamic cursor, you must first define a cursor type and then declare a cursor variable. The query statement corresponding to the cursor can be dynamically described during program execution.