Oracle Study Notes: use Oracle's "Table type" Compound variable to implement the temporary table function

Scenarios and requirements:

Table A is a fact table, and table B is a historical table.

Create Table A (fact_id int not null primary key, name varchar2 (50 ));

Create Table B (log_id int not null primary key, name varchar2 (50), addtime timestamp );

Requirement: establish the Stored Procedure PRC, externalProgramConcurrent periodic calls. In this process, a certain amount of data (record set) is obtained from table A and backed up to table B, and the data (record set) of this batch of backup is deleted from table ), at the same time, the batch of data (record set) is returned to the external program for use by the external program.

 

Analysis:

To meet the preceding requirements, we first consider that this process is used for parallel processing. We need to isolate transactions for each processing process-both read isolation and select... from a for update implementation. The number of records processed each time can be achieved through the "rowcount int" input parameter of the subscription stored procedure. The returned record set can be implemented through an "RS out ref_syscursor. What is the key aspect? Generally, we can define a temporary table t at the transaction level to implement it: A-> T, T [+ A]-> B, delete t from a, and return T's record set, delete T. Although Oracle temporary tables have been optimized for a lot of performance and have their own characteristics, but it is still the practice of dealing with disks-if the temporary table has a large amount of data-this is a good choice, but if the intermediate data volume is not large, is it better to use the memory variable structure! For this reason, sqlserver provides the table variable mechanism, while the Oracle Compound Data Type [or called Set Data Type] "Table type" solves this problem, for collections similar to standard SQL, Oracle provides bulk collect and for all operations used in PL/SQL.

 

ImplementationCode:

Create or replace procedure PRC
(
rowcount int,
Rs out sys_refcursor
)
as
type t_fact_id is table of int; -- Define the table Type
vt_fact_id t_fact_id; -- Define the table Type Variable
v_first_fact_id int;
v_last_fact_id int;
begin
-- Obtain the fact_id field of A in batches to
select fact_id bulk collect
into vt_fact_id
from
where rownum <= rowcount
for update
order by fact_id;

-- Batch insert to B
Forall I in vt_fact_id.first... vt_fact_id.last
Insert into B
Select a. *, sysdate
From
Where fact_id = vt_fact_id (I );

-- Obtain the minimum and maximum values of fact_id inserted to Table A, mainly to define the conditions of the output result set.
V_first_fact_id: = vt_fact_id (vt_fact_id.first );
V_last_fact_id: = vt_fact_id (vt_fact_id.last );

-- Output result set
Open RS
Select *
From
Where fact_id between v_first_fact_id and v_last_fact_id;

-- Batch delete records in
Forall I in vt_fact_id.first... vt_fact_id.last
Delete from
Where fact_id = vt_fact_id (I );

Commit;

Exception
When others then
Dbms_output.put_line (sqlerrm );
Open RS
Select *
From
Where 0 = 1;
End;

 

Discussion:

1. if the primary key column fact_id of Table A is directly inserted to table B, you can use DML (delete) the returning fact_id bulk collect into vt_fact_id to obtain the fact_id table-type variable data of the record set. In this example, select from a for update first, and then obtain the returned result set from Table B.

2. In the example, vt_fact_id is not processed in the normal program, but is processed in the exception part instead.