Dec 20, 2022 · 5분 읽기

KH_ORACLE_03_SQL 주요 함수

ORACLE

목록 보기숨기기3/12

DUAL 테이블과 SQL 함수 분류

DUAL 테이블은 DUMMY라는 단 하나의 컬럼에 X라는 단 하나의 로우만을 저장하고 있으나 이 값은 아무런 의미가 없다.

DUAL 테이블은 산술 연산의 결과를 한 줄로 얻기 위해서 오라클에서 제공하는 테이블이다.

SELECT *
FROM DUAL;
SELECT 24*60
FROM EMP;
SELECT 24*60
FROM DUAL;
SELECT SYSDATE
FROM DUAL;
-- 현재 날짜를 얻는 쿼리문

절댓값을 구하는 ABS함수

ABS함수는 절대값을 구한다. 절대값은 방향은 없고 크기만 있는 것으로서 주어진 데이터가 음수일 경우 양수로 표현된다.

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

소수점 아래를 버리는 FLOOR함수

FLOOR함수는 소수점 아래를 버린다. 12.345를 FLOOR함수에 적용하면 12가 구해진다.

SELECT 34.5678, FLOOR(34.5678)
FROM DUAL;

특정 자릿수에서 반올림하는 ROUND함수

56.789를 반올림하면 57이다. 이처럼 반올림한 결과를 구하기 위한 함수로 오라클에서는 ROUND가 제공된다.

SELECT 34.5678, ROUND(34.5678)
FROM DUAL;
SELECT 34.5678, ROUND(34.5678, 2)
FROM DUAL;
SELECT 34.5678, ROUND(34.5678, -1)
FROM DUAL;

특정 자릿수에서 잘라내는 TRUNC 함수

TRUNC 함수는 지정한 자리 수 이하를 버린 결과를 구해주는 함수이다.

SELECT TRUNC(34.5678, 2), TRUNC(34.5678, -1), TRUNC(34.5678)
FROM DUAL;

나머지 구하는 MOD함수

MOD함수는 나누기 연산을 한 후에 구한 몫이 아닌 나머지를 결과로 되돌려주는 함수이다.

SELECT MOD (27, 2), MOD (27, 5), MOD (27, 7)
FROM DUAL;

문자 처리 함수

UPPER 함수

UPPER 함수는 입력한 문자값을 대문자로 변환하는 함수이다.

SELECT 'Welcome to Oracle', UPPER('Welcome to Oracle')
FROM DUAL;

LOWER 함수

LOWER 함수는 문자열을 모두 소문자로 변경한다.

SELECT 'Welcome to Oracle', LOWER('Welcome to Oracle')
FROM DUAL;

INITCAP 함수

INITCAP 함수는 문자열의 이니셜만 대문자로 변경한다.

SELECT 'WELCOME TO ORACLE', INITCAP('WELCOME TO ORACLE')
FROM DUAL;

LENGTH 함수

LENGTH 함수는 컬럼에 저장된 데이터 값이 몇 개의 문자로 구성되었는지 길이를 알려주는 함수이다.

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

LENGTHB 함수

LENGTHB 함수는 바이트 수를 알려주는 함수이다. 특히, 한글 1자는 2바이트를 차지하는데, 이 때문에 수행 결과를 보면 한글 3자로 구성된 '오라클'의 LENGTHB 함수의 결과는 9가 된다.

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

SUBSTR, SUBSTRB 함수

SUBSTR과 SUBSTRB 함수는 대상 문자열이나 칼럼의 자료에서 시작위치부터 선택 개수만큼의 문자를 추출한다. SUBSTRB 함수도 같은 형식이지만 명시된 개수만큼의 문자가 아닌 바이트 수를 잘라낸다는 점에서만 차이가 있다.

SELECT SUBSTR('Welcome to Oracle', 4, 3)
FROM DUAL;

INSTR 함수

INSTR 함수는 대상 문자열이나 칼럼에서 특정 문자가 나타나는 위치를 알려준다.

SELECT INSTR('WELCOME TO ORACLE', 'O')
FROM DUAL;

INSTRB 함수

INSTRB 함수 역시 SUBSTRB 함수에서와 마찬가지로 문자의 위치를 알아내기 위한 바이트 기준으로 한다.

SELECT INSTR('데이터베이스', '이', 3, 1), INSTRB('데이터베이스', '이', 3, 1)
FROM DUAL;

