오라클에서는 연산자로 다루기 어려운 복잡한 데이터의
처리와 다양한 결과를 제공하기위해 내장함수를 제공한다
자주 쓰이는 것들을 보자면
-
문자 함수
LENGTH SUBSTR INSTR REPLACE TRIM -
숫자 함수
ROUND TRUNC CEIL FLOOR MOD -
날짜 함수
SYSDATE -
NULL 관련 함수
NVL NVL2 -
DECODE함수, CASE문
이것들은 결과값이 1행으로 처리되는 단일행 함수이다
함수란?
사용자가 입력 값 X를 넣으면 정해 놓은 출력 값 Y가 나오는 개념
오라클 데이터베이스 시스템에서 쓰는 함수는
미리 정의된 기능을 통해 데이터를 좀 더 편리하게 조작할 수 있도록 도와준다
단일행 함수, 다중행 함수
함수의 유형은 두가지로 나눌수 있다.
-
단일행 함수
단일 행만 연산하여 행 당 하나의 결과를 RETURN -
다중행 함수
여러 행을 연산하여 그룹 당 하나의 결과를 RETURN
문자 함수
문자를 입력 값으로 받으면 문자 또는 숫자 값을 RETURN한다
단일행을 연산하는 단일행 함수다
1. 대소문자를 바꿔주는 UPPER, LOWER, INITCAP 함수
1-1. UPPER(문자열) -> 괄호 안의 문자데이터를 모두 대문자로 변환후 RETURN
1-2. LOWER(문자열) -> 괄호 안의 문자데이터를 모두 소문자로 변환후 RETURN
1-3. INITCAP(문자열) -> 괄호 안의 첫번째 문자만 대문자로 변환후 RETURN
SELECT UPPER('문자열')
FROM 테이블SELECT 절에서 사용
해당 컬럼에 있는 모든 데이터들이 대문자로 변경후 리턴된다
SELECT 게시판제목
FROM 게시판
WHERE 게시판제목 LIKE '%Oracle%'
OR 게시판제목 LIKE '%oracle%'게시판제목에서 'Oracle', 'oracle'을 다 찾아야할 때 연산자로만 찾으려고 하면
불편해진다 하지만 함수를 사용하면
SELECT 게시판제목
FROM 게시판
WHERE UPPER(게시판제목) LIKE '%ORACLE%';WHERE 조건절에서 사용
게시판제목을 UPPER로 대문자로 바꾸고
찾아야하는 데이터값도 대문자로 바꿔서 검색하면
Oracle, oracle, oRacle 전부 검색이 가능해진다
SELECT 에서 사용하는 UPPER와
WHERE 에서 사용하는 UPPER는 같은 기능이기도 하지만 결과값에는 차이가 있다
- SELECT UPPER(게시판제목) -> 리턴하는 데이터값을 대문자로 바꾼다
- WHERE UPPER(게시판제목) -> 조건문에서 사용하려는 게시판제목을 대문자로 바꾼다
함수의 기능은 같아도 어디에 들어가느냐에 따라서 얻을수 있는 결과는 달라진다
차이점을 분명히 알고 써야한다
내가 리턴받아야 하는 데이터를 대문자로 받아야하는건지
검색하기 위해서 데이터를 대문자로 변환하는건지 이해하고 써야한다
2. LENGTH() 함수
문자열의 갯수를 숫자값으로 리턴한다
LENGTH(문자열)
SELECT LENGTH(문자열)
FROM DUAL;국가이름의 문자열갯수를 숫자형태로 리턴받는다
- SELECT에서 사용할 경우엔 숫자 형태의 값을 출력할 것이고
WHERE에서 사용하면 숫자형태의 값을 통해 특정 조건을 만족시켜서
원하는 데이터만 얻을 수 있게 가공할 수 있다
3. SUBSTR() 함수(SUBSTRING)
문자열의 일부를 추출해서 리턴한다
SUBSTR(문자열, 시작위치, 추출길이)
-> 시작위치부터 시작하여 추출길이만큼 잘라서 리턴
SUBSTR(문자열, 시작위치)
-> 추출길이가 생략되면 마지막 문자까지 잘라서 리턴
- 주민등록번호의 앞자리만 필요하거나 전화번호의 마지막 4자리만 필요한 경우는
일상에서도 흔히 볼 수 있는 상황이다 이때 SUBSTR을 사용하면 편하게 검색이 가능해진다
SELECT SUBSTR(전화번호, 10, 4)
FROM 전화번호부 // 데이터는 '010-1234-5678' 형태로 들어가있다
// '-'도 문자열의 길이에 포함된다
// 시작위치가 10인 인덱스 번호 부터(= 10 이상)
// 추출길이는 총 4 가 될때까지(= 4 이하) 선택후 리턴- 문자열 INDEX 세는법
위에서 10과 4는 인덱스 번호로써 쓰고 있다
자바는 인덱스번호가 0부터 시작이지만
오라클DB에선 1부터 시작한다
'010-1234-5' 는 인덱스번호가 10까지 있는것이다
따라서 인덱스번호 10, 11, 12 ,13 이 RETURN 된다
(JAVA INDEX랑 헷갈리지 말것)
4. INSTR() 함수
문자열에서 찾고자하는 문자열과 일치하는 위치를 RETURN한다
INSTR(문자열, 찾을문자열, 시작위치, 발생횟수)
시작위치를 명시하지않으면 DEFAULT값은 1
발생횟수는 몇 번째 일치하는 지를 명시하며 DEFAULT값 역시 1
SELECT INSTR('ABCD BCDE CDEF','CD', 4)
FROM DUAL;이러면 4번째 인덱스 부터 CD위치의 첫번째 발생의 인덱스번호를 리턴한다
값이 7이 나오면 정상이다
SELECT INSTR(
'ABCD BCDE CDEF','CD', 4, 2
)
FROM DUAL;이러면 값이 11 이 나오면 정상이다
FROM DUAL -> 더미 테이블이다
가상의 테이블을 만들어주어서 쿼리문 연습할때 쓰기 좋다
5. REPLACE 함수
컬럼의 데이터들을 변환문자 = 찾을문자로 치환한다음 리턴한다
REPLACE('문자열', '찾을문자', '변환문자')
변환문자에 '' 아무것도 없는 값을 넣으면 찾을 문자를 제거한다음 리턴하라는 뜻이다
6. LPAD, RPAD 함수
LPAD(문자열, 전체문자길이, '채움문자')
문자열데이터의 길이를 전체문자길이만큼 고정시키고
공간에 데이터가 없다면 채움문자로 채우는 함수
채움문자에 값이 없으면 DEFAULT값인 공백으로 채워진다
문자열의 현재 길이에 상관없이 전체문자길이 기준으로 빈공간이 생기면
채움문자로 채워지고 문자열의길이가 전체문자길이가 길면 총문자 길이만큼 잘라버린다
- 숫자형 문자인 경우 문자길이를 똑같이 맞춰야하는 경우가 있다.
LPAD는 왼쪽, RPAD는 오른쪽부터 총길이 만큼 지정한 문자를 채운다.
특정 길이를 만들어서 출력하게 되면 데이터를 깔끔하게 볼수 있다
SELECT LPAD(NUMBER, 5, 0)
FROM DUAL; 데이터가 1, 10, 123이런 형태면
00001
00010
00123
이런식으로 보여지게 되면서 가독성이 좋아진다
- 심화활용
사람 이름을 성만 표시하고 이름부분을 *로 숨겨서 데이터를 출력하시오
SELECT RPAD(SUBSTR(NAME, 1, 1), LENGTH(NAME), '*')
FROM LIST문자열에 해당하는 부분에 SUBSTR을 써서 성만 나오게끔 하고
전체길이를 LENGTH를 활용해서 NAME의 길이를 숫자형태로 받아서 전체길이로 지정하면
빈공간이 2개가 생기는데 거기에 '*' 문자로 채운다 라는 의미다
SUBSTR은 특정 길이로 자른 값이 필요할때 주로 사용
LPAD RPAD함수와 연계해서 주민등록번호의 뒷자리는 * 형태로
숨겨서 출력하는 그런 상황에서 활용 가능하다
7. CONCAT 함수
7-1. CONCAT(문자열1, 문자열2, ...)
문자열끼리 연결하는 함수
SELECT CONCAT('문자열1','문자열2','문자열3')
FROM DUAL; 각 컬럼에 데이터가 JAVA, ORACLE, DB 라면
JAVAORACLEDB 형태로 리턴한다
실무에선 || 로 이어붙이는걸 선호한다
확실히 가독성 면에서도 ||가 좋다
ORACLE SQL DEVELOPER 에서 기본제공하는 기능인
|| 연산자
CONCAT기능과 같고 직관적이기 떄문에 실무에서는 ||를 많이 사용한다.
SELECT '문자열1' || '문자열2' || '문자열3'
FROM DUAL;
/* [ABC] 이렇게 출력한다 */8. TRIM 함수
SELECT TRIM('문자열')
-> 문자열의 공백을 지우는 함수
SELECT TRIM(' Sample ')
SELECT LTRIM(' Sample ')
SELECT RTRIM(' Sample ')각각 양쪽, 왼쪽, 오른쪽 공백을 지우는 함수
REPLACE로도 공백을 지울수있지만 공백만 지우는게 필요하다면 TRIM이 더 편하다
