oracle子程序和程序包.ppt

第七章 子程序和程序包 回顾 游标用于处理查询结果集中的数据游标类型有 隐式游标 显式游标和REF游标隐式游标由PL SQL自动定义 打开和关闭显式游标用于处理返回多行的查询显式游标可以删除和更新活动集中的行要处理结果集中所有记录时 可使用循环游标在声明REF游标时 不需要将SELECT语句与其关联 目标 了解和使用子程序了解和使用程序包 子程序2 1 命名的PL SQL块 编译并存储在数据库中 子程序的各个部分 声明部分可执行部分异常处理部分 可选 子程序的分类 过程 执行某些操作函数 执行操作并返回值 子程序2 2 子程序的优点 模块化将程序分解为逻辑模块可重用性可以被任意数目的程序调用可维护性简化维护操作安全性通过设置权限 使数据更安全 过程8 1 过程是用于完成特定任务的子程序例如 前往售票厅 询问关于车票的信息 排队等候 在柜台购买车票 过程8 2 创建过程的语法 CREATE ORREPLACE PROCEDURE IS ASBEGIN EXCEPTION END 创建过程 可指定运行过程需传递的参数 处理异常 包括在过程中要执行的语句 过程8 3 createorreplaceproceduretest4 v idvarchar2 isv enameemp ename type v salemp sal type beginselectename salintov ename v salfromempwhereempno v id dbms output put line v ename v sal end 过程8 4 执行过程的语法 EXECUTEprocedure name parameters list SQL SETSERVEROUTPUTONSQL EXECproc stu 007 过程8 5 过程参数的三种模式 IN用于接受调用程序的值默认的参数模式OUT用于向调用程序返回值INOUT用于接受调用程序的值 并向调用程序返回更新的值 过程8 6 createorreplaceproceduretest5 xinnumber yinnumber zoutnumber asbeginz x y end 过程8 7 createorreplaceproceduretest6 ainoutnumber binoutnumber isbegindbms output put line a dbms output put line b a 100 b 100 end SQL SETSERVEROUTONSQL declarenum1number 100 num2number 500 beginp swap num1 num2 dbms output put line num1 num1 dbms output put line num2 num2 end 过程8 8 将过程的执行权限授予其他用户 删除过程 SQL GRANTEXECUTEONproc stuTOSCTOO SQL GRANTEXECUTEONproc swapTOPUBLIC SQL DROPPROCEDUREproc swap 函数4 1 函数是可以返回值的命名的PL SQL子程序 创建函数的语法 CREATE ORREPLACE FUNCTION param1 param2 RETURNIS AS localdeclarations BEGINExecutableStatements RETURNresult EXCEPTIONExceptionhandlers END 函数4 2 定义函数的限制 函数只能接受IN参数 而不能接受INOUT或OUT参数形参不能是PL SQL类型函数的返回类型也必须是数据库类型访问函数的两种方式 使用PL SQL块使用SQL语句 函数4 3 创建函数 查看函数 varnamevarchar2 exec name funl 函数名字 从SQL语句调用函数 CREATEORREPLACEFUNCTIONfunc helloRETURNVARCHAR2ISBEGINRETURN 朋友 您好 END SQL SELECTfunc helloFROMDUAL 函数4 4 createorreplacefunctionget sal v enameinvarchar2 returnnumberisv salnumber beginselectsalintov salfromempwhereupper ename upper v ename returnv sal exceptionwhenno data foundthenraise application error 20000 员工不存在 end 过程和函数的比较 程序包 程序包是对相关过程 函数 变量 游标和异常等对象的封装程序包由规范和主体两部分组成 声明程序包中公共对象 包括类型 变量 常量 异常 游标规范和子程序规范等 声明程序包私有对象和实现在包规范中声明的子程序和游标 程序包 规范 主体 创建程序包2 1 程序包规范CREATE ORREPLACE PACKAGEpackage nameIS AS Publicitemdeclarations Subprogramspecification END package name 程序包主体CREATE ORREPLACE PACKAGEBODYpackage nameIS AS Privateitemdeclarations Subprogrambodies BEGINInitialization END package name 创建程序包2 2 createorreplacepackagepack stuAsv globalidchar 8 procedureproc stu v idvarchar2 functionfunc stu v idvarchar2 returnvarchar2 endpack stu createorreplacepackagebodypack stuAsprocedureproc stu v idvarchar2 Isv namet student f name type v deptt student f department type v classt student f class type beginv globalid v id selectf name f department f classintov name v dept v classfromt studentwheref id v id endproc stu functionfunc stu v idvarchar2 returnvarchar2As endfunc stu endpack stu 程序包的优点 模块化更轻松的应用程序设计信息隐藏新增功能性能更佳 程序包中的游标2 1 游标的定义分为游标规范和游标主体两部分在包规范中声明游标规范时必须使用RETURN子句指定游标的返回类型RETURN子句指定的数据类型可以是 用 ROWTYPE属性引用表定义的记录类型程序员定义的记录类型 程序包中的游标2 2 SQL CREATEORREPLACEPACKAGEpack curAsCursorcur stu stuclassnumber returnt student rowtype procedureproc stu stuclassnumber end SQL CREATEORREPLACEPACKAGEBODYpack curASCursorcur stu stuclassnumber returnt student rowtypeIsselect fromt studentwheref class stuclass procedureproc stu stuclassnumber Asrec stut student rowtype beginOpencur stu stuclass loopFetchcur stuIntorec stu Exitwhencur stu NotFound dbms output put line 学生姓名 rec stu f name endloop endproc stu end 有关子程序和程序包的信息 USER OBJECTS视图包含用户创建的子程序和程序包的信息USER SOURCE视图存储子程序和程序包的源代码 SELECTobject name object typeFROMUSER OBJECTSWHEREobject typeIN PROCEDURE FUNCTION PACKAGE PACKAGEBODY SELECTline textFROMUSER SOURCEWHERENAME PACK CUR 总结 子程序是命名的PL SQL块 可带参数并可在需要时随时调用有两种类型的PL SQL子程序 即过程和函数过程用户执行特定的任务 函数用于执行任务并返回值程序包是对相关类型 变量 常量 游标 异常 过程和函数等对象的封装程序包由两部分组成 即包规范和包主体使用程序包的优点是 模块化 更轻松的程序设计 信息隐藏 新增功能以及性能更佳