[SQL] Data Type Conversion, DECODE, CASE, ROWNUM, 논리연산자, 함수

👀 유용한 정보

Hash 함수

💡피드백 Tip

📘 Data Type Conversion

실행 계획 (Execution Plan) 조회

📘 DECODE, CASE

DECODE

CASE

📘 ROWNUM

📘 논리연산자 AND OR NOT

📘 함수

단일행 함수

문자 함수

숫자 함수 (1)

숫자 함수 (2)

📝0403 과제

2. OR 연산시 중복되는 ROW는 어떻게 처리되는지 아래의 SQL을 실행한후 결과를 설명. DEPTNO = 20 OR JOB = 'CLERK' 를 둘다 만족하는 ROW는 2번 출력 되는가 ?

3. 부서별 차등 보너스를 계산하는 SQL 작성

4. DECODE 와 CASE이 차이점 정리후 발표

5. CASE 구문에서 LIKE 연산자 와 IN 연산자를 사용하는 임의의 SQL 작성

6. 실행후 결과 설명

7. 중첩 decode를 사용한 아래의 결과와 동일한 결과를 decode + || (합성연산자)를 사용하여 작성 하십시요

---

👀 유용한 정보

Hash 함수

💡

- Value to Address Hash 함수에 Key 값을 넣으면 그에 해당하는 Address가 출력 된다. 따라서, 데이터에 직접 접근할 수 있으며 검색 속도가 매우 빠르다.

해싱의 특징

• 기억 장소의 낭비가 심하기 때문에 많은 기억 공간 요구

• 검색 방법 중에서 속도가 가장 빠르다

• 삽입, 삭제 작업의 빈도가 많을 때 유리

암호화 해시 특징

• 일방향성 – 암호화된 값을 다시 원래의 값으로 돌려 놓을 수 없음

• 고유성 – 서로 다른 입력 값에 대해 동일한 해시 값이 생성될 확률이 매우 낮음

• 무결성 – 입력 값에 대한 해시 값이 조금이라도 다르면, 원본 값이 다르다는 것을 의미

• 불변성 – 입력 값이 조금이라도 변경되면, 해시 값도 전혀 다른 값

Oracle DISTINCT 연산 - (구버전) Sort vs (10g 이후) Hash

💡피드백 Tip

💡

최종 프로젝트에 Hash 함수 알고리즘을 적용하여 사용자의 회원 정보를 암호화 하는 것 등에 응용하자

💡

추후 Interactive SQL을 통해 SQL을 개발 및 기능, 성능을 검증하고, embeded SQL로 해당 SQL을 넣어 개발한다.

💡

최종 프로젝트에 SQL의 실행 계획 조회 → 쿼리 튜닝을 적용하자

SHOW USER;

---

📘 Data Type Conversion

실행 계획 (Execution Plan) 조회

💡

① SELECT * FROM EMP WHERE SAL = '3000’; ② SQL 선택 > 마우스 오른쪽 버튼 > 설명 > 계획설명 (Explain execution plan)

③[에러 발생시] 권한이 없는 경우 DBA 계정으로 SQL 워크시트 생성

• 도구(T) > SQL 워크시트 > mgrDinkDBMS 클릭

• grant select_any_catalog to scott; ## 권한부여

• 도구(T) > SQL 워크시트 > devDinkDBMS 클릭 ## 신규 session 생성부터 적용

④ ② 재실행 !! 암시적 형변환(Implicit Datatype Conversion)

📘 DECODE, CASE

DECODE

💡

DECODE를 사용하면 코드의 간결과 성능 효율성을 개선할 수 있다.

💡

DECODE

• 조건절( IF ~ ELSE IF ~ELSE ) 연산자

• ‘=‘ 비교 연산자만 사용

• DECODE Syntax Diagram

---

①

SELECT DEPTNO, ENAME,
DECODE(DEPTNO, 10, 'ACCOUNTING', 20, 'RESEARCH', 30, 'SALES', 'ETC')
FROM EMP
ORDER BY DEPTNO;

②

SELECT COMM, DECODE(COMM, NULL, 0, COMM) FROM EMP;

→ DECODE를 활용해 NVL함수 기능 구현

