숫자함수

1)수학적함수(ABS(n), SIGN(n), POWER(n,y), SQRT(n),...)

• ABS(n): n의 절대값
• SIGN(n) : n의 부호에 따라 -1(음수), 0(0), 1(양수)의 값을 반환
• POWER(n,y) : n의 y승 반환
• SQRT : 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)을 반환

사용예) 회원테이블에서 회원들의 마일리지가 1000미만인 회원들의 마일리지를 1000으로 바꾸시오.
Alias는 회원번호, 회원명, 원래마일리지, 변경마일리지

   SELECT MEM_ID AS 회원번호, 
              MEM_NAME AS 회원명,
              MEM_MILEAGE AS 원래마일리지,
              GREATEST(MEM_MILEAGE,1000) AS 변경마일리지
         FROM MEMBER;

3)ROUND(n1[,i]), TRUNC(n[,i])

• ROUND
  . 주어진 수 n에서 i가 양수인경우 소수점이하 i+1번째 자리에서 반올림하여 i번째까지 반환
  . i가 음수이면 주어진 수 n의 정수부분에서 -i번째자리에서 반올림
  . i 가 생략되면 0으로 간주 --정수부분만 취함
• TRUNC : ROUND와 같이 방법으로 연산하나 반올림하지 않고 절삭하여 반환

사용예) 사원테이블(EMP)에서 보너스와 세금을 계산하여 이번달 지급액을 조회하는 Query를 작성하시오
Alias는 사원번호, 사원명, 급여, 영업실적, 보너스, 세금, 지급액

    보너스=급여*영업실적의 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(ROUND((SALARY*COMMISSION_PCT)*0.25,1),0))*0.13,1) 
               AS 세금,
              SALARY+ NVL(ROUND((SALARY*COMMISSION_PCT)*0.25,1),0)
              - ROUND((SALARY+ROUND((SALARY*COMMISSION_PCT)*0.25,1))*0.13,1) 
              AS 지급액
          FROM EMP;

        
          

사용예) 장바구니 테이블에서 2005년 회원들의 구매정보를 이용하여 마일리지를 계산하시오
상품별 마일리지=상품의 마일리지*구매수량이다.

1) 상품별 마일리지 설정: 상품의 판매가의 0.05%(0.0005)

 UPDATE PROD
          SET PROD_MILEAGE=PROD_PRICE*0.0005;

   COMMIT;

2) 회원테이블의 보유마일리지를 0으로 변경

UPDATE MEMBER
            SET MEM_MILEAGE=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%');
                      
                                      COMMIT;

4) FLOOR(n), CELL(n)

• FLOOR : n과 같거나 n작은수 중 가장 큰 정수(작은쪽에서 가장 가까운 정수)
• CEIL : n과 같거나 n큰수 중 가장 작은 정수(큰쪽에서 가장 가까운 정수)
  무조건 자리올림을하는 결과로 급여, 세금 등 금액과 관련된 계산에 주로 사용

사용예)

SELECT FLOOR(123.456), FLOOR(-123.456),
              CEIL(123.456), CEIL(-123.456)
        FROM DUAL;

5) MOD(n1, n2)

• 주어진 수 n1을 n2로 나눈 나머지를 반환
• 자바의'%'연산자와 같은 기능 -
• 오라클에서 연산자는 사칙연산자밖에없어서
  함수로 나머지 구해야함

사용예) 키보드로 년도(4자리정수)를 입력받아 그해가 윤년인지 평년인지 판별하는 코드를 작성하시오
윤년=4의 배수이면서 100의 배수가 아니거나 또는 400의 배수가 되는 해

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
         V_RES :=V_YEAR||'은 윤년입니다!';
         ELSE
           V_RES :=V_YEAR||'은 평년입니다!';
           END IF;

       DEMS_OUTPUT.PUT_LINE(V_RES);
       END;

   

6)REMAINDER(n,c)

• 주어진 수 n을 c로 나눈 나머지를 반환
• MOD 와 비슷한 기능이나 나머지의 값의 크기에 따라 다른 결과반환
  (내부처리가 다름)
  (1) MOD
  나머지 = 분모 - 분자FLOOR(분모/분자)
  (2) REMAINDER
  나머지 = 분모 - 분자ROUND(분모/분자)
  ex)

MOD(13,7)= 13 - 7*FLOOR(13/7) --FLOOR n의 가까운 값의 가장큰 값
            = 13 - 7*FLOOR(1.857..)
            = 13 - 7*1
            = 6

 REMAINDER(13,7) = 13 - 7*ROUND(13/7)
                 = 13 - 7*ROUND(1.857..)--소수첫째자리에서 반올림
                 = 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..)
                 --소수점이하 0.5넘지않으면 mod나 remainder나 
                 값 똑같음.
                 = 15 - 7*2
                 = 1

7) WIDTH_BUCKET(n, min, max, b)

• 주어진 값(min~max)을 b개의 구간으로 나누었을 때 n이 속한 구간의 순번(인덱스)을 반환

사용예) 회원테이블에서 회원들이 보유한 마일리지를(100~20000)를 10개의 구간으로 나누고 각 회원들이 그 중 어느구간에 속하는지를 조회하시오
Alias는 회원번호, 회원명, 마일리지, 구간값

 SELECT MEM_ID AS 회원번호,
              MEM_NAME AS 회원명,
              MEM_MILEAGE AS 마일리지,
              WIDTH_BUCKET(MEM_MILEAGE,20000,100,10) AS 등급
              -- 11-WIDTH_BUCKET(MEM_MILEAGE,100,20000,10) AS 등급
         FROM MEMBER;

사용예) 회원들의 마일리지를(100-25000)을 3개의 구간으로 구분하고 각 회원들이 속한 구간값이 1이하이면 '새싹회원', 2 구간에 속하면
'정상활동회원', 그 이상이면 'VIP회원'을 비고난에 출력하시오
Alias는 회원번호, 회원명, 마일리지, 구간값, 비고

SELECT MEM_ID AS 회원번호,
              MEM_NAME AS 회원명, 
              MEM_MILEAGE AS 마일리지, 
              WIDTH_BUCKET(MEM_MILEAGE,100,25000,3) AS 구간값, 
              CASE WHEN WIDTH_BUCKET(MEM_MILEAGE,100,25000,3)<=1 
              THEN --자바의 IF문과 같음.
                        '새싹회원'
                   WHEN WIDTH_BUCKET(MEM_MILEAGE,100,25000,3)=2 
                   THEN
                        '정상활동회원'
                   ELSE 
                        'VIP'
              END AS 비고
        FROM MEMBER;