PL/SQL Subprograms

8.4. Memahami PL/SQL Functions

Sebuah function adalah sebuah subprogram yang menghitung sebuah nilai. Functions dan procedures memiliki struktur sama, kecuali bahwa functions memiliki sebuah klausa RETURN. Kita menuliskan functions lokal dengan menggunakan sintaks:

[CREATE [OR REPLACE ] ]FUNCTION function_name [ ( parameter [ , parameter ]... ) ] RETURN
datatype
[ AUTHID { DEFINER | CURRENT_USER } ]
[ PARALLEL_ENABLE
[ { [CLUSTER parameter BY (column_name [, column_name ]... ) ] |
[ORDER parameter BY (column_name [ , column_name ]... ) ] } ]
[ ( PARTITION parameter BY
{ [ {RANGE | HASH } (column_name [, column_name]...)] | ANY }
) ]
]
[DETERMINISTIC] [ PIPELINED [ USING implementation_type ] ]
[ AGGREGATE [UPDATE VALUE] [WITH EXTERNAL CONTEXT]
USING implementation_type ] {IS | AS}
[ PRAGMA AUTONOMOUS_TRANSACTION; ]
[ local declarations ]
BEGIN
executable statements
[ EXCEPTION
exception handlers ]
END [ name ];

Klausa CREATE mengijinkan kita untuk menciptakan standalone functions, yang disimpan di dalam Oracle database. Kita dapat mengeksekusi perintah CREATE FUNCTION secara interaktif dari SQL*Plus atau dari sebuah program dengan menggunakan native dynamic SQL.

Klausa AUTHID menentukan apakah stored function dieksekusi dengan privileges dari pemiliknya (default) atau user terkini dan apakah referensi-referensi yang tidak memenuhi syarat terhadap schema objects ditetapkan di dalam schema dari pemiliknya atau user terkini. Kita dapat menimpa kelakuan default ini dengan menentukan CURRENT_USER.

Opsi PARALLEL_ENABLE mendeklarasikan bahwa sebuah stored function dapat digunakan secara aman di dalam slave sessions dari evaluasi-evaluasi parallel DML. Keadaan dari sebuah session utama (logon) adalah tidak pernah dibagi dengan slave sessions. Setiap slave session memiliki keadaannya sendiri, yang mana diinisialisasi ketika session tersebut dimulai. Hasil dari function sebaiknya tidak bergantung kepada keadaan dari variable-variable (statis)

Hint DETERMINISTIC membantu optimizer menghindari pemanggilan-pemanggilan function yang berlebihan. Jika stored function pernah dipanggil sebelumnya dengan argumen-argumen yang sama, optimizer dapat memilih untuk menggunakan hasil sebelumnya.

Hasil function sebaiknya tidak bergantung kepada keadaan dari variable-variable session atau schema objects. Selain daripada itu, hasil-hasil dapat berbeda diantara banyak pemanggilan. Hanya DETERMINISTIC functions yang dapat dipanggil dari sebuah function-based index atau materialized view yang memiliki query-rewrite yang di-enable-kan.

Pragma AUTONOMOUS_TRANSACTION menginstruksikan PL/SQL compiler untuk menandai sebuah function sebagai autonomous (independen). Autonomous transactions mengijinkan kita untuk menghentikan sementara transaksi utama, melakukan operasi-operasi SQL, melakukan commit atau rollback terhadap operasi-operasi tersebut, dan kemudian melanjutkan kembali transaksi utama tersebut.

Kita tidak dapat memaksakan aturan terhadap tipe data (dengan NOT NULL misalnya) dari sebuah parameter atau terhadap nilai yang dihasilkan oleh sebuah function.

Seperti sebuah procedure, sebuah function memiliki dua bagian: spec dan body. Spec dari function diawali dengan keyword FUNCTION dan diakhiri dengan klausa RETURN, yang menentukan tipe data dari nilai yang dihasilkan/dikembalikan oleh function. Deklarasi-deklarasi parameter adalah opsional. Functions yang tidak menerima parameter ditulis tanpa tanda kurung buka dan kurung tutup.

Body dari function diawali dengan keyword IS (atau AS) dan diakhiri dengan keyword END dan secara opsional diikuti oleh nama function. Body dari function memiliki tiga bagian: bagian declarative, bagian executable, dan bagian exception-handling (opsional).

