Cacher is the code snippet organizer for pro developers

--根据分号切割（只返回一个字符）
    --        SELECT NVL(REGEXP_SUBSTR(LB, '[^,]', 1, 2+(i-1)*12, 'i'), 0) INTO LB_SPLIT FROM DUAL;
CREATE OR REPLACE PROCEDURE CHANGETABLE IS
  --TYPE S  IS TABLE OF  V_GK_TJ_NEWS_ALL %ROWTYPE;
  --   V_STRUCTS S ;
  CURSOR C_RECORDS IS
    SELECT * FROM V_GK_TJ_NEWS_ALL T;
  V_STRUCTS  C_RECORDS%ROWTYPE;
  LB         VARCHAR2(500);
  ARRLENGTH  NUMBER;
  I          NUMBER;
  LEN        NUMBER;
  LB_SPLIT   VARCHAR2(100);
  V_SQL_STMT VARCHAR2(500);
BEGIN
  EXECUTE IMMEDIATE 'TRUNCATE TABLE T_V_GK_TJ_NEWS_ALL_SPLIT';
  OPEN C_RECORDS;
  LOOP
    FETCH C_RECORDS
      INTO V_STRUCTS;
    EXIT WHEN C_RECORDS%NOTFOUND;
    LB := V_STRUCTS.RYXL;
   -- DBMS_OUTPUT.PUT_LINE(LB); 
    SELECT NVL(LENGTH(LB), 0) INTO LEN FROM DUAL;
  
    IF LEN > 0 THEN
      BEGIN
        SELECT ((LENGTH(LB) - LENGTH(REGEXP_REPLACE(LB, ',', ''))) + 1) INTO ARRLENGTH FROM DUAL;
      
        --DBMS_OUTPUT.PUT_LINE(ARRLENGTH);--数组长度
        I := 1;
        WHILE I <= ARRLENGTH LOOP
          BEGIN
            SELECT NVL(REGEXP_SUBSTR(LB, '[^,]', 1, 2+(i-1)*12, 'i'), 0) INTO LB_SPLIT FROM DUAL;
            --DBMS_OUTPUT.PUT_LINE(V_STRUCTS.TEST_ID);
            -- DBMS_OUTPUT.PUT_LINE(LB_SPLIT);--
            INSERT INTO T_V_GK_TJ_NEWS_ALL_SPLIT
              (WBGJ, LGGJ, MHXX, QTXX, HDSJ, GRBH,XM, HJDPCSBM, RYXL, JG)
            VALUES
              (V_STRUCTS.WBGJ,
               V_STRUCTS.LGGJ,
               V_STRUCTS.MHXX,
               V_STRUCTS.QTXX,
               V_STRUCTS.HDSJ,
               V_STRUCTS.GRBH,
               V_STRUCTS.XM,
               V_STRUCTS.HJDPCSBM,
               LB_SPLIT,
               V_STRUCTS.JG);
            COMMIT;
            I := I + 1;
          END;
        
        END LOOP;
      END;
    ELSE
      INSERT INTO T_V_GK_TJ_NEWS_ALL_SPLIT
        (WBGJ, LGGJ, MHXX, QTXX, HDSJ, GRBH, XM, HJDPCSBM, RYXL, JG)
      VALUES
        (V_STRUCTS.WBGJ,
         V_STRUCTS.LGGJ,
         V_STRUCTS.MHXX,
         V_STRUCTS.QTXX,
         V_STRUCTS.HDSJ,
         V_STRUCTS.GRBH,
         V_STRUCTS.XM,
         V_STRUCTS.HJDPCSBM,
         '0',
         V_STRUCTS.JG);
      COMMIT;
    END IF;
  END LOOP;
  CLOSE C_RECORDS;
EXCEPTION
  WHEN OTHERS THEN
    V_SQL_STMT := 'SQLCODE:' || SQLCODE || '###SQLERRM:' || SQLERRM;
    ROLLBACK;
    --成功时记录日志
    INSERT INTO GK_BD_LOG
      (BD_LOG_CODE, PROC_KSSJ, PROC_JSSJ, BD_JZSJC, CGBZ, PROC_NAME)
    VALUES
      (F_GETSEQ(30), SYSDATE, SYSDATE, SYSDATE, 'Y', 'CHANGETABLE');
    COMMIT;
  
    --失败时记录日志
    INSERT INTO GK_BD_LOG
      (BD_LOG_CODE,
       PROC_KSSJ,
       PROC_JSSJ,
       BD_JZSJC,
       CGBZ,
       ERROR_INFO,
       PROC_NAME)
    VALUES
      (F_GETSEQ(30),
       SYSDATE,
       SYSDATE,
       SYSDATE,
       'N',
       V_SQL_STMT,
       'CHANGETABLE');
    COMMIT;
END CHANGETABLE;


根据分号切割（只返回一个字符）
            SELECT NVL(REGEXP_SUBSTR(LB, '[^,]', 1, 2+(i-1)*12, 'i'), 0) INTO LB_SPLIT FROM DUAL;