Oracle8i9i-数据库.doc

一、 第十六章 存储过程和函数ORACLE编写的程序一般分为两类，一种是可以完成一定功能的程序叫存储过程；另一种就是在使用时给出一个或多个值，处理完后返回一个或多个结果的程序叫函数。这两种程序都存放在Oracle数据库字典中。下面分别介绍这两种程序的编写方法。(一) 16.1 引言ORACLE 提供可以把PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。在本节中，主要介绍：1 创建存储过程和函数2 正确使用系统级的异常处理和用户定义的异常处理3 建立和管理存储过程和函数(二) 16.2 存储过程与其它的数据库系统一样，Oracle的存储过程是用PL/SQL语言编写的能完成一定处理功能的存储在数据库字典中的程序。1. 16.2.1 创建过程建立内嵌过程 在 oracle server 上建立内嵌过程，可以被多个应用程序调用，可以向内嵌过程传递参数，也可以向内嵌过程传回参数.创建过程语法:create or replace procedure procedure_name (argment in| in out type, argment in | out | in out type is | as ( 注: 不用 declare 语句 ) Begin exception end;l 这里的IN表示向存储过程传递参数，OUT表示从存储过程返回参数。而IN OUT 表示传递参数和返回参数；l 在存储过程内的变量类型只能指定变量类型；不能指定长度；l 在AS或IS 后声明要用到的变量名称和变量类型及长度；l 在AS或IS 后声明变量不要加declare 语句。例1-节选自在线代码modetest.sqlREM 作者： Scott Urman.REM 中文注释:赵元杰CREATE OR REPLACE PROCEDURE ModeTest ( p_InParameter IN NUMBER, p_OutParameter OUT NUMBER, p_InOutParameter IN OUT NUMBER ) IS v_LocalVariable NUMBER;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. 这是合法的,因为声明是IN OUT */ v_LocalVariable := p_InOutParameter; - Legal /*分配 7 给 p_InOutParameter. 这是合法的,因为声明是IN OUT */ p_InOutParameter := 7; - LegalEND ModeTest;/2. 16.2.2 使用过程 存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS 、Oracle开发工具或第三方开发工具来调用运行。Oracle 使用EXECUTE 语句来实现对存储过程的调用。EXECUTE procedure_name( parameter1, parameter2);例：CREATE PACKAGE emp_data ASTYPE EmpRecTyp IS RECORD (emp_id NUMBER(4),emp_name VARCHAR2(10),job_title VARCHAR2(9),dept_name VARCHAR2(14),dept_loc VARCHAR2(13);TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;PROCEDURE get_staff (dept_no IN NUMBER,emp_cv IN OUT EmpCurTyp);END;/CREATE PACKAGE BODY emp_data ASPROCEDURE get_staff (dept_no IN NUMBER,emp_cv IN OUT EmpCurTyp) ISBEGINOPEN emp_cv FORSELECT empno, ename, job, dname, loc FROM emp, deptWHERE emp.deptno = dept_no AND emp.deptno = dept.deptnoORDER BY empno;END;END;/COLUMN EMPNO HEADING NumberCOLUMN ENAME HEADING NameCOLUMN JOB HEADING JobTitleCOLUMN DNAME HEADING DepartmentCOLUMN LOC HEADING LocationSET AUTOPRINT ONVARIABLE cv REFCURSOREXECUTE emp_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命令来启动解释。如：SQLstart c:stat1.sql如果使用调试工具，可直接编辑和点击相应的按钮即可生成存储过程。16.2.3.3 调试源码直到正确我们不能保证所写的存储过程达到一次就正确。所以这里的调试是每个程序员必须进行的工作之一。在SQLPLUS下来调试主要用的方法是：1 使用 SHOW ERROR命令来提示源码的错误位置；2 使用 user_errors 数据字典来查看各存储过程的错误位置。16.2.3.4 授权执行权给相关的用户或角色如果调试正确的存储过程没有进行授权，那就只有建立者本人才可以运行。所以作为应用系统的一部分的存储过程也必须进行授权才能达到要求。 在SQLPLUS下可以用GRANT命令来进行存储过程的运行授权。GRANT语法：GRANT system_privilege | role TO user | role | PUBLICWITH ADMIN OPTIONGRANT object_privilege | ALL column ON schema.objectFROM user | role | PUBLIC WITH GRANT OPTION变量：system_privilege: 系统权限role: 角色名user: 被授权的用户名object_privilege: 所授予的权限名字，可以是l ALTERl DELETEl EXECUTEl INDEXl INSERTl REFERENCESl SELECTl UPDATEColumn: 列名schema: 模式名object: 对象名例子：GRANT team_leader TO crystal;GRANT INSERT, UPDATE ON sales TO larry WITH GRANTOPTION;GRANT ALL TO PUBLIC;.4. 16.2.4 与存储过程相关数据字典user_source 用户的存储过程、函数的源代码字典all_source 所有用户的存储过程、函数的源代码字典user_errors 用户的存储过程、函数的源代码存在错误的信息字典相关的权限:create any proceduredrop any procedure如果某个用户没有权限来创建存储过程，则需要DBA将创建过程的权限授予某用户。如：sqlgrant create any procedure to user1;(三) 16.3 创建函数Oracle的函数是一个独有的对象，它也是由PL/SQL语句编写而成，但的不同的地方是：函数必须返回某些值，而存储过程可以不返回任何值。与创建存储过程类似，创建函数的语法如下：1. 建立内嵌函数 CREATE FUNCTION语法如下：create or replace function function_name (argment in| in out TYPE, argment in | out | in out type return return_type is | as begin function_body exception . . . . . . end;例1.create or replace function text_len( t varchar2, l number ) return varchar2 as tmp varchar2(20); begin tmp := substr( t, 1, l ); return to_char(l)| |tmp; end;例2. 较为复杂的函数：REM 选自clasinfo.sqlREM 作者： Scott Urman.REM 中文注释:赵元杰CREATE OR REPLACE FUNCTION ClassInfo ( /* 如果教室全满，则返回 Full 如果教室超过80%，则返回Some Room 如果教室超过60%，则返回More Room 如果教室小于60%，则返回Lots of Room 如果教室没有学生，则返回Empty. */ p_Department classes.department%TYPE, p_Course classes.course%TYPE) RETURN VARCHAR2 IS v_CurrentStudents NUMBER; v_MaxStudents NUMBER; v_PercentFull NUMBER;BEGIN - 得到学生的当前和最大值 SELECT current_students, max_students INTO v_CurrentStudents, v_MaxStudents FROM classes WHERE department = p_Department AND course = p_Course; - 计算当前的百分比. v_PercentFull := v_CurrentStudents / v_MaxStudents * 100; IF v_PercentFull = 100 THEN RETURN Full; ELSIF v_PercentFull 80 THEN RETURN Some Room; ELSIF v_PercentFull 60 THEN RETURN More Room; ELSIF v_PercentFull 0 THEN RETURN Lots of Room; ELSE RETURN Empty; END IF;END ClassInfo;/(四) 16.4 过程和函数中的例外处理与编写存储过程一样，在编写PL/SQL函数时，也需要对可能出现的各种错误进行描述，以保证函数在运行中出现错误时，程序能按照我们定义的要求进行处理。5. 16.4.1 使用系统定义的例外处理16.4.1.1 没有例外处理的缺点如果在编写时没有给出EXCEPTION 的话，一旦出现例外的情况，Oracle就自动终止程序的运行。如果编写的程序没有给出例外处理，则当程序出错时用户无法得到提示，调试者也无法进行修改程序。所以，一般无论多简单的程序最好也要给出例外处理的要求。16.4.1.2 使用预定义的例外处理例。实际例子：procedure sum_interest_year(cur_procdate in varchar2 ,rtn out number) is/*/* 程序名: sprocess.SQL */* 功能: 由PB调用的PL/SQL子过程集，公积金转移金额及利息的计算等. */* 编程语言: PL/SQL for ORACLE 7.3.2,oracle8 v8.x */* 运行环境: ORACLE 7.3.X, oracle8i */* 修 改 : 赵元杰 */* 修改日期: 1999 06.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_det and per_detail 的余额转入per_fix_abst,将年结数据加入年余额表save_bal修改系统表日期 sssyspar.yearbegin=date_str*/set transaction use rollback segment hdhouse_rs;select distinct to_char(procdate,yyyymmdd) into ls_procdatefrom vw_tmp_inte;select to_char(to_number(substr(yearbegin,1,4)+1) into ls_yearfrom sssyspar;if substr(ls_procdate,1,4)ls_year thenrtn:=-1;return;elsif substr(ls_procdate,5,4)0630 thenrtn:=-1;return;end if;datestr:=substr(cur_procdate,1,4)|substr(cur_procdate,6,2)|substr(cur_procdate,9,2);update per_fix_abst set (avail_bal,acc_int,acc_int_fix,interest,interest_b,crea_int,crea_int_b) =(select bal+inteval+inteval_fix,acumbase,acumbase_fix,inteval_c+inteval_c_fix,inteval+inteval_fix,0,0from vw_tmp_inte where per_fix_abst.acc_no=vw_tmp_inte.accnoand per_fix_abst.emp_acc_no=vw_tmp_inte.emp_accno)where nvl(acc_status,0)=0;if sqlcode!=0 thenrtn:=sqlcode;rollback;return;end if;update per_abst set avail_bal=0,accu_int=0,interest=0where nvl(acc_status,0)=0;if sqlcode!=0 thenrtn:=sqlcode;rollback;return;end if;update per_detail set status=1 where nvl(status,0)=0and to_char(crea_date,yyyy/mm/dd)=cur_procdate;if sqlcode!=0 thenrtn:=sqlcode;rollback;return;end if;update per_fix_det set status=1 where nvl(status,0)=0and to_char(crea_date,yyyy/mm/dd)=cur_procdate;if sqlcode!=0 thenrtn:=sqlcode;rollback;return;end if;insert into per_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)select acc_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_codefrom per_fix_abst where nvl(acc_status,0)=0;if sqlcode!=0 thenrtn:=sqlcode;rollback;return;end if;insert into save_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)select acc_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 from per_fix_abst ,sssysparwhere nvl(acc_status,0)=0;if sqlcode!=0 thenrtn:=sqlcode;rollback;return;end if;update sssyspar set yearbegin=substr(cur_procdate,1,4)|-07-01;if sqlcode!=0 thenrtn:=sqlcode;rollback;return;end if;commit;rtn:=1;exceptionwhen no_data_found thenrtn:=sqlcode;rollback;wh