③ - 1

SELECT GREATEST(3000,1500,2100,5000),LEAST(3000,1500,2100,5000) FROM DUAL;

③ - 2

SELECT DEPTNO, ENAME, SAL,
DECODE(GREATEST(SAL,4800),SAL,'HIGH',DECODE(GREATEST(SAL,3000),SAL,'MID','LOW'))
FROM EMP
ORDER BY DEPTNO;

- DECODE에 다시 DECODE와 GREATEST를 사용하면서 범위에 따라 조건 처리를 하는 구문 활용 (DECODE의 NESTED 구조) ‘=‘ 연산자만 사용 가능한 decode를 greatest 함수와 사용하여 범위 연산 수행

CASE

💡

조건절( IF ~ ELSE IF ~ELSE ) 연산자

DECODE 연산자 기능 확장 & 성능 향상

• CASE: ANSI-SQL , DECODE : Oracle SQL

• 기능: 비교 연산자, 논리 연산자 사용, SQL 연산자(ex LIKE , IN )

• 성능: DECODE ~ 함수 , CASE ~ Statement (SQL, PL/SQL)

CASE Syntax Diagram

💡

①

SELECT DEPTNO,ENAME,
CASE DEPTNO WHEN '10' THEN 'ACCOUNTING' -- 암시적(x), 에러 원인? 수정후 실행
            WHEN 20 THEN 'RESEARCH'
            WHEN 30 THEN 'SALES'
            ELSE 'ETC'
            END AS DEPARTMENT
FROM EMP
ORDER BY DEPTNO;

수정

SELECT DEPTNO, ENAME,
CASE DEPTNO WHEN 10 THEN 'ACCOUNTING'
            WHEN 20 THEN 'RESEARCH'
            WHEN 30 THEN 'SALES'
            ELSE 'ETC'
            END AS DEPARTMENT
FROM EMP
ORDER BY DEPTNO;

②

SELECT DEPTNO, ENAME, SAL, -- Searched case
CASE WHEN SAL >= 4800 THEN 'HIGH' -- 비교 연산자
     WHEN SAL BETWEEN 3000 AND 4799 THEN 'MID' -- SQL 연산자
     WHEN SAL >= 1000 AND SAL <=2999 THEN 'LOW' -- 비교 & 논리 연산자
     ELSE 'Passion pay'
     END SAL_GRADE
FROM EMP
ORDER BY DEPTNO;

→ DECODE는 ‘=’ 연산만 가능한 것에 비해 CASE는 범위, 논리 등 자유롭게 연산이 가능하다.

③

SELECT DEPTNO, ENAME, COMM,
CASE WHEN COMM >= 1000 THEN 'Great'
     WHEN COMM >= 500 THEN 'Good'
     WHEN COMM >= 0 THEN 'Bad'
     ELSE 'Dreadful' -- ELSE에서 NULL Catch
END COMM_GRADE
FROM EMP
ORDER BY DEPTNO;

→ COMM 은 NULL을 가진 컬럼이다. NULL은 일반 비교연산자로 비교할 수 없다. → NULL에 대한 처리는 else 구문에서 처리 된다.

④-1

— 작은 값부터 큰 값 조건 비교 → 제대로 처리되지 못한다.

SELECT SAL,
CASE
    WHEN SAL >= 1000 THEN 1
    WHEN SAL >= 2000 THEN 2
    WHEN SAL >= 3000 THEN 3
    WHEN SAL >= 4000 THEN 4
    WHEN SAL >= 5000 THEN 5
    ELSE 0
    END AS SAL_GRADE
FROM EMP
ORDER BY SAL;

④-2

— 큰 값부터 작은 값 조건 비교 → 항상 큰 값부터 작은 값으로 조건 처리 해야 한다.

SELECT SAL, CASE
    WHEN SAL >= 5000 THEN 5
    WHEN SAL >= 4000 THEN 4
    WHEN SAL >= 3000 THEN 3
    WHEN SAL >= 2000 THEN 2
    WHEN SAL >= 1000 THEN 1
    ELSE 0
    END "Sal Grade"
FROM EMP
ORDER BY SAL;

💡

