오라클(SQL)숫자변환 문자열( 숫자변환, TO_NUMBER(c [,fmt]))

숫자변환 문자열

테이블 다운로드

 

 형식문자열       의미
---------------------------------------------------------------
   9              대응되는 데이터가 유효숫자이면 원본자료를 출력하고
                  대응되는 데이터가 무효의 0이면 공백처리 
   0              대응되는 데이터가 유효숫자이면 원본자료를 출력하고
                  대응되는 데이터가 무효의 0이면 '0'을 출력
   $,L(l)         화폐기호를 출력
   MI             데이터가 음수인 경우 우측에 '-'부호 출력(반드시 
                  형식문자열 맨 우측에 기술)
   PR             데이터가 음수인 경우 '-'부호 대신 '< >'로 묶어 출력
   ,(comma)       3자리마다 자리점 출력
   .(dot)         소숫점 출력 
-----------------------------------------------------------------   
사용예) 우리나라 시도별 대출잔액테이블(KOR_LOAN_STATUS)에서
       2013년 11월 정보를 조회하시오
       Alias는 시도, 대출구분, 대출잔액이며 대출잔액은 3자리마다 자리점과
       소숫점을 삽입하여 출력하시오

SELECT REGION AS 시도, 
       GUBUN AS 대출구분, 
       TO_CHAR(LOAN_JAN_AMT, '9,999,999.99') AS 대출잔액1,
       TO_CHAR(LOAN_JAN_AMT, '0,000,000.00') AS 대출잔액2,
       TO_CHAR(LOAN_JAN_AMT, 'L9,999,999.99') AS 대출잔액3
  FROM KOR_LOAN_STATUS
 WHERE PERIOD='201311'
 ORDER BY 1; 

테이블 변경 )상품테이블에서 제품번호 'P102000001'과 'P102000002'제품의 할인판매가를  
                 매입가격의 90%로 조정하시오

UPDATE PROD
   SET PROD_SALE=PROD_COST*0.9
 WHERE PROD_ID IN ('P102000001','P102000002'); 

COMMIT;

테이블 변경 확인

SELECT PROD_COST,PROD_SALE
  FROM PROD
 WHERE PROD_ID IN ('P102000001','P102000002');

사용예)상품테이블에서 거래처가 대우전자(P10201)의 제품정보를 조회하시오
      Alias는 상품명,매입가,판매가,할인판매가,할인판내이익이며
      할인판내이익은 할인판매가-매입가이고,
      음수인 경우 < >안에 출력하시오

SELECT PROD_NAME AS 상품명,
       TO_CHAR(PROD_COST,'9,999,999') AS 매입가,
       TO_CHAR(PROD_PRICE,'9,999,999') AS 판매가,
       TO_CHAR(PROD_SALE,'9,999,999') AS 할인판매가,
       TO_CHAR(PROD_SALE-PROD_COST,'9,999,999PR') AS 할인판매이익
  FROM PROD
 WHERE UPPER(PROD_BUYER)='P10201'; 

2. TO_NUMBER(c [,fmt])
  - 주어진 문자열 c(숫자형식)를 숫자로 변환
  - fmt는 TO_CHAR형식과 동일하나 모두 적용되지는 않음
  
사용예)

SELECT TO_NUMBER('2021'),
       TO_NUMBER('$1,200','$9,999')+100,
       TO_NUMBER('<200,000>','999,999PR')/10
  FROM DUAL;

사용예)오늘이 2005년 4월 1일이라고 가정하고 오철회 회원('k001')이
      상품을 구입하기 위해 쇼핑몰에 로그인 할때 카트번호를
      부여하도록 코딩하시오

CREATE OR REPLACE FUNCTION FN_CART_NO(
 P_DATE IN DATE)
RETURN CHAR
IS 
  V_NUM NUMBER := 0;
  V_COUNT NUMBER := 0;
  V_DAY CHAR(9) := TO_CHAR(P_DATE,'YYYYMMDD')||'%';
  V_CART_NO CART.CART_NO%TYPE;
BEGIN
  SELECT COUNT(*) INTO V_COUNT
    FROM CART
   WHERE CART_NO LIKE  V_DAY;
  
  IF V_COUNT=0 THEN
     V_NUM:=1;
  ELSE 
     SELECT MAX(SUBSTR(CART_NO,9))+1 INTO V_NUM
       FROM CART
      WHERE CART_NO LIKE  V_DAY;  
  END IF;
  V_CART_NO := TO_CHAR(P_DATE,'YYYYMMDD')||TRIM(TO_CHAR(V_NUM,'00000'));
  
  DBMS_OUTPUT.PUT_LINE(V_CART_NO);
  RETURN V_CART_NO;
END;  

확인 해보기)

SELECT FN_CART_NO(TO_DATE('20050405')) AS 카트번호
  FROM DUAL;

SELECT FN_CART_NO(TO_DATE('20050810')) AS 카트번호
  FROM DUAL;

사용예)오늘이 2005년 8월 10일이라고 가정하고 다음 자료를 입력하시오
   구매회원 : 탁원재('n001')
   상품코드 : 남성용 봄자켓2('P202000014')
   구매수량 : 2

테이블 내용 변경)

INSERT INTO CART(CART_MEMBER,CART_NO,CART_PROD,CART_QTY)
  VALUES('n001',FN_CART_NO(TO_DATE('20050810')),'P202000014',2);

테이블 내용 변경 확인)

SELECT * FROM CART WHERE CART_NO LIKE '200508%';

   상품코드 : 남성용 향수('P302000011')
   구매수량 : 5 

DECLARE
  V_COUNT  NUMBER:=0;
  V_CART_NO NUMBER:=0;
BEGIN
  SELECT COUNT(*) INTO V_COUNT
    FROM CART
   WHERE SUBSTR(CART_NO,1,8)='20050810'
     AND CART_MEMBER='n001';
  
  IF V_COUNT = 0 THEN
     INSERT INTO CART
       VALUES('n001',FN_CART_NO(TO_DATE('20050810')),'P202000014',2);
  ELSE
     SELECT DISTINCT CART_NO INTO V_CART_NO
       FROM CART
      WHERE SUBSTR(CART_NO,1,8)='20050810'
        AND CART_MEMBER='n001'; 
        
     INSERT INTO CART
       VALUES('n001',V_CART_NO,'P302000014',5);
  END IF;
  COMMIT;
END;  

******************

위의 코드에서 중복되는 상품 코드를 넣을수없다(p302000014)

다른 상품 코드를 넣어서 실행하면 실행된다.

 

실행결과확인)

SELECT * FROM CART WHERE CART_NO LIKE '200508%';