Oracle Database cursors use

oracle| Data | database | Cursor SQL is the language used to access the Oracle database, Pl/sql expands and strengthens the functionality of SQL, while introducing stronger program logic. Pl/sql supports DML commands and SQL transaction control statements. DDL is not supported in Pl/sql, which means that a table or any other object cannot be created in a PL/SQL program block. A better Pl/sql program is to execute DDL commands in Pl/sql blocks using built-in packages such as Dbms_sql or execute the Execute immediate command, pl/sql the compiler to guarantee object references and user permissions.

Here we discuss various DDL and TCL statements for accessing the Oracle database.

Inquire

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, and the return value of the query is given to the variable in the INTO clause, and the declaration of the variable is 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 you have more than one row of data, use an explicit cursor (we'll do the discussion of the cursor later), and in the INTO clause we want to have a variable with the same number of columns in the SELECT clause. INTO clause can also be a record variable.

%type Property

In Pl/sql, variables and constants can be declared as built-in or user-defined data types to refer to a column name while inheriting his data type and size. This dynamic assignment method is useful, such as the data type and size of the column referenced by the variable, and if the%type is used, the user does not have to modify the code, otherwise the code must be changed.

Cases:

V_empno SCOTT. Emp. Empno%type;
V_salary EMP. Salary%type;

Not only the column names can use%type, but also variables, cursors, records, or declared constants can use%type. This is useful for variables that define the same data type.

Delcare
V_a Number (5): = 10;
V_b v_a%type:=15;
V_c V_a%type;
BEGIN
Dbms_output. Put_Line
(' V_a= ' | | v_a| | ' V_b= ' | | v_b| | ' V_c= ' | | V_c);
End

sql>/
v_a=10 v_b=15 v_c=
Pl/sql procedure successfully completed.

Sql>

Other DML statements

The DML statements for other operational data are: INSERT, UPDATE, delete, and lock TABLE, which have the same syntax in Pl/sql as in SQL. We've discussed the use of DML statements before and we don't repeat them here. You can use any variable declared in the Declare section in a DML statement, and if it is a nested block, pay attention to the scope of the variable.

Cases:

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 (P_empno,v_ename);

DELETE from EMP
WHERE Empno=p_empno;

UPDATE former_emp
SET date_deleted=sysdate
WHERE Empno=p_empno;

EXCEPTION
When No_data_found THEN
Dbms_output. Put_Line (' Employee number not found! ');

End

Results of DML statements

When a DML statement is executed, the result of the DML statement is saved in four cursor properties, which are used for the control process or to understand the state of the program. When you run a DML statement, Pl/sql opens a built-in cursor and processes the result, which is an area of memory that maintains the results of the query, which is opened when the DML statement is run, and closed when it is finished. An implicit cursor uses only sql%found,sql%notfound,sql%rowcount three properties. Sql%found,sql%notfound is a Boolean, Sql%rowcount is an integer value.

Sql%found and Sql%notfound

The values for Sql%found and sql%notfound before any DML statements are executed are null, and after the DML statement is executed, the Sql%found property value will be:

. True:insert

. TRUE:D elete and update, at least one row is delete or update.

. True:select to return at least one row

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

Sql%rowcount

Before any DML statements are executed, the value of the sql%rowcount is null, and for the SELECT INTO statement, if the execution succeeds, the Sql%rowcount value is 1, and if not successful, the Sql%rowcount value is 0 and an exception is generated No_ Data_found.

Sql%isopen

Sql%isopen is a Boolean value, true if the cursor is open, or false if the cursor is closed. Sql%isopen is always false for an implicit cursor, because an implicit cursor is opened at the time the DML statement is executed, and closes immediately when it finishes.

Transaction CONTROL Statement

A transaction is a working logical unit that can include one or more DML statements, and things control helps the user to ensure data consistency. If any of the DML statements in the transaction control logical unit fail, the entire transaction is rolled back, and in Pl/sql the user can explicitly use the commit, ROLLBACK, SavePoint, and set transaction statements.

The commit statement terminates the transaction, permanently saves the changes to the database while releasing all lock,rollback terminating the current transaction releasing all lock, but not saving any changes to the database, SavePoint is used to set the middle point, and when the transaction invokes too many database operations, Intermediate points are useful, set transaction are used to set transaction properties, such as read-write and isolation levels.

An explicit cursor

When a query returns more than one row, an explicit cursor is required, at which point the user cannot use the SELECT INTO statement. Pl/sql manages implicit cursors, which are open when the query starts, and when the query ends, the implicit cursor closes automatically. An explicit cursor is declared in the declaration portion of a pl/sql block, opened, fetched, and closed in the execution part or exception handling section. The following table shows the difference between an explicit and an implicit cursor:

Table 1 Implicit and explicit cursors
Implicit cursor Explicit cursors
Pl/sql maintenance, which automatically turns on and off when the query is executed, explicitly defined, turned on, closed in the program, and the cursor has a name.
The cursor property prefix is a prefix of the SQL cursor property that is the cursor name
Property%isopen is always false%isopen determine values based on the state of the cursor
The SELECT statement has an into substring, and only one row of data is processed to handle multiple rows of data, a loop is set in the program, and each row of data is fetched.

Using cursors

To make a declaration here, the cursor we refer to is usually an explicit cursor, so there is no particular case from now on, and the cursor we are talking about is an explicit cursor. To use a cursor in a program, you must first declare the cursor.

declaring cursors

Grammar:

CURSOR cursor_name is select_statement;

The Pl/sql is an undeclared variable and cannot be assigned to a cursor name or used in an expression.

Cases:

Delcare
CURSOR C_emp is SELECT empno,ename,salary
From EMP
WHERE salary>2000
Order by ename;
........
BEGIN

In a cursor definition, the SELECT statement does not necessarily need a table to be a view, a column that can be selected from more than one table or view, or even use the * to select all columns.

Open cursor

You should first open the cursor and open the cursor initialization query processing before using the value in the cursor. The syntax for opening cursors is:

OPEN cursor_name

Cursor_name is the name of the cursor defined in the Declarations section.

Cases:

OPEN c_emp;

Close cursor

Grammar:

Close cursor_name

Cases:

Close c_emp;

Extracting data from a cursor

Get a row of data from a cursor using the FETCH command. Each time the data is fetched, the cursor points to the next row in the result set. The syntax is as follows:

FETCH cursor_name into variable[,variable,...]

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

Cases:

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
||' Is ' | | V_salary);
FETCH c_emp into V_ename,v_salary;
Dbms_output. Put_Line (' Salary of Employee ' | | v_ename
||' Is ' | | V_salary);
FETCH c_emp into V_ename,v_salary;
Dbms_output. Put_Line (' Salary of Employee ' | | v_ename
||' Is ' | | V_salary);
Close c_emp;
End

