Developer to developer

sys_connect_by_path (scbp) alternative in oracle 8.17

Karena sama sekali belum pernah nulis prosedur di Oracle, biar cepet (ngeles_mode=on), pimpro saya memberi solusi fungsi alternatif pengganti scbp ini. FYI, scbp hanya bisa jalan di Ora 9.2 keatas. Fungsi scbp ini sih gunanya menurut Mastering Oracle SQL, “You can list the entire path of a given node starting from the root node using the SYS_CONNECT_BY_PATH function (in Oracle9i and later). This function takes two arguments: a column name and a character string. The function then returns a list containing each value of the column from the root node to the current node, separating values by the character string you provide.”.

Saya mencoba scbp di user klasik SCOTT.EMP, yang mempunyai self-referential integrity constraint dengan query :

SELECT LEVEL, LPAD(‘ ‘,2*(LEVEL – 1)) || ename “EMPLOYEE”,
empno, mgr, sys_connect_by_path(ename, ‘/’) child
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY job;

Result :

q1

Terlihat hirarki dari tabel EMP beserta levelnya. Kemudian fungsi alternatif scbp saya beri nama get_child sebagai berikut :

CREATE OR REPLACE FUNCTION get_child(p_emp_no varchar2,p_separator varchar2)
RETURN VARCHAR2
IS
l_text VARCHAR2(32767) := ”;
cursor cur_rec is select emp.*, level from emp
connect by prior mgr=empno
start with empno = p_emp_no
order by level desc;
BEGIN
FOR pcur_rec IN cur_rec LOOP
l_text := l_text || p_separator || pcur_rec.ename;
END LOOP;
RETURN(ltrim(l_text));
END;
/
SHOW ERRORS

Variabel yang dibutuhkan fungsi ini adalah kolom EMPNO dan separator yang diinginkan. Oh ya, kelemahannya fungsi ini hanya bisa digunakan per table. Setelah membuat fungsi kemudian jalankan query ini :

SELECT LEVEL, LPAD(‘ ‘,2*(LEVEL – 1)) || ename “EMPLOYEE”,
empno, mgr, get_child(to_char(empno), ‘/’) child
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY job;

Query ini sama seperti query sebelumnya. Hanya, fungsi scbp diganti fungsi get_child dengan mengisi variabel ’emp_no’ untuk nama kolomnya dan separator ‘/’. Hasil yang didapat sama dengan scbp.

Tags:

Standard

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