8 过程函数程序包

1第8章过程、函数和程序包到目前为止，所创建的PL/SQL程序都是匿名的。这些匿名的程序块没有被存储，在每次执行后，都不可以被重新使用。因此，每次运行匿名程序块时，都需要先编译然后再执行。在很多时候，都需要保存PL/SQL程序块，以随后可以重新使用。这也意味着，程序块需要一个名称，这样在调用或引用它时，系统就可以找到这个特定的程序块。创建并命名的程序块称为“命名程序单元”或“子程序”。由于存储代码的位置、代码所执行的任务类型以及调用方式的不同，命名PL/SQL程序单元分为：存储过程、函数和程序包三种。2本章学习目标：•了解Oracle匿名程序块•创建Oracle存储过程•调用存储过程•理解存储过程中各种形式的参数•修改存储过程•为过程添加局部变量和子过程•创建Oracle函数•调用Oracle函数•创建程序包•创建程序包的公有成员和私有成员•理解程序包中重载的过程和函数•理解程序单元之间的依赖性38.1存储过程存储过程是一种命名PL/SQL程序块，它可以被赋予参数，存储在数据库中，可以被用户调用。由于存储过程是已经编译好的代码，所以在调用的时候不必再次进行编译，从而提高了程序的运行效率。另外，使用存储过程可以实现程序的模块化设计。过程的基本操作包括创建、查看、修改、调用和删除。1.创建过程create[orreplace]procedureprocedure_name(arg1[mode],datatype,……)is|as[localdeclaration]beginexecutablestatements[exceptionexceptionhandler]end[procedurename]P200例子2.调用过程一旦创建存储过程后，就可以任意调用该存储过程。①直接写过程名(在匿名块中调用)②Execute过程名(在sql*plus直接调用)3.修改过程①可以在企业管理器修改②使用带有orreplace选项的重建命令修改4.查看过程①可以在企业管理器查看②通过user_source数据字典查看5.删除过程①可以在企业管理器删除②dropprocedure过程名；6.参数在创建存储过程时，需要考虑的第一件重要事情就是使过程可以灵活应用，以便重新使用它们。通过“参数”可以使程序单元变得很灵活。参数是一种向程序单元输入、输出数据的机制。存储过程可以接收和返回0个或多个参数。Oracle有三种参数模式：•参数模式in–in表示在调用过程的时候，实际参数的取值被传递给该过程，形式参数被认为是只读的，当过程结束时，控制会返回控制环境，实际参数的值不会改变。同时也是参数的缺省模式。P202例子调用存储过程时，有三种向其传递参数的方法：1.名称表示法：为各个参数传递参数值时，指定传入数值的参数名。procedure(形参名=实参值[,形参名=实参值,…])使用这种方法传递参数值时，由于指定各个参数获取的值，因此用户不需要考虑创建过程时定义的参数顺序。2.位置表示法：用户提供的参数值必须与过程中定义的参数顺序相对应。3.混合表示法：位置表示法和名称表示法各有自己的优缺点，为了弥补两者的缺陷，可以采用混合表示法。当用户使用名称表示法后，后续的参数必须使用名称表示法。•参数模式out–out在调用过程时实际参数的取值都将被忽略，在过程内部形式参数只能是被赋值，而不能从中读取数据，在过程结束后形式参数的内容将被赋予实际参数P203例子•参数模式inout–inout这种模式是in和out的组合，在过程内部实际参数的值会传递给形式参数，形式参数的值可读也可写，过程结束后，形式参数的值将赋予实际参数。P204例子默认值过程的参数也可以像表中的列一样具有默认值。这样当调用该过程时，如果没有向该参数传入值，则该参数将使用定义的默认值。P205例子注意：out和inout参数不具有默认值。7.局部变量和子过程如同匿名PL/SQL程序块一样，过程也可以定义局部变量。在过程中定义的变量不必再使用declare关键字，而是写在is或as的后面。过程内定义的局部变量可以在过程内的任何地方使用。在局部变量的定义部分，不仅仅可以定义变量，还可以定义记录类型、游标等，甚至其它过程。练习：编写过程计算emp表指定部门（默认时为10号部门）的工资总和，并统计其中的职工数量。调用此过程显示30号和10号部门的工资总和及人数。168.2函数函数与过程非常相似，它也是一种存储在数据库中的命名程序块，也可以接接收输入值，并进行逻辑处理后将处理结果返回给调用者。函数与过程之间的主要区别在于，函数必须有返回值，并可以作为一个表达式的一部分，并不能作为一个完整的语句使用。函数的基本操作包括创建、查看、修改、调用和删除。1.创建函数create[orreplace]functionfunction_name(arg1[mode],datatype,…)returndatatypeis|as[localdeclaration]beginexecutablestatements[exceptionexceptionhandler]end[functionname]从以上的语法结构可以看出，定义存储过程和函数之间的主要区别之一是，函数必须包含return数据类型子句，用于指定函数返回的数据类型。在函数体内，用户可以在任何地方使用return表达式语句返回值，其返回值的数据类型必须是函数头中声明的。P207例子2.调用函数不能像调用过程那样作为一个独立的语句调用，因为函数具有返回值，必须作为表达式的一部分来调用。3.修改函数①可以在企业管理器修改②使用带有orreplace选项的重建命令修改4.查看函数①可以在企业管理器查看②通过user_source数据字典查看5.删除函数①可以在企业管理器删除②dropfunction过程名；218.3程序包在PL/SQL程序中，为了输出运行结果，在程序的代码中使用了DBMS_OUTPT.PUT_LINE语句。事实上，这是调用程序包DBMS_OUTPUT中的PUT_LINE方法。DBMS_OUTPUT程序包主要的功能就是负责在PL/SQL程序中的输入和输出。什么是程序包呢？程序包就是一组相关的过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合。一个程序包通常由两部分组成：规范和主体。规范用于声明包内的数据类型、变量、常量、游标、异常处理以及过程和函数等元素。主体是规范部分声明的过程、函数的具体实现，以及所有的私有数据。1.规范程序包规范是程序包的接口。也就是说，在规范中定义的所有内容都可以由调用者使用。在规范中定义的过程可以被调用者调用执行，变量可以被引用。这些就组成了程序包的公共接口。对于程序单元，规范就像一个说明书，它说明了在程序包中哪些过程或函数可以使用，如何使用。创建程序包必须首先创建包规范。创建程序包规范语法：create[orreplace]packagepackage_name{as|is}public_variable_declarations|public_type_declarations|public_exception_declarations|public_cursor_declarations|function_declarations|procedure_specificationsend[package_name]P209例子在程序包规范中没有为其提供任何代码，只是简单定义了它们的名称和参数。而过程和函数体都被排除在外。因为规范仅是显示程序包中包括了哪些内容，而具体的实现则包含在程序包的主体部分。执行程序包内的过程与执行独立的过程相似，只是在调用程序包内的过程时，需要使用程序包加以限定，即在过程名前添加过程所在包的名称。2.主体程序包主体包含了在规范中声明的过程和函数的代码。程序包主体的名称必须与规范的名称相同，这个相同的名称将规范和主体结合在一起。另外，程序包主体中的过程和函数的头部代码必须与规范中声明的过程或函数完全匹配。创建包主体语法如下：create[orreplace]packagebodypackage_name{as|is}private_variable_declarations|private_type_declarations|private_exception_declarations|private_cursor_declarations|function_declarations|procedure_specificationsend[package_name]P209例子•创建包createorreplacepackagep_empasversionvarchar2(11):='version1.0';authorvarchar2(15):='buaasem108xk';procedureemp_info(v_deptnoemp.deptno%type);functionbirthday(v_empnoemp.empno%type)returnbinary_integer;endp_emp;/createorreplacepackagebodyp_empasprocedureemp_info(v_deptnoemp.deptno%type)ascursorc_empisselect*fromempwheredeptno=v_deptno;v_empc_emp%rowtype;beginopenc_emp;loopfetchc_empintov_emp;exitwhenc_emp%notfound;dbms_output.put_line(v_emp.empno||','||v_emp.ename||','||v_emp.job||','||to_char(v_emp.hiredate,'YYYY-MM-DD')||','||v_emp.sal||','||v_emp.comm);endloop;closec_emp;end;functionbirthday(v_empnoemp.empno%type)returnbinary_integerassys_datevarchar2(5);emp_datevarchar2(5);tempdate;lenbinary_integer;beginselecthiredateintotempfromempwhereempno=v_empno;emp_date:=substr(to_char(temp,'YYYY-MM-DD'),6,5);sys_date:=substr(to_char(SYSDATE,'YYYY-MM-DD'),6,5);foriin1..5loopifsubstr(emp_date,i,1)substr(sys_date,i,1)thenreturn-1;endif;endloop;return1;endbirthday;endp_emp;/3.私有成员程序包中的项目也分为公有成员和私有成员两种。私有成员只能被包内的函数和过程访问，共有成员除了可以被包中的函数、过程访问外，还可以被包外的PL/SQL程序访问。私有过程和函数私有过程和函数的作用是为程序包内的共有过程和函数提供服务，它只可以被程序包内的公有过程和函数调用。私有过程和函数的方法只在程序包主体中定义，而没有在程序规范内声明。程序变量可以在程序包规范和程序包主体内定义变量。定义在规范中的变量可以像规范中的过程和函数一样被引用，称公有变量。在程序包主体中定义的变量只能被程序包内的过程来访问，称私有变量。4.实例化在程序包中还可以包含一段特殊的代码，这段代码在用户第一次调用程序包时只允许一次，这段代码称实例化代码。实例化代码只能被调用一次，并且会字段进行。实例化代码是程序包主体中的一个匿名程序块，能够包含任何合法的PL/SQL命令。5.重载程序包的重载是指在一个程序包中，多个程序单元使用相同的名称。单独的过程和函数必须具有唯一的