oracle带游标存储过程编写

create or replace procedure pro_init_dzz_dy_exception
as
    log_id number(16);     --新增日志id
    errdesc varchar(3000); --新增日志内容
    v_userid varchar(40);
    v_zjhm varchar(30);
    v_csrq date;
    v_rdsj date;
    v_zzsj date;
    v_idcardmult number(1);
    v_idcardvalidity number(1);
    v_subzjhmstr varchar(50);
    v_csrqstr varchar(50);
    dbname varchar(100);
    insertSqlStrStart varchar(5000);
    insertSqlStrMiddle varchar(5000);
    insertSqlStrEnd varchar(5000);
    insertSqlStr varchar(5000);
    querysql varchar(5000);
    deletesql varchar(5000);
    v_all_dy sys_refcursor;
    --v_dy_info zj_zjs.T_DY_INFO%rowtype;
    cursor allSche is select schemaname from s_qkdzzinfo;
    
begin
     
    SELECT SEQ_AUTO_CALC_LOG.NEXTVAL INTO LOG_ID FROM DUAL;
    --插入日志表
    INSERT INTO T_AUTO_CALC_LOG
      (UUID, DJSJ, CCGCNAME, CWDM)
    VALUES
      (LOG_ID, SYSDATE, 'pro_init_dzz_dy_exception', '0');
    COMMIT;
    
    
    deletesql := 'delete from t_dzz_dy_exception_info';
    execute immediate deletesql;
    commit;
    
    insertSqlStrStart := 'insert into t_dzz_dy_exception_info 
                         (uuid,dzz_dy_id,datatype,errorlevel,errortype,operatetime) 
                         select sys_guid(),';
    insertSqlStrEnd := 'sysdate from dual';
    for v_sche in allSche loop
      dbname := v_sche.schemaname;
      querysql := 'select userid,zjhm,csrq,rdsj,zzsj,idcardmult,idcardvalidity from '||dbname||'.t_dy_info where delflag =1 and dylb in (1,2) and dyzt = 1';
      open v_all_dy for querysql;
      
      loop
        fetch v_all_dy into v_userid,v_zjhm,v_csrq,v_rdsj,v_zzsj,v_idcardmult,v_idcardvalidity;
         --v_userid := v_dy_info.userid;
         v_zjhm := REPLACE (v_zjhm,' ','');
         --v_csrq := v_dy_info.csrq;
         --v_rdsj := v_dy_info.rdsj;
         --v_zzsj := v_dy_info.zzsj;
         --v_idcardmult := v_dy_info.idcardmult;
         --v_idcardvalidity := v_dy_info.idcardvalidity;
            if v_zjhm is not null and v_csrq is not null then
                v_subzjhmstr := substrb(v_zjhm, 7, 4)||'-'||substrb(v_zjhm, 11, 2)||'-'||substrb(v_zjhm, 13, 2);
                v_csrqstr := to_char(v_csrq,'yyyy-mm-dd');
                if v_subzjhmstr != v_csrqstr then
                   insertSqlStrMiddle := ',2,1,1,';
                   insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
                   execute immediate insertSqlStr;
                   commit;
                end if;
            end if;
            if v_rdsj is not null and v_zzsj is not null then
               if (to_date('1921-07-01','yyyy-mm-dd') <= v_rdsj and v_rdsj <= to_date('1923-06-09','yyyy-mm-dd')) or
                  (to_date('1928-06-18','yyyy-mm-dd') <= v_rdsj and v_rdsj <= to_date('1945-04-22','yyyy-mm-dd')) or
                  (to_date('1969-04-01','yyyy-mm-dd') <= v_rdsj and v_rdsj <= to_date('1977-08-11','yyyy-mm-dd')) then
                 if  v_zzsj - v_rdsj <> 0 then
                   insertSqlStrMiddle := ',2,1,2,';
                   insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
                   execute immediate insertSqlStr;
                   commit;
                 end if;
               elsif to_date('1923-06-10','yyyy-mm-dd') <= v_rdsj and v_rdsj <= to_date('1927-04-26','yyyy-mm-dd') then
                 if  months_between(v_zzsj,v_rdsj) not in (3,6) then
                   insertSqlStrMiddle := ',2,1,2,';
                   insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
                   execute immediate insertSqlStr;
                   commit;
                 end if;
               elsif to_date('1927-04-27','yyyy-mm-dd') <= v_rdsj and v_rdsj <= to_date('1928-06-17','yyyy-mm-dd') then
                 if  months_between(v_zzsj,v_rdsj) not in (0,3) then
                   insertSqlStrMiddle := ',2,1,2,';
                   insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
                   execute immediate insertSqlStr;
                   commit;
                 end if;
               elsif to_date('1945-04-23','yyyy-mm-dd') <= v_rdsj and v_rdsj <= to_date('1956-09-14','yyyy-mm-dd') then
                 if  months_between(v_zzsj,v_rdsj) not in (6,12,24) then
                   insertSqlStrMiddle := ',2,1,2,';
                   insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
                   execute immediate insertSqlStr;
                   commit;
                 end if;
               else
                 if  v_zzsj - v_rdsj not in (12) then
                   insertSqlStrMiddle := ',2,1,2,';
                   insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
                   execute immediate insertSqlStr;
                   commit;
                 end if;
               end if;
            end if;
            if v_idcardvalidity is not null then
              if v_idcardvalidity = 1 then
                 insertSqlStrMiddle := ',2,1,3,';
                 insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
                 execute immediate insertSqlStr;
                 commit;
              end if;
            end if;
            if v_idcardmult is not null then
              if v_idcardmult = 1 then
                 insertSqlStrMiddle := ',2,1,4,';
                 insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
                 execute immediate insertSqlStr;
                 commit;
              end if;
            end if;
            if v_rdsj is not null and v_csrq is not null then
              if months_between(v_rdsj,v_csrq) < 228 then
                 insertSqlStrMiddle := ',2,1,5,';
                 insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
                 execute immediate insertSqlStr;
                 commit;
              end if;
              if v_rdsj < v_csrq then
               insertSqlStrMiddle := ',2,2,6,';
               insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
               execute immediate insertSqlStr;
               commit;
              end if;
            end if;
            if v_rdsj is not null then
              if v_rdsj < to_date('1921-07-01','yyyy-mm-dd') then
                 insertSqlStrMiddle := ',2,2,7,';
                 insertSqlStr := insertSqlStrStart||chr(39)||v_userid||chr(39)||insertSqlStrMiddle||insertSqlStrEnd;
                 execute immediate insertSqlStr;
                 commit;
              end if;
            end if;
            exit when v_all_dy%notfound;
         end loop;
         close v_all_dy;
      end loop;
      
    --日志表更新结束时间
    update t_auto_calc_log py set py.jsdjsj = sysdate where uuid = log_id;
    commit;

    EXCEPTION
      WHEN OTHERS THEN
        BEGIN
          ROLLBACK;
          ERRDESC := SQLERRM; --如果存储过程执行失败,则修改记录日志并将异常内容存入表中
          DBMS_OUTPUT.PUT_LINE(ERRDESC);
          UPDATE T_AUTO_CALC_LOG PY
             SET PY.JSDJSJ = SYSDATE, PY.CWDM = '1', PY.CWXX = ERRDESC
           WHERE UUID = LOG_ID;
          COMMIT;
        END;
end pro_init_dzz_dy_exception;