08-Oracle游标管理（定义，打开、获取数据及关闭游标）

目标

1.确定何时需要显示游标

2.声明、打开和关闭显示游标

3.从显示游标中提取数据

4.了解与游标有关的属性

5.使用游标FOR循环检索游标中的数据

6.在游标FOR循环的子查询中声明游标

7.评估使用逻辑运算符结合在一起的布尔条件

游标

1、在使用一个PL/SQL块来执行DML语句或只返回一行结果的SELECT语句时，将创建一个隐式游标。

2、可用将游标视为内存中的一个数组或区域，它包含当前处理的值。

3、当SELECT语句返回多行结果时，用户必须创建一个显示游标。然后查询所返回的数据就将放在显示游标中进行处理。

•示例：显示当用户没有创建一个显示游标以获得多行结果时发生的情况。

DECLAREv_title books.title%TYPE;v_retail books.retail%TYPE;	
BEGINSELECT title,retailINTO v_title,v_retailFROM books NATURAL JOIN orderitemsWHERE order#=1012;DBMS_OUTPUT.PUT_LINE('Books title: '||v_title||' Retail price: '||v_retail);
END;

示例中虽然包括了一个WHERE子句来确保只处理一个订单，但是在这个订单中包括了不止一本书。当PL/SQL块将会检索多个行时，必须使用显示游标来存储要处理的数据。与Oracle自动维护的隐式游标不同，必须通过PL/SQL语句来手工声明、打开和关闭显示游标。

声明显示游标

•显示游标是在PL/SQL块的声明部分声明的。

•声明游标的语法：

CURSOR cursor_name IS selectquery;

•前面示例中，需要一个显示游标来检索订单1012中的图书的书名和零售价。

DECLARECURSOR books_cursor ISSELECT title,retailFROM books NATURAL JOIN orderitemsWHERE order#=1012;

•示例中声明了一个名为BOOKS_CURSOR的游标来存储行。然后SELECT语句将检索图书的书名和零售价。

•游标的结构是由使用SELECT语句检索的数据定义的。

•注意：虽然需要每一本书的ISBN来联接BOOKS表和ORDERITEMS表，以便确定要选择的正确图书，但是在游标本身中并没有包括这一列。

•目前只是声明了游标，在进行任何处理之前，必须首先打开游标。

打开显示游标

•在打开游标时，将分配必要的内存，执行SELECT语句，然后SELECT子句检索的数据加载到游标中。

•打开游标的语法

OPEN cursor_name;示例：
OPEN books_cursor;打开游标之后，可以将游标中包含的数据赋给变量以进行处理

关闭显示游标

•从游标中检索了所有数据之后，必须明确关闭它。

•在PL/SQL块的可执行部分使用CLOSE命令来关闭显示游标

•CLOSE命令的语法

CLOSE cursor_nameCLOSE books_cursor;

从显示游标中提取数据

•SELECT语句不能用来检索显示游标中的数据。

•必须使用FETCH命令来提取这种游标中的数据。

•FETCH命令检索游标中的行，然后将值赋给变量。注意，必须首先在PL/SQL块的声明部分声明变量，然后才能在可执行部分引用它们。

•FETCH命令语法：

FETCH cursor_name INTO variablename[,…variablename];FETCH books_cursor INTO v_title,v_retail;

示例：显示游标的使用

DECLAREv_title books.title%TYPE;v_retail books.retail%TYPE;CURSOR books_cursor ISSELECT title,retailFROM books NATURAL JOIN orderitemsWHERE order#=1012;
BEGINOPEN books_cursor;FETCH books_cursor INTO v_title,v_retail;DBMS_OUTPUT.PUT_LINE('Books title: '||v_title||' Retail price: '||v_retail);CLOSE books_cursor;
END;

•解释示例语句

声明部分定义了变量v_title 和v_retail 以及名为books_cursor 的游标。

游标是由一个SELECT语句定义的，该语句检索包括在订单1012中的图书的书名和零售价。

这个块的可执行部分，打开了之前声明的游标。

FETCH语句用来检索游标中的行并将值赋给v_title 和v_retail变量。

包括了 DBMS_OUTPUT程序包以显示变量的内容。

结果

结果中发现只有一行数据输出

 独立执行这个PL/SQL块的SELECT语句，显示

•要想显示订单1012中每一本书的书名和零售价，应该将检索和显示游标内容的语句包括在一个循环中。

•基本、FOR和WHILE循环都要求包括某种条件来确定应该在何时终止循环。

•每一种游标（隐式游标和显示游标）都有4个可以用作PL/SQL块中的条件的属性。

•最经常用来控制循环的三个游标属性是：

%ROWCOUNT

%FOUND

%NOTFOUND

%ROWCOUNT属性包含一个整数值，代表在执行块时处理的行数。

