Oracle.SQL함수

 

   

Oracle SQL 함수 완전 정복: 필수 데이터 조작 가이드 🧙‍♂️

안녕하세요! 데이터를 다루는 데 있어 SQL 함수는 정말 강력한 도구입니다. 이번 포스팅에서는

Oracle 데이터베이스에서 데이터를 효율적으로 조작하고 분석하는 데 필수적인 SQL 함수들

을 예제와 함께 완벽하게 정리해 드릴게요.

1. SQL 함수란?

SQL 함수는 특정 작업을 수행하고 결과를 반환하는 데이터 처리의 기본 도구입니다. RDBMS마다 차이가 있지만, Oracle에서는 다양한 내장 함수를 통해 데이터를 가공하고 분석할 수 있습니다.

✅ 함수 종류
  ▶ 단일행 함수 (Single-Row Functions)
  ▶ 그룹 함수 (Group Functions) → 다음 포스팅에서 다룰 예정!
  ▶ 숫자 / 문자 / 날짜 / 기타 함수로도 분류 가능  

2. 단일행 함수 (Single-Row Functions)

단일행 함수는 각 행에 대해 처리를 수행하고, 행 단위로 결과를 반환합니다. 즉, 행의 개수만큼 결과가 반환됩니다.

2.1 숫자 타입 함수

📌 CEIL (올림)

소수값을 가지는 컬럼에 대해 올림 처리된 정수값을 얻을 수 있습니다. 예를 들어, 92.1과 같은 소수도 CEIL을 적용하면 93이 됩니다.

SELECT stu_id, stu_name, CEIL(stu_avg) AS stu_avg
FROM stu_view;

STU_ID	STU_NAME	STU_AVG
2021001011	배순하	93
2021001009	정민기	91
2021001004	최진소	91
2021001003	이두원	90
2021001004	최진소	89
2021001008	이진화	89
2021001002	유인아	89
2021001004	최진소	87

📌 FLOOR (내림)

소수값을 가지는 컬럼에 대해 내림 처리된 정수값을 얻을 수 있습니다. 예를 들어, 92.9와 같은 소수도 FLOOR를 적용하면 92가 됩니다.

SELECT stu_id, stu_name, FLOOR(stu_avg) AS stu_avg
FROM stu_view;

STU_ID	STU_NAME	STU_AVG
2021001011	배순하	92
2021001009	정민기	91
2021001004	최진소	90
2021001003	이두원	89
2021001004	최진소	88
2021001008	이진화	88
2021001002	유인아	88
2021001004	최진소	87

📌 ROUND (반올림)

소수값을 가지는 컬럼에 대해 소수 몇째 자리에서 반올림한 값을 적용할 수 있습니다.

-- 소수 첫째 자리에서 반올림 (정수)
SELECT stu_id, stu_name, ROUND(stu_avg) AS stu_avg
FROM stu_view;

-- 소수 n째 자리에서 반올림
SELECT stu_id, stu_name, ROUND(stu_avg, 1) AS stu_avg_1, ROUND(stu_avg, 2) AS stu_avg_2
FROM stu_view;

참고: SQLD 기출문제에서 반올림 함수로 알맞은 것은 `ROUND`입니다.

📌 ABS (절댓값)

파라미터의 숫자값에 대해 절댓값(Absolute)을 적용해줍니다.

SELECT ABS(-30) AS abs_minus_30, ABS(15) AS abs_plus_15
FROM dual;

ABS_MINUS_30	ABS_PLUS_15
30	15

📌 MOD (나머지)

`MOD(m, n)` 형태로, $m$을 $n$으로 나누었을 때의 나머지 값을 리턴합니다.

SELECT MOD(10, 3) AS remainder
FROM dual;

REMAINDER
1

2.2 문자 타입 함수

📌 UPPER / LOWER

`UPPER`는 파라미터 내부 알파벳을 전부 대문자로 변환하고, `LOWER`는 전부 소문자로 변환시켜줍니다.

SELECT UPPER('SQLDeveloper') AS upper_case, LOWER('SQLDeveloper') AS lower_case
FROM dual;

UPPER_CASE	LOWER_CASE
SQLDEVELOPER	sqldeveloper

📌 REPLACE

문자열(VARCHAR2) 데이터에서 특정 문자를 변경할 수 있습니다.

SELECT REPLACE('재미없는 데이터베이스', '없', '있') AS replaceA
, REPLACE('데구이구터구베구이구스구 구수구업구', '구', '') AS replaceB
FROM dual;

REPLACEA	REPLACEB
재미있는 데이터베이스	데이터베이스 수업

📌 TRIM / LTRIM / RTRIM

문자열의 공백을 제거하는 함수들입니다.

   
• `LTRIM`: 문자열의 좌측에 존재하는 공백들 또는 지정한 문자를 제거합니다.
   
• `RTRIM`: 문자열의 우측에 존재하는 공백들 또는 지정한 문자를 제거합니다.
   
• `TRIM`: 문자열 좌, 우에 존재하는 공백들을 제거합니다.
 

