[ORACL PLSQL] RETURNING INTO 사용하기

INSERT, UPDATE, DELETE 수행된 ROW를 반환해서 변수에 저장합니다.

 

CREATE OR REPLACE SP_COMPANY_CUD (
    IN_COMPANY_NAME IN     VARCHAR2
    RETURN_CODE     IN OUT VARCHAR2
) IS

V_COMPANY_ID    VARCHAR2(10);
V_COMPANY_NAME  VARCHAR2(10);
V_COMPANY_IDS   DBMS_SQL.NUMBER_TABLE;
-- V_COMPANY_IDS   DBMS_SQL.VARCHAR2_TABLE;

BEGIN
  
    -- 추가
    INSERT 
      INTO COMPANY_TABLE (COMPANY_ID, COMPANY_NAME)
    VALUES (10, 'GOOGLE')
 RETURNING COMAPANY_ID, COMPANY_NAME
      INTO V_COMAPANY_ID, V_COMPANY_NAME;
     
    DBMS_OUTPUT.PUT_LINE('추가된 DATA = ' || V_COMAPANY_ID || ' - ' || V_COMPANY_NAME);
    -- 추가된 DATA = 10 - GOOGLE
    
    -- 수정
    UPDATE COMPANY_TABLE
       SET COMPANY_NAME = 'APPLE'
     WHERE COMPANY_ID = V_COMAPANY_ID
 RETURNING COMPANY_NAME
      INTO V_COMPANY_NAME;
     
    DBMS_OUTPUT.PUT_LINE('수정된 DATA = ' || V_COMPANY_NAME || ' - ' || V_COMPANY_NAME);
    -- 수정된 DATA = 10 - APPLE
    
    
    -- 삭제
    DELETE COMPANY_TABLE
     WHERE COMPANY_ID = V_COMAPANY_ID
 RETURNING COMPANY_NAME
      INTO V_COMPANY_NAME;
     
    DBMS_OUTPUT.PUT_LINE('삭제된 DATA = ' || V_COMPANY_NAME || ' - ' || V_COMPANY_NAME);
    -- 삭제된 DATA = 10 - APPLE
    
    
    -- 삭제 - RETURNING BULK COLLECT INTO
    DELETE COMPANY_TABLE
 RETURNING COMPANY_NAME
      BULK COLLECT INTO V_COMPANY_IDS;
     
    FOR i IN 1 .. V_COMPANY_IDS.COUNT 
    LOOP 
        DBMS_OUTPUT.PUT_LINE('삭제된 DATA = ' || V_COMPANY_IDS(i));
    END LOOP;
    -- 삭제된 DATA = 10
    -- 삭제된 DATA = 20
    -- 삭제된 DATA = 30
    
    COMMIT;   -- ROLLBACK;
END;