1) 문자열함수 -> 지난 수업 때 함
- UPPER(문자열) : 괄호 안 문자 데이터를 모두 대문자로 변환하여 반환
- LOWER(문자열) : 괄호 안 문자 데이터를 모두 소문자로 변환하여 반환
- INITCAP(문자열) : 첫 글자는 대문자, 나머지는 소문자로 변환 후 반환
- LENGTH : 특정 문자열의 길이 구할 때
- SUBSTR : 문자열 일부를 추출
SUBSTR(문자열 데이터, 시작 위치, 추출 길이) : 시작위치부터 추출 길이만큼 추출
SUBSTR(문자열 데이터, 시작 위치) : 시작 위치부터 문자열 데이터 끝까지 추출 - INSTR : 문자열 데이터 안에서 특정 문자 위치 찾기
INSTR(대상 문자열 데이터(필수), 위치를 찾으려는 부분 문자(필수), 위치 찾기를 시작할 대상 문자열 데이터 위치(선택, 기본값은 1), 시작위치에서 찾으려는 문자가 몇 번째인지 지정(선택, 기본값은 1)) - REPLACE : 특정 문자 다른문자로 변경
REPLACE(문자열데이터 또는 열 이름(필수),
찾는문자(필수), 대체할 문자(선택))
-> 대체할 문자 입력하지 않으면 찾는 문자로 지정한 문자는 문자열데이터에서 삭제됨 - LPAD, RPAD : 데이터의 빈공간을 특정 문자로 채우기
LPAD(문자열데이터 또는 열 이름(필수),
데이터의 자릿수(필수), 빈공간에 채울 문자(선택))
RPAD(문자열데이터 또는 열 이름(필수),
데이터의 자릿수(필수), 빈공간에 채울 문자(선택))
:채울문자 지정하지 않으면 빈공간 자릿수만큼 공백문자 - TRIM, LTRIM, RTRIM : 특정 문자 지우기
TRIM(삭제옵션(선택) 삭제할문자(선택) FROM 원본 문자열 데이터(필수))
LTRIM(원본문자열데이터(필수), 삭제할 문자집합(선택))
RTRIM (원본문자열데이터(필수), 삭제할 문자집합(선택))
2) 숫자함수
ABS : 절대값
SQL> select abs(-20) from dual;
ABS(-20)
----------
20ROUND : 지정된 숫자의 특정 위치에서 반올림
ROUND(숫자(필수), 반올림위치(선택))
자연수 둘째자리 : -2
자연수 첫째자리 : -1
소수점 첫째자리 : 0
소수점 둘째자리 : 1
소수점 셋째자리 : -2
반올림위치 지정 않으면 소수점 첫째자리 반올림
SQL> select round(0.222),round(0.8),round(2.5) from dual;
ROUND(0.222) ROUND(0.8) ROUND(2.5)
------------ ---------- ----------
0 1 3
SQL> select round(12345.777,1), round(12345.777,-1) from dual;
ROUND(12345.777,1) ROUND(12345.777,-1)
------------------ -------------------
12345.8 12350
TRUNC : 특정 위치에서 버리기
TRUNC(숫자(필수), 버림위치(선택))
SQL> select trunc(33.88) from dual;
TRUNC(33.88)
------------
33
SQL> select trunc(5623.12777,2), trunc(5623.12777,1), trunc(5623.12777,-1) from dual
2 ;
TRUNC(5623.12777,2) TRUNC(5623.12777,1) TRUNC(5623.12777,-1)
------------------- ------------------- --------------------
5623.12 5623.1 5620CEIL, FLOOR : 지정한 숫자와 가까운 정수 찾기
CEIL(숫자(필수)) : 입력한 숫자에 가장 가까운 큰 정수
FLOOR(숫자(필수)) : 입력한 숫자에 가장 가까운 작은 정수
SQL> select ceil(38.88) from dual;
CEIL(38.88)
-----------
39
SQL> select floor(33.88) from dual;
FLOOR(33.88)
------------
33MOD : 숫자를 나눈 나머지 값을 구하기
MOD(나눗셈 될 숫자(필수), 나눌 숫자(필수))
SQL> select mod(15,6) from dual;
MOD(15,6)
----------
33) 날짜함수
SYSDATE : 데이터베이스 서버가 놓인 OS의 현재날짜와 시간
- 날짜데이터 + 숫자 : 날짜데이터보다 숫자만큼 일수 이후 날짜
- 날짜데이터 - 숫자 : 날짜데이터보다 숫자만큼 일수 이전 날짜
- 날짜데이터 - 날짜데이터 : 두 날짜데이터 간 일수 차이
- 날짜데이터 + 날짜데이터 : 연산불가
SQL> select sysdate, sysdate+5, sysdate-5 from dual;
SYSDATE SYSDATE+ SYSDATE-
-------- -------- --------
24/04/08 24/04/13 24/04/03
ADD_MONTHS : 몇개월 이후 날짜 구하기
ADD_MONTHS(날짜데이터(필),더할개월수(정수)(필))
SQL> select sysdate, add_months(sysdate,-5), add_months(sysdate,5) from dual;
SYSDATE ADD_MONT ADD_MONT
-------- -------- --------
24/04/08 23/11/08 24/09/08
MONTHS_BETWEEN : 두 날짜간 개월수 차이 구하기
month_between(날짜데이터1, 날짜데이터2)
SQL> select months_between(sysdate,'2024/02/19') from dual;
MONTHS_BETWEEN(SYSDATE,'2024/02/19')
------------------------------------
1.65830533
SQL> select months_between(hiredate,sysdate) from emp;
MONTHS_BETWEEN(HIREDATE,SYSDATE)
--------------------------------
-519.72285
-517.62608
-517.56156
-516.20672
-510.36801
-515.23898
-513.98092
-495.98092
-508.72285
-511
-494.88414
MONTHS_BETWEEN(HIREDATE,SYSDATE)
--------------------------------
-508.17446
-508.17446
-506.5293
NEXT_DAY, LAST_DAY : 돌아오는 요일, 달의 마지막 날짜 구하기
NEXT_DAY(날짜데이터,요일문자)
LAST_DAY(날짜데이터)
SQL> select last_day(sysdate) from dual;
LAST_DAY
--------
24/04/30
SQL> select next_day(sysdate,'금요일') from dual;
NEXT_DAY
--------
24/04/12
미션
1. 오늘이 속한 달의 마지막 날짜와 다가오는 금요일의 날짜를 출력
SQL> select next_day(sysdate,'금요일') from dual;
NEXT_DAY
--------
24/04/12
SQL> select last_day(sysdate) from dual;
LAST_DAY
--------
24/04/302. 각 직원들이 입사한후 6개월이 지나면 근무평가를 하기로 한다. 각 직원들에 대한 이름,입사일,최초의 근무평가일은 언제인지 구하고 근무평가일로부터 돌아오는 금요일의 날짜가 언제인지도 구하세요.
SQL> select ename, hiredate,
add_months(hiredate,6) as 최초근평,
next_day(add_months(hiredate,6),'금요일') as 금요일
from emp;
ENAME HIREDATE 최초근평 금요일
-------------------- -------- -------- --------
SMITH 80/12/17 81/06/17 81/06/19
ALLEN 81/02/20 81/08/20 81/08/21
WARD 81/02/22 81/08/22 81/08/28
JONES 81/04/02 81/10/02 81/10/09
MARTIN 81/09/28 82/03/28 82/04/02
BLAKE 81/05/01 81/11/01 81/11/06
CLARK 81/06/09 81/12/09 81/12/11
SCOTT 82/12/09 83/06/09 83/06/10
KING 81/11/17 82/05/17 82/05/21
TURNER 81/09/08 82/03/08 82/03/12
ADAMS 83/01/12 83/07/12 83/07/15
ENAME HIREDATE 최초근평 금요일
-------------------- -------- -------- --------
JAMES 81/12/03 82/06/03 82/06/04
FORD 81/12/03 82/06/03 82/06/04
MILLER 82/01/23 82/07/23 82/07/30
14 rows selected.3. 모든 사원에 대해 근무한 일수가 얼마인지 구하고 근무한 지 몇주가 지났는지 출력 또한 근무주수가 많은 직원부터 나타내고,같은 근무주수가 있으면 이름에 대해서 오름차순으로 정렬하세요
(week명으로 필드명)
SQL> select ename, ceil(sysdate-hiredate) as 근무일수,
2 trunc((sysdate-hiredate)/7) week
3 from emp
4 order by week desc, ename asc;
ENAME 근무일수 WEEK
-------------------- ---------- ----------
SMITH 15819 2259
ALLEN 15754 2250
WARD 15752 2250
JONES 15713 2244
BLAKE 15684 2240
CLARK 15645 2234
TURNER 15554 2221
MARTIN 15534 2219
KING 15484 2211
FORD 15468 2209
JAMES 15468 2209
ENAME 근무일수 WEEK
-------------------- ---------- ----------
MILLER 15417 2202
SCOTT 15097 2156
ADAMS 15063 2151
14 rows selected.- 현재 날짜 5일 뒤의 날짜와 5일 전의 날짜 출력
SQL> select sysdate+5,sysdate-5 from dual;
SYSDATE+ SYSDATE-
-------- --------
24/04/13 24/04/03- 근무한 지 280000 시간이 넘은 사원을 출력하시오.
SQL> select ename,(sysdate-hiredate)*24
2 from emp
3 where (sysdate-hiredate)*24>28000;
ENAME (SYSDATE-HIREDATE)*24
-------------------- ---------------------
SMITH 379642.795
ALLEN 378082.795
WARD 378034.795
JONES 377098.795
MARTIN 372802.795
BLAKE 376402.795
CLARK 375466.795
SCOTT 362314.795
KING 371602.795
TURNER 373282.795
ADAMS 361498.795
ENAME (SYSDATE-HIREDATE)*24
-------------------- ---------------------
JAMES 371218.795
FORD 371218.795
MILLER 369994.795
14 rows selected.- emp테이블에서 10번 부서에 근무하는 직원들의 현재까지 근무월수를 조회하기
(사원명, 오늘날짜, 입사일, 근무개월수 출력하기)
SQL> select ename,sysdate, hiredate,months_between(sysdate,hiredate)
2 from emp
3 where deptno = 10;
ENAME SYSDATE HIREDATE MONTHS_BETWEEN(SYSDATE,HIREDATE)
-------------------- -------- -------- --------------------------------
CLARK 24/04/08 81/06/09 513.98159
KING 24/04/08 81/11/17 508.723525
MILLER 24/04/08 82/01/23 506.529977
4) 형변환함수, 157p
TO_CHAR : 날짜, 숫자데이터를 문자데이터로 변환
TO_CHAR(날짜데이터,출력되길 원하는 문자형태)
SQL> select hiredate,to_char(hiredate,'YYYY')
2 from emp;
HIREDATE TO_CHAR(
-------- --------
80/12/17 1980
81/02/20 1981
81/02/22 1981
81/04/02 1981
81/09/28 1981
81/05/01 1981
81/06/09 1981
82/12/09 1982
81/11/17 1981
81/09/08 1981
83/01/12 1983
HIREDATE TO_CHAR(
-------- --------
81/12/03 1981
81/12/03 1981
82/01/23 1982
14 rows selected.TO_CHAR(날짜데이터,'출력되길 원하는 문자형태','NSL_DATE_LANGUAGE = language'(선택))
SQL> select sysdate,
to_char(sysdate,'MON', 'NLS_date_language=ENGLISH')
from dual;
SYSDATE TO_CHAR(SYSDATE,'MON','N
-------- ------------------------
24/04/08 APRSQL> select sysdate,
to_char(sysdate,'MON', 'NLS_date_language=ENGLISH'),
to_char(sysdate,'MONTH', 'NLS_date_language=korean')
from dual;
SYSDATE TO_CHAR(SYSDATE,'MON','N TO_CHAR(SYSDATE,
-------- ------------------------ ----------------
24/04/08 APR 4월
여러 숫자형식 사용하여 급여출력 162p
- L이라는 기호가 지역화폐단위 출력되게 하는 것
컴퓨터 로케일이 한국으로 되어있어서 원화로 나옴
SQL> select ename, to_char(sal,'L999,999')
from emp;
ENAME TO_CHAR(SAL,'L999,999')
-------------------- ------------------------------------
SMITH ￦800
ALLEN ￦1,600
WARD ￦1,250
JONES ￦2,975
MARTIN ￦1,250
BLAKE ￦2,850
CLARK ￦2,450
SCOTT ￦3,000
KING ￦5,000
TURNER ￦1,500
ADAMS ￦1,100
ENAME TO_CHAR(SAL,'L999,999')
-------------------- ------------------------------------
JAMES ￦950
FORD ￦3,000
MILLER ￦1,300
14 rows selected.SQL> select ename, to_char(sal,'$999,999.99') from emp;
ENAME TO_CHAR(SAL,'$999,999.99
-------------------- ------------------------
SMITH $800.00
ALLEN $1,600.00
WARD $1,250.00
JONES $2,975.00
MARTIN $1,250.00
BLAKE $2,850.00
CLARK $2,450.00
SCOTT $3,000.00
KING $5,000.00
TURNER $1,500.00
ADAMS $1,100.00
ENAME TO_CHAR(SAL,'$999,999.99
-------------------- ------------------------
JAMES $950.00
FORD $3,000.00
MILLER $1,300.00
14 rows selected.
TO_NUMBER : 문자데이터를 숫자데이터로 변환
TO_NUMBER(문자열데이터,인식될숫자형태)
SQL> select to_number('1,300','999,999')
from dual;
TO_NUMBER('1,300','999,999')
----------------------------
1300SQL> select to_number('1,300','999,999')+200
from dual;
TO_NUMBER('1,300','999,999')+200
--------------------------------
1500TO_DATE : 문자데이터를 날짜데이터로 변환
TO_DATE(문자열데이터,인식될 날짜형태)
SQL> select to_date('2024/04/08','YYYY/MM/DD')
from dual;
TO_DATE(
--------
24/04/08
null처리함수,167p
nvl
nvl(null인지 여부를 검사할 데이터 또는 열, 앞의 데이터가 null일경우 반환할 데이터)
- nvl은 커미션이 숫자임, 그리고 직접 출력됨, 이 둘이 형식이 다르면안됨
SQL> select ename,sal,nvl(to_char(comm),'미지급')
2 from emp;
ENAME SAL NVL(TO_CHAR(COMM),'미지급')
-------------------- ---------- --------------------------------------------------------------------------------
SMITH 800 미지급
ALLEN 1600 300
WARD 1250 500
JONES 2975 미지급
MARTIN 1250 1400
BLAKE 2850 미지급
CLARK 2450 미지급
SCOTT 3000 미지급
KING 5000 미지급
TURNER 1500 0
ADAMS 1100 미지급
ENAME SAL NVL(TO_CHAR(COMM),'미지급')
-------------------- ---------- --------------------------------------------------------------------------------
JAMES 950 미지급
FORD 3000 미지급
MILLER 1300 미지급
14 rows selected.nvl2
nvl2(null인지 여부를 검사할 데이터 또는 열, 앞의 데이터가 null이 아닐경우 반환할 데이터 또는 계산식, 앞의 데이터가 null일경우 반환할 데이터 또는 계산식)
- nvl2는 커미션이 직접출력되지않고 지급이나 미지급이 출력돼서 문제없음
둘다 문자열로 타입동일
SQL> select ename, sal, comm,
nvl2(comm,'지급','미지급')
from emp;
ENAME SAL COMM NVL2(COMM,'지급','
-------------------- ---------- ---------- ------------------
SMITH 800 미지급
ALLEN 1600 300 지급
WARD 1250 500 지급
JONES 2975 미지급
MARTIN 1250 1400 지급
BLAKE 2850 미지급
CLARK 2450 미지급
SCOTT 3000 미지급
KING 5000 미지급
TURNER 1500 0 지급
ADAMS 1100 미지급
ENAME SAL COMM NVL2(COMM,'지급','
-------------------- ---------- ---------- ------------------
JAMES 950 미지급
FORD 3000 미지급
MILLER 1300 미지급
14 rows selected.
nullif
nullif(값1,값2)
값1과 2가 같으면 null로 처리하고 같지않으면 값1을리턴
특정컬럼의 값을 널로 변경할때 사용하는 함수
SQL> select ename,job,nullif(job,'SALESMAN')
from emp;
ENAME JOB NULLIF(JOB,'SALESM
-------------------- ------------------ ------------------
SMITH CLERK CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER MANAGER
MARTIN SALESMAN
BLAKE MANAGER MANAGER
CLARK MANAGER MANAGER
SCOTT ANALYST ANALYST
KING PRESIDENT PRESIDENT
TURNER SALESMAN
ADAMS CLERK CLERK
ENAME JOB NULLIF(JOB,'SALESM
-------------------- ------------------ ------------------
JAMES CLERK CLERK
FORD ANALYST ANALYST
MILLER CLERK CLERK
14 rows selected.
미션
emp테이블에서 성명,sal,comm과 연봉을 계산해서 출력하세요.단, null인 경우는 comm이 0이 더해지도록 처리하세요
SQL> select ename,sal,comm,sal*12+nvl(comm,0) 연봉
2 from emp;
ENAME SAL COMM 연봉
-------------------- ---------- ---------- ----------
SMITH 800 9600
ALLEN 1600 300 19500
WARD 1250 500 15500
JONES 2975 35700
MARTIN 1250 1400 16400
BLAKE 2850 34200
CLARK 2450 29400
SCOTT 3000 36000
KING 5000 60000
TURNER 1500 0 18000
ADAMS 1100 13200
ENAME SAL COMM 연봉
-------------------- ---------- ---------- ----------
JAMES 950 11400
FORD 3000 36000
MILLER 1300 15600
14 rows selected.
cmd에서 오라클접속
decode함수&case문
decode(검사 대상 열 또는 데이터/연산이나 함수의 결과, 조건1, 데이터가 조건1과 일치할때 반환할 결과,조건2, 데이터가 조건2과 일치할때 반환할 결과,... )
SQL> select ename,job,deptno,decode(deptno,
2 10,'전살실',
3 20, '영업부',
4 30, '인사팀',
5 '미배정')
6 from emp;
ENAME JOB DEPTNO DECODE(DEPTNO,10,'
-------------------- ------------------ ---------- ------------------
SMITH CLERK 20 영업부
ALLEN SALESMAN 30 인사팀
WARD SALESMAN 30 인사팀
JONES MANAGER 20 영업부
MARTIN SALESMAN 30 인사팀
BLAKE MANAGER 30 인사팀
CLARK MANAGER 10 전살실
SCOTT ANALYST 20 영업부
KING PRESIDENT 10 전살실
TURNER SALESMAN 30 인사팀
ADAMS CLERK 20 영업부
ENAME JOB DEPTNO DECODE(DEPTNO,10,'
-------------------- ------------------ ---------- ------------------
JAMES CLERK 30 인사팀
FORD ANALYST 20 영업부
MILLER CLERK 10 전살실
14 rows selected.
미션
job이 CLERK이면 comm은 급여(sal)의 10%, SALESMAN은 급여의 7%, MANAGER는 급여의 15%, PRESIDENT는 20%,ANALYST는 22%의 특별보너스를 지급하려한다. 총 연봉을 계산해보세요
연봉 = sal*12+comm+특별보너스
출력해야하는 컬럼 = ename,sal,comm,특별보너스,연봉
SQL> select ename,sal,comm,decode(job,
2 'CLERK' ,sal*0.10,
3 'SALESMAN', sal*0.07,
4 'MANAGER',sal*0.15,
5 'PRESIDENT',sal*0.20,
6 'ANALYST',sal*0.22,
7 0) as 특별보너스,
8 sal*12 + nvl(comm,0) +decode(job,
9 'CLERK' ,sal*0.10,
10 'SALESMAN', sal*0.07,
11 'MANAGER',sal*0.15,
12 'PRESIDENT',sal*0.20,
13 'ANALYST',sal*0.22,
14 0) as 연봉
15 from emp;
ENAME SAL COMM 특별보너스 연봉
-------------------- ---------- ---------- ---------- ----------
SMITH 800 80 9680
ALLEN 1600 300 112 19612
WARD 1250 500 87.5 15587.5
JONES 2975 446.25 36146.25
MARTIN 1250 1400 87.5 16487.5
BLAKE 2850 427.5 34627.5
CLARK 2450 367.5 29767.5
SCOTT 3000 660 36660
KING 5000 1000 61000
TURNER 1500 0 105 18105
ADAMS 1100 110 13310
ENAME SAL COMM 특별보너스 연봉
-------------------- ---------- ---------- ---------- ----------
JAMES 950 95 11495
FORD 3000 660 36660
MILLER 1300 130 15730
14 rows selected.
case문
171페이지
decode보다 유연하게 사용가능
SQL> select ename, deptno,case
2 when deptno=10 then '전산실'
3 when deptno=20 then '영업팀'
4 when deptno=30 then '인사팀'
5 end as 부서명
6 from emp;
ENAME DEPTNO 부서명
-------------------- ---------- ------------------
SMITH 20 영업팀
ALLEN 30 인사팀
WARD 30 인사팀
JONES 20 영업팀
MARTIN 30 인사팀
BLAKE 30 인사팀
CLARK 10 전산실
SCOTT 20 영업팀
KING 10 전산실
TURNER 30 인사팀
ADAMS 20 영업팀
ENAME DEPTNO 부서명
-------------------- ---------- ------------------
JAMES 30 인사팀
FORD 20 영업팀
MILLER 10 전산실
14 rows selected.
SQL> select ename, sal,case
2 when sal>=3000 then '고'
3 when sal>=2000 then '중'
4 end as 테스트
5 from emp;
ENAME SAL 테스트
-------------------- ---------- ------
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975 중
MARTIN 1250
BLAKE 2850 중
CLARK 2450 중
SCOTT 3000 고
KING 5000 고
TURNER 1500
ADAMS 1100
ENAME SAL 테스트
-------------------- ---------- ------
JAMES 950
FORD 3000 고
MILLER 1300
14 rows selected.
174-175p mission
본 포스팅은 멀티캠퍼스의 멀티잇 백엔드 개발(Java)의 교육을 수강하고 작성되었습니다.
