alexmen

专注.net软件开发,项目管理体系PMBOK.

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

显示游标

显示游标包括四种属性:

%ISOPEN :检测游标是否已经打开

%FOUND :检测游标结果集是否存在数据,存在则返回TRUE

%NOTFOUND :检测游标结果集是否不存在数据,不存在则返回TRUE

%ROWCOUNT :返回已提取的实际行数

使用显示游标

定义游标:CURSOR cursor_name IS select_statement;

打开游标:OPEN cursor_name;

提取数据:FETCH cursor_name INTO variable1[,variable2,…];

FETCH INTO每次只能提取一行数据,批量数据需使用循环

使用游标变量接受数据:

SQL> declare

2 cursor emp_cursor is

3 select ename,job,sal from emp where deptno=&dno;

4 vname emp.ename%type;

5 vsal emp.sal%type;

6 vjob emp.job%type;

7 begin Oracle 学习笔记 第 74 页 共 90 页

8 open emp_cursor;

9 loop

10 fetch emp_cursor into vname,vjob,vsal;

11 exit when emp_cursor%notfound;

12 dbms_output.put_line('姓名:'||vname||',岗位:'||vjob||',工资:'||vsal);

13 end loop;

14 close emp_cursor;

15 end;

16 /

输入 dno 的值: 30

姓名:ALLEN,岗位:SALESMAN,工资:1600

姓名:WARD,岗位:SALESMAN,工资:1250

姓名:JONES,岗位:MANAGER,工资:3272.5

PL/SQL 过程已成功完成。

使用PL/SQL记录变量接受游标数据:简化单行数据处理

SQL> declare

2 cursor ecur is select ename,sal from emp order by sal desc;

3 erec ecur%rowtype;

4 begin

5 open ecur;

6 loop

7 fetch ecur into erec;

8 exit when ecur%notfound or ecur%rowcount>&n;

9 dbms_output.put_line('姓名:'||erec.ename||',工资:'||erec.sal);

10 end loop;

11 close ecur;

12 end;

13 /

输入 n 的值: 5

姓名:KING,工资:5000

姓名:FORD,工资:3300

姓名:JONES,工资:3272.5

姓名:BLAKE,工资:2850

姓名:MARY,工资:2000

PL/SQL 过程已成功完成。

使用PL/SQL集合变量接受游标数据:简化多行多列数据处理

SQL> declare

2 cursor ec is select ename,sal from emp where lower(job)=lower('&job');

3 type etype is table of ec%rowtype index by binary_integer;

4 et etype;

5 i int;

6 begin

7 open ec;

8 loop Oracle 学习笔记 第 75 页 共 90 页

9 i:=ec%rowcount+1;

10 fetch ec into et(i);

11 exit when ec%notfound;

12 dbms_output.put_line('姓名:'||et(i).ename||',工资:'||et(i).sal);

13 end loop;

14 close ec;

15 end;

16 /

输入 job 的值: manager

姓名:JONES,工资:3272.5

姓名:BLAKE,工资:2850

姓名:CLARK,工资:1500

PL/SQL 过程已成功完成。

游标FOR循环

使用游标for循环时,oracle会隐含的打开游标,提取数据并关闭游标

在游标for循环中引用已定义游标:

SQL> declare

2 cursor ec is select ename,hiredate from emp order by hiredate desc;

3 begin

4 for erec in ec loop

5 dbms_output.put_line('姓名:'||erec.ename||',工作日期:'||erec.hiredate);

6 exit when ec%rowcount=&n;

7 end loop;

8 end;

9 /

输入 n 的值: 3

姓名:MARY,工作日期:

姓名:ADAMS,工作日期:23-5月 -87

姓名:SCOTT,工作日期:01-1月 -84

PL/SQL 过程已成功完成。

在游标for循环中直接引用子查询:

SQL> begin

2 for erec in (select ename,hiredate,rownum from emp order by hiredate) loop

3 dbms_output.put_line('姓名:'||erec.ename||',工作日期:'||erec.hiredate);

4 exit when erec.rownum=&n;end loop;

5 end;

6 /

输入 n 的值: 2

姓名:ALLEN,工作日期:20-2月 -81

姓名:WARD,工作日期:22-2月 -81

PL/SQL 过程已成功完成。

参数游标:参数只能指定数据类型,不能指定长度,而且必须在where子句中引用参数

SQL> declare Oracle 学习笔记 第 76 页 共 90 页

2 cursor ec(dno number) is select ename,job from emp where deptno=dno;

3 begin

4 for erec in ec(&dno) loop

5 dbms_output.put_line('姓名:'||erec.ename||',岗位:'||erec.job);

6 end loop;

7 end;

8 /

输入 dno 的值: 30

姓名:ALLEN,岗位:SALESMAN

姓名:WARD,岗位:SALESMAN

姓名:JONES,岗位:MANAGER

PL/SQL 过程已成功完成。

更新游标行

declare

cursor emp_cursor is select ename,sal,deptno from emp for update;

dno int:=&no;

begin

for emp_record in emp_cursor loop

if emp_record.deptno=dno then

dbms_output.put_line(‘姓名:’||emp_record.ename||’,原工资:’||emp_record.sal);

update emp set sal=sal*1.1 where current of emp_cursor;

end if;

end loop;

end;

/

删除游标行

declare

cursor emp_cursor is select ename from emp for update;

name varchar2(10):=lower(‘&name’);

begin

for emp_record in emp_cursor loop

if lower(emp_record.ename)=name then

delete from emp where current of emp_cursor;

else

dbms_output.put_line(‘姓名:’||emp_record.ename);

end if;

