目标
1.确定何时需要显示游标
2.声明、打开和关闭显示游标
3.从显示游标中提取数据
4.了解与游标有关的属性
5.使用游标
FOR
循环检索游标中的数据
6.在游标
FOR
循环的子查询中声明游标
7.评估使用逻辑运算符结合在一起的布尔条件
游标
1、在使用一个
PL/SQL
块来
执行
DML
语句
或
只返回一行结果
的
SELECT
语句时，将创建一个
隐式游标
。
2、可用将游标视为内存中的一个数组或区域，它包含当前处理的值。
3、当
SELECT
语句
返回多行结果
时，用户必须创建一个
显示游标
。然后查询所返回的数据就将放在显示游标中进行处理。
•示例：显示当用户没有创建一个显示游标以获得多行结果时发生的情况。
DECLARE
v_title books.title%TYPE;
v_retail books.retail%TYPE;
BEGIN
SELECT title,retail
INTO v_title,v_retail
FROM books NATURAL JOIN orderitems
WHERE 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中的图书的书名和零售价。
DECLARE
CURSOR books_cursor IS
SELECT title,retail
FROM books NATURAL JOIN orderitems
WHERE order#=1012;
•
示例中声明了一个名为
BOOKS_CURSOR
的游标来存储行。然后
SELECT
语句将检索图书的书名和零售价。
•
游标的结构是由使用
SELECT
语句检索的数据定义的
。
•
注意
：虽然需要每一本书的
ISBN
来联接
BOOKS
表和
ORDERITEMS
表，以便确定要选择的正确图书，但是在游标本身中并没有包括这一列。
•
目前只是声明了游标，
在进行任何处理之前，必须首先打开游标。
打开显示游标
•
在打开游标时，将分配必要的内存，执行
SELECT
语句，然后
SELECT
子句检索的数据加载到游标中。
•
打开游标的语法
OPEN cursor_name;
示例：
OPEN books_cursor;
打开游标之后，可以将游标中包含的数据赋给变量以进行处理
关闭显示游标
•
从游标中检索了所有数据之后，必须明确
关闭
它。
•
在
PL/SQL
块的可执行部分使用
CLOSE
命令来关闭显示游标
•
CLOSE
命令的语法
CLOSE cursor_name
CLOSE books_cursor;
从显示游标中提取数据
•
SELECT
语句不能用来检索显示游标中的数据。
•
必须使用
FETCH
命令来提取这种游标中的数据。
•
FETCH
命令检索游标中的行，然后将值赋给变量。注意，必须首先在
PL/SQL
块的声明部分声明变量，然后才能在可执行部分引用它们。
•
FETCH
命令语法：
FETCH cursor_name INTO variablename[,…variablename];
FETCH books_cursor INTO v_title,v_retail;
示例：显示游标的使用
DECLARE
v_title books.title%TYPE;
v_retail books.retail%TYPE;
CURSOR books_cursor IS
SELECT title,retail
FROM books NATURAL JOIN orderitems
WHERE order#=1012;
BEGIN
OPEN 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
块中包括了一个基本循环来控制一组语句的执行，那么必须包括一个条件来结束这个循环。
•
可以将条件指定为“当游标不再包含任何值时，就结束循环”。
DECLARE
v_title books.title%TYPE;
v_retail books.retail%TYPE;
CURSOR books_cursor IS
SELECT title,retail
FROM books NATURAL JOIN orderitems
WHERE order#=1012;
BEGIN
OPEN books_cursor;
LOOP
FETCH 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 LOOP
statements;
[statement;…]
END LOOP;
说明：不是检索游标中的行并向变量赋值，而是 将行的内容赋给一个记录 。“记录”是一种复合数据类型 ， 可以使其结构与检索的行的结构相同 。要想指定记录的结构与检索的行的结构相同，可以在定义记录时使用 %ROWTYPE 属性。与 %TYPE 属性相似， %TYPE 用来定义基于一个单独列的变量，而 %ROWTYPE 属性定义了基于数据库表中包含的所有列的记录。
示例：包含游标FOR循环的PL/SQL块
DECLARE
CURSOR books_cursor IS
SELECT title,retail
FROM books NATURAL JOIN orderitems
WHERE order#=1012;
r_books books%ROWTYPE;
BEGIN
FOR r_books IN books_cursor LOOP
DBMS_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
循环
BEGIN
FOR r_books IN (SELECT title,retail
FROM books NATURAL JOIN orderitems
WHERE order#=1012) LOOP
DBMS_OUTPUT.PUT_LINE('Books title: '||r_books.title||' Retail price: '||r_books.retail);
END LOOP;
END;
•修改示例，实现确定订单1012中购买的图书以及总应付款额。
DECLARE
v_ordertotal NUMBER(5,2):=0;
BEGIN
FOR r_books IN (SELECT title,retail,quantity
FROM books NATURAL JOIN orderitems
WHERE order#=1012) LOOP
DBMS_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
属性，以便确保只有在游标包含额外的行时才会继续循环。
DECLARE
v_title books.title%TYPE;
v_retail books.retail%TYPE;
v_number NUMBER(2):=&How_Many_Books_To_Display;
CURSOR books_cursor IS
SELECT title,retail
FROM books
ORDER BY retail DESC;
BEGIN
OPEN books_cursor;
FETCH books_cursor INTO v_title,v_retail;
WHILE books_cursor%ROWCOUNT<=v_number AND
books_cursor%FOUND LOOP
DBMS_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;
BEGIN
DELETE FROM books WHERE pubid=v_pubid;
DBMS_OUTPUT.PUT_LINE('删除出版社编号为'||v_pubid||'的图书记录');
COMMIT;
IF SQL%NOTFOUND THEN
DELETE FROM publisher WHERE pubid=v_pubid;
END IF;
COMMIT;
END;
练习
1.
删除订单表中指定客户编号的订单信息，如果不再存在该客户的订单，则在客户信息中删除该客户信息
.
总结
•
从一个表中检索多个行时，就需要显示游标
•
在使用数据填充显示游标之前必须定义并打开它
•
在打开游标时，将执行定义这个游标的
SELECT
语句
•
可以使用
FETCH
命令来检索显示游标中的数据
•
必须关闭显示游标
•
游标
FOR
循环将隐含（或自动）打开和关闭显示游标并提取游标中的数据
•
游标属性可以用来确定游标是否是打开的以及游标所处理的行数（如果有）
1301
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
