The Debugging Chronicles : "코드의 미학"
06 데이터 처리와 가공을 위한 오라클 함수 본문
06-1 오라클 함수
함수란?
x와 y 변수가 존재하고 x 값이 변하면 그 변화에 따라 어떤 연산 또는 가공을 거쳐 y값도 함께 변할 때 이 y를 함수라고 한다
오라클 함수에서는 특정 결과 데이터를 얻기 위해 어떤 값이나 데이터를 입력하는데 그 값에 따라 고공 또는 여산의 과정을 거쳐 결과 값이 나온다. 즉 오라클 함수는 특정한 결과 값을 얻기 위해 데이터를 입력할 수 있는 특수 명령어를 의미
오라클 함수의 종류
내장 함수 built-in-function
사용자 정의 함수 user-defined -function
내장함수의 종류
입력 방식에 따라 데이터 처리에 사용하는 행이 나뉜다.
데이터가 한 행씩 입력되고 입력된 한 행당 결과가 하나씩 나오는 함수를 단일행 함수 single - row function
여러행이 입력되어 하나의 행으로 결과가 반환되는 함수를 다중행 함수 mutiple-row function
06-2 문자 데이터를 개공하는 문자 함수
문자 함수
문자 데이터를 가공하거나 문자 데이터로부터 특정 결과를 얻고자 할때 사용하는 함수
대, 소문자를 바꿔주는 UPPER , LOWER, INITCAP 함수
| 함수 | 설명 |
| UPPER(문자열) | 괄호 안 문자 데이터를 모두 대문자로 변환하여 반환 |
| LOWER(문자열) | 괄호 안 문자 데이터를 모두 소문자로 변환하여 반환 |
| INITCAP(문자열) | 괄호 안 문자 데이터 중 첫 글자는 대문자로 나머지 문자는 소문자로 변환 후 반환 |
SELECT *
FROM EMP
WHERE UPPER(ENAME) = UPPER('scott');
-- UPPER 함수로 문자열 비교하기(사원 이름이 SCOTT인 데이터 찾기)
문자열 길이를 구하는 LENGTH 함수
특정 문자열의 길이를 구할 때 사용
-- 선택한 열의 문자열 길이 구하기
SELECT ENAME, LENGTH(ENAME)
FROM EMP;
-- 사원 이름의 길이가 5 이상인 행 출력하기
SELECT ENAME, LENGTH(ENAME)
FROM EMP
WHERE LENGTH(ENAME) >= 5;
문자열 일부를 추출하는 SUBSTR함수
주민등록번호 중 생년월일 앞자리만 필요하거나 전화번호의 마지막 네 자리 숫자만 추출하는 경우와 같이 문자열 중 일부를 추출할때 사용
| 함수 | 설명 |
| SUBSTR(문자열 데이터, 시작 위치 , 추출 길이) | 문자열 데이터의 시작 위치부터 추출 길이 만큼 추출합니다. 시작 위치가 음수 일 경우에는 마지막 위치 부터 거슬러 올라간 위치에서 시작된다 |
| SUBSTR(문자열 데이터 , 시작 위치) | 문자열 데이터의 시작 위치부터 문자열 데이터 끝까지 추출한다. 시작 위치가 음수일 경우 마지막 위치부터 거슬러 올라간 위치에서 끝까지 추출 |
-- SUBSTR 함수를 사용하는 예
SELECT JOB, SUBSTR(JOB, 1, 2),SUBSTR(JOB, 3, 2), SUBSTR(JOB, 5)
FROM EMP;
-SUBSTR 함수와 다른 함수 함께 사용하기
-- SUBSTR 함수 안에 다른 함수(LENGTH)함께 사용하기
SELECT JOB,
SUBSTR(JOB, -LENGTH(JOB)),
SUBSTR(JOB, -LENGTH(JOB, 2)),
SUBSTR(JOB, -3)
FROM EMP;
문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR 함수
문자열 데이터 안에 특정 문자나 문자열이 어디에 포함되어 있는지를 알고자 할 때 INSTR 함수
INSTR([대상 문자열 데이터(필수)],
[위치를 찾으려는 부분 문자(필수)],
[위치 찾기를 시작할 대상 문자열 데이터 위치 (선택, 기본값은 1)],
[시작 위치에서 찾으려는 문자가 몇번째인지 지정(선택, 기본값은 1)])
-- INSTR 함수로 문자열 데이터에서 특정 문자열 찾기
SELECT INSTR('HELLO, ORACLE!', 'L')AS INSTR_1,
INSTR('HELLO, ORACLE!', 'L',5)AS INSTR_2,
INSTR('HELLO, ORACLE!', 'L',2,2)AS INSTR_3
FROM DUAL;
--INSTR 함수로 사원 이름에 문자 S가 있는 행 구하기
SELECT*
FROM EMP
WHERE INSTR(ENAME, 'S')>0;
-- LIKE 연산자로 사원 이름에 문자 S가 있는 행 구하기
SELECT*
FROM EMP
WHERE ENAME LIKE '%S%'
특정 문자를 다른 문자로 바꾸는 REPLACE 함수
REPLACE 함수는 특정 문자열 데이터에 포함된 문자를 다른 문자로 댕체할 경우에 유용한 함수
REPLACE([문자열 데이터 또는 열 이름(필수)],[찾는 문자(필수)], [대체할 문자(선택)])
SELECT '010-1234-5678' AS REPLACE BEFORE,
REPLACE('010-1234-5678','-',' ')AS REPLACE_1, -- 문자를 한칸 공백으로 바꾸어 출력
REPLACE('010-1234-5678','-',)AS REPLACE_2, -- 대체할 문자를 지정하디 않아 - 문자가 삭제되어 출력
FROM DUAL;
데이터의 빈 공간을 특정 문자로 채우는 LPAD,RPAD 함수
LPAD - LEFT PADDING ; 남은 빈공간을 왼쪽에 채움
RPAD - RIGHT PADDING ; 남은 빈공간을 오른쪽에 ㅇ채움
데이터와 자릿수를 지정한 후 데이터 길이가 지정한 자릿수보다 작을 경우에 나머지 공간을 특정 문자로 채우는 함수
만약 빈공간에 채울 문자를 지정하지 않으면 빈공간의 자릿 수 만큼 공백 문자로 띄운다
LAPD([문자열 데이터 또는 열이름(필수)] , [데이터의 자릿수(필수)] , [빈 공간에 채울 문자(선택)])
RAPD([문자열 데이터 또는 열이름(필수)] , [데이터의 자릿수(필수)] , [빈 공간에 채울 문자(선택)])
-- LPAD, RPAD 함수 사용하여 출력하기
SELECT 'ORACLE'
LPAD('ORACLE',10, '#') AS LPAD_1, --결과 : ####Oracle
RPAD('ORACLE',10, '*') AS RPAD_1, --결과 : Oracle****
LPAD('ORACLE',10) AS LPAD_2, -- 결과 : Oracle
RPAD('ORACLE',10) AS RPAD_2, -- 결과 :Oracle
FROM DUAL;
--RPAD 함수를 사용하여 개인정보 뒷자리 * 표시로 출력하기
SELECT
RPAD('971225-',14,'*') AS RDAP_JMNO, -- 결과 : 971225-********
RPAD('010-1234-',13,'*') AS RDAP_PHONE -- 결과 : 010-1234-****
FROM DUAL;
두 문자열 데이터를 함치는 CONCAT 함수
두 개의 문자열 데이터를 하나의 데이터로 연결해 주는 역할
SELECT CONCAT(EMPNO,ENAME), -- 결과 : 7788SCOTT
CONCAT(EMPNO, CONCAT(' : ' , ENAME)) -- 결과 : 7788 : SCOTT
FROM EMP
WHERE ENAME = 'SCOTT';
특정 문자를 지우는 TRIM,LTRIM,RTRIM함수
TRIM
원본 문자열 데이터를 제외한 나머지 데이터는 모두 생량할 수 있다
삭제할 문자가 생략될 경우에 기본적으로 공백을 제거
삭제 옵션은 왼쪽에 있는 글자를 지우는 LEADING , 오른쪽 TRAILING,양쪽 BOTH
TRIM([삭제 옵션(선택)] [삭제할 문자(선택)] FROM [원본 문자열 데이터(필수)])
LTRIM,RTRIM
각 각 왼쪽, 오른쪽 의 지정 문자를 삭제하는데 사용
문자를 하나만 지정하는 것이 아니라 여러 문자 지정이 가능
LTRIM([원본 문자열 데이터(필수)] , [삭제할 문자 집합(선택)])
-- 원본 문자열의 왼쪽에서 삭제할 문자열을 지정합니다(삭제할 문자열을 지정하지 않으면 공백이 삭제됨)
RTIRM([원본 문자열 데이터(필수)] , [삭제할 문자 집합(선택)])
--원본 문자열의 오른쪽에서 삭제할 문자열을 지정합니다(삭제할 문자열을 지정하지 않으면 공백이 삭제됨)
SELECT '[' || TRIM(' _ _ Oracle _ _ ' || ']' AS TRIM, -- [_ _ Oracle _ _]
'[' || TRIM(LEADING FROM ' _ _ Oracle _ _ ' || ']' AS TRIM_LEADING, -- [_ _ Oracle _ _]
'[' || TRIM(TRAILING FROM ' _ _ Oracle _ _ ' || ']' AS TRIM_TRAILING, -- [_ _ Oracle _ _]
'[' || TRIM(BOTH FROM ' _ _ Oracle _ _ ' || ']' AS TRIM_BOTH -- [_ _ Oracle _ _]
FROM DUAL;
--TRIM,LTRIM, RTRIM 사용하여 문자열 출력하기
SELECT '[' || TRIM(' _Oracle_ ' || ']' AS TRIM -- [_Oracle_]
'[' || LTRIM(' _Oracle_ ' || ']' AS LTRIM -- [_Oracle_ ]
'[' || LTRIM('<_Oracle_>','_<' || ']' AS LTRIM2 -- [_Oracle_>]
'[' || RTRIM(' _Oracle_ ' || ']' AS RTRIM -- [ _Oracle_]
'[' || LTRIM('<_Oracle_>','>_' || ']' AS LTRIM2 -- [<_Oracle]
FROM DUAL;06-3 숫자 데이터를 연산하고 수치를 조정하는 숫자 함수
특정 위치에서 반올림하는 ROUND 함수 - 지정된 숫자의 특정 위치에서 반올림한 값을 반환
ROUND([숫자(필수)], [반올림 위치(선택)])
특정 위치에서 버리는 TRUNC 함수 - 지정된 숫자의 특정 위치에서 버림한 값을 반환
TRUNC([숫자 (필수)], [버림 위치(선택)])
특정 위치에서 숫자를 버림한 결과를 출력하는데 사용, 버림 위치를 지정하지 않을 경우 소수점 첫번째 자리에서 버림
지정한 숫자와 가까운 정수를 찾는 CEIL, FLOOR함수 - 지정된 숫자보다 큰 정수 / 작은 정수 중 가장 작은 정수/ 큰 정수를 반환
CEIL([숫자(필수)])
FLOOR([숫자(필수)])
숫자를 나눈 나머지 값을 구하는 MOD 함수 - 지정된 숫자를 나눈 나머지 값을 반환
MOD([나눗셈 될 숫자(필수)], [나눌 숫자(필수)])
특정 숫자를 나누고 그 나머지를 출력하는 함수
06-4 날짜 데이터를 다루는 날짜 함수
| 연산 | 설명 |
| 날짜 데이터 + 숫자 | 날짜 데이터 보다 숫자만큼 일수 이후의 날짜 |
| 날짜 데이터 - 숫자 | 날짜 데이터 보다 숫자만큼 일수 이전의 날짜 |
| 날짜 데이터 - 날짜 데이터 | 두 날짜 데이터 간의 일수 차이 |
| 날짜 데이터 + 날짜 데이터 | 연산 불가, 지원하지 않음 |
몇 개월 이후 날짜를 구하는 ADD_MONTHS 함수
ADD_MONTHS([날짜 데이터(필수)] , [더할 개월 수(정수) (필수)])
특정 데이터에 입력한 개월수 만큼의 이후 날짜를 출력
두 날짜 간의 개월 수 차이를 구하는 MONTHS_BETWEEN 함수
HONTHS_BETNENC[날짜데이터1(필수)], [날짜데이터2(필수)])
돌아오는 요일, 달의 마지막 날짜를 구하는 NEXT_DAY, LAST_DAY함수
NEXT_DAY([날짜데이터(필수)], [요일문자(필수)])
LAST_DAY([날짜데이터(필수)])
날짜의 반올림, 버림을 하는 ROUND, TRUNC함수
--ROUND 함수 사용하여 날짜 데이터 출력하기
SELECT SYSDATE,
ROUND (SYSDATE,'CC') AS FORMAT_CC,
ROUND (SYSDATE, 'MY") SA FORMAT_MY,
ROUND (SYSDATE, 'Q') AS FORMAT_O,
ROUND (SYSDATE, D' DD' AS FORMAT_DDD,
ROUND(SYSDATE, 'HH') AS FORMATH_H
FROM DUAL;
--TRUNC 함수 사용하여 날짜 데이터 출력하기
SELECT SYSDATE,
TRUNC (SYSDATE, 'CC') SA FORMAT_C,
TRUNC (SYSDATE, ' M Y AS FORMAT_MY, 04
TRUNC (SYSDATE, 'Q') AS FORMAT_Q,
TRUNC (SYSDATE, 'DDD' AS FORMAT_DDD,
TRUNC (SYSDATE, "HH") SA FORMAT H_
FROM DUAL;06-5 자료형을 변환하는 형 변환 함수
지정된 자료형을 필요에 따라 바꿔주어야할 때 각 데이터에 지정된 자료형을 바꿔주는 함수를 형변환 함수라고 한다.
-- 숫자와 문자열 (숫자) 더하여 출려하기
SELECT EMPNO, ENAME, EMPNO + '500'
FROM EMP
WHERE ENAME = 'SCOTT';
-- 문자열(문자)와 숫자 더하여 출력하기
SELECT 'ABCD' + EMPNO, EMPNO
FROM EMP
WHERE ENAME = 'SCOTT';
날짜, 숫자 데이터를 문자 데이터로 변환하는 TO_CHAR 함수
TO_CHAR([날짜데이터(필수)],' [출력되길원하는문자형태(필수)]')
문자 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수
문자 데이터를 날짜 데이터로 변환하는 TO_DATE 함수
06-6 NULL 처리 함수
NVL 함수의 기본 사용법
// 기본 형식
NVL([NULL인지 여부를 검사할 데이터 또는 열(필수)], [앞의 데이터가 NULL일 경우 반환할 데이터(필수)])
// 열 또는 데이터를 입력하여 해당 데이터가 NULL이 아닐 경우 데이터를 그대로 반환하고, NULL인 경우 지정한 데이터로 반환한다.
NVL2 함수의 기본 사용법
// 기본 형식
NVL([NULL인지 여부를 검사할 데이터 또는 열(필수)], [앞의 데이터가 NULL이 아닐 경우 반환할 데이터 또는 계산식(필수)],
[앞의 데이터가 NULL일 경우 반환할 데이터(필수)])
// 열 또는 데이터를 입력하여 해당 데이터가 NULL이 아닐 때와 NULL일 때 출력 데이터를 각각 지정한다.06-7 상황에 따라 다른 데이터를 반환하는 DECODE 함수와 CASE문
DECODE 함수
기준이 되는 데이터를 먼저 지정한 후 해당 데이터 값에 따라 다른 결과를 내보내는 함수이다.
기준 데이터를 반드시 명시해야한다.
// 기본 형식
DECODE([검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과],
[조건1], [데이터가 조건1과 일치할 때 반환할 결과],
[조건2], [데이터가 조건2와 일치할 때 반환할 결과],
...
[조건n], [데이터가 조건n과 일치할 때 반환할 결과],
[위 조건1~조건n과 일치한 경우가 없을 때 반환할 결과])CASE 문
특정 조건에 따라 반환할 데이터를 설정할 때 사용한다.
각 조건에 사용하는 데이터가 서로 상관없어도 된다.
기준 데이터 값이 같은(=) 데이터 외에 다양한 조건을 사용할 수 있다.
// 기본 형식
CASE([검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(선택)],
WHEN [조건1] THEN [조건1의 결과 값이 true일 때, 반환할 결과],
WHEN [조건2] THEN [조건2의 결과 값이 true일 때, 반환할 결과],
...
WHEN [조건n] THEN [조건n의 결과 값이 true일 때, 반환할 결과],
ELSE [위 조건1~조건n과 일치한 경우가 없을 때 반환할 결과])'DBMS > Do it! 오라클로 배우는 데이터 베이스 입문' 카테고리의 다른 글
| 10 데이터 처리를 추가, 수정, 삭제하는 데이터 조작어 (1) | 2024.07.30 |
|---|---|
| 05 더 정확하고 다양하게 결과를 출력하는 WHERE절과 연산자 (0) | 2024.07.28 |
| 04 SELECT문의 기본 형식 (0) | 2024.07.28 |
| 01 데이터베이스 / 02 관계형 데이터 베이스와 오라클 데이터 베이스 (0) | 2024.07.27 |
