[코테를 위한 Oracle 정리] 5. 데이터 조작 : 내장 함수

손에 잡히는 10분 SQL 8장 참고 및 본인 추가

 

같은 기능을 하는 함수여도 DBMS마다 함수나 문법이 매우 다르다.

문자열 조작

• UPPER() : 대문자로 변환
• LOWER() : 문자열을 소문자로 변환
• RTRIM() : 문자열의 오른쪽에 있는 공백 문자를 삭제
• LTRIM() : 문자열의 왼쪽에 있는 공백 문자를 삭제
• LEFT(), RIGHT(): ORACLE에서는 없다. SUBSTR로 대체한다
• LENGTH() : 문자열의 길이를 반환
• SUBSTR(문자열, 시작, 가져올 문자 길이) : 문자열 일부 추출하기
  • SUBSTR(PROD_ID,3,4) 라면 3번째부터 4개를 가져온다는 뜻이다

select prod_id,SUBSTR(prod_id,2,2)
from products;

수치, 수학 계산

• ABS() : 절댓값 반환
• SQRT() : 제곱근을 반환
• COS(), SIN(), TAN() : 코사인, 사인, 탄젠트값
• EXP() : 지숫값 반환
• ROUND(실수, 반올림을 할 소숫점 아래 자리) : 반올림
• TRUNC(수, 자릿수) : 자릿수 아래부터 절사
  • TRUNC(123.456,2) -> 123.45
  • TRUNC( 123.456,-1) -> 120 (일의 자리 단위 절사)

날짜와 시간

• SYSDATE : 현재 날짜 가져오기
• date -1과 같이 연산을 통해 하루 전 날짜를 구할 수 있다
• TO_DATE(문자열, 날짜 포맷)
  • TO_DATE('2020-11-11', 'yyyy-mm-dd') : 문자열을 날짜로 변환한다
  • 대소문자를 구분하지 않는다

select EXTRACT(year FROM order_date)
from ORDERS;

 

• EXTRACT({필드} FROM date열) : 날짜 일부분 추출
  • YEAR, MONTH, DAY, HOUR, MINUTE, SECOND을 필드로 추출할 수 있다

---

EXTRACT 제대로 사용하기

프로그래머스 '입양 시각 구하기(1)'문제에서 EXTRACT가 제대로 작동하지 않았다. 열의 type도 확인했지만 datetime 이었다.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/EXTRACT-datetime.html

SQL Language Reference

위의 oracle documentation을 보니

이렇게 필드 별로 지원하는 타입이 달랐다. 정리하자면 아래와 같다.

필드	지원하는 타입
YEAR, MONTH, DAY	date, timestamp
HOUR, MINUTE, SECOND	timestamp

 

SELECT 
EXTRACT(DAY FROM a.DATETIME),
EXTRACT(HOUR FROM cast(a.DATETIME as timestamp))
from animal_outs a

 

이렇게 예시로 사용해볼 수 있겠다.

programmers에서 datetime으로 주어도 extract에 입력되는 열은 ANSI DATE로 인식되어 시분초를 추출하고 싶을 때는 timestamp로 바꿔야 한다고 생각하면 될 것 같다.

---

• MONTHS_BETWEEN(date, date) : 개월수 차이를 반환한다(날짜1-날짜2)
• TRUNC(date, 옵션) : 옵션에 적은 기준까지만 보여주고 아래는 절사한다
  • TRUNC('2024-01-02 21:35:24', 'HH24') -> '2024-01-02 21:00:00'
  • MONTH, YEAR, DAY 등의 옵션도 있다

SELECT TRUNC(TO_DATE('2018-12-13 22:10:59', 'YYYY-MM-DD HH24:MI:SS'), 'YEAR')
FROM DUAL

• ADD_MONTHS(date,수) : 날짜 데이터의 월 단위를 더하거나 뺴서 반환한다
• ADD_MONTHS(TO_DATE('2024-04-22'), 3) -> 2024-07-22

데이터 형 변환

• TO_DATE
• TO_CHAR
  
  • date형에서 원하는 필드를 추출하는데 사용하기도 한다
    • TO_CHAR(date, 'YYYY/MM/DD') : 연월일
    • TO_CHAR(date, 'HH24') : 24시간 형식의 hour
    • TO_CHAR(date, 'HHMISS'), TO_CHAR(date, 'HH24MISS') : 시분초
• TO_NUMBER
• CAST
  • CAST( 열 as VARCHAR(n)) : 숫자를 문자로, 날짜를 문자로
  • CAST( 열 as NUMBER(n)) : 문자를 숫자로
    • CAST( 2.99 as NUMBER(10)) -> 3 : 소수 첫째자리에서 반올림 한다
  • CAST( 열 as NUMBER(n, m)) : 문자를 실수로(m은 소수점 자릿수) : 이 또한 반올림 된다
  • CAST( 열 as DATE) : 문자를 날짜로
  • CAST( 열 as timestamp) : 문자를 타임스탬프로