[Oracle] SQL Function 복습

SQL의 함수에는 단일 행 함수와 그룹 함수로 나눠진다.

• 단일 행 함수
  • 각 행마다 반복적으로 적용되어 입력 받은 행의 개수만큼 결과 반환
• 그룹 함수
  • 특정 행들의 집합으로 그룹이 형성되어 적용되기 때문에 그룹 당 1개의 결과만 반환

문자 처리 함수

LENGTH, LENGTHB, INSTR, INSTRB 함수는 반환 값이 NUMBER이고 이를 제외한 나머지 함수의 반환 값은CHARACTER형이다.

LENGTH(CHAR / STRING), LENGTHB(CHAR / STRING)

SELECT
       LENGTH('HELLO'), -- 길이를 반환 (5)
       LENGTHB('HELLO'), -- 문자열 BYTE로 변환해서 반환 (5)
       LENGTH('안녕'), -- 길이를 반환 (2)
       LENGTHB('안녕'), -- BYTE로 반환 (6)
       LENGTHB('안녕!') -- BYTE로 반환 (7)
FROM DUAL;

INSTR(STRING, STR, POSITION)

STRING : 원본

STR : 찾고자 하는 문자열

POSITION : 찾을 위치 시작 값 (DEFAULT : 1)

• POSITION > 0 : 앞에서 부터 찾겠다.
• POSITION < 0 : 뒤에서 부터 찾겠다.

하지만 결과값은 앞에서부터 찾는 위치를 반환한다.

SELECT
      INSTR('HELLO', 'E', 1), -- 앞에서부터 찾는다 (2)
      INSTR('HELLO', 'E', -1) -- 뒤에서부터 찾는다 (2)
FROM DUAL;

LPAD / RPAD(STRING, N, STR)

STRING : 문자열

N : 총 몇칸을 기준으로 할것인가?

STR : N보다 STRING의 길이가 작다면 그 공간에 뭘 넣을것인가?

SELECT
      LPAD('-1111-1111', 13, '*'), -- ***-1111-1111
      RPAD('010-1111-', 13, '*') -- 010-1111-****
FROM DUAL;

LTRIM / RTRIM(STRING, STR)

STRING : 문자열

STR : 여기에 오는 문자열이 하나라도 존재한다면 지운다. (DEFAULT ' ')

• 예시) 'ABC' 라면 'A','B','C' 이렇게 나눈 다음 일치하는게 있다면 지운다.
• 그렇게 제거하다가 아닌 값이 나오면 함수를 멈춘다.
• 뒤에 일치하는 값이 있어서 더이상 찾지 않는다.

SELECT
      LTRIM('ABCDEAB', 'ABCD'), -- EAB
      RTRIM('ABECVBA', 'ABCD') -- ABECV
FROM DUAL;

TRIM(LEADING / TRAILING / BOTH STR FROM STRING)

LEADING(앞), TRAILING(뒤), BOTH(양쪽) : 어디서 부터 제거 할껀지 정한다.

• DEFUALT : BOTH

STR : 여기에 오는 문자열이 하나라도 존재한다면 지운다. (DEFUALT ' ')

STRING : 문자열

SELECT
      TRIM(' T EST '), -- T EST
      TRIM('X' FROM 'XBOX'), -- BO
      TRIM(LEADING 'X' FROM 'XBOX'), -- BOX
      TRIM(TRAILING 'X' FROM 'XBOX'), -- XBO
      TRIM(BOTH 'X' FROM 'XBOX') -- BO
FROM DUAL;

SUBSTR(STRING, START, LENGTH)

STRING : 문자열

START : 어디 위치에서 자를껀지 정하기

• 음수일 경우 뒤에서 부터 위치를 말한다.

LENGTH : START위치 부터 몇개 반환할지

• LENGTH를 정하지 않으면 문자열의 끝까지 반환한다.

SELECT
      SUBSTR('HELLOWORLD', 6), -- WORLD
      SUBSTR('HELLOWORLD', 3, 2), -- LL
      SUBSTR('HELLOWORLD', -5) -- WORLD
FROM DUAL;

LOWER / UPPER / INITCAP (STRING)

