Example of Oracle cursor, recursive query, subquery, and batch update

-- Create a temporary table
Create Table tj_org_new
(
Deptcode nvarchar2 (255 ),
Orgdesc nvarchar2 (255 ),
Organtype nvarchar2 (255 ),
Levelfact number (6 ),
Parentdeptcode nvarchar2 (255)
)
Tablespace Test
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64 K
Minextents 1
Maxextents Unlimited
);
-- Initialize temporary table data
Insert into tj_org_new (deptcode, orgdesc, organtype, levelfact, parentdeptcode) Select deptcode, "org", organtype, level, parentdeptcode from "tj_org"
Connect by prior deptcode = parentdeptcode
Start with parentdeptcode = 1 order by level;

/*
Batch logical processing of data using cursors
*/
Declare
Varuserdeptcode varchar (255); -- defines the same type as the table Field
Cursor mycursor is -- defines the cursor
Select userdeptcode from tj_user;

My_record mycursor % rowtype; -- defines the cursor record type
Counter INT: = 0;

Begin
Open mycursor; -- open the cursor
If mycursor % isopen then -- determines whether the open is successful
Loop -- Obtain Record Sets cyclically
Fetch mycursor into my_record; -- get records in the cursor
If mycursor % found then -- the cursor's found attribute determines whether a record exists
-- Perform actual business processing begin
If my_record.userdeptcode = 90033751 then -- provincial-level user updates
Update tj_user set userorgcode = 90033751 where userdeptcode = 90033751;
Dbms_output.put_line (my_record.userdeptcode | 'A ');
Else -- Non-provincial users updated
Update tj_user set userorgcode =
(Select deptcode from (select deptcode, orgdesc, organtype, parentdeptcode, levelfact from tj_org_new
Connect by prior parentdeptcode = deptcode
Start with deptcode = my_record.userdeptcode
Order by levelfact) Where organtype = 1 and levelfact = 2)
Where (userdeptcode <> 90033751) and
(Userdeptcode in (select deptcode from tj_org_new
Connect by prior parentdeptcode = deptcode
Start with deptcode = my_record.userdeptcode ));
Dbms_output.put_line (my_record.userdeptcode | 'B ');
End if;
-- Actual business processing end
Else
Exit;
End if;
End loop;
Else
Dbms_output.put_line ('cursor not opened ');
End if;
Close mycursor;
End;