SELECT REPLACE(' 공백을 제거하자!! ', ' ', '') AS replaceEx  -- 공백을 전부 제거
, LTRIM(' 공백을 제거하자!! ') AS ltrimEx         -- 좌측 공백 제거
, RTRIM(' 공백을 제거하자!! ') AS rtrimEx         -- 우측 공백 제거
, TRIM(' 공백을 제거하자!! ') AS trimEx           -- 좌우 공백 제거
FROM DUAL;

REPLACEEX	LTRIMEX	RTRIMEX	TRIMEX
공백을제거하자!!	공백을 제거하자!!	공백을 제거하자!!	공백을 제거하자!!

📌 SUBSTR (문자열 자르기)

문자열(VARCHAR2) 데이터에서 문자열을 자를 수 있습니다.

`SUBSTR(문자열, 인덱스, 길이)` 형태로, 해당 문자열의 인덱스부터 길이만큼 자른 문자열을 리턴합니다. Oracle 문자열 인덱스는 1부터 시작하며, 음수 인덱스는 문자열의 끝에서부터 카운트합니다.

SELECT SUBSTR('ABCDEFG', 1, 4) AS substr1  -- 'ABCDEFG'의 인덱스 1부터 4개만큼 선택 -> ABCD
, SUBSTR('ABCDEFG', 4, 3) AS substr2  -- 'ABCDEFG'의 인덱스 4부터 3개만큼 선택 -> DEF
, SUBSTR('ABCDEFG', -4, 2) AS substr3 -- 'ABCDEFG'의 뒤에서 4번째부터 2개만큼 선택 -> DE
, SUBSTR('ABCDEFG', -1, 2) AS substr4 -- 'ABCDEFG'의 뒤에서 1번째부터 2개만큼 선택 -> G (길이 2를 요청했지만 끝이 1자리라 G만 나옴)
, SUBSTR('ABCDEFG', 5) AS substr5    -- 'ABCDEFG'의 인덱스 5부터 끝까지 선택 -> EFG
FROM DUAL;

SUBSTR1	SUBSTR2	SUBSTR3	SUBSTR4	SUBSTR5
ABCD	DEF	DE	G	EFG

📌 LPAD / RPAD (문자열 채우기)

숫자형 문자인 경우 문자 길이를 맞추는 경우가 있습니다.

`LPAD(문자열, 길이, 문자)`와 같은 형태로 `LPAD`는 왼쪽, `RPAD`는 오른쪽부터 주어진 길이만큼 지정한 문자를 채웁니다.

SELECT LPAD(9, 3, '0') AS lpad1  -- 9를 총 3자리로 만들고, 빈자리를 왼쪽에 '0'으로 채움 -> 009
, LPAD(31 , 3, '0') AS lpad2 -- 31을 총 3자리로 만들고, 빈자리를 왼쪽에 '0'으로 채움 -> 031
, RPAD(123, 5, '0') AS rpad1 -- 123을 총 5자리로 만들고, 빈자리를 오른쪽에 '0'으로 채움 -> 12300
, RPAD(12 , 5, '0') AS rpad2 -- 12를 총 5자리로 만들고, 빈자리를 오른쪽에 '0'으로 채움 -> 12000
FROM dual;

LPAD1	LPAD2	RPAD1	RPAD2
009	031	12300	12000

2.3 날짜 타입 함수

📌 TO_CHAR (날짜를 문자로 변환)

DATE 및 TIMESTAMP 타입의 날짜 데이터의 경우 출력되는 날짜의 형태(포맷)를 원하는 형태로 변경할 수 있습니다.

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS dateA
, TO_CHAR(SYSDATE, 'YYYY.MM.DD AM HH:MI:SS') AS dateB
FROM dual;

DATEA	DATEB
2023/05/21 15:05:09	2023.05.21 오후 03:05:09

날짜 포맷 요소 예시:

   
• 세기 및 년도: `AD`, `BC`, `CC`, `YEAR`, `YYYY`, `YY`, `Y`
   
• 분기: `Q`
   
• 월: `MONTH`, `MON`, `MM`, `RM`
   
• 주차: `W`, `WW`, `IW`
   
• 일, 일차: `DDD`, `DD`, `D`, `J`
   
• 요일: `DAY`, `DY`, `D`
   
• 오전/오후: `AM`, `PM`, `A.M.`, `P.M.`
   
• 시간: `HH`, `HH12`, `HH24`
   
• 분: `MI`
   
• 초: `SS`, `SSSS` (자정 이후의 초 0 - 86399)
 

📌 TO_DATE / TO_TIMESTAMP (문자를 날짜 타입으로 변환)

VARCHAR2 타입으로 저장된 생년월일(예: 980713), 글작성일(예: 23/05/21 13:12) 등의 데이터를 날짜 타입으로 변경할 수 있습니다. 값의 날짜 형식에 대한 날짜 포맷을 정확히 맞춰주어야 합니다.

SELECT TO_DATE('980713', 'YYMMDD') AS dateA
, TO_TIMESTAMP('23/05/21 13:12', 'YY/MM/DD HH24:MI') AS dateB
FROM dual;

