Oracle 存储过程，函数和包。

1. 存储过程和函数
1.1 创建和删除存储过程
            创建存储过程，需要有CREATE PROCEDURE 或 CREATE ANY PROCEDURE的系统权限.
基本语法如下：

            CREATE  OR REPLACE PROCEDURE  SP_NAME( PM_NAME ［IN/OUT/IN OUT］ PM_TYPE...)
            { AS}   
            [说明(变量定义)部分]
            BEGIN
                  可执行部分
            [ EXCEPTION]
                  错误处理部分
            END ［SP_NAME］ ;

参数部分用于定义若干个参数（若没有参数，可以忽略）。参数有三种形式： IN / OUT 和 IN OUT, 默认为IN。
关键字AS 也可以写成 IS.

删除stored procedure 的用户必须是其owner或者具有 DROP ANY PROCEDURE的权限。
语法如下：
            DROP PROCEDURE SP_NAME;


如果要 重新compile 1个 stored procedure. 则用户必须是其owner或者拥有 ALTER ANY PROCEDURE的权限
语法如下：
          ALTER PORCEDURE SP_NAME COMPILE;

执行stored procedure 的用户必须是其owner 或者具有EXECUTE ANY PROCEDURE 的权限。

方法1：
        EXECUTE 模式名.SP_NAME[(PARAMETER..];
方法2：
        BEGIN
             模式名.SP_NAME[(PARAMETER..]
        END;

如果是调用本帐户下的存储过程， 则模式名可以省略。 要调用其他帐户编写的存储过程，则模式明必须添加。

例1：
步骤1： 登录SCOTT帐号 (默认密码是tiger)
步骤2： 创建1个返回雇员总人数的stored procedure

注1： 每一条语句都必须用;结尾，否则编译出错。
注2： 变量名不能用＠字符开头，否则编译出错～
注3： 变量从select 语句复制赋值应该 用 select field1, field2 into v_va1,v_val2 from tb_name.
          而不能使用Sybase的  select v_val1 = field1, v_val2 = field2 from tb_name.

步骤3：执行存储过程。

例2：
循环列出，emp表中所有员工name, 并调用上面的存储过程显示总人数。
步骤1: 编写Stored procedure 如下图

注1： cursor 也必须定义在as 与 begin end之间。
注2 ： 执行另1个stored procedure, 直接输入sp名字加";" 符号就可以。

步骤2: 执行stored procedure.

 

 

 

1.2 参数传递
Oracle stored procedure的参数有3种类型如下：

参数类型           说明
IN                       定义1个输入参数变量，用于传递参数给stored procedure
OUT                    定义1个输出参数变量，用于从stored procedure中获取数据
IN OUT               定义1个输入输出参数，同上拥有以上两个功能。

用法如下：

      PM_name IN DATA_type DEFAULT default_value;
       定义1个输入参数变量，用于传递参数给stored procedure.在调用sp时，主程序的实际参数可以是常量，有值变量或表达式等。Default关键字为可选项，用来设定参数的默认值。如果在调用stored procedure时不指明参数，则该参数变量取默认值。 在存储过程中，输入变量用于接受主程序传递的值， 但不能对其进行赋值。

      PM_name OUT DATA_type;
       定义1个输出参数变量，用于从stored procedure中获取数据，即变量从存储过程中返回值给程序。
       在调用sp时，主程序传给sp的输出参数只能是1个变量，不能是常量或者表达式。 在sp中，参数变量只能被赋值，而不能将其的值赋予其他对象，在sp中必须给输出变量赋值1次。

        PM_name IN OUT DATA_type DEFAULT default_value;
        定义1个输入输出变量，同时具有以上功能。在调用sp时，主程序传给sp的实际参数只能是变量，而不能是常量或表达式。Default关键字同样是可选项，用来设定参数的默认值。 在sp内，变量接受主程序传递的值，同时可以参加赋值运算，也可以对其赋值。在sp内至少对其赋值 一次。

 如果省略 IN， OUT 或 IN OUT关键字，则默认是 IN.

例3：
编写1个给员工增加工资的stored procedure CHANGE_SALARY  通过对IN类型的参数传递 要增加工资的雇员编号和增加的工资额。
步骤1： 编写sp如下图：

 


步骤2： 执行sp如下图

可以见到7788的工资由3000 变成3020

参数传递的个数和顺序应该与定义的一致，如果顺序不一致也可以采用如下方法：
EXECUTE USP_CHANGE_SALARY(p_raise=>20, p_empno=>7788);

 
例4：
使用OUT类型参数返回stored procedure的结果。
步骤1： 分别编写两个sp， 其中USP_GET_EMP_COUNT通过使用 OUT类型参数，将值传给另1个sp USP_SHOW_EMP_COUNT.

 

 

步骤2：执行sp

 
说明： 在存储过程中定义了OUT类型的参数P_TOTAL, 在主程序调用该存储过程时，传递了参数v_empcount. 在存储过程的
select .. into ...语句中对p_total进行赋值。赋值结果有v_empcount变量带回给主程序显示。

例5：
使用 IN OUT类型的参数，给电话号码增加区码。
步骤1：
编写并编译如下存储过程：

 
步骤2：
输入以下命令执行该存储过程：

 
可以见到下面输出了加了区号的电话号码。
注1： Oracle 语句不区分大小写
注2 ：赋值语句请用:=    而不能用=.

1.3 创建和删除存储函数
      创建函数,需要CREATE PROCEDURE 或 CREATE ANY PROCEDURE的系统权限。

      CREATE [ OR REPLACE] FUNCTION func_name[(para_name para_type...)]
      RETURN Data_type
      IS
            [declare part]
      Begin
            main part
            RETURN (expression)
      [ EXCEPTION
              errors handle part]
      END [func_name] ;

       其中，参数是可选的，但是只能是IN类型（IN关键字一般被忽略）。
       在定义部分的RETURN 数据类型，用来表示函数的数据类型，也就是返回值的数据类型，不可忽略。
       在可执行部分的Return(表达式),用来生成函数的返回值，其表达式的类型必须跟定义部分说明的
函数类型一致。在函数的执行部分可以有多个Return语句，但只有1个会被执行，一旦执行了return语句，
则函数结束并返回调用环境。
       一个存储函数在不需要时可以换删除，但删除的人应该是函数的创建者或者是拥有DROP ANY PROCEDURE系统权限的人，其语法如下：
      
       DROP FUNCTION func_name;

        重新编译一个存储函数时,编译的人应该是函数的创建者或者拥有ALTER ANY PROCEDURE系统权限的人，重新编译一个存储函数的语法如下：

         ALTER PROCEDURE func_name COMPILE;

         函数的调用者应该是函数的创建者或者拥有EXECUTE ANY PROCEDURE系统权限的人，或者是被函数的拥有者授予了函数执行权限的用户，函数的引用和存储过程不同，函数需要出现在程序体重，可以参加表达式的运算和单独出现在表达式中，其形式如下：
     
         Varible_name := func_name(para...);

例6：
        创建1个通过雇员编号返回雇员名称的函数GET_EMP_NAME.
        步骤1：
                  编写并编译如下存储过程


       步骤2： 测试：

会见到如果输入的数据在数据库不存在， 函数会捕捉错误信息。

1.5 存储过程和函数的查看
       可以通过对数据字典的访问来查询存储过程或函数的有关信息，如果要查询当前用户的存储过程或函数的源代码，可以通过对USER_SOURCE数据字典视图查询得到。USER_SOURCE的结构如下：

说明： 里面按行存放着过程或函数的脚步，name 是名字， Type表示类型（SP or function),line是行号，Text为对应行的脚本。

  例7: 查询过程USP_EMPCOUNT的代码。

 

例8: 查询函数GET_EMP_NAME的参数

 

例9: 查询USP_EMP_LIST 存储过程是否可用

 
说明： 这里要从视图user_objects里查询，VALID表示该存储过程有效（即是通过了编译），INVALID表示无效或未通过编译。当Oracle调用1个无效的存储过程或函数时，首先试图对其进行编译，如果编译成功则将状态status设置成valid并执行，否则给出错误信息。








      当1个存储过程编译成功，状态变成valid,会不会在某些情况下变成invalid呢，结论是完成可能的，比如1个存储过程里面哟你道的1张表被update或删除了。存储过过程就会变成无效invalid,所以要注意存储过程和函数与其他对象的依赖关系。
      如果要检查stored procs和function的依赖性，可以通过查询数据字典 USER_DEPENDENCIES来确定,
      该视图结构如下：    
       

说明：
          Name为对象名，
          Type为对象类型，
          referenced_owner 为涉及到的（被依赖的）对象的拥有者账户，
          referenced_name   被依赖的对象名
          referenced_type    被依赖的对象的类型
         
参考下图：

 

           还有一种情况需要注意，如果1个用户A被授予执行属于用户B的一个存储过程，在用户B的存储过后层访问到用户C的表，用户B被授予访问用户C表的权限，但用户A没有授予访问用户C的表的权限，那么用户A调用用户B的存储过程是失败的还是成功的呢？答案是成功的，可以自己实际测试下。




2. 包 （Package)

