[SQL] SQL 함수

SELECT 문을 사용하여 특정 쿼리들을 출력할 때, 함수들을 사용하여 데이터를 효과적으로 출력할 수 있다. SQL 함수는 기본적인 쿼리문을 더욱 강력하게 해주고 질의문을 짤 떄 쉽게 짤 수 있게 한다. SQL 함수는 단일 행 함수와 다중 행 함수(그룹함수) 가 있다. 

 

* SQL 언어 특성

- 선언적 언어

- 결과지향적 언어

- 과정 기술 못함

- 예외처리 못함

- 변수 사용 못함

- 명시적으로 IF 조건처리 (CASE , DECODE), FOR 반복처리 어려움  

 

이러한 특성들 때문에 SQL은 함수를 사용하여 데이터의 계산이나 FORMAT 형식변환, 컬럼값 변환 , 중첩사용 같은 것들을 POWERFUL하게 처리가 가능하다.

 

 

* 단일 행 함수 

단일 행 함수는 행 하나당 하나의 결과를 반환한다. SELECT ,WHERE, OREDER BY절에 사용할 수 있고 , 데이터 타입을 변경 할 수 있으며 중복 사용이 가능하다. 

 

 

* 문자 함수

1) LOWER : 모든 문자를 소문자로 변환해 준다

2) UPPER : 모든 문자를 대문자로 변환해 준다.

3) INITCAP : 단어의 첫 문자는 대문자 나머지는 소문자로 변환해준다.

select upper('hello') RESULT_1 , lower('HELLO') RESULT_2, initcap('hello world') RESULT_3
FROM DUAL;

RESULT)

RESULT_1	RESULT_2	RESULT_3
HELLO	hello	Hello World

 

4) CONCAT : 2개 이상의 문자열을 결합시켜준다.

SELECT CONCAT(EMPNO,ENAME) RESULT
FROM EMP;

 

5) SUBSTR : 문자열 중 지정한 위치에서 지정한 길이 만큼의 문자열을 추출한다. SUBSTR(컬럼,출력할 순서(INDEX+1),출력할 문자 길이)

--substr(char, position, length)
select substr('hello world',7) , substr('hello world',3,3)
from dual;


select substr('hello world',-5) , substr('hello world',-10,3)
from dual;

RESULT ) 

RESULT_1	RESULT_2
world	llo

6) LENGTH : 문자의 길이를 숫자값으로 표시한다. 

+ LENGTHB : 문자의 길이를 바이트 값으로 표시한다.

select length('korea'), length('대한민국'),lengthb('korea'), lengthb('대한민국')
from dual;

7) INSTR : 문자열 내의 특정 스트링의 위치를 숫자로 표시한다.

--instr(char,substring,position,occurrence)
select instr('hello world','o') , instr('hello world','o',6),instr('hello world','o',3,2)
from dual;

8) LPAD : 문자열을 제외한 왼쪽 공간에 지정한 문자로 채움

9) RPAD :  문자열을 제외한 오른쪽 공간에 지정한 문자로 채운다

LPAD와 RPAD의 문법은 (처음 문자열, 최종 문자열 길이 , 채울 문자)

select lpad(sal,10,'*') ,rpad(sal,10,'*')
from emp;

10) LTRIM  : 왼쪽에 나오는 지정 문자를 제거한다

11) RTRIM : 오른쪽에 나오는 지정 문자를 제거한다.

LTRIM과 RTRIM의 문법은 (처음 문자열, 삭제할 문자)

select ltrim('hello world','h'),ltrim('hello world','hello'),ltrim('hello world','llo'),rtrim('hello world','rld')
from dual;

12) TRIM : 앞, 뒤에 나오는 특정문자를 제거한다.

select length('  hello world   '), length(trim('  hello  world    '))
from dual;

13) TRANSLATE : 첫 문자는 탐색집합의 첫 문자로 대체한다.

14) REPLACE : 특정 문자열을 다른 문자열로 대체한다. 

* 숫자 함수

1) ROUND(숫자, 반올림할 자릿수) : 지정된 자리로 반올림을 수행한다

