过程、函数、程序包

1过程、函数、程序包2目录•概述•过程•函数•程序包313.1概述•使用过程、函数和程序包代替PL/SQL程序块由许多好处，例如，增强可扩展性、提高模块化水平、可重用性增强、可维护性加强、有利于抽象和数据隐藏、增强安全性等。•使用过程和函数可以允许开发人员增加新的功能，编写自己的例程，可以让用户灵活地扩展数据库的核心能力。这些改变并不是要改变Oracle的工作方式，而是可以让用户建立附加的过程和函数。用户既可以自己使用这些过程和函数，也可以与其他用户共享这些能力。•许多开发人员都非常重视开发模块化代码。用户通过编写小型的、可管理的过程，以便在用户应用中定义单独的处理模块，可以将其组织在一起形成复杂的应用。小型的、分离的过程远远比复杂的过程更容易修改，且可以被更加有效地共享。•由于这些例程命名后保存在数据库中，所以任何应用都能够执行它们(只要它们具有合适的权限)。413.2过程•从本质上来看，过程就是命名的PL/SQL程序块，它可以被赋予参数并存储在数据库中，然后由另外一个应用或PL/SQL例程调用。5创建或替换•[create[orreplace]]•procedureprocedure_name[(parameter[,parameter]…)]•[authid{definer|current_schema}]{is|as}•[pragmaautonomous_transaction;]•[local_declarations]•begin–executablestatements•[exception–exceptionhandlers]•end[procedure_name];6示例7执行过程•创建过程之后即可执行过程。用户既可以从匿名PL/SQL程序块中直接调用，也可以在SQL*Plus中使用execute()函数来执行。Execute()函数可以简写为exec。•首先，使用匿名PL/SQL程序块执行过程。如图13-4所示的示例使用了匿名PL/SQL程序块执行前面创建的my_proc过程。•还可以使用exec函数执行过程。相对来说，使用exec函数执行过程比使用匿名PL/SQL程序块调用my_proc过程更快捷、方便。8使用参数•过程可以进行参数化处理。参数可以是任意合法的PL/SQL类型。参数可以有3种模式，即in、out和inout各种参数的意义分别如下：。–in参数是输入参数，需要通过调用者传入，只能由过程来读取。–out参数是输出参数，能够由过程写入。该参数适合用于过程向调用者返回多个信息。–inout参数同时具有in参数和out参数的特性，过程可以读取和写入它们的值。9参数传递•前面的示例使用了p_parm=100方式传递参数。实际上，在Oracle系统中，可以使用3种方式传递参数，即：–使用名称表示法–使用位置表示法–使用混合表示法10局域声明•如同匿名PL/SQL程序块一样，过程也可以定义局域变量。这些定义紧随可选的参数列表之后。在匿名PL/SQL程序块中，它使用declare关键字开始。在过程声明中，由于使用了createorreplace语法，所以不必再使用declare关键字。在前面的swap过程示例中，l_temp就是一个局域变量。•除了局域变量之外，在局域声明部分还可以定义其他内容。用户可以定义记录、类型、数组，甚至还可以是其它过程。11过程其他操作•将过程的执行权限授予其他用户：•删除过程：•查看过程源代码SQLGRANTEXECUTEONfind_empTOMARTIN;SQLGRANTEXECUTEONswapTOPUBLIC;SQLDROPPROCEDUREswap;SQLselecttextfromuser_sourcewherelower(name)=‘swap’;12常见错误•定义过程的参数类型时，指定参数的长度–createorreplaceprocedureselectall(tablenamevarchar2(50))–去掉50•语句语法错误ifthenendif;•编写存储过程时，最好使用“新建-程序窗口-过程”来编写•查看编译错误–selectposition,textfromuser_errorswherelower(name)='selectall‘•查看过程源代码–selecttextfromuser_sourcewherelower(name)='selectall‘•在PL/SQLDeveloper中调试过程–1、找到过程–2、右键“添加调试信息”–3、测试13过程练习1•传入参数（行数）•实现如下图形的输出,需要异常处理14过程练习2•假设有一个表StudentInfo，有StNumber,StName两个字段，要求写一个存储过程，实现：•1、如果传入的参数v_StNumber在表StudentInfo中存在，则用传入的参数v_StuName更新表相应的StName字段,返回1；•2、如果不存在则新增一条数据，返回2；•3、如果发生错误，返回-1•CreateProcedureStudentInfoADDorEdit(v_StNumberinvarchar2,v_StNameinvarchar2,v_OutvalOUTnumber)•As•…….1513.3函数•函数与过程非常类似，也是数据库中存储的命名PL/SQL程序块。创建函数与创建过程要都遵循同样的规则。函数与过程的安全方式和参数传递也相同。•函数的主要特征是它必须返回一个值。这个返回值既可以是number或varchar2这样简单的数据类型，也可以是PL/SQL数组或对象这样复杂的数据类型。•函数也可以有in、out、inout参数,只不过实际中很少使用out参数16语法•[create[orreplace]]•functionfunction_name[(parameter[,parameter]…)]•returndatatype•[deterministic]{is|as}–[localdeclarations]•begin–executablestatements–[exception–exceptionhandlers]•end[name];17返回值•定义过程和定义函数的主要区别之一是，定义函数必须使用return子句返回数据。在定义函数的函数体的任何地方，用户都可以returnexpression;子句。需要注意的是，这里的expression表达式要等于return数据类型。•如图13-25所示的是一个创建函数的示例。18常见错误•实际上，用户在开发函数时，经常会遇到下面的错误：忘记获取返回值；试图定义不能返回值的函数；定义没有返回数据类型的函数等。•了解这些错误的形式将会帮助用户避免这些问题，并且可以在出现错误时快速修正它们。下面通过几个示例来介绍这些常见的错误。19函数的其他操作•函数的调用：–因为函数要有返回值，所以只能作为表达式的一部分调用•Sql语句中只能调用输入参数的函数，out和inout的函数不可以•Sql语句中调用的函数不能含有insertupdatedelete语句•Sql语句中调用的函数不能使用pl/sql的数据类型–1、selectget_user(‘name’)fromdual;–2、dbms_output.put_line(get_user(‘name’));–3、PL/SQL块，v_value:=get_user(‘name’);•查看函数源代码–Selecttextfromuser_sourcewherelower(name)=‘get_user’;•删除函数–Dropfunctionget_user;20函数练习1•具体要求如下：–一至九月要写为：零壹、零贰、零叁、零肆、零伍、–零陆、零柒、零捌、零玖–十至十二月写为：壹拾、壹拾壹、壹拾贰–比如2005-8-3–应写为：贰零零伍-零捌-零叁–2005-11-22–应写为：二零零伍-壹拾壹-贰拾贰•比如函数名为：date_change();•date_change(‘2005-11-22’)--返回结果：贰零零伍－壹拾壹－贰拾贰•传入的不是日期格式要提示21函数练习2•创建一个函数，实现身份证15位转换为18位的功能•身份证号15位转换为18的算法–在6位行政区划分后插入2位世纪号码（19），构成四位出生年份，得到17位号码；–把17位号码从高位到低位与下列17位数字分别相乘求和(N)[2，4，8，5，10，9，7，3，6，1，2，4，8，5，10，9，7]；–例如身份证号码为：C1C2C3…...C16C17则：N=C17×2+C16×4+......+C1×7；–将Ｎ除以11取余数R,根据余数计算尾数T:•如果R=0,则T=1;如果R=1,则T=0;如果R=2，则T=X；•如果R=3，则T=9；如果R=4，则T=8；依此类推......如果R=10,则T=2；–将尾数T添加到17位号码后即得到18位号码；•需要异常处理:输入非数字或者不够15位需要提示用户22过程和函数的比较过程函数作为PL/SQL语句执行作为表达式的一部分调用在规格说明中不需要包含RETURN子句必须在规格说明中包含RETURN子句不返回任何值必须返回单个值可以包含RETURN语句，但是与函数不同，它不能用于返回值必须包含至少一条RETURN语句23程序包•程序包是对相关过程、函数、变量、游标和异常等对象的封装•程序包由规范和主体两部分组成声明程序包中公共对象。是程序包的公共接口声明程序包私有对象和规范的实现程序包规范主体24创建程序包2-1程序包规范CREATE[ORREPLACE]PACKAGEpackage_nameIS|AS[Publicitemdeclarations][Subprogramspecification]END[package_name];程序包主体CREATE[ORREPLACE]PACKAGEBODYpackage_nameIS|AS[Privateitemdeclarations][Subprogrambodies][BEGINInitialization]END[package_name];25程序包2-2•程序包规范说明–使用CREATEPACKAGE命令进行创建–包含公用对象和类型–声明类型、常量、变量、异常、游标和子程序–可以在没有程序包主体的情况下存在•程序包主体–使用CREATEPACKAGEBODY命令进行创建–包含子程序和游标的定义–包含私有声明–不能在没有程序包规格说明的情况下存在公用项私有项可以在程序包之外引用不能在程序包之外引用是在程序包规格说明中定义的是在程序包主体中定义的用于全局目的用于局部目的26规范•程序包规范是程序包的接口。在规范中定义的所有内容都可以由调用者使用，并且可以由具有这个程序包的execute权限的用户使用。在规范中定义的过程可以被执行，变量也可以被引用，类型也能够被访问。这些是程序包的公共特性。•在如图13-32所示的示例中，将使用两个过程print_ename()和print_sal()，定义一个被称为employee_pkg的程序包。27主体•程序包主体是用户实际编写的子例程，用于实现规范中定义的接口。规范中显示的所有过程和函数都必须在主体中实现。•如图13-34所示的示例显示了如何实现employee_pkg程序包中的print_ename过程和print_sal过程。28有关子程序和程序包的信息•USER_OBJECTS视图包含用户创建的子程序和程序包的信息•USER_SOURCE视图存储子程序和程序包的源代码SELECTobject_name,object_typeFROMUSER_OBJECTSWHEREobject_typeIN('PROCEDURE','FUNCTION','PACKAGE','PACKAGEBODY');SELECTline,textFROMUSER_SOURCEWHERENAME='TE