13 Feb 2007

Oracle Package untuk fungsi ‘terbilang’ (repost)

Contoh penggunaan:
  1. SELECT PKG_DIGIT_SPELLER.ENGRupiah('12345') FROM DUAL
  2. SELECT PKG_DIGIT_SPELLER.ENGRupiah('12345.56') FROM DUAL
Hasil:
  1. TWELVE THOUSAND THREE HUNDRED AND FOURTY-FIVE RUPIAHS
  2. TWELVE THOUSAND THREE HUNDRED AND FOURTY-FIVE RUPIAHS FIFTY-SIX
CREATE OR REPLACE PACKAGE PKG_DIGIT_SPELLER IS
/*******************************************************************************

  Nama Package : PKG_DIGIT_SPELLER
  Oleh         : Ibrahim F Burhan (ibrahim@burhan.biz)
  Deskripsi    : Menghasilkan ucapan bilangan dalam bahasa Indonesia dan
                 Inggris

  Hak Cipta Oleh Ibrahim F Burhan (ibrahim@burhan.biz) 2000

  Siapa saja diperkenankan untuk memakai, mengubah ataupun mengembangkan
  source code ini, dengan syarat tetap mencantumkan keterangan ini.

  Reposted from my archive (Mar 16, 2001)
*******************************************************************************/
  TYPE typeSpellString IS VARRAY(100) OF VARCHAR2(20);

  arrSpellString typeSpellString;

  /* Bahasa Indonesia */
  FUNCTION INA(vchrDigitString VARCHAR2) RETURN VARCHAR2;

  /* English */
  FUNCTION ENG(vchrDigitString VARCHAR2) RETURN VARCHAR2;

  /* Bahasa Indonesia */
  FUNCTION INARupiah(vchrDigitString VARCHAR2) RETURN VARCHAR2;

  /* English */
  FUNCTION ENGRupiah(vchrDigitString VARCHAR2) RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY PKG_DIGIT_SPELLER IS

  /* Initialization */
  /* Must be called before executing other procedures */
  PROCEDURE Initialize IS
  BEGIN
    arrSpellString := typeSpellString ('SATU ',           --1
                                       'DUA ',
                                       'TIGA ',
                                       'EMPAT ',
                                       'LIMA ',
                                       'ENAM ',
                                       'TUJUH ',
                                       'DELAPAN ',
                                       'SEMBILAN ',
                                       'PULUH ',          --10
                                       'BELAS ',
                                       'SE',
                                       'RATUS ',
                                       NULL,
                                       'RIBU ',
                                       'JUTA ',
                                       'MILYAR ',
                                       'TRILIUN ',
                                       NULL,
                                       NULL,             --20
                                       'ONE ',
                                       'TWO ',
                                       'THREE ',
                                       'FOUR ',
                                       'FIVE ',
                                       'SIX ',
                                       'SEVEN ',
                                       'EIGHT ',
                                       'NINE ',
                                       'TEN ',            --30
                                       'ELEVEN ',
                                       'TWELVE ',
                                       'THIRTEEN ',
                                       'FOURTEEN ',
                                       'FIFTEEN ',
                                       'SIXTEEN ',
                                       'SEVENTEEN ',
                                       'EIGHTEEN ',
                                       'NINETEEN ',
                                       'TWENTY',        --40
                                       'THIRTY',
                                       'FOURTY',
                                       'FIFTY',
                                       'SIXTY',
                                       'SEVENTY',
                                       'EIGHTY',
                                       'NINETY',
                                       'AND ',
                                       'HUNDRED ',
                                       NULL,            --50
                                       'THOUSAND ',
                                       'MILLION ',
                                       'BILLION ',
                                       'TRILLION ');
  END;

  /* Bahasa Indonesia two digit spelling */
  FUNCTION INASpell2(vchrDigit2 VARCHAR2) RETURN VARCHAR2 IS
    intLen NUMBER;
    chrDigit1 CHAR(1);
        chrDigit2 CHAR(1);
  BEGIN
    intLen := LENGTH(vchrDigit2);
    chrDigit1 := SUBSTR(vchrDigit2, 1, 1);
    chrDigit2 := SUBSTR(vchrDigit2, 2, 1);

        IF intLen = 2 THEN
          IF (chrDigit1 = '0') THEN
            BEGIN
              IF (chrDigit2 = '0') THEN
                    RETURN arrSpellString(14);
                  ELSE
                RETURN arrSpellString(TO_NUMBER(chrDigit2));
                  END IF;
            END;
          ELSIF (chrDigit1 = '1') THEN
            BEGIN
              IF (chrDigit2 = '0') THEN
                    RETURN arrSpellString(12)||arrSpellString(10);
                  ELSIF (chrDigit2 = '1') THEN
                    RETURN arrSpellString(12)||arrSpellString(11);
                  ELSE
                    RETURN arrSpellString(TO_NUMBER(chrDigit2))||arrSpellString(11);
                  END IF;
            END;
          ELSE
            BEGIN
              IF (chrDigit2 = '0') THEN
                    RETURN arrSpellString(TO_NUMBER(chrDigit1))||arrSpellString(10);
                  ELSE
                    RETURN arrSpellString(TO_NUMBER(chrDigit1))||arrSpellString(10)||arrSpellString(TO_NUMBER(chrDigit2));
                  END IF;
            END;
          END IF;
        ELSE
          RETURN arrSpellString(TO_NUMBER(chrDigit1));
        END IF;
  END;

  /* Bahasa Indonesia three digit spelling */
  FUNCTION INASpell3(vchrDigit3 VARCHAR2) RETURN VARCHAR2 IS
    intLen NUMBER(1);
        chrDigit1 CHAR(1);
        chrDigit2 CHAR(1);
        chrDigit3 CHAR(1);
  BEGIN
    intLen := LENGTH(vchrDigit3);
    chrDigit1 := SUBSTR(vchrDigit3, 1, 1);
    chrDigit2 := SUBSTR(vchrDigit3, 2, 1);
    chrDigit3 := SUBSTR(vchrDigit3, 3, 1);

        IF (intLen = 1) THEN
          BEGIN
            IF (chrDigit1 = '0') THEN
                  RETURN arrSpellString(14);
                ELSE
              RETURN arrSpellString(TO_NUMBER(chrDigit1));
                END IF;
          END;
        ELSIF (intLen = 2) THEN
          BEGIN
            RETURN INASpell2(vchrDigit3);
          END;
        ELSE
          BEGIN
            IF (chrDigit1 = '0') THEN
              RETURN INASpell2(SUBSTR(vchrDigit3, -2, 2));
                ELSIF (chrDigit1 = '1') THEN
                  RETURN arrSpellString(12)||arrSpellString(13)||INASpell2(SUBSTR(vchrDigit3, -2, 2));
                ELSE
              RETURN arrSpellString(TO_NUMBER(chrDigit1))||arrSpellString(13)||INASpell2(SUBSTR(vchrDigit3, -2, 2));
                END IF;
          END;
        END IF;
  END;

  /* Bahasa Indonesia integer spelling */
  FUNCTION INASpellInteger(vchrDigitInteger VARCHAR2) RETURN VARCHAR2 IS
    intLen NUMBER;
        int3DigitCount NUMBER;
        intStart NUMBER;
        intLenSub NUMBER;
        i NUMBER;
        vchrSpell VARCHAR2(512) := '';
        vchrSpellTemp VARCHAR2(512) := NULL;
  BEGIN
    intLen := LENGTH(vchrDigitInteger);
        int3DigitCount := CEIL(intLen / 3);
        intStart := 1;

        FOR i IN 1..int3DigitCount LOOP
          intStart := i * 3;
          intLenSub := 3;
          IF (intStart > intLen) THEN
                intLenSub := intLen - (intStart - 3);
            intStart := intLen;
          END IF;

          vchrSpellTemp := INASpell3(SUBSTR(vchrDigitInteger, -intStart, intLenSub));

          IF vchrSpellTemp IS NULL THEN
            vchrSpell := vchrSpell;
          ELSE
            vchrSpell := vchrSpellTemp||arrSpellString(13+i)||vchrSpell;
          END IF;

        END LOOP;

        RETURN vchrSpell;
  END;

  /* Bahasa Indonesia spelling */
  FUNCTION INA(vchrDigitString VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    Initialize;

    RETURN INASpellInteger(vchrDigitString);
  END;

  /* English two digit spelling */
  FUNCTION ENGSpell2(vchrDigit2 VARCHAR2) RETURN VARCHAR2 IS
    intLen NUMBER;
    chrDigit1 CHAR(1);
        chrDigit2 CHAR(1);
  BEGIN
    intLen := LENGTH(vchrDigit2);
    chrDigit1 := SUBSTR(vchrDigit2, 1, 1);
    chrDigit2 := SUBSTR(vchrDigit2, 2, 1);

    IF intLen = 2 THEN
          IF (chrDigit1 = '0') THEN
            BEGIN
              IF (chrDigit2 = '0') THEN
                    RETURN arrSpellString(14);
                  ELSE
                RETURN arrSpellString(20+TO_NUMBER(chrDigit2));
                  END IF;
            END;
          ELSIF (chrDigit1 = '1') THEN
            BEGIN
          RETURN arrSpellString(30+TO_NUMBER(chrDigit2));
            END;
          ELSE
            BEGIN
              IF (chrDigit2 = '0') THEN
                    RETURN arrSpellString(38+TO_NUMBER(chrDigit1))||' ';
                  ELSE
                    RETURN arrSpellString(38+TO_NUMBER(chrDigit1))||'-'||arrSpellString(20+TO_NUMBER(chrDigit2));
                  END IF;
            END;
          END IF;
        ELSE
          RETURN arrSpellString(20+TO_NUMBER(chrDigit1));
        END IF;
  END;

  /* English three digit spelling */
  FUNCTION ENGSpell3(vchrDigit3 VARCHAR2) RETURN VARCHAR2 IS
    intLen NUMBER(1);
        chrDigit1 CHAR(1);
        chrDigit2 CHAR(1);
        chrDigit3 CHAR(1);
        vchrSpellTemp VARCHAR2(500);
  BEGIN
    intLen := LENGTH(vchrDigit3);
    chrDigit1 := SUBSTR(vchrDigit3, 1, 1);
    chrDigit2 := SUBSTR(vchrDigit3, 2, 1);
    chrDigit3 := SUBSTR(vchrDigit3, 3, 1);

        IF (intLen = 1) THEN
          BEGIN
            IF (chrDigit1 = '0') THEN
                  RETURN arrSpellString(14);
                ELSE
              RETURN arrSpellString(20+TO_NUMBER(chrDigit1));
                END IF;
          END;
        ELSIF (intLen = 2) THEN
          BEGIN
            RETURN ENGSpell2(vchrDigit3);
          END;
        ELSE
          BEGIN
            IF (chrDigit1 = '0') THEN
              RETURN ENGSpell2(SUBSTR(vchrDigit3, -2, 2));
                ELSE
                  vchrSpellTemp := ENGSpell2(SUBSTR(vchrDigit3, -2, 2));
                  IF vchrSpellTemp IS NULL THEN
                    RETURN arrSpellString(20+TO_NUMBER(chrDigit1))||arrSpellString(49)||vchrSpellTemp;
                  ELSE
                RETURN arrSpellString(20+TO_NUMBER(chrDigit1))||arrSpellString(49)||arrSpellString(48)||vchrSpellTemp;
                  END IF;
                END IF;
          END;
        END IF;
  END;

  /* English integer spelling */
  FUNCTION ENGSpellInteger(vchrDigitInteger VARCHAR2) RETURN VARCHAR2 IS
    intLen NUMBER;
        int3DigitCount NUMBER;
        intStart NUMBER;
        intLenSub NUMBER;
        i NUMBER;
        vchrSpell VARCHAR2(512) := '';
        vchrSpellTemp VARCHAR2(512) := NULL;
  BEGIN
    intLen := LENGTH(vchrDigitInteger);
        int3DigitCount := CEIL(intLen / 3);
        intStart := 1;

        FOR i IN 1..int3DigitCount LOOP
          intStart := i * 3;
          intLenSub := 3;
          IF (intStart > intLen) THEN
                intLenSub := intLen - (intStart - 3);
            intStart := intLen;
          END IF;

          vchrSpellTemp := ENGSpell3(SUBSTR(vchrDigitInteger, -intStart, intLenSub));

          IF vchrSpellTemp IS NULL THEN
            vchrSpell := vchrSpell;
          ELSE
            vchrSpell := vchrSpellTemp||arrSpellString(49+i)||vchrSpell;
          END IF;

        END LOOP;

        RETURN vchrSpell;
  END;

  /* English spelling */
  FUNCTION ENG(vchrDigitString VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    Initialize;

    RETURN ENGSpellInteger(vchrDigitString);
  END;

  /* Bahasa Indonesia spelling with Rupiah currency */
  FUNCTION INARupiah(vchrDigitString VARCHAR2) RETURN VARCHAR2 IS
    vchrInString VARCHAR2(30);
  BEGIN
    vchrInString := TO_CHAR(TRUNC(TO_NUMBER(vchrDigitString), 2));

    RETURN INA(TO_CHAR(TRUNC(TO_NUMBER(vchrInString))))||' RUPIAH '||
           INA(TO_CHAR((TO_NUMBER(vchrInString) - TRUNC(TO_NUMBER(vchrInString))) * 100));
  END;

  /* English spelling with Rupiah currency */
  FUNCTION ENGRupiah(vchrDigitString VARCHAR2) RETURN VARCHAR2 IS
    vchrInString VARCHAR2(30);
  BEGIN
    vchrInString := TO_CHAR(TRUNC(TO_NUMBER(vchrDigitString), 2));

    RETURN ENG(TO_CHAR(TRUNC(TO_NUMBER(vchrInString))))||' RUPIAHS '||
           ENG(TO_CHAR((TO_NUMBER(vchrInString) - TRUNC(TO_NUMBER(vchrInString))) * 100));
  END;

END ;

Tidak ada komentar:

Posting Komentar