Oracle数据库子程序和程序包课件

子程序和程序包子程序和程序包2回顾游标用于处理查询结果集中的数据游标类型有：隐式游标、显式游标和REF游标隐式游标由PL/SQL自动定义、打开和关闭显式游标用于处理返回多行的查询显式游标可以删除和更新活动集中的行要处理结果集中所有记录时，可使用循环游标在声明REF游标时，不需要将SELECT语句与其关联2回顾游标用于处理查询结果集中的数据3目标创建和使用子程序创建和使用程序包3目标创建和使用子程序4子程序2-1命名的PL/SQL块，编译并存储在数据库中。子程序的各个部分：声明部分可执行部分异常处理部分(可选)子程序的分类：过程－执行某些操作函数－执行操作并返回值4子程序2-1命名的PL/SQL块，编译并存储在数据库5子程序2-2子程序的优点：模块化将程序分解为逻辑模块可重用性可以被任意数目的程序调用可维护性简化维护操作安全性通过设置权限，使数据更安全5子程序2-2子程序的优点：6过程8-1过程是用于完成特定任务的子程序

例如：前往售票厅询问关于车票的信息排队等候在柜台购买车票6过程8-1过程是用于完成特定任务的子程序前往售票厅询问7过程

8-2创建过程的语法：CREATE[ORREPLACE]PROCEDURE<procedurename>[(<parameterlist>)]IS|AS<localvariabledeclaration>BEGIN<executablestatements>[EXCEPTION<exceptionhandlers>]END;创建过程，可指定运行过程需传递的参数处理异常包括在过程中要执行的语句7过程8-2创建过程的语法：创建过程，可指定运行过程需传递8过程

8-3CREATEORREPLACEPROCEDUREfind_emp(emp_noNUMBER)ASempnameVARCHAR2(20);BEGINSELECTenameINTOempnameFROMEMPWHEREempno=emp_no;DBMS_OUTPUT.PUT_LINE('雇员姓名是'||empname);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('雇员编号未找到');ENDfind_emp;/8过程8-3CREATEORREPLACEPROCE9过程

8-4过程参数的三种模式：IN用于接受调用程序的值默认的参数模式OUT用于向调用程序返回值INOUT用于接受调用程序的值，并向调用程序返回更新的值9过程8-4过程参数的三种模式：10过程

8-5SQL>CREATEORREPLACEPROCEDUREitemdesc(item_codeINVARCHAR2)ISv_itemdescVARCHAR2(5);BEGINSELECTitemdescINTOv_itemdescFROMitemfileWHEREitemcode=item_code;DBMS_OUTPUT.PUT_LINE(item_code||'项目的说明为'||v_itemdesc);END;/SQL>SETSERVEROUTPUTONSQL>EXECUTEitemdesc('i201');执行过程的语法：

EXECUTEprocedure_name(parameters_list);10过程8-5SQL>CREATEORREPLACE11过程

8-6SQL>CREATEORREPLACEPROCEDUREtest(value1INVARCHAR2,value2OUTNUMBER)ISidentityNUMBER;BEGINSELECTITEMRATEINTOidentityFROMitemFileWHEREitemcode=value1;IFidentity<200THENvalue2:=100;ENDIF;END;DECLAREvalue1VARCHAR2(5):='i202';value2NUMBER;BEGINtest(value1,value2);DBMS_OUTPUT.PUT_LINE('value2的值为'||TO_CHAR(value2));END;/11过程8-6SQL>CREATEORREPLACE12过程

8-7CREATEORREPLACEPROCEDUREswap(p1INOUTNUMBER,p2INOUTNUMBER)ISv_tempNUMBER;BEGINv_temp:=p1;p1:=p2;p2:=v_temp;END;/SQL>SETSERVEROUTONSQL>DECLAREnum1NUMBER:=100;num2NUMBER:=200;BEGINswap(num1,num2);DBMS_OUTPUT.PUT_LINE('num1='||num1);DBMS_OUTPUT.PUT_LINE('num2='||num2);END;/12过程8-7CREATEORREPLACEPROC13过程

8-8将过程的执行权限授予其他用户：删除过程：SQL>GRANTEXECUTEONfind_empTOMARTIN;SQL>GRANTEXECUTEONswapTOPUBLIC;SQL>DROPPROCEDUREfind_emp;13过程8-8将过程的执行权限授予其他用户：SQL>GR14函数4-1函数是可以返回值的命名的PL/SQL子程序。

创建函数的语法：

CREATE[ORREPLACE]FUNCTION

<functionname>[(param1,param2)]RETURN<datatype>IS|AS[localdeclarations]BEGINExecutableStatements;RETURNresult;EXCEPTIONExceptionhandlers;END;14函数4-1函数是可以返回值的命名的PL/SQL子程15函数4-2定义函数的限制：函数只能接受IN参数，而不能接受INOUT或OUT参数形参不能是PL/SQL类型函数的返回类型也必须是数据库类型访问函数的两种方式：使用PL/SQL块使用SQL语句15函数4-2定义函数的限制：16函数

