oracle存储过程、函数、触发器和包

我会带着你远行 2022-05-24 04:46 271阅读 0赞

**要点：**

*  存储过程的创建
 *  带参数的存储过程的使用
 *  存储过程的管理
 *  函数的创建和使用
 *  触发器的类型和作用
 *  程序包的创建和使用

存储过程

1、存储过程的创建

2、调用存储过程

3、带参数的存储过程

4、修改与删除存储过程

5、查询存储过程的定义信息

函数

1、定义

2、存储过程与函数的区别与联系

触发器

1、触发器类型

2、创建触发器

3、DML触发器

4、INSTEAD OF触发器

5、系统事件触发器

6、DDL触发器

7、禁用与启用触发器

8、修改与删除触发器

包

1、创建包

1）、创建包规范

2）、创建包体

2、调用包

3、删除包

**存储过程**

存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集，它大大提高了SQL语句的功能和灵活性。存储过程编译后存储在数据库中，所以执行存储过程比执行存储过程中封装的SQL语句更有效率。

**1、存储过程的创建**

Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。创建存储过程需要使用CREATE PROCEDURE语句，具体语法如下：

--------------------

CREATE \[ OR REPLACE \] PROCEDURE procedure\_name

( parameter \[ IN | OUT | IN | IN OUT \] data\_type )

\[ , ...\]

\{ IS | AS \}

\[ declaration\_section ; \]

BEGIN

procedure\_body ;

END \[ procedure\_name \] ;

说明：

*  OR REPLACE：表示如果存储过程已经存在，则替换已有存储过程。
 *  procedure\_name：存储过程名称
 *  parameter：参数，可以为存储过程设置多个参数，参数定义之间用（,）分隔。
 *  IN | OUT | IN | IN OUT：指定参数的模式。IN表示输入参数，在调用存储过程时需要为输入参数返回值，而且其值不能在存储过程中修改；OUT表示输出参数，存储过程通过输出参数返回值；IN OUT则表示输入输出参数，这种类型的参数既要接受传递值也允许在过程体重修改其值，并可以返回。默认为IN，在使用IN参数时，还可以使用DEFAULT关键字为该参数设置默认值：

parameter \[ IN \] data\_type DEFAULT value ;

*  data\_type：参数的数据类型，不能指定精确数据类型，例如只能使用NUMBER，不能使用NUMBER(2)等。
 *  declaration\_section：声明变量。在储存声明的变量不能使用DECLARE语句，这些变量只要用于过程体中。
 *  procedure\_body：过程体。
 *  END \[ procedure\_name \] ：END关键字后添加过程名，可以提高程序的阅读性，不是必须的。

--------------------

**2、调用存储过程**

调用过程有两种形式，一种是使用CALL语句，另一种是使用EXECUTE语句，如下：

CALL procedure\_name ( \[ parameter \[ , ...\] \] ) ;

EXEC\[UTE\] procedure\_name \[ ( parameter \[ , ...\] ) \] ;

**3、带参数的存储过程**

a）、IN参数的使用

IN 参数是指输入参数，由存储过程中的调用者为其赋值（也可以使用默认值），如果不为参数制定模式，则其模式默认为IN。

--------------------

例：创建带IN参数的存储过程，修改人员年龄：

CREATE PROCEDURE update\_age

( age in NUMBER , user\_name IN VARCHAR2 ) AS

BEGIN

UPDATE ts\_user set user\_age = age

WHERE usname = user\_name ;

END update\_age ;

调用时，需要对两个输入参数赋值，赋值有两种形式：

*  不指定参数名

调用过程只提供参数值，不指定该值赋予那个参数，oracle会自动按照过程中的参数顺序为参数赋值，如果值的个数或者数据类型不匹配，则会返回错误。

EXEC update\_age ( 15，'dong' )

*  指定参数名

调用过程时不仅提供参数值，还指定所赋予的参数。此方式可不按照顺序赋值。指定参数名的赋值形式为：parameter\_name => value。

EXEC update\_age ( age => 15，user\_name => 'dong' )

--------------------

b）、OUT参数的使用

OUT参数是指输出参数，由存储过程中的语句为其赋值，并返回给用户。此模式参数必须在参数后添加OUT关键字。

--------------------

例：创建存储过程select\_user

CREATE PROCEDURE select\_user

(age in NUMBER , user\_name OUT VARCHAR2 ) AS

BEGIN

SELECT user\_name INTO emp\_name

FROM ts\_user

WHERE user\_age = age ;

END select\_user ;

--------------------

调用存储过程时，需要显示该过程中OUT参数的返回值，还需要实现使用VARIABLE语句声明对应的变量接受返回值，并在调用过程时绑定该变量，形式如下：

VARIABLE variable\_name data\_type ;

\[ , ...\]

EXEC\[UTE\] procedure\_name ( :variable\_name \[ , ...\] ) ;

注意：在EXECUTE中绑定变量时，需要在变量名前添加冒号（:）

--------------------

例：调用select\_user

VARIABLE emp1\_name VARCHAR2(10) ;

EXECUTE select\_user (15 ,:emp1\_name ) ;

然后使用PRINT命令查看变量emp1\_name 中的值，如下：

PRINT emp1\_name ;

也可以使用SELECT语句查看变量emp1\_name 中的值，如下：

SELECT :emp1\_name FROM dual ；

--------------------

c）、IN OUT参数的使用

IN OUT参数同时拥有IN与OUT参数的特性，它既接受用户的传值，又允许在过程体中修改其值，并可以将至返回。使用这种模式的参数需要在参数后加IN OUT关键字。IN OUT参数不接受常量值，只能使用变量为其传值。

--------------------

例：创建存储过程exchange\_value，交换两个变量值

CREATE PROCEDURE exchange\_value

( value1 IN OUT NUMBER, value2 IN OUT NUMBER )

temp1 NUMBER ;

temp2 NUMBER ;

BEGIN

temp1 := value1 ;

temp2 := value2 ;

value1 := temp2 ;

value2 := temp1 ;

END exchange\_value ;

调用exchange\_value过程，调用前声明为IN OUT参数赋值的变量，调用后使用SELECT语句输出交换值后的结果，如下：

VARIABLE value1 NUMBER ;

VARIABLE value2 NUMBER ;

EXEC :value1 := 111 ;

EXEC :value2 := 222 ;

EXEC exchange\_value (:value1 , :value2) ;

SELECT :value1 , :value2 FROM dual ;

提示：使用EXEC\[UTE\]命令可以为变量赋值，并且变量名前也需要添加冒号（:）

--------------------

**4、修改与删除存储过程**

修改是在 CREATE PROCEDURE添加 OR REPLACE为 CREATE OR REPLACE PROCEDURE，其本质是删除原有创建新的。

删除存储过程需要使用DROP PROCEDURE语句，形式为： DROP PROCEDURE procedure\_name；

**5、查询存储过程的定义信息**

SELECT \* FROM user\_source WHERE name = 'exchange\_value'

**函数**

**1、定义**

函数与存储过程的结构类似，但是函数必须有一个return子句，用于返回函数值。

创建函数使用CREATE FUNCTION语句，如下：

CREATE \[ OR REPLACE \] FUNCTION function\_name

( paramter \[ IN | OUT | IN OUT \] data\_type )

\[ , ...\]

RETURN data\_type

\{ IS | AS \}

\[ declaration\_section ; \]

BEGIN

function\_body ;

END \[ function\_name \] ;

--------------------

例：创建函数，实现empno获取ename。

CREATE FUNCTION get\_name (emp\_num NUMBER)

RETURN VARCHAR2

emp\_name emp.ename%TYPE ;

BEGIN

SELECT ename INTO emp\_name FROM emp WHERE empno = enm\_num ;

END get\_name;

--------------------

**2、存储过程与函数的区别与联系**

相同点：1）.创建语法结构相似，都可以携带多个传入参数和传出参数。

2）.都是一次编译，多次执行。

不同点：1）.存储过程定义关键字用procedure，函数定义用function。

2）.存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。procedur不叫返回值，叫带出，可以带出多个值;

3）.执行方式略有不同，存储过程的执行方式有两种（1.使用execute2.使用begin和end），函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）。

4）.在sql数据操纵语句中只能调用函数而不能调用存储过程,

**触发器**

触发器是一种特殊的存储过程，触发器在数据库里以独立的对象存储，它与存储过程和函数不同的是，存储过程与函数需要用户显示调用才执行，而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且，触发器不能接收参数。所以运行触发器就叫触发或点火（firing）。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE，如数据库的启动与关闭等。所以触发器常用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。

**1、触发器类型**

主要有DML触发器、替代触发器、系统事件触发器和DDL触发器。

*  DML触发器：ORACLE可以在DML语句进行触发，可以在DML操作前（BEFORE触发器）或操作后（AFTER触发器）进行触发，并且可以对每个行或语句操作上进行触发（行级触发器和语句级触发器）。 
 *  INSTEAD OF触发器：又称为替代触发器，用于执行一个替代操作来代替触发事件的操作， 由于在ORACLE里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。
 *  系统事件触发器：在发生如数据库启动或者关闭等系统事件时触发。
 *  DDL触发器：由DDL语句触发，例如CREATE、ALTER和DROP语句。可分为BEFORE触发器和AFTER触发器。

**2、创建触发器**

使用CREATE TRIGGER语句，语法如下：

--------------------

CREATE \[ OR REPLACE \] TRIGGER trigger\_name

\{ BEFORE | AFTER | INSTEAD OF \}

\{ INSERT | DELETE | UPDATE \[ OF column \[, column … \] \] \} \#tigger\_event

\[ OR \{ INSERT | DELETE | UPDATE \[OF column \[, column … \] \] \} ... \]

ON \[ schema. \] table\_name | \[ schema.\] view\_name | \[ DATAASE \]

\[ REFERENCING  \{ OLD \[ AS \] old | NEW \[ AS \]  new | PARENT as parent \} \]

\[ FOR EACH ROW \]

\[ WHEN trigger\_condition \]

\[ DECLARE declaration\_statements ; \]

BEGIN

trigger\_body ;

END \[ trigger\_name \]

PL/SQL\_BLOCK | CALL procedure\_name;

语法说明：

*  trigger\_name：触发器名称。
 *  BEFORE | AFTER | INSTEAD OF：BEFORE和AFTER表示触发器执行的时间在触发事件的前后，INSTEAD OF表示触发器中的事件代替触发事件执行。
 *  tigger\_event：激活触发器的事件，例如 INSERT、DELETE、UPDATE 。
 *  ON \[ schema. \] table\_name | \[ schema.\] view\_name | \[ DATABASE \]：table\_name为DML触发器所针对的表，如果是替代触发器则需要指定视图名称（view\_name），如果是DDL触发器或者系统事件触发器，则使用ON DATABASE。
 *  REFERENCING：说明相关名称，在行触发器的PL/SQL块和WHEN 子句中可以使用相关名称参照当前的新、旧列值，默认的相关名称分别为OLD和NEW。触发器的PL/SQL块中应用相关名称时，必须在它们之前加冒号(:)，但在WHEN子句中则不能加冒号。
 *  FOR EACH ROW：表示是行级触发器，如果未指定则为语句级触发器。
 *  WHEN trigger\_condition：为触发的运行指定限制条件。
 *  trigger\_body：触发器体，包含触发器的内容。

--------------------

**3、DML触发器**

DML触发器由DML语句触发，对应的tigger\_event为：

\{ INSERT | DELEATE | UPDATE \[ OF column \[ , ... \] \] \}

说明：

