ตอนที่ 8 : การสร้าง Declare Temp Table - TEMPORARY (Oracle : Stored Procedure)

CREATE GLOBAL TEMPORARY TABLE temp_table (
  NO NUMBER(1), 
  NAME            VARCHAR2(50),
  COUNTRY_NAME    VARCHAR2(50)
)
ON COMMIT DELETE ROWS;

CREATE OR REPLACE PROCEDURE GET_CUSTOMER
AS
BEGIN
     
  -- Insert Table
  INSERT INTO temp_table (NO,NAME,COUNTRY_NAME) VALUES ('1','Weerachai Nukitram','Thailand');
  INSERT INTO temp_table (NO,NAME,COUNTRY_NAME) VALUES ('2','Surachai Sirisart','United Stats');
  INSERT INTO temp_table (NO,NAME,COUNTRY_NAME) VALUES ('3','Adisorn Boonsong','United Kingdom');
  
  -- Loop Table
  FOR CusInfo IN (SELECT * FROM temp_table)
  LOOP
      DBMS_OUTPUT.PUT_LINE(CusInfo.NO || ' ' || CusInfo.NAME || ' ' || CusInfo.COUNTRY_NAME);
  END LOOP;
  
  COMMIT;
               
END;

EXEC GET_CUSTOMER()

CREATE OR REPLACE PROCEDURE GET_CUSTOMER(pCustomer OUT SYS_REFCURSOR)
AS
BEGIN
     
      -- Insert Temp Table
      INSERT INTO temp_table (NO,NAME,COUNTRY_NAME) VALUES ('1','Weerachai Nukitram','Thailand');
      INSERT INTO temp_table (NO,NAME,COUNTRY_NAME) VALUES ('2','Surachai Sirisart','United Stats');
      INSERT INTO temp_table (NO,NAME,COUNTRY_NAME) VALUES ('3','Adisorn Boonsong','United Kingdom');
      
       -- Return  SYS_REFCURSOR
      OPEN pCustomer FOR SELECT * FROM temp_table;

       -- Commit
      COMMIT;
            
END;

VAR  pCustomer REFCURSOR;
EXEC GET_CUSTOMER(:pCustomer)
PRINT pCustomer;