oracle Oracle触发器：声明全局变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5025692/
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 trigger: Declare global variable
提问by Zeck
I have a table for which i have written a trigger:
我有一个表，我已经为它写了一个触发器：
CREATE OR REPLACE TRIGGER EMPLOYEE_TRG
AFTER INSERT OR DELETE OR UPDATE ON EMPLOYEE
FOR EACH ROW
DECLARE
TYPE arr IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
v_array arr;
BEGIN
IF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('NEW DATA: ' || :new.NAME || ', OLD DATA: ' || :old.NAME);
DBMS_OUTPUT.PUT_LINE('ID: ' || :new.P_ID);
v_array(:new.P_ID) := :new.NAME;
DBMS_OUTPUT.PUT_LINE('COUNTER: ' || v_array.COUNT); -- DISPLAY COUNTER: 1
END IF;
END;
when i'm updating EMPLOYEEtable trigger's working fine. But v_arrayarray isn't store data? could anybody please help?
当我更新EMPLOYEE表触发器工作正常时。但是v_array数组不是存储数据吗？有人可以帮忙吗？
回答by Vincent Malgrat
declare v_arrayin a package if you want to make it global (to a session -- each session will have its own copy of the variable).
v_array如果要使其成为全局变量，请在包中声明（对于会话——每个会话都有自己的变量副本）。
CREATE OR REPLACE PACKAGE my_global_pkg IS
TYPE arr IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
g_array arr;
END my_global_pkg;
CREATE OR REPLACE TRIGGER EMPLOYEE_TRG
AFTER INSERT OR DELETE OR UPDATE ON EMPLOYEE
FOR EACH ROW
BEGIN
IF UPDATING THEN
DBMS_OUTPUT.PUT_LINE('NEW DATA: ' ||:new.NAME ||', OLD DATA: '||:old.NAME);
DBMS_OUTPUT.PUT_LINE('ID: ' || :new.P_ID);
my_global_pkg.g_array(:new.P_ID) := :new.NAME;
DBMS_OUTPUT.PUT_LINE('COUNTER: ' || my_global_pkg.g_array.COUNT);
END IF;
END;
For Multi-session global variables, use relational tables (with appropriate multi-user locking).
对于多会话全局变量，使用关系表（具有适当的多用户锁定）。
回答by GolezTrol
How do you know it doesn't save data? You declare the array inside the trigger. So it isn't global, but local. Everytime your trigger runs, you get a new array. You add one item, display its count, and release it again. The count shows 1, so that works.
你怎么知道它不保存数据？您在触发器内声明数组。所以它不是全局的，而是本地的。每次触发器运行时，您都会得到一个新数组。您添加一项，显示其计数，然后再次释放。计数显示1，因此有效。
Your code is working fine, although it's useless. :) What did you mean it to do?No wait, the count belongs to a different array. You put an item in a local array, and display the count of another (global?) array. No wonder it won't work. I think you're modifying the wrong array.
您的代码运行良好，尽管它没用。:) 你是什么意思？不用等待，计数属于不同的数组。您将一个项目放在本地数组中，并显示另一个（全局？）数组的计数。难怪它行不通。我认为您正在修改错误的数组。
