oracle存储过程函数和程序包

专题七过程、函数和程序包

回顾游标就是指向上下文区的句柄或指针。游标有两种类型：显式游标、隐式游标。四个游标属性：SQL％FOUND、SQL％NOTFOUND、SQL％ROWCOUNT、SQL％ISOPEN显式游标的使用步骤：４个记录变量和%ROWTYPE带参数的游标游标FOR循环（循环游标）游标中的更新和删除REF游标教学目标掌握过程的用法掌握函数的用法理解过程与函数的相同点和不同点理解程序包的概念并能熟练应用工作任务

用无参过程实现“HelloWorld！”程序用带输入参数的过程向表中插入记录用带输出参数的过程查询表中的记录数使用带输入输出参数的过程查询记录是否存在使用函数查询部门信息使用程序包封装过程和函数相关实践知识从开始菜单中打开SQL*Plus工具，以SCOTT用户的身份登录到数据库

不带参数的过程2-1输入以下代码，创建一个最简单的过程功能:显示”HelloWorld!”CREATEORREPLACEPROCEDUREsp_helloWorldASBEGIN DBMS_OUTPUT.PUT_LINE('HelloWorld!');ENDsp_helloWorld;不带参数的过程2-2执行过程EXECUTEsp_helloWorld;执行结果:删除过程DROPPROCEDUREsp_helloWorld;带输入参数的过程3-1实现的功能：向表dept中插入一条记录创建带输入参数的过程，代码为：带输入参数的过程3-2CREATEORREPLACEPROCEDUREsp_dept_insert(i_deptnoNUMBER,i_dnameVARCHAR2,i_locVARCHAR2)ASBEGININSERTINTOdeptVALUES(i_deptno,i_dname,i_loc);COMMIT;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('添加失败!原因为:'||SQLERRM);ROLLBACK;ENDsp_dept_insert;带输入参数的过程3-3分别输入两组数据来执行过程，结果如下：带输出出参数数的过过程3-1实现功功能：：通过过输出出参数数count来来得到到dept表中中的记记录数数创建带带输出参参数的过程程CREATEORREPLACEPROCEDUREsp_getcount(o_countOUTNUMBER)ASBEGINSELECTCOUNT(*)INTOo_countFROMdept;ENDsp_getcount;带输输出出参参数数的的过过程程3-2编写写一一段段匿匿名名的的PL/SQL块块来来执执行行过过程程DECLAREcntNUMBER;BEGINsp_getcount(cnt);DBMS_OUTPUT.PUT_LINE('dept表表中中的的记记录录数数为为:'||cnt);END;带输输出出参参数数的的过过程程3-3输出出结结果果：：带INOUT参参数数的的过过程程2-1实现现功功能能:查查询询某某个个部部门门名名称称在在表表dept中中是是否否已已经经存存在在创建建带带INOUT参参数数的过过程程，，代代码码为为：：CREATEORREPLACEPROCEDUREsp_dept_dname_exist(io_valueINOUTVARCHAR2)ISl_countNUMBER;BEGINSELECTCOUNT(*)INTOl_countFROMdeptWHEREdname=io_value;IF(l_count>0)THENio_value:='存存在在';ELSEio_value:='不不存存在在';ENDIF;ENDsp_dept_dname_exist;带INOUT参参数数的的过过程程2-2执行行过过程程：：输出出结结果果：：部门门名名称称ACCOUNTING存存在在!DECLAREl_iotestvarchar2(20):='ACCOUNTING';BEGINsp_dept_dname_exist(l_iotest);DBMS_OUTPUT.PUT_LINE('部部门门名名称称ACCOUNTING'||l_iotest||'!');END;函数数4-1实现现功功能能：：按部部门门编编号号查查询询出出表表dept中中的的部部门门名名称称创建建一一个个函数数，代代码码为为：：函数数4-2CREATEORREPLACEFUNCTIONf_dept_getname_byno(i_deptnoNUMBER)RETURNVARCHAR2ASl_dnameVARCHAR2(14);BEGINSELECTdnameINTOl_dnameFROMdeptWHEREdeptno=i_deptno;RETURNl_dname;EXCEPTIONWHENNO_DATA_FOUNDTHENRETURN'错错误误!该该编编号号的的部部门门不不存存在在!';ENDf_dept_getname_byno;函数数4-3函数数的的调调用用及其其输输出出结结果果：：函数数4-4删除除函函数数DROPFUNCTIONf_dept_getname_byno过程程与与函函数数小小结结2-1共同同点点：：两两者者的的实实质质都都是是已已命名名的的PL/SQL程程序序块块，即即子程程序序，它它们们是是子子程程序序的的两两种种类类型型，，存存储储在在数数据据库库中中，，可可以以从从任任何何数数据据库库客客户户端端和和前前台台应应用用程程序序中中调调用用它它们们。。过程程与与函函数数小小结结2-2不同同点点：：过程函数参数模式可以是IN、OUT或INOUT参数模式只能是IN模式在语法规范中不包含RETURN子句在语法规范中必须包含RETURN子句在可执行语句部分可以有RETURN语句，但其后不能加任何表达式在可执行语句部分至少应该包含一条RETURNexpression语句可以用EXECUTE语句来执行不能用EXECUTE语句来执行程序序包包5-1利用用程序序包包封装装过过程程sp_dept_insert和和函函数数f_dept_getname_byno程序序包包规规范范部部分分的的代代码码：：CREATEORREPLACEPACKAGEpkg_deptASPROCEDUREsp_dept_insert(i_deptnoNUMBER,i_dnameVARCHAR2,i_locVARCHAR2);FUNCTIONf_dept_getname_byno(i_deptnoNUMBER)RETURNVARCHAR2;ENDpkg_dept;程序序包包5-2程序序包包主主体体部部分分的的代代码码CREATEORREPLACEPACKAGEBODYpkg_deptAS--过程程sp_dept_insertPROCEDUREsp_dept_insert(i_deptnoNUMBER,i_dnameVARCHAR2,i_locVARCHAR2)ASBEGININSERTINTOdeptVALUES(i_deptno,i_dname,i_loc);COMMIT;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('添加失失败!原原因为:'||SQLERRM);ROLLBACK;ENDsp_dept_insert;程序包5-3--函数数f_num_rangeFUNCTIONf_dept_getname_byno(i_deptnoNUMBER)RETURNVARCHAR2ASl_dnameVARCHAR2(14);BEGINSELECTdnameINTOl_dnameFROMdeptWHEREdeptno=i_deptno;RETURNl_dname;EXCEPTIONWHENNO_DATA_FOUNDTHENRETURN'错误误!该编编号的部部门不存存在!';ENDf_dept_getname_byno;ENDpkg_dept;程序包5-4执行程序序包中的的过程和和函数：程序包5-5删除程序序包：只删除程程序包主主体：DROPPACKAGEBODYpkg_dept;删除整个个程序包包（规范范+主体体）：DROPPACKAGEpkg_dept;小结：程程序包的的优点使用程序序包的优优点：信息隐藏藏模块化对多态的的支持性能更佳佳过程返回回结果集集2-1在Oracle中的过过程不能能象SQLSERVER那那样直接接返回结结果集，，而必须须借助于于REF游标程序包规规范中的的代码:CREATEORREPLACEPACKAGEpkg_deptASTYPEdeptcursorISREFCURSOR;PROCEDUREsp_dept_getall(dept_curOUTdeptcursor);ENDpkg_dept;过程返回回结果集集2-2程序包主主体中的的代码:执行过程程:CREATEORREPLACEPACKAGEBODYpkg_deptASPROCEDUREsp_dept_getall(dept_curOUTdeptcursor)ISBEGINOPENdept_curFORSELECT*FROMdept;ENDsp_dept_getall;ENDpkg_dept;VARIABLEtest_curREFCURSOR;EXECUTEpkg_dept.sp_dept_getall(:test_cur);PRINTtest_cur;OEM中中管理过过程、函函数、程程序包请老师用用浏览器器打开OEM，，演示在在OEM中管理过程程、函数数、程序序包总结过程和函函数都是是命名的的PL/SQL程序块块，合称称子程序序过程有三三种参数数模式：：IN、、OUT和INOUT过程用户户执行特特定的任任务，函函数用于于执行任任务并返返回值过程与函函数的相相同点和和不同点点程序包是是对过程程、函数数、变量量、常量量、游标标、异常常及PL/SQL数据据类型等等的封装装程序包由由两部分分组成，，即包规规范和包包主体使用程序序包的优优点过程的定定义定义：过程是一一组为了了完成特定定功能的、符合合数据库库程序脚脚本规范范的程序序，经编译后存储在数数据库中中，然后后由一个个应用程程序或其其他的PL/SQL程程序调用用。从根根本上讲讲，过程程就是命名的PL/SQL程程序块。创建过程程的语法法CREATE[ORREPLACE]PROCEDUREprocedure_name[(parameter_list)]{AS|IS}[local_declarations]BEGINexecutable_statements[EXCEPTIONexception_handlers]END[procedure_name];执行过程程在SQL提示符符下：：EXEC[UTE]procedure_name(parameters_list)在其它的的过程、、函数或或匿名的的PL/SQL块中的的可执行行语句部部分执行行过程，，直接写写过程的的名称（（如有参参数需带带上）即即可删除过程程删除过程程的语法法:DROPPROCEDUREprocedure_name;过程的参参数模式式Oracle中中过程程的参数数模式有有三种：：IN、、OUT和INOUT，，即输入入、输出出和输入入输出定义参数数的语法法：parameter_name[IN|OUT|INOUT]DATATYPE[{:=|DEFAULT}expression]IN模式式参数IN模式式是输入入模式，，可以传传递输入入参数IN模式式是默认认模式，，如果未未指定参参数的模模式，则则该参数数是IN模式的的可以在参参数列表表中为IN参数数赋予一一个默认认值OUT模模式参数数OUT模模式是输输出模式式，可以以传递输输出参数数OUT模模式的参参数，则则必须明明确指定定，即即OUT必须注注明在返回到到调用环环境之前前，应该该先给OUT模模式的参参数赋值值不能为OUT模模式的参参数赋默默认值INOUT模模式参数数INOUT模模式是一一种比较较特殊的的模式，，它兼有有IN模模式和OUT模模式的特特点INOUT模模式的参参数，则则必须明明确指定定不能为INOUT模模式的参参数赋默默认值函数的定定义定义函数的实实质也是是数据库库中已命名的PL/SQL程程序块。它的主主要特性性是函数数能且只能能返回一一个值。创建函数数的语法法CREATE[ORREPLACE]FUNCTIONfunction_name[(parameter_list)]RETURNDATATYPE{AS|IS}[local_declarations]BEGINexecutable_statements[EXCEPTIONexception_handlers]END[function_name];函数定义义的要点点创建函数数时通过过RETURN子句来来定义函函数的返返回类型型在函数体体的任何何地方用用户都可可以通过过RETURNexpression语句从从函数返返回，但但表达式式的类型型一定要要与RETURN子句句中定义义的数据据类型一一致。函数的参参数模式式只能是是IN模模式的，，而不能能是OUT或INOUT模模式的。。调用函数数在SQL提示符符下：SELECTfunction_namefromdual;在其它的的过程、、函数或或匿名的的PL/SQL块中的的可以用用一个与与函数的的返回类类型相同同的变量量来接收收该函数数不能用EXECUTE语句来来调用函函数删除函数数删除函数数的语法法：DROPFUNCTIONfunction_name;程序包程序包是是对过程程、函数数、变量量、常量量、游标