触发器变异表问题，oracle--ORA:04091：触发器/函数不能读它

coderLee0607

已于 2024-02-22 11:32:54 修改

阅读量1.5k

点赞数

分类专栏：数据库文章标签： oracle vr 数据库

于 2018-10-16 17:54:00 首次发布

本文链接：https://blog.csdn.net/ptsx0607/article/details/83094986

版权

数据库专栏收录该内容

23 篇文章 0 订阅

订阅专栏

问题分析

在Oracle中执行DML语句的时候是需要显示进行提交操作的。当我们进行插入的时候，会触发触发器执行对触发器作用表和扩展表的种种操作，但是这个时候触发器和插入语句是在同一个事务管理中的，因此在插入语句没有被提交的情况下，我们无法对触发器作用表进行其他额外的操作。如果执行其他额外的操作则会抛出如上异常信息。

解决方案

1,我们知道，出错的原因是因为触发器和DML语句在同一事务管理中，所以方案一便是将触发器和DML语句分成两个单独的事务处理。这里可以使用Pragma autonomous_transaction; 告诉Oracle触发器是自定义事务处理。

SQL语句如下：

create trigger iu_table
  after insert or update on tr_table
  for each row
  declare  --这里是关键的地方，在变量申明的地方，指定自定义事务处理。
  pragma autonomous_transaction; 
  begin
   insert into ts_table select * from tr_table t where t.tab_id = :new.tab_id;
  --这里需要显示提交事务
   commit;
  end iu_table;

2,在Oracle Trigger中有:new,:old两个特殊变量，当触发器为行级触发器的时候，触发器就会提供new和old两个保存临时行数据的特殊变量，我们可以从俩个特殊的变量中取出数据执行扩张表的DML操作。

SQL语句如下：

 create trigger iu_table
  after insert on tr_table
  for each row
  begin
   insert into ts_table(tab_id,tab_name) values(:new.tab_id,:new.tab_name);
   --这里需要注意，要知道不同的触发类型其特殊变量:new和:old保存的值的区别。
   --commit; 注意使用方案二，这里不能显示的进行提交操作操作，trigger中在没有声明自定义事务管理的时候，不能执行显示提交。
  end iu_table;

附工大科雅中间库过程:

/*
 * CREATED BY LS 2018.07.30
 * 河北廊坊广达-工大科雅中间库
*/
--一、建表
--清除已存在内容
DROP TABLE USER_INFO;
DROP TABLE USER_SF_INFO;
DROP TABLE RB_INFO;
DROP FUNCTION GET_ORGNAME_F;
DROP FUNCTION GET_ITEMVALUE_F;
DROP TRIGGER TR_USER_INFO_IU;
DROP TRIGGER TR_USER_INFO_DELETE;
DROP TRIGGER TR_AREA_INFO;
DROP TRIGGER TR_USER_SF_INFO;
DROP SEQUENCE EBZ_METER_READ_S;
DROP PROCEDURE RB_INFO_PRO;
DROP JOB xxx;



--用户信息表
CREATE TABLE USER_INFO
(
  YHBH      NUMBER, 
  YHBM      VARCHAR2(20),
  FGS       VARCHAR2(20),
  RLZ       VARCHAR2(20),
  XQ        VARCHAR2(50),
  LH        VARCHAR2(20),
  DYH       VARCHAR2(20),
  CS        VARCHAR2(20),
  SH        VARCHAR2(20),
  GNZT      VARCHAR2(20),
  YHMC      VARCHAR2(20),
  LXDH      VARCHAR2(20),
  CNMJ      NUMBER,
  SYNCSTATE NUMBER 
);
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN USER_INFO.YHBH
  IS '用户编号（收费系统中的内部编号，唯一）';
COMMENT ON COLUMN USER_INFO.YHBM
  IS '用户编码（热力公司给用户做出的编码）';
COMMENT ON COLUMN USER_INFO.FGS
  IS '分公司';
COMMENT ON COLUMN USER_INFO.RLZ
  IS '热力站';
COMMENT ON COLUMN USER_INFO.XQ
  IS '小区';
COMMENT ON COLUMN USER_INFO.LH
  IS '楼号';
