Oracle 存储过程 创建Type以及Array

 

一、创建表

BEGIN  
   DROP TABLE PlacementTrace;  
EXCEPTION
  WHEN OTHERS THEN  
    IF sqlcode != -0942 THEN RAISE; END IF;  
END;  
 
CREATE TABLE PlacementTrace(
       reelID VARCHAR2(32),
       PCBID VARCHAR2(32),
       onTime DATE,
       offTime DATE 
);

二、创建TYPE

CREATE OR REPLACE TYPE PlacementTrace_TYPE FORCE AS OBJECT
(
       reelID VARCHAR2(32),
       PCBID VARCHAR2(32),
       onTime DATE,
       offTime DATE 
);

三、创建ARRAY

CREATE OR REPLACE TYPE PlacementTrace_ARR AS TABLE OF PlacementTrace_TYPE;

四、添加数据

SELECT EXTRACTVALUE(VALUE(xml_body), '/message/header/location/@factoryName') AS plant_code,
       EXTRACTVALUE(VALUE(xml_body), '/message/body/panel/@productSide')      AS mount_side,
       EXTRACTVALUE(VALUE(xml_body), '/message/header/location/@machineName') AS machine_name,
       EXTRACTVALUE(VALUE(xml_body), '/message/header/location/@laneNo')      AS lane_no,
       EXTRACTVALUE(VALUE(xml_body), '/message/body/panel/@pcbID')            AS boardnumber,
       EXTRACTVALUE(VALUE(xml_body), '/message/body/panel/@pcbID')            AS wip_sn,
       EXTRACTVALUE(VALUE(xml_body), '/message/body/panel/@productName')      AS program_name,
       EXTRACTVALUE(VALUE(xml_body), '/message/body/panel/@startTime')        AS create_date,
       EXTRACTVALUE(VALUE(xml_body), '/message/header/location/@machineMode') AS line_mode,
       EXTRACTVALUE(VALUE(xml_body), '/message/header/location/@jobStatus')   AS job_status,
       EXTRACTVALUE(VALUE(xml_body), '/message/header/location/@vendorType')  AS vendor_type,
       EXTRACTVALUE(VALUE(xml_body), '/message/header/location/@lastMachine') AS last_machine,
       EXTRACTVALUE(VALUE(xml_body), '/message/body/stageNo/@output')         AS stage_no,
       EXTRACTVALUE(VALUE(xml_body), '/message/body/panel/@startTime')        AS entry_time,
       EXTRACTVALUE(VALUE(xml_body), '/message/body/panel/@endTime')          AS release_time,
       EXTRACTVALUE(VALUE(xml_body), '/message/body/panel/@modelStr')         AS model_name
    BULK COLLECT
INTO l_header_data_list
FROM TABLE (XMLSEQUENCE(EXTRACT(l_xml_source, '/message'))) xml_body;

 

五、从一个数组逐条添加到另一个数组

IF (l_part_data_list.COUNT > 0) THEN
    FOR i IN l_part_data_list.FIRST..l_part_data_list.LAST LOOP
        --l_part_data_list_final.COUNT 从0开始计算
        l_part_data_list_final(l_part_data_list_final.COUNT) := l_part_data_list(i);
        l_part_data_list_final(l_part_data_list_final.COUNT - 1).location_no := l_part_data_list_final(l_part_data_list_final.COUNT - 1).location_no || '/';
    END LOOP;
END IF;