bokee.net

软件工程师博客

正文 更多文章

ORACLE基础(11)--使用显式游标处理查询

什么是游标?

服务器执行的每个SQL 语句都有一个与其相关联的单独的游标。
两种类型:
隐式游标:为所有的DML 和PL/SQL SELECT 语句声明。
显式游标:由程序员声明和命名。

显式游标的功能

一行一行地处理查询返回的多行结果。
跟踪当前正在处理的行。
在PL/SQL 块中手动控制游标

声明游标:语法

定义参数,允许替换值进入游标查询。

DECLARE
CURSOR cursor_name IS
select_statement;

在游标声明中,不要包含INTO 子句。

声明游标:例子

逐行获取一个订单的行记录元素:

DECLARE
. . .
v_ord_id s_item.ord_id%TYPE;
v_product_id s_item.product_id%TYPE;
v_item_total NUMBER (11,2);
CURSOR item_cursor IS
SELECT product_id, price*quantity
FROM s_item
WHERE ord_id = v_ord_id;
BEGIN
. . .
打开游标:语法

打开游标以执行查询和标记活动集合

OPEN cursor_name;

如果查询没有返回记录,不会引起异常。
在一次提取之后,通过使用游标属性进行测试状态。


从游标获取数据:语法

检索当前记录的值到输出变量中。

FETCH cursor_name INTO variable1, variable2,...;

包含同样数目的变量。
按照位置,把每个变量和列相对应。
测试,查看游标是否包含行。

从游标获取数据:例子

逐行获取某个订单的行元素:

FETCH item_cursor
INTO v_product_id, v_item_total;

关闭游标:语法

在完成对行记录的处理之后,关闭游标。

CLOSE cursor_name;

如果需要,要重新打开游标。
一旦游标已经关闭,不要试图提取数据

控制多行提取

使用循环从一个显式游标中处理多行。
每次重复,提取一行。
通过使用%NOTFOUND属性,为不成功提取编写一个测试。
使用显式游标属性测试每次提取是否成功。

%ISOPEN 属性:例子

只有在游标打开时,才能提取行。
在执行提取之前,使用%ISOPEN 游标属性测试游标是否是打开的状态。

IF item_cursor%ISOPEN THEN
FETCH item_cursor INTO v_quantity, v_price;
ELSE
OPEN item_cursor;
END IF;
%NOTFOUND 和%ROWCOUNT 属性:例子

使用%ROWCOUNT 游标属性,获取准确的行数目。
使用%NOTFOUND 游标属性,确定何时退出循环。

LOOP
FETCH item_cursor
INTO v_product_id, v_item_total;
EXIT WHEN item_cursor%ROWCOUNT > 5
OR item_cursor%NOTFOUND;
v_order_total := v_order_total + v_item_total;
. . .
END LOOP;


游标和记录:例子

把提取出来的数据存入PL/SQL RECORD变量,方便了对活动行集合的处理。

CURSOR emp_cursor IS
SELECT id, salary, start_date, rowid
FROM s_emp
WHERE dept_id = 41;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
. . .
FETCH emp_cursor INTO emp_record;

带参数的游标:语法

当游标打开,正在执行查询时,传递参数给游标。
CURSOR cursor_name
[(parameter_name datatype, ...)]
IS
select_statement;

使用不同的活动集合多次打开显式游标。

带参数的游标:例子

传递部门编号和工作标题给WHERE 子句。

CURSOR emp_cursor
(v_dept NUMBER, v_job VARCHAR2) IS
SELECT last_name, salary, start_date
FROM s_emp
WHERE dept_id = v_dept
AND title = v_job;


游标FOR 循环:语法

处理显式游标的捷径。
隐式地发生打开、提取和关闭的过程。

FOR record_name IN cursor_name LOOP
statement1;
statement2;
. . .
END LOOP;
不要声明记录;它会隐式声明

游标FOR 循环:例子

逐行获取某个订单的行记录,直到没有行记录为止。

FOR item_record IN item_cursor LOOP
-- implicit open and implicit fetch occur
v_order_total := v_order_total +
(item_record.price * item_record.quantity);
i := i + 1;
product_id_table (i) := item_record.product_id;
order_total_table (i) := v_order_total;
END LOOP; -- implicit close occurs

WHERE CURRENT OF 子句

使用游标更新或删除当前行。
通过在游标查询中包含FOR UPDATE 子句, 首先锁定行。
通过使用WHERE CURRENT OF 子句,从显式的游标中参考当前行。
如果使用FOR UPDATE ,在从显式游标中提取记录时,不要提交。
SELECT...FROM...FOR UPDATE [OF column-reference][NOWAIT]

WHERE CURRENT OF 子句:例子

根据游标中的标准更新行。

...
CURSOR emp_cursor IS
SELECT ...
FOR UPDATE;
BEGIN
...
FOR emp_record IN emp_cursor LOOP
UPDATE ...
WHERE CURRENT OF emp_cursor;
...
END LOOP;
COMMIT;
END;

分享到:

上一篇:ORACLE基础(10)--在PL

下一篇:潘石屹能否拯救中国楼市?

评论 (0条) 发表评论

抢沙发,第一个发表评论
验证码