提示：公众号展示代码会自动换行，建议横屏阅读或左右滑动代码
游标的使用可以让用户像操作数组一样操作查询出来的数据集，这使得使用PL/SQL编程更加方便，实际上，它提供了一种从集合性质的结果中提取单条记录的方法。
一 游标概念
可以将游标（Cursor）形象地看成一个变动地光标。它实际上是一个指针，它在一段Oracle存放数据查询结果集或数据操作结果集地内存中，这个指针可以指向结果集中地任何一条记录。这样就可以得到它所指向数据了，但初始时它指向记录首记录。
可以简单地理解游标为指向结果集记录的指针，利用游标可以返回它当前指向的行记录（只能返回一行记录）。如果要返回多行记录，那么需要不断地滚动游标，把想要地数据查询一遍。用户可以操作游标所在行位置的记录，例如，把返回记录作为另一个查询的条件。
二 游标的种类
Oracle中游标分为静态游标和REF游标两种。其中，静态游标就像一个数据快照，打开游标后的结果集是对数据库数据的一个备份，数据不随着对表执行DML操作后而改变。
静态游标包含如下两种类型：
1.显式游标：是指在使用之前必须有明确的游标声明和定义，这样的游标定义后关联数据查询语句，通常会返回一行或多行。打开游标后，用户可以利用游标的位置对结果集进行检索，使之返回单一的行记录，用户可以操作此记录。关闭游标后，就不能再对结果集进行任何操作。显式游标需要用户自己写代码完成，一切由用户控制。
2.隐式游标：和显式游标不同，它被PL/SQL自动管理，也被称为SQL游标，用于处理多行查询返回的数据记录。由Oracle自动管理。该游标用户无法控制，但能得到它的属性信息。
三 显式游标
当使用显式游标时，需要使用游标属性确定显式游标的执行信息，显式游标包括%ISOPEN，%FOUND，%NOTFOUND和%ROWCOUNT四种属性。
游标的使用步骤主要分为四个步骤。
1.声明游标，声明游标主要用来给游标命名并且使得游标关联一个查询，具体语法如下：
1DECLARE CURSOR cursor_name IS SELECT_statement;
2.打开游标，游标中任何对数据的操作都是建立在游标被打开的前提下，打开游标初始化了游标指针，游标一旦打开，其结果集都是静态的。也就是说，结果集此时不会反映出数据库中对数据进行的增加，删除，修改操作，具体语法如下：
1OPEN cursor_name;
3.读取数据，读取数据要使用FETCH语句完成，它可以把游标指向位置的记录放入到PL/SQL声明的变量中，它只能取出指针当前行的记录。正常情况下，FETCH要和循环语句一起使用，这样指针会不断前进，直到某个条件不符合要求而退出，使用FETCH时游标属性%ROWCOUNT会不断累加，具体语法如下：
1FETCH cursor_name INTO record_name;
4.关闭游标，当关闭某个游标时意味着释放资源，结果集中的数据将不能做任何操作，具体语法如下：
1CLOSE cursor_name;
注意，当引用显式游标属性时，需要带有游标名作为前缀，例如: emp_cursor%ROWCOUNT。下面介绍这四种游标的属性。
1.%ISOPEN
该属性用于检测游标是否已经打开。如果游标已经打开，则返回TURE，否则返回FALSE。想要获取游标的数据，首先一点就是游标是打开的，如果游标在关闭状态下操作数据则会出现错误。下面以检测是否打开游标pdc_isopen_cur，并打开游标为例，说明使用%ISOPEN属性的方法：
1DECLARE
2CURSOR pdc_isopen_cur IS SELECT*FROM productinfo;
3cur_prodrcd productinfo%ROWTYPE;
4BEING
5 IF NOT pdc_isopen_cur %ISOPEN THEN
6 OPEN pdc_isopen_cur ;
7 ELSE
8 FETCH pdc_isopen_cur INTO cur_prodrcd;
9 DBMS_OUTPUT.PUT_LINE(‘产品ID:’||cur_prodrcd.productid||’产品名称:’||cur_prodrcd.productname||’产品价格:’||cur_prodrcd.productprice);
10 END IF;
11END;
2.%FOUND
该属性用于检测游标结果集是否存在数据。如果存在数据，则返回TRUE，否则返回FALSE。下面以检测是否提取到游标数据，并且在未提取到数据时退出循环为例：
1DECLARE
2CURSOR pdc_found_cur IS SELECT*FROM productinfo;
3cur_prodrcd productinfo%ROWTYPE;
4BEING
5 OPEN pdc_isopen_cur ;
6 LOOP
7 IF pdc_found_cur%FOUND THEN
8 FETCH pdc_isopen_cur INTO cur_prodrcd;
9 DBMS_OUTPUT.PUT_LINE(‘产品ID:’||cur_prodrcd.productid||’产品名称:’||cur_prodrcd.productname||’产品价格:’||cur_prodrcd.productprice);
10 ELSE
11 DBMS_OUTPUT.PUT_LINE(‘没有数据被提取’);
12 EXIT;
13 END IF;
14 END LOOP;
15 CLOSE pdc_found_cur;
16END;
3.%NOTFOUND
该属性与%FOUND的含义正好相反，用于检测结果集是否不存在数据。如果不存在则返回TURE，否则返回FALSE。它的使用方法可以参考%FOUND的例子。
4.%ROWCOUNT
它也是比较常用的属性，利用它可以知道当前已经返回了多少行数据。具体使用方法如下：
1DECLARE
2CURSOR pdc_rowcount_cur IS SELECT*FROM productinfo;
3TYPE pdct_tab IS TABLE OF productinfo%ROWTYPE;
4pdc_count_rd pdct_tab;
5BEING
6 OPEN pdc_rowcount_cur ;
7 LOOP
8 FETCH pdc_rowcount_cur BULK COLLECT INTO pdc_count_rd LIMIT 2;
9 FOR i IN pdc_count_rd.first..pdc_count_rd.last LOOP
10 DBMS_OUTPUT.PUT_LINE(‘产品ID:’|| pdc_count_rd(i).productid||’产品名称:’||pdc_count.rd(i).productname||’产品价格:’||pdc_count_rd(i).productprice);
11 END LOOP;
12 IF MOD(pdc_rowcount_cur%ROWCOUNT，2) = 0 THEN
13 DBMS_OUTPUT.PUT_LINE(‘读取到了第’||pdc_rowcount_cur%ROWCOUNT||’条记录’);
14 ELSE
15 DBMS_OUTPUT.PUT_LINE(‘读取到单条记录为第’||pdc_rowcount_cur%ROWCOUNT||’条记录’);
16 END IF;
17 EXIT WHEN pdc_rowcount_cur%NOTFOUND;
18 END LOOP;
19 CLOSE pdc_rowcount_cur;
20END;
四 显式游标中的循环
1.游标中的LOOP 语句
通常显式游标提取数据不会只有一条，而是多条记录，这样就需要一个遍历结果集的方式，而LOOP语句就能实现该功能，下面示例将演示在游标中任何使用LOOP语句：
1DECLARE
2CURSOR pdct_loop_cur IS SELECT productid,productname,productprice FROM productinfo WHERE productprice>2400;
3cur_productid productinfo.productid%TYPE;
4cur_productname productinfo.productname%TYPE;
5cur_productprice productinfo.productprice%TYPE;
6BEGIN
7 OPEN pdct_loop_cur;
8 LOOP
9 FETCH pdct_loop_cur INTO cur_productid, cur_productname, cur_productprice;
10 EXIT WHEN pdct_loop_cur%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE(‘产品ID:’|| cur_productid ||’产品名称:’|| cur_productname ||’产品价格:’|cur_productprice);
12 END LOOP;
13 CLOSE pdct_loop_cur;
14END;
2.使用BULK COLLECT和FOR语句的游标
游标中通常使用FETCH…INTO…语句提取数据，这种方法是单条数据提取，在数据量很大的情况下执行效率不是很理想，而FETCH…BUKK COLLECT INTO 语句可以批量提取数据，在数据量大的情况下，它的执行效率比单条提取数据的高。此语句的用法可参考如下示例：
1DECLARE
2CURSOR pdct_collect_cur IS SELECT*FROM productinfo;
3TYPE pdct_tab IS TABLE OF productinfo%ROWTYPE;
4pdct_rd pdct_tab;
5BEGIN
6 OPEN pdct_collect_cur;
7 LOOP
8 FETCH pdct_ collect_cur BULK COLLECT INTO prdct_rd LIMIT 2;
9 FOR i IN 1..pdct_rd.count LOOP
10 DBMS_OUTPUT.PUT_LINE(‘产品ID:’|| pdct_rd(i).productid||’产品名称:’||pdct.rd(i).productname||’产品价格:’||pdct_rd(i).productprice);
11 END LOOP;
12 EXIT WHEN pdct_collect_cur%NOTFOUND;
13 END LOOP;
14 CLOSE pdct_collect_cur;
15END;
3.使用CURSOR FOR LOOP
游标很多机会都会使用迭代结果集，在PL/SQL这个过程中可以使用更简单的方式实现，CURSOR FOR LOOP不需要特别的声明变量，它可以提出行对象类型的数据。在执行循环之前，Oracle会隐含的打开游标，并且每循环一次自动提取一行数据，在提取了所有数据之后自动退出循环并隐含地关闭游标。可以按照类似示例中提取的方式得到列数据，详细脚本如下：
1DECLARE
2CURSOR cf1 IS SELECT productname, productprice FROM productinfo WHERE productprice>1200;
3BEGIN
4 FOR curcf1 IN cf1 LOOP
5 LOOP
6 DBMS_OUTPUT.PUT_LINE(‘名称:’||curcf1.productname||’产品价格:’||curcf1.productprice);
7 EXIT WHEN cf1%ROWCOUNT:=&input;
8 END LOOP;
9END;
五 带参数的显式游标
在使用显式游标时是可以指定参数的，指定的参数包括参数的顺序和参数的类型，参数可以传递给游标在查询中使用，这样方便了用户根据不同的查询条件进行查询，也方便了游标会在存储过程中的使用。注意，定义参数只能指定数据类型，不能指定长度，另外，必须在游标的SELECT语句的WHERE子句中引用游标参数，否则失去了定义参数游标的意义。
此类型的游标语法可以参考如下示例：
1DECLARE
2cur_productid productinfo.productid%TYPE:=’0204’;
3cur_productprice productinfo.productprice%TYPE:=1200;
4cur_prodrcd productinfo %ROWTYPE;
5CURSOR pdct_parameter_cur (id VARCHAR, price NUMBER) IS SELECT *FROM productinfo WHERE productid LIKE id||’%’ AND productprice>price;
6BEGIN
7 OPEN pdct_parameter_cur(cur_productid, cur_productprice);
8 LOOP
9 FETCH pdct_parameter_cur INTO cur_prodrcd;
10 EXIT WHEN pdct_parameter_cur%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE(‘产品ID:’|| cur_prodrcd.productid||’产品名称:’|| cur_prodrcd.productname||’产品价格:’|| cur_prodrcd.productprice);
12 END LOOP;
13 CLOSE pdct_parameter_cur;
14END;
六 更新或删除显式游标行
通过使用显式游标，不仅可以取得游标结果集的数据，而且可以更新或删除游标结果集的当前行。注意，当使用游标更新或删除数据时，定义游标必须带有FOR UPDATE子句，并且在更新或删除游标行时必须带有WHERE CURRENT OF子句。
1.更新游标，下面以使用替代变量输入部门号，输出该部门所有雇员姓名及原工资，并更新雇员工资为例：
1DECLARE
2CURSOR emp_cursor IS SELECT ename,sal,deptno FORM emp FOR UPDATE;
3dno INT:=&input;
4BEGIN
5 FOR emp_record IN emp_cursor LOOP
6 IF emp_record.deptno=dno THEN
7 DBMS_OUTPUT.PUT_LINE(‘姓名:’||emp_record.ename||’原工资:’||emp_record.sal);
8 UPDATE emp SET sal=sal*1.1 WHERE CURRENT OF emp_cursor;
9 END IF;
10 END LOOP;
11END;
2.删除游标变量，使用替代变量输入雇员姓名，输出其雇员姓名及工资，并删除该雇员说明其用法：
1DECLARE
2CURSOR emp_cursor IS SELECT ename FROM emp FOR UPDATE;
3name VARCHAR2(10):=LOWER(‘&name’);
4BEGIN
5FOR emp_record IN emp_cursor LOOP
6 IF LOWER(emp_record.ename)=name THEN
7 DELETE FROM emp WHERE CURRENT OF emp_cursor;
8 ELSE
9 DBMS_OUTPUT.PUT_LINE(‘姓名:’||emp_record.ename);
10 END IF;
11 END LOOP;
12END;
3.使用OF子句在特定表上加行共享锁，当游标子查询涉及到多张表时，如果在特定表上加行共享锁，那么需要使用OF子句，通过使用输入变量，显示该部门所有员工的姓名，并删除这些雇员为例，说明在显式游标使用OF子句的方法：
1DECLARE
2CURSOR emp_cursor IS SELECT a.dname, b,ename FROM dept a JOIN emp b ON a.deptno=b.deptno FOR UPDATE OF b.deptno;
3Name VARCHAR2(10):=LOWER(‘&input_name’);
4BEGIN
5 FOR emp_record IN emp_cursor LOOP
6 IF LOWER(emp_record.dname)=name THEN
7 DBMS_OUTPUT.PUT_LINE(‘姓名:’||emp_record.ename);
8 DELETE FROM emp WHERE CURRENT OF emp_cursor;
9 END IF;
10 END LOOP;
11END;
七 隐式游标
隐式游标和显式游标有所差异，它虽然没有显式游标一样的操作作性，但在实际的工作中也经常使用。
1.隐式游标的特点
每当运行SELECT或DML语句时，PL/SQL会打开一个隐式的游标，隐式的游标不受用户的控制，这点和显式的游标有明显的不同，下面列出了两者的不同处：
（1）隐式游标由PL/SQL自动管理。
（2）隐式游标的默认名称是SQL。
（3）SELECT或DML操作产生隐式游标。
（4）隐式游标的属性值始终是最新执行的SQL语句的。
具体代码如下：
1DECLARE
2cur_productname prodctinfo.productname%TYPE;
3cur_productprice prodctinfo.productprice%TYPE;
4BEGIN
5 SELECT productname, productprice INTO cur_productname, cur_productprice FROM productinfo WHERE productid=’&inputID’;
6 IF SQL%FOUND THEN
7 DBMS_OUTPPUT.PUT_LINE(‘产品名称:’||cur_productname||’产品价格:’||cur_productprice);
8 END IF;
9END;
2.隐式游标的属性
隐式游标的属性和显式游标的属性具体表达含义有区别，但属性种类没有变，下面列出了隐式游标的属性:
（1）%ISOPEN属性，该属性永远返回FALSE，它由Oracle子集控制。
（2）%FOUND属性，此属性可以反映DML操作是否影响到了数据，当DML操作对数据有了影响时该属性为TRUE，否则为FALSE，也可以反映SELECT INTO语句是否返回了数据。
（3）%NOTFOUND属性，与%FOUND属性相反，当DML操作没有影响数据以及SELECT INTO没有返回数据时该属性为TRUE，否则返回FALSE。
（4）%ROWCOUNT属性，该属性可以反映出DML操作对数据影响的数量。
八 游标中使用异常处理
使用游标时，某些情况下得到的数据超出了控制范围，如果不加处理会出现脚本执行中断的情况，这种情况下，脚本开发者通常会使用异常处理来维护脚本的稳定性，前面介绍过当数据库中的数据记录发生变化时使用SELECT INTO语句返回的结果有可能不是单体记录（可能空，也可能多条），这时会出现脚本中断报错的现象，为了避免程序中断则要做异常处理，其代码如下：
1DECLARE
2cur_productname prodctinfo.productname%TYPE;
3cur_productprice prodctinfo.productprice%TYPE;
4BEGIN
5 SELECT productname, productprice INTO cur_productname, cur_productprice FROM productinfo WHERE productid=’&inputID’;
6 IF SQL%FOUND THEN
7 DBMS_OUTPPUT.PUT_LINE(‘产品名称:’||cur_productname||’产品价格:’||cur_productprice);
8 END IF;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 DBMS_OUTPPUT.PUT_LINE(‘没有相应数据…’);
12 WHEN TOO_MANY_ROWS THEN
13 DBMS_OUTPPUT.PUT_LINE(‘数据过多…’);
14END;
九 REF游标
REF游标是一种动态游标，游标变量是基于REF CURSOR类型所定义的变量，它实际是指向内存地址的指针。使用显式游标只能定义静态游标，而通过使用游标变量可以在打开游标时指定其所对应的SELECT语句，从而实现动态游标。注意，不能在远程子程序中使用游标变量，游标变量包括定义游标变量，打开游标变量，提取数据，关闭游标变量，如下所示：
1.定义REF CURSOR类型和游标变量：
1TYPE ref_type_name IS REF CURSOR [ RETURN return_type];
2cursor_variable ref_type_name;
2.打开游标，打开游标变量用于指定游标所对应的SELECT语句。注意，当指定游标子查询时，不能带有FOR UPDATE子句。当打开游标变量时，会执行游标变量所对应的SELECT语句，并将数据存放到游标结果集，语法如下：
1OPEN cursor_variable FOR select_statement;
3.提取数据，提取数据用于将结果集中的行数据存放到PL/SQL变量，语法如下：
1FETCH cursor_variable INTO variable1,variable2,…;
4.关闭游标变量，关闭游标变量用于释放游标结果集，语法如下：
1CLOSE cursor_variable;
5.使用无返回类型的游标变量
当定义REF CURSOR类型时，如果不指定RETURN子句，那么在打开游标时可以指定任何SELECT语句，下面使用输入变量列名，表名，WHERE条件，并输出列值说明：
1DECLARE
2TYPE ref_cursor_type IS REF CURSOR;
3ref_cursor ref_curor_type;
4v_empno NUMBER(6);
5v_ename VARCHAR2(10);
6BEGIN
7 OPEN ref_cursor FOR SELECT &empno, &ename FROM &table WHERE &cond;
8 LOOP
9 FETCH ref_cursor INTO v_empno,v_ename;
10 EXIT WHEN ref_cursor%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE(‘empno=’||v_empno||’ename=’||v_ename);
12 END LOOP;
13 CLOSE ref_cursor;
14END;
6.使用有返回类型的游标变量
定义REF CURSOR类型时，如果指定RETURN子句，那么在打开游标时SELECT语句的返回结果必须与RETURN子句所指定的记录类型匹配。下面以使用替代变量输入部门号，并显式该部门所有雇员的姓名和工资为例，说明使用有返回类型游标变量的方法：
1DECLARE
2TYPE emp_cursor_type IS REF CURSOR RETURN emp%ROWTYPE;
3emp_cursor emp_cursor_type;
4emp_record emp%ROWTYPE;
5BEGIN
6 OPEN emp_cursor FOR SELEC*FROM emp WHERE deptno=&dno;
7 LOOP
8 FETCH emp_cursor INTO emp_record;
9 EXIT WHEN emp_cursor%NOTFOUND;
10 DBMS_OUTPUT.PUT_LINE(‘姓名:’||emp_record.ename||’工资:’||emp_record.sal);
11 END LOOP;
12 CLOSE emp_cursor;
13END;
7.使用CURSOR表达式
CURSOR表达式用于实现嵌套游标，通过使用CURSOR表达式，可以在PL/SQL块中处理基于多张表的复杂关联数据，为了在PL/SQL块中取得嵌套游标的数据，需要定义游标变量和嵌套循环，下面以使用替代变量输入部门号，输出部门名称以及该部门所有雇员的姓名和岗位为例，说明使用CURSOR表达式的方法：
1DECLARE
2CURSOR dept_cursor(no NUMBER) IS SELECT a.dname,CURSOR(SELECT*FROM EMP WHERE deptno =a.deptno) FROM dept a WHERE a.deptno=no;
3TYPE ref_cursor_type IS REF CURSOR;
4emp_cursor ref_cursor_type;
5emp_record emp%ROWTYPE;
6v_dname dept.dname%TYPE;
7BEGIN
8 OPEN dept_cursor(&dno);
9 LOOP
10 FETCH dept_cursor INTO v_dname, emp_cursor;
11 EXIT WHEN dept_cursor%NOTFOUND;
12 DBMS_OUTPUT.PUT_LINE(‘部门名:’||v_dname);
13 LOOP
14 FETCH emp_cursor INTO emp_record;
15 EXIT WHEN emp_cursor%NOTFOUND;
16 DBMS_OUTPUT.PUT_LINE('-----雇员名:'||emp_record.ename||'岗位:'||emp_record.job);
17 END LOOP;
18 END LOOP;
19 CLOSE dept_cursor;
20END;
扫描下方二维码关注公众号，了解相关更新
