오라클 - 함수(Function)

 

함수(Function)

프로그램에서 반복적으로 사용되는 부분들을 분리하여 작성해 놓은 작은 서브 프로그램

호출하여 값을 전달하면 결과를 리턴하는 방식

 

DUAL 테이블

SYS 사용자가 소유하는 DUMMY(가상) 테이블

산술 연산의 결과나 가상 칼럼 등의 값을 한 번만 출력하려고 할 때 사용

SQL Function을 테스트하기위해 사용함

 

 

문자 처리함수

LENGTH

주어진 컬럼 값/문자열의 길이(문자 개수 반환)

오라클에서 VARCHAR2 한글 값은 3바이트로 인식

SELECT LENGTH('오라클'), LENGTHB('오라클') FROM DUAL;

 

INSERT

INSERT('문자열 ' | 컬럼명, '문자', 찾을 위치의 시작 값)

SELECT INSTR('AABAACAABBAA', 'B') FROM DUAL;
SELECT INSTR('AABAACAABBAA', 'B', 1) FROM DUAL;
-- 1번 인덱스부터 검색
SELECT INSTR('AABAACAABBAA', 'B', -1) FROM DUAL;
-- 음수의 절대값 만큼 왼쪽으로 이동 후 왼쪽으로 검색
SELECT INSTR('AABAACAABBAA', 'B', 1, 2) FROM DUAL;
-- 1번 인덱스부터 2번째 위치의 'B'를 검색
SELECT INSTR('AABAACAABBAA', 'B', -1, 2) FROM DUAL;
-- 맨 끝 인덱스부터 왼족으로 이동하며 2번째 위치의 'B'를 검색

 

SUBSTR

컬럼이나 문자열에서 지정한 위치부터 지정한 개수의 문자열을 잘라내어 반환

SUBSTR(STRING, POSITION, [LENGTH])

SELECT SUBSTR('SHOWMETHEMONEY', 5, 2) FROM DUAL; --ME
SELECT SUBSTR('SHOWMETHEMONEY', 7) FROM DUAL; -- 
SELECT SUBSTR('SHOWMETHEMONEY', -8, 3) FROM DUAL;
SELECT SUBSTR('쇼우 미더 머니', 2, 5) FROM DUAL;

 

LPAD / RPAD

주어진 컬럼이나 문자열에 임의의 문자를 왼쪽 / 오른쪽에 덧붙여 길이 N의 문자열을 반환

('문자열' | 컬럼명, 반환할 문자의 길이(바이크), [덧 붙이려는 문자열])

SELECT LPAD(EMAIL, 20) FROM EMPLOYEE; --오른쪽정렬
SELECT LPAD(EMAIL, 20, '#') FROM EMPLOYEE; -- 오른쪽정렬 빈공간 #
SELECT RPAD(EMAIL, 20, '#') FROM EMPLOYEE; -- 왼쪽정렬 빈공간#

 

 

 

LTRIM / RTIRM

주어진 컬럼이나 문자열의 왼쪽 혹은 오른쪽에서 지정한 STR에 포함 된 모든 문자를 제거한 나머지를 반환

('문자열' | 컬럼명, [제거하려는 문자열])

제거하려는 문자열 'ABC' 를 작성하였따면 ABC 전체를 찾는게아니고 A 찾고 B찾고 찾고 하나하나를 읽어서 찾는다.

 

SELECT LTRIM('000123456', '0') FROM DUAL;
SELECT LTRIM('123123KH123', '123') FROM DUAL;
SELECT LTRIM('5782KH', '0123456789') FROM DUAL;

 

TRIM

주어진 컬럼이나 문자열의 앞/뒤에 있는 지정한 문자를 제거

SELECT TRIM('   KH  ') FROM DUAL;
SELECT TRIM('Z' FROM 'ZZZKHZZZ') FROM DUAL;

 

 

LOWER / UPPER / INITCAP

소문자로 변경하거나, 대문자로 변경하거나 카멜표기법처럼 단어의 첫단어는 대문자로변경

SELECT LOWER('Welcome To My World') FROM DUAL; -- 소문자
SELECT UPPER('Welcome To My World') FROM DUAL; -- 대문자
SELECT INITCAP('wecome to my world') FROM DUAL; -- 카멜표기법처럼

 

CONCAT

칼럼의 문자 혹은 문자열을 두개 전달받아 하나로 합친후 반환 || 리터럴과같은 효과임

SELECT CONCAT('가나다라', 'ABCD') FROM DUAL;

 

REPLACE

