Oracle(2022.02.14)-REPLACE ,INSTR,MOD등
8)REPLACE 사용빈도:***
-문자열 대치 또는 단어 내부의 공백 제거용으로 주로 사용
(사용형식)
REPLACE(c1,c2[,c3])
. 주어진 문자열 c1에서 모든 c2문자열을 찾아 c3문자열로 대치(치환)
.c3가 생략되면 c2를 제거
사용예)상품테이블에서 상품명 중 '대우'를 찾아 'APPLE'로 치환하시오
SELECT PROD_NAME AS " 구 상품명",
REPLACE(PROD_NAME,'대우','APPLE') AS "신 상품명"
FROM PROD
WHERE PROD_NAME LIKE '%대우%';빈공간 있는 사용자정의글자이면 " "를 이용해서 묶어줘야 한다.
AS "신 상품명" , AS 신상품명
사용예)회원테이블의 우편번호컬럼에서 '-'를 제거하여 출력하시오
SELECT MEM_NAME AS 회원이름,
MEM_ZIP AS "원 우편번호",
REPLACE(MEM_ZIP,'-') AS 우편번호
FROM MEMBER;
TO_NUMBER(MEM_ZIP) AS "원 우편번호", -->불가능,원래 문자열이라서
TO_NUMBER(REPLACE(MEM_ZIP,'-')) AS 우편번호 --> 얘는 TO_NUMBER가능 숫자로 이제 사용할 수 있게된다.
:
데이터 생략
사용예)회원테이블 집전화번호(MEM_HOMETEL)에서 숫자로만 구성된 값을 구하시오
SELECT MEM_NAME AS 회원번호,
MEM_HOMETEL AS "구 집전화번호",
TO_NUMBER(REPLACE(MEM_HOMETEL,'-')) AS 집전화번호
FROM MEMBER;
:
데이터 생략
사용예)사원테이블의 사원명(EMP_NAME)에서 공백을 제거하시오
Alias는 사원번호, 사원명, 입사일, 부서코드
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
HIRE_DATE AS 입사일,
DEPARTMENT_ID AS 부서코드,
REPLACE(EMP_NAME,' ') AS "공백제거 사원명"
FROM HR.EMPLOYEES;
:
데이터 생략
9)INSTR 사용빈도:**
-특정 문자열의 시작위치값을 반환
(사용형식)
INSTR(c1,c2[,m[,n]])
. 주어진 문자열 c1에서 c2문자열의 시작 위치값을 반환
. 'm'은 검색 시작위치를 지정하는 경우 사용
. 'n'은 반복 횟수를 지정하여 검색하는 경우 사용
사용예)
SELECT INSTR('APPLE PERSIMMON BANNER ORANGE','ER'),
INSTR('APPLE PERSIMMON BANNER ORANGE','A',5),
INSTR('APPLE PERSIMMON BANNER ORANGE','A',1,3),
-->첫번째 만나서 3번째 만나는 A
INSTR('APPLE PERSIMMON BANNER ORANGE','A',1,4),
-->4번째로 만나는 A 그런데 0이면 조건에 맞는것이 없다.
INSTR('APPLE PERSIMMON BANNER ORANGE','KOR')
FROM DUAL;
10)LENGTH, LENGTHB 사용빈도:**
-문자열의 길이(LENGTH), 문자열 저장공간의 크기(BYTE수,LENGTHB) 반환
(사용형식)
LENGTH(c1), LENGTHB(c1)
사용예)
SELECT MEM_ADD1 AS 주소,
LENGTH(MEM_ADD1) AS "주소의 글자수",
LENGTHB(MEM_ADD1) AS "주소기억공간의 크기"
FROM MEMBER;
-->3*8=24 +빈공간2 =26 (한글은 3BYTE)
:
데이터 생략
2. 숫자함수
1)수학적함수 사용빈도:*
-삼각함수, 지수함수 등을 제공
-ABS(n) : n의 절대값 반환
-SIGN(n): n의 부호에 따라 음수이면 -1, 양수이면1, 0이면 0을 반환
-SQRT(n): n의 평방근 값을 반환
-POWER(e,n): e의 n승 값(e를 n번 반복 곱셈한 결과) 반환
-그 밖에 SIN, TAN, COS, LOG 등의 함수 사용 가능
사용예)
SELECT ABS(-100),
ABS(0.00005),
SIGN(-100000),
SIGN(0),
SIGN(0.000009),
SQRT(24),
POWER(2,10)
FROM DUAL;
2)GREATEST, LEAST 사용빈도:**
-최대값과 최소값 반환
(사용형식)
GREATEST(n1,n2[,n3,...])-주어진 수 중 최대값 반환
LEAST(n1,n2[,n3,...]) -주어진 수 중 최소값 반환
--> 한 사람의 수평적인 데이터중에서 최대값 반환할 때 사용
GREATEST, LEAST: 횡적,알고 있는 자료값, ex) 국,영,수에서 가장 잘 본 과목?
MAX: 종적, 알고있지 않은 자료값 ex) 국어과목에서 시험을 제일 잘 본 학생?
사용예)
SELECT GREATEST('홍길동','김선동','홍길순'),
LEAST('홍길동',57,'홍길순')
FROM DUAL;
-->김선동 아웃 제일 작아서. 홍길동과 홍길순 그런데 홍길은 똑같으므로 동과 순으로 비교하여 순 즉 가나다 순
-->홍길동 아웃 제일 커서. 홍자는 아스키 코드 값이 크기때문 즉 문자열이 아스키코드 변환되어 비교
사용예) 회원테이블에서 회원들이 보유한 마일리지를 조사하여 1000미만의 회원들의 마일리지를
1000로 부여하여 출력하시오
Alias는 회원번호, 회원명, 원래의 마일리지,변경 마일리지
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS "원래의 마일리지",
GREATEST(MEM_MILEAGE,1000) AS "변경 마일리지"
-->기준보다 작으면 1000을 출력 즉 1000보다 작으면 1000을 출력
FROM MEMBER
ORDER BY 3;
:
데이터 생략
3) ROUND, TRUNC 사용빈도:***
-반올림과 자리버림한 결과 반환
(사용형식)
ROUND(n[,l])
TRUNC(n[,l])
. 주어진 수 n을 l+1번째 자리에서 반올림(ROUND) 또는 자리버림(TRUNC)하여 반환
. l이 생략되면 0으로 간주 (소수점 없앰)
. l이 음수이면 n에서 정수부분 -l자리에서 반올림 또는 자리버림 수행
사용예)
SELECT ROUND(765265.56089,3),
TRUNC(765265.56089,3), -->0은 아무의미가 없어서 또 잘라진다.
TRUNC(765265.56189,3),
ROUND(765265.56189),
TRUNC(765265.561789),
ROUND(765265.56089,-2),
TRUNC(765265.56089,-2)
FROM DUAL;
사용예)사원테이블에서 이번달 보너스를 계산한 후 이를 포함하는 지급액을 계산하시오
보너스=급여*영업실적(COMMISSION_PCT)의 27%
지급액=급여+보너스
Alias는 사원번호, 사원명, 급여, 영업실적코드, 보너스, 지급액
단, 보너스와 지급액은 정수로 표현(소수 첫자리에서 반올림)
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
SALARY AS 급여,
COMMISSION_PCT AS 영업실적코드,
NVL(ROUND((SALARY *COMMISSION_PCT) *0.27,0),0) AS 보너스,
ROUND(SALARY+NVL(((SALARY *COMMISSION_PCT)*0.27),0),0) AS 지급액
FROM HR.EMPLOYEESNVL 함수는 값이 NULL인 경우 지정값을 출력하고, NULL이 아니면 원래 값을 그대로 출력한다.
- 함수 : NVL("값", "지정값")
NVL(n1,0) => n1값이 NULL이면 0을 출력해라
NVL를 사용하지않으면 COMMISSION_PCT가 NULL값이면 지급액과 보너스값이 NULL로 나온다.
영업실적이 없으면 보너스가 없지만 지급액도 NULL값이 되면 안된다.
4)MOD 사용빈도:***
-나머지를 반환(자바의 %연산자와 동일 기능을 수행하는 함수)
-내부적으
(사용형식)
MOD(n,m)
. 주어진 수 n을 m으로 나눈 나머지 반환
사용예)
SELECT MEM_NAME AS 회원명,
MEM_BIR AS 생년월일,
TRUNC((TRUNC(SYSDATE)-TRUNC(MEM_BIR))/7) ||'주' ||
->나이를 '주'수로 계산, 사실 더 정확하게는 오늘이 아직 안지났기에 SYSDATE-1 을 해줘야함
-- TRUNC((TRUNC(SYSDATE)-TRUNC(MEM_BIR))/7)/53 || '주 '||
->이렇게하면 정수 부분이 나이의 '살'이 됨(43살 이렇게)
MOD(TRUNC(SYSDATE)-TRUNC(MEM_BIR),7) ||'일 경과'
FROM MEMBER;
:
데이터 생략
5)CEIL, FLOOR
-가장 가까운 정수를 반환
(사용형식)
CEIL(n), FLOOR(n)
.CEIL(n): n과 같거나(n이 정수일때) 큰쪽에서 가장 가까운(제일 작은) 정수 반환
.FLOOR(n): n과 같거나(n이 정수일때) 작은쪽에서 가장 가까운(제일 큰) 정수 반환, MOD에 사용되어짐
사용예)
SELECT CEIL(9.2435),CEIL(9), CEIL(-9.2435),
FLOOR(9.2435),FLOOR(9),FLOOR(-9.2435)
FROM DUAL;
6)REMAINDER(n,m)
-주어진 수 n을 m으로 나눈 나머지를 반환. 단, 나머지가 m의 절반 크기보다 크면
n을 m으로 나눈 몫+1의 값이 되기위한 n값을 원수(n)에서 뺀 값을 반환
-내부에 사용되는 알고리즘이 MOD와 REMAINDER가 서로 다름
MOD(n,m):n-(m* FLOOR(n/m))
REMAINDER(n,m) : n-(m*ROUND(n/m))
MOD(29,7): 29-(7*FLOOR(29/7))
29-7(7*FLOOR(4.143))
29-(7*4)
29-28 => 1
REMAINDER(29,7): 29-(7*ROUND(29/7))
29-(7*ROUND(4.143))
29-(7*4)
29-28 =>1
MOD(26,7): 26-(7*FLOOR(26/7))
26-7(7*FLOOR(3.714))
26-(7*3)
26-21 => 5
REMAINDER(26,7): 26-(7*ROUND(26/7))
26-(7*ROUND(3.714))
26-(7*4)
26-28 => -2
사용예)
SELECT MOD(29,7),REMAINDER(29,7),
MOD(26,7),REMAINDER(26,7)
FROM DUAL;
7)WIDTH_BUCKET(n,min,max,b) 사용빈도:***
-주어진수 n이 min~max 값의 범위를 b개의 구간으로 나누었을때 몇번째 구간에
속하는지를 판단하여 구간 INDEX를 반환함.
-n이 min보다 작으면 0번 구간을 반환하고, max보다 크거나 같으면 b+1번 구간 INDEX값을 반환함
min<=범위<max
- min는 '='이 포함된 구간 최하값, max는 '='이 포함 X
ex) 1~100을 10개의 구간으로 나눴을 때, n=0이면 0구간, n=1이면 1구간, n=100이면 11구간
WIDTH_BUCKET(컬럼,최소값,최대값,분할 수)
사용예)
SELECT WIDTH_BUCKET(86,1,90,9),
WIDTH_BUCKET(5,10,90,9),
WIDTH_BUCKET(90,10,90,9),
WIDTH_BUCKET(10,10,90,9)
FROM DUAL;
사용예)회원테이블에서 회원들의 마일리지 1000~8000 점을 8개 구간으로 구분하고
각회원들의 구간 인덱스 값을 구하시오
Alias는 회원번호, 회원명, 마일리지, 등급
'등급'난에 구간 인덱스를 구하되 많은 마일리지를 보유한 회원이 작은 수의 등급을
제일적은 마일리지를 보유한 회원이 제일 큰 등급을 부여할 것 (EX700-9등급 8700-0등급)
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
10-WIDTH_BUCKET(MEM_MILEAGE,1000,8000,8) AS 등급
FROM MEMBER;