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;
解决方案 »
- oracle存储过程里要判断一个变量值是不是在存在于数据库中,怎么实现?
- PL/SQL问题
- 物化视图上创建了触发器,有问题
- 还是刚才触发器问题,00036错误
- 想判断是那个程序连接的数据库?
- 虚拟请教:linux下SHELL文件里调用exp命令导出某用户下DMP文件时的问题及局域网内传输DMP文件问题
- 请问这个插入语句怎么不能执行(触发器/函数不能读)
- imp怎样重新导入数据?
- 如何怎加索引空间??
- 请问oracle里面number如何实现类似int自增一
- Oracle中如何获得指定用户模式下,那些表被操作(insert/update/delete)过?
- 小问题:number类型的精度和刻度是什么意思?
传入变量为条件
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;