《PLSQL中的游标》PPT课件.ppt

PL SQL中的游标 游标的概念 在PL SQL程序中 对于处理多行记录的事务经常使用游标来实现 为了处理SQL语句 ORACLE必须分配一片叫上下文 contextarea 的区域来处理所必需的信息 其中包括要处理的行的数目 一个指向语句被分析以后的表示形式的指针以及查询的活动集 activeset 游标是一个指向上下文的句柄 handle 或指针 通过游标 PL SQL可以控制上下文区和处理语句时上下文区会发生些什么事情 游标的概念 对于不同的SQL语句 游标的使用情况不同 显式游标 处理显式游标需要四个PL SQL步骤 定义游标 就是定义一个游标名 以及与其相对应的SELECT语句 其一般形式为 CURSORcursor nameISselect statement 其中 游标声明部分是唯一可以出现在模块声明部分的步骤 其他三个步骤都在执行或异常处理部分中游标名是标识符 所以也有作用域 并且必须在使用前进行说明任何SELECT语句都是合法的 但是SELECT INTO语句是非法的在声明部分的末尾声明游标 显式游标 处理显式游标需要四个PL SQL步骤 打开游标 就是执行游标所对应的SELECT语句 将其查询结果放入工作区 并且指针指向工作区的首部 标识游标结果集合 其中 PL SQL程序不能用OPEN语句重复打开同一个游标 OPENcursor name 显式游标 处理显式游标需要四个PL SQL步骤 提取游标 就是检索结果集合中的数据行 放入指定的输出变量中 FETCHcursor nameINTO variable list record variable 显式游标 处理显式游标需要四个PL SQL步骤 关闭游标 当提取和处理完游标结果集合数据后 应及时关闭游标 以释放该游标所占用的系统资源 并使该游标的工作区变成无效 不能再使用FETCH语句取其中数据 关闭后的游标可以使用OPEN语句重新打开 CLOSEcursor name 显式游标 例 查询前10名员工的信息 DECLAREv enameemp ename TYPE v salemp sal TYPE CURSORc cursorISSELECTename salFROMempWHERErownum 11 BEGINOPENc cursor FETCHc cursorINTOv ename v sal WHILEc cursor FOUNDLOOPDBMS OUTPUT PUT LINE v ename to char v sal FETCHc cursorINTOv ename v sal ENDLOOP CLOSEc cursor END 参数化游标 例 DECLAREv enameemp ename TYPE v salemp sal TYPE CURSORc cursor P salemp sal type ISSELECTename salFROMempWHEREsal P sal BEGINOPENc cursor 1000 FETCHc cursorINTOv ename v sal WHILEc cursor FOUNDLOOPDBMS OUTPUT PUT LINE v ename to char v sal FETCHc cursorINTOv ename v sal ENDLOOP CLOSEc cursor END 游标属性 游标属性 DECLATECURSORC cursoris BEGINOPENC cursorFETCHC cursorINTO FETCHC cursorINTO FETCHC cursorINTO CLOSEC cursorEND FOUND返回错误 FOUND为NULL FOUND为TRUE FOUND为TRUE FOUND返回错误 FOUND为FALSE 游标属性 DECLATECURSORC cursoris BEGINOPENC cursorFETCHC cursorINTO FETCHC cursorINTO FETCHC cursorINTO CLOSEC cursorEND NOTFOUND返回错误 NOTFOUND为NULL NOTFOUND为FALSE NOTFOUND为FALSE NOTFOUND返回错误 NOTFOUND为TRUE 游标属性 DECLATECURSORC cursoris BEGINOPENC cursorFETCHC cursorINTO FETCHC cursorINTO FETCHC cursorINTO CLOSEC cursorEND ISOPEN为FALSE ISOPEN为TRUE ISOPEN为TRUE ISOPEN为TRUE ISOPEN为FALSE ISOPEN为TRUE 游标属性 DECLATECURSORC cursoris BEGINOPENC cursorFETCHC cursorINTO FETCHC cursorINTO FETCHC cursorINTO CLOSEC cursorEND ROWCOUNT返回错误 ROWCOUNT为0 ROWCOUNT为1 ROWCOUNT为2 ROWCOUNT返回错误 ROWCOUNT为2 练习 给工资低于1200的员工增加工资50 处理隐式游标 显式游标主要是用于对查询语句的处理 尤其是在查询结果为多条记录的情况下 而对于非查询语句 如修改 删除操作 则由ORACLE系统自动地为这些操作设置游标并创建其工作区 这些由系统隐含创建的游标称为隐式游标 隐式游标的名字为SQL 这是由ORACLE系统定义的 对于隐式游标的操作 如定义 打开 取值及关闭操作 都由ORACLE系统自动地完成 无需用户进行处理 用户只能通过隐式游标的相关属性 来完成相应的操作 在隐式游标的工作区中 所存放的数据是与用户自定义的显示游标无关的 最新处理的一条SQL语句所包含的数据 INSERT UPDATE DELETE SELECTINTO语句中不必明确定义游标 隐式游标属性 处理隐式游标 例 删除EMP表中20号部门的所有员工 如果该部门中已没有员工可删除 则在DEPT表中删除该部门DECLAREV deptnoemp deptno TYPE 20 BEGINDELETEFROMempWHEREdeptno v deptno IFSQL NOTFOUNDTHENDELETEFROMdeptWHEREdeptno 20 ENDIF END 游标检索循环 例 DECLAREv empnoemp empno TYPE v salemp sal TYPE CURSORc cursorISSELECTempno salFROMemp BEGINOPENc cursor LOOPFETCHc cursorINTOv empno v sal EXITWHENc cursor NOTFOUND IFv sal 1200THENUPDATEempSETsal sal 50WHEREempno v empno DBMS OUTPUT PUT LINE 编码为 v empno 工资已更新 ENDIF DBMS OUTPUT PUT LINE 记录数 c cursor ROWCOUNT ENDLOOP CLOSEc cursor END 游标的FOR循环 PL SQL语言提供了游标FOR循环语句 自动执行游标的OPEN FETCH CLOSE语句和循环语句的功能 当进入循环时 游标FOR循环语句自动打开游标 并提取第一行游标数据 当程序处理完当前所提取的数据而进入下一次循环时 游标FOR循环语句自动提取下一行数据供程序处理 当提取完结果集合中的所有数据行后结束循环 并自动关闭游标 FORindex variableINcursor name value value LOOP 游标数据处理代码ENDLOOP 游标的FOR循环 其中 index variable为游标FOR循环语句隐含声明的索引变量 该变量为记录变量 其结构与游标查询语句返回的结构集合的结构相同 在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据 index variable中各元素的名称与游标查询语句选择列表中所制定的列名相同 如果在游标查询语句的选择列表中存在计算列 则必须为这些计算列指定别名后才能通过游标FOR循环语句中的索引变量来访问这些列数据 游标的FOR循环 例 DECLARECURSORc salISSELECTempno ename salFROMemp BEGIN 隐含打开游标FORv salINc salLOOP 隐含执行一个FETCH语句 隐含监测c sal NOTFOUNDDBMS OUTPUT PUT LINE to char v sal empno v sal ename to char v sal sal ENDLOOP 隐含关闭游标END 游标的FOR循环 例 DECLARECURSORc cursor dept noNUMBERDEFAULT10 ISSELECTdname locFROMdeptWHEREdeptno dept no BEGINDBMS OUTPUT PUT LINE dept no参数值为30 FORc1 recINc cursor 30 LOOPDBMS OUTPUT PUT LINE c1 rec dname c1 rec loc ENDLOOP DBMS OUTPUT PUT LINE CHR 10 使用默认的dept no参数值10 FORc1 recINc cursorLOOPDBMS OUTPUT PUT LINE c1 rec dname c1 rec loc ENDLOOP END NO DATA FOUND和 NOTFOUND的区别 SELECT INTO语句触发NO DATA FOUND 当一个显式游标的WHERE子句未找到时触发 NOTFOUND 当UPDATE或DELETE语句的WHERE子句未找到时触发SQL NOTFOUND 在提取循环中要用 NOTFOUND或 FOUND来确定循环的退出条件 不要用NO DATA FOUND SELECTFORUPDATE游标 游标修改和删除操作是指在游标定位下 修改或删除表中指定的数据行 这时 要求游标查询语句中必须使用FORUPDATE选项 以便在打开游标时锁定游标结果集合在表中对应数据行的所有列和部分列 为了对正在处理 查询 的行不被另外的用户改动 ORACLE提供一个FORUPDATE子句来对所选择的行进行锁住 该需求迫使ORACLE锁定游标结果集合的行 可以防止其他事务处理更新或删除相同的行 直到您的事务处理提交或回退为止 SELECTFORUPDATE游标 一般形式为 注意 如果另一个会话已对活动集中的行加了锁 那么SELECTFORUPDATE操作一直等待到其它的会话释放这些锁后才继续自己的操作 对于这种情况 当加上NOWAIT子句时 如果这些行真的被另一个会话锁定 则OPEN立即返回并给出 ORA 0054 resourcebusyandacquirewithnowaitspecified 如果使用FORUPDATE声明游标 则可在DELETE和UPDATE语句中使用WHERECURRENTOFcursor name子句 修改或删除游标结果集合当前行对应的数据库表中的数据行 SELECT FROM FORUPDATE OFcolumn column NOWAIT SELECTFORUPDATE游标 例 从EMP表中查询10号部门的员工情况 将其工资最低定为1500 DECLAREV deptnoemp deptno TYPE 10 CURSORemp cursorISSELECTempno salFROMempWHEREdeptno v deptnoFORUPDATENOWAIT BEGINFORemp recordINemp cursorLOOPIFemp record sal 1500THENUPDATEempSETsal 1500WHERECURRENTOFemp cursor ENDIF ENDLOOP COMMIT END 游标变量 与游标一样 游标变量也是一个指向多行查询结果集合中当前数据行的指针 但与游标不同的是 游标变量是动态的 而游标是静态的 游标只能与指定的查询相连 即固定指向一个查询的内存处理区域 而游标变量则可与不同的查询语句相连 它可以指向不同查询语句的内存处理区域 但不能同时指向多个内存处理区域 在某一时刻只能与一个查询语句相连 只要这些查询语句的返回类型兼容即可 声明游标变量 游标变量为一个指针 它属于参照类型 所以在声明游标变量类型之前必须先定义游标变量类型 在PL SQL中 可以在块 子程序和包的声明区域内定义游标变量参照类型 TYPEref type nameISREFCURSOR RETURNreturn type 其中 ref type name为新定义的游标变量类型名称 retuen type为游标变量的返回值类型 它必须为记录变量 声明游标变量 例 声明两个强类型定义游标变量和一个弱类型游标变量DECLARETYPEdeptrecordISRECORD Deptnodept deptno TYPE Dnamedept deptno TYPE Locdept loc TYPE TYPEdeptcurtypeISREFCURSORRETURNdept ROWTYPE TYPEdeptcurtyp1ISREFCURSORRETURNdeptrecord TYPEcurtypeISREFCURSOR Dept c1deptcurtype Dept c2deptcurtyp1 Cvcurtype 游标变量操作 与游标一样 游标变量操作也包括打开 提取和关闭三个步骤 打开游标变量 打开游标变量时使用的是OPEN FOR语句 格式为 OPEN cursor variable name host cursor variable name FORselect statement 其中 cursor variable name为游标变量 host cursor variable name为PL SQL主机环境 如OCI ORACLECallInterface Pro c程序等 中声明的游标变量 OPEN FOR语句可以在关闭当前的游标变量之前重新打开游标变量 而不会导致CURSOR ALREAD OPEN异常错误 新打开游标变量时 前一个查询的内存处理区将被释放 游标变量操作 与游标一样 游标变量操作也包括打开 提取和关闭三个步骤 提取游标变量数据 使用FETCH语句提取游标变量结果集合中的数据 格式为 FETCH cursor variable name host cursor variable name INTO variable variable record variable 其中 cursor variable name和host cursor variable name分别为游标变量和宿主游标变量名称 variable和record variable分别为普通变量和记录变量名称 游标变量操作 与游标一样 游标变量操作也包括打开 提取和关闭三个步骤 关闭游标变量 CLOSE语句关闭游标变量 格式为 CLOSE cursor variable name host cursor variable name 其中 cursor variable name和host cursor variable name分别为游标变量和宿主游标变量名称 如果应用程序试图关闭一个未打开的游标变量 则将导致INVALID CURSOR异常错误 游标变量应用举例 例 强类型参照游标变量类型DECLARETYPEemp job recISRECORD Employee idemp empno TYPE Employee nameemp ename TYPE Job titleemp job TYPE TYPEemp job refcur typeISREFCURSORRETURNemp job rec Emp refcuremp job refcur type Emp jobemp job rec 游标变量应用举例 例 BEGINOPENemp refcurFORSELECTempno ename jobFROMempORDERBYdeptno FETCHemp refcurINTOemp job WHILEemp refcur FOUNDLOOPDBMS OUTPUT PUT LINE emp job employee id emp job employee name isa emp job job title FETCHemp refcurINTOemp job ENDLOOP END 游标变量应用举例 例 弱类型参照游标变量类型DECLARETyperefcur tISREFCURSOR Refcurrefcur t TYPEsample rec typeISRECORD Idnumber DescriptionVARCHAR2 30 samplesample rec type selectionvarchar2 1 UPPER SUBSTR 游标变量应用举例 例 BEGINIFselection D THENOPENrefcurFORSELECTdeptno dnameFROMdept DBMS OUTPUT PUT LINE Departmentdata ELSIFselection E THENOPENrefcurFORSELECTempno ename isa jobFROMemp DBMS OUTPUT PUT LINE Employeedata ELSEDBMS OUTPUT PUT LINE Pleaseenter D or E RETURN ENDIF DBMS OUTPUT PUT LINE FETCHrefcurINTOsample WHILErefcur FOUNDLOOPDBMS OUTPUT PUT LINE sample id sample description FETCHrefcurINTOsample ENDLOOP CLOSErefcur END