Bagian declarative mengandung deklarasi-deklarasi lokal, yang ditempatkan diantara keywords IS dan BEGIN. Keyword DECLARE tidak digunakan. Bagian executable mengandung perintah-perintah, yang diletakkan diantara keywords BEGIN dan EXCEPTION (atau END). Satu atau lebih perintah-perintah RETURN harus ada di dalam bagian executable dari sebuah function. Bagian exception-handling mengandung exception handlers, yang diletakkan diantara keywords EXCEPTION dan END.

Mari kita perhatikan function sal_ok berikut ini, yang menentukan apakah sebuah gaji berada diluar jangkauan:

FUNCTION sal_ok (salary REAL, title VARCHAR2) RETURN BOOLEAN IS
  min_sal REAL;
  max_sal REAL;
BEGIN
  SELECT losal, hisal INTO min_sal, max_sal FROM sals
  WHERE job = title;
  RETURN (salary >= min_sal) AND (salary <= max_sal);
END sal_ok;

Ketika dipanggil, function ini menerima gaji dan jabatan karyawan. Ia menggunakan jabatan untuk menampilkan batas-batas jangkauan yang diambil dari database table sals.

Pengidentifikasi function, sal_ok, diatur menjadi sebuah nilai Boolean oleh perintah RETURN. Jika gaji berada diluar jangkauan, sal_ok diset menjadi FALSE; selain daripada itu, sal_ok diset menjadi TRUE.

Sebuah function dipanggil sebagai bagian dari sebuah ekspresi, seperti ditunjukkan oleh contoh berikut ini. Pengidentifikasi function sal_ok bertindak seperti sebuah variable yang nilainya bergantung kepada parameter yang dilewatkan kepadanya.

IF sal_ok(new_sal, new_title) THEN ...

8.4.1. Menggunakan Perintah RETURN

Perintah RETURN secara segera menyelesaikan eksekusi dari sebuah subprogram dan mengembalikan kontrol ke pemanggilnya. Eksekusi lalu berlanjut dengan perintah yang mengikuti pemanggilan subprogram. (Jangan keliru antara perintah RETURN dengan klausa RETURN di dalam spec dari sebuah function, yang menentukan tipe data dari nilai yang dihasilkan).

Sebuah subprogram dapat mengandung beberapa perintah-perintah RETURN. Perintah lexical terakhir tidak perlu menjadi sebuah perintah RETURN. Mengeksekusi perintah RETURN menyelesaikan subprogram secara segera. Namun, memiliki banyak titik keluar (exit points) di dalam sebuah subprogram merupakan praktek pemrograman yang buruk.

Dalam procedures, sebuah perintah RETURN tidak dapat mengembalikan sebuah nilai, dan sehingga tidak dapat mengandung sebuah ekspresi. Perintah tersebut secara sederhana mengembalikan kontrol kepada pemanggilnya sebelum akhir normal dari procedure dicapai.

Namun, dalam functions, sebuah perintah RETURN harus mengandung sebuah ekspresi, yang dievaluasi ketika perintah RETURN dieksekusi. Nilai yang dihasilkan diberikan kepada pengidentifikasi function, yang bertindak seperti sebuah variable dari tipe yang ditentukan di dalam klausa RETURN. Mari kita perhatikan bagaimana function balance menghasilkan saldo rekening bank yang ditentukan:

FUNCTION balance (acct_id INTEGER) RETURN REAL IS
acct_bal REAL;
BEGIN
  SELECT bal
  INTO acct_bal
  FROM accts
  WHERE acct_no = acct_id;
  RETURN acct_bal;
END balance;

Contoh berikut ini menunjukkan bagaiman ekspresi di dalam sebuah perintah RETURN dari function dapat berubah menjadi kompleks:

FUNCTION compound (
years NUMBER,
amount NUMBER,
rate NUMBER) RETURN NUMBER IS
BEGIN
  RETURN amount * POWER((rate / 100) + 1, years);
END compound;

Dalam sebuah function, harus ada paling tidak satu jalur eksekusi (execution path) yang berada di depan sebuah perintah RETURN. Sebaliknya, kita akan mendapatkan  a function returned without value error saat runtime.