Oracle8i9i数据库基础.docx
《Oracle8i9i数据库基础.docx》由会员分享，可在线阅读，更多相关《Oracle8i9i数据库基础.docx（13页珍藏版）》请在冰豆网上搜索。
Oracle8i9i数据库基础
一、第十六章存储过程和函数
ORACLE编写的程序一般分为两类，一种是可以完成一定功能的程序叫存储过程；另一种就是在使用时给出一个或多个值，处理完后返回一个或多个结果的程序叫函数。
这两种程序都存放在Oracle数据库字典中。
下面分别介绍这两种程序的编写方法。
（一）§16.1引言
ORACLE提供可以把PL/SQL程序存储在数据库中，并可以在任何地方来运行它。
这样就叫存储过程或函数。
在本节中，主要介绍：
1．创建存储过程和函数
2．正确使用系统级的异常处理和用户定义的异常处理
3．建立和管理存储过程和函数
（二）§16.2存储过程
与其它的数据库系统一样，Oracle的存储过程是用PL/SQL语言编写的能完成一定处理功能的存储在数据库字典中的程序。
1.§16.2.1创建过程
建立内嵌过程
在oracleserver上建立内嵌过程，可以被多个应用程序调用，可以向内嵌过程
传递参数，也可以向内嵌过程传回参数.
创建过程语法:
create[orreplace]procedureprocedure_name
[（argment[{in|inout}]type,
argment[{in|out|inout}]type
{is|as}
<类型.变量的说明>
（注:
不用declare语句）
<执行部分>Begin
exception
<可选的异常处理说明>
end;
●这里的IN表示向存储过程传递参数，OUT表示从存储过程返回参数。
而INOUT表示传递参数和返回参数；
●在存储过程内的变量类型只能指定变量类型；不能指定长度；
●在AS或IS后声明要用到的变量名称和变量类型及长度；
●在AS或IS后声明变量不要加declare语句。
例1．
--节选自在线代码modetest.sql
REM作者：
ScottUrman.
REM中文注释:
赵元杰
CREATEORREPLACEPROCEDUREModeTest（
p_InParameterINNUMBER,
p_OutParameterOUTNUMBER,
p_InOutParameterINOUTNUMBER）IS
v_LocalVariableNUMBER;
BEGIN
/*分配p_InParameter给v_LocalVariable.*/
v_LocalVariable:
=p_InParameter;--Legal
/*分配7给p_InParameter.这是非法的,因为声明是IN*/
p_InParameter:
=7;--Illegal
/*分配7给p_InParameter.这是合法的,因为声明是OUT*/
p_OutParameter:
=7;--Legal
/*分配p_OutParameter给v_LocalVariable.这是非法的,因为声明是IN*/
v_LocalVariable:
=p_outParameter;--Illegal
/*分配p_InOutParameter给v_LocalVariable.这是合法的,因为声明是INOUT*/
v_LocalVariable:
=p_InOutParameter;--Legal
/*分配7给p_InOutParameter.这是合法的,因为声明是INOUT*/
p_InOutParameter:
=7;--Legal
ENDModeTest;
/
2.§16.2.2使用过程
存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS、Oracle开发工具或第三方开发工具来调用运行。
Oracle使用EXECUTE语句来实现对存储过程的调用。
EXEC[UTE]procedure_name（parameter1,parameter2…）;
例：
CREATEPACKAGEemp_dataAS
TYPEEmpRecTypISRECORD（
emp_idNUMBER（4）,
emp_nameVARCHAR2（10）,
job_titleVARCHAR2（9）,
dept_nameVARCHAR2（14）,
dept_locVARCHAR2（13）
）;
TYPEEmpCurTypISREFCURSORRETURNEmpRecTyp;
PROCEDUREget_staff（
dept_noINNUMBER,
emp_cvINOUTEmpCurTyp）;
END;
/
CREATEPACKAGEBODYemp_dataAS
PROCEDUREget_staff（
dept_noINNUMBER,
emp_cvINOUTEmpCurTyp）IS
BEGIN
OPENemp_cvFOR
SELECTempno,ename,job,dname,locFROMemp,dept
WHEREemp.deptno=dept_noANDemp.deptno=dept.deptno
ORDERBYempno;
END;
END;
/
COLUMNEMPNOHEADINGNumber
COLUMNENAMEHEADINGName
COLUMNJOBHEADINGJobTitle
COLUMNDNAMEHEADINGDepartment
COLUMNLOCHEADINGLocation
SETAUTOPRINTON
VARIABLEcvREFCURSOR
EXECUTEemp_data.get_staff（20,:
cv）
3.§16.2.3开发存储过程步骤
目前的几大数据库厂商提供的编写存储过程的工具都没有统一，虽然它们的编写风格有些相似，但由于没有标准，所以各家的开发调试过程也不一样。
下面编写PL/SQL存储过程、函数、包及触发器的步骤如下：
§16.2.3.1编辑存储过程源码
使用文字编辑处理软件编辑存储过程源码，要用类似WORD文字处理软件进行编辑时，要将源码存为文本格式。
§16.2.3.2对存储过程程序进行解释
在SQLPLUS或用调试工具将存储过程程序进行解释；
在SQL>下调试，可用start或get等Oracle命令来启动解释。
如：
SQL>startc:
\stat1.sql
如果使用调试工具，可直接编辑和点击相应的按钮即可生成存储过程。
§16.2.3.3调试源码直到正确
我们不能保证所写的存储过程达到一次就正确。
所以这里的调试是每个程序员必须进行的工作之一。
在SQLPLUS下来调试主要用的方法是：
1．使用SHOWERROR命令来提示源码的错误位置；
2．使用user_errors数据字典来查看各存储过程的错误位置。
§16.2.3.4授权执行权给相关的用户或角色
如果调试正确的存储过程没有进行授权，那就只有建立者本人才可以运行。
所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。
在SQLPLUS下可以用GRANT命令来进行存储过程的运行授权。
GRANT语法：
GRANTsystem_privilege|roleTOuser|role|PUBLIC
[WITHADMINOPTION]
GRANTobject_privilege|ALLcolumnONschema.object
FROMuser|role|PUBLICWITHGRANTOPTION
变量：
system_privilege:
系统权限
role:
角色名
user:
被授权的用户名
object_privilege:
所授予的权限名字，可以是
●ALTER
●DELETE
●EXECUTE
●INDEX
●INSERT
●REFERENCES
●SELECT
●UPDATE
Column:
列名
schema:
模式名
object:
对象名
例子：
GRANTteam_leaderTOcrystal;
GRANTINSERT,UPDATEONsalesTOlarryWITHGRANT
OPTION;
GRANTALLTOPUBLIC;.
4.§16.2.4与存储过程相关数据字典
user_source用户的存储过程、函数的源代码字典
all_source所有用户的存储过程、函数的源代码字典
user_errors用户的存储过程、函数的源代码存在错误的信息字典
相关的权限:
createanyprocedure
dropanyprocedure
如果某个用户没有权限来创建存储过程，则需要DBA将创建过程的权限授予某用户。
如：
sql>grantcreateanyproceduretouser1;
（三）§16.3创建函数
Oracle的函数是一个独有的对象，它也是由PL/SQL语句编写而成，但的不同的地方是：
函数必须返回某些值，而存储过程可以不返回任何值。
与创建存储过程类似，创建函数的语法如下：
1.建立内嵌函数
CREATEFUNCTION语法如下：
create[orreplace]functionfunction_name
[（argment[{in|inout}]TYPE,
argment[{in|out|inout}]type]
returnreturn_type{is|as}
begin
function_body
exception
......
end;
例1.
createorreplacefunctiontext_len（tvarchar2,lnumber）
returnvarchar2as
tmpvarchar2（20）;
begin
tmp:
=substr（t,1,l）;
returnto_char（l）||’‘||tmp;
end;
例2.较为复杂的函数：
REM选自clasinfo.sql
REM作者：
ScottUrman.
REM中文注释:
赵元杰
CREATEORREPLACEFUNCTIONClassInfo（
/*如果教室全满，则返回'Full'
如果教室超过80%，则返回'SomeRoom'
如果教室超过60%，则返回'MoreRoom'
如果教室小于60%，则返回'LotsofRoom'
如果教室没有学生，则返回'Empty'.*/
p_Departmentclasses.department%TYPE,
p_Courseclasses.course%TYPE）
RETURNVARCHAR2IS
v_CurrentStudentsNUMBER;
v_MaxStudentsNUMBER;
v_PercentFullNUMBER;
BEGIN
--得到学生的当前和最大值
SELECTcurrent_students,max_students
INTOv_CurrentStudents,v_MaxStudents
FROMclasses
WHEREdepartment=p_Department
ANDcourse=p_Course;
--计算当前的百分比.
v_PercentFull:
=v_CurrentStudents/v_MaxStudents*100;
IFv_PercentFull=100THEN
RETURN'Full';
ELSIFv_PercentFull>80THEN
RETURN'SomeRoom';
ELSIFv_PercentFull>60THEN
RETURN'MoreRoom';
ELSIFv_PercentFull>0THEN
RETURN'LotsofRoom';
ELSE
RETURN'Empty';
ENDIF;
ENDClassInfo;
/
（四）§16.4过程和函数中的例外处理
与编写存储过程一样，在编写PL/SQL函数时，也需要对可能出现的各种错误进行描述，以保证函数在运行中出现错误时，程序能按照我们定义的要求进行处理。
5.§16.4.1使用系统定义的例外处理
§16.4.1.1没有例外处理的缺点
如果在编写时没有给出EXCEPTION的话，一旦出现例外的情况，Oracle就自动终止程序的运行。
如果编写的程序没有给出例外处理，则当程序出错时用户无法得到提示，调试者也无法进行修改程序。
所以，一般无论多简单的程序最好也要给出例外处理的要求。
§16.4.1.2使用预定义的例外处理
例。
实际例子：
proceduresum_interest_year（cur_procdateinvarchar2,rtnoutnumber）is
/***********************************************************************/
/*程序名:
sprocess.SQL*/
/*功能:
由PB调用的PL/SQL子过程集，公积金转移金额及利息的计算等....*/
/*编程语言:
PL/SQLforORACLE7.3.2,oracle8v8.x*/
/*运行环境:
ORACLE7.3.X,oracle8i*/
/*修改:
赵元杰*/
/*修改日期:
199906.25*/
/**********************************************************************/
datestrvarchar2（10）;
ls_procdatevarchar2（10）;
ls_yearvarchar2（10）;
begin
/*修改定期主表年终余额、利息、积数、状态位，将年余额
按新的一年加入per_fix_det，status='0'；
将per_detail.status='1',per_fix_det.status='1',
per_fix_detandper_detail的余额转入per_fix_abst,
将年结数据加入年余额表save_bal
修改系统表日期sssyspar.yearbegin=date_str*/
settransactionuserollbacksegmenthdhouse_rs;
selectdistinctto_char（procdate,'yyyymmdd'）intols_procdate
fromvw_tmp_inte;
selectto_char（to_number（substr（yearbegin,1,4））+1）intols_year
fromsssyspar;
ifsubstr（ls_procdate,1,4）<>ls_yearthen
rtn:
=-1;
return;
elsifsubstr（ls_procdate,5,4）<>'0630'then
rtn:
=-1;
return;
endif;
datestr:
=substr（cur_procdate,1,4）||substr（cur_procdate,6,2）||substr（cur_procdate,9,2）;
updateper_fix_abstset（avail_bal,acc_int,acc_int_fix,interest,
interest_b,crea_int,crea_int_b）=
（selectbal+inteval+inteval_fix,acumbase,acumbase_fix,
inteval_c+inteval_c_fix,
inteval+inteval_fix,0,0
fromvw_tmp_inte
whereper_fix_abst.acc_no=vw_tmp_inte.accno
andper_fix_abst.emp_acc_no=vw_tmp_inte.emp_accno
）
wherenvl（acc_status,'0'）='0';
ifsqlcode!
=0then
rtn:
=sqlcode;
rollback;
return;
endif;
updateper_abstsetavail_bal=0,accu_int=0,interest=0
wherenvl（acc_status,'0'）='0';
ifsqlcode!
=0then
rtn:
=sqlcode;
rollback;
return;
endif;
updateper_detailsetstatus='1'wherenvl（status,'0'）='0'
andto_char（crea_date,'yyyy/mm/dd'）<=cur_procdate;
ifsqlcode!
=0then
rtn:
=sqlcode;
rollback;
return;
endif;
updateper_fix_detsetstatus='1'wherenvl（status,'0'）='0'
andto_char（crea_date,'yyyy/mm/dd'）<=cur_procdate;
ifsqlcode!
=0then
rtn:
=sqlcode;
rollback;
return;
endif;
insertintoper_fix_det（acc_no,emp_acc_no,tran_date,tran_val,
tran_code,balance,db_cr_flag,cash_check,memo,crea_date,
base_int,status,bank_code）
selectacc_no,emp_acc_no,to_date（substr（cur_procdate,1,4）||'/07/01','yyyy/mm/dd'）,
avail_bal,'',avail_bal,'1','0','年结
',to_date（substr（cur_procdate,1,4）||'/07/01','yyyy/mm/dd'）,
0,'0',bank_code
fromper_fix_abstwherenvl（acc_status,'0'）='0';
ifsqlcode!
=0then
rtn:
=sqlcode;
rollback;
return;
endif;
insertintosave_bal（acc_no,emp_acc_no,cal_year,balance,interest,
interest_b,accu_int,accu_int_fix,rate_c,rate_c_fix,
rate_b,rate_b_fix）
selectacc_no,emp_acc_no,datestr,avail_bal,interest,interest_b,
acc_int,acc_int_fix,sssyspar.rate_val_c,
sssyspar.rate_fixval_c,sssyspar.rate_val_b,
sssyspar.rate_fixval_b
fromper_fix_abst,sssyspar
wherenvl（acc_status,'0'）='0';
ifsqlcode!
=0then
rtn:
=sqlcode;
rollback;
return;
endif;
updatesssysparsetyearbegin=substr（cur_procdate,1,4）||'-07-01';
ifsqlcode!
=0then
rtn:
=sqlcode;
rollback;
return;
endif;
commit;
rtn:
=1;
exception
whenno_data_foundthen
rtn:
=sqlcode;
rollback;
whenothersthen
rtn:
=sqlcode;
rollback;
end;
6.§16.4.2使用用户定义的例外处理+
§16.4.2.1定义的用户例外处理
PL/SQL可以让你定义自己的例外。
与预定义不同，用户定义的例外必须声明且必须用RAISE语句来激活（raise）。
声明例外
例外必须在PL/SQL块、子程序或包中进行声明。
但不能在一个块里声明两次。
但可以在两个块中对同一个例外进行声明。
§16.4.2.2使用户EXCEPTION_INIT处理
在PL/SQL的程序中，除了所列出的系统错误代码外，实际上还有许多的可能错误。
这些内部异常（错误）必须用OTHERS或EXCEPTION_INIT来处理，实际程序（pragma）是一个编译指示器。
它是通过一个叫附加说明来传给编译器。
Pragma（也叫伪指令）是在编译时内处理，不是在运行时被处理。
DECLARE
deadlock_detectedEXCEPTION;
PRAGMAEXCEPTION_INIT（deadlock_detected,-60）;
BEGIN
...
EXCEPTION
WHENdeadlock_detectedTHEN
--handletheerror
END;
§16.4.2.3使用户raise_application_error处理
例：
CREATEPROCEDUREraise_salary（emp_idNUMBER,amountNUMBER）AS
curr_salNUMBER;
BEGIN
SELECTsalINTOcurr_salFROMempWHEREemp
升级会员 