游标、存储过程和触发器

第10章游标、存储过程和触发器,课程描述介绍Oracle数据库程序设计中经常会用到的3个概念，即游标、存储过程和触发器。,本章知识点,游标存储过程管理触发器管理,游标,游标的基本概念游标控制语句游标属性游标FOR循环,游标的基本概念,游标示意图,游标的基本概念,使用显式游标（1）声明游标。（2）打开游标。（3）读取数据。（4）关闭游标。,游标的基本概念,隐式游标【例】使用SELECT语句声明隐式游标，从HR.Departments表中读取Department_name字段的值到变量DepName：SETServerOutputON;DECLAREDepNameHR.Departments.Department_Name%Type;BEGINSELECTDepartment_nameINTODepNameFROMHR.DepartmentsWHEREDepartment_ID=10;dbms_output.put_line(DepName);END;,类型描述符,游标控制语句,（1）声明游标语句CURSOR：DECLARECURSOR()IS;【例】声明一个游标MyCur，读取指定类型的用户信息：DECLARECURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;,游标控制语句,（2）打开游标语句OPEN：OPEN();【例】打开游标MyCur，读取类型为1的用户信息：OPENMyCur(1);,游标控制语句,（3）游标取值语句FETCH。游标取值语句FETCH的基本语法结构如下：FETCHINTO;【例】在打开的游标MyCur的当前位置读取数据：FETCHMyCurINTOvarI,dvarName;（4）关闭游标语句CLOSE：CLOSE;【例】关闭游标MyCur：CLOSEMyCur;,游标控制语句,【例】下面介绍一个完整的游标应用实例：/*打开显示模式*/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;-结束程序体,游标属性,（1）%ISOPEN属性【例】下面的代码演示当使用未打开的游标时，将会出现错误：/*打开显示模式*/SETServerOutputON;DECLARE-开始声明部分varNameVARCHAR2(50);-声明变量,用来保存游标中的用户名varIdNUMBER;-声明变量,用来保存游标中的用户编号-定义游标,varType为参数,指定用户类型编号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN-开始程序体FETCHMyCurINTOvarId,varName;-读取当前游标位置的数据CLOSEMyCur;-关闭游标dbms_output.put_line(用户编号:|varId|,用户名:|varName);-显示读取的数据END;-结束程序体,游标属性,【例】修改上面的程序，在使用游标之前，调用%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;-结束程序体,游标属性,（2）%FOUND属性和%NOTFOUND属性【例】%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;-结束程序体,游标属性,（3）%ROWCOUNT属性【例】只读取前2行记录：/*打开显示模式*/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);-显示读取的数据IFMyCur%ROWCOUNT=2THENEXIT;ENDIF;FETCHMyCurINTOvarId,varName;-读取当前游标位置的数据ENDLOOP;CLOSEMyCur;-关闭游标END;-结束程序体,游标FOR循环,【例】声明记录类型User_Record_Type和定义记录变量var_UserRecord：TYPEUser_Record_TypeISRECORD(UserIdUsers.UserId%Type,UserNameUsers.UserName%Type);var_UserRecordUser_Record_Type;,游标FOR循环,【例】PL/SQL记录可以与游标结合使用：/*打开显示模式*/SETServerOutputON;DECLARE-开始声明部分/*声明记录类型*/TYPEUser_Record_TypeISRECORD(UserIdUsers.UserId%Type,UserNameUsers.UserName%Type);/*定义记录变量*/var_UserRecordUser_Record_Type;-定义游标,varType为参数,指定用户类型编号CURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;,游标FOR循环,BEGIN-开始程序体IFMyCur%ISOPEN=FALSEThenOPENMyCur(1);ENDIF;LOOPFETCHMyCurINTOvar_UserRecord;-读取当前游标位置的数据到记录变量var_UserRecordEXITWHENMyCur%NOTFOUND;-当游标指向结果集结尾时退出循环/*显示保存在记录变量var_UserRecord中的数据*/dbms_output.put_line(用户编号:|var_UserRecord.UserId|,用户名:|var_UserRecord.UserName);ENDLOOP;CLOSEMyCur;-关闭游标END;-结束程序体,游标FOR循环,【例】典型游标FOR循环的例子：/*打开显示模式*/SETServerOutputON;DECLARECURSORMyCur(varTypeNUMBER)ISSELECTUserId,UserNameFROMUsersWHEREUserType=varType;BEGIN-开始程序体FORvar_UserRecordINMyCur(1)LOOP/*显示保存在记录变量var_UserRecord中的数据*/dbms_output.put_line(用户编号:|var_UserRecord.UserId|,用户名:|var_UserRecord.UserName);ENDLOOP;END;-结束程序体,10.2存储过程管理,过程一种基本的存储过程，由过程名、参数和程序体组成。函数与过程类似，只是函数有返回值。程序包一组相关的PL/SQL过程和函数，由包名、说明部分和包体组成。FUNCTION,PROCEDURE和PACKAGE区别function和procedure是PL/SQL代码的集合，通常为了完成一个任务。procedure不需要返回任何值而function将返回一个值，Package是为了完成一个商业功能的一组function和proceudre的集合,过程,CREATEPROCEDURE语句来创建过程：CREATEORREPLACEPROCEDUREIS|ASBEGINEND;参数声明格式：IN|OUT|INOUT:=,过程,【例】创建示例过程ResetPwd，此过程的功能是将表Users中指定用户的密码重置为111111：CREATEORREPLACEPROCEDUREUserMan.ResetPwd(varUserIdINNUMBER)ASBEGINUPDATEUsersSETUserPwd=111111WHEREUserId=varUserId;END;,过程,存储过程管理,过程,添加存储过程,过程,修改存储过程,函数,CREATEFUNCTION语句来创建函数：CREATEORREPLACEFUNCTIONRETURNIS|ASBEGINRETURNEND;,CREATEORREPLACEPROCEDUREIS|ASBEGINEND;,函数,【例】下面介绍一个示例函数GetPwd，此函数的功能是在表Users中根据指定的用户名返回该用户的密码信息：CREATEFUNCTIONUserMan.GetPwd(nameINUsers.UserName%Type)RETURNUsers.UserPwd%TypeASoutpwdUsers.UserPwd%Type;BEGINSELECTUserPwdINTOoutpwdFROMUsersWHEREUserName=name;RETURNoutpwd;END;,函数,函数管理,函数,添加函数,函数,修改函数,程序包,CREATEPACKAGE语句来创建包的说明部分：CREATEORREPLACEPACKAGEIS|ASEND;-可以包括类型、变量、过程、函数和游标的说明,程序包,【例】下面介绍一个示例创建程序包MyPack，它包含前面2小节中的过程ResetPwd和函数GetPwd：CREATEORREPLACEPACKAGEUserMan.MyPackISPROCEDUREResetPwd(UserIdINNUMBER);FUNCTIONGetPwd(nameINUsers.UserName%Type)RETURNUsers.UserPwd%Type;ENDMyPack;,程序包,程序包管理,程序包,添加程序包,程序包,CREATEPACKAGEBODY语句来创建包体部分：CREATEPACKAGEBODYIS|ASEND;,程序包,【例】下面创建程序包MyPack的包体体部分：CREATEPACKAGEBODYUserMan.MyPackISPROCEDUREResetPwd(varUserIdINNUMBER)ASBEGINUPDATEUsersSETUserPwd=111111WHEREUserId=varUserId;END;FUNCTIONGetPwd(nameINUsers.UserName%Type)RETURNUsers.UserPwd%TypeASoutpwdUsers.UserPwd%Type;BEGINSELECTUserPwdINTOoutpwdFROMUsersWHEREUserName=|name|;RETURNoutpwd;END;ENDMyPack;,程序包,程序包管理查看MyPack程序包体,程序包,添加程序包,程序包,调用程序包中的过程.调用程序包中的函数：.【例】调用UserMan.MyPack.GetPwd函数，返回指定用户的密码信息：SETServerOutputON;DECLAREvarPwdUsers.UserPwd%Type;BEGINvarPwd:=UserMan.MyPack.GetPwd(Admin);dbms_output.put_line(varPwd);END;,程序包,DROPPACKAGEBODY命令删除程序包体：DROPPACKAGEBODYUserMan.MyPack;DROPPACKAGE命令删