%FOUND和%NOTFOUND属性用来反映是否已经找到了要处理的行。如果在执行块的过程中影响了一行或多行，那么%FOUND属性将包含布尔值TRUE，相反，如果没有行受到影响，那么%NOTFOUND属性为TRUE。

还有一个属性，%ISOPEN通常用来确定在执行PL/SQL块之后是否关闭游标。

游标属性	说明
%ROWCOUNT	指出处理的行
%FOUND	如果处理了一行或多行，这包含值TRUE——如果没有处理行，则为FALSE
%NOTFOUND	如果没有处理行，则包含值TRUE——如果处理了一行或多行，则为FALSE
%ISOPEN	如果在处理之后不关闭数据，这包含值TRUE——如果关闭游标，则为FALSE。在发生隐式游标时，由于它是自动关闭的，因此这个值在处理之后总是FALSE

•如果在一个PL/SQL块中包括了一个基本循环来控制一组语句的执行，那么必须包括一个条件来结束这个循环。

•可以将条件指定为“当游标不再包含任何值时，就结束循环”。

DECLAREv_title books.title%TYPE;v_retail books.retail%TYPE;CURSOR books_cursor ISSELECT title,retailFROM books NATURAL JOIN orderitemsWHERE order#=1012;
BEGINOPEN books_cursor;LOOPFETCH books_cursor INTO v_title,v_retail;EXIT WHEN books_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE('Books title: '||v_title||' Retail price: '||v_retail);END LOOP;CLOSE books_cursor;
END;

显示结果

游标FOR循环

•因为经常使用循环来控制游标中检索的行的处理，所有Oracle包括了一种快捷方法来处理PL/SQL块中的显示游标。游标FOR循环可以用来自动（或隐含）打开和关闭一个游标，也可以用来提取游标中的数据。

•游标FOR循环的语法：

FOR record_name IN cursor_name LOOPstatements;[statement;…]
END LOOP;

说明：

不是检索游标中的行并向变量赋值，而是将行的内容赋给一个记录。

“记录”是一种复合数据类型，可以使其结构与检索的行的结构相同。要想指定记录的结构与检索的行的结构相同，可以在定义记录时使用%ROWTYPE属性。

与%TYPE属性相似， %TYPE用来定义基于一个单独列的变量，而%ROWTYPE属性定义了基于数据库表中包含的所有列的记录。

示例：包含游标FOR循环的PL/SQL块

DECLARECURSOR books_cursor ISSELECT title,retailFROM books NATURAL JOIN orderitemsWHERE order#=1012;r_books books%ROWTYPE;
BEGINFOR r_books IN books_cursor LOOPDBMS_OUTPUT.PUT_LINE('Books title: '||r_books.title||' Retail price: '||r_books.retail);END LOOP;
END;

•示例说明：

在块的声明部分定义了记录 r_books，它具有books表的结构。

可执行部分包含一个游标FOR循环，它隐含打开books_cursor的游标，并将其内容赋给r_books记录

对名为books_cursor的游标中包含的每一行都执行一次这个游标FOR循环。这个循环中包含的唯一一个语句将显示当前正在处理的行。

注意：在DBMS_OUTPUT程序包显示的字符引用了记录中包含的列名称。因为将数据赋值给一个记录而不是单独的变量，所以显示值的唯一方法就是指定包含所需数据的列名称。在使用%ROWTYPE属性定义这个记录时，还为从books表中检索的各个值指定了列名称。要想指定应该显示哪一列或哪些列，必须在列名称之前添加包含数据的记录的名称，如r_books.title

在处理了books_cursor中包含的最后一行之后，这个循环将会终止，将隐含关闭游标。

在使用游标FOR循环时，可以使用一个子查询来声明游标

不在PL/SQL块中的声明部分声明游标，可以使用IN子句中的SELECT语句来代替游标名称。

注意：因为游标没有一个名字，所以不能应用游标属性，例如%NOTFOUND

示例：使用子查询的游标FOR循环

