Beranda > Tutorial > Membuat fungsi Terbilang di Oracle

Membuat fungsi Terbilang di Oracle

sumber: http://nailuvar.com/blog/database/oracle/10/27/fungsi-terbilang/

Berikut adalah contoh fungsi tebilang menggunakan Oracle, cukup simple didalam oracle hanya dengan menggunakan fungsi TO_DATE dengan format Jsp.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
FUNCTION terbilang (
   v_number   IN   NUMBER
)
   RETURN VARCHAR2
AS
   TYPE myArray IS TABLE OF VARCHAR2 (255);
   str_array    myArray
      := myArray ('',
                  'thousand ',
                  ' million ',
                  ' billion ',
                  ' trillion ',
                  ' quadrillion ',
                  ' quintillion ',
                  ' sextillion ',
                  'septillion ',
                  ' octillion ',
                  ' nonillion ',
                  ' decillion ',
                  ' undecillion ',
                  ' duodecillion '
                 );
   str_number   VARCHAR2 (50);
   l_return     VARCHAR2 (4000);
   trans1       VARCHAR2 (4000);
   trans2       VARCHAR2 (4000);
BEGIN
   str_number := TRUNC (ABS (v_number));
   FOR i IN 1 .. str_array.COUNT
   LOOP
      EXIT WHEN str_number IS NULL;
      IF (SUBSTR (str_number, LENGTH (str_number) - 2, 3) <> 0)
      THEN
         trans1 :=
               TO_CHAR (TO_DATE (SUBSTR (str_number, LENGTH (str_number) - 2,
                                         3),
                                 'J'
                                ),
                        'Jsp'
                       )
            || str_array (i)
            || trans1;
      END IF;
      str_number := SUBSTR (str_number, 1, LENGTH (str_number) - 3);
   END LOOP;
   l_return := trans1;
   IF TO_CHAR (v_number) LIKE '%.%'
   THEN
      str_number :=
           (ABS (v_number) - TRUNC (ABS (v_number)))
         * (TO_NUMBER (RPAD ('1',
                             (LENGTH ((ABS (v_number) - TRUNC (ABS (v_number))
                                      )
                                     )
                             ),
                             RPAD ('0', 50, '0')
                            )
                      )
           );
      FOR i IN 1 .. str_array.COUNT
      LOOP
         EXIT WHEN str_number IS NULL;
         IF (SUBSTR (str_number, LENGTH (str_number) - 2, 3) <> 0)
         THEN
            trans2 :=
                  TO_CHAR (TO_DATE (SUBSTR (str_number,
                                            LENGTH (str_number) - 2,
                                            3
                                           ),
                                    'J'
                                   ),
                           'Jsp'
                          )
               || str_array (i)
               || trans2;
         END IF;
         str_number := SUBSTR (str_number, 1, LENGTH (str_number) - 3);
      END LOOP;
      l_return := trans1 || ' point ' || trans2;
   END IF;
   RETURN l_return;
END terbilang;

untuk mencobanya

1
2
3
4
SQL> SELECT TERBILANG(100) as AA FROM DUAL;
AA
-----------------
One Hundred

Untuk fungsi terbilang dalam bahasa Indonesia

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
FUNCTION terbilang_indo(v_number IN NUMBER) RETURN VARCHAR2 AS
BEGIN
    RETURN      REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
             REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
             REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
             REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
             REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
             REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
             REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
             REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(
             REPLACE(REPLACE( REPLACE( REPLACE(
             LOWER( TERBILANG(v_number))
             , 'trillion'    , 'Trilyun')
             , 'billion'    , 'Milyar')
             , 'million'    , 'Juta')
             , 'onethousand'    , 'Seribu')
             , 'thousand'    , 'Ribu')
             , 'nine hundred'    , 'Sembilan Ratus')
             , 'eigth hundred'    , 'Delapan Ratus')
             , 'seven hundred'    , 'Tujuh Ratus')
             , 'six hundred'    , 'Enam Ratus')
             , 'five hundred'    , 'Lima Ratus')
             , 'four hundred'    , 'Empat Ratus')
             , 'three hundred'    , 'Tiga Ratus')
             , 'two hundred'    , 'Dua Ratus')
             , 'one hundred'    , 'Seratus')
             , 'hundred'    , 'Ratus')
             , 'ninety'        , 'Sembilan Puluh')
             , 'eighty'        , 'Delapan Puluh')
             , 'seventy'    , 'Tujuh Puluh')
             , 'sixty'        , 'Enam Puluh')
             , 'fifty'        , 'Lima Puluh')
             , 'forty'        , 'Empat Puluh')
             , 'thirty'        , 'Tiga Puluh')
             , 'twenty'        , 'Dua Puluh')
             , 'nineteen'    , 'Sembilanbelas')
             , 'eighteen'    , 'Delapanbelas')
             , 'seventeen'    , 'Tujuhbelas')
             , 'sixteen'    , 'Enambelas')
             , 'fifteen'    , 'Limabelas')
             , 'fourteen'    , 'Empatbelas')
             , 'thirteen'    , 'Tigabelas')
             , 'twelve'        , 'Duabelas')
             , 'eleven'        , 'Sebelas')
             , 'ten'        , 'Sepuluh')
             , 'nine'        , 'Sembilan')
             , 'eight'        , 'Delapan')
             , 'seven'        , 'Tujuh')
             , 'six'        , 'Enam')
             , 'five'        , 'Lima')
             , 'four'       , 'Empat')
             , 'three'        , 'Tiga')
             , 'two'        , 'Dua')
             , 'one'        , 'Satu')
             , 'point'        , 'koma')
             , '','');
END terbilang_indo;

untuk mencobanya

1
2
3
4
SQL> SELECT TERBILANG_INDO(100) as AA FROM DUAL;
AA
-----------------
Seratus
Kategori:Tutorial
  1. Belum ada komentar.
  1. No trackbacks yet.

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: