Oracle语法：游标

什么是游标

Oracle游标是通过关键字cursor来定义一组Oracle查询出来的数据集，类似数组一样，把查询的数据集存储在内存当中，然后通过游标指向其中一条记录，通过循环游标达到循环数据集的目的。

游标的类型

Oracle中游标的类型可以分为静态游标和REF游标，其中静态游标有可以分为隐式游标和现实游标。

静态游标：结果集已经确实（静态定义）的游标。
- 隐式游标：指的是Oracle自己管理的游标，开发者不能自己控制操作，只能获得它的属性信息。
- 显示游标：指的是游标使用之前必须得先声明定义，一般是对查询语句的结果集进行定义游标，然后通过打开游标循环获取结果集内的记录，或者可以根据业务需求跳出循环结束游标的获取。循环完成后，可以通过关闭游标，结果集就不能再获取了。全部操作完全由开发者自己编写完成，自己控制。从现实游标的描述可以看出来，它可以用来处理多行数据。
REF游标：引用游标，特点是不依赖指定的查询语句，可以在使用时关联不同的查询语句，分为强游标和弱游标两类。

游标的属性

游标的属性是通过‘百分号+函数名’的格式来获取的，常见属性如下：

%isopen ：游标是否已经是打开状态，值是true或false。
%found ：当前游标是否能提取出数据，值是true或false。
%notfound ：和%found相反，表示当前游标是否不能提取出数据（也就是说最后一行数据是否已提取出），值是true或false。
%rowcount：当前游标提取的行数，会随着每次fetch而累加（参考显示游标用法中的示例）。

隐式游标的用法

在我们执行insert、update、delete和select…into…的操作中，使用的就是隐式游标。

select…into…

select…into…语句就是最常见的隐式游标的用法，比如下面的sql：

declare

-- 声明部分
v_name varchar2(30);
v_sal  number;

begin

-- 执行部分 
select ename,sal into v_name,v_sal from emp where empno = 7499;
dbms_output.put_line(v_name||' 薪水是 '||v_sal);

end;

注意：select…into…语句只能用来处理返回一行数据的情况，没有数据或返回多行数据都会报错。

游标属性：sql%found, sql%notfound, sql%rowcount, sql%isopen

当执行一条DML语句（指insert,delete,update,select语句）后，DML语句的结果保存在四个游标属性中，这样开发者就可以通过这些属性用于控制程序流程或者了解程序的状态。游标会在在运行DML语句时打开，完成后关闭。因为是隐式游标，所以只使用sql%found，sql%notfound，sql%rowcount三个属性。sql%found，sql%notfound是布尔值，sql%rowcount是整数值。

sql%found和sql%notfound
在执行任何DML语句前，sql%found和sql%notfound的值都是NULL，在执行DML语句后，sql%found的属性值将是：

TRUE :INSERT。
TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE。
TRUE :SELECT INTO至少返回一行。

当sql%found为TRUE时,sql%notfound为FALSE。

sql%rowcount
在执行任何DML语句之前，sql%rowcount的值都是NULL，对于select…into…语句，如果执行成功，sql%rowcount的值为1，如果没有成功，sql%rowcount的值为0，同时产生一个异常NO_DATA_FOUND。如果是DELETE和UPDATE语句，则是DELETE或UPDATE的行数。

sql%isopen
sql%isopen是一个布尔值，如果游标打开，则为TRUE，如果游标关闭，则为FALSE.对于隐式游标而言sql%isopen总是FALSE，这是因为隐式游标在DML语句执行时打开，结束时就立即关闭。

如下就是使用sql%found的隐式游标的例子：

declare
  v_row stuinfo%rowtype; --声明变量v_row的类型就是stuinfo表中一行中所有字段的类型
begin
  --查询学生信息
  select * into v_row from stuinfo t where t.stuid = 'SC201801001';
  if sql%found then
    dbms_output.put_line('学号：' || v_row.stuid || ',姓名：' ||
                         v_row.stuname);
  end if;

  --查询学生信息(不存在的学生)
  select * into v_row from stuinfo t where t.stuid = 'SC201901001';
  if sql%found then
    dbms_output.put_line('学号：' || v_row.stuid || ',姓名：' ||
                         v_row.stuname);
  end if;
exception
  when no_data_found then
    dbms_output.put_line('该学生SC201901001不存在');
end;

执行结果：

显示游标的用法

显示游标的使用步骤如下如下：

声明游标
声明游标是给游标命名并给游标关联一个查询结果集，具体声明语法如下：
1
2
3
declare
cursor mycursor is
select 语句
说明：
1. 必须在代码块的DECLEAR部分声明游标；
2. SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。
3. 声明游标时并没有执行Select 语句。
打开游标
游标声明完，可以通过打开游标打开命令，初始化游标指针，游标一旦打开后，游标对应的结果集就是静态不会再变了，不管查询的表的基础数据发生了变化。打开游标的命令如下：
1
open cursor_name;
说明：
1. 必须在代码块的可执行部分打开游标；
2. 打开游标时，执行Select 语句，SELECT语句的查询结果就被传送到了游标工作区。
3. 打开游标后，游标指向结果集头，而不是第一条记录。
读取游标中数据
打开游标之后，就可以在代码块的可执行部分，将游标工作区中的数据取到变量中。
1
2
3
fetch cursor_name into 变量名1[，变量名2...];
或者
fetch cursor_name into 记录变量;
说明：
1. 游标打开后有一个指针指向数据区，FETCH语句执行一次返回指针所指的一行数据，要返回多行数据可以使用循环语句来实现。可以通过判断游标的%found或%notfound属性的值来控制循环。
2. 第一种格式中变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。
3. 第二种格式一次将一行数据取到记录变量中（游标的查询语句要查出所有列，即select *），需要使用%ROWTYPE事先定义记录变量。
关闭游标：
游标使用完，一定要关闭游标释放资源。关闭后，该游标关联的结果集就释放了，不能够再操作了，命令如下：
1
close cursor_name;

