游标、存储过程和触发器 数据库课件

第10章游标、存储过程和触发器第10章游标、存储过程和触发器介绍Oracle数据库程序设计中经常会用到的3个概念，即游标、存储过程和触发器。第10章游标、存储过程和触发器本章学习目标•理解游标的基本概念；•掌握游标的基本操作、属性操作和循环游标；•掌握PL/SQL语言的三种存储过程；•理解触发器的概念，掌握创建和使用触发器的方法；第10章游标、存储过程和触发器本章知识点•游标•存储过程管理•触发器管理第10章游标、存储过程和触发器游标•游标的基本概念•游标控制语句•游标属性•游标FOR循环第10章游标、存储过程和触发器游标的基本概念游标：游动的光标。游标是映射在结果集中一行数据上的位置实体，有了游标，用户就可以访问结果集中的任意一行数据了。将游标放置到某行后，即可对该行数据进行操作，最常见的操作是提取当前行数据。游标分两种：显式游标隐式游标第10章游标、存储过程和触发器游标的基本概念•游标示意图游标第10章游标、存储过程和触发器游标的基本概念隐式游标不需要声明，使用时也不需要执行打开和关闭操作。实际上，就是在Select语句中增加了INTO子句，把结果集自动读取到指定的比变量中。【例】使用SELECT语句声明隐式游标，从Students表中读取Sname字段的值到变量VSname：DECLAREVSnameStudents.Sname%Type;BEGINSELECTSnameINTOVSnameFROMStudentsWHERESdept='Automation';dbms_output.put_line(VSname);END;第10章游标、存储过程和触发器游标的基本概念•显式游标显式游标需要声明，在使用之前需要打开游标，使用完成后要关闭游标。使用显式游标的步骤包括：（1）声明游标。（2）打开游标。（3）读取数据。（4）关闭游标。第10章游标、存储过程和触发器游标控制语句（1）声明游标语句CURSOR：DECLARECURSOR游标名[(参数列表)]ISSELECT语句;【例】声明一个游标MyCur，读取指定类型的用户信息：DECLARECURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;第10章游标、存储过程和触发器游标控制语句（2）打开游标语句OPEN：OPEN游标名[(参数列表)];【例】打开游标MyCur，读取类型为1的用户信息：OPENMyCur(1);第10章游标、存储过程和触发器游标控制语句（3）游标取值语句FETCH。游标取值语句FETCH的基本语法结构如下：FETCH游标名INTO变量列表;【例】在打开的游标MyCur的当前位置读取数据：FETCHMyCurINTOvarId,varName;（4）关闭游标语句CLOSE：CLOSE游标名;【例】关闭游标MyCur：CLOSEMyCur;第10章游标、存储过程和触发器游标控制语句【例】下面介绍一个完整的游标应用实例：/*打开显示模式*/SETServerOutputON;DECLARE--开始声明部分varIdNUMBER;--声明变量,用来保存游标中的用户编号varNameVARCHAR2(50);--声明变量,用来保存游标中的用户名--定义游标,varType为参数,指定用户类型编号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--开始程序体OPENMyCur(1);--打开游标,参数为1,表示读取用户类型编号为1的记录FETCHMyCurINTOvarId,varName;--读取当前游标位置的数据CLOSEMyCur;--关闭游标dbms_output.put_line('用户编号:'||varId||',用户名:'||varName);--显示读取的数据END;--结束程序体第10章游标、存储过程和触发器TheEnd第10章游标、存储过程和触发器第10章游标、存储过程和触发器游标属性（1）%ISOPEN属性判断游标是否被打开，如果打开，则%ISOPEN等于TRUE，否则等于FALSE。【例】下面的代码演示当使用未打开的游标时，将会出现错误：/*打开显示模式*/SETServerOutputON;DECLARE--开始声明部分varNameVARCHAR2(50);--声明变量,用来保存游标中的用户名varIdNUMBER;--声明变量,用来保存游标中的用户编号--定义游标,varType为参数,指定用户类型编号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--开始程序体FETCHMyCurINTOvarId,varName;--读取当前游标位置的数据CLOSEMyCur;--关闭游标dbms_output.put_line('用户编号:'||varId||',用户名:'||varName);--显示读取的数据END;--结束程序体第10章游标、存储过程和触发器游标属性【例】修改上面的程序，在使用游标之前，调用%ISOPEN属性判断游标是否打开。/*打开显示模式*/SETServerOutputON;DECLARE--开始声明部分varNameVARCHAR2(50);--声明变量,用来保存游标中的用户名varIdNUMBER;--声明变量,用来保存游标中的用户编号--定义游标,varType为参数,指定用户类型编号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--开始程序体IFMyCur%ISOPEN=FALSEThenOPENMyCur(2);ENDIF;FETCHMyCurINTOvarId,varName;--读取当前游标位置的数据CLOSEMyCur;--关闭游标dbms_output.put_line('用户编号:'||varId||',用户名:'||varName);--显示读取的数据END;--结束程序体第10章游标、存储过程和触发器游标属性（2）%FOUND属性和%NOTFOUND属性%FOUND属性用来判断游标所在的行是否有效，如果有效，返回TRUE，无效时返回FALSE。【例】%FOUND属性可以循环执行游标读取数据：/*打开显示模式*/SETServerOutputON;DECLARE--开始声明部分varNameVARCHAR2(50);--声明变量,用来保存游标中的用户名varIdNUMBER;--声明变量,用来保存游标中的用户编号--定义游标,varType为参数,指定用户类型编号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN--开始程序体IFMyCur%ISOPEN=FALSEThenOPENMyCur(1);ENDIF;FETCHMyCurINTOvarId,varName;--读取当前游标位置的数据WHILEMyCur%FOUND--如果当前游标有效，则执行循环LOOPdbms_output.put_line('用户编号:'||varId||',用户名:'||varName);--显示读取的数据FETCHMyCurINTOvarId,varName;--读取当前游标位置的数据ENDLOOP;CLOSEMyCur;--关闭游标END;--结束程序体第10章游标、存储过程和触发器游标属性（3）%ROWCOUNT属性返回到当前位置为止游标读取的记录行数。【例】只读取前2行记录：/*打开显示模式*/SETServerOutputON;DECLARE--开始声明部分varNameVARCHAR2(50);--声明变量,用来保存游标中的用户名varIdNUMBER;--声明变量,用来保存游标中的用户编号--定义游标,varType为参数,指定用户类型编号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;第10章游标、存储过程和触发器游标属性BEGIN--开始程序体IFMyCur%ISOPEN=FALSEThenOPENMyCur(1);ENDIF;FETCHMyCurINTOvarId,varName;--读取当前游标位置的数据WHILEMyCur%FOUND--如果当前游标有效，则执行循环LOOPdbms_output.put_line('用户编号:'||varId||',用户名:'||varName);--显示读取的数据IFMyCur%ROWCOUNT=2THENEXIT;ENDIF;FETCHMyCurINTOvarId,varName;--读取当前游标位置的数据ENDLOOP;CLOSEMyCur;--关闭游标END;--结束程序体第10章游标、存储过程和触发器第10章游标、存储过程和触发器游标FOR循环游标FOR循环是显式游标的一种快捷使用方式，它使用FOR循环依次读取结果集中的行数据。当FOR循环开始时，游标被自动打开（不再需要OPEN语句）；每循环一次，系统自动读取游标当前行的数据（不需要使用FETCH语句）；当退出循环时，游标自动关闭（不需要使用CLOSE语句）。第10章游标、存储过程和触发器游标FOR循环游标FOR循环通常与PL/SQL记录一起使用。PL/SQL记录（RECORD）是由一组数据构成的逻辑单元，并不保存在数据库中，与变量一样，保存在内存空间中。使用记录时，需要先定义记录的结构，然后声明记录变量。定义记录类型的基本语法如下：TYPE记录类型名ISRECORD(字段声明[,字段声明]…);定义记录变量的方法与定义普通变量的方法相同，语法如下：记录变量名记录变量类型第10章游标、存储过程和触发器游标FOR循环【例】声明记录类型User_Record_Type和定义记录变量var_UserRecord：TYPEUser_Record_TypeISRECORD(UserIdUsers.UserId%Type,UserNameUsers.UserName%Type);var_UserRecordUser_Record_Type;第10章游标、存储过程和触发器游标FOR循环【例】PL/SQL记录可以与游标结合使用：/*打开显示模式*/SETServerOutputON;DECLARE--开始声明部分/*声明记录类型*/TYPEUser_Record_TypeISRECORD(UserIdUsers.UserId%Type,UserNameUsers.UserName%Type);/*定义记录变量*/var_UserRecordUser_Record_Type;--定义游标,varType为参数,指定用户类型编号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;第10章游标、存储过程和触发器游标FOR循环BEGIN--开始程序体IFMyCur%ISOPEN=FALSEThenOPENMyCur(1);ENDIF;LOOPFETCHMyCurINTOvar_UserRecord;--读取当前游标位置的数据到记录变量var_UserRecordEXITWHENMyCur%NOTFOUND;--当游标指向结果集结尾时退出循环/*显示保存在记录变量var_UserRecord中的数据*/dbms_output.put_line('用户编号:'||v