DECODE, CASE를 사용하지 않는다면, Java 코드 내에서 SQL과 If-case문의 처리를 통해 구현해야 한다. Java 코드 내에서 SQL 처리와 Java 조건 처리를 반복하면 불필요한 Overhead (Context Switch) 가 증가 한다. 따라서, 가능한 SQL로 데이터 처리를 하는 것이 좋다!

📘 ROWNUM

💡

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

ROWNUM은 특정 레코드에 부여되는 고유한 번호가 아니다! ROWNUM은 쿼리에 의해 리턴 된 각각의 ROW(Result Set)에 부여된다.

→ ROWNUM은 Pseudo Column (가상의 컬럼) 이다. (조회가 가능하다.)

💡

cf) Pseudo Colum 종류 - ROWNUM - ROWID - Sequence - Nextval - currentval

---

💡

SQL-Developer 메뉴 창 > Configure Window > 세로로분할(V) 후 왼쪽창(① 실행) 오른쪽창(② 실행)하여 ROWNUM이 각 행에 부여되는 고유번호인지 비교

①

SELECT ROWNUM, ENAME, DEPTNO, SAL FROM EMP;

②

SELECT ROWNUM, ENAME, DEPTNO, SAL FROM EMP ORDER BY DEPTNO, SAL;

③

SELECT ROWNUM, ENAME, DEPTNO, SAL FROM EMP WHERE DEPTNO IN (10, 20) ORDER BY DEPTNO, SAL;

• 실행 순서 : WHERE > ROWNUM > ORDER BY

④ SELECT ENAME,DEPTNO,SAL FROM EMP WHERE ROWNUM = 1; // O , 1 Row = 1

⑤ SELECT ENAME,DEPTNO,SAL FROM EMP WHERE ROWNUM = 3; // X

• 1 Row != 3 ➔ 필터링 ➔ 2 Row가 1 Row가 되어 1 Row != 3 비교를 반복

⑥ SELECT ENAME,DEPTNO,SAL FROM EMP WHERE ROWNUM > 3; // X

⑦ SELECT ENAME,DEPTNO,SAL FROM EMP WHERE ROWNUM <= 3; // O

⑧ SELECT ENAME,DEPTNO,SAL FROM EMP WHERE ROWNUM < 3; // O

💡

Result Set의 실행 순서 * Result Set : Where 절에 의해 결과 집합으로 리턴 된 것 1) FROM 2) WHERE 3) ROWNUM 4) Order By * ROWNUM은 Where절에 의해 생성된 결과 집합에 번호를 매긴 것

📘 논리연산자 AND OR NOT

💡

① SELECT ENAME,JOB,SAL,DEPTNO FROM EMP WHERE DEPTNO = 10 AND SAL > 2000;

② SELECT ENAME,JOB,SAL,DEPTNO FROM EMP WHERE DEPTNO = 10 OR SAL > 2000;

③

SELECT ENAME, JOB SAL, DEPTNO FROM EMP WHERE SAL > 2000 OR SAL > 2000;

❗

연산자 우선순위 , 좋은 방식의 SQL? , OPTIMIZER(SQL 최적화기)가 AND 와 OR중 선호하는 연산자는? AND 가 더 우선순위가 높다! (일 양을 줄임)

④ SELECT ENAME,JOB,SAL,DEPTNO FROM EMP WHERE DEPTNO = 10 AND SAL > 2000 OR JOB = 'CLERK';

⑤ SELECT ENAME,JOB,SAL,DEPTNO FROM EMP WHERE (DEPTNO = 10 AND SAL > 2000) OR JOB = 'CLERK';

⑥ SELECT ENAME,JOB,SAL,DEPTNO FROM EMP WHERE DEPTNO = 10 AND (SAL > 2000 OR JOB = 'CLERK');

⑦ SELECT ENAME,JOB,SAL FROM EMP WHERE JOB != 'CLERK';

⑧ SELECT ENAME,JOB,SAL FROM EMP WHERE JOB NOT IN('CLERK','MANAGER');

📘 함수

💡

