[SQLD 요약] II. SQL 기본 및 활용 - 01. SQL 기본 (5) - DECODE, CASE, ROWNUM, ROWID, WITH

 

 

SQLD 요약 / SQL Developer 요약

🔑 명시적형변환/ 암시적형변환/ 형변환함수/ 내장형함수/ DUAL테이블/ 문자열함수/ 숫자형함수/ 날짜형함수/ 

       DECODE/ CASE문/ ROWNUM/ 인라인뷰/ ROWID/ WITH구문/ 서브쿼리

 

 

II. SQL 기본 및 활용

   01. SQL 기본

          [1] 관계형 데이터베이스

          [2] SQL 종류

          [3] DDL (Data Definition Language)

          [4] DML (Data Manipulation Language)

          [5] WHERE문 사용

          [6] GROUP 연산

          [7] SELECT문 실행 순서

          [8] 명시적 형변환과 암시적 형변환

          [9] 내장형 함수 (BUILT-IN Function)

          [10] DECODE와 CASE문

          [11] ROWNUM과 ROWID

          [12] WITH 구문

 

---

---

 

01. SQL 기본

 

[8] 명시적 형변환과 암시적 형변환

(1) 형변환 | 2개 데이터의 데이터타입이 일치하도록 변환하는 것

• 데이터타입이 불일치하는 경우에 발생함 (숫자-문자열 비교, 문자열-날짜형 비교 등)
• 명시적 형변환 & 암시적 형변환

명시적 형변환 (Explicit)	암시적 형변환 (Implicit)
- 형변환 함수를 사용하여 데이터타입 일치시킴 - 개발자가 SQL 함수로 형변환 하는 경우	- 데이터베이스 관리시스템이 자동으로 형변환 - 개발자가 형변환을 하지 않은 경우

 

 

• 형변환 함수: 문자열→숫자, 문자열→날짜, 숫자→문자열, 날짜→문자열

형변환 함수	설명
TO_NUMBER (문자열)	문자열 → 숫자
TO_CHAR (숫자 or 날짜, [FORMAT])	숫자 or 날짜 → 지정된 포맷의 문자열
TO_DATE (문자열, FORMAT)	문자열 → 지정된 포맷의 날짜형

 

 

• 인덱스 칼럼의 경우: 
  - 인덱스: 데이터를 빠르게 조회하기 위해 인덱스 키를 기준으로 데이터를 정렬함
  
  - 인덱스 칼럼에 형변환이 발생하면, 인덱스가 있어도 사용할 수 없게 된다.
  - 따라서 암시적 형변환이 발생하지 않도록, 미리 명시적 형변환을 사용하면 된다.

# 암시적 형변환이 발생하는 경우
# DBMS가 자동으로 stdno를 문자열로 변환하여 데이터타입을 일치시킴
SELECT * FROM STUDENT
  WHERE stdno='100';
    
    
# 명시적 형변환으로 방지 >> 인덱스 사용 가능!
# 형변환 함수 TO_NUNBER를 사용하여 stdno 칼럼이 변환되지 않도록 함
SELECT * FROM STUDENT
  WHERE stdno=TO_NUMBER('100');

 

---

 

[9] 내장형 함수 (BUILT-IN Function)

(1) 내장형 함수 | 형변환, 문자열, 숫자열, 날짜형 함수

 

(2) DUAL 테이블 | Oracle DB에 의해 자동 생성되는 테이블

내장형 함수	DUAL 테이블
- 모든 데이터베이스가 SQL 내장형 함수를 가지고 있음	- 모든 사용자가 사용 가능 / 임시로 사용하는 테이블
- DBMS 벤더별로 약간의 차이 있으나, 거의 비슷함	- 내장형 함수 실행할 때도 사용 가능

 

 

(3) 내장형 함수의 종류

• DUAL 테이블에서 사용하는 문자열/숫자열/날짜형 내장함수들은 중첩 사용도 가능하다!

# (1) 문자열 함수 예시
# 결과값: 97, 'XY', 3
SELECT ASCII('X'), SUBSTR('XYZ', 1, 2), LENGTH(LTRIM(' XYZ'))
  FROM DUAL;


# (2) 숫자형 함수 예시
# 결과값: 오늘 날짜, 지금 연도, 문자열 형태의 오늘 날짜
SELECT SYSDATE,
  EXTRACT(YEAR From SYSDATE),
  TO_CHAR(SYSDATE, 'YYYYMMDD')
  FROM DUAL;


# (3) 날짜형 함수 예시
# 결과값: 10, 1, 1, 10.1
SELECT ABS(-10), SIGN(10), MOD(5,2), ROUND(10.123, 1)
  FROM DUAL;

 

 

• 문자열 함수

문자열 함수	기능	문자열 함수	기능
ASCII (문자)	문자/숫자 → 아스키 코드값	LOWER (문자열)	소문자 변환
CHAR (ASCII 코드값)	아스키 코드값 → 문자열	UPPER (문자열)	대문자 변환
SUBSTR (문자열, m, n)	문자열 m번째부터 n개 자름	LTRIM (문자열, 지정문자)	왼쪽에서 지정문자 삭제
CONCAT (문자열1, 문자열2)	문자열1과 문자열2를 결합	RTRIM (문자열, 지정문자)	오른쪽에서 지정문자 삭제
LENGTH(문자열), LEN(문자열)	공백포함 문자열 길이	TRIM (문자열 지정문자)	왼쪽,오른쪽에서 지정문자 삭제

 

 

