PLSQL 제어문_CASE문

 

PLSQL 제어문_CASE문

오라클 9i 이후 CASE문을 사용할 수 있는데 IF-THEN-ELSE 가능과 유사 합니다.

SIMPLE CASE문과 Searched CASE문으로 구별할 수 있는데 SIMPLE CASE문은 단일 표현식을 평가하고 그 결과를 일부 값과 비교합니다. SEARCHED CASE문은 여러 부울 식을 평가하고 TRUE로 평가되는 첫 번째 WHEN절의 THEN 이하를 실행합니다.

여러 부울 표현식의 결과를 기반으로 일련의 명령문을 실행하려는 경우 SEARCHED CASE 문을 사용하고 하나의  결과를 기반으로 일련의 명령문을 실행하려는 경우 SIMPLE CASE 문을 사용합니다.

 아래 기본형식을 보면서 이해해 봅시다.

<Simple CASE문 기본형식>

CASE selector      WHEN condition1 THEN result1    WHEN condition2 THEN result2    ...    WHEN conditionN THEN  resultN    ELSE result END CASE; selector : 한번 평가되는 표현식으로 WHEN절에서 condition 값과 비교 됩니다.  WHEN condition THEN result : selector 값이 condition과 같을 때 THEN 이하가 실행 됩니다. ELSE result : selector와 일치하는 WHEN 조건이 없을 때 실행됩니다. END : CASE문의 종료를 표시 합니다. 조건이 만족할 때 아무것도 하지말라는 의미를 부여하려면 result 자리에 NULL 이라고 기술하면 됩니다.

<Searched  CASE문 기본형식>

CASE      WHEN condition1 THEN result1    WHEN condition2 THEN result2    ...    WHEN conditionN THEN  resultN    [ELSE result] END CASE; WHEN 절의 조건은 위에서 아래로 순서대로 평가됩니다. 조건이 TRUE로 평가되는 WHEN 절과 연관된 일련의 명령문이 실행되고 둘 이상의 조건이 TRUE로 평가되면 첫 번째 조건 만 실행됩니다. TRUE로 평가되는 조건이 없으면 ELSE 절의 [ELSE result]가 실행됩니다. ELSE 절을 생략되고 TRUE식이 없으면 CASE_NOT_FOUND 예외가 발생합니다. 조건이 만족할 때 아무것도 하지말라는 의미를 부여하려면 result 자리에 NULL 이라고 기술하면 됩니다.

15.1.3.2.1 부서코드값을 비교하는 예제 실습 : SIMPLE CASE

익명코드 블록으로 EMP테이블에서 부서코드(deptno) 컬럼 값을 SIMPLE CASE문으로 비교하는 예제 입니다. 

EMP 테이블에서 사번이 7369인 사원의 부서코드(deptno)가 10이면 accounting, 20이면  research, 30이면 sales를 출력하세요.

SET SERVEROUTPUT ON; DECLARE    -- EMP 테이블의 DEPTNO 컬럼과 타입이 같은 V_DEPTNO 변수를 선언 합니다.    V_DEPTNO EMP.DEPTNO%TYPE; BEGIN    -- BEGIN내의 SELECT문은 한건만 SELECT되야 하고 INTO절에서 받아줘야 합니다.    SELECT  DEPTNO INTO V_DEPTNO    FROM    EMP    WHERE   EMPNO = 7369;        CASE V_DEPTNO        WHEN 10 THEN           DBMS_OUTPUT.PUT_LINE('accounting...' || V_DEPTNO);        WHEN 20 THEN ❶           DBMS_OUTPUT.PUT_LINE('research...' || V_DEPTNO);        WHEN 30 THEN           DBMS_OUTPUT.PUT_LINE('sales...' || V_DEPTNO);    END CASE; END;

<실행결과>

research...20

❶ 7369 SMITH 직원의 부서코드는 20 입니다. 그래서 WHEN 20 THEN이 실행되어 결과가 나왔습니다.

15.1.3.2.2 급여값을 비교하는 예제 실습 : SEARCHED CASE

익명코드 블록으로 EMP테이블에서 급여(sal) 컬럼 값을 SEARCHED CASE문으로 비교하는 예제 입니다. 

EMP 테이블에서 사번이 7369인 사원의 급여(sal)가 3000보다 크면 'high sal' 2000보다 크면 'mid sal' 그 밖에는 'low sal'을 출력하세요.

