oracle存储过程举例之二

CREATE OR REPLACE PROCEDURE PROC_REPORT_ALARM(ALARM_TYPE_ID   IN INTEGER,ALARM_TYPE_NAME IN VARCHAR2,ALARM_CONTENT   IN VARCHAR2,LINE_CODE       IN VARCHAR2,STATION           IN VARCHAR2,REPORTER_CODE   IN VARCHAR2,RESULT          OUT VARCHAR2) AS
  V_ALARM_TYPE_ID   INTEGER := ALARM_TYPE_ID;
  V_ALARM_TYPE_NAME VARCHAR2(50) := ALARM_TYPE_NAME;
  V_ALARM_CONTENT   VARCHAR2(1024) := ALARM_CONTENT;
  V_LINE_CODE       VARCHAR2(50) := LINE_CODE;
  V_STATION         VARCHAR2(50) := STATION;
  V_REPORTER_CODE   VARCHAR2(50) := REPORTER_CODE;
  V_REPORT_ID       INT;
  V_ERR_MSG         VARCHAR2(1000);
  V_BEGIN_TIME      TIMESTAMP := SYSTIMESTAMP;
  V_PROC_NAME       VARCHAR2(1000) := 'PROC_REPORT_ALARM/' ||
                                      V_ALARM_TYPE_ID || '/' ||
                                      V_ALARM_TYPE_NAME || '/' ||
                                      V_ALARM_CONTENT || '/' || V_LINE_CODE || '/' ||
                                      V_STATION || '/' || V_REPORTER_CODE;
  V_EXE_MSG      CLOB;
  V_OLD_TIME     DATE ;
  V_NEW_TIME    DATE :=SYSDATE();
 V_NOW_TIME    DATE :=SYSDATE();
  --V_OLD_CONTENT VARCHAR2(1024);
  --V_TIMES NUMBER;
  V_NUM NUMBER;
  --时间差60秒
  V_TIME_DT  NUMBER :=60*30;


  /*预警上报*/


BEGIN
RESULT := 'OK';


 IF(V_ALARM_TYPE_ID=-1) THEN
 RESULT :=  'NO-OK';
 RETURN;
 END IF;


--判断该线体是否在生产（屏蔽不生产的线体报警）ANNE.DAI 20180109
 




  DBMS_LOB.CREATETEMPORARY(V_EXE_MSG,TRUE); --初始化CLOB
  DBMS_LOB.APPEND(V_EXE_MSG,'预警上报处理:' || CHR(10));


--连续重复预警检查


