it개발자스터디공간

SQL 기초 및 PLSQL 실무 강좌 자료] 실무에서 서브쿼리 사용시 주의사항

SQL 기초 및 PLSQL 실무 강좌 자료] 실무에서 서브쿼리 사용시 주의사항

실무개발자를위한 실무교육 전문교육센터학원
www.oraclejava.co.kr에 오시면 보다 다양한 강좌를 보실 수 있습니다.

실무에서 서브쿼리 사용시 주의사항

- 서브쿼리의 결과가 NULL인 경우

- 사용 예
: 2002년에 입사한 교수의 평균 급여보다 많은 급여를 받는 교수의 교수 번호, 이름, 급여를 출력하여라.

​

​

- 실습​

​

: Blake와 같은 부서에 있는 모든 사원에 대해서 사원 이름과 입사일을 디스플레이하라.

​

​

: 평균 급여 이상을 받는 모든 사원에 대해서 사원 번호와 이름을 디스플레이하는 질의문을 생성하라. 단 출력은 급여 내림차순 정렬하라.
​

​

: 부서 번호와 급여가 보너스를 받는 어떤 사원의 부서 번호와 급여에 일치하는 사원의 이름,

부서 번호 그리고 급여를 디스플레이하라.
​

SQL 기초 및 PLSQL 실무 강좌 자료] 실무에서 서브쿼리 사용시 주의사항

SQL 기초 및 PLSQL 실무 강좌 자료] 실무에서 서브쿼리 사용시 주의사항

실무개발자를위한 실무교육 전문교육센터학원
www.oraclejava.co.kr에 오시면 보다 다양한 강좌를 보실 수 있습니다.

실무에서 서브쿼리 사용시 주의사항

- 메인쿼리와 서브쿼리 칼럼의 수가 일치하지 않는 경우
​

- 사용 예
: 101번 학과 교수 중에서 최소 급여를 받는 교수의 이름, 직급, 급여를 출력하여라.
 

- ORDER BY 절 사용
: 서브쿼리 내에서 ORDER BY 절 사용하면 오류 발생

​

SQL 기초 및 PLSQL 실무 강좌 자료] 실무에서 서브쿼리 사용시 주의사항

SQL 기초 및 PLSQL 실무 강좌 자료] 실무에서 서브쿼리 사용시 주의사항

실무개발자를위한 실무교육 전문교육센터학원
www.oraclejava.co.kr에 오시면 보다 다양한 강좌를 보실 수 있습니다

실무에서 서브쿼리 사용시 주의사항

* 단일행 서브쿼리에서 오류가 발생하는 경우
​

- 복수 행 값을 반환하는 서브쿼리와 단일행 비교연산자 함께 사용하는 경우
- 반환되는 칼럼의 수와 메인쿼리에서 비교되는 칼럼 수가 일치하지 않는 경우
- 복수행을 출력하는 서브쿼리와 ‘=‘단일행 연산자로 비교하는 경우

​

​
 

SQL 기초 및 PLSQL 실무 강좌 자료] DECODE 사용시 주의사항

SQL 기초 및 PLSQL 실무 강좌 자료] DECODE 사용시 주의사항

실무개발자를위한 실무교육 전문교육센터학원
www.oraclejava.co.kr에 오시면 보다 다양한 강좌를 보실 수 있습니다.

DECODE 사용시 주의사항

​

* 펼칠 컬럼이 너무 많을때는 먼저 GROUP BY 한 후 처리하라.
  

▶   select dept_no,
         sum(decode(substr(wk_dt,7,2),’01’,amt)) d1,
         sum(decode(substr(wk_dt,7,2),’02’,amt)) d2,
         sum(decode(substr(wk_dt,7,2),’03’,amt)) d3,
         sum(decode(substr(wk_dt,7,2),’04’,amt)) d2,
         ,,,
         sum(decode(substr(wk_dt,7,2),’31’,amt)) d31

▶   select dept_no,
         sum(decode(dd,’01’,amt)) d1,
         sum(decode(dd,’02’,amt)) d2,
         sum(decode(dd,’03’,amt)) d3,
         sum(decode(dd,’04’,amt)) d4, 
         ,,,,
         sum(decode(dd,’31’,amt)) d31
     from ( select dept_no, substr(wk_dt,7,2) dd, sum(amt) amt
            from tab1
            where wk_dt like ‘200401%’
          )
     group by dept_no

▶ COUNT, SUM의 비교
      ① sum(decode(col1,’A’,1,0))
      ② sum(decode(col1,’A’,1))
      ③ count(decode(col1,’A’,1,0))
      ④ count(decode(col1,’A’,1))
      ⑤ count(decode(col1,’A’,’C’))

 ①, ②, ④, ⑤ 는 동일하다.

 ③ 의 결과는 count(*) 과 동일하다.

 ①은 ②보다 불리하다(불필요한 0 연산)

 ②는 ④, ⑤ 보다 불리

 ④, ⑤ 는 동일하다.

 SUM은 COUNT에 비해 30~50% 불리함

 COUNT는 not null 인 경우에만 처리

 COUNT를 사용할 수 있다면 SUM을 사용하지 마라.

* DECODE

□ DECODE 함수와 CASE 문

​

▷ DECODE 함수 내부에서 DECODE문을 사용하는 경우에는 성능저하 발생
▷ UNION ALL를 사용할 경우 동일 테이블에 대한 인덱스 스캔이나 테이블 스캔 반복발생으로 성능저하
▷ Concat(‘||’) 함수로 비교대상컬럼를 묶어서 DECODE 함수의 중복사용을 줄이고, CASE 문으로 비교하는 방식 추천

    (INDEX 주의)
