1。
PL/SQL里的游标可以分为显式和隐式两种,而隐式有分为select into隐式游标和for .. in 隐式游标两种。所以,我们可以认为,有3种游标用法:
A. 显式游标
普通显式游标
代码:
declare
cursor c is select tname from tab ;
l_tname varchar2(64);
begin
open c ;
loop
fetch c into l_tname ;
exit when c%notfound ;
dbms_output.put_line(l_tname);
end loop;
close c;
end;
/
..
..
代码:
declare
cursor c is select tname from tab ;
l_tname_array dbms_sql.varchar2_table;
begin
open c ;
fetch c bulk collect into l_tname_array ;
for i in 1 .. l_tname_array.count loop
dbms_output.put_line(l_tname_array(i) );
end loop;
close c;
end;
/
..
..
代码:
declare
cursor c is select tname from tab ;
l_tname_array dbms_sql.varchar2_table;
begin
open c ;
loop
fetch c bulk collect into l_tname_array limit 10 ;
exit when c%notfound ;
for i in 1 .. l_tname_array.count loop
dbms_output.put_line(l_tname_array(i) );
end loop;
end loop;
close c;
end;
/
..
..
代码:
declare
l_tname varchar2(100);
begin
select tname into l_tname from tab where rownum = 1 ;
dbms_output.put_line(l_tname);
end;
/
..
..
代码:
declare
l_tname varchar2(100);
l_table_name varchar2(100);
l_sql varchar2(200);
begin
l_table_name := 'TAB' ;
l_sql := 'select tname from '||l_table_name ||' where rownum = 1 ' ;
execute immediate l_sql into l_tname;
for i in 1 .. l_tname_array.count loop
dbms_output.put_line(l_tname_array(i) );
end loop;
end;
/
..
..
代码:
declare
l_tname_array dbms_sql.varchar2_table;
l_table_name varchar2(100);
l_sql varchar2(200);
begin
l_table_name := 'TAB' ;
l_sql := 'select tname from '||l_table_name ;
execute immediate l_sql bulk collect into l_tname_array;
for i in 1 .. l_tname_array.count loop
dbms_output.put_line(l_tname_array(i) );
end loop;
end;
/
..
..
代码:
begin
for c in (select tname from tab) loop
dbms_output.put_line(c.tname);
end loop;
end;
/
..
..
联系客服