create or replace trigger MY
before insert on TEST
for each row
declare
tableName varchar2(20);
preTime varchar2(10);
varsql varchar2(255);
vid number;
vName varchar2(10);
vTime date;
begin tableName := 'TEST';
preTime := '1';
tableName := tableName || preTime;
vid := 2;--:new.id;
vName := '3';--:new.name;
vTime := TO_DATE('2008-09-18','yyyy/mm/dd'); varsql := 'insert into ' ||tablename||' values ('|| vid||','||vName||','||vTime||')';
execute immediate varsql;
end;
错误是:缺少逗号?是不是Date型变量不能动态绑定啊?
before insert on TEST
for each row
declare
tableName varchar2(20);
preTime varchar2(10);
varsql varchar2(255);
vid number;
vName varchar2(10);
vTime date;
begin tableName := 'TEST';
preTime := '1';
tableName := tableName || preTime;
vid := 2;--:new.id;
vName := '3';--:new.name;
vTime := TO_DATE('2008-09-18','yyyy/mm/dd'); varsql := 'insert into ' ||tablename||' values ('|| vid||','||vName||','||vTime||')';
execute immediate varsql;
end;
错误是:缺少逗号?是不是Date型变量不能动态绑定啊?
CREATE OR REPLACE TRIGGER MY
BEFORE INSERT ON TEST
FOR EACH ROW
DECLARE
TABLENAME VARCHAR2(20);
PRETIME VARCHAR2(10);
VARSQL VARCHAR2(255);
VID NUMBER;
VNAME VARCHAR2(10);
VTIME DATE;
BEGIN TABLENAME := 'TEST';
PRETIME := '1';
TABLENAME := TABLENAME || PRETIME;
VID := 2; --:new.id;
VNAME := '3'; --:new.name;
VTIME := TO_DATE('2008-09-18', 'yyyy-mm-dd'); VARSQL := 'insert into ' || TABLENAME || ' values (' || VID || ',''' ||
VNAME || ''',' || VTIME || ')';
EXECUTE IMMEDIATE VARSQL;
END;
/
BEFORE INSERT ON TEST
FOR EACH ROW
DECLARE
TABLENAME VARCHAR2(20);
PRETIME VARCHAR2(10);
VARSQL VARCHAR2(255);
VID NUMBER;
VNAME VARCHAR2(10);
VTIME DATE;
BEGIN TABLENAME := 'TEST';
PRETIME := '1';
TABLENAME := TABLENAME || PRETIME;
VID := 2; --:new.id;
VNAME := '3'; --:new.name;
VTIME := TO_DATE('2008-09-18', 'yyyy-mm-dd'); VARSQL := 'insert into ' || TABLENAME || ' values (' || VID || ',''' ||
VNAME || ''',' || VTIME || ')';
EXECUTE IMMEDIATE VARSQL;
END;
VARSQL := 'insert into ' || TABLENAME || ' values (:x1,:x2,:x3)';
EXECUTE IMMEDIATE VARSQL using vid,vname,vtime; 成功了!
但上面为什么不对呢?请大侠们赐教啊!
转了后,要用to_date再转一次才能正确插入