[SQL] 형변환 함수 / NULL 처리 함수 / 와일드카드

‣ DUAL 테이블

• 아무것도 가지고 있지 않음.
  사실상 FROM절이 필요 없지만 오라클의 특성상 FROM을 입력해야 하는 경우를 위해 오라클에서 임의로 만들어 놓은 테이블
  (SYS 계정에서 소유)

---

정리

1. DUMMY 칼럼 1개를 가지고 있는 테이블이다.
2. 'X' 값을 가지고 있다.
3. FROM절이 필요 없는 SELECT문을 사용할 때 DUAL 테이블을 이용한다.

---

⇢DUAL 테이블 예시

-- 연산
SELECT 1 + 1 FROM DUAL;         -- DUAL : 임의로 만들어진 테이블

-- 시퀀스
CREATE SEQUENCE TEST_SEQ ORDER;
SELECT TEST_SEQ.NEXTVAL FROM DUAL;

---

---

‣ 형변환 함수

데이터 타입 변환

: 숫자, 날짜, 문자 등의 데이터타입을 바꿈

---

정리

---

FORMAT

---

---

⇢데이터 타입 변환 예제

---

1. 문자 -> 숫자로 변환하기
   TO_NUMBER(문자)

SELECT TO_NUMBER('123') 
  FROM DUAL;

---

2. 숫자 -> 문자로 변환하기
   TO_CHAR(숫자, [형식])

SELECT TO_CHAR(1234)
     , TO_CHAR(1234, '999999')  -- '  1234'
     , TO_CHAR(1234, '000000')  -- '001234'
     , TO_CHAR(1234, '9,999')   -- '1,234'
     , TO_CHAR(12345, '9,999')  -- '#####'  -> 숫자는 5자리인데, 형식은 4자리만 지정되었다.
     , TO_CHAR(12345, '99,999') -- '12,345' 
  FROM DUAL;

---

3. 날짜 -> 문자로 변환하기
   TO_CHAR(날짜, [형식])

/*
    3. 날짜 -> 문자로 변환하기
        TO_CHAR(날짜, [형식])
        
        * 날짜/시간 형식
        1) YY   : 년도 2자리
        2) YYYY : 년도 4자리
        3) MM   : 월 2자리(01~12)
        4) DD   : 일 2자리(01~31)
        5) AM   : 오전 / 오후
        6) HH   : 12시각(01~12)
        7) HH24 : 24시각(00~23)
        8) MI   : 분(00~59)
        9) SS   : 초(00~59)
*/

