游标遍历
-- 将user_tables的所有table_name以逗号间隔作为一个字符串输出
-- 通过for循环 显示游标 遍历
declare
var_sql varchar2(32767) := '';
cursor cur is
select table_name from user_tables order by table_name;
begin
for rec in cur
loop
var_sql := var_sql || rec.table_name || ',';
end loop;
var_sql := substr(var_sql, 1, length(var_sql) - 1);
dbms_output.put_line(var_sql);
end;
-- 通过for循环 隐式游标 遍历
declare
var_sql varchar2(32767) := '';
begin
for rec in (select table_name from user_tables order by table_name)
loop
var_sql := var_sql || rec.table_name || ',';
end loop;
var_sql := substr(var_sql, 1, length(var_sql) - 1);
dbms_output.put_line(var_sql);
end;
-- 通过loop..if遍历
declare
cursor cur is
select table_name from user_tables order by table_name;
var_sql varchar2(32767) := '';
var_table_name user_tables.table_name%type;
begin
open cur;
loop
fetch cur
into var_table_name;
if cur%notfound then
var_sql := substr(var_sql, 1, length(var_sql) - 1);
exit;
else
var_sql := var_sql || var_table_name || ',';
end if;
end loop;
close cur;
dbms_output.put_line(var_sql);
end;
-- 通过loop..exit when遍历
declare
cursor cur is
select table_name from user_tables order by table_name;
var_sql varchar2(32767) := '';
var_table_name user_tables.table_name%type;
begin
open cur;
loop
fetch cur
into rec;
exit when cur%notfound;
var_sql := var_sql || var_table_name || ',';
end loop;
close cur;
var_sql := substr(var_sql, 1, length(var_sql) - 1);
dbms_output.put_line(var_sql);
end;
-- 通过loop..record类型遍历
declare
cursor cur is
select table_name from user_tables order by table_name;
type type_rec is record(
table_name user_tables.table_name%type);
rec type_rec;
var_sql varchar2(32767) := '';
begin
open cur;
loop
fetch cur
into rec;
exit when cur%notfound;
var_sql := var_sql || rec.table_name || ',';
end loop;
close cur;
var_sql := substr(var_sql, 1, length(var_sql) - 1);
dbms_output.put_line(var_sql);
end;