[SQL 입문] SQL 단일행 함수

- SQL 함수

• SQL을 이용한 데이터 처리를 더 강력하게 해줌 > 선언적 sql 언어 보완
• 반드시 1개의 값을 리턴함
• 데이터의 복잡한 계산 수행
• 컬럼값 변환(=타입 변환)
• format 형식 변환
• select절, where절, order by 절에 사용 가능
• 중첩 사용 가능

 

- SQL 함수 종류

1. custom function 사용자 정의 함수(plsql 문법)

2. predefined function 내장 함수

• single row function(단일행 함수) : 주어진 문자열에서 특정 길이만 골라낼 때 사용
• multiple row function(복수행 함수, 그룹 함수)
• window function(analysis function) : 행과 행의 관계 정의

 

 

---

- single row function: 단일행 함수

** 참고 SQL Language Reference **
: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/index.html

0. 사용 위치 : select절, where절, order by절 

1. character function : 함수('문자열char', ~) number OR character 리턴

-- LOWER : 모든 문자열 소문자로 반환
-- UPPER : 모든 문자열 대문자로 반환
-- INITCAP : 첫 단어만 대문자로 반환

SELECT lower('Hello World'), upper('Hello World'), initcap('hello world')
FROM dual;

 

-- length : 문자수  반환
-- lengthb : 바이트 수 반환

SELECT length('korea'), length('대한민국'), lengthb('korea'), lengthb('대한민국')
FROM dual;

 

-- concat : 문자열 연결

SELECT concat(ename, concat(' works as a', jol))
FROM emp;

 

-- SUBSTR(chr, posiition, length) : 문자열의 일부분을 떼어내는 기능을 하는 함수

SELECT substr('Hello World', 7), substr('Hello World', 2, 4), substr('Hello World', -5)
FROM dual;

 

-- INSTR(string, search_string, position, occurrence) : 대상 문자열을 찾아 그 위치를 반환하는 함수

Q> EMP 테이블에서 이름 중 "L"자의 위치를 조회한다.

SELECT ename, instr(ename, 'L'), instr(ename, 'L', 1, 2), instr(ename, 'L', 4, 1) 
FROM emp;

 

Q > EMP 테이블에서 이름의 첫글자가 'K'보다 크고 'Y'보다 작은 사원의 사원번호, 이름, 업무, 급여, 부서번호를 조회한다. 결과는 이름순으로 정렬하라.

SELECT empno, ename, job, sal, deptno
FROM emp
WHERE substr(ename, 1, 1) > 'K' AND substr(ename, 1, 1) < 'Y'
ORDER BY ename;

 

-- LPAD(expr1, n, [expr2]) : expr1을 n자리 만큼 문자열 길이로 만들어 반환하는 함수
-- RPAD(expr1, n, [expr2]) 

# 급여 출력

SELECT sal, lpad(sal, 10, '$'), rpad(sal, 10, '$')
FROM emp;

 

--LTRIM(char [, set]) : 파라미터인 char에서 set으로 지정된 문자를 가장 왼쪽 끝에서 제거한 결과를 반환
--RTRIM(char [, set]) : 가장 오른쪽 끝에서 제거한 결과 반환

SELECT ltrim('Hello World', 'H'), ltrim('Hello World', 'e'), 
       rtrim('Hello World', 'd'), rtrim('Hello World', 'i')
FROM dual;

 

-- TRIM([leading, trailing, both] [, trim_char] [FROM] trim_source) : 공백 제거

SELECT length('   he   llo     '), trim('   he   llo     '), length(trim('   he   llo     '))
FROM dual;                          --중간은 삭제 안 됨

SELECT trim(leading 'A' FROM 'AABDCADD') 결과1,
           trim('A' FROM 'AABDCADD') 결과2,
           trim(trailing 'D' FROM 'AABDCADD') 결과3
FROM dual;

 

-- TRANSLATE(expr, from_string, to_string) : 문자열 하나를 일대일로 변환하는 작업을 수행
-- REPLACE(chr, search_string, replace_string) : char 문자열에서 search_string 값으로 들어온 문자를 replace_string값으로 대체하여 이 결과를 반환
-- CHR(이진법) : 이진법을 변환해서 출력

SELECT replace('JACK and JUE', 'J', 'BL')
FROM dual;

SELECT translate('SQL*Plus', ' *SQL', '_s3#')
FROM dual;

SELECT chr(67)||chr(65)||chr(84) "Dog"
FROM dual;

 

Q> EMP 테이블에서 20번 부서의 사원에 대하여 담당 업무 중 좌측에 'A'를 삭제하고 급여 중 좌측의 1을 삭제하여 출력하여라.