COMMENT ON COLUMN USER_INFO.DYH
  IS '单元号';
COMMENT ON COLUMN USER_INFO.CS
  IS '层数';
COMMENT ON COLUMN USER_INFO.SH
  IS '室号';
COMMENT ON COLUMN USER_INFO.GNZT
  IS '供暖状态';
COMMENT ON COLUMN USER_INFO.YHMC
  IS '用户名称';
COMMENT ON COLUMN USER_INFO.LXDH
  IS '联系电话';
COMMENT ON COLUMN USER_INFO.CNMJ
  IS '采暖面积';
COMMENT ON COLUMN USER_INFO.SYNCSTATE
  IS '热计量系统进行数据同步：状态0，未同步；1，已同步';  
  
--缴费信息表
CREATE TABLE USER_SF_INFO
(
  YHBH      NUMBER,
  YSJE      NUMBER,
  JFJE      NUMBER, 
  FMZT      VARCHAR2(10), 
  JFRQ      DATE,
  SYNCSTATE NUMBER
);
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN USER_SF_INFO.YHBH
  IS '用户编号（收费系统中的内部编号，唯一）';
COMMENT ON COLUMN USER_SF_INFO.YSJE
  IS '应收金额';
COMMENT ON COLUMN USER_SF_INFO.JFJE
  IS '缴费金额';
COMMENT ON COLUMN USER_SF_INFO.FMZT
  IS '阀门状态 0，关阀，1开阀';
COMMENT ON COLUMN USER_SF_INFO.JFRQ
  IS '缴费日期';
COMMENT ON COLUMN USER_SF_INFO.SYNCSTATE
  IS '热计量系统进行数据同步: 状态0，未同步；1，已同步';
  

 -- 表计量数据表
CREATE TABLE RB_INFO
(
  YHBH      NUMBER,
  CNQ       VARCHAR2(20),
  RBBH      NUMBER,
  QCBL      NUMBER,
  JZBL      NUMBER,
  BJYL      NUMBER,
  SJYL      NUMBER,
  ROOMTEMP  NUMBER,
  SETTEMP   NUMBER,
  INTEMP    NUMBER,
  OUTTEMP   NUMBER,
  BOLTSTATUS NUMBER,
  SETBOLTSTATUS NUMBER,
  CBSJ      DATE,
  SYNCSTATE NUMBER
);
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN RB_INFO.YHBH
  IS '用户编号';
COMMENT ON COLUMN RB_INFO.CNQ
  IS '采暖期';
COMMENT ON COLUMN RB_INFO.RBBH
  IS '热表编号';
COMMENT ON COLUMN RB_INFO.QCBL
  IS '期初表量（单位KWH）';
COMMENT ON COLUMN RB_INFO.JZBL
  IS '截止表量（单位KWH）';
COMMENT ON COLUMN RB_INFO.BJYL
  IS '表计用量（单位KWH）';
COMMENT ON COLUMN RB_INFO.SJYL
  IS '实际用量（单位KWH）';
COMMENT ON COLUMN RB_INFO.ROOMTEMP
  IS '室温';
COMMENT ON COLUMN RB_INFO.SETTEMP
  IS '设定室温';
COMMENT ON COLUMN RB_INFO.INTEMP
  IS '供水温度';
COMMENT ON COLUMN RB_INFO.OUTTEMP
  IS '回水温度';
COMMENT ON COLUMN RB_INFO.BOLTSTATUS
  IS '阀门状态';
COMMENT ON COLUMN RB_INFO.SETBOLTSTATUS
  IS '供暖状态';
COMMENT ON COLUMN RB_INFO.CBSJ
  IS '抄表时间';
COMMENT ON COLUMN RB_INFO.SYNCSTATE
  IS '热计量系统进行数据同步: 状态0，未同步；1，已同步';

--二、触发器
--根据CODE获取机构信息名称函数
CREATE OR REPLACE FUNCTION GET_ORGNAME_F(
V_FGSCODE IN VARCHAR2,
V_RLZCODE IN VARCHAR2,
V_XQCODE IN VARCHAR2,
V_LHCODE IN VARCHAR2,
V_TYPE   IN VARCHAR2
) RETURN VARCHAR2 IS
  RESULT VARCHAR2(200);
