📗단일행 함수 - 숫자 관련 함수
| 이름 | 설명 | 사용 예 |
|---|---|---|
| ROUND | 소수점 N번째 자리까지 나타낼 때, 그 N+1번째 자리에서 반올림 | ROUND(12.345,2) ⇒ 12.35 |
| TRUNC | 소수점 N번째 자리까지만 나타낼 때, N+1의 자리에서 버림 | TRUNC(12.345,2) ⇒ 12.34 |
| MOD | 나머지 | MOD(12,10) ⇒ 2 |
| CEIL | 소수점 올림(정수로 만들어버림) | CEIL(12.345) ⇒13 |
| FLOOR | 소수점 내림(정수로 만들어버림) | FLOOR(12.345) ⇒12 |
| POWER | 제곱 | POWER (3,2) ⇒ 9 |
📖예제 1
SELECT ROUND(987.654,2)"ROUND" -- 소수점 세번째 자리에서 반올림
-- 소수점 두번째 자리까지만 나오게 하기
, ROUND(987.654,0)"ROUND" -- 소수점 첫번째 자리에서 반올림
, ROUND(987.654,-1)"ROUND" -- 일의 자리에서 반올림
, ROUND(987.654,-2)"ROUND" -- 십의 자리에서 반올림
, TRUNC (987.654,2)"ROUND" -- 소수점 3번째 자리에서 버림
-- 소수점 두번째 자리까지만 나오게 하기
, TRUNC (987.654,0)"ROUND" -- 소수점 아래의 수 버림
, TRUNC (987.654,-1)"ROUND" -- 일의 자리에서 버림
FROM DUAL;
📖예제 2
SELECT MOD(121,10) "A" --나머지
, CEIL(123.45) -- 소수점 일의 자리에서 올림
, FLOOR (123.45) -- 소수점 일의 자리에서 버림
, POWER(2,3) -- 2^3
FROM DUAL;
📗단일행 함수 - 날짜 관련 함수
| 함수명 | 설명 |
|---|---|
| SYSDATE | 오늘 날짜 출력 |
| MONTHS_BETWEEN | 달과 달 사이를 숫자로 표시 (한달보다 짧으면 소수점이 됨) |
| ADD_MONTHS | 달 더하기 |
| NEXT_DAY | 기준 날짜에 가장 가까운 기준 요일의 날짜 |
| LAST_DAY | 그 날짜에 해당되는 달의 가장 마지막 날짜 |
📖예제
SELECT SYSDATE -- 오늘 날짜 출력
, MONTHS_BETWEEN('22/07/19','22/06/19') "미래, 과거 : 양수"
, MONTHS_BETWEEN('22/06/19','22/07/19') "과거, 미래 : 음수"
, ADD_MONTHS(SYSDATE, 1) --오늘날+1달
, '22/07/19'
, NEXT_DAY('22/07/19','월')
, NEXT_DAY('22/07/19','화')
, NEXT_DAY('22/07/19','수')
, NEXT_DAY('22/07/19','목')
, LAST_DAY(SYSDATE)
, LAST_DAY('22/07/19')
FROM DUAL;
📗단일 행 함수 - 명시적 형변환
- 한번에 날짜에서 숫자로, 숫자에서 날짜로 바뀌는 함수 없음
- 만약에 날짜에서 숫자로 바꾸고 싶다!
⇒ TO.CHAR한 다음에 TO.NUMBER 사용해야함
📗단일 행 함수 - 형 변환 함수(11g 기준)
| 데이터 타입 | 설명 |
|---|---|
| CHAR(n) | 최대 2000 bytes 까지 입력가능(고정)/ 딱 n byte까지의 글자 입력 가능(n-1)bytes, (n+1)bytes 안됨 |
| VARCHAR2(n) | 최대 4000bytes까지 입력 가능(가변) |
| NUMBER(p,s) | 정밀도(precision, 전체 자리수) :1~38(자리 수까지) / 스케일(scale, 소수점 이하 자리수): -84~127 |
| DATE | BC 4712 ~ AD 9999 |
| LONG | 최대 크기 2GB |
| CLOB | 최대 크기 4GB |
| BLOB | 최대 크기 4GB |
📖예제 1
SELECT 2+2
, 2+'2' -- 묵시적 (자동 형변환)
, 2+TO_NUMBER('2') -- 명시적 (수동 형변환)
-- , 2+'A' → 오류 발생
FROM DUAL;
📖예제 2
SELECT SYSDATE
, TO_CHAR(SYSDATE, 'YYYY') "Y" -- 4글자 표현
, TO_CHAR(SYSDATE, 'YY') "Y" -- 2글자 표현
, TO_CHAR(SYSDATE, 'YEAR') "Y" -- 영어로 표현
, TO_CHAR(SYSDATE, 'MM') "M" -- 2글자 표현
, TO_CHAR(SYSDATE, 'MON') "M" -- 한글로 표시
, TO_CHAR(SYSDATE, 'MONTH') "M" -- 한글로 표시
, TO_CHAR(SYSDATE, 'DD') "D" -- 2글자 표시
, TO_CHAR(SYSDATE, 'DAY') "D" -- 한글로 표시
, TO_CHAR(SYSDATE, 'YYYY-MM-DD') "A" --YYYY-MM-DD, 실무에서 많이 씀
, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI') "A"
, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI') "A"
, TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')"A" --초 추가, 실무에서 많이 씀
FROM DUAL;
📖예제 3
날짜 변환하기
student 테이블의BIRTHDAY 컬럼 이용해서 생일이 1월인 학생의 학번과 이름과 BIRTHDAY를 아래 화면과 같이 출력하시오
SELECT STUDNO
, NAME
,BIRTHDAY
FROM student
WHERE SUBSTR(BIRTHDAY,4,2) =01;
--------------------------------------
SELECT STUDNO
, NAME
,BIRTHDAY
FROM student
WHERE TO_CHAR(BIRTHDAY,'MM')='01';
📖예제 4
EMP 테이블의 HIREDATE 컬럼(입사일)을 사용해서 입사일이 1,2,3월인 사람들의 사번과 이름 , 입사일 출력
-- 내 답변
SELECT EMPNO
, ENAME
,HIREDATE
FROM EMP
WHERE TO_CHAR(HIREDATE,'MM')<04;
-- 강사님의 답
SELECT EMPNO
, ENAME
,HIREDATE
FROM EMP
WHERE TO_CHAR(HIREDATE,'MM')IN('01','02','03');
📗단일 행 함수 - TO.CHAR 함수
| 종류 | 사용 예 | 결과 |
|---|---|---|
| 9 | TO_CHAR(1234,’99999’) | 1234 |
| 0 | TO_CHAR(1234,’099999’) | 001234 |
| $ | TO_CHAR(1234,’$9999’) | $1234 |
| . | TO_CHAR(1234,’9999.99’) | 1234.00 |
| , | TO_CHAR(1234,’99,999’) | 12,345 |
📖예제 1
EMP 테이블 조회하여 이름이 ‘ALLEN’인 사원의 사번, 이름, 월급, 커미션과 연봉 추출
(연봉은(sal*12)+comm 으로 계산하고 천단위 구분 기호 표시)
SELECT EMPNO
, ENAME
, SAL
,COMM
,to_char((sal*12)+comm,'99,999')"SALARY"
FROM EMP
where ENAME ='ALLEN';
📖예제 2
PROFESSOR테이블 조회하여 201학과에 근무하는 교수들의 이름. 급여, 보너스, 연봉을 출력
(연봉: PAY*12+BOUNS)
SELECT NAME
, PAY
,BONUS
,TO_CHAR((PAY*12)+BONUS,'999,999') "SALARY"
FROM PROFESSOR
WHERE DEPTNO = '201';📖예제 3
PROFESSOR테이블 조회하여 학과 번호가 201인 학과에 근무하는 교수들의 이름. 급여, 보너스, 연봉을 출력 (연봉: PAY*12+BOUNS)
SELECT NAME
, PAY
,BONUS
,TO_CHAR((PAY*12)+BONUS,'999,999') "TOTAL"
FROM PROFESSOR
WHERE DEPTNO = '201';
📖예제 4
EMP 테이블 조회하여 COMM 값을 가지고 있는 사람들의 EMPNO, ENAME, HIREDATE, 총연봉, 총연봉 15% 인상 후 연봉을 출력
(단 총연봉은(SAL*12+COMM)으로 계산)
SELECT EMPNO
, ENAME
,HIREDATE
,TO_CHAR((SAL*12)+COMM,'$999,999') "SAL"
,TO_CHAR(((SAL*12)+COMM)*115/100,'$999,999') "15% UP"
FROM EMP
WHERE COMM IS NOT NULL;
📖예제 5
SELECT '5'
, TO_NUMBER('5')
, TO_DATE('2022-02-19')
FROM DUAL;
📗NVL,DECODE 함수
📖NVL
SELECT ENAME
, COMM
,NVL(COMM,0) --NULL값을 0으로 잡겠다.
,NVL(COMM,100) --NULL값을 100으로 잡겠다.
FROM EMP;
📖NVL 예제
PROFESSOR 테이블에서 201학과(DEPTNO)교수들의 교수번호, 이름 , 급여, 보너스, 총연봉 계산
(총연봉 : PAY*12+BOUNS)
보너스 없으면 0으로 계산
SELECT PROFNO
, NAME
, PAY
, NVL(BONUS,0)"BONUS"
, TO_CHAR((PAY*12+NVL(BONUS,0)),'999,999')"TOTAL"
FROM PROFESSOR
WHERE DEPTNO ='201';
📖NVL2
SELECT ENAME
, SAL
, COMM
, NVL2(COMM,SAL+COMM,SAL*0)
-- COMM값이 NULL이면 SAL+COMM.NULL 아니면 SAL*0
FROM EMP;
📖NVL2 예제
아래 화면과 같이 EMP테이블에서DEPTNO가 30번인 사원들을 조회하여
COMM값이 있는경우 ‘Exist’출력, NULL인경우 ‘NULL’ 출력
SELECT EMPNO
, ENAME
, COMM
, NVL2(COMM,'Exist','NULL')
FROM EMP
WHERE DEPTNO ='30';
📖DECODE
SELECT DECODE('A','B','1') -- A가 와 같으면 1 아니면 NULL
, DECODE('A','A','1') -- A가 A와 같으면 1 아니면 NULL
, DECODE('A','B','1','2') -- A가 A와 같으면 1 아니면 2
, DECODE('A','A','1','2') -- A가 A와 같으면 1 아니면 2
, DECODE('A','A','1','C','2','3')
--A가 A면 1 A가 C면 2 그것도 아니면 3 출력
--ELSE IF 느낌으로 이해하기
, DECODE('A','B','1','A','2','3')
, DECODE('A','C','1','B','2','3')
FROM DUAL; 
📖DECODE 예제 1
PROFESSOR 테이블에서 학과번호, 교수명, 학과명 출력하되 DEPTNO가 101인 교수만 학과명을 “Computer Engineering”로 출력
SELECT DEPTNO
, NAME
,DECODE(DEPTNO,'101','Computer Engineering')"DNAME"
FROM PROFESSOR;
📖DECODE 예제 2
PROFESSOR 테이블에서 학과번호, 교수명, 학과명 출력하되 DEPTNO가 101인 교수만 학과명을 “Computer Engineering”로 출력하고 101이 아닌 교수는 학과명에 “ETC”로 출력
SELECT DEPTNO
, NAME
,DECODE(DEPTNO,'101','Computer Engineering','ETC')"DNAME"
FROM PROFESSOR;
📖DECODE 예제 3
PROFESSOR 테이블에서 학과번호, 교수명, 학과명 출력하되 DEPTNO가 101인 교수만 학과명을 “Computer Engineering”로 출력하고 102번이면 “Multimedia Engineering”,103이면 “Software Engineering”, 나머지는 “ETC”출력
SELECT DEPTNO
, NAME
,DECODE(DEPTNO,'101','Computer Engineering'
,'102','Multimedia Engineering'
,'103','Software Engineering','ETC')"DNAME"
FROM PROFESSOR;
