Oracle cursor problem: Waiting for expert advice

Problems encountered at work:

1. There is a custom dictionary table: mdl_class_js

This table stores the names of many other tables (called resource tables). (In the mdl_class_js table, fields: table_name are used to represent these resource tables)

2. These resource tables exist in the Database and each table has a city field;

The city field values come from the city Field in a table named com_city;

 

The current task is:

To verify that the values of all city fields are not the names of the resource tables in the com_city table

(That is, find the resource tables with invalid City field values)

 

-- Write a cursor by yourselfAlgorithm(Stored Procedure representation ),

My solution is:

Use a cursor to extract non-repeated table_name fields from all mdl_class_js tables.

Then, use the cursor to extract the City field value from the current table_name (resource table ).

Check whether it exists in the com_city table (not in). If it does not exist, insert these table resource tables into the check results.

However, after compilation and translation, the table in the SELECT statement in the nested cursor is dynamic.

Or you can solve it in other ways. Thank you!

The following is what I wrote:Code

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

Create or replace procedure com_check_citynull is
Begin
--- Define the cursor and cycle mdl_class_js
Declare cursor fetch_mdl_class is
Select distinct table_name, name from mdl_class_js;

-- % Rowtype indicates that all fields in the table are of the same type as all corresponding fields in each row read in mdl_class.
Tbname fetch_mdl_class % rowtype;

Table_name varchar2 (100); -- table English name tablename
Cnname varchar2 (100); -- Chinese Table Name
Strsql varchar2 (500 );
Begin
Open fetch_mdl_class;
Loop
-- Extract data from the cursor
Fetch fetch_mdl_class into tbname;
Exit when fetch_mdl_class % notfound;

Table_name: = '0 ';
Cnname: = '0 ';

Strsql: = 'select city from' | tbname. table_name;
Declare cursor fetch_tbname is
Execute immediate strsql;
City_value fetch_tbname % rowtype;
Begin
Open fetch_tbname;
Loop
-- Obtain the field name
Fetch fetch_tbname into city_value;
Exit when fetch_tbname % notfound;

If city_value is not in (select city from com_city) then
Table_name: = tbname. table_name;
Cnname: = tbname. Name;
End if;
End loop;
End;
If table_name! = '0' and cnname! = '0' then

-- Insert the verified table name and Chinese table name to the verification result table.
Insert into com_check_result (rule_id, table_en_name, table_ch_name) values ('2013', table_name, cnname );
End if;
End loop;
End;
End com_check_citynull;

Bytes ---------------------------------------------------------------------------------------------------------------------------

 

Additional cursor information:

(Oracle cursor Daquan)

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 type and size.. 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 need to modify the code, otherwise, 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;

-- Declare a variable of the same size as v_a
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 controlProgramProcess 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. Implicit cursors only use SQL % found, SQL % notfound, and SQL % rowcount attributes.

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 elete 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.

 

Explicit 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.

 

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, the SELECT statement does not have to be a view. You can also select columns from multiple tables or views. You can 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

-- Define variable name: The type and size are consistent with those obtained by select.
V_ename EMP. ename % type;
V_salary EMP. Salary % type;
Cursor c_emp is select ename, salary from EMP;
Begin

-- Open
Open c_emp;

-- Extract the value assignment variable from the cursor
Fetch c_emp into v_ename, v_salary;
Dbms_output.put_line ('salary of employee' | v_ename | 'ais '| v_salary );
Fetch c_emp into v_ename, v_salary;
Dbms_output.put_line ('salary of employee' | v_ename | 'ais '| v_salary );
Fetch c_emp into v_ename, v_salary;
Dbms_output.put_line ('salary of employee' | v_ename | 'ais '| 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;

-- Cyclic output variable
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 | 'ais '| v_salary );
End

Record variable 

The % rowtype type declaration determines that all fields in the declared variable name (expressed as tables) are of the same type as the Fields read by the SELECT statement in the cursor (in the same order.
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, then, using * in the select clause is much more than listing all columns. 

Example:
Set serveriutput on
Declare

-- Declare the % rowtype variable (table)
R_emp EMP % rowtype;

-- Define the cursor c_emp
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;

 

Note: % rowtype can also be defined by the cursor name. In this way, you must first declare the cursor:

Set serveriutput on
Declare

-- Define a cursor first
Cursor c_emp is select ename, salary from EMP;

-- Use the cursor name (c_emp) to define a variable of the % rowtype (table name)
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
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 preceding example 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