oracle - How should I temporarily store data within a PL/SQL procedure? -

i new pl/sql. have data in initial table, named 'flex_panel_inspections' attempting summarise in second table, named 'panel_status_2' using pl/sql procedure. however, due nature of data, have had write case statement in order correctly summarise data flex_panel_inspections. have therefore created third, intermediate table bridge 2 (named 'panel_status_1') since case statement not allow columns in group clause order data (to extent of knowledge - error when try , this). not want storing data in intermediate table - there way can either make temporary (i.e. exist while procedure runs data 'panel_status_1' not retained); create view within procedure, or remove need intermediate table altogether?

any or criticism of mistakes / misunderstanding of pl/sql appreciated. here code have written:

create or replace procedure panel_status_procedure (panel_lot_id in number)  begin  --populate intermediate table information status of panels. insert panel_status_1 (flex_lot_id, flex_panel_dmx, flex_panel_status)    select flex_lot_id, flex_panel_dmx,  --sum status values of 4 panel inspections. panel passes if , if sum = 4.  case sum (flex_panel_status)     when 4 1     else 0  end new_panel_status  flex_panel_inspections flex_lot_id = panel_lot_id group flex_lot_id, flex_panel_dmx;  --add information machine id , upload time table. insert panel_status_2 (flex_lot_id, flex_panel_dmx, flex_panel_status, machine_id, upload_time) select distinct panel_status_1.*, machine_id, upload_time panel_status_1, flex_panel_inspections  (flex_panel_inspections.flex_lot_id = panel_status_1.flex_lot_id        , flex_panel_inspections.flex_panel_dmx = panel_status_1.flex_panel_dmx)  , flex_panel_inspections.flex_lot_id = panel_lot_id;  end panel_status_procedure; / 

you can create temp table as

create global temporary table gtt_panel_status ( column datatype ... ) on commit [delete|preserve] rows; 

(specifying either delete or preserve in on commit clause).

however don't need temp table. might try with clause (cte), or else inline view along lines of select x, y, z (select subquery here).

edit: looking @ query more, think need analytic sum, i.e. total without aggregating. example, this:

create or replace procedure panel_status_procedure     ( panel_lot_id in number ) begin     -- add information machine id , upload time table.     insert panel_status_2          ( flex_lot_id          , flex_panel_dmx          , flex_panel_status          , machine_id          , upload_time )     select distinct            flex_lot_id          , flex_panel_dmx          , case sum(flex_panel_status) on (partition flex_lot_id, flex_panel_dmx)                when 4 1                else 0            end          , machine_id          , upload_time       flex_panel_inspections pi      pi.flex_lot_id = panel_lot_id;  end panel_status_procedure;