莫名其妙被扯入Oracle EBS系統，被迫學習PL/SQL語法；
以下為建立暫存資料表及利用迴圈取得相對應子項目資料再一起寫入暫存的筆記：
//建立暫存資料表
create GLOBAL
TEMPORARY TABLE temp_wales (
DESCRIPTION
VARCHAR2(80 BYTE),
ASSET_NUMBER
VARCHAR2(15 BYTE),
DATE_PLACED_IN_SERVICE DATE,
LIFE_IN_MONTHS
NUMBER(4,0)
)
ON COMMIT PRESERVE
ROWS;
//由A取得ID後再去比對B資料表中的Parent_ID
BEGIN
FOR rec
IN (SELECT ID,DESCRIPTION,ASSET_NUMBER,DATE_PLACED_IN_SERVICE,LIFE_IN_MONTHS
FROM A )
//開始迴圈
LOOP
//先寫入父資料的記錄到暫存裡
INSERT INTO temp_wales( ASSET_NUMBER,DESCRIPTION,DATE_PLACED_IN_SERVICE,
LIFE_IN_MONTHS) values(
rec.ASSET_NUMBER,rec.DESCRIPTION,rec.DATE_PLACED_IN_SERVICE,rec.LIFE_IN_MONTHS);
//再寫入子資料的記錄到暫存裡
INSERT INTO temp_wales(
ASSET_NUMBER,DESCRIPTION,DATE_PLACED_IN_SERVICE, LIFE_IN_MONTHS) SELECT
ASSET_NUMBER,'改良',rec.DATE_PLACED_IN_SERVICE,rec.LIFE_IN_MONTHS FROM B
WHERE Parent_ID= rec.ID;
//結束迴圈
END LOOP;
END;
//撈撈看是否有資料了
select * from temp_wales;
