《ORACLE后台存储过程开发培训课件》由会员分享，可在线阅读，更多相关《ORACLE后台存储过程开发培训课件（35页珍藏版）》请在金锄头文库上搜索。
1、,ORACLE后台存储过程开发培训,2012-11-5,讲师：陈明林,过程、函数和包介绍 事务和异常定义 类型、参数和变量定义 游标、数组和内存表使用 开发技巧与性能优化,培训纲要,存储过程介绍,存储过程是由流控制和SQL语句书写，经编译和优化后存储在数据库服务器中的可执行代码。主要包含申明、执行、和异常三部分，入参和出参都可有可无，但不能设置返回值。 示例： create or replace procedure PROC_GET_DATA_CNT( i_ny in varchar2, o_cnt out number) as begin SELECT count(*) into o_cnt
2、 FROM TABLE_NAME WHERE NY = i_ny; end;,函数介绍,函数也是由流控制和SQL语句书写，经编译和优化后存储在数据库服务器中的可执行代码。主要包含申明、执行、和异常三部分，入参和出参都可有可无，切记必须为函数设置返回值。 示例： create or replace function FUN_GET_DATA_CNT(i_ny in varchar2) return number is v_cnt number; begin SELECT count(*) into v_cnt FROM TABLE_NAME WHERE NY = i_ny; return v_c
3、nt; end;,包介绍,存储过程和函数都仅能实现某单一的后台数据处理功能， 包是存储过程和函数的集合，由包和包体两部分组成，包和包 体中的不同过程和函数不仅可实现不同的数据处理，还为代码 的开发、维护和管理带来极大方便，也为越来越多的后台数据 库开发者所青睐。包头示例： create or replace package PKG_SP_CK as procedure PROC_GET_DATA_CNT(i_ny in varchar2, o_cnt out number); function FUN_GET_DATA_CNT(i_ny in varchar2) return number;
4、end PKG_SP_CK; 注意： 包头中可以不定义任何过程和函数，可仅定义常量、全局变量和自定义的数据类型，比如recorde、table等。,包介绍,包体示例：create or replace package body PKG_SP_CK as procedure PROC_GET_DATA_CNT(i_ny in varchar2, o_cnt out number)as begin.end PROC_GET_DATA_CNT; function FUN_GET_DATA_CNT(i_ny in varchar2) return number is begin.end FUN_GET
5、_DATA_CNT; end PKG_SP_CK; 注意：包头中定义的过程或者函数，在包体中必须实现。但在包体中定义的函数可以作为包体的私有过程和私有函数来使用，这些私有过程和函数不能被其它包体来调用。而包头中定义的函数和过程可以被自身包体和其它包体调用。 要求：包体中的过程/函数，在结束时都必须加上过程/函数名，这样能更好的区分多个过程/函数。,过程、函数和包介绍 事务和异常定义 类型、参数和变量定义 游标、数组和内存表使用 开发技巧与性能优化,培训纲要,事务定义,考虑到数据处理的安全性和事务的唯一性，需要对有数据更新（包括插入和删除）的处理进行提交或者回滚。一般情况下不 用显示申明事务，可
6、以把一个甚至多个过程/函数作为一个事务来处理。如果过程/函数间没有相互依存关系，建议对每一个过程/函数进行独立的事务处理，这样能提高过程的处理效率。 有一种情况需要定义事务来进行数据处理：当主函数调用子函数时，如果主函数不能提交而子函数需要实时提交，就需要为 子函数定义自动提交事务，比如主函数中调用日志输入函数，每一条日志都需要实时提交。这样方能保证在提交子函数中的数据 时不提交主函数中的数据。 事务定义语句： PRAGMA AUTONOMOUS_TRANSACTION; 注意：定义自动提交事务后，过程/函数中必须有提交/回滚处理。,异常定义,存储过程/函数都是以begin开始、以end结束，
7、都要求在end结束前捕获异常，这样能实时捕获异常进行异常处理，不会导致错误没有处理而程序挂起。 示例： create or replace procedure PROC_INS_XXX_DATA (i_ny in varchar2)as begin INSERT INTO TABLE_A SELECT * FROM TABLE_B WHERE NY = i_ny; commit; Exception when others then FUN_WRITE_LOG(); rollback; end;,异常定义,在过程/函数中如果处理比较复杂，不同的异常（或者检测到参数或者数据非法）需要进行不同的处
8、理，给调用方返回不同的错误消息，这中情况下就需要定义多个异常。 示例： create or replace procedure PROC_INS_XXX_DATA (i_ny in varchar2)as v_del_exp exception; -删除异常 v_ins_exp exception; -插入异常 begin begin DELETE FROM TABLE_A WHERE NY = i_ny; - 异常捕获 exception when others then raise v_del_exp; end;,异常定义,begin INSERT INTO TABLE_A SELECT
9、* FROM TABLE_B WHERE NY = i_ny; - 异常捕获 exception when others then raise v_ins_exp; end; commit; Exception when v_del_exp then FUN_WRITE_LOG(XXX表数据删除失败); rollback; when v_ins_exp then FUN_WRITE_LOG(XXX表数据生成失败); rollback; end;,过程、函数和包介绍 事务和异常定义 类型、参数和变量定义 游标、数组和内存表使用 开发技巧与性能优化,培训纲要,类型定义,过程/函数/包都支持开发者定
10、义记录和表等类型，定义之后就可以在参数、返回值和程序体中使用。 过程/函数/包中定义的类型： - 定义用户电费记录 TYPE YH_DF_REC IS RECORD( YHBH VARCHAR2(32), - 用户编号 ZDF NUMBER - 总电费 ); - 定义用户电费表 TYPE YH_DF_TAB IS TABLE OF YH_DF_REC INDEX BY INARY_INTEGER;,类型定义,用户定义的类型不仅可以定义在过程/函数/包中，还可以定义在ORACLE的用户下，这样可供用户下的所有过程/函数/包使用。 ORACLE用户下定义的类型： - 定义用户电费记录 CREATE
11、 OR REPLACE TYPE YH_DF_OBJ IS OBJECT ( YHBH VARCHAR2(32), - 用户编号 ZDF NUMBER - 总电费 ); - 定义用户电费表 CREATE OR REPLACE TYPE YH_DF_TBL” IS TABLE OF YH_DF_OBJ;,参数定义,过程/函数/包中的参数有输入参数、输出参数、输入输出参数三种类型，函数的返回值可以当输出参数来处理。示例： create or replace procedure PROC_GET_DATA_CNT( i_ny in varchar2, o_cnt out number, io_msg
12、 in out varchar2) as begin end; 说明：在定义参数时，出/入参规定以小写的io开头，且不需要指定数据长度；入参不能被赋值，出参用来输出数据；由于出 入参即可输入数据也可输出数据，不利于参数被多次使用，强 烈建议不要使用in out参数；参数类型不局限于字符和数字类型，可以用开发者自定义的记录和表、也可用表的行记录和ORACLE内置包的数据类型。,变量定义,过程/函数中定义的变量属于局部变量，而包头中定义的变量属于全局变量。由于全局变量可以被包体中的每一个过程/函数赋值，所以一定要谨慎使用。变量类型和参数类型完全一样，不局限于字符和数字类型，可以用开发者自定义的记录
13、和表、也可用表的行记录和ORACLE内置包的数据类型。 示例： create or replace procedure PROC_GET_DATA_CNT v_data_cnt number; -数据件数 v_row_num number; - 行号 begin end; 说明：在定义变量时，需要考虑参数长度是否合理。变量名可以用英文或者汉语拼音，但不可是汉语拼音和英文的组合。 要求：变量名以小写的v开头，每一个变量都必须有注释。定义的参数和变量都必须在程序中使用，编译时不能有错误和警告。,过程、函数和包介绍 事务和异常定义 类型、参数和变量定义 游标、数组和内存表使用 开发技巧与性能优化,培
14、训纲要,游标定义,游标（CURSOR）也叫光标，在关系数据库中经常使用，在PL/SQL程序中可以用CURSOR与SELECT一起对表或者视图中的数据进行查询并逐行读取。 示例： create or replace procedure PROC_GET_DFXX cursor v_cur is SELECT YHBH,ZDF FROM DF_FXMXB WHERE ; begin end; 说明：由于游标是在查询后逐行读取数据，所以执行效率比较低。 但游标有一个优点：游标是分批次从数据库中查询得到数据，而不是一次性将全部数据装载到游标变量中，不会有数据库内存不够问题。因此游标对十万、百万级以上的
15、数据处理是十分重要。,游标使用,create or replace procedure PROC_GET_DFXX v_yhbh DF_FXMXB.YHBH%TYPE;-用户编号 v_zdf DF_FXMXB.ZDF%TYPE; -总电费 cursor v_cur is SELECT YHBH,ZDF FROM DF_FXMXB WHERE ; begin open v_cur; loop fetch v_cur into v_yhbh, v_zdf exit when C%notfound; - 进行数据插入/更新处理 end loop; close v_cur; exception whe
16、n v_cur%isopen then close v_cur; end;,游标使用,create or replace procedure PROC_GET_DFXX v_data DF_FXMXB.%ROWTYPE;-电费发行数据 cursor v_cur is SELECT * FROM DF_FXMXB WHERE ; begin open v_cur; loop fetch v_cur into v_data exit when C%notfound; v_data.YHBH, v_data.ZDF, - 进行数据插入/更新处理 end loop; close v_cur; exception when v_cur%isopen then close v_cur; end;,游标使用,游标数据来源于多表不能用行变量，可以
