Oracle:将行类型数据插入另一个表 [英] Oracle: Insert rowtype data into another table

问题描述

我有一个名为 event 的表，并创建了另一个全局临时表 tmp_event ，它们具有与事件相同的列和定义.是否可以使用this将事件中的记录插入到tmp_event中?

I have one table called event, and created another global temp table tmp_event with the same columns and definition with event. Is it possible to insert records in event to tmp_event using this ?

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.

忘记提及:我将在触发器中使用它，在将EVENT表插入后，此v_record可以作为对象:new 吗?

Forget to mention: I will use this in the trigger, can this v_record be the object :new after insert on EVENT table ?

推荐答案

要插入一行-

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;

或更详尽的版本可插入event-

Or a more elaborate version to insert all rows from 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;
/

在触发器中，是有可能的，但是它就像鸡肉或鸡蛋一样.您必须使用:new列值(如-

In a trigger, yes it is possible but its like the chicken or the egg. You have to initialize every field of the rowtype with the :new column values like-

v_record.col1 := :new.col1;
v_record.col2 := :new.col2;
v_record.col3 := :new.col3;
....

显然，上面的PLSQL示例不能在触发器中使用，因为它会引发变异的触发器错误.正如上面我所解释的那样，除了单独访问每一列之外，没有其他方法可以让您在触发器中获得整行，因此，如果您执行所有这些操作，为什么不直接在INSERT into temp_event本身中使用:new.col将会节省您的时间很多工作.

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.col in the INSERT into temp_event itself, will save you a lot of work.

另外，由于您说要提到所有列需要很多工作，(在 Oracle 11gR2 中)，这是一种通过生成INSERT语句并动态执行它的快速方法(尽管未经性能测试.

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 INSERT statement and executing it dynamically (although not tested for performance).

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;

这篇关于Oracle:将行类型数据插入另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！