LPAD/RPAD 함수

LPAD 함수는 칼럼이나 대상 문자열을 명시된 자릿수에서 오른쪽에 나타내고, 남은 왼쪽 자리를 특정 기호로 채운다. RPAD 함수는 반대로 칼럼이나 대상 문자열을 명시된 자릿수에서 왼쪽에 나타내고, 남은 오른쪽 자리를 특정 기호로 채운다.

SELECT LPAD('Oracle', 20, '#')
FROM DUAL;
SELECT RPAD('Oracle', 20, '#')
FROM DUAL;

LTRIM 와 RTRIM 함수

LTRIM 함수는 문자열의 왼쪽의 공백 문자들을 삭제한다. RTRIM 함수 역시 오른쪽의 공백 문자를 잘라낸다.

SELECT LTRIM(' Oracle ')
FROM DUAL;
SELECT RTRIM(' Oracle ')
FROM DUAL;

TRIM 함수

TRIM 함수는 칼럼이나 대상 문자열에서 특정 문자가 첫 번째 글자이거나 마지막 글자라면 잘라내고 남은 문자열만 반환한다.

SELECT TRIM('a' FROM 'aaaaOracleaaaa')
FROM DUAL;

REPLACE 함수

REPLACE()는 문자열을 치환하는 함수이다. 예를 들어 담당자의 실수로 도서의 제목을 잘못 입력한 경우 REPLACE 함수를 사용하면 일일이 변경하지 않고 한꺼번에 변경할 수 있다.

CONN MADANG/MADANG;
SELECT BOOKID, REPLACE(BOOKNAME,'야구','농구') BOOKNAME, PUBLISHER, PRICE FROM BOOK;

날짜 함수

현재 날짜를 반환하는 SYSDATE 함수

STSDATE 함수는 시스템에 저장된 현재 날짜를 반환하는 함수이다.

SELECT SYSDATE
FROM DUAL;

날짜 연산

날짜 형 데이터에 숫자를 더하면 그 날짜로부터 그 기간만큼 지난 날짜를 계산한다. 날짜 형 데이터에 숫자를 빼면 그 날짜로부터 그 기간만큼 이전 날짜를 구한다.

SELECT SYSDATE-1 어제, SYSDATE 오늘, SYSDATE+1 내일
FROM DUAL;

특정 기준으로 반올림하는 ROUND 함수

ROUND 함수는 숫자를 반올림하는 함수로 학습하였다. 하지만, 이 함수에 포멧 모델을 지정하면 숫자 이외에 날짜에 대해서도 반올림을 할 수 있다.

-- 형식
ROUND (date, format)
-- 예
SELECT HIREDATE, ROUND(HIREDATE, 'MONTH')
FROM EMP;

특정 기준으로 버리는 TRUNC 함수

TRUNC 함수 역시 숫자를 잘라내는 것 뿐만 아니라 날짜를 잘라낼 수 있다. ROUND 함수와 마찬가지로 포맷 형식을 주어 다양한 기준으로 날짜를 잘라낼 수 있다.

--형식
TRUNC (date, format)
-- 예
SELECT HIREDATE, TRUNC(HIREDATE, 'MONTH')
FROM EMP;

두 날짜 사이의 날수를 구하는 MONTHS_BETWEEN 함수

MONTH_BETWEEN 함수는 날짜와 날짜 사이의 개월 수를 구하는 함수이다.

-- 형식
MONTHS_BETWEEN (date1, date2)
-- 예
SELECT ENAME, SYSDATE, HIREDATE, MONTHS_BETWEEN (SYSDATE, HIREDATE)
FROM EMP;

개월 수를 더하는 ADD_MONTHS 함수

ADD_MONTHS 함수는 특정 개월 수를 더한 날짜를 구하는 함수이다.

-- 형식
ADD_MONTHS (date, number)
-- 예
SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 6)
FROM EMP;

해당 요일의 가장 가까운 날짜를 반환하는 NEXT_DAY 함수

NEXT_DAY 함수는 해당 날짜를 기준으로 최초로 도래하는 요일에 해당되는 날짜를 반환하는 함수이다.

-- 형식
NEXT_DAY (date, 요일)
-- 예
SELECT SYSDATE, NEXT_DAY(SYSDATE, '수요일')
FROM DUAL;

