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;程序包包程序包包是对对过程程、函函数、、变量量、常常量、、游标标、异异常及及PL/SQL数据据类型型等的的封装装，是是一种种数据据库对对象。。它由两