2.숫자함수
1) 수학적함수(ABS(n), SIGN(n), POWER(n,y), SQRT(n),...)
- ABC(n) : n의 절대값
- SIGN : n의 부호에 따라 -1(음수이면 크기에 상관없이 -1), 0(0), 1(양수이면 크기에 상관없이 1)의 값을 반환
- POWER(n,y) : n의 y승 반환
- SQRT(n) : n의 평방근 값을 반환 --루트값
사용예)SELECT ABS(-10.999),ABS(23.5),SIGN(10000),SIGN(0.00001),SIGN(-123),SIGN(0),POWER(2,10),
ROUND(SQRT(85),2) --소수 둘째 자리 까지 표현
FROM DUAL;
2)GREATEST(n1,n2[,n3...]),LEAST(n1,n2[,n3...])
- 주어진 데이터 중 최대값(GREATEST)와 최소값(LEAST)을 반환 --알려진 값중에 최대 최소값 구하기(한 행에서 최대 최소)
--NIM과 MAX는 알려지지 않은 값들의 최대 최소값을 구하는 것
사용예) 회원테이블에서 회원들의 마일리지가 1000미만인 회원들의 마일리지를 1000로 바꾸시오
Alias는 회원번호, 회원명, 원래마일리지, 변경마일리지
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 원래마일리지,
GREATEST(MEM_MILEAGE,1000) AS 변경마일리지
--1000을 마일리지와 비교해서 1000이 안되는 마일리지는 1000이라는 최대값으로 바꿈
FROM MEMBER;
3) ROUND(n1[,i]), TRUNC(n1[,i])
- ROUND --돈을 받아낼때
. 주어진 수 n에서 I가 양수인경우 소숫점이하 i+1번째 자리에서 반올림하여 i번째까지 반환
. i가 음수이면 주어진 수 n의 정수부분에서 -i번째자리에서 반올림
. i가 생략되면 0으로 간주 --정수부분만 출력
- TRUNC: ROUND와 같이 방법으로 연산이나 반올림하지 않고 절삭(자리버림)하여 반환 -- 돈을 줄때
사용예) 사원테이블(EMP)에서 보너스와 세금을 계산하여 이번달 지급액을 조회하는 Query를 작성하시오
Alias는 사원번호, 사원명, 급여, 영업실적(commission_pct), 보너스, 세금, 지급액
보너스 = 급여 * 영업실적의 25%
세금 = (급여+보너스)의 13%
지급액 = 급여 + 보너스 - 세금이며 소수1자리까지 출력하시오
SELECT EMPLOYEE_ID AS 사원번호,
EMP_NAME AS 사원명,
SALARY AS 급여,
COMMISSION_PCT AS 영업실적,
NVL(ROUND((SALARY * COMMISSION_PCT)*0.25,1),0) AS 보너스,
ROUND((SALARY+ NVL(SALARY * COMMISSION_PCT*0.25,0),1))*0.13,1) AS 세금,
SALARY+NVL((SALARY * COMMISSION_PCT)*0.25,1),0)
-(SALARY+NVL(SALARY * COMMISSION_PCT*0.25,1),0)*0.13) AS 지급액
FROM EMP;
**사용예) **장바구니테이블에서 2005년 회원들이 구매정보를 이용하여 마일리지를 계산하시오
상품별 마일리지 = 상품의 마일리지 * 구매수량이다.
1)상품별 마일리지 설정: 상품 판매가의 0.05%(0.0005)
SELECT 3330000*0.005 FROM DUAL;
UPDATE PROD
SET PROD_MILEAGE =PROD_PRICE*0.0005;
COMMIT; --저장
2) 회원테이블의 보유마일리지를 0으로 변경
UPDATE MEMBER
SET MEM_MILEAGE=0; --0으로 세팅
COMMIT;
3) 구매내용에 따라 회원별 마일리지 결정
SELECT CART_MEMBER AS 회원번호,
SUM(PROD_MILEAGE*CART_QTY) AS 마일리지합계
FROM CART, PROD
WHERE CART_PROD=PROD_ID
GROUP BY CART_MEMBER
ORDER BY 1;
4)회원 테이블에서 3)에서 구한 마일리지 반영
UPDATE MEMBER B
SET MEM_MILEAGE =
(SELECT NVL(A.MILE,0)
FROM (SELECT CART_MEMBER AS CID,
--회원별 마일리지를 곱하면 합계가 나옴 /프롬 후 괄호는 서브쿼리(뷰)
SUM(PROD_MILEAGE*CART_QTY)AS MILE
--마일리지 합계를 마일이라는 변수에 집어넣음
FROM CART, PROD
WHERE CART_PROD = PROD_ID
AND CART_NO LIKE '2005%'
GROUP BY CART_MEMBER)A
WHERE B.MEM_ID=A.CID)
WHERE B.MEM_ID IN(SELECT DISTINCT CART_MEMBER --흔적이 없으면 구매를 한적이 없는것
FROM CART
WHERE CART_NO LIKE '2005%');--2005년에 구매한 이력이 있는 사람
COMMIT;
4)FLOOR(n), CELL(n)
- FLOOR : n과 같거나 n작은수 중 가장 큰 정수(작은쪽에서 가장 가까운 정수)
- CEIL : n과 같거나 n큰수 중 가장 작은 정수(큰쪽에서 가장 가까운 정수)
무조건 자리 올림을 하는 결과로 급여, 세금, 등 금액과 관련된 계산에 주로 사용
--소수점이 아니여도 자리올림 해야함
사용예) SELECT FLOOR(123.456), FLOOR(-123.456),
CELL(123.456), CELL(-123.456)
FROM DUAL;
5) MOD(n1, n2)
- 주어진 수 n1을 n2로 나눈 나머지를 반환
- 자바의 '%' 연산자와 같은 기능 --함수로 제공되어짐 (연산은 오라클에서 사칙연산 뿐임)
사용예) 키보드로 년도(4자리 정수)를 입력 받아 그해가 윤년인지 평년인지 판별하는 코드를 작성하시오
윤년 = (4의 배수이면서 100의 배수)가 아니거나 (400의 배수)가 되는 해
--OR로 연결(또는)
ACCEPT P_YEAR PROMPT '년도 입력 :'
DECLARE
V_YEAR NUMBER := TO_NUMBER('&P_YEAR');
V_RES VARCHAR2(100);
BEGIN
IF (MOD(V_YEAR,4)=0 AND MOD(V_YEAR,100)!=0) OR MOD(V_YEAR,400)=0 THEN
--4의 배수와 100의 배수가 아니거나 400의 배수일때 윤년
V_RES := V_YEAR || '은 윤년입니다!.';
ELSE
V_RES := V_YEAR || '은 평년입니다!.';
END IF
DBMS_OUTPUT.PUT_LINE(V_RES);
END;
6) REMAINDER(n,c)
-주어진 수 n을 c로 나눈 나머지를 반환
- MOD와 비슷한 기능이나 나머지의 값의 크기에 따라 다른 결과 반환
(내부 처리가 다름)
-- %는 와일드 카드로 나머지 연산 수행 기능이 없다.
(1)MOD
나머지 = 분모 - 분자 * FLOOR(분모/분자) --FLOOR 주어진 값의 최대 정수
(2)REMAINDER
나머지 = 분모 - 분자 * ROUND(분모/분자)
EX)
MOD(13,7) = 13 - 7*FLOOR(13/7)
= 13 - 7*FLOOR(1.857...) --1.857을 초과하지 않는 최대 정수라 1이 됨
= 13 - 7*1
= 6
REMAINDER(13,7) = 13 - 7* ROUND(13/7)
= 13 - 7* ROUND(1.857...)
--소수 첫째 자리에서 반올림하여 2가 됨(1.5를 넘어서면 분자의 절반을 넘어서는것)
= 13 - 7*2
= -1 --다음 몫을 위해 더해줘야 할 수
MOD(15,7) = 15 - 7*FLOOR(15/7)
= 15 - 7*FLOOR(2.14...)
= 15 - 7*2
= 1
REMAINDER(15,7) = 15 - 7* ROUND(15/7)
= 15 - 7* ROUND(2.14...)
= 15 - 7*2
= 1
7) WIDTH_BUCKET(n,min,max,b)
--최대 최소값은 해당 구간에 들어가지 않은것
- 주어진 값(min~max)을 b개의 구간으로 나누었을 때 n이 속한 구간의 순번(인덱스)을 반환
사용예) 회원테이블에서 회원들이 보유한 마일리지(100~20000)를 10개의 구간으로 나누고 --100은 min값 20000은max값
각 회원들이 그 중 어느구간에 속하는지를 조회하시오
Alias는 회원번호, 회원명, 마일리지, 구간값
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
WIDTH_BUCKET(MEM_MILEAGE,100,20000,10) AS 구간값
FROM MEMBER;
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
WIDTH_BUCKET(MEM_MILEAGE,20000,100,10) AS 등급
--min값과 max값을 바꾸면 됨 / 바꾸지 않아도 11- 조건식을 쓰면 같은 값이 나옴
FROM MEMBER; 사용예) 회원들의 마일리지를 (100-2500)을 3개의 구간으로 구분하고
각 회원들이 속한 구간값이 1이하이면 '새싹회원', 2구간에 속하면 '정상활동회원', 그 이상이면 ,'VIP회원'을 비고난에 출력하시오
Alias는 회원번호, 회원명, 마일리지, 구간값, 비고
-- 비고난은 MEMBER테이블에 존재하지 않지만 만들어서 사용해야댐
SELECT MEM_ID AS 회원번호,
MEM_NAME AS 회원명,
MEM_MILEAGE AS 마일리지,
WIDTH_BUCKET(MEM_MILEAGE,100,25000,3) AS 구간값,
-- CASE WHEN은 자바에서 IF
CASE WHEN WIDTH_BUCKET(MEM_MILEAGE,100,25000,3) <= 1 THEN --1과 크거나 같으면 새싹회원
'새싹회원'
WHEN WIDTH_BUCKET(MEM_MILEAGE,100,25000,3) = 2 THEN --2와 같다면 정상회원
'정상활동회원'
ELSE
'VIP회원'
END AS 비고
FROM MEMBER;
신입 개발자 입니다!!!