/*========================================================================================
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;