Oracle, 오라클, 함수


DB 관리는 소스코드를 직접 작성하는 것과 더불어 굉장히 중요한 작업이다. DB와 관련된 내용을 정리하자면 끝이 없겠지만, 그 중요성만큼은 누구도 부정하지 않을 것이다.

사용자의 데이터를 적절한 쿼리문을 통해 저장하고, 불러오는 이른바 CRUD 작업은 DB만 전문적으로 관리하는 관리자뿐만 아니라, 응용프로그래머도 기본적인 소양으로써 지식을 갖추고 있어야 한다. 그리하여 첫 번째로 DB와 관련하여, RDBMS 중 오라클에서 제공하는 검색 관련 내장 함수에 대해 정리해보고자 한다.

Function

  • 내장 함수, 사용자 정의 함수
  • 매개변수에 어떤 값을 전달하면, 내부적인 처리를 진행하고, 결과를 반환하도록 만들어진 코드.
  • 단일행 함수(행당 결과 한 개)와 복수행 함수(여러개의 행을 투입하면 결과 한 개)로 구분된다.
  • 단일행 함수는 문자함수, 숫자함수, 날짜함수, 변환함수, 일반함수로 구분.

SUBSTR()함수

  • 문자열의 일부(시작위치, 글자수)만 추출해서 반환하는 함수.
  • 주의) 시작 위치는 1부터 시작한다.
  • 주의) 시작 위치에 -값을 주면 오른쪽 끝에서부터 출발한다.
  • 유사한 함수로 SUBSTRB() 함수가 있다. 바이트 수 기준으로 추출한다.

INSTR() 함수

  • 대상 문자열에서 특정 문자열이 존재하는지 여부를 확인하고, 위치를 반환하는 함수.
  • INSTR(대상문자열, 찾는문자열, 시작위치, 특정번째) 형식으로 사용. 특정번째는 생략 가능.
  • 검색 결과가 존재하지 않는 경우 위치값 0 반환한다.

LPAD() 함수

  • 특정 문자로 빈 공간을 채우는 함수. 채우는 위치는 LEFT.
  • LPAD(대상문자열, 전체자릿수, 채울문자열)
  • 유사한 함수로 RPAD() 함수가 있다. 채우는 위치는 RIGHT.

RPAD() 함수

  • 특정 문자로 빈 공간을 채우는 함수. 채우는 위치는 RIGHT.
  • RPAD(대상문자열, 전체자릿수, 채울문자열)
  • 유사한 함수로 LPAD() 함수가 있다. 채우는 위치는 LEFT.

LTRIM() 함수

  • 특정 문자의 빈 공간(특정 문자)을 제거하는 함수. 제거하는 위치는 LEFT.
  • LTRIM(대상문자열, 특정문자열) 형식으로 사용. 특정문자열 생략 가능.
  • 유사한 함수로 RTRIM() 함수가 있다. 제거하는 위치는 RIGHT.

RTRIM() 함수

  • 특정 문자의 빈 공간(특정 문자)을 제거하는 함수. 제거하는 위치는 RIGHT;
  • RTRIM(대상문자열, 특정문자열) 형식으로 사용. 특정문자열 생략 가능.
  • 유사한 함수로 LTRIM() 함수가 있다. 제거하는 위치는 LEFT.

REPLACE() 함수

  • 대상 문자열에서 특정 문자열을 다른 문자열로 치환하는 함수.
  • REPLACE(대상문자열, 찾는문자열, 치환문자열) 형식으로 사용.
  • 주의) 찾는 문자열이 없는 경우 치환이 일어나지 않는다.

ROUND() 함수

  • 반올림 처리 함수
  • ROUND(대상숫자, 자릿수) 형식으로 사용. 자릿수 지정 생략 가능.
  • 유사한 함수로 TRUNC()함수가 있다. 절삭 기능.

TRUNC() 함수

  • 절삭 처리 함수
  • TRUNC(대상숫자, 자릿수) 형식으로 사용. 자릿수 지정 생략 가능.
  • 유사한 함수로 ROUND()함수가 있다. 반올림 기능.
  • 몫 연산에도 사용 가능.

MOD() 함수

  • 나머지 결과 반환하는 함수
  • 유사한 함수로 TRUNC() 함수. 몫 연산 결과 반환.

SYSDATE 함수

  • 시스템의 오늘 날짜(시간)를 반환하는 함수
  • 주의) 매개변수 전달값이 없기 때문에 ()를 표기하지 않는다.
  • 날짜 자료를 가지고 산술 연산 가능. 날짜 단위로 계산된다.

MONTHS_BETWEEN() 함수

  • 날짜 자료를 가지고 산술 연산(빼기 연산) 가능. 월(개월) 단위로 계산된다.
  • 유사한 함수로 ADD_MONTHS() 함수가 있다. 더하기 연산.

ADD_MONTHS() 함수

  • 날짜 자료를 가지고 산술 연산(더하기 연산) 가능. 월(개월) 단위로 계산된다.
  • 유사한 함수로 MONTHS_BETWEEN() 함수가 있다. 빼기 연산.

TO_CHAR() 함수

  • 날짜, 숫자 자료를 문자 자료로 형변환하는 함수.
  • 서식 지정 내용에 따라서 여러가지 형태의 문자열로 변환할 수 있다.
  • 날짜 서식은 YYYY, MM, DD, DAY, HH24, MI, SS 등이 있다.
  • 숫자 서식은 9, 0, .(dot), ,(comma) 등의 서식 사용.

년도 출력시 2자리 사용하는 경우