칼럼의 문자 혹은 문자열에서 특정 문자를 지정한 문자로 바꾼 후 변환

REPLACE(STRING, STR1, STR2)

SELECT REPLACE('서울시 강남구 역삼동', '역삼동', '삼성동') FROM DUAL;

 

숫자 처리함수

ABS

절대값을 구하여 리턴하는 함수

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

 

MOD

두 수를 나누어서 나머지를 구하는 함수

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

 

ROUND

반올림하여 리턴하는 함수

ROUND(숫자 | 숫자로 된 컬럼, [위치])

SELECT ROUND(123.456) FROM DUAL; --123
SELECT ROUND(123.456, 1) FROM DUAL; -- 123.5

 

FLOOR 

무조건 내림 처리하는 함수

FLOOR(숫자 | 숫자로 된 컬럼)

SELECT FLOOR(123.456) FROM DUAL;
SELECT FLOOR(123.678) FROM DUAL;

 

CEIL

무조건 올림 처리하는 함수

SELECT CEIL(123.456) FROM DUAL;
SELECT CEIL(123.678) FROM DUAL;

 

 

날짜 처리 함수

 

SYSDATE 

시스템에 저장 되어 있는 날짜를 반환하는 함수 말그대로 현재 년.월.일.시.분.초 정보값 가져옴

 

MONTHS BETWEEN

두개의 날짜를 입력받아 개월 수의 차이를 숫자로 리턴하는 함수

SELECT
       EMP_NAME
     , HIRE_DATE
     , CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) || '개월차' AS "근무 개월 수 "
  FROM EMPLOYEE;

 

ADD_MONTHS

날짜에 숫자만큼 개월수를 더하여 날짜를 리턴

-- 현재 블로그 작성하는 날은 2021년 06월 28일임 
SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;

 

NEXT_DAY

기준 날짜에서 구하려는 요일의 가장 가까운 날짜를 리턴하는 함수

/*
 블로그 작성한 날은 2021년 06월 28일임
*/
SELECT NEXT_DAY(SYSDATE, '목요일') FROM DUAL;
/*
 1 => 일요일
 2 => 월요일
 3 => 화요일
 4 => 수요일
 5 => 목요일
 6 => 금요일
 7 => 토요일
*/
SELECT NEXT_DAY(SYSDATE, 5) FROM DUAL;

7월 1일 목요일 일치

 

LAST_DAY

해당 월의 마지막 날짜를 구하여 리턴함

-- 블로그 작성 날짜는 2021년 06월 28일임
SELECT LAST_DAY(SYSDATE) FROM DUAL;

 

EXTRACT

날짜의 년, 월, 일 따로따로 호출하는 함수

-- 블로그작성일 : 2021년 06월 28일
SELECT
       EXTRACT(YEAR FROM SYSDATE) AS "년" -- 년 추출
     , EXTRACT(MONTH FROM SYSDATE) AS "월" -- 월 추출
     , EXTRACT(DAY FROM SYSDATE) AS "일" -- 일 추출
     FROM DUAL;

 

 

 

형 변환함수

TO_CHAR

날짜 혹은 숫자형 데이터를 문자형 데이터로 변환하여 반환 하는 함수

 

SELECT TO_CHAR(1234, '99999') FROM DUAL; -- 5칸, 오른쪽 정렬, 빈칸 공백
SELECT TO_CHAR(1234, '00000') FROM DUAL; -- 5칸, 오른쪽 정렬, 빈칸 0
SELECT TO_CHAR(1234, 'L99999') FROM DUAL; -- 설정 된 나라의 화폐 단위
SELECT TO_CHAR(1234, '$99999') FROM DUAL; -- 달러

 

저장 되어있는 데이터에 정수값은 화폐단위로 형변환

SELECT
       EMP_NAME
     , TO_CHAR(SALARY,'L999,999,999')
  FROM EMPLOYEE;

 

날짜 데이터 포맷 형식 변경 하기

SELECT TO_CHAR(SYSDATE, 'PM HH24: MI:SS') FROM DUAL; -- 시간 00~ 23 으로 표시
SELECT TO_CHAR(SYSDATE, 'AM HH: MI:SS') FROM DUAL;   -- 시간 0~11 으로표시
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-FMDD DAY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YEAR, Q') || '분기' FROM DUAL;

 

TO_DATE

문자, 숫자 형을 데이터를 날짜로 변경하는 형변환 함수

 

아래에서 년도를설정할때 YYYY와 RRRR이 있다.

YYYY 로설정하면 현재세기(2000) 년으로 설정되고있고,

