SQL 함수
함수의 특징 및 이점
- 데이터에 연산을 수행할 수 있다.
- 개별적인 데이터 항목을 수정할 수 있다.
- 행의 그룹에 대해 결과를 조작할 수 있다.
- 출력을 위한 날짜와 숫자형식을 조절할 수 있다.
- 열의 자료형을 변환할 수 있다.
함수의 종류
- 단일행 함수
- 그룹 함수
- 기타 함수
단일행 함수
- 단일 행에 대해서만 적용이 가능하고, 행별로 하나의 결과를 반환한다.
- cf) 그룹 함수
단일행 함수와 달리 그룹함수는 여러 행 또는 테이블 전체에 대해 함수가 적용되어
하나의 결과를 가져오는 함수를 의미한다. - 함수명 (컬럼 |표현식[arg1,arg2....])
단일행 함수 종류
- 문자형 함수: 문자를 입력받고 문자와 숫자 모두를 반환할 수 있다.
- 숫자형 함수: 숫자를 입력받고 숫자를 반환한다.
- 날짜형 함수: 날짜형에 대해 수행하고 날짜 데이터형의 값을 반환한다.
cf. MONTHS_BETWEEN 함수: 숫자 반환 - 변환형 함수: 어떤 데이터형의 값을 다른 데이터형으로 변환한다.
- 일반적인 함수: NVL, DECODE
단일행 함수의 특징
- 질의에서 반환되는 각각의 행에 대해 수행
- 행별로 하나의 결과를 반환
- 참조시 사용한 데이터형과 다른 데이터형으로 결과를 반환할 수 있다.
- 하나 이상의 인수를 필요로 한다.
- SELECT, WHERE, ORDER BY 절에서 사용할 수 있다.
- 함수를 중첩할 수 있다.
단일행 함수들은 여러 레벨에 걸쳐 중첩사용이 가능하다.
중첩된 함수들은 가장 하위 레벨에서 가장 상위 레벨순으로 진행된다.
1. 문자형 함수
| 종류 | 함수 | 사용목적 |
| 변환함수 | LOWER | 알파벳을 소문자로 변환 |
| UPPER | 알파벳을 대문자로 변환 | |
| INITCAP | 첫 번째 글자만 대문자로 변환 | |
| 문자조작함수 | CONCAT | 두 문자열을 연결 |
| SUBSTR | 문자열 중 특정 문자 또는 문자열의 일부분을 선택 | |
| LENGTH | 문자열의 길이를 구함 | |
| LPAD | 왼쪽 문자 자리 채움 | |
| RPAD | 오른쪽 문자 자리 채움 | |
| LTRIM | 왼쪽 문자를 자름 | |
| RTRIM | 오른쪽 문자를 자름 | |
| REPLACE | 특정 문자열을 대신함 |
LOWER / UPPER
LOWER는 대문자를 소문자로, UPPER는 소문자를 대문자로 바꿔서 출력하는 함수
SELECT LOWER('HAPPY BIRTHDAY') FROM dual;
SELECT UPPER('java') FROM dual;
dual 테이블
- 오라클에서 제공하는 기본 테이블로, 한 개의 행으로 구성되어 있다.
- dual 테이블은 사용자가 함수(계산)를 실행할 때 임시로 사용하는데 적합하다.
INITCAP
첫 문자를 대문자로 반환하고, 나머지는 소문자로 반환하여 출력하는 함수
--dept에서 부서번호, 부서명, 근무지를 출력하되 첫 글자만 대문자로 보여주세요.
SELECT deptno, INITCAP(dname), INITCAP(loc) FROM dept;
CONCAT
- 두 개 이상의 문자나 컬럼을 서로 연결시켜주는 함수
가령 우편번호와 주소 컬럼이 따로 떨어져 있다면 CONCAT 함수를 이용하여 하나의 컬럼처럼 사용할 수 있다. - CONCAT(변수1, 변수2)
- 결과값: 변수1과 변수2를 연결한 값을 리턴한다.
단, 변수1과 변수2에 숫자를 입력하더라도 문자로 인식하여 결합하게 된다.
SELECT CONCAT('ABCD','1234') FROM dual;
SELECT CONCAT(empno, ename) AS A FROM emp;
SUBSTR
- SUBSTR(변수 or 컬럼, START, LEN)
START값에서 시작해서 LEN값만큼 변수를 리턴한다. - 만약 START값이 0이라면 1로 처리되고, 음수라면 문자의 오른쪽에서부터 세어서 리턴한다.
- LEN값이 없다면 디폴트로 전체 길이를 의미하고, LEN값이 0 이하라면 null이 리턴된다.
- 주로 주민등록번호의 앞 번호만 추출할 때 많이 사용한다.
SELECT SUBSTR('ABCDEFG', 3, 2) FROM dual; --CD
SELECT SUBSTR('ABCDEFG', -3, 2) FROM dual; --EF
SELECT SUBSTR('990215-1012345', 8, 7) FROM dual; --1012345
SELECT SUBSTR('990215-1012345', -7) FROM dual; --1012345
SELECT SUBSTR('990215-1012345', 1, 6) FROM dual; --990215
SELECT SUBSTR('990215-1012345', -14, 6) FROM dual; --990215
LENGTH
- 주어진 컬럼 또는 문자열의 길이를 리턴하는 함수
- LENGTH(변수 또는 컬럼)
- 결과값: 변수나 컬럼의 길이를 문자 단위로 리턴함. 만약 변수가 null일 경우, null을 리턴함
SELECT LENGTH('990215-1012345') FROM dual; --14
[실습]
-- 상품 테이블에서 판매가를 화면에 보여줄 때 금액의 단위를 함께 붙여서 출력하세요.
SELECT pnum, products_name, CONCAT(output_price, '원') AS 판매가 FROM products;
SELECT pnum, products_name, output_price|| '원' AS 판매가 FROM products;
-- 고객테이블에서 고객 이름과 나이를 하나의 컬럼으로 만들어 결과값을 화면에 보여주세요.
SELECT name, age, CONCAT(name, age) AS "이름과 나이" FROM member;
-- 사원 테이블에서 첫글자가 'K'보다 크고 'Y'보다 작은 사원의
-- 사번, 이름, 업무, 급여를 출력하세요. 단, 이름순으로 정렬하세요.
SELECT empno, ename, job, sal FROM emp
WHERE SUBSTR(ename, 1, 1) > 'K' AND SUBSTR(ename, 1, 1) < 'Y';
-- 사원테이블에서 부서가 20번인 사원의 사번, 이름, 이름자릿수, 급여, 급여의 자릿수를 출력하세요.
SELECT empno, ename, LENGTH(ename) AS 이름자릿수, sal, LENGTH(sal) AS "급여의 자릿수"
FROM emp
WHERE deptno=20;
-- 사원테이블의 사원이름 중 6자리 이상을 차지하는 사원의 이름과 이름자릿수를 보여주세요.
SELECT ename, LENGTH(ename) AS 이름자릿수
FROM emp
WHERE LENGTH(ename) >= 6;
LPAD / RPAD
- 문자값을 왼쪽 / 오른쪽부터 채운다.
- LPAD(컬럼, N, C) / RPAD(컬럼, N, C)
SELECT ename, LPAD(ename, 15, '#') FROM emp;
SELECT ename, sal, LPAD(CONCAT('$',sal),10,' ') AS 급여 FROM emp; --오른쪽 정렬
SELECT RPAD(dname, 15, '@') FROM dept;
LTRIM / RTRIM
- LTRIM(변수, 문자) / RTRIM(변수, 문자)
- 변수값 중 주어진 문자와 같은 단어가 있을 경우 그 문자를 삭제한 나머지 값을 반환
- 주로 공백을 제거할 때 많이 사용한다.
SELECT LTRIM('ttHello test', 't'), RTRIM('tttHello test','t') FROM dual;
--앞 뒤의 공백문자를 제거함 (공백은 생략 가능)
SELECT RTRIM(LTRIM(' 오늘의 날씨 ')) AS result FROM dual;
SELECT TRIM(' 오늘의 날씨 ') AS result FROM dual;
--사원 테이블에서 10번 부서의 사원에 대해 담당 업무 중 우측에 'T'를 삭제하고
--급여 중 우측의 0을 삭제하여 출력하세요.
SELECT ename, job, RTRIM(job, 'T') AS 직업, sal, RTRIM(sal, 0) AS 급여, deptno
FROM emp
WHERE deptno=10;
REPLACE
- REPLACE(컬럼, 값1, 값2)
- 컬럼값 중에 값1이 있으면 값2로 교체하는 함수
--emp 테이블의 job에서 'A'를 '$'로 바꾸어 출력하기
SELECT job, REPLACE(job, 'A', '$') AS 직업
FROM emp;
2. 숫자형 함수
| 함수 | 사용목적 |
| ROUND | 숫자를 반올림 |
| TRUNC | 숫자를 절삭 |
| MOD | 나머지 구함 |
| POWER | 거듭제곱 |
| SQRT | 제곱근 |
| SIGN | 양수, 음수, 0인지 구분 |
| CHR | ASCII값에 해당하는 문자를 구함 |
| ABS | 절대값을 구함 |
| CEIL | 올림함수 |
| FLOOR | 내림함수 |
ROUND
- ROUND(값), ROUND(X, Y): 반올림 함수
- 소수점의 오른쪽(왼쪽)에서 Y자리만큼 반올림된 X를 출력
- 한 자리 숫자일 경우에는 반올림하고, 두 자리 숫자일 경우에는 소수점의 오른쪽에서 Y자리만큼 반올림된 X를 나타낸다. 이때 Y값이 음수인지 양수인지에 따라 다른 결과를 보여준다. 숫자를 Y자리만큼 반올림할 때 Y자리가 양수이면 소수자리를, Y자리가 음수이면 정수 자리를 반올림한다. 생략할 경우 디폴트는 0이다.
SELECT ROUND(456.678), ROUND(456.478,0),
ROUND(456.678,2), ROUND(456.678,-2) FROM DUAL;
TRUNC
- TRUNC(값), TRUNC(X, Y): 버림 함수. 절삭
- 일정한 단위로 절사하는 경우에 주로 사용하는 함수로, ROUND함수와 사용방법은 같다.
- 주로 영수증에서 10이하 단위는 잘라내고 10원 단위로 금액을 출력시킬 때 사용
SELECT TRUNC(456.678), TRUNC(456.678,0),
TRUNC(456.678,2), TRUNC(456.678,-2) FROM DUAL;
MOD
- MOD(X, Y): X를 Y로 나눈 나머지값을 구하는 함수
SELECT MOD(10,3) FROM DUAL; --1
ABS
- ABS(값): 선택된 값의 절댓값을 구하는 함수
- 음수값이 있는 컬럼값을 양수로 나타내고자 할 때 사용
SELECT ABS(-9), ABS(9) FROM DUAL;
CEIL / FLOOR
- CEIL(값): 올림함수
- FLOOR(값): 내림함수
SELECT CEIL(123.0001), FLOOR(123.0001) FROM DUAL;
CHR / ASCII
- CHR(숫자): 아스키코드값 얻기
- ASCII(문자): CHR값 얻기
SELECT CHR(65), ASCII('F') FROM DUAL;
[실습]
--회원 테이블에서 회원의 이름, 마일리지, 나이, 마일리지를 나이로 나눈 값을 반올림하여 보여주세요.
SELECT name, mileage, age, ROUND(mileage/age) AS result
FROM member;
--상품 테이블의 상품 정보가운데 백원 단위까지 버린 배송비를 비교하여 출력하세요.
SELECT products_name, trans_cost, TRUNC(trans_cost, -3)
FROM proudcts;
--사원 테이블에서 부서번호가 10인 사원의 급여를 30으로 나눈 나머지를 출력하세요.
SELECT ename, sal, MOD(sal, 30), deptno
FROM emp
WHERE deptno=10;
--회원 테이블에서 회원의 나이에서 40을 뺀 절댓값을 출력하세요.
SELECT name, age, ABS(age-40)
FROM member;
3. 날짜 함수
오라클은 세기, 년, 월, 일, 시, 분, 초를 내부 숫자(7BYTE) 형식으로 날짜를 지정한다.
날짜 연산
| 연산 | 결과 | 설명 |
| DATE + NUMBER | DATE | 일수를 날짜에 더함 |
| DATE - NUMBER | DATE | 일수를 날짜에 뺌 |
| DATE - DATE | 일수 | |
| DATE + NUMBER/24 | DATE | 시간을 날짜에 더함 |
SELECT SYSDATE, SYSDATE +3, SYSDATE -3, TO_CHAR(SYSDATE,'YY/MM/DD HH:MI:SS') AS 현재시간,
TO_CHAR(SYSDATE +3/24,'YY/MM/DD HH:MI:SS') AS "현재시간+3h" FROM DUAL;
SELECT NAME, REG_DATE, FLOOR(SYSDATE -REG_DATE) "등록 이후 일수" FROM MEMBER;
[실습]
-- 사원테이블에서 현재까지의 근무 일수가 몇 주 며칠인가를 출력하세요.
-- 단, 근무 일수가 많은 사람순으로 출력하세요.
SELECT ename, sysdate, hiredate, TRUNC((sysdate - hiredate)/7) AS weeks,
TRUNC(MOD(sysdate - hiredate, 7)) AS days
FROM emp ORDER BY 4 DESC;
| 함수 | 사용목적 |
| MONTHS_BETWEEN | 두 날짜 사이의 월수를 계산 |
| ADD_MONTHS | 월을 날짜에 더함 |
| LAST_DAY | 월의 마지막 날을 구함 |
| SYSDATE | 오라클이 설치되어 있는 서버의 현재 날짜와 시간을 반환 |
MONTHS_BETWEEN
- MONTHS_BETWEEN(date1, date2): date1날짜와 date2날짜 사이의 월수를 계산한다.
- 결과는 음수 또는 양수가 될 수 있다.
- 결과의 정수 부분은 월을, 소수 부분은 일을 나타낸다.
SELECT ENAME, SYSDATE, HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) AS 월수
FROM EMP
ORDER BY 4 DESC;
SELECT NAME, SYSDATE, REG_DATE, MONTHS_BETWEEN(SYSDATE,REG_DATE)AS 월수
FROM MEMBER
ORDER BY 4;
ADD_MONTHS
- ADD_MONTHS(date, M): 날짜에 월을 더함
- 결과값: 날짜 date에 M월 만큼 더한 날짜를 리턴한다. 여기서 M은 정수이고, 만약 결과값의 월이 date의 월보다 날짜수가 적다면 결과값의 월의 마지막 일이 리턴된다.
SELECT ADD_MONTHS(SYSDATE,5) "5개월 뒤", ADD_MONTHS(SYSDATE,-3) "석달 전" FROM DUAL;
SELECT ADD_MONTHS('22/01/31', 8) FROM DUAL;
Last_DAY
- LAST_DAY(date): date가 포함되어 있는 월의 마지막 날짜를 구할 때 사용하는 함수(윤년, 평년은 자동으로 계산함)
- 일정 관리 프로그램이나 다이어리 등을 만들 때 1월 ~ 12월까지의 마지막 일자를 LAST_DAY 함수를 이용하여 구하면 쉽게 달력 프로그램을 만들 수 있다.
SELECT SYSDATE, LAST_DAY(SYSDATE), LAST_DAY('23/02/01') FROM DUAL;
SYSDATE, SYSTIMESTAMP
- 현재 시간을 DATE타입으로 출력하는 함수
- SYSDATE: 년월일시분초까지 표시 가능
- SYSTIMESTAMP: 년월일시분초 밀리세컨드까지 표시 가능
SELECT SYSDATE, SYSTIMESTAMP FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'CC YEAR-MONTH-DDD DY') FROM DUAL;
4. 변환 함수
TO_CHAR(날짜)
- 문자가 아닌 자료형의 값을 문자형으로 변환시키는 함수
- TO_CHAR(날짜) 함수는 DATE타입을 문자로 바꾼다.
- TO_CHAR(D, 출력형식)
- 결과값: DATE형인 D를 출력형식에 맞는 VARCHAR2로 변환하여 리턴. 출력형식이 없을 경우에는 기본이 되는 날짜형을 문자형으로 변환하여 리턴
SELECT NAME, REG_DATE FROM MEMBER;
SELECT NAME, TO_CHAR(REG_DATE, 'YYYY-MM-DD') FROM MEMBER;
SELECT NAME, TO_CHAR(REG_DATE) FROM MEMBER;
--고객테이블에 있는 고객 정보 중 등록연도가 2023년인 고객의 정보를 보여주세요.
SELECT name, reg_date
FROM member
WHERE TO_CHAR(reg_date, 'YY')='23';
--고객테이블에 있는 고객 정보 중 등록일자가 2023년 5월 1일보다 늦은 정보를 출력하세요.
--단, 고객등록 정보는 년, 월만 보이도록 합니다.
SELECT name, TO_CHAR(reg_date, 'YYYY-DD')
FROM member
WHERE reg_date > '23/05/01';
TO_DATE(문자)
- TO_DATE(문자, 출력형식): 문자열을 DATE유형으로 변환하는 함수
- TO_CHAR(날짜)와 상반된 기능을 갖는다. 즉, 문자 데이터를 강제로 날짜형 데이터로 변환시키는 것.
- TO_CHAR(날짜)는 출력을 위해 사용하는 함수이며, TO_DATE는 프로그램 내부에서 날짜를 계산하거나 비교하기 위해 날짜형 데이터로 변환하는 데 사용되는 함수이다.
- 결과값: CHAR나 VARCHAR2 형식의 변수를 날짜형 데이터 DATE로 변환하여 값을 리턴
만일 출력형식이 없다면 세션의 기본 날짜 출력형식을 사용해야 한다.
SELECT TO_DATE('22-08-19','YY-MM-DD') +3 FROM DUAL;
SELECT SYSDATE - TO_DATE('20191107','YYYYMMDD') FROM DUAL;
TO_CHAR(숫자)
- TO_CHAR(숫자, 출력형식)
- 결과값: 숫자를 오른쪽의 출력 형식에 맞는 VARCHAR2로 변환하여 리턴.
| 포맷요소 | 포맷된 문자 | 설명 |
| 9 | 99 | 각각의 9는 결과에서 유효자리를 나타냄 여기서 리턴되는 값은 9의 수와 같은 유효 자리의 수를 갖는다. |
| 0 | 999 | 빈칸이 아니라 앞에 붙은 0을 리턴함 |
| $ | $999 | 통화기호와는 무관하게 앞에 달러 기호를 가진 값을 리턴함 |
| B | B999 | 정수 부분이 0일 경우 소수의 정수 부분에 대해 빈칸을 리턴함 |
| S | S9999 | 앞에 붙은 기호를 리턴함 양수에 대해서는 "+", 음수에 대해서는 "-"를 리턴 |
| S | 9999S | 뒤에 붙은 기호를 리턴함 양수에 대해서는 "+", 음수에 대해서는 "-"를 리턴 |
| D | 99D9 | 지정된 위치에서 소수점을 리턴함 |
| G | 9G999 | 지정된 위치에서 그룹 구분 문자를 리턴함 |
| C | C99 | 지정된 위치에서 ISO 통화기호를 리턴함 |
| L | L999 | 지정된 위치에서 지역 통화기호를 리턴함 |
| EEEE | 9.99EEEE | 과학적 표기법을 사용하여 값을 리턴함 |
| RM | RM | 대문자 로마 숫자를 사용하여 값을 리턴함 |
| , | 999,999 | 지정된 위치에 ","를 표시함 |
| . | 999.99 | 소수점 자리를 표시함 |
--L: 통화기호 리턴
SELECT TO_CHAR(1500000,'L9,999,999') FROM DUAL;
--상품 테이블에서 상품의 공급 금액을 가격 표시 방법으로 표시하세요. 천자리 마다 , 를 표시합니다.
SELECT PRODUCTS_NAME, INPUT_PRICE, TO_CHAR(INPUT_PRICE,'9,999,999') 공급가
FROM PRODUCTS;
--상품 테이블에서 상품의 판매가를 출력하되 주화를 표시할 때 사용하는 방법을
--사용하여 출력하세요.[ISO 통화기호]
SELECT products_name, output_price, TO_CHAR(output_price,'C9,999,999') 판매가
FROM products;
TO_NUMBER(문자)
- TO_NUMBER(문자, 출력형식): CHAR, VARCHAR2를 숫자로 변환
-- '150,000'의 2배값을 구하세요
SELECT TO_NUMBER('150,000', '999,999') * 2 FROM DUAL; --300000
-- '$450.25'의 3배값을 구하세요
SELECT TO_NUMBER('$450.25','$999D99')*3 FROM DUAL; --1350.75
SELECT TO_CHAR(23,'99D99'), TO_CHAR(23,'99.99EEEE') FROM DUAL;
SELECT TO_CHAR(23, 'S99'), TO_CHAR(-23, '99S') FROM DUAL;