4-3创建函数：从SQL语句调用函数：CREATEORREPLACEFUNCTIONfun_helloRETURNVARCHAR2ISBEGINRETURN'朋友，您好';END;/SQL>SELECTfun_helloFROMDUAL;16函数4-3创建函数：CREATEORREPLACE17函数

4-4CREATEORREPLACEFUNCTIONitem_price_range(priceNUMBER)RETURNVARCHAR2ASmin_priceNUMBER;max_priceNUMBER;BEGINSELECTMAX(ITEMRATE),MIN(ITEMRATE)INTOmax_price,min_priceFROMitemfile;IFprice>=min_priceANDprice<=max_priceTHENRETURN'输入的单价介于最低价与最高价之间';ELSERETURN'超出范围';ENDIF;END;/DECLAREPNUMBER:=300;MSGVARCHAR2(200);BEGINMSG:=item_price_range(300);DBMS_OUTPUT.PUT_LINE(MSG);END;/17函数4-4CREATEORREPLACEFUNC18过程和函数的比较过程函数作为PL/SQL语句执行作为表达式的一部分调用在规格说明中不包含RETURN子句必须在规格说明中包含RETURN子句不返回任何值必须返回单个值可以包含RETURN语句，但是与函数不同，它不能用于返回值必须包含至少一条RETURN

语句18过程和函数的比较过程函数作为PL/SQL语句执19自主事务处理2-1自主事务处理主事务处理启动独立事务处理然后主事务处理被暂停自主事务处理子程序内的SQL操作然后终止自主事务处理恢复主事务处理PRAGMAAUTONOMOUS_TRANSACTION

用于标记子程序为自主事务处理19自主事务处理2-1自主事务处理20自主事务处理2-2自主事务处理的特征：与主事务处理的状态无关提交或回滚操作不影响主事务处理自主事务处理的结果对其他事务是可见的能够启动其他自主事务处理20自主事务处理2-2自主事务处理的特征：21程序包程序包是对相关过程、函数、变量、游标和异常等对象的封装程序包由规范和主体两部分组成声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等声明程序包私有对象和实现在包规范中声明的子程序和游标程序包规范主体21程序包程序包是对相关过程、函数、变量、游标和异常等对象的22创建程序包2-1程序包规范CREATE[ORREPLACE]PACKAGEpackage_nameIS|AS[Publicitemdeclarations][Subprogramspecification]END[package_name];程序包主体CREATE[ORREPLACE]PACKAGEBODYpackage_nameIS|AS[Privateitemdeclarations][Subprogrambodies][BEGINInitialization]END[package_name];22创建程序包2-1程序包规范程序包主体23创建程序包2-2CREATEORREPLACEPACKAGEpack_meISPROCEDUREorder_proc(ornoVARCHAR2);FUNCTIONorder_fun(ornosVARCHAR2)RETURNVARCHAR2;ENDpack_me;/CREATEORREPLACEPACKAGEBODYpack_meASPROCEDUREorder_proc(ornoVARCHAR2)ISstatCHAR(1);BEGINSELECTostatusINTOstatFROMorder_masterWHEREorderno=orno;……ENDorder_proc;FUNCTIONorder_fun(ornosVARCHAR2)RETURNVARCHAR2ISicodeVARCHAR2(5);ocodeVARCHAR2(5);BEGIN……ENDorder_fun;ENDpack_me;/23创建程序包2-2CREATEORREPLACEP24程序包的优点模块化更轻松的应用程序设计信息隐藏新增功能性能更佳24程序包的优点模块化25程序包中的游标

2-1游标的定义分为游标规范和游标主体两部分在包规范中声明游标规范时必须使用RETURN子句指定游标的返回类型RETURN子句指定的数据类型可以是：用

%ROWTYPE属性引用表定义的记录类型程序员定义的记录类型25程序包中的游标2-1游标的定义分为游标规范和游标主体两26程序包中的游标