- YY, RR
SELECT '2060-01-01'
        , '1905-01-01'
        , TO_CHAR(TO_DATE('2060-01-01'), 'YY/MM/DD')
        , TO_CHAR(TO_DATE('1905-01-01'), 'YY/MM/DD')
        , TO_DATE(TO_CHAR(TO_DATE('1905-01-01'), 'YY/MM/DD'))
        , TO_DATE(TO_CHAR(TO_DATE('1905-01-01'), 'YYYY/MM/DD'))
FROM dual;

숫자 출력시 서식 지정

SELECT 12345.67
    , TO_CHAR(12345.67, '99999')
    , TO_CHAR(12345.67, '99999.99')
    , TO_CHAR(12345.67, '99,999.99')
    , CONCAT('A',LTRIM(TO_CHAR(1, '0000'))) newNum
FROM dual;

TO_NUMBER() 함수

  • (숫자 형태의)문자 자료를 숫자 자료로 형변환하는 함수

TO_DATE() 함수

  • (날짜 형태의)문자 자료를 날짜 자료로 형변환하는 함수

NVL() 함수

  • NULL 값을 다른 값으로 대체해서 반환하는 함수
  • NULL 값을 가진 컬럼을 연산에 참여하는 경우 최종 결과 전체가 NULL이 된다. NULL 대신 연산 가능한 값으로 대체해야하는 경우가 있다.
  • 유사한 함수로 NVL2() 함수가 있다. NULL일때의 대체값과 NULL이 아닐때의 대체값이 별도 존재.

NVL2() 함수

  • NULL 값을 다른 값으로 대체해서 반환하는 함수. NULL이 아닌 경우에 대한 대체값도 있다.
  • NULL 값을 가진 컬럼을 연산에 참여하는 경우 최종 결과 전체가 NULL이 된다. NULL 대신 연산 가능한 값으로 대체해야하는 경우가 있다.
  • 유사한 함수로 NVL() 함수가 있다. NULL일때의 대체값만 존재.

DECODE() 함수

  • IF ~ ELSE IF ~ ELSE 문 역할을 하는 함수.
  • 특정 조건(일치하는 경우만 검사 가능)을 만족하는지에 따라서 다른 결과를 반환하는 함수.
  • DECOCE(컬럼, 비교값1, 결과값1, 비교값2, 결과값2,….) 형식으로 사용.
  • DECOCE(컬럼, 비교값1, 결과값1)
  • DECOCE(컬럼, 비교값1, 결과값1, 비교값2, 결과값2)
  • DECOCE(컬럼, 비교값1, 결과값1, 비교값2, 결과값2, 결과값3)

CASE ~ END 구문

  • IF ~ ELSE IF ~ ELSE 문 역할을 하는 구문.
  • 특정 조건을 만족하는지에 따라서 다른 결과를 반환하는 구문.
  • 기본 형식

연산자가 =인 경우

CASE 컬럼 WHEN 값1 THEN 결과1 WHEN 값2 THEN 결과2 WHEN 값3 THEN 결과3 … ELSE 결과 END

연산자가 =이 아닌 경우

CASE WHEN 조건식1 THEN 결과1 WHEN 조건식2 THEN 결과2 WHEN 조건식3 THEN 결과3 … ELSE 결과 END

정규식 함수

정규식 표현을 이용해서 검색, 치환 등을 진행하는 함수. 정규식만 잘 써도 레알 Geek스러울 수 있다..

REGEXP_LIKE() 함수

특정 패턴(정규표현식)과 매칭되는 결과를 반환하는 함수

REGEXP_REPLACE() 함수

특정 패턴(정규표현식)과 매칭되는 결과를 치환하는 함수

REGEXP_SUBSTR() 함수

특정 패턴(정규표현식)과 매칭되는 결과에서 부분 문자열을 반환하는 함수

복수행 함수

  • 여러개의 행을 투입해서 한 개의 결과를 반환하는 함수
  • 주의) NULL 값을 가진 자료는 복수행 함수 연산시 제외된다. 예를 들어, SUM() 함수 사용시 NULL 값이 들어오면 자동으로 제외된다.
  • COUNT(), SUM(), AVG(), MAX(), MIN() 등이 있다.
  • 주의) 함수 사용시 단일행 함수, 일반 컬럼과 복수행 함수를 혼용할 수 없다.

COUNT() 함수

  • 투입된 행의 갯수 반환하는 함수

SUM() 함수

  • 투입된 행의 값(숫자 자료형)들을 모두 합산한 결과 반환.

AVG() 함수

  • 투입된 행의 값(숫자 자료형)들의 평균 결과 반환.
  • 주의) NULL 값은 제외되기 때문에 평균 계산 결과가 다르게 나올 수 있다.

AVG() 함수

  • 투입된 행의 값(숫자 자료형)들의 평균 결과 반환.
  • 주의) NULL 값은 제외되기 때문에 평균 계산 결과가 다르게 나올 수 있다.

MAX(), MIN() 함수

  • 최대값, 최소값을 반환하는 함수

오라클에서 제공하는 검색용 메소드가 정말 많다… 데이터베이스를 설계하고 값을 저장하고 실제 쿼리문을 날려서 데이터를 뽑아오기 위해 검색 쿼리는 필수라고 할 수 있다. 그래서 그런지? SQL 쿼리 담당자들을 위해 많인 내장 함수를 제공 하는 것으로 보인다..

다음엔 오라클에서 제공하는 분석함수 및 서브쿼리에 대해 알아보는 걸로~

  • 오타나 잘못된 부분을 지적해주시면 감사히 생각하고 수정토록 하겠습니다 :)
  • 참고문헌
    서진수, 김도균 지음 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL .
    미크 지음, 윤인성 옮김 DB 성능 최적화를 위한 SQL 실전 가이드 SQL 레벨업 .