原帖由 fly-in-the-sky 于 2007-11-22 13:47 发表
以下内容是自己在平时收集的一些关于游标的內容,现帖出来,希望对大家可能有所帮助。其中如果有值得商榷的地方,还请大家指正。
以前在论坛里面看游标的內容比较零星,不够全面,所以在这里也想起到一个抛砖引玉的作用,看大家还有什么要补充的地方,可以跟帖完善之,共同学习!共同进步!谢谢!
游标
在Oracle9i之前,使用FETCH语句每次只能提取一行数据;从Oracle9i开始,通过使用FETCH…BULK COLLECT INTO语句,每次可以提取多行数据。语法如下:
(1) FETCH cursor_name INTO variable1,variable2,…;
此方法必须要使用循环语句处理结果集的所有数据。
(2) FETCH cursor_name BULK COLLECT INTO collect1,collect2,…[LIMIT rows]
[LIMIT rows]可用来限制每次游标每次提取的行数。
基于游标定义记录变量
使用%ROWTYPE属性不紧可以基于表和视图定义记录变量,也可以基于游标定义记录变量。当基于游标定义记录变量时,记录成员名实际就是SELECT语句的列名和列别名。为了简化显示游标的数据处理,建議开发人员使用记录变量存放游标数据。
For example:
DECLARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp;
emp_reocrd emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_record%NOTFOUND;
dbms_ouput.put_line(‘雇员名:’||emp_record.ename||’,雇员工资:’||emp_record.sal);
END LOOP;
CLOSE emp_cursor;
END;
参数游标
Defined: CURSOR cursor_name(parameter_name datatype) IS select_statement;
在定义参数游标时,游标参数只能指定数据类型,而不能指定长度.另外,定义参数游标时,一定要在游标子查询的WHERE子句中引用该参数,否则失去了定义参数游标的意义.
For example:
CURSOR emp_cursor(no number) IS SELECT ename FROM emp WHERE deptno=no;
当使用显示游标属性时,必须要在显式游标属性之前带有显式游标名作为前缀(游标名.属性名).(属性主要指:%FOUND,NOTFOUND,%ROWCOUNT,%ISOPEN)
使用游标更新或者删除数据
Syntax:
CUROSR cursor_name(parameter_name datatype)
IS select_statement
FOR UPDATE[OF column_reference] [NOWAIT];
FOR UPDATE 子句用于在游标结果集数据上加行共享锁,以防止其它用户在相应行上执行DML操作;当SELECT 语句引用到多张表时,使用OF子句可以确定哪些表(或者是哪些表里的哪些字段)要加锁,如果没有OF子句,则会在SELECT语句所引用的全部表上加锁。
NOWAIT子句用于指定不等待锁。使用FOR UPDATE 语句对被作用行加锁,如果其它会话已经在被作用行上加锁,那么在默认情况下当前会话要一直等待对方释放锁。通过在FOR UPDATE子句指定NOWAIT子句,可以避免等待锁。当指定NOWAIT子句拉,如果其它会话已经在被作用行加锁,那么当前会话会显示错误提示信息,并退出PL/SQL块。
在提取了游标数据之后,为了更新或者删除当前游标数据,必须在UPDATE或DELETE语句中引用WHERE CURRENT OF 子句。语法如下:
UPDATE table_name SET column=.. WHERE CURRENT OF cursor_name;
DELETE table_name WHERE CURRENT OF cursor_name;
游标FOR循环
游标FOR循环简化了对游标的处理,当使用游标FOR循环时,Oracle会隐含地打开游标、提取游标数据并关闭游标。为了简化程序代码,建议大家使用游标FOR循环。语法如下:
FOR record_name IN cursor_name LOOP
Statement1;
Statement2;
…
END LOOP;
record_name是Oracle隐含定义的记录变量名(不用在定义部分定义,直接使用即可)。
如果在使用游标FOR循环时不需要使用任何游标属性,那么直接在游标FOR循环中使用子查询。语法如下:
FOR record_name IN (SELECT column1,column2…FROM…[WHERE …]) LOOP
Statement1;
Statement2;
…
END LOOP;
游標變量(REF CURSOR 游標或者動態游標)
語法:
首先定義REF CURSOR類型
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
然后定義游標變量
Cursor_variable ref_type_name;
當指定RETURN 子句時,其數据類型必須是記錄類型;另外,不能在包內定義游標變量。
动态游标,在运行的时候才能确定游标使用的查询。分类:
强类型(限制)REF CURSOR,规定返回类型
弱类型(非限制)REF CURSOR,不规定返回类型,可以获取任何结果集。
打開游標:
為了引用該游標變量,在打開游標時需要指定其所對應的SELECT語句。當打開游標變量時,會執行游標變量所對應的SELECT語句,并將語句結果存放到游標結果擊集中。語法如下:
OPEN cursor_variable FOR select_statement;
强类型举例:
declare
--声明记录类型
type emp_job_rec is record(
employee_id number,
employee_name varchar2(50),
job_title varchar2(30)
);
--声明REF CURSOR,返回值为该记录类型
type emp_job_refcur_type is ref cursor return emp_job_rec;
--定义REF CURSOR游标的变量
emp_refcur emp_job_refcur_type;
emp_job emp_job_rec; ──定义记录类型的变量
begin
open emp_refcur for
select e.employee_id,
e.first_name || ' ' ||e.last_name "employee_name",
j.job_title
from employees e, jobs j
where e.job_id = j.job_id and rownum < 11 order by 1;
fetch emp_refcur into emp_job;
while emp_refcur%found loop
dbms_output.put_line(emp_job.employee_name || '''s job is ');
dbms_output.put_line(emp_job.job_title);
fetch emp_refcur into emp_job;
end loop;
end;
fly-in-the-sky 发表于 2007-11-22 13:52
用REF CURSOR实现BULK功能
1. To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL sta ...
欢迎光临 ITPUB论坛-专业的IT技术社区 (http://www.itpub.net/) | Powered by Discuz! X3.2 |