SET SERVEROUTPUT ON; DECLARE    -- EMP 테이블의 SAL 컬럼과 타입이 같은 V_SAL 변수를 선언 합니다.    V_SAL EMP.SAL%TYPE; BEGIN    -- BEGIN내의 SELECT문은 한건만 SELECT되야 하고 INTO절에서 받아줘야 합니다.    SELECT  SAL INTO V_SAL    FROM     EMP    WHERE  EMPNO = 7369;        CASE         WHEN V_SAL >= 3000 THEN           DBMS_OUTPUT.PUT_LINE('high sal...' || V_SAL);        WHEN V_SAL >= 2000 THEN           DBMS_OUTPUT.PUT_LINE('mid sal...' || V_SAL);        ELSE ❶           DBMS_OUTPUT.PUT_LINE('low sal...' || V_SAL);    END CASE; END;

<실행결과>

low sal...800

❶ 7369 SMITH 직원의 급여는 800 입니다. 그래서 ELSE 이하가 실행되어 결과가 나왔습니다.

오라클, SQL, 스칼라서브쿼리, Scalar SubQuery

오라클, SQL, 스칼라서브쿼리, Scalar SubQuery

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=616

 

오라클, SQL, 스칼라서브쿼리, Scalar SubQuery

오라클, SQL, 스칼라서브쿼리, Scalar SubQuery5.4 Scalar SubQuery 하나의 단일값을 나타내기 위해 SELECT구문을 SELECT LIST, WHERE절, ORDER BY절, DML등에 사용하는 서브쿼리를 지칭하며 데이터건수가 적을 경우 조인 방식보다 유리하다. 유효한 수식이 사용될 수 있는 곳이라면 어디든 사용가능하며 반드시 하…

ojc.asia

5.4 Scalar SubQuery

하나의 단일값을 나타내기 위해 SELECT구문을 SELECT LIST, WHERE절, ORDER BY절, DML등에 사용하는 서브쿼리를 지칭하며 데이터건수가 적을 경우 조인 방식보다 유리하다. 유효한 수식이 사용될 수 있는 곳이라면 어디든 사용가능하며 반드시 하나의 결과만 되돌려야 한다.

--사원테이블과 부서테이블을 조인하여 사번, 사원명, 부서코드, 부서명을 추출한다고 하자. SQL> select empno, ename, emp.deptno, dname from emp, dept 2 where emp.deptno = dept.deptno; EMPNO ENAME DEPTNO DNAME ---------- ---------- ------ -------------- 7782 CLARK 10 ACCOUNTING …… 7654 MARTIN 30 SALES 14 개의 행이 선택되었습니다. -- 스칼라 서브쿼리로 바꾸면 SQL> select empno, ename, deptno, (select dname from dept where emp.deptno = dept.deptno) 2 from emp; EMPNO ENAME DEPTNO (SELECTDNAMEFR ---------- ---------- ------ -------------- 7369 SMITH 20 RESEARCH …… 7934 MILLER 10 ACCOUNTING 14 개의 행이 선택되었습니다. -- 부서별로 사원수를 출력하는 쿼리를 스칼라 서브쿼리를 이용해 보자. SQL> select deptno, dname, 2 (select nvl(count(*),0) from emp where emp.deptno = dept.deptno) emp_cnt 3 from dept ; DEPTNO DNAME EMP_CNT ------ -------------- ---------- 10 ACCOUNTING 3 20 RESEARCH 5 30 SALES 6 40 OPERATIONS 0 -- insert 구문에 스칼라 서브쿼리를 사용해보자. SQL> create table emp_summary ( 2 sum_sal number, 3 avg_sal number, 4 max_sal number, 5 min_sal number 6 ); 테이블이 생성되었습니다. SQL> insert into emp_summary ( 2 sum_sal, 3 avg_sal, 4 max_sal, 5 min_sal ) 6 values ( 7 (select sum(sal) from emp), 8 (select avg(sal) from emp), 9 (select max(sal) from emp), 10 (select min(sal) from emp) 11 ); 1 개의 행이 만들어졌습니다. SQL> commit; -- 아래처럼 order by절에서도 사용 가능하다. EMP테이블에서 부서이름으로 정렬 SQL> select empno, ename, sal 2 from emp e 3 order by ( select dname from dept d 4 where e.deptno = d.deptno); EMPNO ENAME SAL ---------- ---------- ---------- 7782 CLARK 2450 7839 KING 5000 7934 MILLER 1300 7566 JONES 2975 ……