This code is undoubtedly very troublesome, if there are multiple rows to return results, you can use the loop and the cursor properties to end the loop condition, in this way to extract data, the program's readability and simplicity are greatly improved, the following we use the loop to write 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 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
||' Is ' | | V_salary);
End

Record variable

Define a record variable use the type command and%rowtype, for more information about%rowstype see related information.

Record variables are used to extract rows of data from a cursor, and when a cursor chooses many columns, it is much more convenient to use a record than to declare a variable for each column.

If you want to select all the columns in a table when you use%rowtype on a table and put the values taken from the cursor into the record, it is much safer to use * than to list all columns in the SELECT clause.

Cases:

SET Serveriutput on
DECLARE
R_emp Emp%rowtype;
CURSOR C_emp is a SELECT * from EMP;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp into r_emp;
EXIT when C_emp%notfound;
Dbms_out. Put. Put_Line (' Salary of Employee ' | | r_emp.ename| | ' Is ' | | R_emp.salary);
End LOOP;
Close c_emp;
End;

%rowtype can also be defined with a cursor name, which means that the cursor must be declared first:

SET Serveriutput on
DECLARE
CURSOR C_emp is SELECT ename,salary from EMP;
R_emp C_emp%rowtype;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp into r_emp;
EXIT when C_emp%notfound;
Dbms_out. Put. Put_Line (' Salary of Employee ' | | r_emp.ename| | ' Is ' | | R_emp.salary);
End LOOP;
Close c_emp;
End;

Cursors with parameters

