Oracle cursor usage (zz)

SQL is the language used to access the Oracle database. PL/SQL expands and enhances SQL functions. It also introduces stronger Program Logic. PL/SQL supports DML commands and SQL transaction control statements. DDL is not supported in PL/SQL, which means that tables or other objects cannot be created in PL/SQL blocks. A good PL/SQL program is designed to use built-in packages such as dbms_ SQL in PL/SQL blocks or execute the execute immediate command to create dynamic SQL statements to execute DDL commands, the PL/SQL Compiler guarantees object reference and user permissions.

The following describes various DDL and Tcl statements used to access the Oracle database.

Query

The SELECT statement is used to query data from the database. When the SELECT statement is used in PL/SQL, it must be used with the into clause. The return value of the query is assigned to the variable in the into clause, variable declaration 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 ............

In PL/SQL, the SELECT statement returns only one row of data. If a row of data is exceeded, an explicit cursor is used (we will discuss the cursor later). The into clause must contain variables with the same number of columns as the select clause. The into clause can also be a record variable.

% Type attribute

In PL/SQL, you can declare variables and constants as built-in or user-defined data types to reference a column name and inherit its data types and sizes. This dynamic value assignment method is very useful. For example, the data type and size of the columns referenced by variables have changed. If % type is used, you do not have to modify it.CodeOtherwise, you must modify the code.

Example:

V_empno Scott. EMP. empno % type; V_salary EMP. Salary % type;

Not only can the column name use % type, but also variables, cursors, records, or declared constants can use % type. This is useful for defining variables of 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 data operations are insert, update, delete, and lock table. the syntax of these statements in PL/SQL is the same as that in SQL. We have discussed the usage of DML statements before. In DML statements, you can use any variable declared in the declare section. If it is a nested block, 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 (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

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.

DisplayType 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. The following table shows the differences between explicit and implicit cursors:

Table 1 implicit and explicit cursors

Implicit cursor	Explicit cursor
PL/SQL maintenance. It is automatically enabled and disabled when queries are executed.	In a program, the cursor is explicitly defined, opened, and closed, and has a name.
The cursor attribute prefix is SQL	The prefix of the cursor attribute is the cursor name.
Attribute % isopen is always false	% Isopen determines the value based on the cursor status
The SELECT statement has an into sub-string and only one row of data is processed.	It can process multiple rows of data and set a loop in the program to retrieve each row of data.

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 | '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 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 | 'Is' | v_salary ); End

Record variable

Define a record variable using the type command and % rowtype. For more information about % rowstype, see related materials.

Record variables are used to extract data rows from the cursor. When multiple columns are selected for the cursor, it is much easier to use records than to declare a variable for each column.

When % rowtype is used in the table and the value retrieved from the cursor is put into the record, if you want to select all columns in the table, therefore, using * in a select clause is much safer than listing all columns.

Example:

Set serveriutput on Declare R_emp EMP % rowtype; Cursor c_emp is 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 by the cursor name. In this way, 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;

Parameter-based cursor

Similar to stored procedures and functions, you can pass parameters to the cursor and use them in the query. This is useful for processing the case where a cursor is opened under certain conditions. Its syntax is as follows:

Cursor cursor_name [(parameter [, parameter],...)] is select_statement;

Syntax for defining parameters:

Parameter_name [in] data_type [{: = | default} value]

Unlike stored procedures, a cursor can only accept passed values, but cannot return values. The parameter only defines the data type and has no size.

In addition, you can set a default value for the parameter. When no parameter value is passed to the cursor, the default value is used. The parameter defined in the cursor is just a placeholder. It is not necessarily reliable to reference it elsewhere.

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

Open cursor_name [value [, value]...];

The parameter value can be text or variable.

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 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 design a program, we follow the steps below:

1. Open the cursor

2. Start Loop

3. values from the cursor

4. Check that the row is returned.

5. Processing

6. close the loop

7. Close the cursor

You can simply call this type of code a cursor for a loop. However, there is another type of loop that is different from this type. This is the for loop. The cursor used for the for loop is declared as normal, it does not need to explicitly open, close, retrieve data, test data, define data storage variables, 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;

The example above is rewritten using 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 ); V_tot_salary: = v_tot_salary + v_salary; End loop; Dbms_output.put_line ('toltal salary for dept: '| v_tot_salary ); End loop; End;

Use query in a cursor For Loop

You can define a query in a cursor for loop. Because no explicit declaration is made, the cursor has no name, and the record name is defined through 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 ); V_tot_salary: = v_tot_salary + v_salary; End loop; Dbms_output.put_line ('toltal salary for dept: '| v_tot_salary ); End loop; End;

Subquery in cursor

Syntax:

Cursor C1 is select * from EMP Where deptno not in (select deptno From Dept Where dname! = 'Accounting ');

It can be seen that it is no different from subqueries in SQL.

Update and delete In cursor



You can still use the update and delete statements to update or delete data rows in PL/SQL. An explicit cursor is used only when multiple rows of data are required. PL/SQL allows you to delete or update records by using only the cursor.

The where current of substring in the update or delete statement is used to process the most recent data retrieved from the table for the update or delete operation. To use this method, you must use the for update substring when declaring the cursor. When you use the for update substring to open a cursor, all data rows in the returned result set will be locked exclusively at the row level. Other objects can only query these data rows and cannot perform update, delete, or select... for update operation.

Syntax:

For update [of [schema.] Table. Column [, [schema.] Table. Column]... [Nowait]

In multi-table queries, the "of" clause is used to lock a specific table. If the "of" clause is ignored, all selected data rows in the table are locked. If these data rows have been locked by other sessions, Oracle will normally wait until the data row is unlocked.

The syntax for using where current of substring 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, 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