오라클 SQL - 단일행함수

 

함수의 종류는 내장함수와 사용자 정의함수로 나눌 수 있다.

 

내장 함수 : 단일행함수: 데이터가 한 행씩 입력되고, 한 행당 결과가 하나씩 나옴

                  다중행함수: 여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수 

                  -> 다루는 자료형에 따라 조금 세분화된다. 

 

1. 단일행 함수들 

 

1.1 문자함수: 

  UPPER,LOWER,INITCAP(첫글자만 대문자)

SELECT ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME)
FROM EMP;

 *SQL문은 대소문자 구분하지 않지만, 데이터는 대소문자를 구분한다.

 

- 문자열길이 LENGTH ,LENGTHB(바이너리 크기 반환) 

- 문자열 일부 추출: SUBSTR (문자열데이터, 시작위치,추출길이)

- 특정 문자 위치 검색(INDEX OF) INSTR

   INSTR(대상문자열, 찾는문자, 시작위치, 몇번째) 

SELECT INSTR('HELLO, ORACLE!', 'L')
SELECT INSTR('HELLO, ORACLE!', 'L', 2,2)
--두번째 L

 - 문자 바꾸기 REPLACE

 - 데이터 빈 공간을 특정 문자로 채우기 LPAD, RPAD

   (문자열데이터 혹은 열 이름, 데이터 자릿수, 빈공간에 채울 문자)

SELECT LPAD('Oracle', 10, '#')
--####Oracle (공간 남으면, 데이터 왼쪽으로)
--Oracle**** (RPAD)

  - 문자열 합치기 CONCAT (||)

CONCAT(EMPNO, ENAME)
CONCAT(EMPNO, CONCAT(':',ENAME))
-- ||로 문자열 합칠 수 있음

 - TRIM,LTRIM,RTRIM > 기본적으로 공백제거, 필요할 때 옵션찾아보자 (문자열 지정시 문자삭제 가능)

  

 

1.2 숫자함수

 

- 반올림 ROUND(숫자,반올림위치)

- 버리기 TRUNC 

- 지정한 숫자와 가까운 정수를 찾기 CEIL(가장 큰 정수), FLOOR(가장작은정수)

- 나머지 MOD

 

1.3 날짜 함수 

 

날짜 +- 숫자 = 지정한 숫자만큼 일수 이후의 날짜 

날짜 - 날짜 = 두 날짜 데이터 간의 일수 차이 

 

- SYSDATE: 운영체제의 현재 날짜와 시간 

- 몇 개월 이후의 날짜: ADD_MONTHS(날짜데이터, 더할월수)

SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;

- 두 날짜간의 개월 수 차이: MONTHS_BETWEEN() 

- 돌아오는 요일, 달의 마지막 날짜 NEXT_DAY(날짜데이터, 요일문자), LAST_DAY(날짜데이터)

- 날짜 반올림, 버림하는 ROUND,TRUNC (날짜데이터, 반올림(버림) 포맷) -> 포맷은 나중에 찾아봐

SELECT ROUND(SYSDATE,'YYYY'), TRUNC(SYSDATE,'DDD') FROM DUAL;

 

1.4 자료형 변환 함수 

 

명시적 형변환 

TO_CHAR, TO_NUMBER,TO_DATE

 

- 날짜,숫자 데이터를 문자로 변환하는 TO_CHAR

   TO_CHAR(날짜, 포맷, 'NLS_DATE_LANGUAGE = ')

 

출처: https://velog.io/@godkimchichi/Oracle-%EB%B3%80%ED%99%98%ED%95%A8%EC%88%98

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS')
       TO_CHAR(SYSDATE, 'HH12:MI:SS')
        TO_CHAR(SYSDATE, 'HH,:MI,SS P.M.')
        
 FROM DUAL;

 

- 숫자 데이터 TO_CHAR(숫자데이터, 포맷)

  9: 숫자 한자리 의미 

  0: 빈자리 0으로 채움 의미

  $: 달러 붙여서 출력 

  L : 지역 화폐 단위 기호 붙여서 출력

  . , ,: 소수점, 천단위 구분

 

- 문자 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수 

   

> 숫자처럼 생긴 문자 데이터 간의 산술연산 수행시 자동 형변환

> TO_NUMBER(문자데이터, 인식될 숫자형태) 위에 숫자 포맷이랑 똑같음 

 

- 문자 데이터를 날짜 데이터로 변환하는 TO_DATE 함수

 

> 날짜 포맷은 위에 표와 같음 

SELECT
TO_DATE('2018-07-14', 'YYYY-MM-DD')
TO_DATE('20180714', 'YYYY-MM-DD')
FROM DUAL;

 > 이렇게 문자를 날짜 데이터로 바꾼 뒤 간단한 연산 가능 

  > YY,RR: 두자리 년도 입력시 YY는 현재 년도 기준 RR은 00~49, 50~99를 기준으로 다르게 반응

 

2. NULL처리 함수 (단일행)

 

NVL(데이터 혹은 열, NULL일 경우 반환할 데이터)

NVL2(데이터 혹은 열, NULL이 아닐 경우 반환, NULL일 경우 반환)

 

3. 상황에 따라 다른 데이터 반환하는 DECODE, CASE문 (단일행)

 

DECODE([검사할 열,데이터,연산결과,함수결과],[조건],[반환값],[조건],[반환]...,위 조건이 모두 불일치시)

SELECT EMPNO, ENAME, JOB, SAL, 
DECODE(JOB, 
      'MANAGER', SAL*1,1
      'SALESMAN', SAL*1.05
       'ANALYST', SAL,
       SAL*1.03)
       
       FROM EMP;

 CASE문 

SELECT EMPNO, ENAME,JOB, SAL,
CASE JOB
     WHEN 'MANAGER' THEN SAL*1.1
     ELSE SAL*1.03
     END AS UPSAL
FROM EMP;

 > 기준 데이터 없어도 WHEN안에 조건문 넣고 THEN가능

 

*단 조건별로 동일한 자료형의 데이터를 반환해야함!