Oracle超详细笔记11--游标

一、游标的概念及类型

1.游标的概念  (游动的光标)

游标（CURSOR）是Oracle系统在内存中开辟的一个工作区，在其中存放SELECT语句返回的查询结果。

使用游标时，SELECT语句查询的结果可以是单条记录，多条记录，也可以是零条记录。

游标工作区中，存在着一个指针（POINTER）,在初始状态它指向查询结果的首记录。

 

2.游标的类型

显式游标

由用户定义、操作，用于处理返回多行数据的SELECT查询。

隐式游标

由系统自动进行操作，用于处理DML语句和返回单行数据的SELECT查询。          

 

二、显式游标

1.显式游标的操作

步骤：

定义游标

打开游标

检索游标

关闭游标

 

2.定义游标

语法

CURSOR cursor_name IS select_statement ;

说明

游标必须在PL/SQL块的声明部分进行定义；

游标定义时可以引用PL/SQL变量，但变量必须在游标定义之前定义；

定义游标时并没有生成数据，只是将定义信息保存到数据字典中；

游标定义后，可以使用cursor_name%ROWTYPE定义游标类型变量。

 

3.打开游标

语法

OPEN cursor_name;

说明

检查变量的值

执行游标定义时对应的SELECT语句，将查询结果检索到工作区中。

游标指针指向第一个元组

一旦游标打开，就无法再次打开，除非先关闭

如果游标定义中的变量值发生变化，则只能在下次打开游标时才起作用。

 

4.检索游标

语法格式

FETCH cursor_name INTO variable_list|record_variable;

说明

在使用FETCH语句之前必须先打开游标

对游标第一次使用FETCH语句时，游标指针指向第一条记录，因此操作的对象是第一条记录，使用后，游标指针指向下一条记录。

游标指针只能向下移动，不能回退

INTO子句中的变量个数、顺序、数据类型必须与工作区中每行记录的字段数、顺序以及数据类型一一对应。

 

5.关闭游标

语法格式

  CLOSE cursor_name;

说明

游标所对应的内存工作区变为无效，释放与游标相关的系统资源。

 

例：

根据输入的部门号查询某个部门的员工信息，部门号在程序运行时指定。

DECLARE

  v_deptno emp.deptno%TYPE;

  CURSOR c_emp IS SELECT * FROM emp WHERE deptno=v_deptno;

  v_emp c_emp%ROWTYPE;

BEGIN

  v_deptno:=&x;

  OPEN c_emp;

  LOOP

     FETCH c_emp INTO v_emp;

     EXIT WHEN c_emp%NOTFOUND;

     DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||

             v_emp.ename||' '||

             v_emp.sal ||'  '||

             v_deptno);                                   

   END LOOP;

   CLOSE c_emp;

END;

 

6.显式游标的属性

%ISOPEN

布尔型。如果游标已经打开，返回TRUE,否则为FALSE。

%FOUND

布尔型，如果最近一次使用FETCH语句，有返回结果则为TRUE,否则为FALSE;

%NOTFOUND

布尔型，如果最近一次使用FETCH语句,没有返回结果则为TRUE,否则为FALSE;

%ROWCOUNT

数值型，返回到目前为止从游标缓冲区检索的元组数。

 

7.参数化显式游标

参数化游标定义语法格式

CURSOR cursor_name(parameter1

datatype[,parameter2 datatype…])

IS select_statement

打开参数化游标的方法

OPEN cursor_name(parameter1

[,parameter2…])

注意：

定义参数化游标时，只能指定参数的类型，而不能指定参数的长度、精度、刻度；

打开带参数的游标时，实参的个数和数据类型等必须与游标定义时形参个数和数据类型等相匹配。

例：

查询并输出某个部门的员工信息。

DECLARE

  CURSOR c_emp(p_deptno emp.deptno%TYPE)IS

  SELECT * FROM emp WHERE deptno=p_deptno;

  v_emp    c_emp%ROWTYPE;

BEGIN

  OPEN c_emp(10);

  LOOP

    FETCH c_emp INTO v_emp;

    EXIT WHEN c_emp%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);    

  END LOOP;  

  CLOSE c_emp;

  OPEN c_emp(20);

  LOOP

    FETCH c_emp INTO v_emp;

    EXIT WHEN c_emp%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.ename);    

  END LOOP;  

  CLOSE c_emp;

END;

 

利用游标更新或删除数据

书本P230, 例13-14

 

三、隐式游标

概念

所有的SQL语句都有一个执行的缓冲区，隐式游标就是指向该缓冲区的指针，由系统隐含地打开、处理和关闭。隐式游标又称为SQL游标。