​

​
 

SQL 기초 및 PLSQL 실무 강좌 자료] DECODE 사용시 주의사항

SQL 기초 및 PLSQL 실무 강좌 자료] DECODE 사용시 주의사항

실무개발자를위한 실무교육 전문교육센터학원
www.oraclejava.co.kr에 오시면 보다 다양한 강좌를 보실 수 있습니다.

DECODE 사용시 주의사항

* ELSE 없는 IF를 사용하라.
        select sum(decode(col1, 1, qty, 0)),…..
     → select sum(decode(col1, 1, qty)),…..

* 가능한 그룹함수내에서 nvl을 사용하지 마라
       select sum(decode(col1, 1, nvl(qty,0))),…  
   →  select nvl(sum(decode(col1, 1,qty)),0),…

* 가능한 반복해서 DECODE를 사용하지 마라
    
- 컬럼결합법
       select sum(decode(market,'D',
                      decode(type,'1',
                           decode(unit,'A',0.2 * col,
                                       'B',0.5 * col,…

    → select sum(decode(market || type || unit, 'D1A', 0.2,
                                                 'D1B', 0.5,…)) * col

- case문으로 대체 1

​- case문으로 대체 2

​

SQL 기초 및 PLSQL 실무 강좌 자료] 일반 함수 CASE 함수

SQL 기초 및 PLSQL 실무 강좌 자료] 일반 함수 CASE 함수

실무개발자를위한 실무교육 전문교육센터학원
www.oraclejava.co.kr에 오시면 보다 다양한 강좌를 보실 수 있습니다.

일반 함수 CASE 함수

* CASE 함수

- CASE 함수는 DECODE 함수의 기능을 확장한 함수

- DECODE 함수는 표현식 또는 칼럼 값이 ‘=‘ 비교를 통해 조건과 일치하는 경우에만 다른 값으로 대치할 수 있지만,

CASE 함수에서는 산술 연산, 관계 연산, 논리 연산과 같은 다양한 비교가 가능

- 또한 WHEN 절에서 표현식을 다양하게 정의

- 8.1.7에서부터 지원되었으며, 9i에서 SQL, PL/SQL에서 완벽히 지원

- DECODE 함수에 비해 직관적인 문법체계와 다양한 비교 표현식 사용

* 사용법

​

* 사용 예
​

​- 교수 테이블에서 소속 학과에 따라 보너스를 다르게 계산하여 출력하여라. 학과 번호별로 보너스는 다음과 같이 계산한다. 학과 번호가 101이면 보너스는 급여의 10%, 102이면 20%, 201이면 30%, 나머지 학과는 0%이다.

​

​
​
 

SQL 기초 및 PLSQL 실무 강좌 자료] 일반 함수 DECODE 함수

SQL 기초 및 PLSQL 실무 강좌 자료] 일반 함수 DECODE 함수

실무개발자를위한 실무교육 전문교육센터학원
www.oraclejava.co.kr에 오시면 보다 다양한 강좌를 보실 수 있습니다.

일반 함수 DECODE 함수

* DECODE 함수

- DECODE 함수는 기존 프로그래밍 언어에서 IF문이나 CASE 문으로 표현되는 복잡한 알고리즘을 하나의

SQL 명령문으로 간단하게 표현할 수 있는 유용한 기능

- DECODE 함수에서 비교 연산자는 ‘=‘만 가능

* 사용법

- 표현식 또는 칼럼의 값이 SEARCH1 값과 일치하면 RESULT1값을 반환, SEARCH2값과 일치하면 RESULT2 값 반환

- 일치하는 값이 없거나 NULL인 경우에는 기본값 반환

- 기본값이 없는 경우에는 NULL 반환

* DECODE 함수의 개념을 도식적으로 표현한 것이다.

​

* 사용 예
​

- 교수 테이블에서 교수의 소속 학과 번호를 학과 이름으로 변환하여 출력하여라. 학과 번호가 101이면 ‘컴퓨터공학과’, 102이면 ‘멀티미디어학과’, 201이면 ‘전자공학과’, 나머지 학과 번호는 ‘기계공학과’(default)로 변환한다.

​

​
​
 

SQL 기초 및 PLSQL 실무 강좌 자료] 일반 함수 NVL 확장 함수 : COALESCE 함수

SQL 기초 및 PLSQL 실무 강좌 자료] 일반 함수 NVL 확장 함수 : COALESCE 함수

실무개발자를위한 실무교육 전문교육센터학원
www.oraclejava.co.kr에 오시면 보다 다양한 강좌를 보실 수 있습니다.

일반 함수 NVL 확장 함수 : COALESCE 함수

* COALESCE 함수

- COALESCE 함수는 인수중에서  NULL이 아닌 첫 번째 인수를 반환하는 함수

* 사용법

- expression-1 : expression-1이 NULL이 아니면 expression-1을 반환

- expression-2 : expression-1이 NULL이고 expression-2가 NULL이 아니면 expression-2를 반환

- expression-n : expression-1부터 expression-n-1까지의 값이 NULL이고 expression-n이 NULL이

아니면 expression-n을 반환

* 사용 예

- 교수 테이블에서 보직수당이 NULL이 아니면 보직수당을 출력하고, 보직수당이 NULL이고 급여가 NULL이 아니면

급여를 출력, 보직수당과 급여가 NULL이면 0을 출력하여라