DATEA	DATEB
98/07/13	23/05/21 13:12:00.000000000

📌 날짜 연산 (`SYSDATE`, `ADD_MONTHS`)

DATE 및 TIMESTAMP 타입의 날짜 데이터에 N일 전/후, N달 전/후, N년 전/후 등의 날짜 연산을 할 수 있습니다. `SYSDATE`는 현재 날짜와 시간을 반환합니다.

-- 현재부터 N일 후/전 날짜
SELECT SYSDATE + 11 AS date_after_11_days
, SYSDATE - 7 AS date_before_7_days
FROM dual;

-- 현재부터 N달 후/전 날짜 (ADD_MONTHS)
SELECT ADD_MONTHS(SYSDATE, 3) AS date_after_3_months
, ADD_MONTHS(SYSDATE, -5) AS date_before_5_months
FROM dual;

-- 현재부터 N년 뒤 날짜 (ADD_MONTHS 함수를 이용하여 년도 계산)
SELECT ADD_MONTHS(SYSDATE, 2 * 12) AS date_after_2_years -- ADD_YEAR와 같은 함수는 없음
FROM dual;

2.4 기타 함수

📌 DUAL 테이블

`DUAL`은 하나의 행만을 포함하고 있는 테이블로, `FROM dual`을 이용하여 데이터베이스로부터 단일행 결과를 얻을 수 있습니다.

주로 현재 시간 확인, 함수 실행 결과 확인, WHERE 조건식 확인, 랜덤 값 생성 등에 사용됩니다.

SELECT SYSDATE FROM dual;           -- 현재 시간 구하기
SELECT DBMS_RANDOM.VALUE FROM dual; -- 랜덤값 구하기 (0부터 1 사이의 랜덤 숫자 생성)

📌 DECODE (값 변환)

컬럼의 특정값을 다른 값으로 바꿔서 출력할 수 있습니다. `CASE`문과 유사한 용도로 사용됩니다.

`DECODE(컬럼, 값, 변환값)` 형태로 사용하며, 여러 개의 값에 대해 변환하려면 `DECODE(컬럼, 값1, 변환값1, 값2, 변환값2, ...)`와 같이 값과 변환값의 쌍을 추가해주면 됩니다.

SELECT bank_name
, DECODE(bank_year, null, 0, bank_year) AS bank_year -- bank_year 값이 null이면 0으로 변환, 그 외 나머지는 bank_year 값 유지
FROM bank;

BANK_NAME	BANK_YEAR
강잔웅	0
김나농	28
백성농	0

📌 NVL (NULL 값 처리)

컬럼에 존재하는 `null` 값에 대해 값을 변화시킬 수 있습니다.

`NVL(컬럼, 변환값)` 의 형태로, 컬럼에 저장된 데이터가 `null`값인 경우 변환값으로 변환하여 출력합니다.

SELECT bank_id
, bank_name
, bank_account
, bank_money
, NVL(bank_year, 0) AS bank_year -- bank_year가 null값인 경우 0으로 변경
FROM bank
WHERE bank_year IS NULL;

BANK_ID	BANK_NAME	BANK_ACCOUNT	BANK_MONEY	BANK_YEAR
730124-1429563	백성농	511-106103-10223-44	216570000	0
980026-2724068	이경호	837-4313-51044-32	422820000	0

    NULL 값 체크 시 주의사항: `WHERE bank_year = null`과 같은 방식으로는 조건 검색이 불가능합니다. `IS NULL`과 `IS NOT NULL`을 이용해야 합니다.
   

     
• `WHERE bank_year IS NULL`: `bank_year`가 `null`인 데이터를 조회합니다.
     
• `WHERE bank_year IS NOT NULL`: `bank_year`가 `null`이 아닌 데이터를 조회합니다.
   

 

📌 NULLIF (특정 값 NULL로 변환)

컬럼 내 저장된 데이터가 특정값인 경우 `null`값으로 변환시킵니다.

`NULLIF(컬럼, 특정값)` 의 형태로, 컬럼에 저장된 데이터가 특정값인 경우 `null`값으로 변환하여 출력합니다.

SELECT NULLIF(100, 100) FROM dual; -- 100과 100이 같으므로 NULL 반환
SELECT NULLIF('A', 'B') FROM dual;  -- 'A'와 'B'가 다르므로 'A' 반환

📌 DISTINCT (중복 제거)

컬럼 내 저장된 값에서 중복된 것이 있다면 하나만 조회되도록 합니다.

SELECT DISTINCT stu_name
FROM students;

STU_NAME
배순하
정민기
최진소
이두원
이진화
유인아

---

📚 마무리 정리

이 글에서는 Oracle SQL에서 가장 기본적이고 자주 사용하는 단일행 함수들을 예제와 함께 알아보았습니다. 실제 업무에서 데이터를 전처리하거나 보고서를 작성할 때 위 함수들을 적절히 조합하면 복잡한 로직도 간단하게 구현할 수 있습니다.