/*
# 함수
- 단일행 함수 : 여러건의 데이터를 한번에 하나씩만 처리하는 함수.
여러행 함수 : 여러건의 데이터를 입력받아서 결과값 한건을 만들어주는 함수.
*/
-- DUAL 테이블
-- 한행으로 결과를 출력하기 위한 테이블.
-- 결과를 한줄로 얻기 위해서 오라클에서 제공하는 테이블.
SELECT 24*12 AS "MULTIFLY"
FROM DUAL;
-- DUAL 테이블은 DUMMY라는 하나의 컬럼으로 구성.
DESC DUAL;
----- -- -----------
-- DUMMY VARCHAR2(1)
-- DUAL 테이블은 DUMMY라는 하나의 컬럼에 X라는 하나의 로우만을 저장하고 있지만 의미없는 값.
SELECT * FROM DUAL;
/*
* 숫자 함수
*/
-- ABS
-- 절대값을 구하는 함수.
SELECT -10, ABS(-10) FROM DUAL;
-- FLOOR
-- 소수점 아래를 버리는 함수.
SELECT 2.4124, FLOOR(2.4124) FROM DUAL;
-- ROUND
-- 반올림하는 함수.
SELECT 1.566, ROUND(1.566,2) FROM DUAL;
-- TRUNC
-- 지정한 자릿수 이하를 버린 결과.
SELECT TRUNC(1.58828), TRUNC(1.58828,4) FROM DUAL;
-- MOD
-- 나머지연산 후 나온 나머지를 구하는 함수.
SELECT MOD(10,3), MOD(10,4) FROM DUAL;
/*
* 문자 함수
*/
-- UPPER
-- 대문자로 변환하는 함수.
SELECT UPPER('a'), UPPER('Hello Oracle'), 'Hello Oracle' FROM DUAL;
-- LOWER
-- 소문자로 변환하는 함수.
SELECT LOWER('A'), LOWER('Hello Oracle'), 'Hello Oracle' FROM DUAL;
-- INITCAP
-- 첫번째 문자만 대문자로 변환하는 함수.
SELECT INITCAP('a'), INITCAP('hello oracle'), 'hello oracle' FROM DUAL;
-- LENGTH
-- 컬럼에 저장된 데이터의 값이 몇개의 문자로 되어 있는지 계산.
SELECT LENGTH('ORACLE'), LENGTH('오라클') FROM DUAL;
-- LENGTHB
-- 문자열의 길이를 BYTE 단위로 처리.
SELECT LENGTHB('ORACLE'), LENGTHB('오라클') FROM DUAL;
-- SUBSTRING
-- 문자열의 시작 위치부터 선택 갯수만큼의 문자를 추출.
-- SUBSTR( 대상, 시작위치, 추출할 갯수)
-- 오라클에서 INDEX는 1부터 시작.
SELECT SUBSTR('ORACLE STRING TEST',8,6) FROM DUAL;
-- 시작 위치 인자값에 음수를 적용할 수 있음. 이때는 문자열의 뒤에서부터 시작 위치가 적용.
SELECT SUBSTR('ORACLE STRING TEST',-4,4) FROM DUAL;
-- emp 테이블에서 입사년도, 월만 출력.
SELECT SUBSTR(HIREDATE,1,2) 년도, SUBSTR(HIREDATE,4,2) 월 FROM EMP;
-- 12월에 입사한 사원을 출력.
select * from emp WHERE (SUBSTR(HIREDATE,4,2)=12);
-- INSTR
-- 특정 문자가 있는 위치를 반환.
-- : INSTR( 대상, 검색글자, 시작위치, 몇번째 검색 )
SELECT INSTR('step by step', 't') FROM DUAL;
SELECT INSTR('step by step', 't',3) FROM DUAL;
SELECT INSTR('step by step', 'e',2,2) FROM DUAL; -- 두번째 나오는 e
SELECT INSTR('데이터베이스', '이',1) FROM DUAL;
-- LPAD & RPAD
-- 대상 문자열을 명시된 자릿수에서 오른쪽&왼쪽에 표시하고, 남은 왼쪽&오른쪽 자리들은 기호로 채움.
-- : LPAD ( 대상, 자릿수, 기호 )
SELECT LPAD('padding', 10,'#') FROM DUAL; -- ###padding
SELECT RPAD('padding', 10,'#') FROM DUAL;
-- LTRIM & RTRIM
-- 문자열의 왼쪽&오른쪽 공백제거.
SELECT LTRIM(' trim test ') FROM DUAL;
SELECT RTRIM(' trim test ') FROM DUAL;
SELECT TRIM(' trim test ') FROM DUAL;
/*
* 날짜 함수
*/
-- SYSDATE
-- 시스템의 현재 날짜를 반환하는 함수.
SELECT SYSDATE FROM DUAL;
-- 날짜 연산
-- 날짜 + 숫자 : 해당 날짜부터 그 기간만큼 지난 날짜를 계산
-- 날짜 - 숫자 : 해당 날짜부터 그 가긴만큼 이전 날짜를 계산
-- 날짜 - 날짜 : 두 날짜 사이의 기간을 계산
SELECT SYSDATE-1 어제, SYSDATE 오늘, SYSDATE+1 내일, SYSDATE+2 모레 FROM DUAL;
-- ROUND 에 포맷 모델 날짜를 사용해서, 날짜를 반올림 할 수 있음.
-- : 포맷 모델 단위
-- DDD 일 기준
-- HH 시간 기준
-- MONTH 월 기준 ( 16일 기준 )
-- emp 테이블의 입사일자를 월 기준으로 반올림.
SELECT ENAME, HIREDATE, ROUND(HIREDATE, 'MONTH') 월 FROM EMP;
-- TRUNC 함수에 포맷 형식을 사용해서, 날짜를 잘라낼 수 있음.
-- emp 테이블의 입사일자의 월을 기준으로 날짜 자르기
SELECT ENAME, HIREDATE, TRUNC(HIREDATE, 'MONTH') FROM EMP;
-- MONTHS_BETWEEN
-- 날짜와 날짜 사이의 개월수를 구함.
-- : MONTHS_BETWEEN( DATE_1, DATE_2 )
-- 직원들의 근무 개월수
SELECT ENAME, SYSDATE, HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) "근무 개월수" FROM EMP;
SELECT ENAME, SYSDATE, HIREDATE, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) "근무 개월수" FROM EMP;
-- ADD_MONTHS
-- 특정 개월수를 더한 날짜를 구함.
-- : ADD_MONTHS( data, number )
-- 입사일에 6개월을 더한 날짜
SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 6) FROM EMP; -- 더한다고 해서 년도는 안 더해짐.
-- NEXT_DAY
-- 날짜를 기준으로 최초로 돌아오는 요일에 해당하는 날짜를 반환.
-- : NEXT_DAY( date, 요일 )
-- 오늘 기준으로 최초로 돌아오는 화요일.
SELECT SYSDATE, NEXT_DAY(SYSDATE, '화요일') "다음주 화요일" FROM DUAL;
-- LAST_DAY
-- 해당 날짜가 속한 달의 마지막 날짜를 반환.
-- emp 테이블의 입사한 달의 마지막날
SELECT HIREDATE, LAST_DAY(HIREDATE) "입사한 달의 마지막 날" FROM EMP;
/*
# 형변환 함수
- 숫자, 문자, 날짜의 데이터 타입을 다른 데이터 타입으로 변환.
- TO_CHAR : 날짜 또는 숫자 타입을 문자형으로 변환
TO_DATE : 문자 타입을 날짜 타입으로 변환
TO_NUMBER : 문자 타입을 숫자 타입으로 변환
*/
/*
# TO_CHAR
- TO_CHAR( 날짜데이터, '출력형식' )
- 출력형식 종류 의미
YYYY 년도(4자리)
YY 년도(2자리)
MM 월을 숫자로 표현
MON 월을 알파벳으로 표현
DAY 요일 표현
*/
-- 현재 날짜를 다른 형태로 출력
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;
-- emp 테이블의 사원 입사일의 요일 출력
SELECT HIREDATE, TO_CHAR(HIREDATE, 'YYYY.MM.DD DAY') "입사일 (YYYY.MM.DD DAY)" FROM EMP;
/*
# 시간 종류 출력 의미
AM or PM 오전(AM), 오후(PM)
HH or HH12 시간(1~12)
HH24 24시간
MI 분
SS 초
*/
-- 현재 날짜와 시간 출력
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD AM HH24:MI:SS') "현재시간" FROM DUAL;
/*
# 숫자 출력 형식
- 구분 의미
0 자릿수를 나타내며, 자릿수가 맞지 않을 경우 0으로 채움.
9 자릿수를 나타내며, 자릿수가 맞지 않을 경우 채우지 않음.
L 통화 기호 앞에 표시.
. 소수점
, 천단위 자리 구분
*/
-- 숫자를 문자 형태로 변환
SELECT TO_CHAR(12300) FROM DUAL;
-- 자리 채우기
SELECT TO_CHAR(123456, '0000000'),TO_CHAR(123456, '999999999') FROM DUAL;
-- 통화기호를 붙이면서, 천단위마다 ',' 출력.
SELECT ENAME,SAL,TO_CHAR(SAL,'L999,999') FROM EMP;
/*
# TO_DATE
- 문자열을 날짜 형식으로 변환.
- : TO_DATE( '문자','format')
*/
-- 숫자를 날짜형으로 변환
SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE=TO_DATE(19801217, 'YYYYMMDD');
-- WHERE HIREDATE == 19800217; -> ERROR!
/*
# TO_NUMBER
- 데이터를 숫자형으로 변환
*/
SELECT TO_NUMBER('20,000','99,999') - TO_NUMBER('12,000','99,999') FROM DUAL;
/* quiz */
-- emp 테이블에서 사원번호가 홀수인 사원을 출력.
SELECT * FROM EMP WHERE (MOD(EMPNO,2)=1);
-- 소문자 manager로 직급 검색해서 출력.
SELECT * FROM EMP WHERE LOWER(JOB) LIKE('%manager%');
-- emp 테이블에서 조회하는 이름을 소문자로 사용해서 사원번호, 이름, 직급, 부서번호를 출력하세요
SELECT EMPNO, ENAME, JOB,DEPTNO FROM EMP WHERE LOWER(ENAME) LIKE('%smith%');
-- dept 테이블에서 첫글자만 대문자로 변환하여 모든 정보를 출력하세요
SELECT DEPTNO, INITCAP(DNAME), INITCAP(LOC) FROM dept;
-- emp 테이블의 ename 컬럼의 마지막 문자 하나만 추출해서 이름이 E 로 끝나는 사원을 출력하세요
SELECT * FROM EMP WHERE SUBSTR(ENAME,-1) = 'E';
-- emp 테이블에서 이름의 세번째 자리가 R 인 사원을 출력하세요
SELECT * FROM EMP WHERE INSTR(ENAME,'R',1,1)=3;
-- emp 테이블에서 20번 부서의 사원번호, 이름, 이름의 글자수, 급여, 급여의 자릿수를 출력하세요
SELECT EMPNO,ENAME,LENGTH(ENAME) "이름의 글자수",SAL,LENGTH(TO_CHAR(SAL)) "급여의 자릿수" FROM EMP WHERE DEPTNO=20;
-- emp 테이블에서 현재까지 근무일수가 몇일 인지를 구하고, 근무일수가 많은 순서로 출력하세요
SELECT EMPNO, ENAME, TRUNC(SYSDATE-HIREDATE) "근무일수" FROM EMP ORDER BY "근무일수" DESC;
/*
# DECODE
- switch case 뭄과 같은 기능.
DECODE ( 표현식, 조건_A, 결과_A
조건_B, 결과_B
...
기본결과
)
*/
SELECT * FROM EMP;
SELECT * FROM DEPT;
--10 ACCOUNTING NEW YORK
--20 RESEARCH DALLAS
--30 SALES CHICAGO
--40 OPERATIONS BOSTON
-- emp 테이블에서 부서번호에 해당되는 부서명을 출력.
SELECT ENAME, DEPTNO, DECODE(DEPTNO, 10, 'ACCOUNTING',
20, 'RESEARCH',
30, 'SALES'
) AS "부서명"
FROM EMP
ORDER BY "부서명";
/*
# CASE
- 여러가지 경우에 대하여 하나를 선택하는 함수.
다양한 비교 연산자를 사용해서 조건을 적용할 수 있음.
IF ~ ELSE if 와 유사.
- CASE 표현식 WHEN 조건_A THEN 결과_A
WHEN 조건_B THEN 결과_B
....
ELSE 결과
END
*/
SELECT ENAME, DEPTNO,
CASE WHEN DEPTNO <= 10 THEN '회계'
WHEN DEPTNO <= 20 THEN '마케팅'
ELSE '영업'
END "부서명"
FROM EMP;
/* quiz */
-- emp 테이블을 사용해서 직급(job)에 따라서 급여를 인상하는 쿼리문을 작성하세요.
--CLERK -> 20%
--SALESMAN -> 15%
--MANAGER -> 10%
--ANALYST -> 5%
SELECT ENAME, JOB, SAL,
CASE JOB WHEN 'CLERK' THEN TRUNC(SAL*1.20)
WHEN 'SALESMAN' THEN TRUNC(SAL*1.15)
WHEN 'MANAGER' THEN TRUNC(SAL*1.10)
WHEN 'ANALYST' THEN TRUNC(SAL*1.05)
ELSE SAL
END "인상급여"
FROM EMP;
-- emp 테이블을 사용해서 급여액에 따라 고액, 보통, 저액을 출력하는 쿼리문을 작성하세요
-- 3000 이상 -> 고액
-- 1000 이상 -> 보통
-- 그외 -> 저액
SELECT ENAME,SAL,
CASE WHEN SAL >=3000 THEN '고액'
WHEN SAL >=1000 THEN '보통'
ELSE '저액'
END "월급정보"
FROM EMP;