隐式游标主要用于处理INSERT、UPDATE，DELETE以及单行的SELECT…INTO语句，没有OPEN，FETCH，CLOSE等操作命令。

隐式游标属性

SQL%ISOPEN：

布尔型值，判断隐式游标是否已经打开。对用户而言，该属性值始终为FALSE，因为操作时系统自动打开，操作完后立即自动关闭。

SQL%FOUND：

布尔型值，判断当前的操作是否会对数据库产生影响。如果有数据的插入、删除、修改或查询到数据，则返回TRUE，否则返回FALSE。

SQL%NOTFOUND：

布尔型值，判断当前的操作是否对数据库产生影响。如果没有数据的插入、删除、修改或没有查询到数据，则返回TRUE，否则返回FALSE。

SQL%ROWCOUNT：

数值型，返回当前操作所涉及的数据库中的行数。

 

例：

修改员工号为1000的员工工资，将其工资增加100。如果该员工不存在，则向emp表中插入一个员工号为1000，工资为1600的员工。

BEGIN

    UPDATE emp SET sal=sal+100 WHERE empno=1000;

    IF SQL%NOTFOUND THEN

       INSERT INTO emp(empno,sal) VALUES(1000,1600);

    END IF;

END;

或

BEGIN

    UPDATE emp SET sal=sal+100 WHERE empno=1000;

    IF SQL%ROWCOUNT=0 THEN

       INSERT INTO emp(empno,sal) VALUES(1000,1600);

    END IF;

END;

 

四、游标变量

1.概念

游标变量是一个指向多行查询结果集的指针，不与特定的查询绑定，因此具有非常大的灵活性，可以在打开游标变量时定义查询，可以返回不同结构的结果集 

 

2.使用游标变量包括

游标引用类型（REF CURSOR）

声明游标变量

打开游标变量

检索游标变量

关闭游标变量

 

3.定义游标引用类型及游标变量

语法

TYPE ref_cursor_type_name

IS REF CURSOR

[RETURN return_type]

 

ref_cursor_type_name variable_name;

RETURN子句用于指定定义的游标类型返回结果集的类型，该类型必须是记录类型。如果定义游标引用类型时带有RETURN子句，则用其定义的变量称为强游标变量，否则称为弱游标变量。

 

例：

TYPE emp_cursor_type IS REF CURSOR RETURN emp%ROWTYPE;

TYPE general_cursor_type IS REF CURSOR;

v_emp emp_cursor_type;

v_general general_cursor_type;

 

4.打开游标变量

语法

OPEN cursor_variable FOR select_statement;

注意

如果打开的游标变量是强游标变量，则查询语句的返回类型必须与游标引用类型定义中RETURN子句指定的返回类型相匹配。

例：

OPEN v_emp FOR SELECT * FROM emp;

OPEN v_general FOR SELECT empno,ename,sal,deptno FROM emp;

 

5.检索游标变量

语法

LOOP

FETCH cursor_variable INTO variable1, variable2, …;

EXIT WHEN cursor_variable%NOTFOUND;

……

END LOOP;

注意

检索游标变量时只能使用简单循环或WHILE循环，不能采用FOR循环。

 

6.关闭游标变量

语法

CLOSE cursor_variable;

 

例：

要求根据输入的不同表名进行不同处理，若表名为emp，则显示高于10号部门平均工资的员工信息；若表名为dept，则显示各个部门的人数。

DECLARE

  v_table CHAR(20);

  TYPE type_cursor IS REF CURSOR;

  v_cursor type_cursor;

  v_emp emp%ROWTYPE;

  v_deptno emp.deptno%TYPE;

  v_num NUMBER;

BEGIN

  v_table:='&table_name';

  IF v_table = 'emp' THEN

    OPEN v_cursor FOR SELECT * FROM emp WHERE sal>(

             SELECT AVG(sal) FROM emp WHERE deptno=10);

  ELSIF v_table = 'dept' THEN

    OPEN v_cursor FOR SELECT deptno,count(*) num

              FROM emp GROUP BY deptno;

  ELSE

    RAISE_APPLICATION_ERROR(-20000,'Input must be  ''emp'‘ or ''dept''');

 END IF;

 LOOP

    IF v_table = 'emp' THEN

      FETCH v_cursor INTO v_emp;

      EXIT WHEN v_cursor%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||  

                                             v_emp.ename||' '||

                                             v_emp.sal||' '||

                                             v_emp.deptno);

    ELSE

      FETCH v_cursor INTO v_deptno,v_num;

      EXIT WHEN v_cursor%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(v_deptno||' '||v_num);

    END IF;

  END LOOP;

  CLOSE v_cursor;

END;