一、概念
游标的作用是临时存储从数据库中提取的数据块,由系统或者用户以变量的形式定义,是sql的内存工作区;
二、分类
如图,游标主要分为静态游标和动态游标
静态游标:
动态游标:ref cursor属于动态cursor(直到运行时才知道这条查询)
其中:
隐式游标:系统定义和管理的,像DML(insert,delete,update)操作和单行SELECT(select…into…)语句都会被Oracle内部解析为:一个cursor名为SQL的隐式透明游标;另外,一些循环操作中的指针for 循环,都是隐式cursor:例如
BEGIN
FOR rec IN (
SELECT empname, empno FROM tmp
)
LOOP
dbms_output.put_line(rec.empname || '--' || rec.empno);
END LOOP;
END;
/
显示游标:用户自己定义的,有明确声明的cursor
三、游标以及游标变量的定义
声明格式
DECLARE
TYPE 游标变量名 IS REF CURSOR;
-- 可以分为强类型和弱类型
TYPE 游标变量名 IS REF CURSOR RETURN 表名%ROWTYPE -- 强类型
TYPE 游标变量名 IS REF CURSOR -- 弱类型
四、游标的生命周期
declare->open->fetch->close
声明游标:declare
在DECLARE部分,按照以下格式声明游标:
CURSOR 游标名[参数1 数据类型[,参数2 数据类型]] IS SELECT语句;
参数是可选部分,如果定义了参数,必须在调用时传入实际的参数,后面的select语句可以是对表、视图等的查询,甚至是联合查询,可以带WHERE 条件、ORDER BY或GROUP BY子句,但不能使用INTO子句,在SELECT 语句中可以使用定义在游标之前定义的变量
DECLARE
CURSOR id_cur(a number) IS
SELECT empno FROM tmp WHERE id = a;
打开游标:open
在可执行部分,按照以下格式打开游标:
OPEN 游标名[实际参数1[,实际参数2]];
OPEN id_cur(101);
提取数据:fetch
在可执行部分,按照以下格式将游标工作区中的数据取到变量中:
FETCH 游标名 INTO 变量名1[,变量名2]
或
FETCH 游标名 INTO 记录变量
FETCH语句一次返回一行数据,要返回多行数据,需要使用循环
定义记录变量的方法如下:
变量名 表名/游标名%ROWTYPE;eg: v_cur tmp.c1%TYPE;其中tmp为表名,c1为tmp表的字段名
其中表名必须存在,游标名也必须先定义
LOOP
FETCH id_cur INTO v_cur;
EXIT WHEN id_cur%notfound; -- postgre: if not found then exit; end if;
dbms_output.put_line(v_cur); -- postgre: raise notice 'result is %',v_cur;
END LOOP;
关闭游标:close
CLOSE 游标名;
显示游标打开后必须显式关闭。
CLOSE id_cur;
一个完整的例子:
create table tmp(id number(10), empname varchar2(20), empno varchar2(15));
insert into tmp values(100, '销售', 'N001');
insert into tmp values(101, '企划', 'N002');
insert into tmp values(102, '运营', 'N003');
insert into tmp values(103, '开发', 'N004');
-- 显示游标
DECLARE
CURSOR id_cur(a number) IS
SELECT empno FROM tmp WHERE id = a;
v_cur tmp.empno%TYPE; -- 记录变量
BEGIN
// step 1
OPEN id_cur(101);
LOOP
FETCH id_cur INTO v_cur;
EXIT WHEN id_cur%notfound; -- postgre: if not found then exit; end if;
dbms_output.put_line(v_cur); -- postgre: raise notice 'result is %',v_cur;
END LOOP;
CLOSE id_cur;
// step 2
OPEN id_cur(102);
LOOP
FETCH id_cur INTO v_cur;
EXIT WHEN id_cur%notfound; -- postgre: if not found then exit; end if;
dbms_output.put_line(v_cur); -- postgre: raise notice 'result is %',v_cur;
END LOOP;
CLOSE id_cur;
END;
/
-- 隐式游标
DECLARE
rec record; -- 记录数据类型
BEGIN
FOR rec IN (
SELECT empname, empno FROM tmp
)
LOOP
dbms_output.put_line(rec.empname || '--' || rec.empno);
END LOOP;
END;
/
五、游标特点
游标是可以被多次open进行使用的
显式cursor是静态cursor,作用域是全局的
静态cursor也只有pl/sql代码才可以使用
PL/SQL cursor 按定义是静态的
ref cursor 正好相反,可以动态地打开,或者利用一组SQL静态语句来打开
六、游标属性
-- 游标%属性 返回值类型 意义
%ROWCOUNT 整型 获得FETCH语句返回的数据行数;
%FOUND 布尔型 最近的fetch返回一行数据则为真,否则为假;
%notfound 布尔型 与%found属性相反
%isopen 布尔型 游标打开则为真,否则为假