PL/SQL Subprograms

8.18.10.1. Memanggil Invoker-Rights Instance Methods

Sebuah invoker-rights subprogram dijalankan dengan privileges dari invoker-nya, bukan creator dari instance tersebut. Misalkan bahwa Person adalah sebuah invoker-rights object type, dan bahwa user scot menciptakan p1, dan object dengan tipe Person. Jika user blake menjalankan instance method change_job untuk beroperasi pada object p1, current user dari method tersebut adalah blake, bukan scott. Mari kita perhatikan contoh berikut ini:

-- user blake menciptakan a definer-rights procedure
CREATE PROCEDURE reassign (p Person, new_job VARCHAR2) AS
BEGIN
-- user blake memanggil method change_job, sehingga
-- method tersebut dijalankan dengan privileges dari blake
p.change_job(new_job);
...
END;
-- user scott melewatkan sebuah object Person ke procedure tersebut
DECLARE
p1 Person;
BEGIN
p1 := Person(...);
blake.reassign(p1, 'CLERK');
...
END;

8.19. Memahami dan Menggunakan Rekursi

Rekursi merupakan sebuah teknik yang sangat powerful untuk menyederhanakan desain dari algoritma. Secara sederhana, recursion (rekursi) berarti self-reference (referensi kepada diri sendiri). Di dalam sebuah urutan rekursi matematika, setiap istilah dihasilkan dengan menerapkan sebuah formula kepada istilah terdahulu. Suatu urutan Fibonacci (0, 1, 1, 2, 3, 5, 8, 13, 21, …), yang mana pertama kali digunakan untuk memodelkan pertumbuhan koloni kelinci, merupakan salah satu contoh. Setiap istilah di dalam urutan (setelah yang kedua) merupakan jumlah dari dua istilah yang mendahuluinya.

Dalam sebuah definisi rekursi, sesuatu didefinisikan sebagai versi-versi yang lebih sederhana dari dirinya sendiri. Mari kita perhatikan definisi dari n factorial (n!), hasil dari seluruh inteter dari 1 sampai n:

n! = n * (n - 1)!

8.19.1. Apa Itu Recursive Subprogram?

Sebuah recursive subprogram adalah subprogram yang memanggil dirinya sendiri. Pikirkan sebuah pemanggilan rekursi sebagai sebuah pemanggilan kepada subprogram lain yang tidak memiliki tugas yang sama dengan subprogram kita. Setiap pemanggilan rekursi menciptakan sebuah instance baru dari item-item yang dideklarasikan di dalam subprogram tersebut, termasuk parameter-parameter, variable-variable, cursor-cursor, dan exception-exception. Demikian juga, instance-instance baru dari perintah-perintah SQL diciptakan pada setiap level di dalam turunan dari rekursi tersebut.

Kita harus berhati-hati ketika menempatkan sebuah pemanggilan rekursi. Jika kita meletakkannya di dalam sebuah cursor FOR loop atau di antara perintah-perintah OPEN dan CLOSE, cursor lainnya dibuka pada setiap pemanggilan. Sebagai hasilnya, program kita mungkin melampaui batasan yang diatur oleh Oracle initialization parameter OPEN_CURSORS.

Harus ada paling tidak dua jalur yang melalui sebuah recursive subprogram: satu yang menuju ke pemanggilan rekursi tersebut dan satu yang tidak. Paling tidak satu jalur harus menuju ke sebuah terminating condition (kondisi penghentian). Jika tidak, maka rekursi tersebut (secara teori) akan berjalan selamanya. Dalam prakteknya, jika sebuah recursive subprogram menyimpang ke regresi tak terbatas, PL/SQL akhirnya berjalan melebihi kapasitas memory dan menyebabkan munculnya predefined exception STORAGE_ERROR.

Contoh Rekursi: Menghitung Factorial

Untuk memecahkan beberapa masalah-masalah pemrograman, kita harus mengulang sebuah urutan perintah-perintah sampai sebuah kondisi terpenuhi. Kita dapat menggunakan iterasi atau rekursi untuk memecahkan problem-problem seperti ini.

Gunakan rekursi ketika problem tersebut dapat dipecah menjadi versi-versi yang lebih sederhana dari dirinya sendiri. Sebagai contoh, kita dapat mengevaluasi 3! seperti berikut ini:

0! = 1 -- berdasarkan definisi
1! = 1 * 0! = 1
2! = 2 * 1! = 2
3! = 3 * 2! = 6

Untuk mengimplementasikan algoritma ini, kita dapat menulis function rekursi berikut ini, yang menghasilkan factorial dari sebuah integer positif:

FUNCTION fac (n POSITIVE) RETURN INTEGER IS -- menghasilkan n!
BEGIN
  IF n = 1 THEN -- kondisi penghentian
    RETURN 1;
  ELSE
    RETURN n * fac(n - 1); -- pemanggilan rekursif
  END IF;
END fac;

Pada setiap pemanggilan rekursi, n dikurangi. Pada akhirnya, n menjadi 1 dan rekursi berhenti.

Contoh Rekursi: Melintasi Data Berstruktur Pohon (Tree)

Mari kita perhatikan procedure berikut ini, yang bertugas menemukan staff dari satu  manager yang diberikan. Procedure tersebut mendeklarasikan dua parameter formal, mgr_no dan tier, yang merepresentasikan nomor pegawai dari manager tersebut dan sebuah tingkatan di dalam departemennya. Staff yang secara langsung melaporkan hasil kerja kepada manager tersebut menempati tingkat pertama.

PROCEDURE find_staff (mgr_no NUMBER, tier NUMBER := 1) IS
boss_name VARCHAR2(10);
CURSOR c1 (boss_no NUMBER) IS
SELECT empno, ename FROM emp WHERE mgr = boss_no;
BEGIN
  /* Mendapatkan nama manager. */
  SELECT ename INTO boss_name FROM emp WHERE empno = mgr_no;
  IF tier = 1 THEN
    INSERT INTO staff -- single-column output table
    VALUES (boss_name || ' manages the staff');
  END IF;
  /* Mencari anggota staff yang melapor langsung ke manager tersebut. */
  FOR ee IN c1 (mgr_no)
  LOOP
    INSERT INTO staff
    VALUES (boss_name || ' manages ' || ee.ename
    || ' on tier ' || TO_CHAR(tier));
    /* Menghapus tingkatan berikutnya dalam organisasi. */
    find_staff(ee.empno, tier + 1); -- pemanggilan rekursif
  END LOOP;
  COMMIT;
END;

Ketika dipanggil, procedure tersebut menerima sebuah nilai untuk mgr_no namun menggunakan nilai default untuk tier-nya (tingkatannya). Sebagai contoh, kita dapat memanggil procedure tersebut dengan cara sebagai berikut:

find_staff(7839);

Procedure tersebut melewatkan mgr_no ke sebuah cursor di dalam sebuah cursor FOR loop, yang mana akan menemukan anggota staff yang menempati tingkatan-tingkatan yang lebih rendah di dalam organisasi tersebut. Pada setiap pemanggilan rekursi, sebuah instance baru dari FOR loop tersebut diciptakan dan cursor lainnya dibuka, namun cursor sebelumnya tetap diposisikan pada baris berikutnya di dalam result sets-nya.

Ketika sebuah fetch gagal menghasilkan baris data, cursor tersebut ditutup secara otomatis dan FOR loop dikeluarkan. Karena pemanggilan rekursi berada di dalam FOR loop, maka rekursi dihentikan. Tidak seperti pemanggilan awal, setiap pemanggilan rekursi melewatkan sebuah parameter aktual kedua (tingkatan berikutnya) kepada procedure tersebut.

Tip: Melakukan Query-query Rekursi dengan Klausa CONNECT BY

Contoh terakhir menggambarkan rekursi, bukan penggunaan efisien dari perintah-perintah set-oriented SQL. Kita mungkin ingin membandingkan performa dari procedure rekursi tersebut dengan perintah SQL berikut ini, yang melakukan tugas yang sama:

INSERT INTO staff
SELECT PRIOR ename || ' manages ' || ename
|| ' on tier ' || TO_CHAR(LEVEL - 1)
FROM emp
START WITH empno = 7839
CONNECT BY PRIOR empno = mgr;

Perintah SQL tersebut ternyata lebih cepat. Namun, procedure lebih fleksibel. Sebagai contoh, sebuah multi-table query tidak dapat mengandung klausa CONNECT BY. Sehingga, tidak seperti procedure tersebut, perintah SQL tidak dapat dimodifikasi untuk melakukan join. (Sebuah join mengkombinasikan baris-baris data dari dua atau lebih database tables). Sebagai tambahan, sebuah procedure dapat memproses data dengan cara yang tidak bisa dilakukan oleh perintah SQL tunggal.