create or replace trigger TRGADDmisunsismst

after insert on misunsismst

FOR EACH ROW

declare

i int;--rmintdatmst0的总列数

j int;--misunsismst的总列数

k int;--misunsismst表包含各项的明细数

m int;

n int;

p int;

loopcount int;

column_id int;--rmintdatmst0表最后一列的id

new_column varchar2(200);--rmintdatmst0表欲增加的列名

column_name varchar2(200);

type columnset is record(name varchar2(100));

type coltab is table of columnset index by binary_integer;

srtab coltab;

mycoltab coltab;

insertsql varchar2(200);

fromsql varchar2(200);

begin

select count(*) into i from user_tab_columns where table_name='RMINTDATMST0';

select count(*) into j from user_tab_columns where table_name='MISUNSISMST';

select count(*) into k from user_tab_columns where table_name='MISUNSISMST' and column_name like 'GAS_%';

--如果misunsismst表包含各项的明细数比rmintdatmst0表的明细列多，则在rmintdatmst0中增加列

while k>i-3 loop

select max(column_id) into column_id from user_tab_columns where table_name='RMINTDATMST0';

new_column:='RMINTDAT_EX'+chr(ascii(97+column_id-3));

exec('alter table RMINTDATMST0 add column '+new_column+' varchar2(60)');

i:=i+1;

end loop;

m:=1;

while m <= k loop

select column_name into mycoltab(m).name from user_tab_columns where table_name='RMINTDATMST0' and column_id=(3+m);

m:=m+1;

end loop;

n:=1;

p:=1;

loopcount:=(j-2)/k;

while n

while p<=k loop

if p!=k then

insertsql:=insertsql+mycoltab(p).name+',';

end if;

if p=k then

insertsql:=insertsql+mycoltab(p).name;

end if;

select column_name into srtab(p).name from user_tab_columns where table_name='MISUNSISMST' and column_id=(n*3+p-1);

if p!=k then

fromsql:=fromsql+':New.'+srtab(p).name+',';

end if;

if p=k then

fromsql:=fromsql+':New.'+srtab(p).name;

end if;

p:=p+1;

end loop;

exec('insert into RMINTDATMST0(RMINTTAG_ID,RMINTTAG_DAT,'+insertsql+')'+' values'+'(:NEW.SAFEDAY_NUM,:NEW.TAG_DTM,'+fromsql+')');

n:=n+1;

end loop;

end TRGADDmisunsismst;

----------------------------------------------------------

执行之后报如下错误:

TRIGGER 3104TEST.TRGADDMISUNSISMST 编译错误

错误：PLS-00201: 必须声明标识符 'EXEC'

行：29

文本：exec('alter table RMINTDATMST0 add column '+new_column+' varchar2(60)');

错误：PL/SQL: Statement ignored

行：29

文本：exec('alter table RMINTDATMST0 add column '+new_column+' varchar2(60)');

错误：PLS-00201: 必须声明标识符 'EXEC'

行：57

文本：exec('insert into RMINTDATMST0(RMINTTAG_ID,RMINTTAG_DAT,'+insertsql+')'+' values'+'(:NEW.SAFEDAY_NUM,:NEW.TAG_DTM,'+fromsql+')');

错误：PL/SQL: Statement ignored

行：57

文本：exec('insert into RMINTDATMST0(RMINTTAG_ID,RMINTTAG_DAT,'+insertsql+')'+' values'+'(:NEW.SAFEDAY_NUM,:NEW.TAG_DTM,'+fromsql+')');

------------------------------------------------------------

各位老大，谁能帮小弟看下到底是何问题？ db是oracle10g