end loop;

end;

/

使用for子句在特定表上加共享锁(涉及多张表时的同步问题)

SQL> declare

2 cursor emp_cursor is Oracle 学习笔记第 77 页 共 90 页

select a.dname,b.ename from dept a JOIN emp b ON a.deprno=b.deptno;

3 name varchar2(10):=lower('&name');

4 begin

5 for emp_record in emp_cursor loop

6 if lower(emp_record.dname)=name then

7 dbms_output.put_line(‘姓名:’||emp_record.ename);

8 delete from emp where current of emp_cursor;

9 end if;

10 end loop;

11 end;

12 /

输入 name 的值: sales

PL/SQL过程已完成。

游标变量

游标变量是基于REF CURSOR类型所定义的变量,它实际上是指向内存地址的指针。使用显式游标只能定义静态游标,而通过使用游标变量可以在打开游标时指定其对应的select语句,从而实现动态游标。

使用无返回类型的游标变量

SQL> set serveroutput on

SQL> set verify off

SQL> declare

2 type ref_cursor_type is ref cursor;

3 rc ref_cursor_type;

4 v1 number(6);

5 v2 varchar2(10);

6 begin

7 open rc for

8 select &col1 col1,&col2 col2 from &table where &cond;

9 loop

10 fetch rc into v1,v2;

11 exit when rc%notfound;

12 dbms_output.put_line('col1= '||v1||',col2= '||v2);

13 end loop;

14 close rc;

15 end;

16 /

输入 col1 的值: empno

输入 col2 的值: ename

输入 table 的值: emp

输入 cond 的值: deptno=10

col1= 7782,col2= CLARK

col1= 7839,col2= KING

col1= 7934,col2= MILLER

PL/SQL 过程已成功完成。 Oracle 学习笔记 第 78 页 共 90 页

使用有返回类型的游标变量

SQL> declare

2 type emp_cursor_type is ref cursor return emp%rowtype;

3 ec emp_cursor_type;

4 er emp%rowtype;

5 begin

6 open ec for select * from emp where deptno=&dno;

7 loop

8 fetch ec into er;

9 exit when ec%notfound;

10 dbms_output.put_line('姓名:'||er.ename||',工资:'||er.sal);

11 end loop;

12 close ec;

13 end;

14 /

输入 dno 的值: 20

姓名:SMITH,工资:800

姓名:JONES,工资:2975

姓名:SCOTT,工资:3000

姓名:ADAMS,工资:1100

姓名:FORD,工资:3000

PL/SQL 过程已成功完成。

使用批量提取

使用fetch…bulk collect提取所有数据;

SQL> declare

2 cursor ec is

3 select * from emp where lower(job)=lower('&job');

4 type etype is table of emp%rowtype;

5 et etype;

6 begin

7 open ec;

8 fetch ec bulk collect into et;

9 close ec;

10 for i in 1..et.count loop

11 dbms_output.put_line('姓名:'||et(i).ename||',工资:'||et(i).sal);

12 end loop;

13 end;

14 /

输入 job 的值: clerk

姓名:SMITH,工资:800

姓名:ADAMS,工资:1100

姓名:JAMES,工资:950

姓名:MILLER,工资:1300

PL/SQL 过程已成功完成。 Oracle 学习笔记 第 79 页 共 90 页

使用LIMIT子句限制提取行数

SQL> declare

2 cursor ec is select * from emp;

3 type emp_array_type is varray(5) of emp%rowtype;

4 ea emp_array_type;

5 begin

6 open ec;

7 loop

8 fetch ec bulk collect into ea limit &rows;

9 for i in 1..ea.count loop

10 dbms_output.put_line('姓名:'||ea(i).ename||',工资:'||ea(i).sal);

11 end loop;

12 exit when ec%notfound;

13 end loop;

14 close ec;

15 end;

16 /

输入 rows 的值: 4

姓名:SMITH,工资:800

姓名:ALLEN,工资:1600

姓名:WARD,工资:1250

姓名:JONES,工资:2975

姓名:MARTIN,工资:1250

姓名:BLAKE,工资:2850

姓名:CLARK,工资:2450

姓名:SCOTT,工资:3000

姓名:KING,工资:5000

姓名:TURNER,工资:1500

姓名:ADAMS,工资:1100

姓名:JAMES,工资:950

姓名:FORD,工资:3000

姓名:MILLER,工资:1300

PL/SQL 过程已成功完成。

使用cursor表达式

SQL> declare

2 cursor dept_cursor(no number) is

3 select a.dname,cursor(select * from emp where deptno=a.deptno)

4 from dept a where a.deptno=no;

5 type ref_cursor_type is ref cursor;

6 ec ref_cursor_type;

7 er emp%rowtype;

8 vdname dept.dname%type;

9 begin

10 open dept_cursor(&dno); Oracle 学习笔记第 80 页 共 90 页

11 loop

12 fetch dept_cursor into vdname,ec;

13 exit when dept_cursor%notfound;

14 dbms_output.put_line('部门名:'||vdname);

15 loop

16 fetch ec into er;

17 exit when ec%notfound;

18 dbms_output.put_line('----雇员名:'||er.ename||',岗位:'||er.job);

19 end loop;

20 end loop;

21 close dept_cursor;

22 end;

23 /

输入 dno 的值: 10

部门名:ACCOUNTING

----雇员名:CLARK,岗位:MANAGER

----雇员名:KING,岗位:PRESIDENT

----雇员名:MILLER,岗位:CLERK

PL/SQL 过程已成功完成。

posted on 2011-05-25 11:01  alexmen  阅读(314)  评论(0编辑  收藏  举报