Oracle游标

 

游标是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

Oralce中游标有静态游标和动态游标2种。

静态游标又可分为显示游标和隐式游标。

一、游标的属性

属性	说明
%ISOPEN	游标是否打开
%FOUND	是否找到结果集
%NOTFOUND	与%FOUND相反
%ROWCOUNT	从结果集中已提取的行数

二、隐式游标

DML操作和单行SELECT (SELECT…INTO…）语句都会用到隐式游标：

插入操作	INSERT
更新操作	UPDATE
删除操作	DELETE
单行查询操作	SELECT…INTO…

每次处理上面的语句时，ORACLE自动打开一个游标，该游标不能使用OPEN,FETCH或CLOSE进行操

作。但可用游标特性来获得执行SQL的信息。隐含游标的名字是：SQL

SQL游标总是反映最后一个SQL语句执行情况。

对于隐式游标而言，属性%ISOPEN的值总是false，这是因为隐式游标在DML语句执行时打开，结束

时就立即自动关闭。

SQL代码：

SET SERVEROUTPUT ON
BEGIN
	UPDATE emp SET sal=sal+100 WHERE ename LIKE '%e';
	IF sql%FOUND THEN
		DBMS_OUTPUT.PUT_LINE('修改行数为：'||TO_CHAR(sql%ROWCOUNT));
	ELSE
		DBMS_OUTPUT.PUT_LINE('没有修改任何行');
    END IF;
	DELETE FROM emp WHERE sal > 3000;
	IF sql%FOUND THEN
		DBMS_OUTPUT.PUT_LINE('删除行数为：'||TO_CHAR(sql%ROWCOUNT));
	ELSE
		DBMS_OUTPUT.PUT_LINE('没有删除任何行');
    END IF;
END
结果：

没有修改任何行

删除行数为：1

PL/SQL 过程已成功完成。 

三、显示游标

1.游标的使用分成以下4个步骤：

(1)．声明游标

在DECLEAR部分按以下格式声明游标：

 
CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])]

IS SELECT语句;

参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须

在打开游标时传递相应的实际参数。

SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或

GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。

(2)．打开游标

在可执行部分，按以下格式打开游标：

OPEN 游标名[(实际参数1[，实际参数2...])];

打开游标时，SELECT语句的查询结果就被传送到了游标工作区。

(3)．提取数据

在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。

FETCH 游标名 INTO 变量名1[，变量名2...];

或

FETCH 游标名 INTO 记录变量;

游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复行，

可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。

下面对这两种格式进行说明：

第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELEC

T语句中的字段变量的个数和类型一致。

第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用

起来比较方便，不必分别定义和使用多个变量。

定义记录变量的方法如下：

变量名 表名|游标名%ROWTYPE；

其中的表必须存在，游标名也必须先定义。

(4)．关闭游标

CLOSE 游标名;

显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重

新打开才能使用。

SET SERVEROUTPUT ON
DECLARE
	CURSOR emp_cursor IS
	SELECT ename,sal FROM emp FOR UPDATE;
	v_ename emp.ename%TYPE;
	v_sal	emp.sal%TYPE;
BEGIN
	OPEN emp_cursor;
	LOOP
		FETCH emp_cursor INTO v_ename,v_sal;
		EXIT WHEN emp_cursor%NOTFOUND;
		IF v_sal < 2000 THEN 
			UPDATE emp SET sal = sal+100 
			WHERE CURRENT OF emp_cursor;
		END IF;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('修改的行数为：'||TO_CHAR(emp_cursor%ROWCOUNT));
	CLOSE emp_cursor;
END;

结果:

修改的行数为：14

PL/SQL 过程已成功完成。

2.游标循环

(1).FOR中引用已定义的游标

SET SERVEROUTPUT ON  
DECLARE  
  CURSOR emp_cursor IS   
  SELECT empno, ename FROM emp;  
BEGIN  
    FOR Emp_record IN emp_cursor LOOP     
    	DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename);  
    END LOOP;
END;
    