RRRR은 50이상이면 이전세기(1900년), 50 미만이면 현재세기(2000년) 으로 적용된다.

SELECT TO_DATE('20100101', 'YYYYMMDD') FROM DUAL;
SELECT TO_DATE(20100101, 'YYYYMMDD') FROM DUAL;
SELECT TO_CHAR(TO_DATE('980630', 'RRMMDD'), 'RRRRMMDD') FROM DUAL;
SELECT TO_CHAR(TO_DATE('201118', 'RRMMDD'), 'RRRRMMDD') FROM DUAL;

NULL 처리 함수

 

 

NVL

NVL(컬럼명, 컬럼값이 NULL일때 바꿀 값)

NULL로 되어 있는 컬럼의 값을 인자로 지정한 숫자 혹은 문자로 변경하여 반환하는 함수

SELECT
      BONUS
      , NVL(BONUS, 0)
 FROM EMPLOYEE;

왼쪽칸은 원래 데이터 표현이고

오른쪽칸은 NVL 함수이용했을시 나오는화면

 

NVL2

NVL2(컬럼명, 바꿀값1, 바꾸값2)

해당 컬럼의 값이 있으면 바꿀값1로 변경, 해당컬럼이 NULL이면 바꿀값2로 변경

SELECT
       EMP_NAME
     , BONUS 현재값
     , NVL2(BONUS, 0.7, 0.5)  "변경 될 보너스 값"
  FROM EMPLOYEE;

선택 함수

개인적으로 가장중요한 부분이아닌가싶습니다

DECODE

비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환

DECODE(표현식, 조건1, 결과2, 조건2, 결과2, 모든조건불일치시 결과)

SELECT
       EMP_NAME
     , EMP_NO
     , DECODE(SUBSTR(EMP_NO, 8, 1), '1' ,'남', '2' , '여')
  FROM EMPLOYEE;

CASE

비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환

CASE WHEN 조건1 THEN 결과1

        WHEN 조건2 THEN 결과2

        WHEN 조건3 THEN 결과3

        ELSE 결과

 END

 

SELECT
       EMP_NAME
       , EMP_NO
       , CASE
            WHEN SUBSTR(EMP_NO,8,1) = 1 THEN '남'
            WHEN SUBSTR(EMP_NO,8,1) = 2 THEN '여'
         END AS "성별"
         FROM EMPLOYEE;

 

 

 

SELECT 
       EMP_NAME
     , SALARY
     , CASE
        WHEN SALARY > 5000000 THEN '1등급'
        WHEN SALARY > 3500000 THEN '2등급'
        WHEN SALARY > 2000000 THEN '3등급'
        ELSE '4등급'
       END AS "등급"
  FROM EMPLOYEE;

 

그룹 함수 

하나 이상의 행을 그룹으로 묶어 연산하며 총합, 평균 등의 하나의 결과를 반환하는 함수

 

SUM

합계를 구하여 리턴

SELECT
      SUM(SALARY)
FROM EMPLOYEE;

 

-- D5 부서 지원들의 보너스 포함 연봉의 합 조회
SELECT
    SUM(SALARY + (SALARY * NVL(BONUS, 0)) * 12) 
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5';

 

 

AVG

평균을 구하여 리턴 하는 함수

-- 전사원의 급여 평균 조회
SELECT
       AVG(SALARY)
  FROM EMPLOYEE;

 

MIN

컬럼에서 가장 작은 값 리턴

취급하는 자료형은 상관없다.

SELECT
       MIN(EMAIL)
     , MIN(HIRE_DATE)
     , MIN(SALARY)
  FROM EMPLOYEE;

 

MAX

컬럼에서 가장 큰 값 리턴

취급하는 자료형은 상관없다.

SELECT
       MAX(EMAIL)
     , MAX(HIRE_DATE)
     , MAX(SALARY)
  FROM EMPLOYEE;

 

COUNT

테이블 조건을 만족하는 행의 개수 반환

-- COUNT(*|컬럼명) : 행 갯수를 헤아려서 리턴
-- COUNT(DISTINCT 컬럼명) : 중복을 제거한 행 갯수를 리턴
-- COUNT(*) : NULL 포함한 전체 행 갯수를 리턴
-- COUNT(컬럼명) : NULL 제외한 실제 값이 기록 된 행 갯수를 리턴
SELECT
       COUNT(*)
     , COUNT(DEPT_CODE)
     , COUNT(DISTINCT DEPT_CODE)
  FROM EMPLOYEE;