oracle 游标实例

[size=small]oracle游标

游标-----内存中的一块区域,存放的是select的结果

游标用来处理从数据库中检索的多行记录（使用SELECT语句）。利用游标，程序可以逐个地处理和遍历一次检索返回的整个记录集。
为了处理SQL语句，Oracle将在内存中分配一个区域，这就是上下文区。这个区包含了已经处理完的行数、指向被分析语句的指针，游标就是指向上下文区句柄或指针。

缺点：游标使用时会对行加锁，可能会影响其他业务的正常进行。
而且，数据量大时其效率也较低。因为游标其实是相当于把磁盘数据整体放入了内存中，如果游标数据量大会造成内存不足，也有可能导致cpu高，所以，在数据量小时才使用游标。

优点：相当于程序中的FOR循环处理。一条一条的处理你读取的记录内容。
数据存放在内存中，数据量少的时候速度比较快。

一、显示游标
显示游标被用于处理返回多行数据的SELECT 语句，游标名通过CURSOR….IS 语句显示地赋给SELECT 语句。
在PL/SQL中处理显示游标所必需的四个步骤：
1）声明游标；CURSOR cursor_name IS select_statement
2）为查询打开游标；OPEN cursor_name
3）取得结果放入PL/SQL变量中；
FETCH cursor_name INTO list_of_variables;
FETCH cursor_name INTO PL/SQL_record;
4）关闭游标。CLOSE cursor_name
注意：在声明游标时，select_statement不能包含INTO子句。当使用显示游标时，INTO子句是FETCH语句的一部分。

二、隐式游标
所有的隐式游标都被假设为只返回一条记录。
使用隐式游标时，用户无需进行声明、打开及关闭。PL/SQL隐含地打开、处理，然后关掉游标。
例如：
SELECT studentNo,studentName INTO curStudentNo,curStudentName
FROM StudentRecord WHERE name=’gg’;
上述游标自动打开，并把相关值赋给对应变量，然后关闭。执行完后，PL/SQL变量curStudentNo,curStudentName中已经有了值。

单条sql语句所产生的结果集合
用关键字SQL表示隐式游标
4个属性 %rowcount 影响的记录的行数 整数
%found 影响到了记录 true
%notfound 没有影响到记录 true
%isopen 是否打开 布尔值 永远是false
多条sql语句 隐式游标SQL永远指的是最后一条sql语句的结果
主要使用在update 和 delete语句上

三、常见游标实例
(1)for循环游标
SQL> set timing on
SQL> set serverout on
SQL> set pagesize 1000
SQL> set linesize 400
SQL> declare
cursor c1 is select id,ins_time,random_string from mytest where random_id=50; ---定义游标
c1cur c1%rowtype; ---定义一个游标变量
begin
for c1cur in c1 loop ---for循环
dbms_output.put_line(c1cur.id||':'||c1cur.ins_time||'-'||c1cur.random_string);
end loop;
end;
/
113:2015-12-23 16:39:41-1TP6AU8U5JJ1O16FIUSZ
176:2015-12-23 16:40:44-N77Y47YGZQJH2ONG504R
187:2015-12-23 16:40:55-PIQG1HNPM6EVFZXD8N7G

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02


(2) fetch游标 --使用的时候 必须要明确的打开和关闭
declare
cursor c1 is select id,ins_time,random_id,random_string from mytest where random_id=50; ---定义显示游标
c1cur c1%rowtype; ---定义一个游标变量
begin
open c1; --打开游标
loop
fetch c1 into c1cur; ---取一行数据到游标变量
exit when c1%notfound; ---判断是否取到了值
dbms_output.put_line(c1cur.id||':'||c1cur.ins_time||'-'||c1cur.random_string);
end loop;
close c1; ---关闭游标
end;
/
113:2015-12-23 16:39:41-1TP6AU8U5JJ1O16FIUSZ
176:2015-12-23 16:40:44-N77Y47YGZQJH2ONG504R
187:2015-12-23 16:40:55-PIQG1HNPM6EVFZXD8N7G

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02