SELECT
      LOWER('HELLO WORLD'), -- hello world
      UPPER('hello world'), -- HELLO WORLD
      INITCAP('hello world') -- Hello World
FROM DUAL;

INITCAP(STRING)의 경우 띄어쓰기를 기준으로 단어의 첫글자만 대문자로 변환해준다.

CONCAT(STRING1, STRING2)

SELECT
      CONCAT('HELLO', ' WORLD') -- HELLO WOLRD
FROM DUAL;

CONCAT함수의 경우는 꼭 지켜하는 규칙이 2개의 문자열만 가능하다는 것이다.

하지만 사용하다 보면 여러개의 문자열을 합쳐야 할때도 있는데 이때는 CONCAT대신 연결연산자(||)를 사용하면 편하다.

SELECT
      'HELLO' || ' WORLD' || 'THIS' || ' IS ' || 'ORACLE'
      -- HELLO WORLD THIS IS ORACLE
FROM DUAL;

REPLACE(STRING, OLDSTR, NEWSTR)

SELECT
      REPLACE('HELLO WORLD', 'HELLO', 'BYE') -- BYE WORLD
FROM DUAL;

숫자 처리 함수

ABS(NUMBER)

ABS(NUMBER) 함수의 경우 절대값을 구하는 함수이다.

SELECT
      ABS(-10), -- 10
      ABS(10), -- 10
      ABS(-100.111) -- 100.111
FROM DUAL;

MOD(NUMBER, DIVISION)

나머지 값을 구하는 함수이며 자바의 "%" 연산자와 같은 역할을 한다.

SELECT
      MOD(10,3), -- 1
      MOD(-10,3) -- -1
FROM DUAL;

ROUND(NUMBER, STANDARD)

STANDARD까지 반올림하는 함수이다.

• STANDARD의 DEFUALT값은 0이며 0은 정수의 첫째 자리를 의미한다.

SELECT
      ROUND(10.56), -- 11
      ROUND(10.39382, 4), -- 10.3938
      ROUND(-10.61, 1), -- -10.6
      ROUND(152, -2) -- 200
FROM DUAL;

FLOOR(NUMBER)

소수점을 버린다고 생각한다면 음수의 경우 잘못된 값을 예상할 수 있기때문에 가장 가까운 왼쪽 정수로 간다고 생각하면 편할 수 있다.

• 예시 -4,-3, -2, -1 , 0, 1, 2, 3, 4
• FLOOR(-2.5)의 경우 가장 가까운 왼쪽에 있는 정수는 -3 이다.

SELECT
      FLOOR(10.11), -- 10
      FLOOR(-16.59), -- -17
      FLOOR(10.99) -- 10
FROM DUAL;

TRUNC(NUMBER, STANDARD)

STANDARD의 DEFAULT 값은 0이다.

SELECT
      TRUNC(100.999), -- 100
      TRUNC(999.111), -- 999
      TRUNC(100.999, 1), -- 100.9
      TRUNC(150.999, 2), -- 150.99
      TRUNC(155.999, -1) -- 150
FROM DUAL;

CEIL(NUMBER)

값이 있다면 무조건 올림하는 함수

SELECT
      CEIL(11.01), -- 12
      CEIL(10), -- 10
      CEIL(-10.11), -- -10
      CEIL(10.99) -- 11
FROM DUAL;

날짜 처리 함수

SYSDATE

MONTHS_BETWEEN(DATE, DATE) 함수의 반환 값은 NUMBER타입이고 나머지 함수의 반환 값은 DATE타입이다.

SELECT
       SYSDATE -- 23/02/26
FROM DUAL

MONTHS_BETWEEN(DATE1, DATE 2)

두 DATE의 차이를 개월 수로 나타낸다.

SELECT
      MONTHS_BETWEEN(TO_DATE('230226'), TO_DATE('230224'))
FROM DUAL;

ADD_MONTHS(DATE, NUMBER)

DATE에 NUMBER만큼 개월이 지난 결과를 보여준다.

SELECT
      ADD_MONTHS(SYSDATE, 3) -- 23/05/26
FROM DUAL;

NEXT_DAY(DATE, STRING OR NUMBER)

