oracle 存储过程实例.docx

oracle 存储过程实例.docx

《oracle 存储过程实例.docx》由会员分享，可在线阅读，更多相关《oracle 存储过程实例.docx（26页珍藏版）》请在冰豆网上搜索。

oracle存储过程实例

一、过程（PROCEDURE）

  过程是作为一个单独的程序编译到Oracle数据库模式中的。

过程能够接收参数。

在编译过程时，CreateProcedure语句的过程标识符在数据字典中成为对象名。

过程结构如下：

   CREATE OR REPLACE PROCEDURE  过程名 （可选参数）  IS

         声明部分

   BEGIN

         程序体

   EXCEPTION

         异常处理程序

   END 过程名

过程的命名应该用动词。

因为过程通常是执行某种动作，比如，更新数据库、写文件，或者发送消息。

过程并不一定具有参数。

当创建的过程没有参数时，就不需要使用圆括号。

当调用过程时空括号是可选的。

可以编码为IS或AS，两种语法都是合法的。

Sql代码

1.PROCEDURE INSERT_TEMP IS （| AS）  

PROCEDUREINSERT_TEMPIS（|AS）

尽管将过程名追加到END子句后是可选的，但强烈推荐这么做。

例如：

Sql代码

1.Create table temp（n number）;  

Createtabletemp（nnumber）;

定义一个过程：

Sql代码

1.PROCEDURE INSERT_TEMP IS  

2.BEGIN  

3.   INSER INTO TEMP （n） VALUES （0）;  

4.END INSERT_TEMP;  

PROCEDUREINSERT_TEMPIS

BEGIN

INSERINTOTEMP（n）VALUES（0）;

ENDINSERT_TEMP;

   常见的过程样式是将IS、BEGIN、EXCEPTION和END对齐。

这些关键字作用域内的全部代码都要进行缩进。

Sql代码

1.PROCEDURE  PRINT_TEMP   

2.IS  

3.    v_average  NUMBER;  

4.    v_sum  NUMBER;  

5.BEGIN  

6.    SELECT  AVG（N）, SUM（N）  INTO  v_average,v_sum FROM TEMP;  

7.    dbms_output.put_line（‘Average:

’ || v_average ）;  

8.    dbms_output.put_line（‘Sum:

’ || v_sum）;  

9.END  PRINT_TEMP;  

PROCEDUREPRINT_TEMP

IS

v_averageNUMBER;

v_sumNUMBER;

BEGIN

SELECTAVG（N）,SUM（N）INTOv_average,v_sumFROMTEMP;

dbms_output.put_line（‘Average:

’||v_average）;

dbms_output.put_line（‘Sum:

’||v_sum）;

ENDPRINT_TEMP;

   单独的过程常会开发成为一个新包或者一个已有的包合并。

将INSERT_TEMP合并到一个包中，仅需要进行下面简单的编辑工作：

1.首先在ORACLE建立PACKAGE

Sql代码

1.CREATE  OR  REPLACE  PACKAGE  TEMP_OP  IS  

2.       PROCEDURE  INSERT_TEMP;  

3.END  TEMP_OP;  

CREATEORREPLACEPACKAGETEMP_OPIS

PROCEDUREINSERT_TEMP;

ENDTEMP_OP;

2.建立PACKAGEBODY

Sql代码

1.CREATE  OR  REPLACE  PACKAGE  BODY  TEMP_OP  IS  

2.       PROCEDURE  INSERT_TEMP  IS  

3.             BEGIN  

4.                  INSERT INTO temp（n）  VALUES （0）;  

5.       END INSERT_TEMP;  

6.END  TEMP_OP;  

CREATEORREPLACEPACKAGEBODYTEMP_OPIS

PROCEDUREINSERT_TEMPIS

BEGIN

INSERTINTOtemp（n）VALUES（0）;

ENDINSERT_TEMP;

ENDTEMP_OP;

二、函数（FUNCTION）

  包通常扮演API的角色，隐藏对象，提供对象上的操作。

