知识点 | Oracle存储过程和触发器之学习

存储过程：存储过程就是编译好了的一组sql语句。

1. 存储过程因为SQL语句已经预编绎过，因此执行速度较快。
2. 可保证数据的安全性和完整性。通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。
3. 可以降低网络的通信量。存储过程主要是在服务器上运行，减少对客户机的压力。
4. 存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因
5. 存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏本身的数据处理逻辑。

这里再简单介绍下触发器，触发器是一种特殊的存储过程；

触发器： 当满足触发器条件，则系统自动执行触发器的触发体。
触发时间：有before,after.触发事件：有insert,update,delete三种。

触发类型：有行触发、语句触发，语句级的触发器可以在某些语句执行前或执行后被触发。而行级触发器则是在定义了触发条件的表中行数据发生改变时会被触发一次。

举些栗子：
1、 在一个表中定义的语句级触发器，当这个表被删除时，程序就会自动执行触发器里面定义的操作过程。这个删除表操作就是触发器执行的前提条件。
2、 在一个表中定义了行级的触发器，当表中一行数据发生变化时，如删除、新增了一行记录，触发器会被自动执行；
触发器的语法：

create [or replace] tigger 触发器名 触发时间 触发事件
on 表名
[for each row]
begin
 pl/sql语句
end

参数解说：

1. 触发器名：触发器对象的名称。触发器是数据库自动执行，故名称无实质用途，仅做个人标注。
2. 触发时间：指明触发器何时执行，该值可取：
   before：表示在数据库动作之前触发器执行;
   after：表示在数据库动作之后触发器执行。
3. 触发事件：哪些数据库动作会触发此触发器：
   insert：数据库插入会触发此触发器;
   update：数据库修改会触发此触发器;
   delete：数据库删除会触发此触发器。
4. 表 名：数据库触发器所在的表。
5. for each row：对表的每一行触发器执行一次。如没这一选，则对整个表执行一次。

触发器能干些什么？
1、 允许/限制对表的修改
2、 自动生成派生列，比如自增字段
3、 强制数据一致性
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 启用复杂的业务逻辑

举例：
1)、允许/限制对表的修改
下面的触发器在更新表tb_emp之前触发，目的是不允许在周末修改表：

create or replace trigger auth_secure before insert or update or DELETE
on tb_emp
begin
  IF(to_char(sysdate,'DY')='星期日') THEN
    RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表tb_emp');
  END IF;
END;
/

2)、使用触发器实现序号自增

创建一个测试表：

create table tab_user(
  id number(11) primary key,
  username varchar(50),
  password varchar(50)
);

创建一个序列：
复制代码 代码如下:
create sequence my_seq increment by 1 start with 1 nomaxvalue nocycle cache 20;

创建一个触发器：

CREATE OR REPLACE TRIGGER MY_TGR
 BEFORE INSERT ON TAB_USER
 FOR EACH ROW--对表的每一行触发器执行一次
DECLARE
 NEXT_ID NUMBER;
BEGIN
 SELECT MY_SEQ.NEXTVAL INTO NEXT_ID FROM DUAL;
 :NEW.ID := NEXT_ID; --:NEW表示新插入的那条记录
END;

向表插入数据:
**
insert into tab_user(username,password) values(‘admin’,‘admin’);
insert into tab_user(username,password) values(‘fgz’,‘fgz’);
insert into tab_user(username,password) values(‘test’,‘test’);
COMMIT;**

3）、创建触发器，将操作CREATE、DROP存储在log_info表
–创建表

CREATE TABLE log_info(
manager_user VARCHAR2(15),
manager_date VARCHAR2(15),
manager_type VARCHAR2(15),
obj_name VARCHAR2(15),
obj_type VARCHAR2(15)
);

–创建触发器
set serveroutput on;
CREATE OR REPLACE TRIGGER TRIG_LOG_INFO
AFTER CREATE OR DROP ON SCHEMA
BEGIN
INSERT INTO LOG_INFO
VALUES
(USER,
SYSDATE,
SYS.DICTIONARY_OBJ_NAME,
SYS.DICTIONARY_OBJ_OWNER,
SYS.DICTIONARY_OBJ_TYPE);
END;
/

–测试语句
CREATE TABLE a(id NUMBER);
CREATE TYPE aa AS OBJECT(id NUMBER);
DROP TABLE a;
DROP TYPE aa;

–查看效果
SELECT * FROM log_info;

–相关数据字典-----------------------------------------------------
SELECT * FROM USER_TRIGGERS;

–必须以DBA身份登陆才能使用此数据字典
SELECT * FROM ALL_TRIGGERS;SELECT * FROM DBA_TRIGGERS;

–启用和禁用
ALTER TRIGGER trigger_name DISABLE;
ALTER TRIGGER trigger_name ENABLE;

再来两个实际生产案例：
题目：
–触发器：
–添加员工信息,流水号作为自动编号(通过序列生成),
–并且判断如果工资小于0,则改为0;如果大于10000,则改为10000。

CREATE TABLE emp2(
e_id NUMBER,
e_no NUMBER,
e_name VARCHAR2(20),
e_sal NUMBER
)

SELECT * FROM emp2;

CREATE SEQUENCE seq_trg_id;

INSERT INTO emp2(e_id,e_no,e_name,e_sal) VALUES(seq_trg_id.nextval,7788,'栩栩',
 1000000)
INSERT INTO emp2(e_id,e_no,e_name,e_sal) VALUES(seq_trg_id.nextval,7788,'有名',-10)

CREATE OR REPLACE TRIGGER trg_add_emp_info
  BEFORE INSERT
  ON emp2
  FOR EACH ROW
  DECLARE
    -- local variables here
  BEGIN
    SELECT seq_trg_id.NEXTVAL INTO :NEW.e_id FROM dual;
    IF  :NEW.e_sal < 0 THEN
       :NEW.e_sal := 0;
    ELSIF  :NEW.e_sal > 10000 THEN
       :NEW.e_sal := 10000;
    END IF;
  END;

案例二：
为emp建立触发器,将删除的记录放到emp3表中(autoid,deptno,empno,ename,del_rq-删除日期)

CREATE OR REPLACE TRIGGER trg_del_emp_info
  BEFORE DELETE
  ON emp
  FOR EACH ROW
  DECLARE
    -- local variables here
  BEGIN
    INSERT INTO emp3(autoid,deptno,empno,ename,del_rq)
          VALUES(seq_trg_del_autoid.NEXTVAL,:OLD.deptno,:OLD.empno,:OLD.ename,sysdate);
  END;

文章结束。**

以下为个人公众号，欢迎扫码关注：

【参考】
https://www.cnblogs.com/sharpest/p/7764662.html
【参考】
https://www.cnblogs.com/quyanhui/p/3386493.html
【参考】
https://blog.youkuaiyun.com/justdo2008/article/details/4137779