BEGIN
   --获取分公司名称
   IF V_TYPE='1' THEN
     SELECT FGS.NAME INTO RESULT FROM JC_CUSTOMER_ORG FGS
     WHERE FGS.TYPE='1'
     AND FGS.IS_DELETED=0
     AND FGS.CODE=V_FGSCODE;
   --获取热力站名称
   ELSIF V_TYPE='2' THEN
     SELECT RLZ.NAME INTO RESULT FROM JC_CUSTOMER_ORG FGS
     LEFT JOIN JC_CUSTOMER_ORG RLZ
     ON FGS.ID=RLZ.PARENT_ID
     AND RLZ.TYPE='2'
     AND RLZ.IS_DELETED=0
     AND RLZ.CODE=V_RLZCODE
     WHERE FGS.IS_DELETED=0
     AND FGS.CODE=V_FGSCODE;
   --获取小区名称
   ELSIF V_TYPE='3' THEN
     SELECT XQ.NAME INTO RESULT FROM JC_CUSTOMER_ORG FGS
     LEFT JOIN JC_CUSTOMER_ORG RLZ
     ON FGS.ID=RLZ.PARENT_ID
     AND RLZ.IS_DELETED=0
     AND RLZ.CODE=V_RLZCODE
     LEFT JOIN JC_CUSTOMER_ORG XQ
     ON XQ.PARENT_ID=RLZ.ID
     AND XQ.TYPE='3'
     AND XQ.IS_DELETED=0
     AND XQ.CODE=V_XQCODE
     WHERE FGS.IS_DELETED=0
     AND FGS.CODE=V_FGSCODE;

   --获取大楼名称
   ELSIF V_TYPE='4' THEN
     SELECT LH.NAME INTO RESULT FROM JC_CUSTOMER_ORG FGS
     LEFT JOIN JC_CUSTOMER_ORG RLZ
     ON FGS.ID=RLZ.PARENT_ID
     AND RLZ.IS_DELETED=0
     AND RLZ.CODE=V_RLZCODE
     LEFT JOIN JC_CUSTOMER_ORG XQ
     ON XQ.PARENT_ID=RLZ.ID
     AND XQ.CODE=V_XQCODE
     AND XQ.IS_DELETED=0
     LEFT JOIN JC_CUSTOMER_ORG LH
     ON LH.PARENT_ID=XQ.ID
     AND LH.TYPE='4'
     AND LH.IS_DELETED=0
     AND LH.CODE=V_LHCODE
     WHERE FGS.IS_DELETED=0
     AND FGS.CODE=V_FGSCODE;
   END IF;
  RETURN(RESULT);
END GET_ORGNAME_F;

--获取自定义项值函数
CREATE OR REPLACE FUNCTION GET_ITEMVALUE_F(
V_CUSTOMERID IN VARCHAR2,
V_TYPE   IN VARCHAR2
) RETURN VARCHAR2 IS
  RESULT VARCHAR2(200);
BEGIN
  --获取用户供暖状态，暂 排除部分停供及拆网
  IF V_TYPE='GNZT' THEN
    SELECT DECODE(JC.HEATING_STATUS,'是','停供','正常') INTO RESULT FROM JC_CUSTOMER JC
    WHERE JC.ID=V_CUSTOMERID
    AND JC.IS_DELETED=0
	AND JC.LOGOUT_FLAG=0;
  --获取用户收费面积
  ELSIF V_TYPE='SFMJ' THEN
    SELECT TO_CHAR(SUM(MJ.CHARGE_AREA_SIZE)) INTO RESULT FROM JC_AREA MJ
    WHERE MJ.JCCUSTOMERID=V_CUSTOMERID
    AND MJ.IS_DELETED=0;
  END IF;
  RETURN(RESULT);
END GET_ITEMVALUE_F;
--非自治事务
--1 用户信息触发器
--01 用户表JC_CUSTOMER触发
--001 插入、更新触发
CREATE OR REPLACE TRIGGER TR_USER_INFO_IU 
AFTER INSERT OR UPDATE ON JC_CUSTOMER
FOR EACH ROW 
DECLARE 
 RSSUM NUMBER;
