为什么要游标:

先看这个:

DECLARE  v_empno emp.empno%type; 
v_ename emp.ename%type;
BEGIN
SELECT empno,ename
into v_ename,v_empno
FROM emp;
dbms_output.put_line(v_empno||' '||v_ename);
END;

这个SELECT语句可能返回多条记录,所以这个赋值语句可能是错误的。所以游标可以发挥作用了。


什么是游标?

 游标(cursor)是Oracle系统在内存中开辟的一个工作区,在其中存放SELECT语句返回的查询结果。 

游标的分类:

隐式游标:PL/SQL隐式建立并自动管理这一游标。隐式游标也叫做SQL游标。 对于SQL游标,不能对其显式地执行                     OPEN、CLOSE和FETCH语句,但是可以使用游标属性从最近执行的SQL语句中获取信息。

显式游标:由程序员显式说明及控制,用于从表中取出多行数据,并将多行数据一行一行单独处理。


隐式游标:


  • 由Oracle在内部声明 
  • 由Oracle自行管理游标 
  • 可以使用游标属性从最近执行的SQL语句中获取信息 
  • 用于处理DML语句以及返回单行的查询

隐式游标属性:



隐式游标举例:

VARIABLE rows_deleted VARCHAR2(30)
DECLARE
v_deptno NUMBER := 20;
BEGIN
DELETE FROM emp
WHERE deptno = v_deptno;
:rows_deleted := (SQL%ROWCOUNT ||' rows deleted.');
END;
/


返回所删除行的数量



显式游标:由用户显式声明,查询返回多行记录



  • 使用游标时,select语句查询的结果可以是单条记录,多条记录,也可以是零条记录。
  • 游标工作区中,存在着一个指针(POINTER),在初始状态它指向查询结果的首记录。
  • 要访问查询结果的所有记录,可以通过FETCH语句,进行指针的移动来实现。
  • 使用游标进行操作,包括定义游标、打开游标、提取数据以及关闭游标几步。



声明:命名游标,定义在游标中执行查询的结构

打开:OPEN 语句执行查询并绑定每一个引用到的变量。满足查询条件的行称为活动集(activeset )。

获取:通过游标从活动集中获得数据,在幻灯片的流程图中,每一次提取之后都要测试游标中是否还有当前行。如果没有要处理的行,就可以关闭游标了。

关闭:CLOSE 语句释放活动集中的所有行。这之后,就可以重新打开这个游标来创建一个新的活动集。


游标声明:

DECLARE  CURSOR emp_cursor IS 
SELECT empno, ename
FROM emp;

CURSOR dept_cursor IS
SELECT *
FROM dept
WHERE deptno = 10;
BEGIN
...

DECLARE
v_empno emp.empno%type;
v_ename emp.ename%type;
CURSOR emp_cursor IS
SELECT empno,ename FROM emp;
BEGIN
OPEN emp_cursor;
FOR i IN 1..5 LOOP
FETCH emp_cursor INTO v_ename,v_empno;
dbms_output.put_line(v_empno||' '||v_ename);
END LOOP;
CLOSE emp_cursor;
END;

显示游标属性:



%ISOPEN属性


仅当游标处于打开状态时才可以从中提取数据。

在执行提取操作之前,使用%ISOPEN 游标属性,检测游标是否已被打开

declare   v_empno     emp.empno%type;
v_ename emp.ename%type;
cursor emp_cursor is
select empno,ename from emp;

begin
if not emp_cursor%isopen then
open emp_cursor;
end if;
loop
fetch emp_cursor into v_empno,v_ename;
exit when emp_cursor%notfound;
dbms_output.put_line(v_empno||' '||v_ename);
end loop;
dbms_output.put_line(emp_cursor%rowcount);
close emp_cursor;
end;

将提取的行值存入一个PL/SQL RECORD 中能方便地处理活动集中的行。

declare      cursor emp_cursor is
select empno,ename from emp;

begin
for emp_record in emp_cursor loop
if emp_record.ename = 'SCOTT' then
dbms_output.put_line(emp_record.empno);
end if;
end loop;
end;

带有参数的游标:

DECLARE 
CURSOR emp_cursor
(p_deptno number,p_sal number)
IS
SELECT ename FROM emp
WHERE deptno= p_deptno and sal>p_sal ;
BEGIN
OPEN emp_cursor(10,2000);
….
CLOSE emp_cursor;
OPEN emp_cursor(20,4000);
….
END;

FORUPDATE 子句


SELECT ... FROM ... FOR UPDATE [OF column_reference][NOWAIT];

在事务执行期间可以显式锁定以拒绝访问。

在更新或删除行时要锁定该行。

DECLARE
CURSOR emp_cursor IS
SELECT empno, ename, sal
FROM emp
WHERE deptno = 30
FOR UPDATE OF sal NOWAIT;


WHERECURRENT OF 子句


更新或删除游标中的当前行数据


WHERE CURRENT OF cursor ;

首先要在游标中使用FOR UPDATE 子句锁定行

使用 WHERE CURRENT OF 子句从显式游标中引用当前行

DECLARE
CURSOR sal_cursor IS
SELECT sal
FROM emp
WHERE deptno = 30
FOR UPDATE OF sal NOWAIT;
BEGIN
FOR emp_record IN sal_cursor LOOP
UPDATE emp
SET sal = emp_record.sal * 1.10
WHERE CURRENT OF sal_cursor;
END LOOP;
--COMMIT;
END;


使用子查询的游标

DECLARE
CURSOR my_cursor IS
SELECT t1.deptno, t1.dname, t2.STAFF
FROM dept t1, (SELECT deptno,
count(*) STAFF
FROM emp
GROUP BY deptno) t2
WHERE t1.deptno = t2.deptno
AND t2.STAFF >= 5;

子查询就是在别的SQL数据处理语句中的查询语句,通常它们被用圆括号括起来。当需要的时候,子查询提供一个值或结果集给数据处理语句。

一般子查询经常会被用在 WHERE 子句中。同样,也可以在FROM子句中使用子查询。