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

This article will explain in detail the use of cursor Cursor in Oracle. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Query

The SELECT statement is used to query data from the database, and when the select statement is used in PL/SQL, it is used with the INTO clause to query the

The return value is assigned to the variable in the INTO clause, and the variable is declared in DELCARE. The SELECT INTO syntax is as follows:

SELECT [DISTICT | ALL] {* | column [, column,...]} INTO (variable [, variable,...] | record) FROM {table | (sub-query)} [alias] WHERE.

The SELECT statement in PL/SQL returns only one row of data. If there is more than one row of data, use explicit cursors (for a discussion of cursors we will

Later), the INTO clause should have the same number of columns as the SELECT clause. There can also be record variables in the INTO clause.

% TYPE attribute

Variables and constants can be declared as built-in or user-defined data types in PL/SQL to refer to a column name while inheriting its data class

Type and size. This dynamic assignment method is very useful, for example, the data type and size of the column referenced by the variable has changed, if% TYPE is used

Then the user does not have to modify the code, otherwise the code must be modified.

Example:

V_empno SCOTT.EMP.EMPNO%TYPE; v_salary EMP.SALARY%TYPE

Not only column names can use% TYPE, but variables, cursors, records, or declared constants can use% TYPE. This is useful for defining the same data class

Type variables are very useful.

DELCARE Ventra NUMBER (5): = 10; VroomB Ventra% TYPEV15; VroomC Ventra% TYPER; BEGIN DBMS_OUTPUT.PUT_LINE ('Vetera' | | 'ViteBale' | | Vacub | | 'Vedic' | | PL/SQL procedure successfully completed. SQL >); END SQL > / V_A=10 V_B=15 Veterc = PL/SQL procedure successfully completed. SQL >

Other DML statements

Other DML statements that manipulate data are: INSERT, UPDATE, DELETE, and LOCK TABLE. The syntax of these statements in PL/SQL is the same as in SQL.

The syntax is the same. We have already begged in front.

Once we have discussed the use of DML statements, we will not repeat them here. You can use any variable declared in the DECLARE section in the DML statement, if it is nested

Block, then pay attention to the scope of the variable.

Example:

CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number) AS v_ename EMP.ENAME%TYPE; BEGIN SELECT ename INTO v_ename FROM emp WHERE empno=p_empno; INSERT INTO FORMER_EMP (EMPNO,ENAME) VALUES EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Employee Number Not foundational'); END

The result of the DML statement

When a DML statement is executed, the results of the DML statement are saved in four cursor properties, which are used to control the program flow or to understand the program.

The state of. When running the DML statement, PL/SQL opens a built-in cursor, which is an area of memory that maintains the query results, and processes the results

The cursor opens when the DML statement is run and closes when it is finished. Implicit cursors only use SQL%FOUND

SQL%NOTFOUND,SQL%ROWCOUNT has three attributes. SQL% found, SQL%NOTFOUND is a Boolean value and SQL%ROWCOUNT is an integer value.

SQL%FOUND and SQL%NOTFOUND

Before executing any DML statement, the value of SQL%FOUND and SQL%NOTFOUND is NULL, and after executing the DML statement, the property value of SQL%FOUND will be:

. TRUE: INSERT

. TRUE: DELETE and UPDATE, at least one line is DELETE or UPDATE.

. TRUE: SELECT INTO returns at least one row

When SQL%FOUND is TRUE, SQL%NOTFOUND is FALSE.

SQL%ROWCOUNT

The value of SQL%ROWCOUNT is NULL before any DML statement is executed, and for SELECT INTO statements, if the execution is successful

The value of SQL%ROWCOUNT is 1, and if it is not successful, the value of SQL%ROWCOUNT is 0, and an exception NO_DATA_FOUND is generated.

SQL%ISOPEN

SQL%ISOPEN is a Boolean value, TRUE if the cursor is open, FALSE if the cursor is closed. For implicit cursors, SQL%

ISOPEN is always FALSE, this is because implicit swimming

The flag is turned on when the DML statement is executed and closed immediately at the end.

Transaction control statement

A transaction is a logical unit of work that can include one or more DML statements, and transaction control helps users ensure data consistency. If any DML in the transaction control logic unit

Statement fails, the entire transaction will be rolled back, and in PL/SQL users can explicitly use COMMIT, ROLLBACK, SAVEPOINT, and

SET TRANSACTION statement.

The COMMIT statement terminates the transaction, permanently saves the changes to the database, and releases all LOCK,ROLLBACK terminating the current transaction and releasing all LOCK.

But do not save any changes to the database, SAVEPOI

NT is used to set the midpoint, which is very useful when transactions invoke too many database operations, and SET TRANSACTION is used to set things.

Service attributes, such as read-write and isolation level, etc.

Explicit cursor

An explicit cursor is required when the query returns more than one row, and the user cannot use the select into statement. PL/SQL management implicit

