금액을 한글로 변환하는 함수... 0 0 5,680

by 정재홍 [2006.02.01 12:25:47]


제가 MSSQL로 만든 함수 인데 오라클로 변경하시면 가능합니다.

 

ALTER FUNCTION UF_NUMTOKOR (
  @AMOUNT   NUMERIC(12,0)
) RETURNS   VARCHAR(50) AS
/*-----------------------------------------------------------------------------
  함수  명 : UF_NUMTOKOR
  호출PROG : 공용
  작업내용 : 숫자를 한글값으로 RETURN
  작성일자 : 2005/07/27
  작 성 자 : 동서정보 정재홍
  수정일자 :
  수 정 자 :
  설    명
  1. 예) 10202 --> ₩일만이백이원정
-----------------------------------------------------------------------------*/
BEGIN
   DECLARE @LI              INT,
           @LL_PRICE        VARCHAR(40),
           @LL_TMP          VARCHAR(02),
           @LL_TMP_DANWEE   VARCHAR(40),
           @LFLAG           VARCHAR(01),
           @LS_AMOUNT       VARCHAR(12)
   --
   SET @LS_AMOUNT = RTRIM(CAST(@AMOUNT AS VARCHAR(15)))
   SET @LFLAG     = 'N'
   SET @LI        = 1
   SET @LL_TMP_DANWEE = ''
   SET @LL_PRICE  = ''
   SET @LL_TMP    = ''
   --
   WHILE @LI <= LEN(@LS_AMOUNT)
      BEGIN
         SET @LL_TMP = CASE LEFT(RIGHT(@LS_AMOUNT, @LI), 1) WHEN '1' THEN '일'
                                                            WHEN '2' THEN '이'
                                                            WHEN '3' THEN '삼'
                                                            WHEN '4' THEN '사'
                                                            WHEN '5' THEN '오'
                                                            WHEN '6' THEN '육'
                                                            WHEN '7' THEN '칠'
                                                            WHEN '8' THEN '팔'
                                                            WHEN '9' THEN '구'
                                                                     ELSE '0'
                        END
         --
         IF  @LL_TMP = '0'
             BEGIN
                SET @LI = @LI + 1
                CONTINUE
             END
         --
         SET @LL_TMP_DANWEE = @LL_TMP
         --
         IF  @LI = 1
             BEGIN
                SET @LL_PRICE = @LL_TMP_DANWEE
                SET @LI = @LI + 1
                CONTINUE
             END

         SET @LL_TMP_DANWEE = CASE ((@LI - 1) % 4) WHEN 1 THEN @LL_TMP_DANWEE + '십'
                                                   WHEN 2 THEN @LL_TMP_DANWEE + '백'
                                                   WHEN 3 THEN @LL_TMP_DANWEE + '천'
                                                          ELSE @LL_TMP_DANWEE + ''
                               END


         IF  @LI > 4 AND @LI < 9 AND @LFLAG <> 'M'
             BEGIN
                SET @LL_TMP_DANWEE = @LL_TMP_DANWEE + '만'
                SET @LFLAG         = 'M'
             END

         IF  @LI > 8 AND @LI < 13 AND @LFLAG <> 'U'
             BEGIN
                SET @LL_TMP_DANWEE = @LL_TMP_DANWEE + '억'
                SET @LFLAG         = 'U'
             END

         IF  @LI > 12 AND @LFLAG <> 'J'
             BEGIN
                SET @LL_TMP_DANWEE = @LL_TMP_DANWEE + '조'
                SET @LFLAG         = 'J'
             END
         --
         SET @LL_PRICE = ISNULL(@LL_TMP_DANWEE,'') + ISNULL(@LL_PRICE,'')
         --
         SET @LI = @LI + 1
         --
      END

   RETURN '₩' + @LL_PRICE + '원정'
--
END
--
GO
--

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입