Oracle显式游标和隐式游标

1、Oracle显式游标和隐式游标SQL是用于访问Oracle数据库的语言，PL/SQL扩展和加强了 SQL的功能，它同时引入了 更强的程序逻辑，下面在本文中将对游标的使用进行一下讲解，希望可以和大家共同学习进游标字面理解就是游动的光标。游标是 SQL的一个内存工作区，由系统或用户以变量 的形式定义。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理， 最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。用数据库语言来描述游标就是映射在结果集中一行数据上的位置实 体，有了游标，用户就可以访问结果集中的任意一行数据了，将游标放置到某行

2、后，即可对 该行数据进行操作，例如提取当前行的数据等。游标有两种类型：显式游标和隐式游标。游标一旦打开，数据就从数据库中传送到游标 变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字 SQL来访问，但要注意，通过SQL游标名总是只 能访问前一个处理操作或单行 SELECT操作的游标属性。所以通常在刚刚执行完操作之后， 立即使用SQL游标名来访问属性。游标的属性有四种，分别是SQL %ISOPEN , SQL %FOUND , SQL %NOTFOUND , SQL %R

3、OWCOUNT 。SQL%ISOPEN返回的类型为布尔型，判断游标是否被打开，如果打开 %ISOPEN等于 true,否则等于false,即执行过程中为真，结束后为假SQL%NOTFOUND返回值为布尔型，判断游标所在的行是否有效，如果有效,则FOUNDD等于true,否则等于false,即与FOUND属性返回值相反。SQL%FOUND返回值的类型为布尔型，值为TRUE代表插入 删除 更新或单行查询操 作成功。SQL%ROWCOUNT返回值类型为整型，返回当前位置为止游标读取的记录行数，即成功执行的数据行数。示例代码如下：set serveroutput on;declarevarno var

4、char2(20);varprice varchar2(20);CURSOR mycur(vartype number) isselect emp_no,emp_zc from cus_emp_basicwhere com_no = vartype;beginif mycur%isopen = false thenopen mycur(043925);end if;fetch mycur into varno,varprice;while mycur%foundloopdbms_output.put_line(varno|','|varprice);if mycur%rowco

5、unt=2 thenexit;end if;fetch mycur into varno,varprice;end loop;close mycur;end;记录并不保存在数据库中，它与变量一样，保存在内存空间中，在使用记录时候，要首先定义记录结构， 然后声明记录变量。 可以把 PL/SQL 记录看作是一个用户自定义的数据类型。游标 for 循环是显示游标的一种快捷使用方式，它使用 for 循环依次读取结果集中的行数据，当 form 循环开始时，游标自动打开（不需要open） ，每循环一次系统自动读取游标当前行的数据（不需要fetch）,当退出for循环时，游标被自动关闭（不需要使用close

6、）。使用游标 for 循环的时候不能使用 open 语句， fetch 语句和 close 语句，否则会产生错误。 declarecursor mycur（vartype number）isselect emp_no,emp_zc from cus_emp_basicwhere com_no=vartype;beginfor person in mycur（000627） loopdbms_output.put_line（' 编号 :'|person.emp_no|', 住址 :'|person.emp_zc）;end loop;end;显式游标的使用总共分4

7、个步骤：1. 声明游标在 DECLEAR 部分按以下格式声明游标：CURSOR 游标名（参数1 数据类型参数2 数据类型 .）IS SELECT 语句 ;参数是可选部分，所定义的参数可以出现在SELECT 语句的 WHERE 子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。SELECT 语句是对表或视图的查询语句，甚至也可以是联合查询。可以带 WHERE 条 件、 ORDER BY 或 GROUP BY 等子句，但不能使用 INTO 子句。在 SELECT 语句中可以 使用在定义游标之前定义的变量。例：DELCARECURSOR C_EMP IS SELECT empno,ena

8、me,salaryFROM emp WHERE salary>1500ORDER BY ename;BEGIN在游标定义中 SELECT 语句中不一定非要表可以是视图，也可以从多个表或视图中选 择.2. 打开游标使用游标中的值之前应该首先打开游标，打开游标初始化查询处理。打开游标的语法OPEN 游标名（实际参数1实际参数2， .）;打开游标时， SELECT 语句的查询结果就被传送到了游标工作区。例：OPEN C_EMP;3. 提取数据从游标得到一行数据使用 FETCH 命令。每一次提取数据后，游标都指向结果集的下一FETCH cursor_name INTO variable,vari