BEGIN
 SELECT COUNT(*) INTO RSSUM FROM USER_INFO WHERE YHBH=:OLD.ID;
  IF RSSUM=0 THEN
    INSERT INTO USER_INFO
    (YHBH,
     YHBM,
     FGS,
     RLZ,
     XQ,
     LH,
     DYH,
     CS,
     SH,
     GNZT,
     YHMC,
     LXDH,
     CNMJ,
     SYNCSTATE)
  VALUES
    (:NEW.ID,
     :NEW.CODE,
     GET_ORGNAME_F(:NEW.COMPANY_CODE, '', '', '', '1'),
     GET_ORGNAME_F(:NEW.COMPANY_CODE, :NEW.STATION_CODE, '', '', '2'),
     GET_ORGNAME_F(:NEW.COMPANY_CODE, :NEW.STATION_CODE, :NEW.COMMUNITY_CODE, '', '3'),
     GET_ORGNAME_F(:NEW.COMPANY_CODE, :NEW.STATION_CODE, :NEW.COMMUNITY_CODE, :NEW.BUILDING_CODE, '4'),
     :NEW.UNIT_NO,
     :NEW.FLOOR_NO,
     :NEW.ROOM_NO,
     '正常',  	--新增用户供暖状态：正常
     :NEW.NAME,
     :NEW.CELLPHONE,
     0, 		--新增用户采暖面积：0
     0);
  ELSIF RSSUM =1 THEN
   UPDATE USER_INFO
	 SET YHBH = :NEW.ID,
		 YHMC = :NEW.NAME,
		 LXDH = :NEW.CELLPHONE
   WHERE YHBH = :OLD.ID;
  END IF;
END TR_USER_INFO_IU;

--002 删除触发，暂用自治事务
CREATE OR REPLACE TRIGGER TR_USER_INFO_DELETE
AFTER DELETE ON JC_CUSTOMER
FOR EACH ROW 
DECLARE
--使用自治事务，一个事务里的子事务
PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN
  DELETE FROM USER_INFO WHERE YHBH = :OLD.ID;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END TR_USER_INFO_DELETE;

--02 面积表JC_AREA触发
--增加package变量，在Oracle Trigger中有:new,:old两个特殊变量，当触发器为行级触发器的时候，
--触发器就会提供new和old两个保存临时行数据的特殊变量，我们可以从俩个特殊的变量中取出数据执行扩张表的DML操作。
--如果既想更新变异表，同时又需要查询变异表，那么如何处理呢?
--将行级触发器与语句级触发器结合起来，在行级触发器中获取要修改的记录的信息，存放到一个软件包的全局变量中，
--然后在语句级后触发器中利用软件包中全局变量信息对变异表的查询，并根据查询的结果进行业务处理
--面积程序包
CREATE OR REPLACE PACKAGE JCAREA_PKG 
AS
  V_NEWCUSTOMERID NUMBER(19); 
  V_OLDCUSTOMERID NUMBER(19); 
END;
--001面积表，行级触发
CREATE OR REPLACE TRIGGER TR_AREA_ROW
AFTER INSERT OR UPDATE ON JC_AREA
FOR EACH ROW
BEGIN
  JCAREA_PKG.V_NEWCUSTOMERID := :NEW.JCCUSTOMERID;
END TR_AREA_ROW;

--002面积表，表级触发 
CREATE OR REPLACE TRIGGER TR_AREA_INFO
AFTER INSERT OR UPDATE OR DELETE ON JC_AREA
BEGIN
  UPDATE USER_INFO
    SET CNMJ = GET_ITEMVALUE_F(JCAREA_PKG.V_NEWCUSTOMERID,'SFMJ'),
	    GNZT = GET_ITEMVALUE_F(JCAREA_PKG.V_NEWCUSTOMERID,'GNZT')
  WHERE YHBH = JCAREA_PKG.V_NEWCUSTOMERID;
END TR_AREA_INFO;


