《Oracle显式游标和隐式游标.docx》由会员分享，可在线阅读，更多相关《Oracle显式游标和隐式游标.docx（11页珍藏版）》请在三一文库上搜索。
1、Oracle显式游标和隐式游标SQL是用于访问Oracle数据库的语言，PL/SQL扩展和加强了 SQL的功能，它同时 引入了更强的程序逻辑，下面在本文中将对游标的使用进行一下讲解，希望可以和大家 共同学习进步。游标字面理解就是游动的光标。游标是 SQL的一个内存工作区，由系统或用户以 变量的形式定义。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中 进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提 高，否那么频繁的磁盘数据交换会降低效率。用数据库语言来描述游标就是映射在结果 集中一行数据上的位置实体，有了游标，用户就可以访问结果集中的任意一行数据了， 将游
2、标放置到某行后，即可对该行数据进行操作，例如提取当前行的数据等。游标有两种类型：显式游标和隐式游标。游标一旦翻开，数据就从数据库中传送 到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果， 进而控制程序的流程。隐式游标可以使用名字 SQL来访问，但要注意，通过 SQL游 标名总是只能访问前一个处理操作或单行 SELECT操作的游标属性。所以通常在刚刚 执行完操作之后，立即使用 SQL游标名来访问属性。游标的属性有四种，分别是 SQL %ISOPEN，SQL %FOUND，SQL %NOTFOUND ，SQ
3、L %ROWCOUNT。SQL%ISOPEN返回的类型为布尔型，判断游标是否被翻开，如果翻开 %ISOPEN 等于true,否那么等于false,即执行过程中为真,结束后为假。SQL%NOTFOUND返回值为布尔型,判断游标所在的行是否有效，如果有效，那么%FOUNDD等于true，否那么等于false,即与%FOUND属性返回值相反。SQL%FOUND返回值的类型为布尔型，值为 TRUE代表插入删除更新或单行查询操作成功SQL%ROWCOUNT 返回值类型为整型，返回当前位置为止游标读取的记录行数 即成功执行的数据行数。 例如代码如下：set serveroutput on;declarev
4、arno varchar2(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 myc
5、ur%rowcount=2 thenexit;end if;fetch mycur into varno,varprice;end loop;close mycur;end;记录并不保存在数据库中，它与变量一样，保存在内存空间中，在使用记录时候， 要首先定义记录结构，然后声明记录变量。可以把 PL/SQL 记录看作是一个用户自定 义的数据类型。游标 for 循环是显示游标的一种快捷使用方式，它使用 for 循环依次读取结果集中 的行数据，当 form 循环开始时，游标自动翻开 不需要 open ，每循环一次系统自动读 取游标当前行的数据 不需要 fetch ，当退出 for 循环时，游标被自动
6、关闭 不需要使用 close 。使用游标 for 循环的时候不能使用 open 语句， fetch 语句和 close 语句，否那么 会产生错误。 declarecursor mycurvartype numberisselect emp_no,emp_zc from cus_emp_basicwhere com_no=vartype;beginfor person in mycur000627 loopdbms_output.put_line('编号:'|person.emp_no|',住址:'|person.emp_zc);end loop;end;显式游标
7、的使用总共分 4 个步骤：1. 声明游标在 DECLEAR 局部按以下格式声明游标：CURSOR 游标名参数1 数据类型参数2 数据类型.IS SELECT 语句 ;参数是可选局部， 所定义的参数可以出现在 SELECT 语句的 WHERE 子句中。 如 果定义了参数，那么必须在翻开游标时传递相应的实际参数。SELECT 语句是对表或视图的查询语句， 甚至也可以是联合查询。 可以带 WHERE 条件、 ORDER BY 或 GROUP BY 等子句，但不能使用 INTO 子句。在 SELECT 语句 中可以使用在定义游标之前定义的变量。例：DELCARECURSOR C_EMP IS SELE
8、CT empno,ename,salaryFROM empWHERE salary>1500ORDER BY ename;BEGIN在游标定义中 SELECT 语句中不一定非要表可以是视图，也可以从多个表或视图 中选择.2. 翻开游标使用游标中的值之前应该首先翻开游标，翻开游标初始化查询处理。翻开游标的 语法OPEN 游标名 实际参数 1实际参数 2，.;翻开游标时， SELECT 语句的查询结果就被传送到了游标工作区。例：OPEN C_EMP;3. 提取数据从游标得到一行数据使用 FETCH 命令。每一次提取数据后，游标都指向结果集 的下一FETCH cursor_name INTO
9、variable,variable,.对于 SELECT 定义的游标的每一列， FETCH 变量列表都应该有一个变量与之相 对应，变在可执行局部，按以下格式将游标工作区中的数据取到变量中。提取操作必须在 翻开游标之后进行。FETCH 游标名 INTO 变量名 1变量名 2，.;FETCH 游标名 INTO 记录变量 ;游标翻开后有一个指针指向数据区， FETCH 语句一次返回指针所指的一行数据， 要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属 性来进行。下面对这两种格式进行说明：第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的 个数和类型应与
10、SELECT 语句中的字段变量的个数和类型一致。第二种格式一次将一行数据取到记录变量中，需要使用 %ROWTYPE 事先定义记 录变量，这种形式使用起来比拟方便，不必分别定义和使用多个变量。定义记录变量的方法如下：变量名 表名|游标名 %ROWTYPE;其中的表必须存在，游标名也必须先定义。4. 关闭游标CLOSE 游标名 ;例：CLOSE C_EMP;显式游标翻开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放， 游标变成无效，必须重新翻开才能使用。Oracle 支持动态 SELECT 语句和动态游标， 动态的方法大大扩展了程序设计的能对于查询结果为一行的SELECT语句，可以用动态
11、生成查询语句字符串的方法, 在程序执行阶段临时地生成并执行，语法是：execute immediate 查询语句字符串into变量1,变量2，.;在变量声明局部定义的游标是静态的，不能在程序运行过程中修改。虽然可以通 过参数传递来取得不同的数据，但还是有很大的局限性。通过采用动态游标，可以在 程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标 类型，然后声明一个游标变量，游标对应的查询语句可以在程序的执行过程中动态地 说明。ORACLE中带参数、REF游标及动态SQL实例2007-10-12 15:42-=带参数的游标=DECLAREdept_code emp.dept
12、 no%TYPE;- 声明列类型变量三个emp_code emp.emp no %TYPE;emp_ name emp.e name%TYPE;CURSOR emp_cur(deptparam NUMBER) ISSELECT emp no, en ame FROM EMP WHERE dept no = deptparam;-声明显示 游标BEGINdept_code := &部门编号；-请用户输入想查看的部门编号OPEN emp_cur(dept_code);- 翻开游标LOOP-死循环FETCH emp_curINTO 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 '你想查看什么信息？员工(E)或部门信息(D):'-使用ACCEPT命令弹出对话框让用户输入数据DECLARETYPE refcur_t IS REF CURSOR; - 声明 REF 游标类型refcur refcur_t; -
14、声明 REF 游标类型的变量pid NUMBER;p_name VARCHAR2(100);selection VARCHAR2(1) := UPPER(SUBSTR('&tab', 1, 1); - 截取用户输入的字符 串并转换为大写BEGINIF selection = 'E' THEN-如果输入的是'E',那么翻开refcurr游标,并将员工表查询出来赋值给此游标OPEN refcur FORSELECT EMPNO ID, ENAME NAME FROM EMP;DBMS_OUTPUT.PUT_LINE'= 员工信息 =&
15、#39;ELSIF selection = 'D' THEN-如果输入是 'D', 那么翻开部门表OPEN refcur FORSELECT deptno id, dname name FROM DEPT;DBMS_OUTPUT.PUT_LINE'= 部门信息 ='ELSE-否那么返回结束DBMS_OUTPUT.PUT_LINE'请输入员工信息E或部门信息D'RETURN;END IF;FETCH refcurINTO pid, p_name; - 提取行WHILE refcur%FOUND LOOPDBMS_OUTPUT.PUT
16、_LINE('#' | pid | ':' | p_name);FETCH refcurINTO pid, p_name;END LOOP;CLOSE refcur; - 关闭游标END;-= 动态 SQL=VARIABLE maxsal NUMBER; - 声明变量EXECUTE :maxsal := 2500; - 执行引用并给变量赋值DECLAREr_emp EMP%ROWTYPE; - 声明一个行类型变量TYPE c_type IS REF CURSOR; - 声明 REF 游标类型cur c_type; - 声明 REF 游标类型的变量p_salary
17、 NUMBER; - 声明一个标量变量BEGINp_salary := :maxsal; - 引用变量-使用USING语句将引用到的值传给动态 SQL语句'SAL >: 1'中的'1'OPEN cur FOR 'SELECT * FROM EMP WHERE SAL >: 1 ORDER BY SAL DESC'USING p_salary;DBMS_OUTPUT.PUT_LINE(' 薪水大于 ' | p_salary | ' 的员工有 :');LOOPFETCH curINTO r_emp;EXIT WHEN cur%NOTFOUND;DBMS_OUTPUT.PUT_LINE(' 编号:' | r_emp.empno | ' 姓名:' | r_emp.ename |'薪水 :' | r_emp.sal);END LOOP;CLOSE cur; - 关闭游标END;