改成这样了,还是报错 CREATE OR REPLACE TRIGGER tr_app_type BEFORE UPDATE ON app_type declare v_host varchar2(50); v_ip varchar2(20); v_table varchar2(50); v_type varchar2(10); v_time date; v_sql1 varchar2(2000); v_sql2 varchar2(2000); v_run1 varchar2(2000); v_run2 varchar2(2000); v_val1 varchar2(2000); v_val2 varchar2(2000); BEGIN select SYS_CONTEXT('USERENV','HOST') into v_host from dual; select SYS_CONTEXT('USERENV','IP_ADDRESS') into v_ip from dual; v_table := upper('app_type'); v_type := 'UPDATE'; v_time := sysdate; v_sql1 :=''; v_sql2 :=''; for c in (select column_name from user_tab_columns where table_name = upper('app_type') and data_type like '%CHAR%') loop if v_sql1 !='' then v_sql1 := v_sql1||','||c.column_name||'='; else v_sql1 := c.column_name||'='; end if; if v_sql2 !='' then v_sql2 := v_sql2||','||c.column_name||'='; else v_sql2 := c.column_name||'='; end if; v_run1 := ':old.'||c.column_name; v_run2 := ':new.'||c.column_name; -- v_run1 := 'select '||v_run1||' from dual '; -- v_run2 := 'select '||v_run2||' from dual where rownum=1'; execute immediate 'select :p1 from dual ' using v_run1 returning into v_val1; -- using p_id returning into v_name,v_salary; v_sql1 := v_sql1||v_val1; execute immediate 'select :p1 from dual ' using v_run2 returning into v_val2; v_sql2 := v_sql2||v_val2; end loop;
insert into sys_monitor(host,ip,table_name,operator_type,op_time,old_value,new_value) select v_host,v_ip,v_table,v_type,v_time,v_sql1,v_sql2 from dual; END tr_app_type;
我明白你的意图了,你是想通过动态sql获取字段更新前后的值。但对这样的sql “execute immediate 'select :p1 from dual ' using v_run1 returning into v_val1;” ...是想通过动态变字段来获取不同的值,是行不通的。因为sql是在解析后才传递变量执行查询的。 我翻了一下,不久前有类似的帖子: http://community.csdn.net/Expert/topic/4137/4137681.xml?temp=.3287317动态sql传递的只能是变量的值,即where子句后面变量的值,如果你在字段处用绑定变量,那动态sql就解析为select 'xxx' from tablename... 就你举的例子,就用触发器也能做的很好啊。如: CREATE OR REPLACE TRIGGER tr_app_type BEFORE UPDATE OF XX,YY,ZZ OF ON app_type -- XX,YY,ZZ...为app_type表中的字符型字段 declare v_host varchar2(50); v_ip varchar2(20); v_table varchar2(50); v_type varchar2(10); v_time date; v_sql1 varchar2(2000); v_sql2 varchar2(2000); v_run1 varchar2(2000); v_run2 varchar2(2000); v_val1 varchar2(2000); v_val2 varchar2(2000); BEGIN select SYS_CONTEXT('USERENV','HOST') into v_host from dual; select SYS_CONTEXT('USERENV','IP_ADDRESS') into v_ip from dual; v_table := upper('app_type'); v_type := 'UPDATE'; v_time := sysdate; if :old.xx<>:new.xx then .... end if; if :old.yy<>:new.yy then .... end if; ....END tr_app_type; /
CREATE OR REPLACE TRIGGER tr_app_type
BEFORE UPDATE ON app_type
declare
v_host varchar2(50);
v_ip varchar2(20);
v_table varchar2(50);
v_type varchar2(10);
v_time date;
v_sql1 varchar2(2000);
v_sql2 varchar2(2000);
v_run1 varchar2(2000);
v_run2 varchar2(2000);
v_val1 varchar2(2000);
v_val2 varchar2(2000);
BEGIN
select SYS_CONTEXT('USERENV','HOST') into v_host from dual;
select SYS_CONTEXT('USERENV','IP_ADDRESS') into v_ip from dual;
v_table := upper('app_type');
v_type := 'UPDATE';
v_time := sysdate;
v_sql1 :='';
v_sql2 :='';
for c in (select column_name from user_tab_columns where table_name = upper('app_type') and data_type like '%CHAR%')
loop
if v_sql1 !='' then
v_sql1 := v_sql1||','||c.column_name||'=';
else
v_sql1 := c.column_name||'=';
end if;
if v_sql2 !='' then
v_sql2 := v_sql2||','||c.column_name||'=';
else
v_sql2 := c.column_name||'=';
end if;
v_run1 := ':old.'||c.column_name;
v_run2 := ':new.'||c.column_name;
-- v_run1 := 'select '||v_run1||' from dual ';
-- v_run2 := 'select '||v_run2||' from dual where rownum=1';
execute immediate 'select :p1 from dual ' using v_run1 returning into v_val1;
-- using p_id returning into v_name,v_salary;
v_sql1 := v_sql1||v_val1;
execute immediate 'select :p1 from dual ' using v_run2 returning into v_val2;
v_sql2 := v_sql2||v_val2; end loop;
insert into sys_monitor(host,ip,table_name,operator_type,op_time,old_value,new_value)
select v_host,v_ip,v_table,v_type,v_time,v_sql1,v_sql2
from dual;
END tr_app_type;
-- Create table
create table SYS_MONITOR
(
HOST VARCHAR2(50),
IP VARCHAR2(20),
TABLE_NAME VARCHAR2(50),
OPERATOR_TYPE VARCHAR2(10),
OP_TIME DATE,
OLD_VALUE VARCHAR2(2000),
NEW_VALUE VARCHAR2(2000)
)
tablespace YD_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
“execute immediate 'select :p1 from dual ' using v_run1 returning into v_val1;”
...是想通过动态变字段来获取不同的值,是行不通的。因为sql是在解析后才传递变量执行查询的。
我翻了一下,不久前有类似的帖子:
http://community.csdn.net/Expert/topic/4137/4137681.xml?temp=.3287317动态sql传递的只能是变量的值,即where子句后面变量的值,如果你在字段处用绑定变量,那动态sql就解析为select 'xxx' from tablename...
就你举的例子,就用触发器也能做的很好啊。如:
CREATE OR REPLACE TRIGGER tr_app_type
BEFORE UPDATE OF XX,YY,ZZ OF ON app_type -- XX,YY,ZZ...为app_type表中的字符型字段
declare
v_host varchar2(50);
v_ip varchar2(20);
v_table varchar2(50);
v_type varchar2(10);
v_time date;
v_sql1 varchar2(2000);
v_sql2 varchar2(2000);
v_run1 varchar2(2000);
v_run2 varchar2(2000);
v_val1 varchar2(2000);
v_val2 varchar2(2000);
BEGIN
select SYS_CONTEXT('USERENV','HOST') into v_host from dual;
select SYS_CONTEXT('USERENV','IP_ADDRESS') into v_ip from dual;
v_table := upper('app_type');
v_type := 'UPDATE';
v_time := sysdate; if :old.xx<>:new.xx then
....
end if; if :old.yy<>:new.yy then
....
end if; ....END tr_app_type;
/
njhart2003()
首先谢谢如果想做成比较通过的是不是就不可能了???????