SELECT empno, job, LTRIM(job, 'A'), sal, LTRIM(sal, 1)
FROM emp
WHERE deptno = 20;

 

Q> EMP 테이블에서 10번 부서의 사원에 대하여 담당 업무 중 우측에 'T'를 삭제하고 급여 중 우측의 0을 삭제하여 출력하여라

SELECT ename, job, LTRIM(job, 'T'), sal, RTRIM(sal, 0)
FROM emp
WHERE deptno = 10;

 

Q> REPACE함수를 사용하여 사원이름에 SC문자열을 *?로 변경해서 조회하라

SELECT ename, job, REPLACE(ENAME, 'SC', '*?') --변경결과1
FROM emp;

 

Q> TRANSLATE함수를 사용하여 사원이름에 SC문자열을 *?로 변경해서 조회하라

SELECT ename, job, TRANSLATE(ename, 'SC', '*?') --변경결과2
FROM emp;

 

---

2. number function : 함수('number', ~) number 리턴

-- ROUND(n, [m]) : 반올림
-- TRUNC(n, [m])  : 버림
-- SIGN(n)              : n이 0보다 클 경우 1, 0보다 작을 경우 -1, 0일 경우 0을 반환
-- POWER(n1, n2) : n1의 n2 제곱값을 구하는 함수
-- MOD(n1, n2)      : n1을 n2로 나누어 남은 값을 반환

SELECT round(4767.678) 결과1, round(4767.678, 0) 결과2,
round(4767.678, 2) 결과3, round(4767.678, -2) 결과4
FROM dual;

SELECT trunc(4767.678) 결과1, trunc(4767.678, 0) 결과2,
trunc(4767.678, 2) 결과3, trunc(4767.678, -2) 결과4
FROM dual;

SELECT power(2, 10) 결과1, ceil(3.7) 결과2, floor(3.7) 결과3
FROM dual;

 

Q > EMP 테이블에서 급여를 30으로 나눈 나머지를 구하여 출력하라

SELECT sal, mod(sal, 30)
FROM emp;

 

Q > EMP 테이블에서 사번이 홀수인 사번 정보를 출력하라

SELECT empno, ename, sal
FROM emp
WHERE mod(empno, 2) = 1;

SELECT empno, sal, sign(sal-3000)
FROM emp
WHERE deptno = 20;

 

---

alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';  --우선 데이터 타입 변경

3. date function : 함수('date', ~) number OR date 리턴

-- date : 'YYYY-MM-DD' 
-- timestamp = date +10억 1초 단위까지 표현 및 저장
-- timestamp with timezone (+9:00 - 한국 기준)
-- interval year to month 기간
-- interval day to second (timestamp) 기간

-- SYSDATE : 'RR/MM/DD' > 데이터베이스 서버의 현재 날짜 및 시간을 반환하는 날짜 함수
-- SYSTIMESTAMP : 'RR/MM/DD HH:MI:SS.FF4 TIMEZONE'
     > 데이터베이스 서버의 현재 날짜와 시간을 timestamp with time zone 데이터 유형 값으로 반환

-- current_date : 'RR/MM/DD' > session의 timezone 기반으로 현재 날짜 및 시간을 반환하는 날짜 함수
-- current_timestamp : 'RR/MM/DD HH:MI:SS.FF4 국가/지역'
     >  session의 timezone 기반으로 현재 날짜 및 시간을 timestamp with time zone 데이터 유형 값으로 반환
-- SESSIONTIMEZONE : '국가/지역' > 현재 서버의 국가 및 지역 반환

alter session set time_zone = '+3:00';  --3시간 빠르게
SELECT SYSDATE, SYSTIMESTAMP, current_date, current_timestamp, sessiontimezone
FROM dual;

 

-- EXTRACT(찾을정보 FROM 입력날짜) : datetime필드를 이용해서 년도, 월, 일, 시, 분, 초를 추출해서 numeric값으로 반환

Q > 오늘 날짜에서 년 월 일 추출

SELECT extract(day from sysdate) 일자, 
              extract(month from sysdate) 월,
              extract(year from sysdate) 년도
FROM dual;

 

-- MONTHS_BETWEEN(date1, date2) : 두 날짜 사이의 개월 수 반환
-- ADD_MONTHS(date, integer) : 날짜에 개월 수를 더한 뒤 그 결과를 반환
-- NEXT_DAY(date, char) : 지정한 date 이후의 날짜 중에서 char로 명시된 요일에 해당되는 첫 번째 일자를 반환
-- ROUND(date, fmt) : 올림 
-- TRUNC(date, fmt) : 버림 