2.1 包的概念和组成
          包是用来存储相关程序结构的对象，他存储与数据字典中，包由两个分离的部分组成，包头（Package)和包体(Package body).包头是包的说明部分， 是对外的操作接口，对应用是可见的，包体是包的代码的实验部分，对应用来说是不可见的黑盒。


2.2 包中包含的程序结构
             包中可以包含的程序结构如下面这张表所示。
                                      包中包含的程序结构
              程序结构                               说明
              过程(Procedure)                    带参数的命名的程序模块
              函数(Function)                      带参数，具有返回值的命名程序模块
              变量(Variable)                       存储变化的量的存储单元
              常量(Constant)                      存储不变的量的存储单元
              游标(Cursor)                          用户定义的数据操作缓存去，可以在执行部分使用。
              类型(Type)                            用户定义的新的结构类型
              异常(Exception)                     在标准包中定义或由用户自定义，用户处理程序错误。

 
              
                                               
               
2.3 包中元素的性质
   
说明部分可以出现在包的三个不同部分，出现在包头中的称为共有元素，出现在包体中的称为私有元素，出现在包体的过程或函数中的称为局部变量。 他们的性质有所不同，如下表中表示

           

 
          在包体中出现的过程或函数，如果需要对外公用，就必须在包头中说明，包头中的说明应该和包体中的说明一致。

          包有以下优点:
          * 包可以方便地将存储过程和函数组织到一起,每个包又是相互独立的. 在不同的包中,过程,函数都可以重名,这解决了同1个用户环境中命名的冲突问题

           *包增强了对存储过程和函数的安全管理,对整个包的访问权只需要一次授予.

           *在同1个会话中,公用变量的值将被保留,知道会话结束.

           * 区分了共有过程和私有过程,包的私有过程增强了过程和函数的保密性.

            * 包在被首次调用时,就作为1个整体被全部调入内存,减少了多次访问过程或函数的I/O次数.


