游标嵌套使用的问题

[jinweifu]

create or replace procedure MULTIPLE_CURSORS_PROC is
v_owner varchar2(40);
v_table_name varchar2(40);
v_column_name varchar2(100);

/* First cursor */
cursor get_tables is
select distinct tbl.owner, tbl.table_name
from all_tables tbl
where tbl.owner = 'SYSTEM';

/* Second cursor */
cursor get_columns is
select distinct col.column_name
from all_tab_columns col
where col.owner = v_owner
and col.table_name = v_table_name;

begin

-- Open first cursor
open get_tables;
loop
fetch get_tables into v_owner, v_table_name;

open get_columns;
loop
fetch get_columns into v_column_name;

end loop;
close get_columns;

end loop;
close get_tables;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end MULTIPLE_CURSORS_PROC;


没有发现哪里相关 很不好理解。我的理解是这样的：
首先获得all_tables中owner='SYSTEM'的子集，存入v_owner,v_table_name;
接着获取，接着循环抓取all_tab_columns里的若owner=all_tables.owner并且table_name=all_tables_name则column_name存入v_column_name中，是不是这样的解的，还有上面的
DISTINCT，select distinct tbl.owner, tbl.table_name
from all_tables tbl
where tbl.owner = 'SYSTEM';
是要求tbl.owner与tbl.table_name同时DISTINCT吗？即：
用下面的语句：
SELECT distinct tbl.owner,tbl.table_name from tbl;


table.owner tbl.table_name
A A1
A A2
B B1
B A1

这样四个是不是会全部选出来?

[tianyacao007]

会全出来的！

[wiler]

try:
/* Second cursor */
cursor get_columns is
select distinct col.column_name
from all_tab_columns col
where col.owner = :1
and col.table_name = :2;
......
open get_columns using v_owner, v_table_name;

[jinweifu]

thx