如何创建与表中记录类型相同的集合? [英] How can I create a collection of the same type as a record in a table?
问题描述
背景
我正在尝试创建一个从审计列表中移出数据的程序 定期将数据表存储到其他数据库.
I'm trying to create a procedure that moves data from a list of audit tables to a different database periodically.
为避免产生大量撤消，我想在 时间.因此，我将大量数据收集到 每个审计表的集合，每个审计表的最大限制为1000条记录 时间，将数据插入远程表，然后删除 总共获取了1000条记录.
To avoid generating massive undo, I want to do this 1000 records at a time. For this reason, I will be bulk collecting the data into a collection for each audit table with a limit of 1000 records at a time, inserting the data into the remote table and then deleting the fetched 1000 records in the forall.
我还将执行其他各种任务(记录数据迁移 每个表格并发送通知.
I will also be doing various other tasks (logging the data migration for each table and sending notifications.
由于审核表的数量很大，所以我不想 为每个表创建一个过程.所以，我需要能够 动态创建与 桌子.
Since, the number of audit tables is quite large, I don't want to create a procedure for every table. So, I need to be able to dynamically create a collection that is of the same structure as a table.
问题
如何创建与记录表中表中记录类型相同的集合 PL/SQL过程?
How can I create a collection of the same type as a record in a table inside a PL/SQL procedure?
注意:在代码运行的那一刻，集合只需要与表具有相同的结构.我不希望集合会 动态变化-我知道这是不可能的.仅在过程运行的那一刻，才需要使用与表相同的结构来创建它，以便我可以将其用于复制操作.
Note: the collection only needs to be of the same structure as the table at that instant when the code runs. I don't expect the collection to dynamically change - I know that's not possible. Only at the instant that the procedure runs, it needs to be created with the same structure as the table, so that I can use it for the copy operations.
我已经尝试过的内容
我尝试尝试根据数据创建动态SQL语句 字典.我意识到我可以针对常见类型执行此操作， 能够处理精度，varchar2字节vs char等.但是我认为 对于某些其他数据类型/精度组合可能会失败.
I tried trying to create a dynamic SQL statement from the data dictionary. I realized that I can do this for common types, and I'm able to handle precision, varchar2 byte vs char, etc.. But I think it might fail for some other data types/precisions combinations.
推荐答案
问题
如何创建与表中记录类型相同的集合 在PL/SQL过程中?
How can I create a collection of the same type as a record in a table inside a PL/SQL procedure?
您可能需要%ROWTYPE声明，请看一个非常简单的示例:
You probably need %ROWTYPE declaration, please take a look at very simple example:
CREATE TABLE abc111(
x int,
y varchar2(100)
);
CREATE TABLE abc222 AS SELECT * FROM abc111;
INSERT INTO abc111
SELECT level , rpad(level, 10 ,level)
FROM dual
CONNECT BY LEVEL <= 10;
DECLARE
-- ====> here %ROWTYPE declaration is used <========
TYPE type_name IS TABLE OF abc111%ROWTYPE;
rec_var type_name;
BEGIN
SELECT * BULK COLLECT INTO rec_var
FROM abc111;
FORALL indx IN 1 .. rec_var.COUNT
INSERT INTO abc222 VALUES rec_var( indx ) ;
END;
/
SELECT * FROM abc222;
X Y
---------- --------------
1 1111111111
2 2222222222
3 3333333333
4 4444444444
5 5555555555
6 6666666666
7 7777777777
8 8888888888
9 9999999999
10 1010101010
10 rows selected
这篇关于如何创建与表中记录类型相同的集合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