2.4 创建包和包体
          
            包由包头和包体两部分组成, 包的创建应该先创建包头部分, 然后再创建包体部分. 创建, 删除和编译包的权限同创建,删除和编译存储过程的权限相同.

创建包头命令如下:

CREATE [OR REPLACE] PACKAGE PK_NAME
IS

共有变量定义
共有类型定义
共有游标定义
共有异常定义

函数说明
过程说明

END;                     

创建包体命令如下:
CREATE [OR REPLACE] PACKAGE BODY PK_NAME
IS

私有变量定义
私有类型定义
私有游标定义
私有异常定义
函数定义
过程定义

END; 

删除包头:
Drop Package PKNAME

删除包头:
Drop Package body PKNAME

重新编译包头:
Alter Package PKNAME COMPILE PACKAGE

 
重新编译包体:
Alter Package PKNAME COMPILE PACKAGE BODY

在包头说明的对象可以在包外调用,调用的方法和调用单独的过程或函数方法基本相同, 唯一的区别就是要在调用的过程或函数名子前加上包的名字(中间要用".")分隔.  但要注意.不同的session将单独对包的公用变量进行初始化,所以不同的session会对包的调用属于不同的应用.


2.5 系统包
       Oracle预定义了很多标准的系统包, 这些包可以在应用中直接使用,比如在例子中我们使用的DBMS_OUTPUT包,就是其中1个系统包,而PUT_LINE是该包的一个函数. 常用的系统包如下面表所示:

 
2.6 包的应用

      在sql*plus环境下,包和包体可以分别编译,也可以一齐编译. 如果分别编译,则要先编译包头,再编译包体,如果一齐编译,则包头写在前,包体在后,中间用"/"分隔.

      可以将已经存在的SP或function添加到包中,犯法是去掉过程或函数的创建语句 CREATE OR REPLACE部分, 将存储过程或函数复制到包体中,然后重新编译即可.

       如果需要将私有过程或函数变成共有过程或函数的话, 将过程或函数说明部分复制到到包头说明部分,然后重新编译就可以了.
       
       
例10: 创建管理雇员信息的包PAK_EMP, 它具有从EMP表获得雇员信息,修改雇员名称,修改雇员工资和写回EMP表的功能.

步骤1:
编写如下代码并编译.

包头部分:

包体部分:
声明部分及 SHOW_DETAIL:

 

 
GET_EMP:

SAVE_EMP:

 
CHANGE NAME及 CHANGE_SAL:

 

步骤2:获取雇员7788的信息:
 

步骤3: 显示雇员信息:

步骤4: 修改雇员工资:

 注意此时 修改是变量emp_row 也就是员工7788的工资.而且未写入数据库.

步骤5: 将雇员信息写入emp表:

 
说明: row_emp作为包PAK_EMP的私有变量,只能被包里面的对象所访问.

 

转载于:https://www.cnblogs.com/nvd11/archive/2013/04/02/2996839.html