# 날짜관련 fmt : YEAR, YYYY, YYY, YY, Y, Q, MONTH, MON, MM, DD, HH, MI, SS

Q > EMP 테이블에서 10번 부서 사원의 현재까지의 근무 월수를 계산하여 조회

SELECT ename, hiredate, sysdate,
               months_between(sysdate, hiredate) m_between,
               trunc(months_between(sysdate, hiredate), 0) t_between
FROM emp
WHERE deptno = 10;

 

Q > EMP 테이블에서 입사 일자로부터 5개월이 지난 후의 날짜를 계산하여 조회

SELECT ename, hiredate, add_months(hiredate, 5) a_month
FROM emp;

 

Q > EMP 테이블에서 입사 일자로부터 돌아오는 금요일, 토요일을 계산하여 조회

SELECT ename, hiredate, next_day(hiredate, '금') "금요일", next_day(hiredate, '토') "토요일"
FROM emp;

SELECT ename, hiredate, next_day(hiredate, 6) n_6, next_day(hiredate, 7) n_7
FROM emp;

>> 둘 다 같은 값 반환

 

 

 

 

 

-- round / trunc

SELECT round(to_date('22/7/16'), 'MONTH') M_16,  --반올림
       round(to_date('22/7/15'), 'MONTH') M_15, 
       round(to_date('22/7/16'), 'YEAR') Y_16,  --반올림
       round(to_date('22/6/15'), 'YEAR') Y_15
FROM dual;

SELECT trunc(to_date('22/7/16'), 'MONTH') M_16,  --무조건 버림
       trunc(to_date('22/7/15'), 'MONTH') M_15, 
       trunc(to_date('22/7/16'), 'YEAR') Y_16, 
       trunc(to_date('22/6/15'), 'YEAR') Y_15
FROM dual;

 

---

4. conversion function

- 형식 : to_xxxx 

- 자동 형변환

• 변환함수 사요없이 DB서버가 형변환 처리
• SELECT 10||10 FROM dual;      --1010     >> 연결연산자니까 단순히 연결
• SELECT '10'+'10' FROM dual;   --출력 20 >> 산술연산자니까 자동 형변환해서 계산 > 만약 못하면 error

- 명시적 형변환

• date → character : to_char(date, 'format')
• character → number : to_number(char, '변환할 fmt의 형식과 동일') 
• number → character : to_char(number, 'fmt')
• character → date : to_date(char, '변환할 fmt의 형식과 동일')

SELECT sal, to_char(sal, '$999,000.00') FROM emp;  --arg1m, arg2 형식이 일치하지 않아도 변환됨

SELECT hiredate, to_char(hiredate, ' YYYY"년" MM"월" DD"일" ') FROM emp;

SELECT to_number('$12,345.50', '99,999.00') FROM dual;  --error 
SELECT to_number('$12,345.50', '$99,999.00') FROM dual; --동일하게 달러를 넣어야함 > $기회는 저장하지 않기 때문

SELECT to_date('2022년 1월 1일', 'YYYY-MM-DD') FROM dual; --error 
SELECT to_date('2022년 1월 1일', 'YYYY"년" MM"월" DD"일"') FROM dual; --동일한 형식으로 변경

 

date Format 지정 가능 : https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Format-Models.html#GUID-EAB212CF-C525-4ED8-9D3F-C76D08EEBC7A

number Format string : https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Format-Models.html#GUID-096CA64F-1DA3-4C49-A18B-ECC7518EE56C

 

-- 'WW' : Same day of the week as the first day of the year
-- 'W' : Same day of the week as the first day of the month
-- to_dsinterval(format) : 인수를 데이터 형식의 값으로 변환

Q > 오늘 날짜가 1년 중 몇 번째 주인지 조회

SELECT to_char(sysdate, 'WW') test  FROM dual;

 

-- TO_YMINTERVAL : CHAR,VARCHAR2,NCHAR,NVARCHAR2 데이터형의 문자열을 INTERVAL YEAR TO MONTH 형태로 변경

Q > 사원의 이름, 입사일, 입사일자, 일사일에서 1년 6개월 후의 날짜를 조회

SELECT ename, empno, hiredate, hiredate + to_yminterval('01-06') as date_1_6
FROM emp;

select ename, empno, hiredate, add_months(hiredate, 18) date_1_6
from EMP;

select ename, empno, hiredate, hiredate + to_dsinterval('547 12:00:00') AS date_1_6
from EMP;

 

---

5. general function 일반 함수 - null 처리, 조건처리 함수

