Oracle - Procedure 함수_NULLIF.COALESCE

[ Oracle - Procedure 함수_NULLIF.COALESCE ]

 

NULLIF와 COALESCE는 CASE 수식의 축약된 형태입니다.

NULLIF는 NVL 함수의 역입니다.

COALESECE는 DECODE 함수와 유사합니다.

 

예: NULLIF)

CREATE OR REPLACE PROCEDURE get_comm_pct (p_comm NUMBER) is

        v_result VARCHAR2(10);  

BEGIN

        v_result := NULLIF(p_comm, 0);

        DBMS_OUTPUT.PUT_LINE ('The commission percent is '|| v_result*100||'%');

END;

 

SQL> Exec get_comm_pct;

 

The commission percent is 20%

PL/SQL procedure successfully completed.

 

예: COALESCE)

CREATE OR REPLACE PROCEDURE get_comm_pct (p_comm NUMBER) is

            v_result number;

BEGIN

    v_result := COALESCE(p_comm, 0);    

    DBMS_OUTPUT.PUT_LINE ('The commission percent is '||v_result*100||'%');

END;

 

SQL> Exec get_comm_pct;

 

The commission percent is 20%

PL/SQL procedure successfully completed.