ORACLE DEFINED FUNCTION ① SINGLE ROW FUNCTION (단일 행 함수) - 문자 함수 - 숫자 함수 - 날짜 함수 - 변환 함수(DATA TYPE CONVERSION) - 기타 함수 ② GROUP ROW FUNCTION (그룹 행 함수)

USER DEFINED FUNCTION (PL/SQL)

→ PL/SQL을 이용해 SQL에서도 사용자 정의 함수를 잘 만들어서 공통 모듈을 만들어 재 사용성을 높일 수 있다.

단일행 함수

문자 함수

💡

① SELECT ENAME, lower(ENAME) ,upper(ENAME), initcap(ENAME) FROM EMP;

② SELECT ENAME, substr(ENAME,1,3), substr(ENAME,4), substr(ENAME,-3,2) FROM EMP;

③ SELECT ENAME, instr(ENAME,'A'), instr(ENAME,'A',2), instr(ENAME,'A',1,2) FROM EMP; // 문자열 내위치

④ SELECT ENAME,rpad(ENAME,10,' '),rpad(ENAME,10), rpad(ENAME,10,''),lpad(ENAME,10,'+') FROM EMP;

SELECT length(rpad('X',1000,'X’)), rpad('X',1000,'X') FROM DUAL; // dummy data

⑤ SELECT ENAME, REPLACE(ENAME,'S','s') FROM EMP;

⑥ SELECT ENAME, concat(ENAME,JOB), ENAME||JOB FROM EMP;

// 앞 뒤 공백문자 없애기 ⑦ SELECT ltrim(' 대한민국 '), rtrim(' 대한민국 '),trim(' ' from ' 대한민국 '), trim('*' from '대한민국') FROM dual;

SELECT trim('장' from '장발장'), ltrim('장발장','장'), rtrim('장발장','장') FROM dual;

// 문자열 길이 리턴 ⑧ SELECT length('abcd'), substr('abcd',2,2), length('대한민국'), substr('대한민국',2,2) FROM dual;

• DBMS 내부 저장시 : 영문자 1 Byte , 한글: 2~3 Bytes 할당 , length: 문자열 함수

• SUBSTR() 사용시 한글이나 영문과 차이가 없다. (같은 문자열로 취급)

⑨ SELECT lengthb('abcd'),lengthb('대한민국'),substr('대한민국',2,2),substrb('대한민국',2,2) FROM dual;

• DBMS 체계가 UNICODE 캐릭터 셋을 사용하므로, 한글은 글자 당 3Bytes를 할당되어있다.

• SUBSTRB는 byte 단위로 처리하므로, ‘대한민국’ 한글을 정상적으로 처리할 수 없다.

⑩ SELECT length('abcd'), vsize('abcd'), length('대한민국'), vsize('대한민국') FROM dual;

• VSIZE : 할당된 Bytes수 리턴

⑪ SELECT ASCII('A’), ASCII('a') FROM dual; -- 65, 97

⑫

SELECT CHR(65), CHR(97) FROM DUAL;

SELECT 'Hellow'||CHR(10)||'World' FROM DUAL; -- Line feed ?? ⓐ run as script ⓑ double-click cell

숫자 함수 (1)

💡

① SELECT round(45.923,2), round(45.923,1), round(45.923,0), round(45.923), round(45.923,-1) FROM dual;

② SELECT trunc(45.923,2), trunc(45.923,1), trunc(45.923,0), trunc(45.923), trunc(45.923,-1) FROM dual;

③ SELECT mod(100,3), mod(100,2) FROM dual;

④ SELECT ENAME,SAL,SAL0.053 as tax, round(SAL0.053,0) as rtax FROM EMP; // 급여의5.3%세금,원단위 반올림

⑤ SELECT CEIL(-45.594),CEIL(-45.294),CEIL(45.294), ROUND(-45.594),ROUND(-45.294),ROUND(45.594) FROM DUAL; // 절대값연산

⑥ SELECT FLOOR(45.245),FLOOR(-45.245),FLOOR(45.545),FLOOR(-45.545) FROM DUAL;

숫자 함수 (2)

💡

① SELECT sign(-999),sign(999) FROM dual;

② SELECT 23,power(2,3), power(-2,3), power(-2,4) FROM dual; -- ??

// PL/SQL ③ variable x number begin :x := 23; end; / print x

---

📝0403 과제

2. OR 연산시 중복되는 ROW는 어떻게 처리되는지 아래의 SQL을 실행한후 결과를 설명. DEPTNO = 20 OR JOB = 'CLERK' 를 둘다 만족하는 ROW는 2번 출력 되는가 ?

SELECT DEPTNO,JOB,ENAME FROM EMP WHERE  DEPTNO = 20;			// 5 Rows
SELECT DEPTNO,JOB,ENAME FROM EMP WHERE  JOB = 'CLERK';			// 4 Rows
SELECT DEPTNO,JOB,ENAME FROM EMP WHERE  DEPTNO = 20  OR JOB = 'CLERK'; // 7 Rows

💡

둘 다 만족하는 행(ROW)는 1번만 출력된다. OR 연산자는 하나가 True이면 전체가 True이다. 또한 어느 하나라도 만족하는 행만 반환 되므로, 중복되는 행은 출력 되지 않는다.

3. 부서별 차등 보너스를 계산하는 SQL 작성

• 10번 부서 급여의 0.3%,20번부서 급여의 20%,30번 부서 급여의 10%,나머지 모든 부서 1%

• 부서 번호, 이름,직무,급여,보너스 출력

• 부서별, 최고 보너스 순서로 정렬

• 소수점 반올림

• 컬럼헤딩 변경시 컬럼 Alias 사용

SELECT DEPTNO, ENAME, JOB, SAL,
ROUND(SAL * DECODE(DEPTNO, 10, 0.003, 20, 0.2, 30, 0.1, 0.01)) AS "보너스"
FROM EMP
ORDER BY DEPTNO, "보너스" DESC;

4. DECODE 와 CASE이 차이점 정리후 발표

💡

Oracle DBMS에서 decode 함수는 조건에 따라 값을 반환하는 데 사용됩니다. 일반적으로 decode 함수는 하나의 값을 비교하고, 일치하는 경우(’=’)에만 해당 값을 반환할 수 있습니다.

조건절( IF ~ ELSE IF ~ELSE ) 연산자

‘=‘ 비교 연산자만 사용

반면에, ANSI/ISO 표준case 함수는decode 연산자의 기능 확장한 것으로 비교, 논리, SQL 연산자를 사용할 수 있습니다.

SELECT CASE
         WHEN age >= 65 THEN 'Senior'
         WHEN age >= 18 THEN 'Adult'
         ELSE 'Child'
       END AS age_group
FROM employees;

5. CASE 구문에서 LIKE 연산자 와 IN 연산자를 사용하는 임의의 SQL 작성

SELECT DEPTNO, ENAME, JOB,
CASE
		WHEN JOB LIKE 'A%' THEN 'A'
		WHEN JOB IN('CLERK', 'SALESMAN') THEN 'B'
        ELSE 'C'
        END AS "JOB_RANK"
FROM EMP;

6. 실행후 결과 설명

SELECT DEPTNO, ENAME, DECODE(DEPTNO,10,'ACCOUNTING',20,'RESEARCH','ETC') FROM EMP ORDER BY DEPTNO;
SELECT DEPTNO, ENAME, DECODE(DEPTNO,10,'ACCOUNTING',20,'RESEARCH') FROM EMP ORDER BY DEPTNO;

💡

첫번 째 DEPTNO가 10, 20이 아닌 다른 값에 대해 'ETC’로 처리된다. 조건문에서 else와 같은 로직이다. 10과 20에 해당하지 않은 나머지는 NULL로 처리된다. 두번 째 DEPTNO가 10, 20이 아닌 다른 값에 대해 NULL을 반환한다.

7. 중첩 decode를 사용한 아래의 결과와 동일한 결과를 decode + || (합성연산자)를 사용하여 작성 하십시요

select deptno,job,decode(deptno, 10 ,decode(job,'CLERK','OK','NO'),'NO’)
from emp
order by deptno,job;

답

SELECT deptno,job,decode(deptno||JOB, '10CLERK' , 'OK', 'NO')
FROM emp
ORDER BY deptno,job;

---

Uploaded by N2T