BEGINFOR r_books IN (SELECT title,retailFROM books NATURAL JOIN orderitemsWHERE order#=1012) LOOPDBMS_OUTPUT.PUT_LINE('Books title: '||r_books.title||' Retail price: '||r_books.retail);END LOOP;
END;

•修改示例，实现确定订单1012中购买的图书以及总应付款额。

DECLAREv_ordertotal NUMBER(5,2):=0;
BEGINFOR r_books IN (SELECT title,retail,quantityFROM books NATURAL JOIN orderitemsWHERE order#=1012) LOOPDBMS_OUTPUT.PUT_LINE('Books title: '||r_books.title||' Retail price: '||r_books.retail);v_ordertotal :=v_ordertotal + r_books.retail*r_books.quantity;END LOOP;DBMS_OUTPUT.PUT_LINE('Total Amount Due: '||v_ordertotal);
END;

带有逻辑运算符的游标循环控制

•如果只需要处理特定数量的行，应该怎么办？

•示例：确定books表中5本价格最高的图书。

•分析：

考虑可以创建以一个子查询，选择ROWNUM小于或等于5的图书。

还可以在PL/SQL块中嵌一个游标，从游标中提取行时，提取它们的顺序与最初将它们放在游标中的顺序相同。要想确保游标中的第一行包含零售价最高的图书，可以在声明游标时向定义游标的SELECT语句添加一个ORDER BY子句，根据Retail列降序排列。

在使用%ROWCOUNT属性作为循环条件时，用户可以指定从游标中提取的行，可以在循环条件中使用AND逻辑运算符以及%FOUND属性，以便确保只有在游标包含额外的行时才会继续循环。

DECLAREv_title books.title%TYPE;v_retail books.retail%TYPE;v_number NUMBER(2):=&How_Many_Books_To_Display;CURSOR books_cursor ISSELECT title,retailFROM booksORDER BY retail DESC;	
BEGINOPEN books_cursor;FETCH books_cursor INTO v_title,v_retail;WHILE books_cursor%ROWCOUNT<=v_number ANDbooks_cursor%FOUND LOOPDBMS_OUTPUT.PUT_LINE(v_title||','||v_retail);FETCH books_cursor INTO v_title,v_retail;END LOOP;CLOSE books_cursor;
END;

•v_number NUMBER(2):=&How_Many_Books_To_Display;要求用户输入要显示的图书数量，如果用户输出数字3，那么在输出中将显示3本价格最高的图书

•WHILE books_cursor%ROWCOUNT<=v_number AND books_cursor%FOUND 两个条件使用AND逻辑运算符结合在一起，所有这两个条件都必须为TRUE，否则这个循环就不会执行

•FETCH books_cursor INTO v_title,v_retail;在WHILE语句之前，确保在访问WHILE之前找到了一行。如果没有提取任何行，那么无论用户要求多少本书或者游标中实际存在多少行，%FOUND属性都将为NULL，循环不会执行

AND	TRUE	FALSE	NULL	OR	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL	TRUE	TRUE	TRUE	TRUE
FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	NULL
NULL	NULL	FALSE	NULL	NULL	TRUE	NULL	NULL

游标的使用——处理隐式游标

•显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；而对于非查询语句，如修改、删除操作，则由 ORACLE 系统自动地为这些操作设置游标并创 建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为 SQL ，这是由ORACLE 系统定义的。

•对于隐式游标的操作，如定义、打开、取值及关闭操作，都由 ORACLE系统自动地完成，无需用户进行处理。

•用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条 SQL语句所 包含的数据。

格式调用为： SQL%

注： INSERT, UPDATE, DELETE, SELECT 语句中不必明确定义游标。

隐式游标属性

SQL %FOUND 布尔型属性 , 当最近一次读记录时成功返回，则值为 true ；

SQL %NOTFOUND 布尔型属性 , 与 %found 相反；

SQL %ROWCOUNT 数字型属性 , 返回已从游标中读取得记录数；

SQL %ISOPEN 布尔型属性 , 取值总是 FALSE 。 SQL 命令执行完毕立即关闭隐式游标。

例：删除图书库存中某出版社出版的图书，如果不再存在该出版社的图书，则在出版设信息中删除该出版社信息.

DECLARE
v_pubid books.pubid%TYPE :=&in_pubid;
BEGINDELETE FROM books WHERE pubid=v_pubid;DBMS_OUTPUT.PUT_LINE('删除出版社编号为'||v_pubid||'的图书记录');COMMIT;IF SQL%NOTFOUND THENDELETE FROM publisher WHERE pubid=v_pubid;END IF;COMMIT;
END;

练习

1.删除订单表中指定客户编号的订单信息，如果不再存在该客户的订单，则在客户信息中删除该客户信息.

总结

•从一个表中检索多个行时，就需要显示游标

•在使用数据填充显示游标之前必须定义并打开它

•在打开游标时，将执行定义这个游标的SELECT语句

•可以使用FETCH命令来检索显示游标中的数据

•必须关闭显示游标

•游标FOR循环将隐含（或自动）打开和关闭显示游标并提取游标中的数据

•游标属性可以用来确定游标是否是打开的以及游标所处理的行数（如果有）

本人从事软件项目开发20多年，2005年开始从事Java工程师系列课程的教学工作，录制50多门精品视频课程，包含java基础，jspweb开发，SSH,SSM，SpringBoot，SpringCloud,人工智能，在线支付等众多商业项目，每门课程都包含有项目实战，上课PPT，及完整的源代码下载，有兴趣的朋友可以看看我的在线课堂

讲师课堂链接：https://edu.csdn.net/lecturer/893