Implementation of multi-table cascade update with Cursors in Oracle

Cascading updates using the nominal:

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 = ' 2008-2009 ' 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 loop the cursor to update 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_ = ' 2008-2009 '                     and t.id_ = everyrow.id_;        end Loop;       commit; End;

The above is an introduction to the use of cursors to the example, we first introduce about cursor usage

Categories of cursors: Static cursors and dynamic cursors

1. Before the description of the cursor classification, first explain the pl/sql of the two operations, cursor properties, variable properties

SET serveroutput on;--this statement is used to turn on the print output feature

Dmbs_output. Put_Line ();--Print the output and wrap the line.

%found: Determines whether the current cursor points to a valid row record. Yes returns true, otherwise returns false

%notfound: Exactly the opposite of%found.

%isopen: Determines whether the cursor is open, returns True if it is opened, or returns false.

%rowcount: Determines the number of rows fetched by the current cursor in the result set to which it is pointing. Not all of the total records

%TYPE: Declares that the type of a variable is exactly the same as the data type of a column in the table.%TYPE declaration has two advantages: one is that you don't have to know the exact data type of a column. The second is that when the data type of a column changes, the data type of the variable is not modified.

%rowtype: Declares that the data type of a variable is the same as the row record data type in the table. For custom records, you must declare your own domain.

2. Static cursors:

Static cursors can be divided into two categories:

1) Implicit Cursors

The characteristics of an implicit cursor:

-Implicit cursors are managed by pl/sql, meaning that you do not need to declare a cursor sentence, and you do not need a open,fetch,close action

-An implicit cursor must have a select Cur_name into [variable name or other data type]. This sentence completes the open,fetch,close operation.

-An implicit cursor can return only one row of records, and a No_data_found exception will occur if no qualifying records are found. If more than one record appears, a Too_many_rows exception appears.

-An implicit cursor can only judge its cursor properties with sql%

-The Sql%isopen result is false for any location, and the implicit cursor is managed by the Pl/sql

-Sql%rowcount result value is null (indeterminate value) for use of Sql%found or sql%notfound before the position of the implicit cursor