而函数常扮演对象状态信息的选择器。

设想一个要计算对象的某个属性值的函数。

函数不是动作者，而是状态的计算值。

所以应该用名词对函数进行命名。

FUNCTION student_status （可选参数）  RETURNVARCHAR2IS

   声明部分

BEGIN

   子程序体Programbody

   RETURNexpression;

EXCEPTION

   异常处理程序，其中应该包括一条RETURN语句

END student_status;

参数是可选的，但是RETURN语句却是必须具备的，FUNCTION语句必须包括一个RETURN和类型。

说明：

1.声明部分

声明变量，需要返回的变量也是在这里声明的。

函数必须具有返回值。

如果函数的返回值是一个NUMBER，则该NUMBER变量就在这里声明。

该变量应该出现在RETURN语句中。

2.子程序体

支持循环、if-then-else结构、case语句和declare-block结构。

程序体必须包括RETURN语句。

3.异常处理程序

可选的，可以编写用于特定类型错误的异常处理程序或者是通用的异常处理程序，确定异常处理程序中包含了RETURN语句。

例如：

Sql代码

1.CREATE  OR  REPLACE  FUNCTION   tomorrow   RETURN   DATE   

2.IS  

3.     next_day  DATE;    

4.BEGIN  

5.     next_day :

= SYSDATE +1 ;  

6.     RETURN  next_day;  

7.END  tomorrow;  

CREATEORREPLACEFUNCTIONtomorrowRETURNDATE

IS

next_dayDATE;

BEGIN

next_day:

=SYSDATE+1;

RETURNnext_day;

ENDtomorrow;

不声明变量，可以直接简写为：

Sql代码

1.CREATE  OR  REPLACE  FUNCTION   tomorrow   RETURN   DATE  IS  

2.BEGIN  

3.     RETURN  SYSDATE +1 ;  

4.END  tomorrow;  

CREATEORREPLACEFUNCTIONtomorrowRETURNDATEIS

BEGIN

RETURNSYSDATE+1;

ENDtomorrow;

如果函数没有参数，则不要在函数定义时使用空括号。

这规则同样适用于过程。

使用函数：

Sql代码

1.CREATE  OR  REPLACE   sample  

2.IS  

3.    today   DATE;  

4.BEGIN  

5.    today :

= tomorrow – 1;  

6.dbms_output.put_line（tomorrow - 1）;  

7.END sample;  

CREATEORREPLACEsample

IS

todayDATE;

BEGIN

today:

=tomorrow–1;

dbms_output.put_line（tomorrow-1）;

ENDsample;

三、包（PACKAGE）

   利用包提供这样一套机制：

将较小的程序单元在逻辑上组合在一起。

这种由过程到包的组合就是代码的模块化。

包的使用意味着只需要管理更少的文件和更少的模块。

对于程序员来说更容易做到模块的重用。

迁移过程包括将这些过程体分别复制到同一个包体中。

过程接口定义成为包规范。

最后可以通过添加新的过程和函数来加强包的整体功能。

完成包的合并后，首先编译包规范，然后编译包体。

例如：

Sql代码

1.PACKAGE   application_name   IS  

2.     PROCEDURE  p1;  

3.     PROCEDURE  p2;  

4.END application_name;  

5.  

6.PACKAGE  BODY  application_name  IS  

7.     PROCEDURE  p1  IS  

8.     BEGIN  

9.         PL/SQL  code  

10.     END  p1;  

11.     PROCEDURE  p2  IS  

12.     BEGIN  

13.         PL/SQL  code  

14.     END  p2;  

15.END application_name;  

PACKAGEapplication_nameIS

PROCEDUREp1;

PROCEDUREp2;

ENDapplication_name;

PACKAGEBODYapplication_nameIS

PROCEDUREp1IS

BEGIN

PL/SQLcode

ENDp1;

PROCEDUREp2IS

BEGIN

PL/SQLcode

ENDp2;

ENDapplication_name;

四、包规范

   PL/SQL语言要求将一个程序集合的接口编译成为单一的程序单元。

