我可以将 :OLD 和 :NEW 伪记录复制到 Oracle 存储过程中吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2337325/
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
Can I copy :OLD and :NEW pseudo-records in/to an Oracle stored procedure?
提问by aw crud
I have an AFTER INSERT OR UPDATE OR DELETEtrigger that I'm writing to store every record revision that occurs in a certain table, by copying the INSERTand UPDATE:NEWvalues into a mirror table, and for DELETEthe :OLDvalues.
我有一个AFTER INSERT OR UPDATE OR DELETE触发器，我正在编写它来存储某个表中发生的每个记录修订，方法是将INSERT和UPDATE:NEW值复制到镜像表中，并针对DELETE这些:OLD值。
I could un-clutter my code considerably by conditionally passing either the :NEWor :OLDrecord into a procedure which would then do the insert into my history table. Unfortunately I cannot seem to find a way to pass the entire :OLDor :NEWrecord.
我可以通过有条件地将:NEW或:OLD记录传递到一个过程中，然后将插入到我的历史记录表中，从而大大简化我的代码。不幸的是，我似乎无法找到一种方法来传递整个:OLD或:NEW记录。
Am I missing something or is there no way to avoid enumerating every :NEWand :OLDcolumn as I invoke my insert procedure?
我缺少的东西或者是有没有办法避免枚举所有的:NEW和:OLD列我调用我的插入过程？
I want to do the following:
我想做以下事情：
DECLARE
PROCEDURE LOCAL_INSERT(historyRecord in ACCT.ACCOUNTS%ROWTYPE) IS
BEGIN
INSERT INTO ACCT.ACCOUNTS_HISTORY (ID, NAME, DESCRIPTION, DATE) VALUES (historyRecord.ID, historyRecord.NAME, historyRecord.DESCRIPTION, SYSDATE);
END;
BEGIN
IF INSERTING OR UPDATING THEN
LOCAL_INSERT(:NEW);
ELSE --DELETING
LOCAL_INSERT(:OLD);
END IF;
END;
But I'm stuck doing this:
但我坚持这样做：
DECLARE
PROCEDURE LOCAL_INSERT(id in ACCT.ACCOUNTS.ID%TYPE,
name in ACCT.ACCOUNTS.NAME%TYPE,
description in ACCT.ACCOUNTS.DESCRIPTION%TYPE) IS
BEGIN
INSERT INTO ACCT.ACCOUNTS_HISTORY (ID, NAME, DESCRIPTION, DATE) VALUES (id, name, description, SYSDATE);
END;
BEGIN
IF INSERTING OR UPDATING THEN
LOCAL_INSERT(:NEW.ID, :NEW.NAME, :NEW.DESCRIPTION);
ELSE --DELETING
LOCAL_INSERT(:OLD.ID, :OLD.NAME, :OLD.DESCRIPTION);
END IF;
END;
Okay, so it doesn't look like a big difference, but this is just an example with 3 columns rather than dozens.
好的，所以它看起来没有太大区别，但这只是一个包含 3 列而不是几十列的示例。
采纳答案by Adam Musch
It isn't. You have to do it yourself through enumeration.
不是。您必须通过枚举自己完成。
The reasons it can't/doesn't work automatically include:
它不能/不能自动工作的原因包括：
the
:oldand:neware default conventions; you can name the:oldand:newreferences to be whatever you want through theREFERENCINGclause of theCREATE TRIGGERstatement.you'd have to have a public declaration of a type (through
CREATE TYPEor through a package declaration) to be able to use it as an argument to another piece of code.trigger code is interpreted code, not compiled code.
的
:old和:new是默认的公约; 您可以通过语句的子句将:old和:new引用命名为您想要的任何名称。REFERENCINGCREATE TRIGGER您必须有一个类型的公共声明（通过
CREATE TYPE或通过包声明）才能将其用作另一段代码的参数。触发代码是解释代码，而不是编译代码。
回答by Peter Lang
I don't think it's possible like that. Documentationdoesn't mention anything like that.
我不认为这是可能的。文档没有提到类似的东西。
This would certainly cost performance, but you could try to define your trigger AFTER INSERTand another one BEFORE UPDATE OR DELETE, and in the trigger do something like:
这肯定会降低性能，但您可以尝试定义您的触发器AFTER INSERT和另一个触发器BEFORE UPDATE OR DELETE，并在触发器中执行以下操作：
SELECT *
INTO rowtype_variable
FROM accounts
WHERE accounts.id = :NEW.id; -- :OLD.id for UPDATE and DELETE
and then call your procedure with that rowtype_variable.
然后用那个调用你的程序rowtype_variable。
回答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 Sergey
If you use AFTERtrigger you can use rowid as parameter to call procedure
如果您使用AFTER触发器，您可以使用 rowid 作为参数来调用过程
insert into t_hist
select * from t where rowid = r;
If you use BEFOREtrigger you will get ORA-04091mutating table, BUTyou solution can be (http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm):
如果您使用BEFORE触发器，您将获得ORA-04091变异表，但您的解决方案可以是（http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm）：
- Don't use triggers - The best way to avoid the mutating table error is not to use triggers. While the object-oriented Oracle provides "methods" that are associated with tables, most savvy PL/SQL developers avoid triggers unless absolutely necessary.
- Use an "after" or "instead of" trigger - If you must use a trigger, it's best to avoid the mutating table error by using an "after" trigger, to avoid the currency issues associated with a mutating table. For example, using a trigger ":after update on xxx", the original update has completed and the table will not be mutating.
- Re-work the trigger syntax - Dr. Hall has some great notes on mutating table errors, and offers other ways to avoid mutating tables with a combination of row-level and statement-level triggers.
- Use autonomous transactions - You can avoid the mutating table error by marking your trigger as an autonomous transaction, making it independent from the table that calls the procedure.
- 不要使用触发器 - 避免变异表错误的最佳方法是不使用触发器。尽管面向对象的 Oracle 提供了与表相关联的“方法”，但大多数精明的 PL/SQL 开发人员会避免使用触发器，除非绝对必要。
- 使用“after”或“instead of”触发器 - 如果您必须使用触发器，最好通过使用“after”触发器来避免变异表错误，以避免与变异表相关的货币问题。例如，使用触发器“:after update on xxx”，原始更新已经完成，表不会发生变异。
- 重新设计触发器语法——Hall 博士对改变表错误有一些很好的说明，并提供了其他方法来避免结合行级和语句级触发器来改变表。
- 使用自治事务 - 您可以通过将触发器标记为自治事务来避免变异表错误，使其独立于调用该过程的表。