7369SMITH
7499ALLEN
7521WARD
7566JONES
7654MARTIN
7698BLAKE
7782CLARK
7788SCOTT
7839KING
7844TURNER
7876ADAMS
7900JAMES
7902FORD
7934MILLER
PL/SQL 过程已成功完成。

（2).FOR中直接引用子查询

SET SERVEROUTPUT ON   
BEGIN  
	FOR re IN (SELECT ename FROM EMP)  LOOP  
		DBMS_OUTPUT.PUT_LINE(re.ename);  
	END LOOP;  
END;  

结果:

SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
PL/SQL 过程已成功完成。

3.参数游标

通过使用参数游标，使用不同参数值生成不同的游标结果集。

SET SERVEROUTPUT ON

DECLARE

	CURSOR emp_cursor(dno NUMBER) IS

		SELECT ename,job FROM emp WHERE deptno=dno;

BEGIN

	FOR emp_record IN emp_cursor(&dno) LOOP

		DBMS_OUTPUT.PUT_LINE('姓名:'||emp_record.ename||',岗位:'||emp_record.job);

	END LOOP;

END;

结果：

输入 dno 的值:  20
原值    5:      FOR emp_record IN emp_cursor(&dno) LOOP
新值    5:      FOR emp_record IN emp_cursor(20) LOOP
姓名:SMITH,岗位:CLERK
姓名:JONES,岗位:MANAGER
姓名:SCOTT,岗位:ANALYST
姓名:ADAMS,岗位:CLERK
姓名:FORD,岗位:ANALYST
PL/SQL 过程已成功完成。

四、动态游标（游标变量）

游标变量是基于REF游标所定义的变量，它实际是指向内存地址的指针。

通过游标变量可以在打开游标时指定其所对应的SELECT语句。

动态游标不能在远程子程序中使用。

定义游标类型的语句如下：

TYPE 游标类型名 REF CURSOR 【RETURN return_type】;

SET SERVEROUTPUT ON
DECLARE
	TYPE emp_cursor_type IS REF CURSOR RETURN emp%ROWTYPE;
	emp_cursor emp_cursor_type;
	emp_record emp%ROWTYPE;
BEGIN
	OPEN emp_cursor FOR SELECT * FROM emp
		WHERE deptno=&dno;
	LOOP
		FETCH emp_cursor INTO emp_record;
		EXIT WHEN emp_cursor%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE('姓名:'||emp_record.ename||',工资:'||emp_record.sal);
	END LOOP;
	CLOSE emp_cursor;
END;

结果:

输入 dno 的值:  20
原值    7:              WHERE deptno=&dno;
新值    7:              WHERE deptno=20;
姓名:SMITH,工资:800
姓名:JONES,工资:2975
姓名:SCOTT,工资:3000
姓名:ADAMS,工资:1100
姓名:FORD,工资:3000
PL/SQL 过程已成功完成。

五、批量提取

FETCH…INTO语句每次只能从游标结果集中提取一行数据。而通过FETCH…BULK COLLECT INTO语句，可以从游标结果集中一次提取所有数据。

LIMIT限制提取行数

SET SERVEROUTPUT ON
DECLARE
	CURSOR emp_cursor IS
		SELECT * FROM emp  ;
	TYPE emp_ARRAY_type IS VARRAY(5) OF emp%ROWTYPE;
	emp_ARRAY emp_ARRAY_type;
BEGIN
	OPEN emp_cursor;
	LOOP
		FETCH emp_cursor BULK COLLECT INTO emp_ARRAY
			LIMIT &rows;
		FOR i IN 1..emp_array.COUNT LOOP
			DBMS_OUTPUT.PUT_LINE('姓名:'||emp_array(i).ename||',工资:'||emp_array(i).sal);
		END LOOP;
		EXIT WHEN emp_cursor%NOTFOUND;
	END LOOP;
	CLOSE emp_cursor;
END;

结果：

输入 rows 的值:  4
原值   10:                      LIMIT &rows;
新值   10:                      LIMIT 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 过程已成功完成。