Oracle PL/SQL：将整行从触发器转发到过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6423466/
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 PL/SQL: Forwarding whole row to procedure from a trigger
提问by Tim Meyer
In have an Oracle (10i) PL/SQL Row-Level trigger which is responsible for three independent tasks. As the trigger is relatively cluttered that way, I want to export these three tasks into three stored procedures.
I was thinking of using a my_table%ROWTYPEparameter or maybe a collection type for the procedures, but my main concern is how to fill these parameters.
有一个 Oracle (10i) PL/SQL 行级触发器，它负责三个独立的任务。由于触发器在这种情况下相对混乱，我想将这三个任务导出到三个存储过程中。我正在考虑为过程使用my_table%ROWTYPE参数或集合类型，但我主要关心的是如何填充这些参数。
Is there a way to put the whole :NEWrow of a trigger into a single variable easily?
So far the only way I could find out was assigning each field separately to the variable which is not quite satisfying, looking at code maintenance etc.
有没有办法:NEW轻松地将触发器的整行放入单个变量中？到目前为止，我能找到的唯一方法是将每个字段分别分配给不太令人满意的变量，查看代码维护等。
Something like
就像是
SELECT :NEW.* INTO <variable> FROM dual;
would be preferred. (I haven't tried that actually but I suppose it wouldn't work)
将是首选。（实际上我还没有尝试过，但我想它行不通）
采纳答案by Peter Lang
回答by Justin Cave
In the vast majority of cases, the only way to assign the new values in the row to a %ROWTYPE variable would be to explicitly assign each column. Something like
在绝大多数情况下，将行中的新值分配给 %ROWTYPE 变量的唯一方法是显式分配每一列。就像是
CREATE OR REPLACE TRIGGER some_trigger_name
BEFORE INSERT OR UPDATE ON some_table
FOR EACH ROW
DECLARE
l_row some_table%rowtype;
BEGIN
l_row.column1 := :NEW.column1;
l_row.column2 := :NEW.column2;
...
l_row.columnN := :NEW.columnN;
procedure1( l_row );
procedure2( l_row );
procedure3( l_row );
END;
If your table happens to be declared based on an object, :NEW will be an object of that type. So if you have a table like
如果您的表恰好是基于对象声明的， :NEW 将是该类型的对象。所以如果你有一张像
CREATE OR REPLACE TYPE obj_foo
AS OBJECT (
column1 NUMBER,
column2 NUMBER,
...
columnN NUMBER );
CREATE TABLE foo OF obj_foo;
then you could declare procedures that accept input parameters of type OBJ_FOOand call those directly from your trigger.
然后您可以声明接受类型输入参数的过程OBJ_FOO并直接从触发器调用这些参数。
The suggestion in the other thread about selecting the row from the table in an AFTER INSERT/ UPDATE thread, unfortunately, does not generally work. That will generally lead to a mutating table exception.
不幸的是，另一个线程中关于在 AFTER INSERT/UPDATE 线程中从表中选择行的建议通常不起作用。这通常会导致变异表异常。
1 create table foo (
2 col1 number,
3 col2 number
4* )
SQL> /
Table created.
SQL> create procedure foo_proc( p_foo in foo%rowtype )
2 as
3 begin
4 dbms_output.put_line( 'In foo_proc' );
5 end;
6 /
Procedure created.
SQL> create or replace trigger trg_foo
2 after insert or update on foo
3 for each row
4 declare
5 l_row foo%rowtype;
6 begin
7 select *
8 into l_row
9 from foo
10 where col1 = :new.col1;
11 foo_proc( l_row );
12 end;
13 /
Trigger created.
SQL> insert into foo values( 1, 2 );
insert into foo values( 1, 2 )
*
ERROR at line 1:
ORA-04091: table SCOTT.FOO is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRG_FOO", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRG_FOO'
回答by Phanton
Use SQL to generate the SQL;
使用SQL生成SQL；
select ' row_field.'||COLUMN_NAME||' := :new.'||COLUMN_NAME||';' from
ALL_TAB_COLUMNS cols
where
cols.TABLE_NAME = 'yourTableName'
order by cols.column_name
Then copy and paste output.
然后复制粘贴输出。
回答by David Bala?ic
This is similar to Justins solution but a little bit shorter (no typing of left part of each assignment) :
这类似于 Justins 解决方案，但更短一些（没有输入每个作业的左侧部分）：
-- use instead of the assignments in Justins example:
select :new.column1,
:new.column2,
...
:new.columnN,
into l_row from dual;
