ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 游标小记(收集汇总)

游标小记(收集汇总)

原创 Linux操作系统 作者:spritesong 时间:2009-03-29 22:05:41 0 删除 编辑
游标小记(收集汇总)

以下内容是自己在平时收集的一些关于游标的內容,现帖出来,希望对大家可能有所帮助。其中如果有值得商榷的地方,还请大家指正。
以前在论坛里面看游标的內容比较零星,不够全面,所以在这里也想起到一个抛砖引玉的作用,看大家还有什么要补充的地方,可以跟帖完善之,共同学习!共同进步!谢谢!



游标
在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;

用REF CURSOR实现BULK功能
1. To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PL/SQL FORALL statement instead of a loop construct.
2. To speed up SELECT statements, include the BULK COLLECT INTO clause in the SELECT statement instead of using INTO.


SQL> create table tab2 as select empno ID, ename NAME, sal SALARY from emp where 1=2;
Table created

-------------------------------------------------------
create or replace procedure REF_BULK is
type empcurtyp is ref cursor;
type idlist is table of emp.empno%type;
type namelist is table of emp.ename%type;
type sallist is table of emp.sal%type;

emp_cv empcurtyp;
ids idlist;
names namelist;
sals sallist;

row_cnt number;
begin
open emp_cv for
select empno, ename, sal from emp;
fetch emp_cv BULK COLLECT
INTO ids, names, sals; --BULK COLLECT INTO instead of INTO
close emp_cv;

for i in ids.first .. ids.last loop
dbms_output.put_line('id=' || ids(i) || ' name=' || names(i) ||
' salary=' || sals(i));
end loop;

forall i in ids.first .. ids.last --FORALL instead of FOR ...LOOP
insert into tab2 values (ids(i), names(i), sals(i));
commit;

select count(*) into row_cnt from tab2;
dbms_output.put_line('-----------------------------------');
dbms_output.put_line('The row number of tab2 is ' || row_cnt);
end REF_BULK;

------------------------------------------------------------

执行:

SQL> exec ref_bulk;

id=7369 name=SMITH salary=800
id=7499 name=ALLEN salary=1600
id=7521 name=WARD salary=1250
id=7566 name=JONES salary=2975
id=7654 name=MARTIN salary=1250
id=7698 name=BLAKE salary=2850
id=7782 name=CLARK salary=2450
id=7788 name=SCOTT salary=3000
id=7839 name=KING salary=5000
id=7844 name=TURNER salary=1500
id=7876 name=ADAMS salary=1100
id=7900 name=JAMES salary=950
id=7902 name=FORD salary=3000
id=7934 name=MILLER salary=1300
-----------------------------------
The row number of tab2 is 14
PL/SQL procedure successfully completed


使用CURSOR表达式(嵌套游标)
从Oracle9i开始,结果集不仅可以包含普通数据,而且允许包含嵌套游标的数据。使用CURSOR的表达式的语法如下:
      CURSOR(subquery)
通过使用CURSOR表达式,开发人员可以在PL/SQL块中处理更加复杂的基于多张表的关联数据。为了在PL/SQL块中取得嵌套游标的数据,需要使用嵌套循环。
For instance:
declare
  type refcursor is ref cursor;
  cursor dept_cursor(no number) is
    select a.dname,cursor(select ename,sal from emp where deptno=a.deptno)      
    from dept a
    where a.deptno=no;
  empcur refcursor;
  v_dname dept.dname%type;
  v_ename emp.ename%type;
  v_sal   emp.sal%type;
begin
  open dept_cursor(&no);
  loop
     fetch dept_cursor into v_dname,empcur;
     exit when dept_cursor%notfound;
     dbms_output.put_line('部门名:'||v_dname);
     loop
        fetch empcur into v_ename,v_sal;
        exit when empcur%notfound;
        dbms_output.put_line('雇员名:'||v_ename||',工资:'||v_sal);
     end loop;
  end loop;
  close dept_cursor;      
end;

output:
      部门名:ACCOUNTING
雇员名:CLARK,工资:2450
雇员名:KING,工资:5000
雇员名:MILLER,工资:1300

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/293106/viewspace-580554/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-28

  • 博文量
    62
  • 访问量
    23763