Projo Kusumo (Kembang Jelata): Oracle Procedure/Function Update dan Order (Urutan)

Aduh mak!!. Saya punya aplikasi dimana aplikasi tersebut harus mengurutkan ketika kita memilih ordering data, gimana ya menjelaskannya. Yaitu mengurutkan data dari urutan record data yang sudah ada, sehingga terbentuklah urutan yang baru, sesuai yang diinginkan. Ada yang paham kata-kata ini gak ya? #palu, okelah para permirsa. Contohnya, saya punya table buah, bagi Anda yang belum terlalu paham cara membuat table, bisa disimak tutorial dari w3shools.com ini. Dan kurang lebih untuk table buah saya yang dibuat di Oracle, dapat disimak strukturnya

TABLE BUAH
ID                      VARCHAR(2)
NAMA                    VARCHAR2(20)
PARENT                  VARCHAR(2)
URUTAN                  NUMBER
PRIMARY KEY (ID) 

INDEX CONSTRAINT (PARENT, URUTAN)

dan setelah saya insert beberapa data, kurang lebihnya menjadi seperti ini

ID	NAMA	PARENT	URUTAN
1	Pohon Berbuah	0	1
2	Mangga	1	1
3	Pisang	1	2
4	Jambu	1	3
5	Sirsat	1	4
6	Pohon Merambat	0	2
7	Markisa	2	1
8	Anggur	2	2

Nah, untuk data parent dan urutan sudah saya constraint sebagai primary key, sehingga mau tidak mau jika saya mengupdate mangga menjadi urutan ke 3, dengan parent 1 yaitu pohon berbuah, maka yang perlu dilakukan adalah pisang dan jambu bergeser ke bawah dan mangga menempati urutan ke 3. Dan datanya menjadi

ID	NAMA	PARENT	URUTAN
1	Pohon Berbuah	0	1
3	Pisang	1	1
4	Jambu	1	2
2	Mangga	1	3
5	Sirsat	1	4
6	Pohon Merambat	0	2
7	Markisa	2	1
8	Anggur	2	2

Setelah browsing kesana-kemari yang belum membuahkan hasil, akhirnya saya membuat procedure/function oracle sendiri. kurang lebih scriptnya seperti ini

CREATE FUNCTION URUT_BUAH(P_PARENT VARCHAR, P_IDBUAH VARCHAR, P_NAMABUAH VARHCAR2, P_URUTAN NUMBER) RETURN VARCHAR2
IS 

 V_JUMLAH NUMBER; 

 V_URUT NUMBER; 

 V_MAXURUT NUMBER; 

BEGIN

 -- untuk mengambil jumlah record, dan urutan semula
 SELECT COUNT(1), URUTAN INTO V_JUMLAH V_URUT FROM BUAH WHERE ID = P_IDBUAH;

 -- untuk mengambil urutan terakhir ditambah satu, jika ingin insert yang baru.

 SELECT NVL(MAX(URUTAN), 0)+1 INTO V_MAXURUT FROM BUAH WHERE PARENT = P_PARENT;

-- jika idbuah ada, dan ingin diurutkan sesuai urutan maka

IF V_JUMLAH = 1 THEN

 -- jika urutan lebih besar dari urutan semula 

 IF P_URUTAN > V_URUT THEN

    UPDATE BUAH SET URUTAN = 0 WHERE ID = P_IDBUAH;

    FOR I IN V_URUT .. P_URUTAN-1 LOOP

     UPDATE BUAH SET URUTAN = I WHERE URUTAN = (I+1) AND PARENT = P_PARENT;

    END LOOP;

    UPDATE BUAH SET URUTAN = P_URUTAN WHERE ID = P_IDBUAH;

 -- jika urutan lebih kecil dari urutan semula

 ELSIF P_URUTAN < V_URUT THEN

    UPDATE BUAH SET URUTAN = 0 WHERE ID = P_IDBUAH;

    FOR I IN REVERSE P_URUTAN+1 .. V_URUT LOOP

      UPDATE BUAH SET URUTAN = I WHERE URUTAN = (I-1) AND PARENT = P_PARENT;

    END LOOP;

    UPDATE BUAH SET URUTAN = P_URUTAN WHERE ID = P_IDBUAH;

 END IF;

 RETURN 'DATA TELAH DIURUTKAN';

-- jika idbuah belum pernah ada, maka tambahkan record

ELSIF V_JUMLAH = 0 THEN

 INSERT INTO BUAH (ID, NAMA, PARENT, URUTAN) VALUES (P_IDBUAH, P_NAMABUAH, P_PARENT, V_MAXURUT); 

 RETURN 'DATA TELAH TERINSERT'; 

ELSE

 RETURN 'ADA KESALAHAN BRO!'; 

END IF;

END URUT_BUAH;

Kalau kurang jelas dapat ditanyakan ya!.
nah, bagaimana jika saya ingin mengubah parent juga Pak? kita simak dan tunggu tutorial selanjutnya