--2 收费信息触发器
--费用信息程序包
CREATE OR REPLACE PACKAGE RFGL_TRANSACTION_PKG 
AS
  V_NEWCUSTOMERID NUMBER(19); 
  V_ID NUMBER(19);
  V_INCOME  NUMBER;
  V_TRANSACTION_TIME TIMESTAMP(6);
END;
--001交易表，行级触发
CREATE OR REPLACE TRIGGER TR_TRANSACTION_ROW
AFTER INSERT OR UPDATE ON RFGL_TRANSACTION
FOR EACH ROW
BEGIN
  RFGL_TRANSACTION_PKG.V_NEWCUSTOMERID := :NEW.CUSTOMER_ID;
  RFGL_TRANSACTION_PKG.V_ID := :NEW.ID;
  RFGL_TRANSACTION_PKG.V_INCOME := :NEW.INCOME;
  RFGL_TRANSACTION_PKG.V_TRANSACTION_TIME := :NEW.TRANSACTION_TIME;
END TR_AREA_ROW;
--002交易表，表级触发
CREATE OR REPLACE TRIGGER TR_TRANSACTION
AFTER INSERT OR UPDATE OR DELETE ON RFGL_TRANSACTION
DECLARE
 V_YSJE NUMBER;
BEGIN
	--获取该户账单应收
    SELECT RECEIVABLE INTO V_YSJE FROM 
    (
       SELECT * FROM RFGL_BILL RB 
        WHERE RB.CUSTOMER_ID=RFGL_TRANSACTION_PKG.V_NEWCUSTOMERID
       ORDER BY RB.CREATED_TIME DESC
    ) 
    WHERE ROWNUM=1;
	--默认关阀
	INSERT INTO USER_SF_INFO
	  (YHBH, YSJE, JFJE, FMZT, JFRQ, SYNCSTATE)
	VALUES
	  (RFGL_TRANSACTION_PKG.V_ID, V_YSJE, RFGL_TRANSACTION_PKG.V_INCOME, 0, RFGL_TRANSACTION_PKG.V_TRANSACTION_TIME, 0);
END TR_TRANSACTION;

--三、抄表、定时器
--抄表序列
CREATE SEQUENCE EBZ_METER_READ_S
MINVALUE -999999999999999999999999999
MAXVALUE -1
START WITH -1
INCREMENT BY -1
CACHE 20;

--读取热计量系统计量信息到收费系统
CREATE OR REPLACE PROCEDURE RB_INFO_PRO IS 
/*V_CUSTOMERID      JL_METER_BIND.CUSTOMER_ID%TYPE; --用户ID
  V_CYCLE           EBZ_METER_READ.CYCLE%TYPE;      --采暖期
  V_METER_CODE      JL_METER.METER_CODE%TYPE;   --仪表ID
  V_LAST_VALUE      EBZ_METER_READ.LAST_VALUE%TYPE; --上次表数
  V_THIS_VALUE      EBZ_METER_READ.THIS_VALUE%TYPE; --本次表数
  V_USE_VALUE       EBZ_METER_READ.USE_VALUE%TYPE;  --表计用量
  V_REAL_VALUE      EBZ_METER_READ.REAL_VALUE%TYPE; --实际用量
  V_READ_DATE       EBZ_METER_READ.READ_DATE%TYPE;  --抄表日期
  V_METER_ID        JL_METER_BIND.YID%TYPE;         --仪表ID
  */
BEGIN
INSERT INTO EBZ_METER_READ
  (ID,
   REAL_VALUE,
   THIS_VALUE,
   LAST_VALUE,
   METER_ID,
   REMARK,
   READER,
   READ_DATE,
   USE_VALUE,
   CYCLE)
  SELECT 
        EBZ_METER_READ_S.NEXTVAL,
        R.SJYL,
        R.JZBL,
        R.QCBL,
        M.YID,
        '工大科雅中间库导入',
        '中间库导入',
        R.CBSJ,
        R.BJYL,
        R.CNQ
    FROM RB_INFO R, JL_METER_BIND M
   WHERE R.YHBH = M.CUSTOMER_ID
     AND R.RBBH = M.METERCODE
     AND SYNCSTATE = 0;
  --更改同步状态
  UPDATE RB_INFO
     SET SYNCSTATE = 1
  WHERE SYNCSTATE = 0;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END; 

