Terbilang – Function Oracle


Berikut contoh terbilang dengan function oracle , sumber utama dari sini

CREATE OR REPLACE FUNCTION SAY(UANG IN NUMBER) RETURN CHAR IS
NUMS VARCHAR2(255);
HASIL VARCHAR2(255);
HASIL2 VARCHAR2(255);
HASIL3 VARCHAR2(255);
TK1 VARCHAR2(255);
TK2 VARCHAR2(255);
SEN NUMBER;
HURUF VARCHAR2(255);
FLAG VARCHAR2(1);
RUPIAH NUMBER;
VUANG NUMBER;
RUPIAH2 NUMBER;
ANG NUMBER;
PECAH VARCHAR2(2);
N NUMBER;

BEGIN
IF UANG > 9999999999999.99 THEN
RUPIAH := 0;
HASIL := 'Error ! Nilai lebih besar dari 9,999,999,999,999.99 ';
ELSE
VUANG := ROUND(UANG,2);
NUMS := RTRIM(LTRIM(TO_CHAR(VUANG,'9999999999999.99')));
SEN := TO_NUMBER(SUBSTR(NUMS,INSTR(NUMS,'.',1,1)+1,2));
RUPIAH := TRUNC(VUANG);
END IF;

WHILE RUPIAH != 0 LOOP
FLAG := 'T';
IF RUPIAH <= 999 THEN
RUPIAH2 := RUPIAH;
TK1 := '';
RUPIAH := 0;
ELSIF RUPIAH <= 999999 THEN
RUPIAH2 := TRUNC(RUPIAH/1000);
TK1 := 'ribu ';
FLAG := 'F';
RUPIAH := TRUNC(MOD(RUPIAH,1000));
ELSIF RUPIAH <= 999999999 THEN
RUPIAH2 := TRUNC(RUPIAH/1000000);
TK1 := 'juta ';
RUPIAH := TRUNC(MOD(RUPIAH,1000000));
ELSIF RUPIAH <= 999999999999 THEN
RUPIAH2 := TRUNC(RUPIAH/1000000000);
TK1 := 'milyar ';
RUPIAH := TRUNC(MOD(RUPIAH,1000000000));
ELSIF RUPIAH <=999999999999999 THEN
RUPIAH2 :=TRUNC(RUPIAH/1000000000000);
TK1 :='trilyun ';
RUPIAH :=TRUNC(MOD(RUPIAH,1000000000000));
END IF;

HASIL2 := NULL;

WHILE RUPIAH2 != 0 LOOP
IF RUPIAH2 <= 9 THEN
TK2 := NULL;
ANG := RUPIAH2;
RUPIAH2 := 0;
ELSIF RUPIAH2 <= 99 THEN
IF RUPIAH2 > 10 AND RUPIAH2 <= 19 THEN
TK2 := 'belas ';
ANG := TRUNC(MOD(RUPIAH2,10));
RUPIAH2 := 0;
ELSE
TK2 := 'puluh ';
ANG := TRUNC(RUPIAH2/10);
RUPIAH2 := TRUNC(MOD(RUPIAH2,10));
END IF;
ELSIF RUPIAH2 <= 999 THEN
TK2 := 'ratus ';
ANG := TRUNC(RUPIAH2/100);
RUPIAH2 := TRUNC(MOD(RUPIAH2,100));
END IF;

IF ANG = 1 THEN
IF FLAG = 'F' THEN
HURUF := 'se';
ELSE
IF TK2 IS NULL THEN
HURUF := 'satu ';
ELSE
HURUF := 'se';
END IF;
END IF;

ELSIF ANG = 2 THEN
HURUF := 'dua ';
ELSIF ANG = 3 THEN
HURUF := 'tiga ';
ELSIF ANG = 4 THEN
HURUF := 'empat ';
ELSIF ANG = 5 THEN
HURUF := 'lima ';
ELSIF ANG = 6 THEN
HURUF := 'enam ';
ELSIF ANG = 7 THEN
HURUF := 'tujuh ';
ELSIF ANG = 8 THEN
HURUF := 'delapan ';
ELSIF ANG = 9 THEN
HURUF := 'sembilan ';
ELSE
HURUF := NULL;
END IF;
HASIL2 := HASIL2 || HURUF || TK2;
END LOOP;
HASIL := HASIL || HASIL2 || TK1;
END LOOP;

IF SEN > 0 THEN
HURUF := NULL;
N := 1;
PECAH := LPAD(TO_CHAR(SEN),2,'0');

WHILE N < 3 LOOP
ANG := TO_NUMBER(SUBSTR(PECAH,N,1));
IF N = 1 AND ANG = 0 THEN
HURUF := 'nol ';
ELSE
IF ANG = 1 THEN
HURUF := 'satu ';
ELSIF ANG = 2 THEN
HURUF := 'dua ';
ELSIF ANG = 3 THEN
HURUF := 'tiga ';
ELSIF ANG = 4 THEN
HURUF := 'empat ';
ELSIF ANG = 5 THEN
HURUF := 'lima ';
ELSIF ANG = 6 THEN
HURUF := 'enam ';
ELSIF ANG = 7 THEN
HURUF := 'tujuh ';
ELSIF ANG = 8 THEN
HURUF := 'delapan ';
ELSIF ANG = 9 THEN
HURUF := 'sembilan ';
ELSE
HURUF := NULL;
END IF;
END IF;
HASIL3 := HASIL3||HURUF ;
N := N + 1;
END LOOP;
HASIL3 := ' koma ' || HASIL3;
END IF;

HASIL := HASIL || HASIL3;
RETURN (HASIL);
END SAY;
/

Contoh Query memanggil function :
select say('711,22') from dual


Semoga Membantu :D




About blackphp

All about programming php, ajax, jquery, mysql, etc, tips n trick. Now, i'm concern in php programming with codeigniter framework n also training my design skill with photoshop or corel... wish me luck! ('o')

Posted on May 30, 2012, in Oracle. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: