Oracle存储过程和触发器

最新推荐文章于 2024-06-26 15:41:07 发布

weixin_33913332

最新推荐文章于 2024-06-26 15:41:07 发布

阅读量262

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/242764/blog/377265

版权

2019独角兽企业重金招聘Python工程师标准>>>

存储过程

在Oracle中，可以在数据库中定义子程序，这种程序块称为存储过程(procedure)。它存放在数据字典中，可以在不同用户和应用程序之间共享，并可实现程序的优化和重用。使用存储过程的优点是：
(1) 过程在服务器端运行，执行速度快。
(2) 过程执行一次后代码就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译代码执行，提高了系统性能。
(3) 确保数据库的安全。可以不授权用户直接访问应用程序中的一些表，而是授权用户执行访问这些表的过程。非表的授权用户除非通过过程，否则就不能访问这些表。
(4) 自动完成需要预先执行的任务。过程可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。
1. SQL命令创建存储过程

 CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name  /*定义过程名*/

 [ (parameter parameter_mode date_type , …n)]       /*定义参数类型及属性*/

 IS | AS

 BEGIN

    sql_statement                              /*PL/SQL过程体，要执行的操作*/

 END procedure_name

其中：
procedure_name：是过程名，必须符合标识符规则。关键字REPLACE表示在创建过程时，如果已存在同名的过程，则重新创建。如果使用CREATE关键字，则需将原有的过程删除后才能创建。
schema.：是指定过程属于的用户方案。
parameter：是过程的参数。参数名必须符合标识符规则，创建过程时，可以声明一个或多个参数，执行过程时应提供相对应的参数。Parameter_mode是参数的类型，过程参数和函数参数一样，也有3种类型，分别为IN、OUT和IN OUT。
① IN：表示参数是输入给过程的；
② OUT：表示参数在过程中将被赋值，可以传给过程体的外部；
③ IN OUT：表示该类型的参数既可以向过程体传值，也可以在过程体中赋值。
sql_statement：代表过程体包含的PL/SQL语句。
2. 调用存储过程
直接输入存储过程的名字就可以执行一个已定义的存储过程。

EXEC[UTE] procedure_name[(parameter,…n)]

其中，procedure_name为要调用的存储过程的名字，parameter为参数值。
【例1】从XSCJ数据库的XS表中查询某人的总学分，根据总学分写评语。

 CREATE OR REPLACE PROCEDURE update_info

 (  xm in char  )

 AS

  Xf number;

 BEGIN

                SELECT ZXF

                           INTO XF

                      FROM XS

                      WHERE XM=xm;

                IF XF>60 THEN

                     UPDATE XS SET BZ=’三好学生’ 

   WHERE XM=xm;

       END IF;

      IF XF<35 THEN

          UPDATE XS SET BZ=’学分未修满’ 

   WHERE XM=xm;

               END IF;

 END update_info; 

      update_info存储过程执行：

      EXEC update_info(‘李明’);

【例2】统计表XS中男女同学的人数，存储过程使用了一个输入参数和一个输出参数。

CREATE OR REPLACE PROCEDURE count_num

 (     sex IN char,

       num OUT number  )

 AS

 BEGIN

  IF sex=’男’ THEN

   SELECT COUNT(XB) INTO num

    FROM XS

    WHERE XB=’男’;

  ELSE

   SELECT COUNT(XB) INTO num

    FROM XS

    WHERE XB=’女’;

  END IF;

 END count_num;

在调用过程count_num时，需要先定义OUT类型参数，调用如下：

DECLARE 

  man_num NUMBER;

 BEGIN

  count_num(‘男’,man_num);

 END;

当某个过程不再需要时，应将其从内存中删除，以释放它占用的内存资源。

 DROP PROCEDURE [schema.] procedure_name;

其中，schema是包含过程的用户；procedure_name是将要删除的存储过程名称。

触发器

触发器(trigger)是一些过程，与表关系密切，用于保护表中的数据，当一个基表被修改(INSERT、UPDATE或DELETE)时，触发器自动执行，例如通过触发器可实现多个表间数据的一致性和完整性。触发器和应用程序无关。例如，对于XSCJ数据库有XS表、XS_KC表和KC表，当插入某一学号的学生某一课程成绩时，该学号应是XS表中已存在的，课程号应是KC表中已存在的，此时，可通过定义INSERT触发器实现上述功能。
触发器的类型有三种：
(1) DML触发器。Oracle可以在DML(数据操纵语句)语句进行触发，可以在DML操作前或操作后进行触发，并且可以在每个行或该语句操作上进行触发。
(2) 替代触发器。由于在Oracle中不能直接对有两个以上的表建立的视图进行操作，所以给出了替代触发器。它是Oracle专门为进行视图操作的一种处理方法。
(3) 系统触发器。在Oracle8i时，提供了第三种类型的触发器叫系统触发器。它可以在Oracle数据库系统的时间中进行触发，如Oracle数据库的关闭或打开等

一般情况下，对表数据的操作有插入、修改、删除，因而维护数据的触发器也可分为INSERT、UPDATE和DELETE。每张基表最多可建立12个触发器，它们是：
(1) BEFORE INSERT；
(2) BEFORE INSERT FOR EACH ROW；
(3) AFTER INSERT；
(4) AFTER INSERT FOR EACH ROW；
(5) BEFORE UPDATE；
(6) BEFORE UPDATE FOR EACH ROW；
(7) AFTER UPDATE；
(8) AFTER UPDATE FOR EACH ROW；
(9) BEFORE DELETE；
(10) BEFORE DELETE FOR EACH ROW；
(11) AFTER DELETE；
(12) AFTER DELETE FOR EACH ROW。
1. 语法格式

 CREATE OR REPLACE TRIGGER [schema.] trigger_name         /*指定触发器名称*/

 { BEFORE∣AFTER∣INSTEAD OF }

  { DELETE [OR INSERTE] [OR UPDATE [ OF column,…n ]]         /*定义触发器种类*/

 ON [schema.] table_name∣view_name                                         /*指定操作对象*/

 [ FOR EACH ROW [ WHEN(condition) ] ]

 sql_statement[…n]

2. 创建触发器的限制
(1) 代码大小。触发器代码大小必须小于32K。
(2) 触发器中有效语句可以包括DML语句，但不能包括DDL语句。ROLLBACK、COMMIT、SAVEPOINT也不能使用。但是，对于系统触发器(system trigger)可以使用CREATE、ALTER、DROP TABLE和ALTER…COMPILE语句。
(3) LONG、LONG RAW和LOB的限制：
① 不能插入数据到LONG或LONG RAW；
② 来自LONG或LONG RAW的数据可以转换成字符型(如char、varchar2)，但是不能超过32K；
③ 使用LONG或LONG RAW不能声明变量；
④ 在LONG或LONG RAW列中不能使用:NEW和:PARENT；
⑤ 在LOB中的:NEW变量不能修改。
(4) 引用包变量的限制。如果UPDATE或DELETE语句检测到当前的UPDATE冲突，则Oracle执行ROLLBACK到SAVEPOINT上并重新启动更新，这样可能需要多次才能成功。
3. 触发器触发次序
Oracle对事件的触发有16种，它们按照一定次序执行：
(1) 执行BEFORE语句的触发器；
(2) 对于受语句影响的每一行：执行BEFORE语句行级触发器à执行DML语句à执行AFTER行级触发器。
(3) 执行AFTER语句级触发器。
4. 创建DML触发器
触发器与过程名和包的名字不一样，它有单独的名字空间，因而触发器名可以和表名或过程名同名，但在同一个schema(方案)中的触发器名不能相同。DML触发器也叫表级触发器，因为对某个表进行DML操作时会触发该触发器运行而得名。
【例1】假设XSCJ数据库中增加一新表XS_HIS，表结构和表XS相同，用来存放从XS表中删除的记录。创建一个触发器，当XS表被删除一行，把删除的记录写到日志表XS_HIS中。

CREATE OR REPLACE TRIGGER del_xs

  BEFORE DELETE ON XS FOR EACH ROW

 BEGIN

  INSERT INTO XS_HIS (XH,XM,ZYM,XB,CSSJ,ZXF,BZ)

            VALUES(:OLD.XH,:OLD.XM, :OLD.ZYM, :OLD.XB, :OLD.CSSJ,

                                  :OLD.ZXF, :OLD.BZ);

 END del_xs;

OLD修饰访问操作完成前列的值，NEW修饰访问操作完成后列的值。

【例2】利用触发器在数据库XSCJ的XS表执行插入、更新和删除3种操作后给出相应提示。

CREATE TRIGGER cue_xs

  AFTER INSERT OR UPDATE OR DELETE ON XS FOR EACH ROW

 DECLARE

  Infor char(10);

 BEGIN

  IF INSERTING THEN

   Infor:=’插入’;

  ELSIF UPDATING THEN

   Infor:=’更新’;

  ELSE

   Infor:=’删除’;

  END IF;

  INSERT INTO SQL_INFO VALUES(infor);

 END cue_xs;

5. 创建系统触发器
Oracle8i开始提供的系统触发器可以在DDL或数据库系统上被触发。DDL指的是数据定义语句，如CREATE、ALTER和DROP等。而数据库系统事件包括数据库服务器的启动或关闭，用户登录与退出等。

 CREATE OR REPLACE TRIGGER [scache.] trigger_name

 { BEFORE︱AFTER }

 { ddl_event_list︱databse_event_list }

 ON { DATABASE︱[schema.] SCHEMA }

 [when_clause]

 tigger_body

其中：
ddl_event_list：表示一个或多个DDL事件，事件间用OR分开。
database_event_list：表示一个或多个数据库事件，事件间用OR分开。
DATABASE：表示是数据库级触发器，而scache表示是用户级触发器。Schema表示用户方案。
Trigger_body：触发器的PL/SQL语句。
6. 利用SQL命令删除触发器