PL/SQL迴圈及暫存資料表

莫名其妙被扯入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;