Oracle：将行类型数据插入另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13866915/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: Insert rowtype data into another table
提问by Frank
I have one table called event, and created another global temp table tmp_eventwith the same columns and definition with event. Is it possible to insert records in event to tmp_event using this ?
我有一个名为event 的表 ，并创建了另一个具有相同列和定义的全局临时表tmp_event。是否可以使用此将事件中的记录插入到 tmp_event 中？
DECLARE
v_record event%rowtype;
BEGIN
Insert into tmp_event values v_record;
END;
There are too many columns in event table, I want to try this because I don't want to list all the columns.
事件表中的列太多，我想尝试一下，因为我不想列出所有列。
Forget to mention: I will use this in the trigger, can this v_record be the object :newafter insert on EVENT table ?
忘了提：我将在触发器中使用它，这个 v_record 可以是对象：插入 EVENT 表后的新对象吗？
回答by AnBisw
To insert one row-
插入一行-
DECLARE
v_record event%rowtype;
BEGIN
SELECT * INTO v_record from event where rownum=1; --or whatever where clause
Insert into tmp_event values v_record;
END;
Or a more elaborate version to insert all rows from event-
或者更复杂的版本来插入所有行event-
DECLARE
TYPE t_bulk_collect_test_tab IS TABLE OF event%ROWTYPE;
l_tab t_bulk_collect_test_tab;
CURSOR c_data IS
SELECT *
FROM event;
BEGIN
OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 10000;
EXIT WHEN l_tab.count = 0;
-- Process contents of collection here.
Insert into tmp_event values v_record;
END LOOP;
CLOSE c_data;
END;
/
In a trigger, yes it is possible but its like the chicken or the egg. You have to initialize every field of the rowtypewith the :newcolumn values like-
在触发器中，是的，这是可能的，但就像鸡或蛋一样。您必须rowtype使用:new列值初始化每个字段，例如-
v_record.col1 := :new.col1;
v_record.col2 := :new.col2;
v_record.col3 := :new.col3;
....
Apparently, the PLSQL examples above cannot be used in a trigger since it would throw a mutating trigger error. And there is no other way for you to get the entire row in the trigger other than accessing each column separately as I explain above, so if you do all this why not directly use :new.colin the INSERT into temp_eventitself, will save you a lot of work.
显然，上面的 PLSQL 示例不能在触发器中使用，因为它会抛出一个变化的触发器错误。并且除了像我上面解释的那样分别访问每一列之外，没有其他方法可以让您在触发器中获取整行，所以如果您这样做，为什么不直接:new.col在其INSERT into temp_event本身中使用，将为您节省大量工作。
Also since you say it's a lot of work to mention all the columns, (in Oracle 11gR2) here's a quick way of doing that by generating the INSERTstatement and executing it dynamically (although not tested for performance).
此外，由于您说提及所有列需要做很多工作，（在Oracle 11gR2 中）这里有一种通过生成INSERT语句并动态执行它的快速方法（尽管未进行性能测试）。
CREATE OR REPLACE TRIGGER event_air --air stands for "after insert of row"
AFTER INSERT ON EVENT
FOR EACH ROW
L_query varchar2(2000); --size it appropriately
BEGIN
SELECT 'INSERT INTO tmp_event VALUES ('|| listagg (':new.'||column_name, ',')
WITHIN GROUP (ORDER BY column_name) ||')'
INTO l_query
FROM all_tab_columns
WHERE table_name='EVENT';
EXECUTE IMMEDIATE l_query;
EXCEPTION
WHEN OTHERS THEN
--Meaningful exception handling here
END;
回答by Nutan P
There is a way to insert multiple rows into table with %Rowtype.
有一种方法可以使用 %Rowtype将多行插入到表中。
checkout below example.
结帐下面的例子。
DECLARE
TYPE v_test IS TABLE OF TEST_TAB%rowtype;
v_test_tab v_test ;
EXECUTE immediate ' SELECT * FROM TEST_TAB ' bulk collect INTO v_test_tab ;
dbms_output.put_line('v_test_tab.count -->'||v_test_tab.count);
FOR i IN 1..v_test_tab.count
LOOP
INSERT INTO TEST_TAB_1 VALUES v_test_tab
(i
) ;
END LOOP;
END;
回答by Ma Ha
sum up to full working excample ...
总结到完整的工作示例......
DECLARE
TYPE t_bulk_collect_test_tab IS TABLE OF event%ROWTYPE;
l_tab t_bulk_collect_test_tab;
CURSOR c_data IS SELECT * FROM event;
BEGIN
OPEN c_data;
LOOP
FETCH c_data
BULK COLLECT INTO l_tab LIMIT 10000;
EXIT WHEN l_tab.count = 0;
FORALL i IN 1..l_tab.count
Insert into tmp_event values l_tab(i);
commit;
END LOOP;
CLOSE c_data;
END;
/
