Oracle 存储过程，函数和包。

向右看齐 2022-03-19 12:57 516阅读 0赞

**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

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011]  
  
注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：执行存储过程。

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 1]  
  
**例2：**  
循环列出，emp表中所有员工name, 并调用上面的存储过程显示总人数。  
步骤1: 编写Stored procedure 如下图

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 2]  
  
注1： cursor 也必须定义在as 与 begin end之间。  
注2 ： 执行另1个stored procedure, 直接输入sp名字加";" 符号就可以。  
  
步骤2: 执行stored procedure.

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 3]

**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如下图：

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 4]

步骤2： 执行sp如下图

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 5]  
可以见到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.

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 6]

步骤2：执行sp

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 7]

说明： 在存储过程中定义了OUT类型的参数P\_TOTAL, 在主程序调用该存储过程时，传递了参数v\_empcount. 在存储过程的  
select .. into ...语句中对p\_total进行赋值。赋值结果有v\_empcount变量带回给主程序显示。  
  
**例5：**  
使用 **IN OUT**类型的参数，给电话号码增加区码。  
步骤1：  
编写并编译如下存储过程：

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 8]

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

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 9]

可以见到下面输出了加了区号的电话号码。  
注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：  
                  编写并编译如下存储过程  
![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 10]  
  
       步骤2： 测试：

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 11]  
  
  
会见到如果输入的数据在数据库不存在， 函数会捕捉错误信息。  
  
**1.5 存储过程和函数的查看**  
       可以通过对数据字典的访问来查询存储过程或函数的有关信息，如果要查询当前用户的存储过程或函数的源代码，可以通过对USER\_SOURCE数据字典视图查询得到。USER\_SOURCE的结构如下：

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 12]  
  
说明： 里面按行存放着过程或函数的脚步，name 是名字， Type表示类型（SP or function),line是行号，Text为对应行的脚本。

**例7: 查询过程USP\_EMPCOUNT的代码。**

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 13]

**例8: 查询函数GET\_EMP\_NAME的参数**

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 14]

**例9: 查询USP\_EMP\_LIST 存储过程是否可用**

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 15]

说明： 这里要从视图user\_objects里查询，VALID表示该存储过程有效（即是通过了编译），INVALID表示无效或未通过编译。当Oracle调用1个无效的存储过程或函数时，首先试图对其进行编译，如果编译成功则将状态status设置成valid并执行，否则给出错误信息。  
  
  
  
  
  
  
  
  
      当1个存储过程编译成功，状态变成valid,会不会在某些情况下变成invalid呢，结论是完成可能的，比如1个存储过程里面哟你道的1张表被update或删除了。存储过过程就会变成无效invalid,所以要注意存储过程和函数与其他对象的依赖关系。  
      如果要检查stored procs和function的依赖性，可以通过查询数据字典 **USER\_DEPENDENCIES**来确定,  
      该视图结构如下：

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 16]  
  
说明：  
          Name为对象名，  
          Type为对象类型，  
          referenced\_owner 为涉及到的（被依赖的）对象的拥有者账户，  
          referenced\_name   被依赖的对象名  
          referenced\_type    被依赖的对象的类型  
           
参考下图：

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 17]

还有一种情况需要注意，如果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**  **包中元素的性质  
   **  
 说明部分可以出现在包的三个不同部分，出现在包头中的称为共有元素，出现在包体中的称为私有元素，出现在包体的过程或函数中的称为局部变量。 他们的性质有所不同，如下表中表示

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 18]

在包体中出现的过程或函数，如果需要对外公用，就必须在包头中说明，包头中的说明应该和包体中的说明一致。  
  
          包有以下优点:  
          \* 包可以方便地将存储过程和函数组织到一起,每个包又是相互独立的. 在不同的包中,过程,函数都可以重名,这解决了同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是该包的一个函数. 常用的系统包如下面表所示:

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 19]

**2.6**  **包的应用**  
  
      在sql\*plus环境下,包和包体可以分别编译,也可以一齐编译. 如果分别编译,则要先编译包头,再编译包体,如果一齐编译,则包头写在前,包体在后,中间用"/"分隔.  
  
      可以将已经存在的SP或function添加到包中,犯法是去掉过程或函数的创建语句 **CREATE OR REPLACE**部分, 将存储过程或函数复制到包体中,然后重新编译即可.  
  
       如果需要将私有过程或函数变成共有过程或函数的话, 将过程或函数说明部分复制到到包头说明部分,然后重新编译就可以了.  
         
         
**例10: 创建管理雇员信息的包PAK\_EMP, 它具有从EMP表获得雇员信息,修改雇员名称,修改雇员工资和写回EMP表的功能.**  
  
步骤1:  
编写如下代码并编译.  
  
包头部分:

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 20]  
  
包体部分:  
声明部分及 SHOW\_DETAIL:

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 21]

GET\_EMP:

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 22]  
  
SAVE\_EMP:

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 23]

CHANGE NAME及 CHANGE\_SAL:

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 24]

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

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 25]  
  
步骤3: 显示雇员信息:

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 26]  
  
