Wednesday, April 23, 2008

Oracle for Beginner: ORDB with Oracle (III)

In this third part of my articles about ORDB with Oracle, I just want to share about a simple example of implementation ORDB in Oracle based on class diagram UML. I want to show you about how to implement aggregation, association and inheritance in Oracle. Here is the class diagram:

Segitiga and Lingkaran class are child class from Bidang class (as parent class). Bidang class has association relationship with Warna class. This association is one direction that means Bidang class has a Warna's type attribute. First, we have to create Warna class:

create type warna as object(
r number(3),
b number(3),
g number(3),
member function getWarna return varchar

create type body warna as
member function getWarna return varchar is
return 'Red=' || to_char(self.r) || ', Blue=' || to_char(self.b) || ', Green=' || to_char(self.g);

Warna class has 3 attributes, i.e. r for Red, b for Blue and g for Green, and 1 operation that will return a varchar for the color. Now, we can create Bidang that will have an attribute as an object for Warna class.

create type Bidang as object(
x number(3),
y number(3),
color warna,
member function getLuas return number,
member function getKeliling return number
) not final;

create type body Bidang as
member function getLuas return number is
return 0;
member function getKeliling return number is
return 0;

Tuesday, April 01, 2008

Oracle for Beginner: ORDB with Oracle (II)

Continuing my last note, here we will try to use VARRAY() to make multiple value for a attribute of table. One of disadvantage of varray is we have to specify the maximum of element array that we want to save in it.

Here an example of how to create a varray type:

SQL> create type v_pengajar_t as varray(3) of pengajar_t;

Like previous example, we can create table which have one attribute with varray() type.

SQL> create table vkursus(
2 no_kursus char(5) primary key,
3 nama varchar(30),
4 pengajar v_pengajar_t
5 );

To insert new data, we can use statement below:

SQL> insert into vkursus values ('X01', 'Oracle', v_pengajar_t(pengajar_t('P01', 'Budi'), pengajar_t('P02', 'Wati')));

1 row created.

SQL> insert into vkursus values ('X02', 'ORDB with Oracle', v_pengajar_t(pengajar_t('P01', 'Budi'), pengajar_t('P03', 'Othie'), pengajar_t('P04','Indah')));

1 row created.

SQL> commit;

To make a query for varray, we can use table() function to create a virtual table from varray field. Here is an example:

SQL> select no_kursus, v.nama, p.nama
2 from vkursus v, table(v.pengajar) p;

----- ------------------------------ ------------------------------
X01 Oracle Budi
X01 Oracle Wati
X02 ORDB with Oracle Budi
X02 ORDB with Oracle Othie
X02 ORDB with Oracle Indah