2-2SQL>CREATEORREPLACEPACKAGEcur_packISCURSORord_cur(vcodeVARCHAR2)RETURNorder_master%ROWTYPE;PROCEDUREord_pro(vcodeVARCHAR2);ENDcur_pack;/SQL>CREATEORREPLACEPACKAGEBODYcur_packASCURSORord_cur(vcodeVARCHAR2)RETURNorder_master%ROWTYPEISSELECT*FROMorder_masterWHEREVENCODE=vcode;PROCEDUREord_pro(vcodeVARCHAR2)ISor_recorder_master%ROWTYPE;BEGINOPENord_cur(vcode);LOOPFETCHord_curINTOor_rec;EXITWHENord_cur%NOTFOUND;DBMS_OUTPUT.PUT_LIne(’返回的值为'||or_rec.orderno);ENDLOOP;ENDord_pro;ENDcur_pack;/26程序包中的游标2-2SQL>CREATEORRE27有关子程序和程序包的信息USER_OBJECTS视图包含用户创建的子程序和程序包的信息USER_SOURCE视图存储子程序和程序包的源代码SELECTobject_name,object_typeFROMUSER_OBJECTSWHEREobject_typeIN('PROCEDURE','FUNCTION','PACKAGE','PACKAGEBODY');SELECTline,textFROMUSER_SOURCEWHERENAME='TEST';27有关子程序和程序包的信息USER_OBJECTS视图包28总结子程序是命名的PL/SQL块，可带参数并可在需要时随时调用有两种类型的PL/SQL子程序，即过程和函数过程用户执行特定的任务，函数用于执行任务并返回值程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装程序包由两部分组成，即包规范和包主体使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳28总结子程序是命名的PL/SQL块，可带参数并可在需要子程序和程序包子程序和程序包30回顾游标用于处理查询结果集中的数据游标类型有：隐式游标、显式游标和REF游标隐式游标由PL/SQL自动定义、打开和关闭显式游标用于处理返回多行的查询显式游标可以删除和更新活动集中的行要处理结果集中所有记录时，可使用循环游标在声明REF游标时，不需要将SELECT语句与其关联2回顾游标用于处理查询结果集中的数据31目标创建和使用子程序创建和使用程序包3目标创建和使用子程序32子程序2-1命名的PL/SQL块，编译并存储在数据库中。子程序的各个部分：声明部分可执行部分异常处理部分(可选)子程序的分类：过程－执行某些操作函数－执行操作并返回值4子程序2-1命名的PL/SQL块，编译并存储在数据库33子程序2-2子程序的优点：模块化将程序分解为逻辑模块可重用性可以被任意数目的程序调用可维护性简化维护操作安全性通过设置权限，使数据更安全5子程序2-2子程序的优点：34过程8-1过程是用于完成特定任务的子程序

例如：前往售票厅询问关于车票的信息排队等候在柜台购买车票6过程8-1过程是用于完成特定任务的子程序前往售票厅询问35过程

8-2创建过程的语法：CREATE[ORREPLACE]PROCEDURE<procedurename>[(<parameterlist>)]IS|AS<localvariabledeclaration>BEGIN<executablestatements>[EXCEPTION<exceptionhandlers>]END;创建过程，可指定运行过程需传递的参数处理异常包括在过程中要执行的语句7过程8-2创建过程的语法：创建过程，可指定运行过程需传递36过程

8-3CREATEORREPLACEPROCEDUREfind_emp(emp_noNUMBER)ASempnameVARCHAR2(20);BEGINSELECTenameINTOempnameFROMEMPWHEREempno=emp_no;DBMS_OUTPUT.PUT_LINE('雇员姓名是'||empname);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('雇员编号未找到');ENDfind_emp;/8过程8-3CREATEORREPLACEPROCE37过程

8-4过程参数的三种模式：IN用于接受调用程序的值默认的参数模式OUT用于向调用程序返回值INOUT用于接受调用程序的值，并向调用程序返回更新的值9过程8-4过程参数的三种模式：38过程

8-5SQL>CREATEORREPLACEPROCEDUREitemdesc(item_codeINVARCHAR2)ISv_itemdescVARCHAR2(5);BEGINSELECTitemdescINTOv_itemdescFROMitemfileWHEREitemcode=item_code;DBMS_OUTPUT.PUT_LINE(item_code||'项目的说明为'||v_itemdesc);END;/SQL>SETSERVEROUTPUTONSQL>EXECUTEitemdesc('i201');执行过程的语法：

EXECUTEprocedure_name(parameters_list);10过程8-5SQL>CREATEORREPLACE39过程

8-6SQL>CREATEORREPLACEPROCEDUREtest(value1INVARCHAR2,value2OUTNUMBER)ISidentityNUMBER;BEGINSELECTITEMRATEINTOidentityFROMitemFileWHEREitemcode=value1;IFidentity<200THENvalue2:=100;ENDIF;END;DECLAREvalue1VARCHAR2(5):='i202';value2NUMBER;BEGINtest(value1,value2);DBMS_OUTPUT.PUT_LINE('value2的值为'||TO_CHAR(value2));END;/11过程8-6SQL>CREATEORREPLACE40过程

8-7CREATEORREPLACEPROCEDUREswap(p1INOUTNUMBER,p2INOUTNUMBER)ISv_tempNUMBER;BEGINv_temp:=p1;p1:=p2;p2:=v_temp;END;/SQL>SETSERVEROUTONSQL>DECLAREnum1NUMBER:=100;num2NUMBER:=200;BEGINswap(num1,num2);DBMS_OUTPUT.PUT_LINE('num1='||num1);DBMS_OUTPUT.PUT_LINE('num2='||num2);END;/12过程8-7CREATEORREPLACEPROC41过程

8-8将过程的执行权限授予其他用户：删除过程：SQL>GRANTEXECUTEONfind_empTOMARTIN;SQL>GRANTEXECUTEONswapTOPUBLIC;SQL>DROPPROCEDUREfind_emp;13过程8-8将过程的执行权限授予其他用户：SQL>GR42函数4-1函数是可以返回值的命名的PL/SQL子程序。

创建函数的语法：

CREATE[ORREPLACE]FUNCTION

<functionname>[(param1,param2)]RETURN<datatype>IS|AS[localdeclarations]BEGINExecutableStatements;RETURNresult;EXCEPTIONExceptionhandlers;END;14函数4-1函数是可以返回值的命名的PL/SQL子程43函数4-2定义函数的限制：函数只能接受IN参数，而不能接受INOUT或OUT参数形参不能是PL/SQL类型函数的返回类型也必须是数据库类型访问函数的两种方式：使用PL/SQL块使用SQL语句15函数4-2定义函数的限制：44函数

4-3创建函数：从SQL语句调用函数：CREATEORREPLACEFUNCTIONfun_helloRETURNVARCHAR2ISBEGINRETURN'朋友，您好';END;/SQL>SELECTfun_helloFROMDUAL;16函数4-3创建函数：CREATEORREPLACE45函数

4-4CREATEORREPLACEFUNCTIONitem_price_range(priceNUMBER)RETURNVARCHAR2ASmin_priceNUMBER;max_priceNUMBER;BEGINSELECTMAX(ITEMRATE),MIN(ITEMRATE)INTOmax_price,min_priceFROMitemfile;IFprice>=min_priceANDprice<=max_priceTHENRETURN'输入的单价介于最低价与最高价之间';ELSERETURN'超出范围';ENDIF;END;/DECLAREPNUMBER:=300;MSGVARCHAR2(200);BEGINMSG:=item_price_range(300);DBMS_OUTPUT.PUT_LINE(MSG);END;/17函数4-4CREATEORREPLACEFUNC46过程和函数的比较过程函数作为PL/SQL语句执行作为表达式的一部分调用在规格说明中不包含RETURN子句必须在规格说明中包含RETURN子句不返回任何值必须返回单个值可以包含RETURN语句，但是与函数不同，它不能用于返回值必须包含至少一条RETURN

语句18过程和函数的比较过程函数作为PL/SQL语句执47自主事务处理2-1自主事务处理主事务处理启动独立事务处理然后主事务处理被暂停自主事务处理子程序内的SQL操作然后终止自主事务处理恢复主事务处理PRAGMAAUTONOMOUS_TRANSACTION

用于标记子程序为自主事务处理19自主事务处理2-1自主事务处理48自主事务处理2-2自主事务处理的特征：与主事务处理的状态无关提交或回滚操作不影响主事务处理自主事务处理的结果对其他事务是可见的能够启动其他自主事务处理20自主事务处理2-2自主事务处理的特征：49程序包程序包是对相关过程、函数、变量、游标和异常等对象的封装程序包由规范和主体两部分组成声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等声明程序包私有对象和实现在包规范中声明的子程序和游标程序包规范主体21程序包程序包是对相关过程、函数、变量、游标和异常等对象的50创建程序包2-1程序包规范CREATE[ORREPLACE]PACKAGEpackage_nameIS|AS[Publicitemdeclarations][Subprogramspecification]END[package_name];程序包主体CREATE[ORREPLACE]PACKAGEBODYpackage_nameIS|AS[Privateitemdeclarations][Subprogrambodies][BEGINInitialization]END[package_name];22创建程序包2-1程序包规范程序包主体51创建程序包2-2CREATEORREPLACEPACKAGEpack_meISPROCEDUREorder_proc(ornoVARCHAR2);FUNCTIONorder_fun(ornosVARCHAR2)RETURNVARCHAR2;ENDpack_me;/CREATEORREPLACEPACKAGEBODYpack_meASPROCEDUREorder_proc(ornoVARCHAR2)ISstatCHAR(1);BEGINSELECTostatusINTOstatFROMorder_masterWHEREorderno=orno;……ENDorder_proc;FUNCTIONorder_fun(ornosVARCHAR2)RETURNVARCHAR2ISicodeVARCHAR2(5);ocodeVARCHAR2(5);BEGIN……ENDorder_fun;ENDpack_me;/23创建程序包2-2CREATEORREPLACEP52程序包的优点模块化更轻松的应用