해당 달의 마지막 날짜를 반환하는 LAST_DAY 함수

LAST_DAY 함수는 해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수이다.

SELECT HIREDATE, LAST_DAY(HIREDATE)
FROM EMP;

형 변환 함수

오라클을 사용하다 보면 숫자, 문자, 날짜의 데이터 형을 다른 데이터 형으로 변환해야 하는 경우가 생긴다.

이럴 때 사용하는 함수가 형 변환 함수이다. 형 변환 함수로는 TO_NUMBER, TO_CHAR, TO_DATE가 있다.

문자형으로 변환하는 TO_CHAR 함수

날짜형을 문자형으로 변환하기

- DATE 형태의 데이터를 지정한 양식에 의해 VARCHAR2 형의 문자로 변환한다.

-- 형식
TO_CHAR (날짜 데이터, '출력형식')
-- 예
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD')
FROM DUAL;

숫자형을 문자형으로 변환하기

SELECT ENAME, SAL, TO_CHAR (SAL, 'L999,999')
FROM EMP;
SELECT TO_CHAR (123456, '000000000'), TO_CHAR (123456, '999,999,999')
FROM DUAL;

날짜형으로 변화하는 TO_DATE 함수

날짜 형은 세기, 년도, 월, 일, 시간, 분, 초와 같이 날짜와 시간에 대한 정보를 저장한다.

오라클에서 기본 날짜 형식은 'YY/MM/DD' 형식으로 '년/월/일' 예를 들면 '06/03/08' 식으로

TO_DATE 함수는 문자열을 날짜 형으로 변환한다.

SELECT ENAME, HIREDATE FROM EMP
WHERE HIREDATE=TO_DATE(19810220,'YYYYMMDD');
SELECT TRUNC(SYSDATE-TO_DATE('2008/01/01', 'YYYY/MM/DD'))
FROM DUAL;

숫자형으로 변환하는 TO_NUMBER 함수

TO_NUMBER 함수는 특정 데이터를 숫자형으로 변환해 주는 함수이다.

SELECT TO_NUMBER('20,000', '99,999') - TO_NUMBER('10,000', '99,999')
FROM DUAL;

NULL을 다른 값으로 변환하는 NVL 함수

NVL 함수는 NULL을 0 또는 다른 값으로 변환하기 위해서 사용하는 함수이다.

SELECT ENAME, SAL, COMM, SAL*12+COMM,
NVL(COMM, 0), SAL*12+NVL(COMM, 0)
FROM EMP
ORDER BY JOB;

선택을 위한 DECODE 함수

DECODE 함수는 프로그램 언어에서 가장 많이 사용되는 switch case 문과 같은 기능을 갖는다. 즉, 여러 가지 경우에 대해서 선택할 수 있도록 한다.

-- 형식
DECODE (표현식, 조건1, 결과1,
 조건2, 결과2,
 조건3, 결과3,
 기본결과n
)
-- 예
SELECT ENAME, DEPTNO,
 DECODE(DEPTNO, 10, 'ACCOUNTING',
 20, 'RESEARCH',
 30, 'SALES',
 40, 'OPERATIONS' )
 	AS DNAME
FROM EMP;

조건에 따라 서로 다른 처리가 가능한 CASE 함수

CASE 함수 역시 여러 가지 경우에 대해서 하나를 선택하는 함수이다.

DECODE 함수와 차이점이 있다면 DECODE 함수는 조건이 일치( = 비교 연산자) 하는 경우에 대해서만 적용되는 반면, CASE 함수는 다양한 비교 연산자를 이용하여 조건을 제시할 수 있으므로 범위를 지정할 수도 있다.

CASE 함수는 프로그램 언어의 if else if-else 와 유사한 구조를 갖는다.

-- 형식
CASE 표현식 WHEN 조건1 THEN 결과1
 WHEN 조건2 THEN 결과2
 WHEN 조건3 THEN 결과3
 ELSE 결과n
END
-- 예
SELECT ENAME, DEPTNO,
 CASE WHEN DEPTNO=10 THEN 'ACCOUNTING'
 WHEN DEPTNO=20 THEN 'RESEARCH'
 WHEN DEPTNO=30 THEN 'SALES'
 WHEN DEPTNO=40 THEN 'OPERATIONS'
 END AS DNAME
FROM EMP;

댓글을 불러오는 중…