oracle存储过程及游标使用实例

oracle存储过程及游标使用实例

create or replace procedure t_table_count_test as

v_tablename varchar2(50);
v_count integer;
str_sql varchar2(200);
–从all_tables里面获取的所有表的表名称,保存在游标内
CURSOR mycursor is
select t.table_name from all_tables t;
begin
if mycursor%isopen = false then
open mycursor;
end if;
–获取游标的当前表名
fetch mycursor into v_tablename;
while mycursor%found
loop
–根据当前表名，获取表内的数据量

str_sql := 'select count(*) from '||v_tablename ;
execute immediate str_sql into v_count ;

–打印表名和数据量。
dbms_output.put_line(v_tablename||’:’||v_count);

fetch mycursor into v_tablename;

end loop;

end t_table_count_test;