SELECT ROUND(123.4567,2) RESULT_1, ROUND(123.4567) RESULT_2, ROUND(123.4567,-1) RESULT_3
FROM DUAL;

RESULT)

RESULT_1	RESULT_2	RESULT_3
123.46	123	120

2) TRUNC(숫자,버림할 자릿수) : 지정된 자리에서 버림을 수행한다

SELECT ROUND(123.4567,2) RESULT_1, ROUND(123.4567) RESULT_2, ROUND(123.4567,-1) RESULT_3
FROM DUAL;

RESULT)

RESULT_1	RESULT_2	RESULT_3
123.45	123	120

3) MOD(M,N) : M을 N으로 나눈 나머지를 출력한다

4) ABS(N) : N 값을 절대값으로 바꾼다

5) FLOOR(N) : 해당 수보다 작거나 같은 정수중 가장 큰 정수 값을 리턴한다. (FLOOR(N) < N) 

6) CEIL(N) : 해당 수보다 크거나 같은 정수중 가장 작은 정수 값을 리턴한다.(N < CEIL(N))

SELECT FLOOR(15.7) RESULT_1, CEIL(15.1) RESULT_2
FROM DUAL;

RESULT)

RESULT_1	RESULT_2
15	16

7) SIGN(N) :  숫자(N)가 양수이면 1 , 음수이면 -1 ,  0 이면 0을 반환

8) POWER(M,N) : M을 N번 제곱한 후 출력한다.

* 날짜 함수 

※ 날짜 함수를 사용하기 위해서는 테이블에 저장된 데이터에 맞게 날짜 형식을 맞춰 주어야 한다.

 

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS';
SELECT SYSDATE ,SYSTIMESTAMP,CURRENT_DATE,CURRENT_TIMESTAMP
FROM DUAL;
ALTER SESSION SET TIME_ZONE = '+3:00';

SELECT SYSDATE ,SYSTIMESTAMP,CURRENT_DATE,CURRENT_TIMESTAMP
FROM DUAL;

SELECT SYSDATE "현재 날짜" ,SYSTIMESTAMP  "현재 날짜 시간",CURRENT_DATE "현재 날짜",CURRENT_TIMESTAMP "현재 날짜 시간 지역"
FROM DUAL;

RESULT)

현재 날짜	현재 날짜 시간	현재 날짜_1	현재 날짜 시간 지역
23/02/07	23/02/07 22:10:47.187000000 +09:00	23/02/07	23/02/07 22:10:47.187000000 ASIA/SEOUL

1) MONTHS_BETWEEN(D1,D2) : D1,D2 구 날짜 간의 경과 개월 수

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)) "근무 개월"
FROM EMP;

2) ADD_MONTHS(D1(날짜 형식),N) :D1날짜에 N개월 더함

SELECT ADD_MONTHS(HIREDATE,6)
FROM EMP;

3) NEXT_DAY(D1(날짜 형식),'CHAR'(요일 형식)) : D1보다 이후 날짜며 지정한 요일에 해당되는 날짜

--사원들의 입사일 이후 첫번째 돌아오는 금요일 날짜 조회
SELECT NEXT_DAY(HIREDATE,'금')
FROM EMP;

4) LAST_DAY(D(날짜 형식)): 해당 월의 마지막 날짜를 리턴

--어느해의 2월의 마지막날 조회
ALTER SESSION SET NLS_DATE_FORMAT = 'RR/MM/DD';
SELECT LAST_DAY('99/2/1'), LAST_DAY('20/2/20')
FROM DUAL;

5) ROUND(D(날짜 형식), ('MONTH' OR 'YEAR')) :  날짜를 년,월 단위로 반올림

6) TRUNC : 날짜를 년, 월 단위로 버림

  

* TIMESTAMP 함수

TIMESTAMP 함수도 날짜 데이터를 사용하기 때문에 TIME 함수처럼 테이블에 저장된 데이터 형식에 맞게 데이터를 바꿔주어야 한다.

 

                            

1) CURRENT_DATE : 해당 세션 시간대의 현재 날짜를 반환

2) CURRENT_TIMESTAMP : 세션 시간대의 현재 날짜와 소수점 이하의 초단위의 시간을 표시

