1018(23일차) - sql2일
다른 환경에서 설치,세팅 시 주의할 점.
1.utf8 인코딩 설정(가장 먼저 할 것)
2.sh 계정 생성시 아이디와 비밀번호에 공백주의,특히 비밀번호 대소문자 확인
(어떤것으로 했는지 기억하지 못한다면 계정 삭제 후 재생성)
3.sh계정 생성 후 , table 내용이 들어있는 SH_sql파일 스크립트 실행 할 것
그렇지 않으면 테이블이 존재하지 않는다고 오류 발생.
작업 순서 준수 할 것.
Function (함수)
BUILT-IN FUCTION
- ORACLE 안에 포함 된 함수
- 일련의 처리를 묶어서 작성해두고 호출하여 사용 하는 것
- 반드시 리턴 값이 존재함
| 단일 행 함수 | 그룹 함수 |
| 행 별로 처리되는 함수 | 행을 그룹핑하고 그룹별로 실행되는 함수 |
| 문자처리함수 숫자처리함수 날짜처리함수 형변환함수 등.. |
아직 안 배움 |
1)문자 처리 함수
1-1
length() , lengthb()
길이값을 반환하는 함수
| length(값) : number | lengthb(값) : number |
| 길이 값을 반환 length(emp_name) |
바이트 수를 반환 lengthb(emp_name) |
1-2
instr(src, search[,start_position, occurrence]) : index
찾을 문자열에서 검색할 문자열의 인덱스를 반환. []안은 생략 가능
검색 시작위치, 출현횟수 등을 지정가능.
1-3
substr(src,strt_index[,length]):string
찾을 문자열에서 start_index 부터 length갯수의 문자열을 잘라 반환.
1-4
lpad/rpad (left, right) 여백을 주기
lpad(value, length, padding str):string
rpad(value, length, padding str):string
length에 value를 작성하고 남은 공간에 padding을 왼쪽/오른쪽에 채우기
padding str을 쓰지않는다면 기본적으로 공백으로 채움.
1-5
dbms_random 난수생성.
floor(dbms_random.value()*10000)"랜덤",
java와 비슷한 방식으로 난수 생성이 가능하다.
1-6
정규표현식 REGEXP 함수
- 문자 검색에 특화된 표현식
regexp_replace(src, regexp, new_str) |
src문자열에서 regexp와 일치하는 문자열을 new_str로 변환한 문자열을 반환한다. |
숫자만 제거:[[:digit:]]
숫자가 아닌 것을 제거 : [^[:digit:]]
regexp_like(src,regexp):boolean |
src문자열에서 regexp 패턴이 있다면 true 없으면 false 반환 직접 t/f 값을 확인할 수 없고 where 조건절로 사용함 |
위에서는 ^가 not으로 쓰였지만
regexp_like(emp_name,'^(김|이)'); --이때는 ^가 시작을 의미
2)숫자 처리 함수
- mod (sql은 %연산이 없음)
- ceil(n) / floor(n) , trunc(n) / round(n)
3)날짜 처리 함수
3-1
add_months(date,number) : date
한달 후 , 세달 후 등 날짜 조회
말일 관련 처리 주의
=> 전 달이 다음 달보다 말일이 더 많은 경우
아래 예제처럼 1월 31일의 한 달 뒤를 체크하는데 2월 28일이 나올 수있다.
2월 27일의 한달 뒤를 조회하면 3월 27일이 나옴.
3-2
months_between(future_date, past_date):number
두 날짜 사이의 개월 수를 반환한다.
내년 1월1일과 오늘날짜가 몇개월 남았나 확인가능.
trunc 함수로 소수점 첫째자리까지만 출력하도록 했다.
3-3
extract(년월일시분초 from date/timestamp):number
4)형 변환 함수
문자 - 숫자 - 날짜 사이의 형 변환 지원
숫자에서 char 로 변환할때
to_char(number,format) : char
-숫자에 형식을 부여함.
9:해당자리에 숫자를 의미. 값이 없는 경우 소수점 이상은 공백/소수점 이하는 0으로 처리
0:해당자리에 숫자를 의미. 값이 없는 경우, 소수점이상과 이하 모두 0으로 처리
fm:포맷으로 생긴 앞쪽의 공백 제거
l 혹은 L: \표시
to_char(123456789,'999,999,999'),
to_char(123456789,'9,999'), --###### 이렇게 나옴
to_char(123.456,'FM9,999.99999'), --123.45600 / fm,FM 포맷으로 생긴 앞쪽 공백 제거
to_char(123.456,'0,000.00000'), --0,123.45600
to_char(123456,'FML9,999,999') --왼쪽정렬+\표시
날짜에서 char 로 변환할때
to_char(date,format) : char
hh24 =>24시간
mi=>minute 분 표시
dy=>수 day=>수요일 d=>4 (일요일1~)
select
sysdate,
to_char(sysdate,'yy-mm-dd hh24:mi'), --hh24:24시간
to_char(sysdate,'yyyy"년" mm"월" dd"일"(dy) dy day d') --dy:수 day:수요일 d:4
from
dual;
char에서 숫자 로 변환할때
to_number(char,format) : number
char에서 날짜 로 변환할때
to_date(char,format) : date
select
to_date('19900909', 'yyyymmdd'),
to_date('1990년02월06일(화)', 'yyyy"년"mm"월"dd"일"(dy)'),
/*
yyyy ,rrrr 둘다 네자리 년도 읽을 때엔 동일 but 2자리 년도일땐 차이가 있음
*/
to_date('990909','yymmdd'), --yy:현재 세기 기준 (2000~2099) -2099
to_date('990909','rrmmdd') --rr:이전세기 50년부터 현재세기 49사이(1950~2049) -1999
from
dual;
5)기타 함수
- null 처리
- nvl(value,null일 때 값)
- nvl2(value,null이 아닐 때 값,null일 때 값)
그 외에!
선택함수
- decode
- case1 (decode와 동일)
- case2(if else if와 비슷)
decode
decode(expression,result1,value1,result2,value2,.... , [,default value]):value
decode(substr(emp_no,8,1),'1','남','3','남','2','여','4','여') gender
이런식으로 쓰인다 특정값이 1이면 남자, 3이면 남자, 2면 여자, 4면 여자...
여러 개 사용 가능함. 마지막 값은 기본값인데 생략이 가능함.
case1(decode 와 동일)
case substr(emp_no,8,1)
when '1' then '남'
when '2' then '여'
when '3' then '남'
when '4' then '여'
end gender
주민번호 뒷자리가 1로 시작하면 남자, 2면 여자를 case1 방식으로 작성
case2(if else if 와 동일)
case
when substr(emp_no,8,1) in('1','3') then '남'
else '여'
end gender
동일한 방식인데 1이나 3이면 남자, else로 여자라고 체크한다.
else인 경우는 1,3이 아닌 즉, 2,4일때!
금일 수업 코드(어제 생성한 oracle_sh.sql에 이어서 진행하였다.
--23.10.18
--============================================
-- BUILT-IN FUCTION
--============================================
--oracle 안에 포함된 함수
--일련의 처리를 묶어서 작성해두고 호출하여 사용하는 것.
--반드시 리턴값이 존재함
/*
구분
1.단일 행 함수 - 행 별로 처리되는 함수
문자 처리 함수, 숫자 처리 함수, 날짜 처리 함수,형 변환 함수, 기타....
2.그룹 함수 - 행을 그룹핑하고 그룹별로 실행되는 함수
*/
-----------------------------------------------------------
--문자 처리 함수
-----------------------------------------------------------
--length(value):number 길이 값을 반환
--lengthb(value):number 바이트 수를 반환
select
emp_name,
length(emp_name) "길이",
lengthb(emp_name) "바이트 수",
email,
length(email)"길이",
lengthb(email)"바이트 수"
from
employee;
--instr(src, search[,start_position , occurrence]):index
--src문자열에서 search 문자열의 인덱스를 반환 []는 생략가능
--검색 시작위치,출현횟수등을 지정가능.
select
instr('kh정보교육원 국가정보원 정보문화사', '정보')예제1,--index는 1부터 시작
instr('kh정보교육원 국가정보원 정보문화사', '정보',1,2)예제2,
instr('kh정보교육원 국가정보원 정보문화사', '정보',-1)예제3 --음수인 경우 뒤에서부터 검색..
from
dual;
--substr(src, start_index[,length]):string
--src문자열에서 start_index부터 length갯수의 문자열을 잘라 반환
select
substr('show me the moeny',6,2), --me
substr('show me the moeny',6), --me the money
substr('show me the moeny',-5) --money
from
dual;
--ex)사원테이블에서 이메일 앞의 아이디만 조회
select
substr(email,1,instr(email, '@')-1) "메일 아이디"
from
employee;
--ex)사원 테이블에서 사원들의 성만 가나다순으로 조회 중복x 모든 성은 한글자임
select distinct
substr(emp_name,1,1) "성"
from
employee
order by
"성";
--lpad/rpad (left, right) 여백을 주기
--lpad(value, length, padding str):string
--rpad(value, length, padding str):string
--length에 value를 작성하고 남은 공간에 padding을 왼쪽/오른쪽에 채우기
select
lpad(email,20), --안쓰면 공백으로 채움(defalut)
rpad(email,20,'%')
from
employee;
--dbms_random 난수생성
select
lpad(123,5,0),
lpad(1,5,0),
'kh-'||to_char(sysdate,'yymmdd')||'-'||lpad(123,5,0)"주문번호", --주문번호처럼 만들어보기
floor(dbms_random.value()*10000)"랜덤",
'kh-'||to_char(sysdate,'yymmdd')||'-'||lpad(floor(dbms_random.value()*10000),5,0)"랜덤 주문번호"
from
dual;
-- 정규표현식 REGEXP 함수
-- 문자 검색에 특화된 표현식
-- regexp_replace(src,regexp,new_str)
-- src문자열에서 regexp와 일치하는 문자열을 new_str로 변환한 문자열 반환
select
'2023/12/25 22:30:55',
regexp_replace('2023/12/25 22:30:55', ' |/|:',''), --가운데 해당하는 공백,슬래시,:를 ''로 변경
'242352345435hellowolrd34523523531hahaha',
regexp_replace('242352345435hellowolrd34523523531hahaha', '[[:digit:]]',''), --숫자만 제거
regexp_replace('242352345435hellowolrd34523523531hahaha', '[^[:digit:]]','') --숫자가 아닌것을 제거 ^:부정
from
dual;
--regexp_like(src,regexp):boolean t/f를 확인하는데 직접 확인할 순 없고 where절에서사용
--src문자열에서 regexp패턴이 있으면 true,없으면 false
select
emp_name
from
employee
where
-- emp_name like '김%' or emp_name like '이%',
-- substr(emp_name,1,1) in ('김','이');
regexp_like(emp_name,'^(김|이)'); --이때는 ^가 시작을 의미
--남자사원만 조회
select
emp_name,
substr(emp_no,8,1)
from
employee
where
regexp_like(substr(emp_no,8,1),'^(1|3)'); --혹은 in('1','3')
select * from employee;
-------------------------------------------------------
--숫자 처리 함수
-------------------------------------------------------
--mod(m,n):remainder
--sql은 %연산 지원안함.
select
mod(10,3) "나머지"
from
dual;
--ex)생일이 짝수인 사원만 조회..
select
*
from
employee
where
mod(substr(emp_no,1,6),2)=0;
--질의 결과에서 문자열은 왼쪽 정렬/숫자는 오른쪽 정렬
--ceil(n)올림
--floor(n)버림 | trunc(n,소수점이하자리수)
--round(n,소수점이하자리수)반올림
select
ceil(123.456), --124
ceil(123.456 *100 )/100"test", --123.46
floor(123.456), --123
round(123.456), --123
round(123.567), --124
round(123.456,2),--123.46 반올림됨
trunc(123.456,2) --123.45
from
dual;
-----------------------------------------------------
--날짜 처리 함수
-----------------------------------------------------
-- add_months(date, number) : date
-- 1달 후 날짜, 3달 후 날짜 조회
-- 말일 관련 처리 주의
select
add_months(sysdate,1), --1달 뒤 / -1이면 한달 전
add_months(to_date('20230131','yyyymmdd'),1), --1월 31일의 한달 뒤는 2월 31일? 하지만 28일까지나옴
add_months(to_date('20230228','yyyymmdd'),1) --3월 31일
from
dual;
--입사 후 3개월 뒤 정식사원으로 계약하게 된다.
--사원들이 정식사원이 된 날을 조회 (이름,날짜)
select
emp_name,
add_months(hire_date,3)
from
employee;
--months_between(future_date, past_date):number
--두 날짜 사이의 개월수 반환
select
months_between('20240101',sysdate),
trunc(months_between('20240101',sysdate),1) --2.4개월
from
dual;
--근무 개월수 조회
--이름 n개월 m년 k개월
select
emp_name,
hire_date,
-- abs(trunc(months_between(hire_date,sysdate),1)) ||'개월',
-- floor(abs((trunc(months_between(hire_date,sysdate),1))/12)) ||'년' "년",
--floor(mod(abs(trunc(months_between(hire_date,sysdate),1)),12)) ||'개월' "개월"
trunc(months_between(nvl(quit_date, sysdate),hire_date)),
floor(trunc(months_between(nvl(quit_date, sysdate),hire_date))/12) 년,
trunc(mod(months_between(nvl(quit_date, sysdate),hire_date),12)) 개월
from employee;
--extract(년월일시분초 from date/timestamp): number
select
extract(year from sysdate) year,
extract(month from sysdate) month,
extract(day from sysdate) day,
extract(hour from cast(sysdate as timestamp)) hour,
extract(minute from cast(sysdate as timestamp)) minute,
extract(second from cast(sysdate as timestamp)) second
from
dual;
--ex)2001년 입사자의 정보 조회
select
*
from
employee
where
extract(year from hire_date) = 2001;
--trunc(date): date
--시분초정보 제거
select
sysdate,
trunc(sysdate) --시분초 날라감
from
dual;
-----------------------------------------------
--형변환 함수
-----------------------------------------------
-- 문자/숫자/날짜 사이의 형변환 지원
/*
to_char to_date
---------------> ----------------->
number char date
<---------------- <-------------------
to_number to_char
*/
--to_char (number, format):char
--숫자에 형식부여
--9 : 해당자리에 숫자를 의미.값이 없는 경우, 소수점 이상은 공백, 소수점 이하는 0으로 처리
--0 : 해당자리에 숫자를 의미.값이 없는 경우, 소수점 이상,이하 모두 0으로 처리
--format의 자릿수는 항상 실제 값보다 커야함.
select
to_char(123456789,'999,999,999'),
to_char(123456789,'9,999'), --###### 이렇게 나옴
to_char(123.456,'FM9,999.99999'), --123.45600 / fm,FM 포맷으로 생긴 앞쪽 공백 제거
to_char(123.456,'0,000.00000'), --0,123.45600
to_char(123456,'FML9,999,999') --왼쪽정렬+\표시
from
dual;
--to_char(date,format):char
--형식문자는 대소문자 구분 안함
select
sysdate,
to_char(sysdate,'yy-mm-dd hh24:mi'), --hh24:24시간
to_char(sysdate,'yyyy"년" mm"월" dd"일"(dy) dy day d') --dy:수 day:수요일 d:4
from
dual;
select * from employee;
--사원테이블에서 사원명 급여,보너스율, 입사일 조회
--급여:세자리콤마
--보너스율:%로 표현(null인 경우 0%)
--입사일:년월일(요일)
select
emp_name "사원명",
to_char(salary,'999,999,999') "급여",
nvl(bonus,0)*100 ||'%', --to_char(nvl(bonus,0),'0.0')||'%',
to_char(hire_date,'yyyy"년" mm"월" dd"일"(dy)')
from
employee;
--to_number(char ,format):number
select
to_number('1,234,567','9,999,999'), --바로 더하기연산해도 작동 잘됨
to_number('1234567','FML9,999,999')
from
dual;
--to_date(char,format) : date
select
to_date('19900909', 'yyyymmdd'),
to_date('1990년02월06일(화)', 'yyyy"년"mm"월"dd"일"(dy)'),
/*
yyyy ,rrrr 둘다 네자리 년도 읽을 때엔 동일
*/
to_date('990909','yymmdd'), --yy:현재 세기 기준 (2000~2099) -2099
to_date('990909','rrmmdd') --rr:이전세기 50년부터 현재세기 49사이(1950~2049) -1999
from
dual;
---------------------------------------------------
--기타 함수
---------------------------------------------------
--null처리
--nvl(value, null일때값)
--nvl2(value, null이 아닐때값,null일때값)
select
emp_name,
bonus,
nvl2(bonus, 'o','x')
from
employee;
--선택함수
--decode(expression,result1,value1,result2,value2,.... , [,default value]):value
--성별 조회
select
emp_name,
emp_no,
decode(substr(emp_no,8,1),'1','남','3','남','2','여','4','여') gender,
decode(substr(emp_no,8,1),'1','남','3','남','여') gender,
decode(substr(emp_no,8,1),'2','여','4','여','남') gender
from
employee;
--ex)직급명 조회
--job table
select
emp_name,
job_code,
decode(job_code,
'J1', '대표',
'J2', '부사장',
'J3', '부장',
'J4', '차장',
'J5', '과장',
'J6', '대리',
'사원') job_name
from
employee;
--case1 (decode와 동일)
/*
case expression
when result1 then value1
when result2 then value2
when result3 then value3
.........
else default_value
end
*/
select
emp_name,
emp_no,
case substr(emp_no,8,1)
when '1' then '남'
when '2' then '여'
when '3' then '남'
when '4' then '여'
end gender,
case substr(emp_no,8,1)
when '2' then '여'
when '4' then '여'
else '남'
end gender
from
employee;
--case2 (if else if와 비슷)
/*
case
when condition1 then value1 --condition:boolean
when condition2 then value2
..
[else default_value]
end
*/
select
emp_name,
emp_no,
/* case
when substr(emp_no,8,1)='1' then '남'
when substr(emp_no,8,1)='2' then '여'
when substr(emp_no,8,1)='3' then '남'
when substr(emp_no,8,1)='4' then '여'
end gender,
*/
case
when substr(emp_no,8,1) in('1','3') then '남'
else '여'
end gender,
case
when substr(emp_no,8,1) in('2','4') then '여'
else '남'
end gender
from
employee;
--직급 출력 J1=대표, J2/J3 = 임원, 나머지는 평사원
select
emp_name,
job_code,
case
when job_code = 'J1' then '대표'
when job_code in ('J2','J3' )then '임직원'
else '평사원'
end jobname
from
employee;
--나이 계산
select
emp_name,
emp_no,
to_date(substr(emp_no,1,2),'yy'),
to_date(substr(emp_no,1,2),'rr'),
decode(substr(emp_no,8,1),'1',1900,'2',1900,2000)+substr(emp_no,1,2) birthyear,
extract(year from sysdate)- (decode(substr(emp_no,8,1),'1',1900,'2',1900,2000)+substr(emp_no,1,2)) age
만나이 -- (현재날짜 - 생일)/365 , months_between(현재날짜, 생일)/12
from
employee;
--1018 숙제
--employee 테이블에서 남자만 사원번호, 사원명, 주민번호, 연봉을 나타내세요.
--단, 주민번호의 뒷6자리는 *처리하세요.
--남자사원만 조회
select
emp_id "사원번호",
emp_name "사원명",
rpad(substr(emp_no,1,8),14,'*') "주민번호",
salary "연봉"
from
employee
where
regexp_like(substr(emp_no,8,1),'^(1|3)'); --혹은 in('1','3')
select * from employee;
commit;