这个单元，也就是包规范。

这只是定义了API接口。

而应用逻辑的具体实现则包含在包体中。

包规范可以是一个单独的ASCII文本文件，能编译成单一程序单元。

包体也可以是一个单独的ASCII文本文件。

必须首先成功编译包规范，然后才能编译包体。

可以把包规范和包体放入同一个文件中。

1.语法与格式

最基本的包规范语法是：

Sql代码

1.CREATE  PACKAGE  package_name  IS  

2.     Type  definitions  for  records, index-by  tables, varrays, nested  tables   

3.Constants  

4.Exceptions  

5.Global  variable  declarations  

6.PROCEDURE  procedure_name_1  （parameters & types）;  

7.FUNCTION  function_name_1 （parameters & types）  RETURN  type;  

8.END package_name;  

CREATEPACKAGEpackage_nameIS

Typedefinitionsforrecords,index-bytables,varrays,nestedtables

Constants

Exceptions

Globalvariabledeclarations

PROCEDUREprocedure_name_1（parameters&types）;

FUNCTIONfunction_name_1（parameters&types）RETURNtype;

ENDpackage_name;

  包规范对过程和函数出现的顺序没有要求。

而且包规范中的每个子程序都必须有一个与之相对应的子程序体。

包规范可以声明数据类型，数据声明和异常。

在包规范中声明的所有数据对象都是全局的。

所以在包规范声明的变量只是那些作用域是全局的变量。

   包体中的PROCEDURE语句必须与相应包规范中的PROCEDURE语句相匹配。

包括子程序名称、参数名称、参数模式和参数类型等。

这一要求同样适用于FUNCTION;

包规范可以声明异常。

异常或者全部声明在规范开头，或者全部声明在规范结尾。

例如：

Sql代码

1.CREATE  PACKAGE  package_name  IS  

2.    Invalie_operation   EXCEPTOIN;  

3.    PROCEDURE procedure_name_1 （ parameters  &  types）;  

4.    …  

5.END   package_name;  

CREATEPACKAGEpackage_nameIS

Invalie_operationEXCEPTOIN;

PROCEDUREprocedure_name_1（parameters&types）;

…

ENDpackage_name;

处理异常的应用程序代码类似于：

Sql代码

1.BEGIN  

2.    other  code, etc  

3.    package_name.procedure_name_1（parameters）;  

4.         other  code, etc  

5.EXCEPTION  

6.    WHEN  package_name.invalid_operation  THEN  do something;    

7.END;  

BEGIN

othercode,etc

package_name.procedure_name_1（parameters）;

othercode,etc

EXCEPTION

WHENpackage_name.invalid_operationTHENdosomething;

END;

五、参数与模式

PL/SQL有三种模式：

1.IN（默认）

   传给子程序的IN模式参数表明了子程序只能将该参数作为一个常量来使用。

这是只读的。

作为IN模式的参数可以是一个文字表达式、常量声明或者变量声明。

当参数为变量时，该模式提供了安全措施保证正确的程序调用。

调用程序能够了解在完成调用后，该变量的值没有发生改变。

下面的过程不能编译，原因是对IN模式变量进行了写操作。

Sql代码

1.PROCEDURE print_next_value （  

2.    v_data  IN  INTEGER   

3.）  

4.BEGIN  

5.    v_data  :

= v_data +1 ;--compile error  

6.    dbms_output.put_line（v_data）;   

7.dbms_output.put_line（v_data + 1）; --compile correct  

8.END;  

PROCEDUREprint_next_value（

v_dataININTEGER

）

BEGIN

v_data:

=v_data+1;--compileerror

dbms_output.put_line（v_data）;

dbms_output.put_line（v_data+1）;--compilecorrect

END;

2.IN OUT

  能够通过这种模式传递的参数只能是变量类型，不允许为文字或者常量。

前提是被调用的过程将会改变传递的内容。

被调过程也能对其进行读写操作。