(3)参数游标(以下都利用scott自带的几张表测试）
SQL> declare
cursor c1 is select deptno from dept;
cursor c2(pno number,pjob varchar2) is select * from emp where deptno=pno and job=pjob; --只能指定类型,不能指定长度
c1cur c1%rowtype;
c2cur c2%rowtype;
begin
for c1cur in c1 loop
for c2cur in c2(c1cur.deptno,'MANAGER')loop
dbms_output.put_line(c1cur.deptno||':'||c2cur.ename||'---'||c2cur.sal);
end loop;
end loop;
end;
/
10:CLARK---2450
20:JONES---2975
30:BLAKE---2850

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02

(4)引用游标/动态游标
SQL>declare
type c1 is ref cursor; --定义一个类型(ref cursor)弱类型
type c2 is ref cursor return emp%rowtype; --定义一个强类型(返回的结果集有要求)
c0cur c1; ---定义一个弱类型的游标变量
c1cur emp%rowtype;
c2cur dept%rowtype;
begin
dbms_output.put_line('all employees :');
open c0cur for select * from emp;
loop
fetch c0cur into c1cur;
exit when c0cur%notfound;
dbms_output.put_line(c1cur.ename);
end loop;
dbms_output.put_line('all departments:');
open c0cur for select * from dept;
loop
fetch c0cur into c2cur;
exit when c0cur%notfound;
dbms_output.put_line(c2cur.dname);
end loop;
close c0cur;
end;
/
all employees:
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
all departments:
ACCOUNTING
RESEARCH
SALES
OPERATIONS

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02

(5)while循环
SQL> set timing on
SQL> set serverout on
SQL> set pagesize 1000
SQL> set linesize 400
SQL> declare
cursor c1 is select loc from dept;
c1cur c1%rowtype;
begin
open c1;
fetch c1 into c1cur;
while c1%found loop
dbms_output.put_line('location: '||c1cur.loc);
fetch c1 into c1cur; ---与for循环不同，while循环需要将下一行的值赋给游标变量，否则会进入死循环，并报错
end loop;
close c1;
end;
/
location: NEW YORK
location: DALLAS
location: CHICAGO
location: BOSTON

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10


死循环，报错：
SQL> declare
cursor c1 is select loc from dept;
c1cur c1%rowtype;
begin
open c1;
fetch c1 into c1cur;
while c1%found loop
dbms_output.put_line('location: '||c1cur.loc);
end loop;
close c1;
end;
/
location: NEW YORK
location: NEW YORK
location: NEW YORK
location: NEW YORK
location: NEW YORK
declare
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi,pl/sql vc2)

执行时，单个进程就导致cpu 100%，因此游标还是要小心使用的！


(6)利用游标来更新（update）
6.1 声明更新显示游标：
Cursor 游标名IS SELECT 语句 For Update [ Of 更新列列名]；
6.2 使用显示游标当前记录来更新：
Update 表名 SET 更新语句 Where Current Of 游标名；

1.所有人普调
SQL> declare
cursor c1 is select * from emp for update of sal;
c1cur c1%rowtype;
saladd emp.sal%type;
salnew emp.sal%type;
begin
for c1cur in c1 loop
saladd:=c1cur.sal*0.2; --所有员工加薪20%
if saladd<300 then
salnew:=c1cur.sal+300; --加薪不足300的，按300加
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
else
salnew:=c1cur.sal+saladd;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
end if;
update emp set sal=salnew where current of c1;
end loop;
end ;
/
SMITH:new salary 1300 ---原来工资1000
ALLEN:new salary 2304
WARD:new salary 1800
JONES:new salary 4284
MARTIN:new salary 1800
BLAKE:new salary 4104
CLARK:new salary 3528
SCOTT:new salary 4320
KING:new salary 7200
TURNER:new salary 2160
ADAMS:new salary 1620
JAMES:new salary 1450
FORD:new salary 4320
MILLER:new salary 1872

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04

2.按照部门来调整
declare
cursor c1 is select * from emp for update of sal;
c1cur c1%rowtype;
salnew emp.sal%type;
begin
for c1cur in c1 loop
case ---必须包含所有的分类，否则会报错ORA-06592: CASE not found while executing CASE statement
when c1cur.deptno=10
then salnew:=c1cur.sal*1.1;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
when c1cur.deptno=20
then salnew:=c1cur.sal*1.15;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
when c1cur.deptno=30
then salnew:=c1cur.sal*1.2;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
when c1cur.deptno=40
then salnew:=c1cur.sal*1.3;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
end case;
update emp set sal=salnew where current of c1;
end loop;
end;
/

SMITH:new salary 1495
ALLEN:new salary 2764.8
WARD:new salary 2160
JONES:new salary 4926.6
MARTIN:new salary 2160
BLAKE:new salary 4924.8
CLARK:new salary 3880.8
SCOTT:new salary 4968
KING:new salary 7920
TURNER:new salary 2592
ADAMS:new salary 1863
JAMES:new salary 1740
FORD:new salary 4968
MILLER:new salary 2059.2
LITING:new salary 7800

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07

