SQL 0613
일반함수 NVL2 : 삼항연산자 NULLIF: 표현식을 비교하여 동일한 경우 NULL, 다를경우 첫번째 표현식 반환 COALESCE: NULL이 아닌 첫번째 표현식을 반환 DECODE: switch~case CACE: if~else Ex34)NVL2로 NULL값처리 SELECT ename,salary,commission,NVL2(commission,salary*12+commission,salary*12) FROM employee;
Ex35)NULLIF 로 NULL값 표현 SELECT NULLIF(‘A’,’A’),NULLIF(‘A’,’B’) FROM dual; Ex36)COALESCE로 NULL값처리 SELECT ename,salary,commission,COALESCE(commission,salary,0) FROM employee ORDER BY job; SELECT COALESCE(null,null,3,2,1) Ex37)DECODE로 부서이름 출력 SELECT ename,dno,DECODE(dno,10,’A’,20,’R’,30,’S’,40,’O’,’DEFAULT’) AS DNAME ORDER BY dno;
Ex38)CASE로 부서이름 SELECT ename,dno,CASE WHEN dno=10 THEN ‘A’ WHEN dno=20 THEN ‘B’ WHEN dno=30 THEN ‘C’ WHEN dno=40 THEN ‘O’ ELSE ‘DEFAULT’ END AS DNAME FROM employee ORDER BY dno;