9、able,.对于 SELECT 定义的游标的每一列， FETCH 变量列表都应该有一个变量与之相对应，变在可执行部分， 按以下格式将游标工作区中的数据取到变量中。 提取操作必须在打开游标之后进行。FETCH 游标名INTO 变量名1变量名2， .;或FETCH 游标名INTO 记录变量 ;游标打开后有一个指针指向数据区， FETCH 语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。下面对这两种格式进行说明：第一种格式中的变量名是用来从游标中接收数据的变量， 需要事先定义。 变量的个数和类型应与 SELECT 语句中的字段变量的

10、个数和类型一致。第二种格式一次将一行数据取到记录变量中，需要使用 %ROWTYPE 事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。定义记录变量的方法如下：变量名 表名 | 游标名%ROWTYPE;其中的表必须存在，游标名也必须先定义。4. 关闭游标CLOSE 游标名 ;例：CLOSE C_EMP;显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。Oracle支持动态SELECT语句和动态游标，动态的方法大大扩展了程序设计的能力。对于查询结果为一行的SELECT语句，可以用动态生成查询语句字符串的方法，在程 序执行

11、阶段临时地生成并执行，语法是：execute immediate 查询语句字符串into变量1，变量2,;在变量声明部分定义的游标是静态的，不能在程序运行过程中修改。虽然可以通过参数传递来取得不同的数据，但还是有很大的局限性。通过采用动态游标，可以在程序运行阶段 随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型，然后声明一个 游标变量，游标对应的查询语句可以在程序的执行过程中动态地说明。ORACLE中带参数、REF游标及动态SQL实例2007-10-12 15:42-=带参数的游标=-DECLARE? dept_code emp.deptno%TYPE;- 声明列类型变量三个

12、? emp_code? emp.empno%TYPE;? emp_name? emp.ename%TYPE;? CURSOR emp_cur(deptparam NUMBER) IS? SELECT empno, ename FROM EMP WHERE deptno = deptparam;-声明显示游标BEGIN? dept_code := &部门编号-请用户输入想查看的部门编号? OPEN emp_cur(dept_code);- 打开游标? LOOP ?-死循环? FETCH emp_cur ? INTO emp_code, emp_name; - 提取游标值赋给上面声明的变量

13、? EXIT WHEN emp_cur%NOTFOUND; - 如果游标里没有数据则退出循环? DBMS_OUTPUT.PUT_LINE(emp_code | '' | emp_name); - 输出查询? END LOOP;? CLOSE emp_cur; - 关闭游标END;-=REF 游标 =-ACCEPT tab FROMPT '你想查看彳f么信息？员工(E)或部门信息(D):'-使用ACCEPT命令弹出对话框让用户输入数据DECLARE? TYPE refcur_t IS REF CURSOR; - 声明 REF 游标类型? refcur? refcu

14、r_t; - 声明 REF 游标类型的变量? pid? NUMBER;? p_name? VARCHAR2(100);? selection VARCHAR2(1) := UPPER(SUBSTR('&tab', 1, 1); - 截取用户输入的字符串并转换为大写BEGIN? IF selection = 'E' THEN?-如果输入的是E',则打开refcurr游标,并将员工表查询出来赋值给此游标? OPEN refcur FOR? SELECT EMPNO ID, ENAME NAME FROM EMP;? DBMS_OUTPUT.PUT_L

15、INE('员工信息=')；? ELSIF selection = 'D' THEN?-如果输入是'P,则打开部门表? OPEN refcur FOR? SELECT deptno id, dname name FROM DEPT;? DBMS_OUTPUT.PUT_LINE('= 部门信息=');? ELSE?-否则返回结束? DBMS_OUTPUT.PUT_LINE(,请输入员工信息(E)或部门信息(D),);? RETURN;? END IF;? FETCH refcur? INTO pid, p_name;- 提取行? WHILE

16、refcur%FOUND LOOP? DBMS_OUTPUT.PUT_LINE('#' | pid | p_name);? FETCH refcur? INTO pid, p_name;? END LOOP;? CLOSE refcur;-关闭游标END;动态SQLVARIABLE maxsal NUMBER; - 声明变量EXECUTE :maxsal := 2500; - 执行引用并给变量赋值DECLARE? r_emp EMP%ROWTYPE; - 声明一个行类型变量? TYPE c_type IS REF CURSOR; - 声明 REF 游标类型? cur? c_type; - 声明 REF 游标类型的变量? p_salary NUMBER; - 声明一个标量变量BEGIN? p_salary := :maxsal; - 引用变量? -使用 USING 语句将引用到的值传给动态SQL 语句 'SAL >: 1' 中的 '1'? OPEN cur FOR 'SELECT * FROM EMP WHERE SAL >: 1 ORDER BY SAL DESC'? USING p_salary;? DBMS_OUTPU