Similar to stored procedures and functions, you can pass parameters to a cursor and use it in a query. This is useful for handling situations where cursors are opened under certain conditions. 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 passed values and cannot return values. The parameter defines only the data type, no size.

You can also set a default value for the parameter, and use the default value when no parameter values are passed to the cursor. The parameter defined in the cursor is only a placeholder, and it is not necessarily reliable to reference the parameter elsewhere.

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

OPEN Cursor_name[value[,value] ...];

Parameter values can be literals or variables.

Cases:

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 c_dept;
LOOP
FETCH c_dept into r_dept;
EXIT when C_dept%notfound;
Dbms_output. Put_Line (' Department: ' | | | r_dept.deptno| | ') -'|| R_dept.dname);
v_tot_salary:=0;
OPEN c_emp (R_DEPT.DEPTNO);
LOOP
FETCH c_emp into V_ename,v_salary;
EXIT when C_emp%notfound;
Dbms_output. Put_Line (' Name: ' | | | v_ename| | ' Salary: ' | | V_salary);
V_tot_salary:=v_tot_salary+v_salary;
End LOOP;
Close c_emp;
Dbms_output. Put_Line (' Toltal Salary for dept: ' | | v_tot_salary ');
End LOOP;
Close c_dept;
End;



Cursor FOR loop

Most of the time we are in the process of designing a program by following these steps:

1. Open cursor

2. Start cycle

3. Take a value from the cursor

4. Check that line is returned

5, processing

6, Closed cycle

7, close the cursor

This type of code can be simply referred to as a cursor for looping. But there is a kind of loop that is not the same type, this is the for loop, the cursor for the for loop is declared as normal, and its advantage is that there is no need to explicitly open, close, fetch data, test the presence of data, define variables that hold data, and so on. The syntax for a cursor for loop is as follows:

For Record_name in
(corsor_name[(Parameter[,parameter] ...)]
| (query_difinition)
LOOP
Statements
End LOOP;

Here we use the For loop to rewrite the above example:

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);
V_tot_salary:=v_tot_salary+v_salary;
End LOOP;
Dbms_output. Put_Line (' Toltal Salary for dept: ' | | v_tot_salary ');
End LOOP;

End;

Using queries in a cursor for loop

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

Decalre

V_tot_salary EMP. Salary%type;

BEGIN

For r_dept in (SELECT deptno,dname to 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);
V_tot_salary:=v_tot_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 dname!= ' ACCOUNTING ');

You can see that there is no difference between subqueries in SQL.

Updates and deletions in cursors

You can still update or delete rows of data using the update and DELETE statements in Pl/sql. An explicit cursor is used only if you need to obtain multiple rows of data. Pl/sql provides a way to delete or update records simply by using a cursor.

The WHERE current of substring in an UPDATE or DELETE statement specializes in the most recent data taken out of the table to perform an update or delete operation. To use this method, you must use a for update substring when declaring a cursor, and when a dialog uses a for update substring to open a cursor, all data rows in the returned set are in row-level (row-level) exclusive locks, and other objects can only query the rows of data. Cannot make update, delete, or select ... For update operation.

Grammar:

for UPDATE [of [Schema.] Table.column[,[schema.] Table.column].
[NoWait]

In a multiple-table query, use the OF clause to lock a particular table, and if the of clause is omitted, the rows of data selected in all tables are locked. If these rows of data are already locked by another session, Oracle will normally wait until the data row unlocks.

The syntax for using the where current of substring in update and delete is as follows:

Where{current of Cursor_name|search_condition}

Cases:

Delcare

CURSOR C1 is SELECT empno,salary
From EMP
WHERE Comm is NULL
for UPDATE of Comm;

V_comm number (10,2);

BEGIN

For R1 in C1 loop

IF r1.salary<500 THEN
v_comm:=r1.salary*0.25;
ELSEIF r1.salary<1000 THEN
v_comm:=r1.salary*0.20;
ELSEIF r1.salary<3000 THEN
v_comm:=r1.salary*0.15;
ELSE
v_comm:=r1.salary*0.12;
End IF;

UPDATE EMP;
SET Comm=v_comm
WHERE Current of c1l;

End LOOP;
End