3.符合if条件的
declare
cursor c1 is select * from emp for update of sal;
c1cur c1%rowtype;
salnew emp.sal%type;
begin
for c1cur in c1 loop
if c1cur.deptno=20 then
salnew:=c1cur.sal*5-8000;
dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
else
salnew:=c1cur.sal;
end if;
update emp set sal=salnew where current of c1;
end loop;
end;
/
SMITH:new salary 1525
JONES:new salary 6633
SCOTT:new salary 6840
ADAMS:new salary 1315
FORD:new salary 6840

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.05


4.以平均值为分界
SQL> select avg(sal) from emp; 平均工资3715
AVG(SAL)
----------
3715

SQL> declare
2 cursor c1 is select empno,ename,sal,deptno,avg(sal) over(partition by deptno) as depavg from emp for update of sal;
3 c1cur c1%rowtype;
4 salnew emp.sal%type;
5 begin
6 for c1cur in c1 loop
7 if c1cur.sal>c1cur.depavg then
8 salnew:=c1cur.sal+50; ---高于平均工资的奖金发50
9 dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
10 else
11 salnew:=c1cur.sal+100; ---低于平均工资的奖金发100
12 dbms_output.put_line(c1cur.ename||':'||'new salary '||salnew);
13 end if;
14 update emp set sal=salnew where current of c1;
15 end loop;
16 end;
17 /
CLARK:new salary 3980
MILLER:new salary 2160
KING:new salary 7970
JONES:new salary 4976
SMITH:new salary 1595
SCOTT:new salary 5018
FORD:new salary 5071
ADAMS:new salary 1415
WARD:new salary 2260
TURNER:new salary 2692
ALLEN:new salary 2814
JAMES:new salary 1840
MARTIN:new salary 2260
BLAKE:new salary 4974
LITING:new salary 7900

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.47


(7)利用游标来删除（delete）
7.1 声明删除显示游标：
Cursor 游标名IS SELECT 语句 For Delete;
7.2 使用显示游标当前记录来更新或删除：
Delete From 表名 Where Current Of 游标名;

SQL> create table emp1 as select * from emp;
Table created.
Elapsed: 00:00:03.05

SQL> declare
cursor c1 is select ename,job from emp1 for update;
e_job emp1.job%type;
e_name emp1.ename%type;
begin
open c1;
loop
fetch c1 into e_name,e_job; --顺序要跟上面的select顺序对应
exit when c1%notfound;
if e_job='ANALYST' then
delete from emp1 where current of c1;
dbms_output.put_line(e_name||':'||'deleted');
end if;
end loop;
close c1;
end;
/
SCOTT:deleted
FORD:deleted

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09

(8)利用游标来插入
SQL> declare
2 cursor c1 is select * from emp1;
3 c1cur c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into c1cur;
8 exit when c1%notfound;
9 insert into emp values c1cur;
10 dbms_output.put_line(c1cur.ename||': inserted');
11 end loop;
12 close c1;
13 end;
14 /
TINA: inserted
BOBO: inserted
LIO: inserted

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.11

SQL> select * from emp; ---查看数据，插入成功

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1595 20
7499 ALLEN SALESMAN 7698 20-FEB-81 2814 300 30
7521 WARD SALESMAN 7698 22-FEB-81 2260 500 30
7566 JONES MANAGER 7839 02-APR-81 4976 20
7654 MARTIN SALESMAN 7698 28-SEP-81 2260 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 4974 30
7782 CLARK MANAGER 7839 09-JUN-81 3980 10
7788 SCOTT ANALYST 7566 19-APR-87 5018 20
7839 KING PRESIDENT 17-NOV-81 7970 10
7844 TURNER SALESMAN 7698 08-SEP-81 2692 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1415 20
7900 JAMES CLERK 7698 03-DEC-81 1840 30
7902 FORD ANALYST 7566 03-DEC-81 5071 20
7934 MILLER CLERK 7782 23-JAN-82 2160 10
7200 LITING CLERK 7788 03-DEC-81 7900 500 40
6000 TINA CLERK 7788 02-APR-86 4503 300 10
6221 BOBO CLERK 7788 02-APR-86 4000 200 10
6307 LIO CLERK 7788 02-APR-86 4409 0 10[/size]