--定时器，每分钟执行一次
DECLARE 
  JOB NUMBER;  
BEGIN 
  DBMS_JOB.SUBMIT(JOB, 'RB_INFO_PRO;', SYSDATE, 'TRUNC(SYSDATE,''MI'') + 1 / (24*60)');  
COMMIT;
END; 

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

--以下是使用PRAGMA AUTONOMOUS_TRANSACTION

--触发器
--暂时用自治事务，有单独的commit，rollback，不使用自治事务会和变异表处在一个事务中
--1 用户信息触发器
--01 用户表JC_CUSTOMER触发
-- 001 插入、更新触发
CREATE OR REPLACE TRIGGER TR_USER_INFO_IU 
AFTER INSERT OR UPDATE ON JC_CUSTOMER
FOR EACH ROW 
DECLARE
--使用自治事务，一个事务里的子事务
PRAGMA AUTONOMOUS_TRANSACTION; 
 RSSUM NUMBER;
BEGIN
 SELECT COUNT(*) INTO RSSUM FROM USER_INFO WHERE YHBH=:OLD.ID;
  IF RSSUM=0 THEN
    INSERT INTO USER_INFO
    (YHBH,
     YHBM,
     FGS,
     RLZ,
     XQ,
     LH,
     DYH,
     CS,
     SH,
     GNZT,
     YHMC,
     LXDH,
     CNMJ,
     SYNCSTATE)
  VALUES
    (:NEW.ID,
     :NEW.CODE,
     GET_ORGNAME_F(:NEW.COMPANY_CODE, '', '', '', '1'),
     GET_ORGNAME_F(:NEW.COMPANY_CODE, :NEW.STATION_CODE, '', '', '2'),
     GET_ORGNAME_F(:NEW.COMPANY_CODE, :NEW.STATION_CODE, :NEW.COMMUNITY_CODE, '', '3'),
     GET_ORGNAME_F(:NEW.COMPANY_CODE, :NEW.STATION_CODE, :NEW.COMMUNITY_CODE, :NEW.BUILDING_CODE, '4'),
     :NEW.UNIT_NO,
     :NEW.FLOOR_NO,
     :NEW.ROOM_NO,
     '正常',  --新增用户：正常
     :NEW.NAME,
     :NEW.CELLPHONE,
     TO_NUMBER(GET_ITEMVALUE_F(:NEW.ID,'SFMJ')), --新增用户表：0
     0);
  ELSIF RSSUM =1 THEN
   UPDATE USER_INFO
	 SET YHBH = :NEW.ID,
		 YHMC = :NEW.NAME,
		 LXDH = :NEW.CELLPHONE
	--	 GNZT = GET_ITEMVALUE_F(:NEW.ID,'GNZT'),
	--   CNMJ = GET_ITEMVALUE_F(:NEW.ID,'SFMJ')
   WHERE YHBH = :OLD.ID;
  END IF;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END TR_USER_INFO_IU;

--002 删除触发
CREATE OR REPLACE TRIGGER TR_USER_INFO_DELETE
AFTER DELETE ON JC_CUSTOMER
FOR EACH ROW 
DECLARE
--使用自治事务，一个事务里的子事务
PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN
  DELETE FROM USER_INFO WHERE YHBH = :OLD.ID;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END TR_USER_INFO_DELETE;

--02 面积表JC_AREA触发
--001插入、更新 触发 
CREATE OR REPLACE TRIGGER TR_AREA_INFO
AFTER INSERT OR UPDATE OR DELETE ON JC_AREA
FOR EACH ROW
DECLARE
--使用自定义事务
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE USER_INFO
    SET CNMJ = GET_ITEMVALUE_F(:NEW.JCCUSTOMERID,'SFMJ'),
	    GNZT = GET_ITEMVALUE_F(:NEW.ID,'GNZT')
  WHERE YHBH = :OLD.JCCUSTOMERID;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END TR_AREA_INFO;