SELECT  NVL(MAX(REPORT_TIME),V_BEGIN_TIME) INTO V_OLD_TIME FROM AD_ALARM_REPORT
WHERE  ALARM_CONTENT= V_ALARM_CONTENT AND LINE_CODE=V_LINE_CODE  AND STATION=V_STATION
 AND (REPORT_STATUS='REPORT' OR REPORT_STATUS='RESPONSE') ;


  IF(V_OLD_TIME<>V_NEW_TIME) THEN
     IF(ROUND(TO_NUMBER(V_NEW_TIME - V_OLD_TIME ) * 24 * 60 * 60)<V_TIME_DT) THEN
           RAISE_APPLICATION_ERROR(-20001,TO_CHAR(V_TIME_DT/60) || '分钟内不能提报相同的预警');
           RETURN;
     END IF;
 END IF;


  --获取主键
  SELECT AD_ALARM_REPORT_SEQ.NEXTVAL INTO V_REPORT_ID FROM DUAL;
  DBMS_LOB.APPEND(V_EXE_MSG,'V_REPORT_ID:' || V_REPORT_ID || CHR(10));
  --增加预警上报记录
  INSERT INTO AD_ALARM_REPORT
    (REPORT_ID,ALARM_TYPE_ID,ALARM_TYPE_NAME,ALARM_CONTENT,LINE_CODE,-- LINE_DESC,STATION,REPORTER_CODE,REPORTER_NAME,REPORT_TIME,REPORT_STATUS)
  VALUES
    (V_REPORT_ID,V_ALARM_TYPE_ID,V_ALARM_TYPE_NAME,V_ALARM_CONTENT,V_LINE_CODE,V_STATION,V_REPORTER_CODE,(SELECT NVL(MAX(ST1.USER_NAME),V_REPORTER_CODE)
        FROM SYS_USER ST1
       where ST1.USER_CODE = V_REPORTER_CODE),SYSDATE,'REPORT');
  DBMS_LOB.APPEND(V_EXE_MSG,'新增AD_ALARM_REPORT' || sql%ROWCOUNT || '条' || CHR(10));
  --拆分RESPONSE
  INSERT INTO AD_ALARM_EVENT
    (ALARM_REPORT_ID,START_TIME,EVENT_TYPE,EVENT_INTERVAL,EVENT_TIME,RESPONDER_CODE,RESPONDER_NAME,MODE_ID,AD_ROLE_ID,PRIORITY)
    SELECT REPORT_ID,'RESPONSE',T.AD_RESPONSE_INTERVAL,T.RESPONSE_EVENT_TIME  AS EVENT_TIME,T.RECEIVER_CODE        AS RESPONDER_CODE,T.RECEIVER_NAME        AS RESPONDER_NAME,T.RESPONSE_MODE_ID,T.AD_RECEIVER_ROLE_ID,P.PRIORITY
      FROM V_ALARM_REPORT_USER_R T--V_ALARM_REPORT_USER T
      LEFT JOIN AD_REPORT_MODE P ON  T.RESPONSE_MODE_ID=P.MODE_ID
     WHERE T.REPORT_ID = V_REPORT_ID ;
  DBMS_LOB.APPEND(V_EXE_MSG,'新增AD_ALARM_EVENT-RESPONSE' || sql%ROWCOUNT || '条' ||
                  CHR(10));
  --如果配置了线别与角色的关系,则删除其余角色的event表数据
  DELETE FROM AD_ALARM_EVENT T
   WHERE T.ALARM_REPORT_ID = V_REPORT_ID
     AND T.EVENT_TYPE = 'RESPONSE'
     AND NOT EXISTS (SELECT 1
            FROM BASE_USER_ROLE ST1
           WHERE ST1.LINE_CODE = T.LINE_CODE
             AND ST1.ROLE_ID = T.AD_ROLE_ID) --EVENT中数据与AD_LINE_CODE无关联
     AND EXISTS (SELECT 1
            FROM AD_ALARM_EVENT ST2,BASE_USER_ROLE ST3
           WHERE ST2.ALARM_REPORT_ID = V_REPORT_ID
             AND ST2.LINE_CODE = ST3.LINE_CODE
             AND ST2.AD_ROLE_ID = ST3.ROLE_ID);--EVENT中存在于LINE相关数据


DELETE  FROM AD_ALARM_EVENT AE WHERE MODE_ID=2  AND  ALARM_REPORT_ID= V_REPORT_ID   AND   AE.RESPONDER_CODE   IN (
SELECT USER_CODE  FROM (
SELECT USER_CODE,WMSYS.WM_CONCAT(LINE_CODE)  LINESTR FROM AD_WATCH_USER GROUP BY USER_CODE) T WHERE LINESTR NOT LIKE '%'||AE.LINE_CODE||'%'
)  AND  RESPONDER_CODE NOT IN (
SELECT USER_CODE  FROM SYS_USER WHERE LINE_CODE='ANDON-LINE-ALL' AND USER_AD_ROLES<>7 );


DELETE  FROM AD_ALARM_EVENT AE WHERE AE.RESPONDER_CODE NOT IN (SELECT USER_CODE
 FROM AD_WATCH_USER  GROUP BY USER_CODE ) AND ALARM_REPORT_ID=V_REPORT_ID AND MODE_ID=2;


  DBMS_LOB.APPEND(V_EXE_MSG,'删除EVENT-RESPONSE' || sql%ROWCOUNT || '条' || CHR(10));


  --日志处理
  PSYS_SAVELOG(V_PROC_NAME,'Y',V_EXE_MSG,V_BEGIN_TIME);
  --异常处理
  RETURN; --若为顺序执行到此位置，则直接返回
  <<PROCESS_ERRMSG>>
  PSYS_PROCESS_ERRMSG(V_PROC_NAME,V_ERR_MSG,V_BEGIN_TIME,RESULT);
EXCEPTION
  WHEN OTHERS THEN
    V_ERR_MSG := SUBSTR(sqlERRM,1,160);
    DBMS_LOB.APPEND(V_EXE_MSG,V_ERR_MSG);
    PSYS_SAVELOG(V_PROC_NAME,'N',V_BEGIN_TIME);
    RESULT := V_ERR_MSG; --错误号对应的信息
END;
oracle存储过程举例之二

猜你在找的Oracle相关文章
