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

子程序和程序包回顾q游标用于处理查询结果集中的数据q游标类型有：隐式游标、显式游标和

REF游标q隐式游标由

PL/SQL

自动定义、打开和关闭q显式游标用于处理返回多行的查询q显式游标可以删除和更新活动集中的行q要处理结果集中所有记录时，可使用循环游标q在声明

REF游标时，不需要将

SELECT

语句与其关联2目标q创建和使用子程序q创建和使用程序包3子程序

2-1q命名的

PL/SQL

块，编译并存储在数据库中。q子程序的各个部分：q声明部分q可执行部分q异常处理部分(可选)q子程序的分类：q过程

－

执行某些操作q函数

－

执行操作并返回值4子程序

2-2子程序的优点：q模块化q将程序分解为逻辑模块q可重用性q可以被任意数目的程序调用q可维护性q简化维护操作q安全性q通过设置权限，使数据更安全5过程

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

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

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-4过程参数的三种模式：qINq用于接受调用程序的值q默认的参数模式qOUTq用于向调用程序返回值qINOUTq用于接受调用程序的值，并向调用程序返回更新的值9过程

8-5执行过程的语法：EXECUTE

procedure_name(parameters_list);SQL>CREATEORREPLACEPROCEDUREitemdesc(item_codeINVARCHAR2)ISv_itemdescVARCHAR2(5);BEGINSELECTitemdescINTOv_itemdescFROMitemfileWHEREitemcode=item_code;DBMS_OUTPUT.PUT_LINE(item_code||'项目的说明为'||v_itemdesc);END;/SQL>SETSERVEROUTPUTONSQL>EXECUTEitemdesc('i201');10过程

8-6SQL>CREATEORREPLACEPROCEDUREDECLAREtest(value1IN

VARCHAR2,value1VARCHAR2(5):='i202';value2OUTNUMBER)ISvalue2NUMBER;BEGINidentity

NUMBER;test(value1,value2);BEGINDBMS_OUTPUT.PUT_LINE('value2

的值为'SELECTITEMRATEINTOidentity||TO_CHAR(value2));FROMitemFileWH

EREitemcode=value1;/IFidentity<200THENvalue2:=100;ENDIF;END;11过程

8-7SCQRLE>ATSEETOSRERREVPELRAOCUETPORNOC

E

D

U

R

ESsQwLa>pDp

E

1

C

I

NLAORUET

NUMBER,p2INOUTNUMBER)ISnum1

NUMBER:=100;v

n

_

u

t

emm2pNNUUMMBBEERR;

=200;BEGINvs_wteamp(pnu:=mp11,;

n

u

m

2

)

;pD1B:M=