Cursor, the implicit cursor opens when the query starts and closes automatically when the query ends. Explicit cursors declare in the declaration section of the PL/SQL block, open, fetch data, and close in the execution or exception handling section.

Use cursors

I would like to make a statement here that the cursors we are talking about usually refer to explicit cursors, so there is no specific situation from now on, what we are talking about.

Cursors are explicit cursors. To use cursors in a program, you must first declare cursors.

Declare a cursor

Syntax:

CURSOR cursor_name IS select_statement

In PL/SQL, the cursor name is an undeclared variable and cannot be assigned to or used in expressions.

Example:

DELCARE CURSOR C_EMP IS SELECT empno,ename,salary FROM emp WHERE salary > 2000 ORDER BY ename;. BEGIN

It is not necessary that a table in a SELECT statement in a cursor definition can be a view, a column that can be selected from multiple tables or views, or even use *

To select all the columns.

Open the 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 the cursor

Syntax:

CLOSE cursor_name

Example:

CLOSE C_EMP

Extract data from cursors

Use the FETCH command to get a row of data from the cursor. Each time the data is extracted, the cursor points to the next row of the result set. The syntax is as follows:

FETCH cursor_name INTO variable [, variable,...]

For each column of a cursor defined by SELECT, the list of FETCH variables should have a variable corresponding to it, and the variable should be of the same type.

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 cantilever emptiness; FETCH c_emp INTO vandalism enameMagnegy vastly available DBMSplayOUTPUT.PUTplumLINE ('Salary of Employee' | | v_ename | |' is' | | v_salary); FETCH c_emp INTO vandalism enameparamente DBMS_OUTPUT.PUT_LINE ('Salary of Employee' | | v_ename | |' is' | | v_salary); FETCH c_emp INTO DBMS_OUTPUT.PUT_LINE ('Salary of Employee' | | v_ename | |' is' | | v_salary); CLOSE cantilever; END

This code is undoubtedly very troublesome. If multiple lines return the result, you can use the loop and use the cursor property as the condition to end the loop, with this

By extracting data in different ways, the readability and simplicity of the program are greatly improved. let's use a loop to rewrite the above program:

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 cymbal emptiness; LOOP FETCH c_emp INTO voluenameMagnegy vicious salt; EXIT WHEN cantilever% NOTFOUNDN; DBMS_OUTPUT.PUT_LINE ('Salary of Employee' | | v_ename | |' is' | | v_salary); END

Record variable

Define a record variable using the TYPE command and% ROWTYPE. For more information about% ROWsTYPE, see the related resources.

Record variables are used to extract rows of data from the cursor, and when the cursor selects many columns, the record ratio is used to declare a variable element for each column.

It's much more.

When using% ROWTYPE on the table and putting the value taken from the cursor into the record, if you want to select all the columns in the table, then in the SELECT clause

It is much safer to use * in than to list all the columns.

Example:

SET SERVERIUTPUT ON DECLARE R_emp EMP%ROWTYPE; CURSOR c_emp IS SELECT * FROM emp; BEGIN OPEN cantilever; LOOP FETCH c_emp INTO ritual; EXIT WHEN cantilever% NOTFOUNDN; DBMS_OUT.PUT.PUT_LINE ('Salary of Employee' | | r_emp.ename | |' is' | | r_emp.salary); END LOOP; CLOSE cantilever; END

% ROWTYPE can also be defined by a cursor name, so the cursor must be declared first:

SET SERVERIUTPUT ON DECLARE CURSOR c_emp IS SELECT ename,salary FROM emp; R_emp cantilever% ROWTYPE; BEGIN OPEN cantilever; LOOP FETCH c_emp INTO cantilever; EXIT WHEN cantilever% NOTFOUNDN; DBMS_OUT.PUT.PUT_LINE ('Salary of Employee' | | r_emp.ename | |' is' | | r_emp.salary); END LOOP; CLOSE cantilever; END

Cursor with parameters

Similar to stored procedures and functions, parameters can be passed to cursors and used in queries. This is useful for dealing with the situation of opening cursors under certain conditions.

The situation is very useful. Its syntax is as follows:

CURSOR cursor_name [(parameter [, parameter],...)] IS select_statement

The syntax for defining parameters is as follows:

Parameter_name [IN] data_type [{: = | DEFAULT} value]

Unlike stored procedures, cursors can only accept values passed, not return values. Parameters define only the data type, not size.

In addition, you can set a default value for the parameter, which is used when no parameter value is passed to the cursor. The parameter defined in the cursor is just a placeholder, and it may not be reliable to reference the parameter elsewhere.

Assign a value to the parameter when the cursor is opened, the syntax is as follows:

OPEN cursor_name [value [, value]....]

Parameter values can be text or variables.

Example:

DECALRE CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno; CURSOR c_emp (p_dept VARACHAR2) IS SELECT ename,salary FROM emp WHERE deptno=p_dept ORDER BY ename r_dept DEPT%ROWTYPE; v_ename EMP.ENAME%TYPE; v_salary EMP.SALARY%TYPE; v_tot_salary EMP.SALARY%TYPE; BEGIN OPEN cpermission dept; LOOP FETCH c_dept INTO rspell dept; EXIT WHEN c_dept%NOTFOUND DBMS_OUTPUT.PUT_LINE ('Department:' | | r_dept.deptno | |' -'| | r_dept.dname); OPEN c_emp (r_dept.deptno); LOOP FETCH c_emp INTO voluename _ CLOSE cymbal emptiness; DBMS_OUTPUT.PUT_LINE ('Toltal Salary for dept:' | | v_tot_salary); END LOOP; CLOSE clocked dept; END

Vernier FOR loop

Most of the time we follow these steps when designing programs:

1. Open the cursor

2. Start the cycle

3. Take a value from the cursor

4. Check which line is returned

5. Deal with

6. Close the loop

7. Close the cursor

You can simply refer to this type of code as cursors for loops. But there is another loop that is different from this type, and this is the FOR loop, which is used for

The cursor of the FOR loop is declared in a normal way, and its advantage is that it does not need to explicitly open, close, fetch data, test the existence of data, define variables to store data, and so on.

The syntax of the cursor FOR loop is as follows:

FOR record_name IN (corsor_name [(parameter [, parameter]...)] | (query_difinition) LOOP statements END LOOP

Let's rewrite the above example with a for loop:

DECALRE CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno; CURSOR c_emp (p_dept VARACHAR2) IS SELECT ename,salary FROM emp WHERE deptno=p_dept ORDER BY ename v_tot_salary EMP.SALARY%TYPE;BEGIN FOR r_dept IN c_dept LOOP DBMS_OUTPUT.PUT_LINE ('Department:' | | r_dept.deptno | |' -'| | r_dept.dname); v_tot_salary:=0 FOR r_emp IN c_emp (r_dept.deptno) LOOP DBMS_OUTPUT.PUT_LINE ('Name:' | | v_ename | |' salary:' | | v_salary); END LOOP; DBMS_OUTPUT.PUT_LINE ('Toltal Salary for dept:' | | v_tot_salary); END LOOP; END

Using queries in cursor FOR loops

The query can be defined in the cursor FOR loop, the cursor has no name because it is not explicitly declared, and the record name is defined by the cursor query.

DECALRE v_tot_salary EMP.SALARY%TYPE; BEGIN FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP DBMS_OUTPUT.PUT_LINE ('Department:' | | r_dept.deptno | |' -'| | r_dept.dname); v_tot_salary:=0 FOR r_emp IN (SELECT ename,salary FROM emp WHERE deptno=p_dept ORDER BY ename) LOOP DBMS_OUTPUT.PUT_LINE ('Name:' | | v_ename | |' salary:' | | v_salary); END LOOP; DBMS_OUTPUT.PUT_LINE ('Toltal Salary for dept:' | | v_tot_salary); END LOOP; END

Subqueries in cursors

The syntax is as follows:

CURSOR C1 IS SELECT * FROM emp WHERE deptno NOT IN (SELECT deptno FROM dept WHERE dnameplate accounts)

You can see that it is no different from a subquery in SQL.

Updates and deletes in cursors

Data rows can still be updated or deleted in PL/SQL using UPDATE and DELETE statements. Explicit cursors only if you need to get multiple rows of data

Use it under the circumstances. PL/SQL provides a way to delete or update records simply by using cursors.

The WHERE CURRENT OF substring in the UPDATE or DELETE statement specifically handles the most recent extracted from the table to perform the UPDATE or DELETE operation

data. To use this method, you must use the FOR UPDATE substring when declaring the cursor. When the conversation opens a cursor using the FOR UPDATE substring, all rows of data in the returned set will be locked at the row level (ROW-LEVEL)

His object can only query these rows of data and cannot perform UPDATE, DELETE, or SELECT...FOR UPDATE operations.

Syntax:

FOR UPDATE [OF [schema.] table.column [, [schema.] table.column]..

[nowait]

In a multi-table query, the OF clause is used to lock a specific table, and if the OF clause is omitted, all selected data rows in the table are locked.

If these data rows have been locked by another session, then normally ORACLE will wait until the data rows are unlocked.

The syntax for using WHERE CURRENT OF substrings in UPDATE and DELETE is as follows:

WHERE {CURRENT OF cursor_name | search_condition}

Example:

DELCARE CURSOR C1 IS SELECT empno,salary FROM emp WHERE comm IS NULL FOR UPDATE OF comm; v_comm NUMBER (10 IN 2); BEGIN FOR R1 IN C1 LOOP IF r1.salary

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.