--2 收费信息触发器
CREATE OR REPLACE TRIGGER TR_USER_SF_INFO
AFTER INSERT OR UPDATE OR DELETE ON RFGL_TRANSACTION
FOR EACH ROW
DECLARE
--使用自定义事务
PRAGMA AUTONOMOUS_TRANSACTION;
 V_YSJE NUMBER;
BEGIN
	--获取该户账单应收
    SELECT RECEIVABLE INTO V_YSJE FROM 
    (
       SELECT * FROM RFGL_BILL RB 
        WHERE RB.CUSTOMER_ID=:NEW.CUSTOMER_ID
       ORDER BY RB.CREATED_TIME DESC
    ) 
    WHERE ROWNUM=1;
	--默认关阀
	INSERT INTO USER_SF_INFO
	  (YHBH, YSJE, JFJE, FMZT, JFRQ, SYNCSTATE)
	VALUES
	  (:NEW.ID, V_YSJE, :NEW.INCOME, 0, :NEW.TRANSACTION_TIME, 0);
	COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END;

补充Oracle定时器内容：

declare

jobno number;

begin dbms_job.submit(

　　jobno,--定时器ID，系统自动获得

　　'PRC_INSERT;', --what执行的过程名

　　 sysdate,--next_date,定时器开始执行的时间，这样写表示立即执行

　　'sysdate + 15/1440'--interval,设置定时器执行的频率，这样写每隔15分钟执行一次

);

commit; 

end;



这里第一个参数是任务编号，系统自动赋值。也可以采用isubmit来手动指定

第二个参数是需要执行的任务过程，代码长的话，可以将它写到一个存储过程里，再放到里面调用，比如'pro_test;' (pro_test假定为一个存储过程名)

第三个参数是，自动任务第一次执行的时间，如果需要它立即执行，则使用sysdate

最后一个参数，系统根据该参数的值指定下一次的执行时间。





declare

jobno

number;

begin

dbms_job.remove(45);

commit;

end;



exec dbms_job.remove(83);--删除一个定时器
exec dbms_job.run(84);--运行一个定时器
exec DBMS_JOB.BROKEN(83,SYS.DIUTIL.INT_TO_BOOL(1));--停止一个定时器
exec DBMS_JOB.INTERVAL(84, 'sysdate + 60/1440');--改变一个定时器的执行频率成每隔一小时执行一次


select * from user_jobs；——查看调度任务

select * from dba_jobs_running；——查看正在执行的调度任务

select * from dba_jobs；——查看执行完的调度任务





定时器的参数说明：

myjob参数是由Submit()过程返回的binary_ineger。这个值用来唯一标识一个工作；

what参数是将被执行的PL/SQL代码块，这里指的是一个存储过程，注意名字后面的分号；

next_date参数指识何时将运行这个工作。写Job的时候可以不指定该值；

interval参数何时这个工作将被重执行。

关于interval的设置，参考以下几个例子：

1、 每分钟执行

Interval => TRUNC(sysdate,’mi’) + 1 / (24*60)

2、 每天定时执行

例如：每天的凌晨2点执行

Interval => TRUNC(sysdate) + 1 +2 / (24)

3、 每周定时执行

例如：每周一凌晨2点执行

Interval => TRUNC(next_day(sysdate,2))+2/24 --星期一,一周的第二天

4、 每月定时执行

例如：每月1日凌晨2点执行

Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24

5、 每季度定时执行

例如每季度的第一天凌晨2点执行

Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24

6、 每半年定时执行

例如：每年7月1日和1月1日凌晨2点

Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24

7、 每年定时执行

例如：每年1月1日凌晨2点执行

Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24

coderLee0607

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
触发器变异表问题，oracle--ORA:04091：触发器/函数不能读它

问题分析在Oracle中执行DML语句的时候是需要显示进行提交操作的。当我们进行插入的时候，会触发触发器执行对触发器作用表和扩展表的种种操作，但是这个时候触发器和插入语句是在同一个事务管理中的，因此在插入语句没有被提交的情况下，我们无法对触发器作用表进行其他额外的操作。如果执行其他额外的操作则会抛出如上异常信息。解决方案1,我们知道，出错的原因是因为触发器和DML语句在同一事务管理中，...
复制链接

扫一扫

专栏目录