用于统计系统中各表的记录用的一个存储过程。
TEST是用于存放表名和记录数的一张临时表。
create or replace procedure tablerecordnum
is
sqlt varchar2(200);
zd varchar2 (100);
num number (10);
CURSOR tb is (select * from user_tables where table_name like 'T_%');
R_emp tb%ROWTYPE;
begin
open tb;
loop
fetch tb into R_emp;
exit when tb% notfound;
zd:= R_emp.table_name;
sqlt := 'select count(1) into ' ||num|| ' from '|| zd;
execute immediate sqlt;
insert into test(ftablename,fnum) values (zd,num);
commit;
end loop; close tb;
end;请问在哪里有问题?如何修改?
TEST是用于存放表名和记录数的一张临时表。
create or replace procedure tablerecordnum
is
sqlt varchar2(200);
zd varchar2 (100);
num number (10);
CURSOR tb is (select * from user_tables where table_name like 'T_%');
R_emp tb%ROWTYPE;
begin
open tb;
loop
fetch tb into R_emp;
exit when tb% notfound;
zd:= R_emp.table_name;
sqlt := 'select count(1) into ' ||num|| ' from '|| zd;
execute immediate sqlt;
insert into test(ftablename,fnum) values (zd,num);
commit;
end loop; close tb;
end;请问在哪里有问题?如何修改?
fetch tb into R_emp;
exit when tb%notfound; --tb%notfound要连在一起,这里有个空格
zd:= R_emp.table_name;
sqlt := 'select count(1) into :1 from :2'; --直接用参数
execute immediate sqlt using num,zd;
insert into test(ftablename,fnum) values (zd,num);
commit;
end loop;
ORA-00936: 缺失表达式
ORA-06512: 在 "HOAUTEST.TABLERECORDNUM", line 15
ORA-06512: 在 line 2
execute immediate sqlt into num;
直接换成参数也不行哦。
报错是:ORA-00942: 表或视图不存在
ORA-06512: 在 "HOAUTEST.TABLERECORDNUM", line 15
ORA-06512: 在 line 2