3) LOCALTIMESTAMP : 세션 시간대의 현재 날짜와 시간을 TIMESTAMP 데이터 유형으로 표시

4) DBTIMEZONE : 데이터베이스의 시간대 값을 표시

5) SESSIONTIMEZONE : 세션의 시간대 값을 표시

6) EXTRACT : DATETIME에 지정된 필드의 값을 추출

7) FROM_TZ : TIMESTAMP값을 'TIMESTAMP WITH TIME ZONE' 값으로 변환

8) TO_TIMESTAMP : 무낮열을 TIMESTAMP데이터 유형 값으로 변환

9) TO_YMINTERVAL : 문자열을 INTERVAL YEAR TO MONTH데이터 유형으로 변환

10) TO_NUMBER(CHARACTER) : 문자열을 숫자로 변환

11) TO_CHAR(NUMBER OR DATE) : 숫자나 날짜 데이터를 문자로 변환

-- 급여데이터를 달러단위로 변환
SELECT EMPNO,ENAME,SAL,TO_CHAR(SAL,'$999,999') FROM EMP
WHERE DEPTNO = 20;

12) TO_DATE(CHARACTER) : 문자열 데이터를 날짜 데이터로 변환                      

 

 

 

* 기타 함수

1) COALESCE : exp1,exp2...expN 중 null이 아닌 첫 번쨰 값을 이용

2) NVL(arg1,arg2) : NULL 처리 함수, arg1 값이 NULL인 경우 arg2을 출력하고, NULL이 아니면 arg1 값을 그대로 출력한다. (if arg1 == null -> return arg2 else return arg1)

3) NVL(arg1, arg2,arg3) :  arg1 컬럼이 null이면 arg3를 , null이 아니면 arg2값을 출력한다. (if arg1 == null -> return arg3 else return arg2)

4) NULLIF(exp1,exp2) : exp1과 exp2값을 비교해 같으면 null, 다르면 exp1값을 리턴 (if exp1==exp2 -> return null else return exp1)

SELECT COALESCE(1,2,NULL,3,4) , COALESCE(NULL,NULL,3,4,5)
FROM DUAL;

SELECT NVL(NULL,100),NVL(100,200),NVL(100,NULL)
FROM DUAL;

SELECT NVL2(NULL,100,200),NVL2(1,100,200)
FROM DUAL;

SELECT NULLIF(100,100) , NULLIF(100,200)
FROM DUAL;

 

* 조건 함수 

자바나 파이썬 , C 문법에서 조건문 IF ~ ELSE 같은 역할을 하는 SQL 문법이다. 조건부 조회를 손쉽게 수행한다. 

 

1) DECODE(VALUE,조건값1,리턴값1,조건값2,리턴값2...) "별칭"

DECODE([검사대상이 될 열 또는 데이터, 연산이나 함수의 결과] ,
[조건1], [조건 1과 일치할 때 반환할 결과], [조건 2],[조건 2와 일치할 때 반환할 결과],
[조건3], [조건 3과 일치할 때 반환할 결과], [조건 4],[조건 2와 일치할 때 반환할 결과],
[ELSE 반환]) "별칭"


-- 연봉에 따른 세금 부여(DECODE)
SELECT EMPNO, ENAME, SAL,
    DECODE(TRUNC(SAL/1000),0,0, 1,SAL * 0.05,2,SAL * 0.1, 3, SAL * 0.15, SAL* 0.2)"TAX"
FROM EMP;


-- 직군에 따른 연봉 인상 계산
SELECT EMPNO,ENAME, JOB, SAL,
DECODE(JOB, 'MANAGER',SAL*1.0,'SALESMAN',SAL*1.05,'ANALIST',SAL,SAL*1.03) "연봉인상"
FROM EMP;

 

2)  CASE (조건 컬럼) WHEN (조건값) THEN (리턴값) ELSE (리턴값) "별칭"

CASE [검사 대상이 될 열 또는 데이터 , 연산이나 함수의 데이터].
WHEN[조건 1] THEN[조건 1의 결과값이 TRUE 일 때 반환할 결과]
WHEN[조건 2] THEN[조건 2의 결과값이 TRUE 일 때 반환할 결과]
WHEN[조건 3] THEN[조건 3의 결과값이 TRUE 일 때 반환할 결과]...
ELSE[위 조건 중 일치하는 결과값이 없을 때 반환]
END "별칭"



