Tutorial SQL : Stored Function dengan Parameter pada Oracle PL/SQL

Stored  Function  dengan  parameter  merupakan  sebuah block PL/SQL yang  dapat menerima  sebuah inputan parameter, menjalankan sebuah program tertentu yang kemudian akan mengembalikan hasil atau nilai tertentu. Stored function disimpan secara permanen di dalam database. Struktur dari procedure dan function dengan parameter hampir sama, akan tetapi setiap function harus mengembalikan nilai atau hasil (paling tidak mempunyai sebuah pernyataan RETURN) pada block yang memanggil function tersebut. Sintaks untuk membuat function dengan parameter sebagai berikut:

CREATE OR REPLACE FUNCTION name 
[(parameter1 [mode1] datatype1 
 (parameter2 [mode2] datatype2, ...)] 
RETURN datatype 
IS|AS 
   [local declarations] 
BEGIN 
   executable statements
RETURN expressions; 
   [EXCEPTION exception handlers]
END [name];

Tipe-tipe mode yang ada pada Stored Function yaitu IN, OUT, dan IN OUT. Meskipun mode OUT dan IN OUT dapat digunakan pada saat pembuatan sebuah function akan tetapi hal tersebut bukanlah sebuah cara yang tepat karena pada function bisa menggunakan RETURN untuk mengembalikan nilai. Mode IN adalah sebuah default pada saat pembuatan sebuah function.

Cara mengakses Stored Function pada ekspresi SQL dapat diletakkan pada :

1. Pernyataan SELECT atau pada klausa dari sebuah query.
2. Pernyataan kondisi WHERE atau HAVING.
3. Klausa query yaitu pada CONNECT BY, START WITH, ORDER BY, dan GROUP BY.
4. Pernyataan INSERT pada saat mengisi VALUES
5. Pernyataan UPDATE pada saat mengisi SET.

Contoh Stored Function 1 :

set serveroutput on
CREATE OR REPLACE FUNCTION LINGKARAN (jari number)
RETURN NUMBER IS
BEGIN
return((22/7*jari*jari)+(2*22/7*jari));
END LINGKARAN;
/

Variable jari2 number;
Declare
   temp number;
Begin
   :jari2 := 7;
   temp := LINGKARAN(:jari2);
DBMS_OUTPUT.PUT_LINE('Lingkaran dengan jari jari '|| :jari2
||' memliki jumlah luas dan keliling sebesar '|| temp);
End;
/

Contoh Stored Function 2 :

set serveroutput on
CREATE OR REPLACE FUNCTION RATA2_GAJI_JABATAN (id
   employees.employee_id%type)
RETURN NUMBER IS
   temp NUMBER;
BEGIN
   SELECT (MIN_SALARY+MAX_SALARY)/2 into temp from employees
   a, jobs b where a.job_id=b.job_id and a.employee_id = id;
RETURN temp;
END RATA2_GAJI_JABATAN;
/

Referensi:

1. Feuerstein, Steven, and Bill Pribyl. Oracle PL/SQL Programming, 5th Edition. Sebastopol : O’Reilly Media, 2009.
2. Srivastava, Tulika, and Glenn Stokol. Oracle Database 10g: Develop PL/SQL Program Units 2nd Edition. Boston: Oracle Publisher, 2006.
3. Urman, Scott, Ron Hardman, and Michael Laughlin. Oracle Database 10g PL/SQL Programming. Boston: McGraw‐Hill, 2004.
4. Rosenzweig, Benjamin and Elena Silvestrova Rakhimov. Oracle PL/SQL by Example, 4th Edition. Boston:Addison-Wesley.

By : Ade Handiyanto – Laboratory Assistant of Computer