orcal 游标使用

1.创建异常记录表

CREATE OR REPLACE PROCEDURE record_proc_err_log(module_name varchar2,
                proc_name   varchar2,
                v_SQLCODE   varchar2,
                v_SQLERRM   varchar2,
                v_err_line  varchar2) is
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  insert into dm_proc_err_log
    (log_id,
     module_name,
     proc_name,
     err_time,
     sql_code,
     sql_errm,
     err_content)
  values
    (seq_record_proc_err.nextval,
     module_name,
     proc_name,
     sysdate,
     v_SQLCODE,
     v_SQLERRM,
     v_err_line);
  commit;
END record_proc_err_log;


create sequence SEQ_RECORD_PROC_ERR
minvalue 1
maxvalue 9999999999999999999999999999
start with 35476209
increment by 1
cache 20;

2.创建游标存储过程

CREATE OR REPLACE PROCEDURE P_SP_SO_APP_INFO
IS
  V_START_TIME DATE            := to_DATE('20180705','yyyyMMdd');
  V_ID            varchar(32)  := '';
  V_REPORTER_UID  varchar(32)  := '';
  V_ORG_CODE      varchar(6)  := '';
  V_ORG_NAME      varchar(50)  := '';
BEGIN

DELETE dm_proc_err_log WHERE MODULE_NAME='生产应用情况统计';

DELETE SP_SO_APP_INFO;

INSERT INTO SP_SO_APP_INFO

SELECT ID, PROVINCE_CODE, BUREAU_CODE, NULL, NULL, REPORTER_UID , 1, SYSDATE
  FROM SP_PD_TRIP_BILL TT --1-跳闸记录
 WHERE TT.CREATE_DATE >=V_START_TIME
UNION ALL
SELECT ID, PROVINCE_CODE, BUREAU_CODE, NULL, NULL, CREATOR_UID, 5, SYSDATE
  FROM SP_PD_DEFECT D --5-缺陷单
 WHERE D.STATE IN (2, 3, 4, 5, 6, 8)
   AND CREATE_DATE >=V_START_TIME
UNION ALL
SELECT ID, PROVINCE_CODE, BUREAU_CODE, NULL, NULL, P.CREATE_UID, 6, SYSDATE
  FROM SP_PD_PP_INFO P --6-维护检修计划
 WHERE P.PLAN_STATE != '10'
   AND CREATE_DATE >=V_START_TIME
UNION ALL
SELECT ID, PROVINCE_CODE, BUREAU_CODE, NULL, NULL, CREATE_UID, 3, SYSDATE
  FROM SP_PD_WTICKET_BASE --3-工作票
 WHERE WORK_STATE != 1
   AND CREATE_TIME >=V_START_TIME
UNION ALL
SELECT ID, PROVINCE_CODE, BUREAU_CODE, NULL, NULL, CREATOR_UID, 2, SYSDATE
  FROM SP_PD_OTICKET --2-操作票
 WHERE CREATE_DATE >=V_START_TIME;

 
 --查询数据到游标
 for DM in ( SELECT ID,PERSON_NAME FROM SP_SO_APP_INFO)
    LOOP
       BEGIN  
       V_ID           := DM.ID;
       V_REPORTER_UID := DM.PERSON_NAME;
       
      --查找县区局
      SELECT  ORG_NAME, ORG_CODE INTO V_ORG_NAME,V_ORG_CODE
        FROM (SELECT *
                FROM TOP_ORGANIZATION
               START WITH ORG_ID = (SELECT ORG_ID
                                      FROM TOP_USER
                                     WHERE USER_ID = V_REPORTER_UID
                                       AND ROWNUM = 1)
              CONNECT BY PRIOR PARENT_ORG_ID = ORG_ID) TT
       WHERE TT.ORG_LEVEL = 3;
     --根据ID更新县区局
       update SP_SO_APP_INFO  set ORG_CODE = V_ORG_CODE ,ORG_NAME = V_ORG_NAME where ID = V_ID;
   
  -- COMMIT;   
 EXCEPTION
        WHEN OTHERS THEN
           record_proc_err_log('生产应用情况统计',
                        'P_SP_SO_APP_INFO',
                        SQLCODE,
                        'ID:'||V_ID||SQLERRM,
                        substr(dbms_utility.format_error_backtrace, 1, 400));
        END;
    end loop;
COMMIT;
end P_SP_SO_APP_INFO;