Oracleの之存储过程编写

--包头部分

CREATE OR REPLACE PACKAGE PKG_REPAIR_ZTB AS
  PROCEDURE PRO_XTCPYYTZD_JSON( --RESULTMESSAGE OUT SYS_REFCURSOR --结果信息
                               RESULTALL  OUT SYS_REFCURSOR
                              ,V_BEG_DATE IN VARCHAR2
                              , --开始时间  
                               V_END_DATE IN VARCHAR2
                              , --结束时间
                               V_CODE     OUT VARCHAR2
                               --循环控制码
                               );

END PKG_REPAIR_ZTB;

--包体部分

CREATE OR REPLACE PACKAGE BODY PKG_REPAIR_ZTB AS

PROCEDURE PRO_XTCPYYTZD_JSON
  /*
    **************************************************
            
    程序功能：3.信托产品运营通知单
    编写人：ZHANGTB
    时间：20210808
    传参：开始时间，结束时间，产品名称
            
    **************************************************
    */
  (RESULTALL  OUT SYS_REFCURSOR
  ,V_BEG_DATE IN VARCHAR2
  , --开始时间  （存储前端输入用）
   V_END_DATE IN VARCHAR2
  , --结束时间（存储前端输入用）
   V_CODE     OUT VARCHAR2
   --循环控制码
   ) IS
    V_CPNAME        VARCHAR2(100); --产品名称
    V_CALENDER_DATE VARCHAR2(20); --开始时间（日历时间，处理时间片）
    V_STARTDATE     VARCHAR2(20); --开始时间（插入落地表用）
    V_ENDDATE       VARCHAR2(20); --结束时间（插入落地表用）
    V_FIRSTDATE     VARCHAR2(20); --开始时间（前台显示用）
    V_SECENDDATE    VARCHAR2(20); --结束时间（前台显示用）
    V_NUMBER        VARCHAR2(20); --序列号（合同号）
    V_XTCPDJBM      VARCHAR2(50); --中信登编码
    V_COUNT         NUMBER(20); --数量
    V_HTBH          VARCHAR2(100); --金格文件编码
    ---金非模板
    V_SQL   VARCHAR2(32767);
    V_HTBM  VARCHAR2(300); --金非合同模板编号    
    I       NUMBER(16);
    V_JSON  VARCHAR2(32767);
    V_UNION VARCHAR2(32767);
    --RESULTALL    SYS_REFCURSOR;
    I_COUNT INT;
    I_LARGE INT;
    V_WJBH  VARCHAR2(50);
    K       NUMBER(16);
  
    --||一周内每一天的日期--游标部分
    CURSOR DATE_CALANDER IS
      SELECT TO_CHAR((TRUNC(TO_DATE(TO_CHAR(TO_DATE(V_BEG_DATE, 'yyyymmdd'), 'yyyy-mm-dd'), 'yyyy-mm-dd'), 'iw')) +
                     ROWNUM - 1,
                     'yyyyMMdd') AS TIME_DATE
        FROM DUAL
      CONNECT BY ROWNUM <= 365;
  
  BEGIN             --程序开始
  
    I      := 1;
    V_HTBM := 't284';
  
    --情况1.当不录入产品名称时，执行此任务。  
    --第一步：整理出当前时间所在的周。
    --||时间片处理
    OPEN DATE_CALANDER;
    --EXECUTE IMMEDIATE 'TRUNCATE TABLE TYYTZD_CALENDAR';
    --EXECUTE IMMEDIATE 'TRUNCATE TABLE TCPYYTZD';
  
    LOOP
      EXIT WHEN DATE_CALANDER%NOTFOUND;
      FETCH DATE_CALANDER
        INTO V_CALENDER_DATE;
      INSERT INTO TYYTZD_CALENDAR W
        (W.ID
        ,W.C_DATE)
      VALUES
        (FUNC_NEXTID('tyytzd_calendar')
        ,V_CALENDER_DATE);
      COMMIT;
      --||删除时间片中重复数据
      DELETE FROM TYYTZD_CALENDAR A
       WHERE A.ROWID != (SELECT MAX(B.ROWID) FROM TYYTZD_CALENDAR B WHERE A.C_DATE = B.C_DATE);
    END LOOP;
  
    --限定取值范围(取这段时间的记录,生成pdf文件.)
    SELECT MIN(K.C_DATE)
          ,MAX(K.C_DATE)
      INTO V_FIRSTDATE
          ,V_SECENDDATE
      FROM TYYTZD_CALENDAR K;
  
    --将时间片内的记录插入到产品运营通知单表中
    MERGE INTO TCPYYTZD WW
    USING (SELECT FUNC_NEXTID('tcpyytzd') ID
                 ,M.CPMC CPMC
                 ,W.SXSJ BEG_DATE
                 ,W.SXSJ END_DATE
                 ,H.XTCPDJBM ZXDBM
                 ,W.ID HTID
                 ,ROWNUM Y
                 ,0
             FROM THTXX W
             LEFT JOIN TFP_CPDM M
               ON W.CPID = M.ID
             LEFT JOIN TFP_XTJH H
               ON H.ID = M.XTJH
            WHERE (W.SXSJ >= V_FIRSTDATE AND W.SXSJ <= V_SECENDDATE AND W.HTZT != -1)
            ORDER BY W.SXSJ) B
    ON (B.HTID = WW.HTID)
    WHEN MATCHED THEN
      UPDATE SET WW.CPMC = B.CPMC
    WHEN NOT MATCHED THEN
      INSERT
        (WW.ID
        ,WW.CPMC
        ,WW.KSSJ
        ,WW.JSSJ
        ,WW.ZXDBM
        ,WW.HTID
        ,WW.ROW_NUM
        ,WW.SFSCWJBZ)
      VALUES
        (B.ID
        ,B.CPMC
        ,B.BEG_DATE
        ,B.END_DATE
        ,B.ZXDBM
        ,B.HTID
        ,B.Y
        ,0);
    COMMIT;
  
    FOR X IN (SELECT U.ID    编号
                    ,U.CPMC  产品名称
                    ,U.KSSJ  开始时间
                    ,U.JSSJ  结束时间
                    ,U.ZXDBM 中信登编码
                    ,U.HTID  合同ID
                FROM TCPYYTZD U
               WHERE U.SFSCWJBZ = 0
               ORDER BY U.KSSJ) LOOP
    
      V_JSON := FUNC_JSON(1, V_JSON, '"v_cpname' || I || '": "' || X.产品名称 || '"');
      V_JSON := FUNC_JSON(1, V_JSON, '"v_startdate' || I || '": "' || X.开始时间 || '"');
      V_JSON := FUNC_JSON(1, V_JSON, '"v_xtcpdjbm' || I || '": "' || X.中信登编码 || '"');
      I      := I + 1;
      UPDATE TCPYYTZD U SET U.SFSCWJBZ = 1 WHERE U.ID = X.编号;
      COMMIT;
      EXIT WHEN I = 21;
    END LOOP;
  
    --LIVEBOS调用控制参数
    /*    IF I = 6 THEN
      V_CODE := 1;
    ELSE
      V_CODE := -1;
    END IF;*/
  
    --当记录<20条时,执行此条语句
    FOR J IN I .. 20 LOOP
      V_JSON := FUNC_JSON(1, V_JSON, '"v_cpname' || J || '": "' || ' ' || '"');
      V_JSON := FUNC_JSON(1, V_JSON, '"v_startdate' || J || '": "' || ' ' || '"');
      V_JSON := FUNC_JSON(1, V_JSON, '"v_xtcpdjbm' || J || '": "' || ' ' || '"');
    
    END LOOP;
  
    V_WJBH := TO_CHAR(SYSDATE, 'yyyymmddhh24miss') || '_' || SUBSTR(V_FIRSTDATE, -4) || '_' || SUBSTR(V_SECENDDATE, -4);
    --SELECT CEIL(COUNT(1) / 3) INTO I_LARGE FROM TCPYYTZD CH;
    V_HTBH := GET_ZHBH();
    V_SQL  := 'SELECT ''' || V_HTBM || ''' 模板编码,      
              ''' || V_HTBH || '''   合同编号,
              ''我是合同名称''   合同名称,
             ''{' || TRIM(',' FROM V_JSON) || '}'' 填充数据,
             ''' || '' || '/home/JZXT/data/app/CPYYTZD/' || V_WJBH || '.CPYYTZD'' 合同保存路径,
                ''KK'' 乙方签章关键字,
                ''Apexsoft_test'' 乙方签章用户编号, 
                ''LL'' 乙方签章编码,
                ''http://10.69.101.130:8080/tosignserver'' 金非信签服务地址,
                ''0dc44bb0b5e84585b9a99749f5ea7d9d'' 金非信签应用ID,
                ''B9Hfq_ewDUD'' 金非信签应用秘钥,
                ''/home/JZXT/data/app/TDZHTYSMB/'' 证据链存放临时目录  
                 FROM DUAL ';
  
    OPEN RESULTALL FOR V_SQL;
  
  END;
END PKG_REPAIR_ZTB;