步骤4: 修改雇员工资:

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 27]

注意此时 修改是变量emp\_row 也就是员工7788的工资.而且未写入数据库.  
  
步骤5: 将雇员信息写入emp表:

![Oracle 存储过程，函数和包。 - 饥民 - 饥民2011][Oracle _ - _ - _2011 28]

说明: row\_emp作为包PAK\_EMP的私有变量,只能被包里面的对象所访问.

[Oracle _ - _ - _2011]: http://img3.ph.126.net/wYqshyGUjHXIJoINmB727g==/6597872410144606663.jpg
[Oracle _ - _ - _2011 1]: http://img1.ph.126.net/EPwCzBV_k8TMVLpps-9SDA==/6598264935795551621.jpg
[Oracle _ - _ - _2011 2]: http://img7.ph.126.net/x-yHp0vIXRUPFAneJprA7g==/6597472187912199327.jpg
[Oracle _ - _ - _2011 3]: http://img9.ph.126.net/bIHvR900ZNcH6h_sAd2DNw==/6597802041400453859.jpg
[Oracle _ - _ - _2011 4]: http://img8.ph.126.net/GsyajhRCZHRqdrVtzcAgNA==/6598256139702550218.jpg
[Oracle _ - _ - _2011 5]: http://img5.ph.126.net/e1TWB0C25u3wMHxWR4pV7Q==/6597924087191138725.jpg
[Oracle _ - _ - _2011 6]: http://img7.ph.126.net/zL_bS2dpdbx-3vOzibk0HQ==/6597254484611207507.jpg
[Oracle _ - _ - _2011 7]: http://img3.ph.126.net/axwOsbltwjGdZqN-T4hkKg==/6597991157400433333.jpg
[Oracle _ - _ - _2011 8]: http://img0.ph.126.net/N36b1kamXZTtIhMa1xXZTw==/6597545855191546990.jpg
[Oracle _ - _ - _2011 9]: http://img8.ph.126.net/eCDAydelc0HsT-h_BnGnHg==/6597736070703152958.jpg
[Oracle _ - _ - _2011 10]: 
[Oracle _ - _ - _2011 11]: http://img0.ph.126.net/qFTzodvLoBGmgpv2f29bGA==/6597423809400828880.jpg
[Oracle _ - _ - _2011 12]: http://img6.ph.126.net/SB2doDyY2Qb7DHNcgBdluw==/6597435904028734596.jpg
[Oracle _ - _ - _2011 13]: http://img5.ph.126.net/T1G1A0wQQa0YDqfMmBNpAg==/6597248987053483125.jpg
[Oracle _ - _ - _2011 14]: http://img9.ph.126.net/APCjbgvL90N3lxIXe8LhoQ==/6597137936379082949.jpg
[Oracle _ - _ - _2011 15]: http://img1.ph.126.net/LFcggSVl-UIn6ek1GRNFoQ==/6597318256284217041.jpg
[Oracle _ - _ - _2011 16]: http://img3.ph.126.net/BgJevidBAutpmQBjstx77A==/6597399620146573723.jpg
[Oracle _ - _ - _2011 17]: http://img0.ph.126.net/LQaAxvVlk_Dk7bT1quxahg==/6597830628703270850.jpg
[Oracle _ - _ - _2011 18]: http://img6.ph.126.net/Cf6BA2_3ulQXeAR8Uth0Hw==/6598084615889064366.jpg
[Oracle _ - _ - _2011 19]: http://img8.ph.126.net/qTRIwHLBEBuGuBbqJbiAxA==/6597412814284554878.jpg
[Oracle _ - _ - _2011 20]: http://img8.ph.126.net/Yv2qQwY8wigfUU41jjT9qA==/6598140690982083848.jpg
[Oracle _ - _ - _2011 21]: http://img4.ph.126.net/Od_P9djKcKfr8TPYFtFo4g==/6597344644565197484.jpg
[Oracle _ - _ - _2011 22]: http://img0.ph.126.net/_XOmOLgTPQqmwhpBFJQsIg==/6597722876563741290.jpg
[Oracle _ - _ - _2011 23]: http://img2.ph.126.net/QwKfu7qvkUlwW-4Y0m0wzw==/6597952674493957472.jpg
[Oracle _ - _ - _2011 24]: http://img6.ph.126.net/E2RB3aV989-uqmqsdzfSyw==/6597499675703152146.jpg
[Oracle _ - _ - _2011 25]: http://img7.ph.126.net/1LCOIpMZW2cqvKi1zRFS0Q==/6598130795377431827.jpg
[Oracle _ - _ - _2011 26]: http://img2.ph.126.net/QC3rjzAywWrZ0kOMjmodjw==/6597294067028406462.jpg
[Oracle _ - _ - _2011 27]: http://img0.ph.126.net/vhR0J7JkSCS7zhdw2bVKmQ==/6597943878400933580.jpg
[Oracle _ - _ - _2011 28]: http://img8.ph.126.net/kzcLba4kGnsvSgs3qshqhQ==/6597173120751173268.jpg