*  DML操作主要包括INSERT、DELETE和UPDATE操作，通过根据针对的事件，可分为INSERT触发器、DELETE触发器和UPDATE触发器。
 *  可以将DML操作细化到列，即针对某列进行DML操作时激活触发器。
 *  任何DML都可按照触发时间分为BEFORE和AFTER。
 *  在行级触发器中，为了获得某列在操作前后的数据，提供两种特殊标识符:OLD和:NEW，通过:OLD.column\_name的形式可以获取该列的旧数据，而通过:NEW.colum\_name可以获取该列的新数据，INSERT触发器只能用:NEW，DELETE触发器只能用:OLD，UPDATE触发器可以用:OLD和:NEW。

注意：如果创建时，不指定FOR EACH ROW，则为与语句级触发器，所有受影响的数据只处罚一次，因此无法使用:NEW和:OLD获取某列的新旧数据。

**4、INSTEAD OF触发器**

执行一个替代操作来代替触发事件的操作，而触发事件本身不会被执行。不过oracle中的INSTEAD OF触发器不能针对表，只能针对视图。

**5、系统事件触发器**

系统事件触发器是指由数据库系统事件触发的触发器，支持的系统事件如下：

<table style="table-layout:fixed;white-space:nowrap;width:0px;">     
 <tbody> 
  <tr style="height:40px;"> 
   <td style="color:rgb(57,57,57);border-color:rgb(167,167,167);text-align:center;white-space:pre-wrap;"> 
    <div> 
     <span style="font-size:12px;font-family:'Microsoft YaHei', STXihei;color:rgb(0,0,0);background-color:transparent;">系统事件</span> 
    </div></td> 
   <td style="color:rgb(57,57,57);border-color:rgb(167,167,167);text-align:center;white-space:pre-wrap;"> 
    <div> 
     <span style="font-size:12px;font-family:'Microsoft YaHei', STXihei;color:rgb(0,0,0);background-color:transparent;">说明</span> 
    </div></td> 
  </tr> 
  <tr style="height:29px;"> 
   <td style="color:rgb(57,57,57);border-color:rgb(167,167,167);white-space:pre-wrap;"> 
    <div> 
     <span style="font-size:12px;font-family:'Microsoft YaHei', STXihei;color:rgb(0,0,0);background-color:transparent;">LOGOFF</span> 
    </div></td> 
   <td style="color:rgb(57,57,57);border-color:rgb(167,167,167);white-space:pre-wrap;"> 
    <div> 
     <span style="font-size:12px;font-family:'Microsoft YaHei', STXihei;color:rgb(0,0,0);background-color:transparent;">用户从数据库注销</span> 
    </div></td> 
  </tr> 
  <tr style="height:32px;"> 
   <td style="color:rgb(57,57,57);border-color:rgb(167,167,167);white-space:pre-wrap;"> 
    <div> 
     <span style="font-size:12px;font-family:'Microsoft YaHei', STXihei;color:rgb(0,0,0);background-color:transparent;">LOGON</span> 
    </div></td> 
   <td style="color:rgb(57,57,57);border-color:rgb(167,167,167);white-space:pre-wrap;"> 
    <div> 
     <span style="font-size:12px;font-family:'Microsoft YaHei', STXihei;color:rgb(0,0,0);background-color:transparent;">用户登录数据库</span> 
    </div></td> 
  </tr> 
  <tr style="height:30px;"> 
   <td style="color:rgb(57,57,57);border-color:rgb(167,167,167);white-space:pre-wrap;"> 
    <div> 
     <span style="font-size:12px;font-family:'Microsoft YaHei', STXihei;color:rgb(0,0,0);background-color:transparent;">SERVERERROR</span> 
    </div></td> 
   <td style="color:rgb(57,57,57);border-color:rgb(167,167,167);white-space:pre-wrap;"> 
    <div> 
     <span style="font-size:12px;font-family:'Microsoft YaHei', STXihei;color:rgb(0,0,0);background-color:transparent;">服务器发生错误</span> 
    </div></td> 
  </tr> 
  <tr style="height:31px;"> 
   <td style="color:rgb(57,57,57);border-color:rgb(167,167,167);white-space:pre-wrap;"> 
    <div> 
     <span style="font-size:12px;font-family:'Microsoft YaHei', STXihei;color:rgb(0,0,0);background-color:transparent;">SHUTDOWN</span> 
    </div></td> 
   <td style="color:rgb(57,57,57);border-color:rgb(167,167,167);white-space:pre-wrap;"> 
    <div> 
     <span style="font-size:12px;font-family:'Microsoft YaHei', STXihei;color:rgb(0,0,0);background-color:transparent;">关闭数据库实例</span> 
    </div></td> 
  </tr> 
  <tr style="height:30px;"> 
   <td style="color:rgb(57,57,57);border-color:rgb(167,167,167);white-space:pre-wrap;"> 
    <div> 
     <span style="font-size:12px;font-family:'Microsoft YaHei', STXihei;color:rgb(0,0,0);background-color:transparent;">STARTUP</span> 
    </div></td> 
   <td style="color:rgb(57,57,57);border-color:rgb(167,167,167);white-space:pre-wrap;"> 
    <div> 
     <span style="font-size:12px;font-family:'Microsoft YaHei', STXihei;color:rgb(0,0,0);background-color:transparent;">打开数据库实例</span> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

