Oracle SQL(3)

단일행 함수

1. RTRIM

• 문자열의 마지막 문자부터 시작해서 지정된 문자와 일치하지 않는 문자가 나올 때까지 계속적으로 해당문자를 제거하는 함수이다.
• RTRIM( 컬럼명 | 표현식, 'str')
• str은 문자열에서 삭제할 문자를 의미한다.

SELECT RTRIM('MILLER, 'R')
FROM dual;

2. TRIM

• 문자열의 양쪽을 삭제하는 함수이다.

TRIM( LEADING 'str' FROM 컬럼명 | 표현식)
TRIM( TRAILING 'str' FROM 컬럼명 | 표현식)
TRIM( BOTH 'str' FROM 컬럼명 | 표현식)

• str은 삭제할 문자를 의미한다.
• LEADING : 왼쪽 삭제
• TRAILING : 오른쪽 삭제
• BOTH : 양쪽을 모두 삭제

SELECT TRIM ('0' FROM '00001234560000')
FROM dual;
-- 123456

• 키워드를 생략하는 경우에는 자동으로 BOTH로 적용되어 양쪽이 삭제된다.

3. ROUND

• 지정한 자리수 이하에서 반올림한 결과를 반환하는 함수이다.
• ROUND (컬럼명 | 표현식, [n])
• n은 반올림하여 출력하기 위한 자리수를 의미하고 n이 양수이면 소수자리를 반올림하고 음수이면 정수 자리를 반올림 한다. 생략하면 기본 값이 0이기 때문에 소수점에서 반올림이 된다.

SELECT ROUND(456.789, 2)
FROM dual;
-- 456.79

SELECT ROUND(456.789, -1)
FROM dual;
-- 460

SELECT ROUND(456.789)
FROM dual;
--457

4. TRUNC

• 지정한 자리수 이하에서 절삭한 결과를 반환하는 함수이다.
• TRUNC( 컬럼명 | 표현식, [n])
• n은 절삭을 위한 자리수를 의미한다.
• n > 0 : 소수 자리를 절삭
• n < 0 : 정수 자리를 절삭
• n = 0 : 소수점에서 절삭

SELECT TRUNC(456.769, 2)
FROM dual;
-- 456.76

SELECT TRUNC(456.769, -2)
FROM dual;
-- 400

SELECT TRUNC(456.769)
FROM dual;
-- 456

5. MOD

• 나누기 연산을 한 후에 나머지를 반환하는 함수이다.
• MOD( 컬럼명 | 표현식, n)
• n은 나눌 값을 의미한다. 만약 n이 0이라면 값 자체를 반환한다.

SELECT MOD(10, 3), MOD(10, 0)
FROM dual;
-- 1, 10

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

6. CEIL

• 소수점을 가진 실수값을 정수값으로 반환하는 함수로서, 주어진 숫자보다 크거나 같은 최소 정수값을 반환한다.
• CEIL( 컬럼명 | 표현식 )

SELECT CEIL(10.6), CEIL(-10.6)
FROM dual;
-- 11, -10

7. FLOOR

• 소수점을 가진 실수 값을 반환하는 함수로서, 주어진 숫자보다 작거나 같은 최대 정수값을 반환한다.
• FLOOR( 컬럼명 | 표현식 )

SELECT FLOOR(10.6), FLOOR(-10.6)
FROM dual;
-- 10, -11

8. SIGN

• 지정된 값이 양수인지 음수인지 또는 0인지 판단해주는 함수이다.
• SIGN( 컬럼명 | 표현식 )
• 값 > 0 : 1
• 값 < 0 : -1
• 값 = 0 : 0

SELECT SIGN( 100 ), SIGN(-20), SIGN(0)
FROM dual;
-- 1, -1, 0

오라클은 세기, 년, 월, 일, 시, 분, 초를 내부적으로 7byte 형식의 숫자로 관리한다.
따라서, 날짜 데이터의 산술연산이 가능하다.

연산	결과	설명
날짜 + 숫자	날짜	날짜에 일수를 더하여 반환한다.
날짜 - 숫자	날짜	날짜에 일수를 빼고 반환한다.
날짜 - 날짜	숫자(일수)	두 날짜의 차이(일수)를 반환한다.
날짜 + 숫자/24	날짜	날짜에 시간을 더한다.

9. SYSDATE

• 오라클이 설치된 시스템의 현재 날짜를 반환하는 함수이다.

SELECT SYSDATE
FROM dual;
--21/01/07

SELECT SYSDATE 오늘, SYSDATE + 1 내일, SYSDATE - 1 어제
FROM dual;
--21/01/07, 21/01/08, 21/01/06

-- 지금까지 근무한 년도 구하기 
SELECT ename, hiredate, TRUNC((SYSDATE - hiredate) / 365)  "년"
FROM emp
ORDER BY 3 DESC;

10. MONTHS_BETWEEN

• 날짜와 날짜 사이의 개월 수를 반환하는 함수이다.
• MONTHS_BETWEEN(date1, date2)

SELECT MONTHS_BETWEEN(SYSDATE, '00/01/01')
FROM dual;
--252.20848....

• 실행결과에서 252는 월을 나타내고 소수점 자리 이하의 수는 월의 일부분을 나타낸다.

11. ADD_MONTHS

• 지정된 날짜에 특정 개월 수를 더하거나 뺀 날짜를 반환하는 함수이다.
• ADD_MONTHS(date1, n)
• date1 : 지정된 날짜
• n : 더하거나 뺄 개월 수

SELECT ADD_MONTHS('20/01/07', 5), ADD_MONTHS('20/01/07', -5)
FROM dual;
-- 20/06/07, 19/08/07

SELECT sysdate 현재, ADD_MONTHS(sysdate, 1) 다음달,
	ADD_MONTHS(sysdate, -1) 이전달,
FROM dual;

12. NEXT_DAY

• 지정된 날짜를 기준으로 돌아오는 가장 가까운 요일에 해당하는 날짜를 반환하는 함수이다.
• NETX_DAY(date1, 'string'|n)
• date1은 지정된 날짜를 의미하고, 'string' 값은 돌아오는 요일을 의미한다.
• n 값은 문자 요일값 대신에 숫자 값으로 표현 가능함을 나타낸다.

--       1      2          3                           4
SELECT ename, hiredate, NEXT_DAY(hiredate, '금'), NEXT_DAY(hiredate, 6)
FROM emp
ORDER BY 3 DESC;
-- 3과 4는 같은 결과

• 지정된 요일은 '금' 또는 '금요일'처럼 한글로 지정해도 되고 숫자 6으로 지정해도 실행결과는 동일하다.

13. LAST_DAY

• 해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수로서 윤년 몇 평년 날짜를 자동으로 계산한다.
• LAST_DAY(date1)

SELECT ename, hiredate, LAST_DAY(hiredate)
FROM emp
ORDER BY 2 desc;

변환 함수

함수	설명	반환 값
TO_NUMBER	문자 데이터를 숫자 데이터로 변환한다.	숫자
TO_DATE	문자 데이터를 날짜 데이터로 변환한다.	날짜
TO_CHAR	숫자 데이터를 문자데이터로 변환하거나 날짜데이터를 문자 데이터로 변환한다.	문자

SELECT empno, ename
FROM emp
WHERE empno = TO_NUMBER('7900');

SELECT empno, ename, hiredate
FROM emp
WHERE hiredate = TO_DATE('82/01/23');

• 형변환을 할 때 두 번째 인자로 형식을 지정할 수 있다.
  
  

SELECT TO_CHAR(sysdate, 'YYYY/MM/DD, (AM) DY HH24:MI:SS')
FROM dual;
-- 2021/01/07, (오후) 목 12:38:36

SELECT TO_CHAR(sysdate, 'YYYY "년"')
FROM dual;

SELECT TO_CHAR(sysdate, 'YYYY "년" MM"월" DD"일"')
FROM dual;

SELECT empno, ename, TO_CHAR(hiredate, 'YYYY"년" MM"월" DD"일"')
FROM emp
WHERE deptno = 10;

SELECT empno, ename, hiredate, sal
FROM emp
WHERE TO_CHAR(hiredate, 'MM') = '09';

SELECT empno, ename, hiredate, sal
FROM emp
WHERE TO_CHAR(hiredate, 'MM') = '09'
ORDER BY 3;

SELECT ename, TO_CHAR(sal, 'L999,999')  원화, TO_CHAR(sal, '$999,999') 달러
FROM emp;

SELECT TO_NUMBER('123456') + 100
FROM dual;

SELECT TO_DATE(20170802181030, 'YYYYMMDDHH24MISS')
FROM dual;

SELECT SYSDATE, SYSDATE-TO_DATE('20170801', 'YYYYMMDD')
FROM dual;

SELECT TRUNC((SYSDATE-TO_DATE('2000', 'YYYY')) / 365) + 1 as 나이
FROM dual;

조건 함수

함수	설명
DECODE	조건이 반드시 일치하는 경우에 사용하는 함수
CASE	조건이 반드시 일치하지 않아도 범위 및 비교가 가능한 경우에 사용하는 함수

1. DECODE

• 문법

DECODE ( 컬럼, 비교값1, 결과값1,
	      비교값2, 결과값2,
              ...
              비교값n, 결과갑수,
              기본결과값)
              
SELECT empno, ename, sal, job,
DECODE( job, 'ANALYST' , sal * 1.1,
             'CLERK',   sal * 1.2,
             'MANAGER', sal * 1.3,
             'PRESIDENT', sal * 1.4, sal) "급여"
FROM emp;

SELECT empno, ename, sal,
DECODE(sal, 5000, sal + 1000,
                    3000, sal + 1000,
                    sal) bonus
FROM emp;

2. CASE

CASE 컬럼 WHEN 비교값1 THEN 결과값1
	 WHEN 비교값2 THEN 결과값2
     	 ELSE 결과값n
END

SELECT empno, ename, 
CASE WHEN sal >= 0 AND sal <= 1000 THEN 'E'
          WHEN sal > 1000 AND sal <= 2000 THEN 'D'
          WHEN sal > 2000 AND sal <= 3000 THEN 'C'
          WHEN sal > 3000 AND sal <= 4000 THEN 'B'
          WHEN sal > 4000 AND sal <= 5000 THEN 'A'
        END "등급"
FROM emp;

SELECT empno, ename, 
CASE WHEN sal >= 0 AND sal <= 1000 THEN 'E'
          WHEN sal > 1000 AND sal <= 2000 THEN 'D'
          WHEN sal > 2000 AND sal <= 3000 THEN 'C'
          WHEN sal > 3000 AND sal <= 4000 THEN 'B'
          WHEN sal > 4000 AND sal <= 5000 THEN 'A'
        END "등급"
FROM emp;

SELECT empno, ename, sal, 
CASE WHEN sal = 3000 AND sal = 5000 THEN sal + 1000
     ELSE sal
     END 보너스
FROM emp;