当查看一个具有INOUT模式参数的过程时，要求调用程序在调用该过程时必须提供数据。

这是INOUT参数中IN部分的要求。

例如：

Sql代码

1.PROCEDURE  change_data （  

2.    v_data  IN  OUT   INTEGER  

3.）  IS  

4.BGIN  

5.    for  i   in   1..10   loop  

6.        v_data :

= v_data +1;  

7.    end loop;  

8.END  change_data;  

PROCEDUREchange_data（

v_dataINOUTINTEGER

）IS

BGIN

foriin1..10loop

v_data:

=v_data+1;

endloop;

ENDchange_data;

块调用

Sql代码

1.DECLARE  

2.    my_data  INTEGER :

=0;--不能为常量  

3.BEGIN  

4.    change_data（my_data）;  

5.    dbms_output.put_line（‘block print:

’  ||  my_data）;--10  

6.END;  

DECLARE

my_dataINTEGER:

=0;--不能为常量

BEGIN

change_data（my_data）;

dbms_output.put_line（‘blockprint:

’||my_data）;--10

END;

3.OUT

   能够通过这种模式传递参数只能是变量类型。

不允许为文字或者常量。

在子程序中，一个OUT模式参数的初始值为NULL。

使用OUT模式参数的目的在于传递关于接口的信息。

调用过程不必为被调过程传递参数。

被调过程完成对数据结构的读写操作。

例如：

Sql代码

1.PROCEDURE provide_data（  

2.    v_data   OUT   INTEGER  

3.）  IS  

4.BEGIN  

5.    v_data :

= 100;  

6.    for  i   in  1..10  loop  

7.        v_data :

= v_data +1;  

8.end loop;   

9.END  provide_data;  

PROCEDUREprovide_data（

v_dataOUTINTEGER

）IS

BEGIN

v_data:

=100;

foriin1..10loop

v_data:

=v_data+1;

endloop;

ENDprovide_data;

块中调用

Sql代码

1.DECLARE  

2.    my_data   INTEGER :

=0;  

3.BEGIN  

4.    insert  into temp values（my_data）;--0  

5.    provide_data（my_data） ;  

6.    insert  into temp values（my_data）;--110  

7.END;  

DECLARE

my_dataINTEGER:

=0;

BEGIN

insertintotempvalues（my_data）;--0

provide_data（my_data）;

insertintotempvalues（my_data）;--110

END;

六、函数与模式

   函数常常用名词来命名，而过程则常用动词来命名。

在所有应用程序中，绝大多数的函数参数都是IN模式的。

但是函数参数的模式可以是所有这3种模式。

下面展示了关于一个函数的设计，该函数返回数据和状态信息。

对于这个接口，假定ARG——1是主键，用来精确确定需要获取的记录。

参数next_rec是需要的数据。

Sql代码

1.FUNCTION   next_rec（ arg1   IN   type,  next_record  OUT   type）  

2.RETURN BOOLEAN;  

FUNCTIONnext_rec（arg1INtype,next_recordOUTtype）

RETURNBOOLEAN;

这种设计允许用户编写如下代码：

Sql代码

1.WHILE （ next_rec（arg1,my_record_structure） ）  

2.LOOP  

3.    process  my_record_structure;  

4.END  LOOP;  

WHILE（next_rec（arg1,my_record_structure））

LOOP

processmy_record_structure;

ENDLOOP;

可以通过下面的过程来代替函数:

Sql代码

1.PROCEDURE  get_next_rec（  

2.arg1         IN   type,  

3.next_record  OUT  type,  

4.status       OUT  BOOLEAN  

5.）;  

PROCEDUREget_next_rec（

arg1INtype,

next_recordOUTtype,

statusOUTBOOLEAN

）;

使用：

Sql代码

1.LOOP  

2.    get_next_rec（arg1,my_record_structure,status）;  

3.    EXIT  WHEN  NOT  status;  

4.    process   my_record_structure;  

5.END   LOOP;  

LOOP

get_next_rec（arg1,my_record_structure,status）;

EXITWHENNO