游标是 sql 的一个内存工作区, 由系统或用户以变量的形式定义, 用于临时存储从数据库中提取的数据块.
游标分为显式游标和隐式游标, 可自定义显式游标用来存储多行多列的数据.
显式游标一旦打开, 就相当于执行了 select 语句, 执行的结果集就存储在游标中.
隐式游标
在进行 DML 操作和单行 SELECT 语句 (select...into...) 数据库会自动使用隐式游标, 可通过调用隐式游标变量直接取值, 隐式游标只存储单个数据.
四个隐式游标:
sql%rowcount: 整型, 用于记录 DML 语句成功修改记录的条数.
sql%found: 布尔型, true 表示 crud 的操作成功.
sql%notfount: 布尔型, 与 sql%found 属性的值相反.
sql%isopen: 布尔型, DML 执行过程中为 true, 执行结束后为 false.
显式游标
使用步骤:
1. 声明游标
在 declear 声明,
语法:
corsor 游标名[参数 1 数据类型[, 参数 2 数据类型...]] is select 语句;
2. 打开游标
游标在使用前必须打开;
open 游标名[(参数 1[, 参数 2.....])];-- 相当于执行 is 后面的 select 语句, 并把查询结果存入游标中.
3. 提取数据
fetch 游标名 into 变量 1[, 变量 2....];-- 取一条 (row) 数据到变量中, 变量的个数和类型要和 select 语句中字段变量的个数和类型一致.
游标打开后有一个指针指向数据区, fetch 语句每一次返回一行的数据, 每次执行完指针指向下一行. 可通过循环实现返回多行数据, 控制循环可通过游标的属性 %found 和 %notfount 控制.
4. 关闭游标
close 游标名;
游标关闭后占用的资源就被释放, 游标变为无效.
显式游标必须手动关闭, 显式游标打开的数量有限制, 默认为 300, 超过限制会报错: ORA-01000: maximum open cursors exceeded
使用游标的属性, 在游标名后面带上属性以取得属性的值. 如: 游标名 %FOUND;
%FOUND: 指针当前指向数据区有数据时为 true, 无数据为 false;
%NOTFOUND: 与 %found 相反, 无数据为 true;
%ISOPEN: 当前游标已打开为 true, 未打开为 false;
%ROWCOUNT: 已从游标中取出的数据的行数;
游标变量(动态游标)ref cursor:
游标变量和普通游标基本相同, 不同之处在于游标变量更加灵活, 在声明时不需要绑定查询, 并且游标变量没有参数.
游标变量分为强类型和弱类型
强类型: 列的数量和列的数据类型在定义游标变量时即定义好.
弱类型: 定义游标变量时不定义列的数据的类型, 在游标被打开时再确定每列的数据类型和列的数量, 游标的列数量和列的数据类型一旦被确定不能再更改.
用法:
1. 声明动态游标类型
弱类型, 不指定 return type:type cur_type is ref cursor;
强类型, 指定 return type:type cur_type is ref cursor return 表名 %rowtype;
2. 声明自定义的动态游标类型的变量: 游标名 cur_type;
3. 打开动态游标: open 游标名 for select 语句;
4. 获取游标中的数据: fatch 游标名 into 变量 1[, 变量 2....];
5. 关闭游标: close 游标名;
使用动态游标的实例:
- set serveroutput on
- declare
- type re_type is record(
- name varchar2(200)
- );
- type ref_cursor is ref cursor return re_type;
- c1 ref_cursor;
- message varchar2(200);
- begin
- open c1 for select name from t_test;
- loop
- fetch c1 into message;
- exit when c1%notfound;
- dbms_output.put_line('第'||c1%rowcount||'个:'||message);
- end loop;
- close c1;
- end;
数据库中关于游标的设置
1. 查看 Oracle 游标最大打开数
show parameter cursor; -- 查看所有和 curcor 有关的参数, 自带 like 关键字
2. 查看当前打开的游标
select * from v$open_cursor;
3. 修改游标的最大打开数
alter system set open_cursors = 500 scope=both;-- 修改游标的最大打开数为 500,
其中 scope 的取值为:
memory: 仅修改内存, 只会影响当前的使用, 重启数据库就会失效.
spfile: 仅修改配置文件, 不会影响本次使用, 重启数据库才生效.
both: 两个都修改.