3,491
社区成员
发帖
与我相关
我的任务
分享
DECLARE
CURSOR cur_tabname
IS
SELECT owner,table_name FROM dba_tables WHERE owner NOT IN ('SYS','TEST','SYSMAN','SYSTEM');
c_1 cur_tabname%ROWTYPE;
BEGIN
FOR c_1 IN cur_tabname LOOP
EXECUTE IMMEDIATE 'select :V1 as 所属用户,:V2 as 表名,*
from :V1||'.'||:V2 WHERE rownum < 11'
USING c_1.owner,c_1.table_name;
END LOOP;
END;
declare
type tb_tbName is table of varchar2(1000);
vtb_tbName tb_tbName;
v_sql varchar2(500);
--type p_lines is ref cursor;
--vp_line p_lines;
begin
select table_name bulk collect into vtb_tbName from dba_tables WHERE owner = 'TEST';
for v_index in vtb_tbName.first .. vtb_tbName.last loop
--dbms_output.put_line( vtb_tbName(v_index));
v_sql:='select * from test.'||vtb_tbName(v_index)||' where rownum < 2';
execute immediate v_sql;
end loop;
end;
declare
type tb_tbName is table of varchar2(1000);
vtb_tbName tb_tbName;
v_sql varchar2(500);
type p_lines is ref cursor;
vp_line p_lines;
begin
select table_name bulk collect into vtb_tbName from user_tables;
for v_index in vtb_tbName.first .. vtb_tbName.last loop
--dbms_output.put_line( vtb_tbName(v_index));
v_sql:='select * from '||vtb_tbName(v_index)||' where rownum<=10';
execute immediate v_sql ...;
end loop;
end;
---------------execute immediate 替换下面似乎也不行--------------------
Open vp_line for v_sql loop
exit when vp_line%notfound;
fetch vp_line into ..... --- 由于每张表列名都是动态的,进一步处理字符串也很麻烦。
end loop;
写了半天发现做到上面那里卡住了,如果谁有比较简洁的办法输出行,问题应该就解决了。declare
type tb_tbName is table of varchar2(1000);
vtb_tbName tb_tbName;
v_sql varchar2(500);
--type p_lines is ref cursor;
--vp_line p_lines;
begin
select table_name bulk collect into vtb_tbName from dba_tables WHERE owner = 'TEST';
for v_index in vtb_tbName.first .. vtb_tbName.last loop
--dbms_output.put_line( vtb_tbName(v_index));
v_sql:='select * from test.'||vtb_tbName(v_index)||' where rownum < 2';
execute immediate v_sql;
end loop;
end;
执行在不到1秒钟的时间就执行好了 ,但是窗口下面没有结果啊~如图:
在我的相像中 是应该有个查询结果才是啊~[/quote]
其实,这和上面楼主的方法差不多,只是代码实现不同而已,我说了,这里最大的难题是输出,你可以把语句挨个执行完,但输出就是个问题,因为每张表的字段数不同,每个字段的类型也不同,这个结果的输出是比较繁琐的。
楼主运行完了,每个SQL也执行完了,根本没输出,所以也就没结果,就是这样。