Detailed description of Multi-Table cascade update using a cursor in Oracle

Using a cursor in Oracle, I think this method is absolutely safe. -- first, set a cursor to store some data that needs to be used in the cursor, and then use the traversal cursor to update it.

Use name of cascading update:

The Code is as follows:	Copy code
Create table A001 (id int primary key, name varchar (20 )) Create table A002 (id int references A001 (ID) on delete cascade on update cascade, age tinyint)

Example 1

The Code is as follows:

Copy code

Declare CURSOR D_CURSOR_CUS_INFO IS Select t3.id _ as id _, T3.owe _ money _ as owe_money _, A. heatingArea as heating_area _ From T_CUS_OWE_MONEY_2 t2 Left join T_CUS_OWE_MONEY_3 t3 on t2.id _ = t3.id _ Left join (select s. bh, sum ( Case When s. stkbz = '0' then nvl (s. mj, 0) When s. stkbz = '1' then 0-nvl (s. mj, 0) End ) As heatingArea from sk s where s. nd = '2017-2008 'group by s. bh) a on t2.bh _ = a. bh Where t3.owe _ money _-t2.owe _ money _ = a. heatingArea * 5 and t3.OWE _ MONEY _> 0;

-- Then the cyclic cursor updates the data:

 

The Code is as follows:	Copy code
Begin FOR everyRow IN D_CURSOR_CUS_INFO Loop Update T_CUS_YEAR_STATUS t Set t. HEATING_AREA _ = everyRow. HEATING_AREA _, T. OWE_MONEY _ = everyRow. owe_money _ Where t. YEAR _ = '2017-2008' And t. id _ = everyRow. id _; End loop; Commit; End;

The previous section describes how to use a cursor for an instance. Let's first introduce the usage of a cursor.

Categories of cursors: static cursors and dynamic cursors

1. Before specifying the cursor type, describe the two PL/SQL operations, cursor attributes and variable attributes.

Set serveroutput on; -- this statement is used to enable the print output function.

DMBS_OUTPUT.PUT_LINE (); -- print the output and wrap it.

% FOUND: determines whether the current cursor points to a valid row record. If yes, TRUE is returned; otherwise, FALSE is returned.

% NOTFOUND: opposite to % FOUND.

% ISOPEN: determines whether the cursor is opened. TRUE is returned if the cursor is opened. Otherwise, FALSE is returned.

% ROWCOUNT: determines the number of rows extracted by the current cursor in the result set to which it points. Not all total records

% TYPE: the declared variable TYPE is exactly the same as the data TYPE of a column in the table. the % TYPE declaration has two advantages: First, you do not need to know the exact data TYPE of a column; second, you do not need to modify the Data TYPE of a variable when the data TYPE of a column changes.

% ROWTYPE: the declared variable data type is the same as the row record data type in the table. For custom records, you must declare your own domain.

2. Static cursor:

Static cursors can be divided into two types:

1) Implicit cursor

Implicit cursor features:

-Implicit cursors are managed by PL/SQL, that is, they do not need to declare cursor statements or OPEN, FETCH, or CLOSE operations.

-The implicit cursor must contain select cur_name into [variable name or other data types]. The OPEN, FETCH, and CLOSE operations are completed.

-An implicit cursor can only return one row of records. If no matching record exists, the NO_DATA_FOUND exception will occur. If multiple records exist, the TOO_MANY_ROWS exception will occur.

-Implicit cursors can only use SQL % to determine their cursors.

-If SQL % ISOPEN is used at any location, the result is FALSE. The implicit cursor is managed by PL/SQL.

-If SQL % FOUND or SQL % NOTFOUND is used before the implicit cursor position, the SQL % ROWCOUNT result value is NULL (uncertain value)