• 숫자형 함수
  

숫자형 함수	기능	숫자형 함수	기능
ABS (숫자)	절댓값	CEIL (숫자), CEILING (숫자)	올림 (크거나 같은 최소의 정수)
SIGN (숫자)	부호 (양수, 음수, 0 구분)	FLOOR (숫자)	버림 (작거나 같은 최대의 정수)
MOD (숫자1, 숫자2)	숫자1을 숫자2로 나눈 나머지	ROUND (숫자, m)	소수점 m자리에서 반올림
		TRUNC (숫자, m)	소수점 m자리에서 절삭

# (1) 올림 예시: 결과 11
SELECT CEIL(10.9) FROM DUAL;

# (2) 반올림 예시: 결과 39
SELECT ROUND(38.534) FROM DUAL;

 

 

• 날짜형 함수

날짜형 함수	기능
SYSDATE	오늘 날짜를 날짜형 타입으로 반환
EXTRACT (YEAR | MONTH | DAY from dual)	날짜에서 연도, 월, 일을 반환

 

---

 

[10] DECODE와 CASE문

• DECODE: IF문 구현
  - 특정 조건이 참이면 A, 거짓이면 B
  

 

• CASE문: 조건문 구현
  - CASE문 구성: IF~THEN~ELSE-END
  - WHEN: 조건
  - THEN: 조건이 참이면 실행
  - ELSE-조건이 거짓이면 실행

# DECODE 예시
# stdno=1001이면 'TRUE'를 출력
# stdno=1001이 아니면 'FALSE'를 출력
SELECT DECODE (stdno, 1001, 'TRUE', 'FALSE')
  FROM STUDENT;


# CASE문 예시
# stdno=1001이면 'A'를 출력
# stdno=1002이면 'B'를 출력
# 둘다 아니면 'C'를 출력
SELECT CASE
       WHEN stdno=1001 THEN 'A'
       WHEN stdno=1002 THEN 'B'
       ELSE 'C'
    END
  FROM STUDENT;

 

---

 

[11] ROWNUM과 ROWID

(1) ROWNUM | 화면에 데이터를 출력할 때 부여되는 논리적 순번

• Oracle 데이터베이스에서 SELECT문의 결과에 → 논리적인 일련번호를 부여함
• Oracle-ROWNUM = SQL Server-TOP문 = MySQL-LIMIT구
  
• ROWNUM은 조회되는 데이터의 행 개수를 제한할 때 사용됨
  
  
• ROWNUM을 사용한 페이지단위 출력을 위해서, 인라인 뷰를 사용해야 함
• 인라인 뷰(Inline view): SELECT문에서 FROM절에 사용되는 서브쿼리(Subquery)
• ROWNUM, BETWEEN구를 함께 사용하면 → 한 페이지당 n개 행을 조회하는 웹페이지 조회를 구현할 수 있다.

# 1개 행을 조회
SELECT * FROM STUDENT
  WHERE ROWNUM <= 1;
    
SELECT * FROM STUDENT
  WHERE ROWNUM < 2;
    
    
# 인라인 뷰: FROM절에 SELECT문을 사용
# ROWNUM에 별칭으로 list를 사용함
# 5개 행을 조회
SELECT *
  FROM (SELECT ROWNUM list, stdname FROM STUDENT)
  WHERE list <= 5;
    
    
# 웹 페이지 조회 구현 예시
# 특정 행의 리스트만 조회
SELECT *
  FROM (SELECT ROWNUM list, stdname FROM STUDENT)
  WHERE list BETWEEN 5 AND 10;

 

 

(2) ROWID | Oracle 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값 (데이터의 주소)

• 데이터가 어떤 데이터파일, 블록에 저장되어 있는가
• 모든 테이블은 ROWID를 가지고 있음
• 예시: SELECT ROWID, stdname FROM STUDENT;

 

• ROWID 구성: 오브젝트 번호 + 상대 파일 번호 + 블록 번호 + 데이터 번호

구성 영역	길이	설명
오브젝트 번호	6자리 (1~6)	해당 데이터 객체가 생성될 때 할당됨 → 오브젝트별로 유일한 고유번호
상대 파일 번호	3자리 (7~9)	테이블스페이스에 속해있는 각각의 데이터파일에 할당됨 → 상대적인 번호
블록 번호	6자리 (10~15)	데이터파일 내부에서 어떤 블록에 데이터가 속해있는가 → 데이터 블록 위치
데이터 번호	3자리 (16~18)	데이터 블록에 데이터가 저장되어 있는 순서 → 블록 내 데이터 행의 번호

 

---

 

[12] WITH 구문

(1) WITH | 서브쿼리를 활용하여, 임시테이블이나 뷰처럼 사용할 수 있는 구문

• 서브쿼리: 어떤 쿼리 내에 속한, Nested/Inner 쿼리
  - 하나의 SQL문 안에 포함된 또다른 SQL문
  
  
• WITH구문은 서브쿼리 블록에 별칭을 지정하고,
  옵티마이저는 인라인 뷰 or 임시 테이블로 판단한다.

# WITH구문을 사용하여, 학과번호가 404인 것의 임시테이블을 만들고 조회함
WITH W_STUDENT AS
  (SELECT * FROM STUDENT WHERE deptno=404)
  SELECT * FROM W_STUDENT;

 

---

 

참고도서: SQL 개발자 이론서+기출문제_이기적, 2020