注：对于LOGOFF和SHUTDOWN事件只能创建BEFORE触发器，对于LOGON、SERVERERROR和STARTUP事件只能创建AFTER触发器。

创建系统触发器需要使用ON DATABASE子句，表示创建的触发器是数据库级触发器。创建系统事件触发器需要用户具有DBA权限。

**6、DDL触发器**

DDL触发器由DDL语句触发，可分为：BEFORE触发器和AFTER触发器，针对事件包含CREATE、ALTER、DROP、ANALYZE、GRANT、COMMENT、REVOKE、RENAME、TRUNCATE、AUDIT、NOTAUDIT、ASSOCIATE STATISTICS和DISASSOCIATE STATISTICS。

创建DDL触发器需要用户具有DBA权限。

**7、禁用与启用触发器**

创建时使用ENABLE与DISABLE关键字制定触发器初始装填为启用或禁用，默认为ENABLE。需要时也可使用ALTER TRIGGER语句修改触发器的状态，如下：

ALTER TRIGGER trigger\_name ENABLE | DISABLE ;

如果修改某个表上所有触发器的状态，可用如下形式：

ALTER TABLE table\_name ENABLE | DISABLE ALL TRIGGERS ;

**8、修改与删除触发器**

修改删除触发器只需要在 CREATE TRIGGER语句中添加OR REPLACE关键字。

删除触发器需要使用DROP TRIGGER语句，具体如下：

DROP TRIGGER trigger\_name;

**包**

**1、创建包**

程序包是对相关过程、函数、变量、游标和异常等对象的封装，程序包由规范和主体两部分组成，程序包规范：声明类型、变量、常量、异常、游标和子程序等元素条目（不包含这些元素的实际代码）；程序包主体：用于实现在程序包规范中定义的游标、子程序（包含了元素的实际代码）。包规范中的条目为共有项目，可供所有的数据库用户访问；而包体中创建的规范中没有提到的项目，属于私有项目，只能在包体中使用。

**1）、创建包规范**

创建包规范使用CREATE PACKAGE语句，如下：

CREATE \[ OR REPLACE \] PACKAGE package\_name

\{ IS | AS \}

package\_specification ;

END package\_name;

说明：

*  package\_name：创建的包名
 *  package\_specification：用于列出用户可以使用的公共存储过程、函数、类型和对象。

**2）、创建包体**

创建包体需要使用CREATE PACKAGE BODY语句，并且在创建的时候需要指定已创建的包，如下：

CREATE \[ OR REPLACE \] PACKAGE BOODY package\_name

\{ IS | AS \}