6.1 오라클 함수
오라클 함수의 종류
오라클에서 기본제공하는 내장함가 있고, 사용자가 필요에 의해 직접 정의하는 사용자 정의 함수가 있다.
6.2 문자 함수
UPPER, LOWER, INITCAP
대,소문자를 바꿔주는 함수들이다.
- UPPER() : 괄호 안 문자 데이터를 모두 대문자로 변환.
- LOWER() : 괄호 안 문자 데이터를 모두 소문자로 변환.
- INITCAP() : 괄호 안 문자 데이터중 첫 글자는 대문자, 나머지는 소문자로 변환.
왜 사용하는가?
SELECT *
FROM EMP
WHERE ENAME LIKE '%Oracle%'이런 sql문이 있다면, 이름에 Oracle이 들어간 사람이 추출될 것이다.
oRacle, oraCle은 해당사항이 없다.
여기서 UPPER나 LOWER로
WHERE ENAME LIKE UPPER('%Oracle%')변환 시켜주면 대소문자 구분없이 검색이 가능하다.
LENGTH
- LENGTH(문자열)
문자열 길이를 구하는 함수다. - LENGTHB(문자열)
문자열의 바이트수를 반환한다.
SUBSTR
문자열 일부를 추출하는 함수.
- SUBSTR(문자열 데이터, 시작위치, 추출길이) : 시작위치부터 추출길이만큼 추출한다.
- SUBSTR(문자열 데이터, 시작위치) : 시작위치부터 끝까지 추출한다.
함수에 들어가는 시작위치는 0부터시작이 아니고 1부터시작이다.
SELECT JOB, SUBSTR(JOB,1,2), SUBSTR(JOB,3,2), SUBSTR(JOB,5)
FROM EMP;
INSTR
문자열 데이터 안에서 특정 문자 위치를 찾는 함수.
- 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;
3번째,4번째 인자는 필수가 아니며, 기본값은 1이다.
SELECT *
FROM EMP
WHERE INSTR(ENAME,'S') > 0;SELECT *
FROM EMP
WHERE ENAME LIKE '%S%';2개 모두 같은결과이다.
※ DUAL은 무슨 테이블?
최고 권한 관리자 계정인 SYS소유의 테이블로 더미테이블이다.
특정 연산 또는 함수의 단일 결과를 확인할 때 자주 사용한다.
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을 뜻한다.
데이터 길이가 지정한 자릿수보다 작을 경우에 나머지 공간을 특정 문자로 채우는 함수.
SELECT 'Oracle',
LPAD('Oracle',10,'#') AS LPAD_1,
RPAD('Oracle',10,'*') AS RPAD_1,
LPAD('Oracle',10) AS LPAD_2,
RPAD('Oracle',10) AS RPAD_2
FROM DUAL;
CONCAT
두개의 문자열 데이터를 하나의 데이터로 연결해 주는 역할
SELECT CONCAT(EMPNO,ENAME),
CONCAT(EMPNO,CONCAT(' : ',ENAME))
FROM EMP
WHERE ENAME = 'SCOTT';
TRIM, LTRIM, RTRIM
문자열 데이터 내에서 특정 문자를 지우기 위해 사용되는함수들.
함수 옵션까지 하나하나 다외우기엔 무리가 있기 때문에, TRIM함수가 경우에 따라서 양쪽의 공백을 제거할때 사용한다는 것만 기억해두자.
6.3 숫자 함수
ROUND
반올림하는 함수.
- ROUND(숫자, 반올림 위치)
반올림 위치는 기본값이 0 이고,
양수면 소수점에서 반올림하는 것 이고
음수면 자연수자리에서 반올림하는 것 이다.
TRUNC
숫자를 버림처리하는 함수.
- TRUNC(숫자, 버림 위치)
ROUND()함수와 같다.
CEIL, FLOOR, MOD
- CEIL(숫자) : 숫자에서 가장 가까운 큰 정수 반환
- FLOOR(숫자) : 숫자에서 가장 작은 정수 반환
- MOD(나눗셈 될 숫자, 나눌 숫자) : % 연산. 나머지값반환
6.4 날짜 함수
ADD_MONTHS
특정 날짜에 지정한 개월 수 이후 날짜 데이터를 반환하는 함수.
ADD_MONTHS(날짜데이터, 더할 개월수)
MONTHS_BETWEEN
MONTHS_BETWEEN(날짜데이터1,날짜데이터2)
두 날짜 데이터를 입력후, 두 날짜 간의 개월 수 차이를 구함
NEXT_DAY, LAST_DAY
- NEXT_DAY(날짜데이터, 요일문자) : 특정 날짜 기준으로 돌아오는 해당 요일의 날짜를 출력.
- LAST_DAY(날짜 데이터) : 해당 날짜가 속한 달의 마지막날을 출력
ROUND, TRUNC
ROUND(숫자, 반올림 위치)
TRUNC(숫자, 버림 위치)
ROUND(날짜, 반올림 기준 포맷)
TRUNC(날짜, 버림 기준 포맷)
양식은 똑같으나, 기준 포맷에 따라 값을 출력한다.
기준 포맷들은 너무 많아 외우기 쉽지않으니 필요할때 마다 구글링해서 찾자.
6.5 자료형 변환 함수
TO_CHAR
숫자데이터를 문자데이터로 혹은 날짜 데이터를 문자데이터로 변환할때 사용한다.
- TO_CHAR(날짜데이터, 출력되길 원하는 문자형태)
TO_NUMBER
문자 데이터를 숫자 데이터로 변환시킴
- TO_NUMBER(문자데이터, 인식될 숫자형태)
TO_DATE
문자 데이터를 날짜 데이터로 변환시킴
- TO_DATE(문자데이터, 인식될 날짜형태)
6.6 NULL 처리 함수
NVL
NVL(NULL인지 여부를 검사할 데이터, NULL일경우 반환할 데이터)
SELECT EMPNO, ENAME, SAL, COMM, SAL+COMM, NVL(COMM,0), SAL+NVL(COMM,0)
FROM EMP;
NVL2
NVL(NULL인지 여부를 검사할 데이터, NULL이 아닐경우 반환할 데이터, NULL일경우 반환할 데이터)
NVL에서 인자가 하나 더 늘었다.
SELECT EMPNO, ENAME, SAL, COMM, NVL2(COMM,'0','X'),
NVL2(COMM,SAL*12+COMM, SAL*12) AS ANNSAL
FROM EMP;
상황에 따라 데이터를 반환하는 함수
두 함수 모두 switch case문과 비슷하다.
DECODE
- DECODE(검사 대상 데이터,
조건1, 조건1과 일치할 때 반환결과,
조건2, 조건2와 일치할 때 반환결과,
...
조건n, 조건n과 일치할 때 반환결과,
조건이 일치하는 경우가 없을 때 반환결과)
CASE
- CASE(검사 대상 데이터,
WHEN 조건1 THEN 조건1과 일치할 때 반환결과,
WHEN 조건2 THEN 조건2와 일치할 때 반환결과,
...
WHEN 조건n THEN 조건n과 일치할 때 반환결과,
ELSE 조건이 일치하는 경우가 없을 때 반환결과
END AS alias
)