Sp2_;OUTPUT.PUT_LINE('num1

='||num1);pD2B:M=

Sv__tOemUTp;PUT.PUT_LINE('num2

='||num2);END;/12过程

8-8q将过程的执行权限授予其他用户：SQL>GRANTEXECUTEONfind_empTOMARTIN;SQL>GRANTEXECUTEONswapTOPUBLIC;q删除过程：SQL>DROPPROCEDUREfind_emp;13函数

4-1q函数是可以返回值的命名的

PL/SQL

子程序。q创建函数的语法：CREATE[ORREPLACE]FUNCTION<functionname>[(param1,param2)]RETURN<datatype>

IS|AS[localdeclarations]BEGINExecutableStatements;RETURNresult;EXCEPTIONExceptionhandlers;END;14函数

4-2q定义函数的限制：q函数只能接受

IN参数，而不能接受

INOUT

或

OUT参数q形参不能是

PL/SQL

类型q函数的返回类型也必须是数据库类型q访问函数的两种方式：q使用

PL/SQL

块q使用

SQL语句15函数

4-3q创建函数：CREATEORREPLACEFUNCTIONfun_helloRETURN

VARCHAR2ISBEGINRETURN'朋友，您好';END;/q从

SQL

语句调用函数：SQL>SELECTfun_helloFROMDUAL;16函数

4-4CREATEORREPLACEFUNCTIONitem_price_range(priceNUMBER)RETURNVARCHAR2ASmin_priceNUMBER;DECLAREmax_priceNUMBER;PNUMBER:=300;BEGINMSGVARCHAR2(200);SELECTMAX(ITEMRATE),MIN(ITEMRATE)INT

Omax_price,min_priceMSG:=item_price_range(300);FROMitemfile;DBMS_OUTPUT.PUT_LINE(MSG);IFprice>=min_priceANDprice<=max_priceEND;/THENRETURN'输入的单价介于最低价与最高价之间';ELSERETURN'超出范围';ENDIF;END;/17过程和函数的比较过

程函

数作为

PL/SQL

语句执行作为表达式的一部分调用在规格说明中不包含

RETURN子句

必须在规格说明中包含RETURN子句不返回任何值可以包含

RETURN语句，但是与函

必须包含至少一条

RETURN数不同，它不能用于返回值语句18自主事务处理

2-1q自主事务处理q主事务处理启动独立事务处理q然后主事务处理被暂停q自主事务处理子程序内的

SQL操作q然后终止自主事务处理q恢复主事务处理qPRAGMAAUTONOMOUS_TRANSACTION用于标记子程序为自主事务处理19自主事务处理

2-2q自主事务处理的特征：q与主事务处理的状态无关q提交或回滚操作不影响主事务处理q自主事务处理的结果对其他事务是可见的q能够启动其他自主事务处理20程序包q程序包是对相关过程、函数、变量、游标和异常等对象的封装q程序包由规范和主体两部分组成声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等21创建程序包

2-1程序包规范程序包主体CREATE

[OR

REPLACE]PACKAGECREATE

[OR

REPLACE]PACKAGE

BODYpackage_name

IS|AS[Public

item

declarations][Subprogram

specification]END

[package_name];package_name

IS|AS[Private

item

declarations][Subprogram

bodies][BEGINEND

[package_name];22创建程序包

2-2CREATEORREPLACEPACKAGEpack_meCREATEORREPLACEPACKAGEBODYpack_meASISPROCEDUREorder_proc(ornoVARCHAR2)ISPBROFUstatCHAR(1);ECEDGUREoIrNder_proc(ornoVARCHAR2);NCTIONorder_fun(ornosVARCHAR2)RETURNVARCHAR2;ENDpack_me;SELECTostatusINTOstatFROMorder_master/WHEREorderno=orno;……ENDorder_proc;FUNCTIONorder_fun(ornosVARCHAR2)RETURNVARCHAR2ISicode

VARCHAR2(5);ocode

VARCHAR2(5);BEGIN……ENDorder_fun;ENDpack_me;23/程序包的优点q模块化q更轻松的应用程序设计q信息隐藏q新增功能q性能更佳24程序包中的游标

2-1q游标的定义分为游标规范和游标主体两部分q在包规范中声明游标规范时必须使用

RETURN子句指定游标的返回类型qRETURN子句指定的数据类型可以是：q用

%ROWTYPE

属性引用表定义的记录类型q程序员定义的记录类型25程序包中的游标

2-2SQL>

CREATE

OR

REPLACE

PACKAGE

BODY

cur_pack

ASCURSOR

ord_cur(vcode

VARCHAR2)SQL>

CREATE

OR

REPLACE

PACKAGE

cur_pack

ISRETURN

order_master%ROWTYPE

ISCURSOR

ord_cur(vcode

VARCHAR2)SELECT

*

FROM

order_master

WHERE

VENCODE=vcode;RETURN

order_master%ROWTYPE;PROCEDURE

ord_pro(vcode

VARCHAR2)

ISPROCEDURE

ord_pro(vcode

VARCHAR2);or_rec

order_master%ROWTYPE;EBNEDGIcNur_pack;/

OPEN

ord_cur(vcode);LOOPFETCH

ord_cur

INTO

or_rec;EXIT

WHEN

ord_cur%NOTFOUND;DBMS_O