SELECT TO_CHAR(HIRE_DATE, 'YYYY-MM-DD')  -- 시간 추가 작성 방식(+ AM HH:MI:SS')
  FROM EMPLOYEES;

---

4. 문자 -> 날짜로 변환하기
   TO_DATE(문자,[형식])

-- 현재 날짜와 시간(을 사용하는 함수)
SELECT SYSDATE       -- '23/07/04'
     , SYSTIMESTAMP  -- '23/07/04 14:46:44.303000000 +09:00'
  FROM DUAL;


-- 현재 날짜와 시간 - 형식 지정
SELECT TO_CHAR(SYSDATE,      'YYYY-MM-DD AM HH:MI:SS')  
     , TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')
  FROM DUAL;
  

-- 문자로 된 날짜/시간을 실제 날짜/시간 타입으로 변환하기
SELECT TO_DATE('23/07/04')              -- '년/월/일' 형식으로 해석
     , TO_DATE('23/07/04', 'DD/MM/YY')  -- '일/월/년' 형식으로 해석
  FROM DUAL;

---

SYSDATE / SYSTIMESTAMP

• SYSDATE 는 날짜만 나오는 함수가 아니라 시간도 함께 사용하는 함수다. 다만, 디폴트 값이 날짜다.

• SYSDATE와 SYSTIMESTAMP의 차이는 초의 소수점 단위까지 표시 하느냐의 차이

• 초의 소수점 뒤 단위까지 보고 싶으면 타임스탬프

• TO_DATE : 날짜를 해석해서 해당 날짜 형식으로 만들어줌

---

예제 데이터 작성

• SYSDATE는 년/월/일만 포함하지 않는데 시간까지 맞출 수 없기에 앞쪽 칼럼에 날짜를 넣어줌 (앞으로 날짜 비교다 싶으면 안전하게 TO_DATE 다 넣기!)

• TO_CHAR 는 문자로 바꾸는 거라서 숫자, 날짜 모두 문자로 바꾸기 가능한 형식

-- 예제 데이터 작성
DROP TABLE EXAMPLE_T;
CREATE TABLE EXAMPLE_T (
    DT1 DATE
  , DT2 TIMESTAMP
);
INSERT INTO EXAMPLE_T(DT1, DT2) VALUES(SYSDATE, SYSTIMESTAMP);
COMMIT;

-- DT1이 '23/07/04'인 데이터를 조회하기(안 됨)
SELECT * 
  FROM EXAMPLE_T
 WHERE DT1 = '23/07/04';

-- DT1이 '23/07/04'인 데이터를 조회하기(안 됨)
SELECT *
  FROM EXAMPLE_T
 WHERE DT1 = TO_DATE('23/07/04', 'YY/MM/DD');

-- DT1이 '23/07/04'인 데이터를 조회하기(됨)
SELECT *
  FROM EXAMPLE_T
 WHERE TO_DATE(DT1, 'YY/MM/DD') = TO_DATE('23/07/04', 'YY/MM/DD');  

---

---

‣ 묵시적 타입 변환

: 암시적 타입변환

• 정확한 연산을 위해 오라클에서 데이터 타입을 내부적으로 스스로 변환하는 것을 의미함
• 주로 숫자와 문자를 연산할 때 발생

---

⇢묵시적 타입 변환 예시

- EMPLOYEE_ID 데이터 타입 ⇒ NUMBER
- 예시 본문에는 ‘작은따옴표’를 이용하여 문자를 대입
- 하지만 오라클이 자체적으로 문자 타입을 숫자 타입 쿼리로 변환하여 출력함

SELECT * 
  FROM EMPLOYEES
 WHERE EMPLOYEE_ID = '150';  

-- 오라클이 WHERE EMPLOYEE_ID = TO_NUMBER('150'); 쿼리로 바꿔서 돌렸다는것임 

---

- 정상 작동 가능하지만, 오라클이 사용하지 않은 쿼리 예시(최적화X)

SELECT * 
  FROM EMPLOYEES
 WHERE TO_CHAR(EMPLOYEE_ID) = '150';   -- PK를 건드림으로 최적화되지 않은 쿼리 

---

---

‣ NULL 처리 함수

1. NVL(표현식, 표현식의 결과가 NULL인 경우에 사용할 값)
2. NVL2(표현식, 표현식의 결과가 NULL이 아닌 경우에 사용할 값, 표현식의 결과가 NULL인 경우에 사용할 값)

-- 사용 예시 --

-- 1. 사원 테이블에서 사원번호와 부서번호를 조회하기
-- 부서 번호가 없는 경우에는 0으로 조회하기
SELECT EMPLOYEE_ID, NVL(DEPARTMENT_ID, 0) 
  FROM EMPLOYEES;
 

-- 2. 사원 테이블에서 모든 사원드르이 실제 커미션을 조회하기
-- 커미션 = 연봉 * 커미션퍼센트
-- 커미션을 받지 않는 경우 0으로 조회하기
SELECT EMPLOYEE_ID
     , NVL(SALARY * COMMISSION_PCT, 0) AS COMMISSION1
     , SALARY * NVL(COMMISSION_PCT, 0) AS COMMISSION2
  FROM EMPLOYEES;

• 데이터에 NULL값이 있는 경우 나중에 JAVA로 연결하여 서비스 구현 시 예외(오류)가 날 수 있다
  • 이런 경우를 방지하기 위해 널 값에도 숫자를 준 뒤에 불러 오는 게 좋다 (예,NVL(DEPARTMENT_ID, 0) ⇒NULL값에 0을 넣기)
  • NULL값을 그대로 재현 시 오류가 날 수 있음

---

---

‣ 와일드카드

• 와일드카드 (만능문자) : % _

• LIKE 연산자 쓸 때 이용함

• % : 모든 문자라는 뜻 (와일드 카드라고 부름)

⇢패턴예시

• 김% : ‘김’으로 시작하는 모든 패턴
• %김 : ‘김’으로 끝나는 모든 패턴
• %김% : ‘김’을 포함하는 모든 패턴 (중간에 있는 ‘김’도 조회 가능)

---

• 추가 정리
  • % : 모든 문자를 의미, 글자 수에 제한이 없음
  • _ (밑줄) : 모든 문자를 의미, 글자 수가 1글자로 제한됨

---

---

• 유닉스 TIMESTAMP 란?
  • 유닉스 타임 스탬프 (POSIX time)는 유닉스 시스템, 리눅스 및 다양한 컴퓨터 시스템에서 널리 사용되는 시간을 결정하는 시스템이다. Unix 타임 스탬프는 UTC 1970 년 1 월 1 일 자정 이후 경과 된 시간 (초)으로 정의됩니다.
  • 1 / 1000 마다 1씩 증가 (= 1초에 1000씩 증가)

---

• 날짜 데이터
  • 날짜도 기본적으로 숫자이다.
  • 타임스탬프로 보면 날짜가 클 수록 최근 날짜
  • 날짜 -(빼기) 연산도 가능 (날짜는 숫자이기 때문)

---

---