-- 1. 테스트용 함수 생성
----------------------------------------------------------------
CREATE OR REPLACE FUNCTION FNC_TEST RETURN VARCHAR2
IS
    P_RTN VARCHAR2(1000);
BEGIN

    DBMS_OUTPUT.PUT_LINE('호출');

    SELECT SYS_GUID()
    INTO P_RTN
    FROM DUAL;
    
    RETURN P_RTN;
    
    EXCEPTION
        WHEN OTHERS THEN
            RETURN NULL;

END;



-- 2. 나쁜 쿼리 (매번호출)
----------------------------------------------------------------
SELECT  T.ID
,       T.ID
,       T.ID
FROM (SELECT FNC_TEST() AS ID
      FROM DUAL) T;


-- DBMS 출력
호출
호출
호출


-- 스크립트 출력
ID                                  ID                                  ID
--------------------------------    --------------------------------    --------------------------------
CB7F1CCA4EF941A1BABAD9CDBAC5CBEE    5A0FB87B0B354A589D52BCDF1A59A4A3    D6E5F959D6114C179FFF7CD746DB3B3C





-- 3. 착한 쿼리 (한번호출)
----------------------------------------------------------------
SELECT  T.ID
,       T.ID
,       T.ID
FROM (SELECT (SELECT FNC_TEST() FROM DUAL) AS ID
      FROM DUAL) T;


-- DBMS 출력
호출


-- 스크립트 출력
ID                                  ID                                  ID
--------------------------------    --------------------------------    --------------------------------
BB30D177761549039C4B780030A0ECB3    BB30D177761549039C4B780030A0ECB3    BB30D177761549039C4B780030A0ECB3



-- 이렇게 해도 한번만 호출된다....왜? ROWNUM으로 발번한 중간집합을 어떤식으로든 가지고 있어야 하기 때문이다.
-- 즉, 중간집합을 가지도록 할 수 있는 방법을 유도한다면 다른 식으로도 얼마든지 가능할 것이다.
SELECT  T.ID
,       T.ID
,       T.ID
FROM (SELECT ROWNUM, FNC_TEST() AS ID
      FROM DUAL) T;




-- 4. 캐시
----------------------------------------------------------------
CREATE OR REPLACE FUNCTION FNC_TEST(P_CST_ID 고객정보.CST_ID%TYPE) RETURN VARCHAR2
IS
    P_RTN VARCHAR2(1000);
BEGIN

    DBMS_OUTPUT.PUT_LINE('CALL');
    
    SELECT CST_NM 
    INTO P_RTN
    FROM 고객정보
    WHERE CST_ID = P_CST_ID;
    
    RETURN P_RTN;
    
    EXCEPTION
        WHEN OTHERS THEN
            RETURN NULL;
END;




WITH TBL
AS
(   SELECT 'ADMIN'      AS CST_ID FROM DUAL UNION ALL
    SELECT 'CS123456'   AS CST_ID FROM DUAL UNION ALL
    SELECT 'ADMIN'      AS CST_ID FROM DUAL UNION ALL
    SELECT 'CS123456'   AS CST_ID FROM DUAL UNION ALL
    SELECT 'ADMIN'      AS CST_ID FROM DUAL UNION ALL
    SELECT 'CS765432'   AS CST_ID FROM DUAL UNION ALL
    SELECT 'CS123456'   AS CST_ID FROM DUAL
)
SELECT  CST_ID
,       (SELECT FNC_TEST(CST_ID) FROM DUAL) AS CST_NM
FROM TBL;


USR_ID                                             USR_NM                                            
-------------------------------------------------- --------------------------------------------------
ADMIN                                              ADMINISTRATOR                                     
CS123456                                           정모씨                                            
ADMIN                                              ADMINISTRATOR                                     
CS123456                                           정모씨                                            
ADMIN                                              ADMINISTRATOR                                     
CS765432                                           김모씨
CS123456                                           정모씨                                            



CALL
CALL
CALL