下面是一个显示游标的使用示例sql：

DECLARE
  v_empno emp.empno%TYPE;   --声明变量v_empno的类型就是emp表中empno字段对应的类型。
  v_ename emp.ename%TYPE;   --声明变量v_ename的类型就是emp表中ename字段对应的类型。
  
  CURSOR emp_cursor IS
  SELECT empno,ename from emp where empno<>1000;
  
BEGIN

LOOP

  IF NOT emp_cursor%ISOPEN  THEN
     OPEN emp_cursor;
  END IF; 
  
  FETCH emp_cursor INTO  v_empno,v_ename; 
  EXIT WHEN emp_cursor%NOTFOUND;
   
  dbms_output.put_line('rowcount is:'|| emp_cursor%ROWCOUNT || '-- empno is:' || v_empno || '-- emp name is:' || v_ename);
    
END LOOP;
CLOSE emp_cursor;

END;

运行结果：

rowcount is:1-- empno is:7369-- emp name is:SMITH
rowcount is:2-- empno is:7499-- emp name is:ALLEN
rowcount is:3-- empno is:7521-- emp name is:WARD
rowcount is:4-- empno is:7566-- emp name is:JONES
rowcount is:5-- empno is:7654-- emp name is:MARTIN
rowcount is:6-- empno is:7698-- emp name is:BLAKE
rowcount is:7-- empno is:7782-- emp name is:CLARK
rowcount is:8-- empno is:7788-- emp name is:SCOTT
rowcount is:9-- empno is:7839-- emp name is:KING
rowcount is:10-- empno is:7844-- emp name is:TURNER
rowcount is:11-- empno is:7876-- emp name is:ADAMS
rowcount is:12-- empno is:7900-- emp name is:JAMES
rowcount is:13-- empno is:7902-- emp name is:FORD
rowcount is:14-- empno is:7934-- emp name is:MILLER

上面的这个sql语句也可以改写为:

DECLARE
  v_row emp%rowtype;   
  
  CURSOR emp_cursor IS
  SELECT * from emp where empno<>1000;
  
BEGIN

LOOP

  IF NOT emp_cursor%ISOPEN  THEN
     OPEN emp_cursor;
  END IF; 
  
  FETCH emp_cursor INTO v_row; 
  EXIT WHEN emp_cursor%NOTFOUND;
   
  dbms_output.put_line('rowcount is:'|| emp_cursor%ROWCOUNT || '-- empno is:' || v_row.empno || '-- emp name is:' || v_row.ename);
    
END LOOP;
CLOSE emp_cursor;

END;

在上面的语句中，我们可以看到需要先打开（open）游标，然后通过fetch提取出游标对应的一行数据，最后关闭（close）游标。实际开发中通常会使用for循环这种更简单的写法，for循环语句不再需要显示地打开、关闭游标，不在需要open、fetch和close语句，也不需要⽤%NOTFOUND属性检测是否到最后⼀条记录，这⼀切Oracle隐式的帮我们完成了。

上面的这段sql可以改成如下：

DECLARE

  CURSOR emp_cursor IS
  SELECT empno,ename from emp where empno<>1000;
  
BEGIN

  FOR v_row IN emp_cursor LOOP
  dbms_output.put_line('rowcount is:'|| emp_cursor%ROWCOUNT || '-- empno is:' || v_row.empno || '-- emp name is:' || v_row.ename);
  END LOOP; 

END;

从游标中批量取数据

前面用FETCH关键词从游标中取数据时，每次都只取一条，但我们也可以一次取多条来进行批量处理，语法格式是：

1	FETCH 游标名 BULK COLLECT INTO 变量名 LIMIT 条数;

用法示例：

DECLARE

  type tab_rowid is table of rowid;
  v_row tab_rowid; 
  
  CURSOR emp_cursor IS
  SELECT ROWID from tuser where id<1500;
  
BEGIN

  LOOP

    IF NOT emp_cursor%ISOPEN  THEN
       OPEN emp_cursor;
    END IF; 
    
    FETCH emp_cursor BULK COLLECT INTO v_row LIMIT 5;   --一次取5条数据
    FOR i IN 1 .. v_row.count LOOP
        dbms_output.put_line('rowcount is:'|| emp_cursor%ROWCOUNT || '-- rowid is:' || v_row(i));
    END LOOP;
    EXIT WHEN emp_cursor%NOTFOUND;
      
  END LOOP;
  CLOSE emp_cursor;

END;

open cursor_name(游标名) for

open cursor_name(游标名) for 语句是直接打开游标结果集，一般用在存储过程返回结果集的情况，比如下面的sql:

CREATE OR REPLACE PROCEDURE procedure_test01( 
       CUR_RECORD OUT TYPES.CURSORTYPE,
)
AS

OPEN CUR_RECORD FOR
SELECT empno,ename FROM emp WHERE empno>1000;