create or replace procedure f_statallrecords
(
p_result out number
)
is
v_tablename varchar2(100);
v_sql varchar2(100);
v_every_count varchar2(100);--number(20) default 0;
v_count number(20) default 0;
begin
for v_tablename in (select table_name from user_tables)
loop
dbms_output.put_line(v_tablename.table_name);
v_sql := 'select count(*) into ' || v_every_count || ' from ';
v_sql := v_sql || v_tablename.table_name;
execute immediate v_sql;
v_count := v_count + v_every_count;
end loop;
p_result := v_count;
end f_statallrecords;
本意是想统计oracle的一个库所拥有的记录数,思路是先得到数据库里所有的表,然后再统计每个表里面有多少条记录,累加最后得到总记录数,现在的问题是黑体字部分执行时报错,就在into赋值给一个变量的时候,麻烦高手帮看下,不胜感激。
此问题已解决,修正后的代码如下:
create or replace procedure f_statallrecords
(
p_result out number
)
is
v_tablename varchar2(1024);
v_sql varchar2(1024);
v_every_count number(20) default 0;
v_count number(20) default 0;
begin
for v_tablename in (select table_name from user_tables)
loop
dbms_output.put_line(v_tablename.table_name);
v_sql := 'select count(*) from ' || v_tablename.table_name;
--动态语句赋值
execute immediate v_sql into v_every_count;
v_count := v_count + v_every_count;
end loop;
p_result := v_count;
end f_statallrecords;