拆解一个长字段内容，长字段到一个新表

/*========================================================================================
EFFECT: 小程序数据预处理，拆解商品信息字段
TARGET_TABLE:
AUTHOR:GORKOR
DATE:2021-10-8
========================================================================================*/
PROCEDURE DWR_XCX_ORDER_R_P(MSG_NUM OUT NUMBER) IS

V_NAME        VARCHAR2(100);
V_NUM         NUMBER(20);
V_INFORMATION VARCHAR2(100);
V_NUMBE       VARCHAR2(5); --拆解字段
V_NUMBER      NUMBER(1); --程序状态字段

BEGIN
V_OBJECT_NAME := ‘DWAPP.XUNHUAN.DWR_XCX_ORDER_R_P’;
V_NUMBER := 1;

SELECT COUNT(1) INTO V_NUM FROM DWAPP.ODS_XCX_ORDER;

FOR I IN 1 .. V_NUM LOOP

  --获取商品信息
  --OPEN CURINFO FOR
  SELECT S.MERCHANDISE_NEWS
    INTO V_INFORMATION
    FROM DWAPP.ODS_XCX_ORDER S
   WHERE S.ORDER_NUMBER = I;

  --获取销售数量
  SELECT SUBSTR(V_INFORMATION, INSTR(V_INFORMATION, 'X', -1，1) -1) 值
    INTO V_NUMBE
    FROM DUAL;

  --获取宝贝标题名称
  SELECT SUBSTR(V_INFORMATION, 0, INSTR(V_INFORMATION, '颜', 1, 1) - 1)
    INTO V_NAME
    FROM DUAL;

  INSERT INTO DWAPP.ODS_XCX_ORDER_TMP S
    (ORDER_ID, PRODUCT_NAME, PRODUCT_COLOR, PRODUCT_SIZE, QUANTITY)
    SELECT TT.ORDER_ID, V_NAME, NULL, NULL, V_NUMBE
      FROM (SELECT T.ORDER_ID
              FROM DWAPP.ODS_XCX_ORDER T
             WHERE T.ORDER_NUMBER = I) TT;

END LOOP;

COMMIT;

END DWR_XCX_ORDER_R_P;