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赋值给一个变量的时候,麻烦高手帮看下,不胜感激。
(
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赋值给一个变量的时候,麻烦高手帮看下,不胜感激。
execute immediate v_sql into v_every_count
代码修改如下: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(*) '|| ' from ';
v_sql := v_sql || 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;