declare
cursor t_sor is
select count(*) num from user_tables;
str varchar2(50);
v_table varchar2(10);
type p_sor is ref cursor;
l_sro p_sor;
begin
for v_sor in t_sor loop
str:='select table_name from user_tables where'||v_sor.num||'>1000';
open l_sor for str;
while l_sor*found; loop
fetch l_sor into v_table;
dbms_output.put_line(v_table);
end loop;
close l_sor;
end loop;
end;
cursor t_sor is
select count(*) num from user_tables;
str varchar2(50);
v_table varchar2(10);
type p_sor is ref cursor;
l_sro p_sor;
begin
for v_sor in t_sor loop
str:='select table_name from user_tables where'||v_sor.num||'>1000';
open l_sor for str;
while l_sor*found; loop
fetch l_sor into v_table;
dbms_output.put_line(v_table);
end loop;
close l_sor;
end loop;
end;
传入变量为条件
create or replace procedure selectnum(vnum number) is
cursor c_desc is select table_name from user_tables;
mycursor integer;
myname integer;
cnt NUMBER;
begin
for i in c_desc loop
mycursor:=dbms_sql.open_cursor;
dbms_sql.parse(mycursor,'select count(*) from '||i.table_name,dbms_sql.v7 );
dbms_sql.define_column(mycursor,1,cnt);
myname:=dbms_sql.execute(mycursor);
myname:=dbms_sql.fetch_rows(mycursor);
if myname<>0 then
dbms_sql.column_value(mycursor,1,cnt);
if cnt>vnum then
dbms_output.put_line(i.table_name);
end if;
end if;
dbms_sql.close_cursor(mycursor);
end loop;
end selectnum;