在trigger中直接写:insert into temp2 values(12,'I',:new.ID,:new.NAME,:new.NOTE)没有问题
考滤到当一个表栏位众多时,想通过程序来生成insert语句字串,然后利用动态SQL执行,我把它写成:
v_statement:='insert into temp2 values(12,'||'''I'''||',:new.ID,:new.NAME,:new.NOTE)';
execute immediate v_statement;
当我往表里insert数据时,就报trigger有问题,无法insert
请问我该如何写成动态SQL执行?
考滤到当一个表栏位众多时,想通过程序来生成insert语句字串,然后利用动态SQL执行,我把它写成:
v_statement:='insert into temp2 values(12,'||'''I'''||',:new.ID,:new.NAME,:new.NOTE)';
execute immediate v_statement;
当我往表里insert数据时,就报trigger有问题,无法insert
请问我该如何写成动态SQL执行?
v_statement:='insert into temp2 values(12,'||'''I'''||','||:new.ID,:new.NAME,:new.NOTE||')';
EXECUTE IMMEDIATE ‘语句’ [INTO {变量1, 变量2, … 变量N | 记录体}] [USING [IN | OUT | IN OUT] 绑定变量1, … 绑定变量N] [{RETURNING | RETURN} INTO 输出1 [, …, 输出N]…];
declare
value1 varchar2(20);
value2 varchar2(20);
value3 varchar2(20);
begin
execute immediate 'insert into insert into temp2 values(:aa,:bb,:cc,:dd,:ee)'
using in 12,in 'I',in value1, in value2, in value3;
end;
execute immediate v_statement;
v_statement:='insert into temp2 values(12,''I'''||','||:new.ID||','||:new.NAME||','||:new.NOTE||')';
execute immediate v_statement;
CREATE OR REPLACE TRIGGER HAN.TRIGGER_TEST
BEFORE INSERT
ON HAN.TEMP1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
v_seq number;
v_type char(1);
v_table_name varchar2(20):='TEMP1';
v_statement varchar2(2000):='insert into temp2 values(';
BEGIN
select seq1.nextval into v_seq from dual;
if inserting then
v_type:='I';
v_statement:=v_statement||v_seq||','||''''||v_type||''',';
for p in (select ':new.'||COLUMN_NAME||',' COLUMN_NAME from user_tab_columns
where table_name=v_table_name)
loop
v_statement:=v_statement||p.COLUMN_NAME;
end loop;
v_statement:=substr(v_statement,1,length(v_statement)-1)||')';
execute immediate v_statement;
end if;
END;
BEFORE INSERT
ON HAN.TEMP1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
v_seq number;
v_type char(1);
v_table_name varchar2(20):='TEMP1';
v_statement varchar2(2000):='insert into temp2 values(';
BEGIN
select seq1.nextval into v_seq from dual;
if inserting then
v_type:='I';
--v_statement:=v_statement||v_seq||','||''''||v_type||''',';
v_statement:=v_statement || v_seq || ',''' || v_type||''',';
for p in (select ':new.'||COLUMN_NAME||',' COLUMN_NAME from user_tab_columns
where table_name=v_table_name)
loop
v_statement:=v_statement||p.COLUMN_NAME;
end loop;
v_statement:=substr(v_statement,1,length(v_statement)-1)||')';
execute immediate v_statement;
end if;
END;或者你把execute immediate改成 dbms_output.putline, 打印出来看看sql拼对了没
但是用execute immediate时却不能执行
不知问题出在哪里?