Oracle--plsql游标创建和使用

为什么要游标：

先看这个：

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子句中使用子查询。