DATE에 가장 가까운 STRING혹은 NUMBER의 DATE를 반환한다.

• NUMBER의 경우 1은 일요일을 의미하게 된다.

SELECT
      NEXT_DAY(SYSDATE, '월'), -- 23/02/27
      NEXT_DAY(SYSDATE, '화요일'), -- 23/02/28
      NEXT_DAY(SYSDATE, 1), --23/03/05
      NEXT_DAY(SYSDATE, 'MONDAY') -- ERROR
FROM DUAL;

오라클의 설정이 한글이라면 한글만 인식이 되고 영어라면 영어만 인식을 할 수 있다.

-- 설정 언어 바꾸는 방법
ALTER SESSION SET NLS_LANGUAGE = AMERICAN; -- 영어로 설정 변경
ALTER SESSION SET NLS_LANGUAGE = KOREA; --- 한글로 설정 변경

LAST_DAY(DATE)

SELECT
      LAST_DAY(SYSDATE) -- 23/02/28
FROM DUAL;

EXTRACT(YEAR OR MONTH OR DAY FROM DATE)

SELECT
      EXTRACT(YEAR FROM SYSDATE), -- 2023
      EXTRACT(MONTH FROM SYSDATE), -- 2
      EXTRACT(DAY FROM SYSDATE) -- 26
FROM DUAL;

형 변환 함수

구분	매개변수	RETURN
TO_CHAR	DATE OR NUMBER	CHAR
TO_DATE	CHAR OR NUMBER	DATE
TO_NUMBER	CHAR	NUMBER

TO_CHAR(DATE OR NUMBER, FORMAT)

형식	의미	형식	의미
YYYY	년도 4자리	MON	월 글자 표현
YY	년도 2자리	DAY	요일
MM	월 숫자표현	DY	요일 약어로 표현

SELECT
     TO_CHAR(SYSDATE, 'YYYY-MM-DD'), -- 2023-02-26
     TO_CHAR(SYSDATE, 'YY/MON - DAY - DY') -- 23/2월 - 일요일 - 일
FROM DUAL;

형식	의미
L999,999,999	설정 통화로 1000단위로 표시
000,000,000	지정된 크기에서 빈공간을 왼쪽에 0으로 채움

SELECT
      TO_CHAR(2500000, 'L999,999,999'), -- 2,500,000
      TO_CHAR(2500000, '000,000,000') -- 002,500,000
FROM DUAL;

TO_DATE(CHAR OR NUMBER, FORMAT)

SELECT
      TO_DATE(20220202, 'YYYYMMDD'), -- 22/02/02
      TO_DATE(20220202, 'YY/MM/DD'), -- 22/02/02
      TO_DATE('20220202', 'YYYY-MM-DD') -- 22/02/02
FROM DUAL;

TO_NUMBER(CHAR, FORMAT)

SELECT
      TO_NUMBER('1,000,000', '999,999,999') -- 1000000
FROM DUAL;

NULL 처리 함수

NVL(COLUMN, FORMAT)

데이터에 NULL값을 지정한 숫자나 문자로 변경하여 NULL값을 처리해준다.

SELECT
      NVL(NULL, 0) -- 0
FROM DUAL;

선택 함수

DECODE(Q, IF1, RESULT1, IF2,RESULT2.......DEDAULT)

-- 주민 등록 번호가 있는 테이블이 있다는 가정
SELECT
      DECODE(SUBSTR(NO,8,1), '1', 'MEN', '2', 'GIRL', 'ALIEN')
      -- SUBSTR(NO,8,1)의 경우 뒷자리 첫번째 자리이며
      -- '1'이면 'MEN' '2'이면 'GIRL' 그외의 값들은 'ALIEN'이 나오게 된다.
FROM DUAL;

CASE WHEN ~ THEN ~ ELSE ~ END

SELECT
      CASE WHEN SUBSTR(NO,8,1) = '1' THEN '남'
           WHEN SUBSTR(NO,8,1) = '2' THEN '여'
           WHEN SUBSTR(NO,8,1) = '3' THEN '남'
           WHEN SUBSTR(NO,8,1) = '4' THEN '여'
           ELSE 'ALIEN'
      END AS 'GENDER'
FROM DUAL;