多谢各位大神! 触发器我没试过,动态SQL我试过,但我对execute immediate的拼接规则不怎么明白,语句执行一直报错。下面我写的代码,恳请各位大神代为指正: create or replace procedure selectx(mail_file varchar2) as m number; begin execute immediate 'select count(*) from dm_inpatient where '||mail_file||'=''-9999'''into m; insert into dm_temp values(mail_file,m); commit; end selectx;declare cursor cur is select mail_file from yy ;//表yy中存放了178个字段名 xx varchar2(20); begin open cur; fetch cur into xx; while cur%found loop call selectx(xx); end loop; close cur; end;
create or replace procedure selectx(mail_file varchar2) as m number; begin execute immediate 'select count(*) from dm_inpatient where ' || mail_file || '=''-9999''' --此处一定要注意,你传入的mail_file值一定要是dm_inpatient的一个字段,否则会报错 into m; insert into dm_temp values (mail_file, m); commit; end selectx;declare begin for c1 in (select mail_file from yy) loop selectx(c1.mail_file); end loop; end;
感谢CT_LXL,您的代码执行完毕,没有报错,达到了预期的目标,再次对您的热情帮助表示感谢。再次请教,您的代码中: for c1 in (select mail_file from yy) loop selectx(c1.mail_file) 原理是什么?
触发器我没试过,动态SQL我试过,但我对execute immediate的拼接规则不怎么明白,语句执行一直报错。下面我写的代码,恳请各位大神代为指正:
create or replace procedure selectx(mail_file varchar2)
as
m number;
begin
execute immediate 'select count(*) from dm_inpatient where '||mail_file||'=''-9999'''into m;
insert into dm_temp values(mail_file,m);
commit;
end selectx;declare
cursor cur
is
select mail_file from yy ;//表yy中存放了178个字段名
xx varchar2(20);
begin
open cur;
fetch cur into xx;
while cur%found loop
call selectx(xx);
end loop;
close cur;
end;
m number;
begin
execute immediate 'select count(*) from dm_inpatient where ' || mail_file ||
'=''-9999''' --此处一定要注意,你传入的mail_file值一定要是dm_inpatient的一个字段,否则会报错
into m;
insert into dm_temp values (mail_file, m);
commit;
end selectx;declare
begin
for c1 in (select mail_file from yy) loop
selectx(c1.mail_file);
end loop;
end;
for c1 in (select mail_file from yy) loop
selectx(c1.mail_file)
原理是什么?