-- 부서번호에 따른 연봉 인상
SELECT EMPNO, ENAME, DEPTNO, SAL,
    CASE DEPTNO WHEN 10 THEN SAL * 1.03
    WHEN 20 THEN SAL * 1.05
    WHEN 30 THEN SAL * 1.1
    ELSE SAL  END "연봉인상"
FROM EMP;


-- 연봉에 따른 세금 부여(CASE)
SELECT EMPNO,ENAME, SAL,
CASE WHEN SAL >= 1000 AND 2000 > SAL THEN SAL * 0.05
    WHEN SAL >= 2000 AND 3000 > SAL THEN SAL * 0.1
    WHEN SAL >= 3000 AND 4000 > SAL THEN SAL * 0.15
    WHEN SAL >= 4000 THEN SAL * 0.2
    ELSE SAL * 0 END "TAX"
FROM EMP;


-- COMM에 따른 상여금 출력
SELECT EMPNO, ENAME, COMM ,
CASE WHEN COMM IS NULL THEN '해당사항없음'
WHEN COMM = 0 THEN '수당없음'
WHEN COMM > 0 THEN '수당 : ' ||  COMM
END "상여금"
FROM EMP;

코딩테스트 연습 - 경기도에 위치한 식품창고 목록 출력하기 | 프로그래머스 스쿨 (programmers.co.kr)

프로그래머스

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, 
CASE WHEN FREEZER_YN  = 'Y' THEN 'Y'
WHEN FREEZER_YN = 'N' THEN 'N'
ELSE 'N' END FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE "경기도%"
ORDER BY WAREHOUSE_ID ASC

* 집합 함수

집합연산자를 통해 SELECT 문을 통해 데이터를 조회한 결과를 하나의 집합과 같이 다룰 수 있다. 집합 연산자는  두 개 이상의 SELECT 문의 결과 값을 연결할 때 사용한다. 집합 연산자를 사용 할 때 두 개의 SELECT 문의 열 개수와 열 자료형은 서로 일치해야 한다.(스키마 형이 서로 같아야 함)

 

1) UNION : 합집합(U)을 의미한다. 연결된 SELECT 문의 결과 첫번째 결과집합으로 정렬수핼 를 합집합으로 묶어준다, 중복된 결과값은 제거한다. (중복된 결과값은 한번만 출력)

SELECT EMPNO, ENAME,SAL,DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT EMPNO,ENAME,SAL,DEPTNO
FROM EMP
WHERE DEPTNO = 20;

 

2) UNION ALL: 합집합(U)을 의미한다. 연결된 SELECT 문의 결과를 정렬 없이 합집합으로 묶어준다, UNION과 다른점은 중복된 결과를 제거하지 않고 같이 출력한다는 것이다. (첫번째 결과 집합(SELECT) + 두번쨰 결과 집합(SELECT))

SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE DEPTNO = 20
UNION ALL
SELECT EMPNO,ENAME,SAL 
FROM EMP
WHERE DEPTNO = 20;

3) INTERSECT : 교집합을 의미한다. SELECT의 결과집합에서 첫번째 컬럼으로 정렬수행후 비교해서 먼저 작성한 SELECT과 다음 SELECT문의 결과 값이 같은 데이터만 출력(중복레코드만 출력)

SELECT EMPNO,ENAME,SAL
FROM EMP
WHERE ENAME NOT LIKE 'A%'
INTERSECT
SELECT EMPNO,ENAME,SAL 
FROM EMP
WHERE DEPTNO = 20;

4) MINUS :  차집합을 의미한다. 먼저 작성한 SELECT문에서 결과 값에서 다은 SELECT문의 결과 값을 차집합 처리한다. 먼저 작성한 SELECT 문에서 중복된 데이터를 제거하고 출력한다. (첫번쨰 결과집합 - 중복레코드)

SELECT EMPNO,ENAME,SAL
FROM EMP
MINUS
SELECT EMPNO,ENAME,SAL 
FROM EMP
WHERE DEPTNO = 20;