*、oracle游标基础
显式游标: CURSOR 游标名 ( 参数 ) [返回值类型] IS Select 语句 生命周期: 在大多数时候我们在设计程序的时候都遵循下面的步骤: 1、打开游标 open cs1; 2、开始循环 while cs1%found loop | for column_name in .. LOOP 3、从游标中取值 fetch .. into.. | 4、检查那一行被返回 5、处理 6、关闭循环 end loop; 7、关闭游标 if cs1&isopen then close cs1; 选项:参数和返回类型
*、oracle存储过程删除某用户表空间的所有表
declare
v_name all_tables.table_name%type;
cursor mycur is select table_name from all_tables where owner='test';
begin
open mycur;
loop
fetch mycur into v_name;
exit when mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
execute immediate 'drop table '|| v_name;
end loop;
close mycur;
end;
*、oracle存储过程删除某用户表空间的所有视图
declare
v_name all_tables.table_name%type;
cursor mycur is select view_name from all_views where owner='test';
begin
open mycur;
loop
fetch mycur into v_name;
exit when mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
execute immediate 'drop view '|| v_name;
end loop;
close mycur;
end;