-- null 처리

• nvl(arg1, arg2)
   : if arg1 is null then return arg2 else return arg1 
   : arg1과 arg2가 동일한 타입이어야 함.
• nvl2(arg1, arg2, arg3)
   :  if arg1 is null then return arg3 else return arg2 
   : arg2와 arg3이 동일한 타입이어야 함.
• coalesce(arg1, arg2, arg3, ..., argN)
   : 255개까지 설정 가능 
   : null이 아닌 최초의 인수를 return, 모든 인수(argument)가 동일한 타입이어야 함.
• nullif(arg1, arg2)
   : if arg1 = arg2 then return null else return arg1
   : arg1과 arg2가 동일한 타입이어야함.

-- nvl(arg1, arg2)
select comm, sal, comm+sal, nvl(comm, 0)+sal
from emp;

select comm, sal, comm+sal, nvl(comm, 'No Commission')
from emp; --error

-- nvl(arg1, arg2, arg3)
select comm, sal, comm+sal, nvl2(comm, sal+comm, sal*1.1)
from emp;

select comm, sal, comm+sal, nvl2(comm, sal+comm, 'sal*1.1')
from emp;

select comm, sal, comm+sal, nvl2(comm, 'sal+comm', 'sal*1.1')
from emp;

-- coalesce
select coalesce('a', null, null, 'b') ab,
       coalesce(null, null, 'b', 'a') ba,
       coalesce(null, null, null, null, 0) null_0
from dual;

-- nullif
select nullif('A', 'a'), nullif(100, 100)
from dual;

select nullif('A', 100)
from dual; --error

 

-- 조건처리 : 함수, sql표준 구문(표현식) > 오라클이 아닌 다른 DB에서도 사용 가능

• decode(컬럼|표현식, 비교값1, 리턴값1, 비교값2, 리턴값2, 비교값3, 리턴값3, ..., 리턴값last) : 연산 비교 수행
• case 컬럼|표현식 when 비교값1 return 리턴값1
                             [ when 비교값2 return 리턴값2
                               .......
                               else 리턴값last ] end
• case when 비교조건1 then 처리1        >> when 비교 조건 구문에서 true/false 결과
          [ when 비교조건2 then 처리2
          ......
          else 디폴트처리 ] end

select DECODE (9+1, 9, '정답1', 10, '정답2') from dual;  --출력: 정답2
select DECODE (9+1, 9, '정답1', 11, '정답2', '정답3') from dual; --출력: 정답3

 

Q. emp 사원의 담당 업무가 ANALYST인 경우 급여는 10% 증가, CLERK인 경우 급여는 20% 증가, MANAGER인 경우 30% 증가, PRESIDENT인 경우 40% 증가, SALESMAN인 경우 50% 증가, 그 외엔 기존 급여 값으로 나오도록 조회한다.

SELECT ename, job, decode(job, 'ANALYST', sal*1.1,
                               'CLERK', sal*1.2,
                               'MANAGER', sal*1.3,
                               'PRESIDENT', sal*1.4,
                               'SALESMAN', sal*1.5) "Increase Salary"
FROM emp;

SELECT ename, job, case job when 'ANALYST' then sal*1.1
                            when 'CLERK' then sal*1.2
                            when 'MANAGER' then sal*1.3
                            when 'PRESIDENT' then sal*1.4
                            when 'SALESMAN' then sal*1.5 
                            else sal end "Increase Salary"
FROM emp;

 

-- decode는 비교연산자 사용이 불가능

Q. 사원번호, 이름, 급여, 급여의 세금을 출력(decode 와 case when then).
 > 급여가 1000미만이면 0, 1000이상 2000미만이면 3%, 2000이상 3000미만이면 6%, 3000이상 4000미만이면 9%, 4000이상 12%. 

-- 나눠서 몫을 비교 > 천대, 이천대, 삼천대, 그 외 이렇게
SELECT empno, ename, sal, decode(trunc(sal/1000), 0, 0,
                               1, sal*0.03,
                               2, sal*0.06,
                               3, sal*0.09,
                               sal*0.12) "tax"
FROM emp;

--case는 비교연산자 활용가능
SELECT empno, ename, sal, case when (sal<1000) then 0
                            when (sal>=1000 and sal<2000) then sal*0.03
                            when (sal>=2000 and sal<3000) then sal*0.06
                            when (sal>=3000 and sal<4000) then sal*0.09
                            when (sal>=4000) then sal*0.12
                            else sal end "tax"
FROM emp;

 

* 내용참고&출처 : 태그의 수업을 복습 목적으로 정리한 내용입니다.