拆解一个长字段内容，长字段到一个新表(不加序列方式)

最新推荐文章于 2024-05-27 14:30:33 发布

原创最新推荐文章于 2024-05-27 14:30:33 发布 · 132 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

该博客详细介绍了如何在小程序环境中进行数据预处理，特别是针对商品信息字段的拆解。通过游标和字符串处理函数，提取销售数量和宝贝名称，并将这些信息插入到临时表中，以供后续分析使用。

/*========================================================================================
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; --程序状态字段
V_ORDER_ID    NUMBER;

CURSOR CEMP IS
  SELECT S.MERCHANDISE_NEWS,S.ORDER_ID FROM DWAPP.ODS_XCX_ORDER S;

BEGIN
V_OBJECT_NAME := ‘DWAPP.PRODUCT_TEST.DWR_XCX_ORDER_R_P’;
V_NUMBER := 1;
OPEN CEMP;
LOOP
FETCH CEMP
INTO V_INFORMATION,V_ORDER_ID;
exit when CEMP%notfound;
dbms_output.put_line(V_INFORMATION);
–获取销售数量
SELECT SUBSTR(V_INFORMATION, INSTR(V_INFORMATION, ‘X’, -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 V_ORDER_ID, V_NAME, NULL, NULL, V_NUMBE
      FROM DUAL;  
              
END LOOP;

  COMMIT;
  
CLOSE CEMP;