Hastinapura

Oracle Database Resources

PL/SQL Subprograms

8.15.9. Mengoptimasi Multiple Calls terhadap Table Functions

Pemanggilan berkelipatan terhadap sebuah table function, baik dalam query yang sama maupun query yang berbeda menyebabkan eksekusi lebih dari satu terhadap implementasi yang mendasarinya. Secara default, tidak terdapat buffering atau penggunaan kembali terhadap baris-baris data:

Sebagai contoh,

SELECT * FROM TABLE(f(...)) t1, TABLE(f(...)) t2
WHERE t1.id = t2.id;
SELECT * FROM TABLE(f());
SELECT * FROM TABLE(f());

Namun, jika output dari sebuah table function ditentukan secara tunggal oleh nilai-nilai yang dilewatkan kepadanya sebagai argumen-argumen, seperti halnya bahwa function tersebut selalu menghasilkan nilai hasil yang sama persis untuk setiap masing-masing kombinasi dari nilai-nilai yang dilewatkan, kita dapat mendeklarasikan function DETERMINISTIC, dan Oracle secara otomatis melakukan buffering baris-baris data untuknya. Catatan, bahwa database tidak memiliki cara untuk mengetahui apakah sebuah function yang ditandai DETERMINISTIC adalah benar-benar DETERMINISTIC, dan jika tidak, hasil-hasilnya menjadi tidak dapat diduga.

8.15.9.1. Melakukan Fetch Hasil-hasil dari Table Functions

PL/SQL cursors dan ref cursors dapat didefinisikan untuk query-query melakui table functions. Sebagai contoh:

OPEN c FOR SELECT * FROM TABLE(f(...));

Cursor melalui table function memiliki bentuk fetch yang sama seperti cursor pada umumnya. Pemberian nilai REF CURSOR berdasarkan pada table functions tidak memiliki bentuk yang khusus.

Namun, SQL optimizer tidak teroptimasi melalui perintah-perintah PL/SQL. Sebagai contoh:

BEGIN
  OPEN r FOR SELECT * FROM TABLE(f(CURSOR(SELECT * FROM tab)));
  SELECT * BULK COLLECT INTO rec_tab FROM TABLE(g(r));
END;

tidak dieksekusi seperti halnya:

SELECT * FROM TABLE(g(CURSOR(SELECT * FROM
TABLE(f(CURSOR(SELECT * FROM tab))))));

Hal ini bahwan begitu mengabaikan overhead yang terkait dengan mengeksekusi dua perintah SQL dan mengasumsikan bahwa hasil-hasilnya dapat di-pipeline-kan diantara dua perintah-perintah tersebut.

8.15.10. Melewatkan Data dengan Cursor Variables

Kita dapat melewatkan sebuah kumpulan dari baris-baris data ke sebuah PL/SQL function di dalam sebuah parameter REF CURSOR. Sebagai contoh, function berikut ini dideklarasikan untuk menerima sebuah argumen dari predefined weakly typed REF CURSOR type, yaitu SYS_REFCURSOR:

FUNCTION f(p1 IN SYS_REFCURSOR) RETURN ... ;

Hasil-hasil dari sebuah subquery dapat dilewatkan ke sebuah function secara langsung:

SELECT * FROM TABLE(f(CURSOR(SELECT empno FROM tab)));

Dalam contoh diatas, keyword CURSOR dibutuhkan untuk mengindikasikan bahwa hasil-hasil dari sebuah subquery sebaiknya dilewatkan sebagai sebuah parameter REF CURSOR.

Sebuah predefined weak REF CURSOR type SYS_REFCURSOR juga didukung. Dengan SYS_REFCURSOR, kita tidak perlu menciptakan sebuah REF CURSOR type terlebih dahulu di dalam sebuah package sebelum kita dapat menggunakannya.

Untuk menggunakan sebuah strong REF CURSOR type, kita tetap harus menciptakan sebuah PL/SQL package dan mendeklarasikan sebuah strong REF CURSOR type di dalamnya. Juga, jika kita menggunakan sebuah strong REF CURSOR type sebagai sebuah argumen ke sebuah table function, maka type sesungguhnya dari argumen REF CURSOR harus sesuai dengan tipe kolom, atau akan muncul error. Argumen-argumen weak REF CURSOR ke table functions hanya dapat dibagi (partitioned) dengan menggunakan klausa PARTITION BY ANY. Kita tidak dapat menggunakan range atau hash partitioning untuk argumen-argumen weak dari REF CURSOR.

Contoh: Menggunakan Multiple REF CURSOR Input Variables

PL/SQL function dapat menerima multiple REF CURSOR input variables:

CREATE FUNCTION g(p1 pkg.refcur_t1, p2 pkg.refcur_t2) RETURN...
PIPELINED ... ;

Function g dapat dipanggil dengan cara sebagai berikut:

SELECT * FROM TABLE(g(CURSOR(SELECT empno FROM tab),
CURSOR(SELECT * FROM emp));

Kita dapat melewatkan hasil yang dikembalikan oleh table function ke table functions yang lain dengan menciptakan sebuah REF CURSOR yang berulang di atas data yang dihasilkan:

SELECT * FROM TABLE(f(CURSOR(SELECT * FROM TABLE(g(...)))));

Contoh: Secara Eksplisit Membuka Sebuah REF CURSOR untuk Sebuah Query

Kita dapat secara eksplisit membuka sebuah REF CURSOR untuk sebuah query dan melewatkannya sebagai sebuah parameter ke sebuah table function:

BEGIN
  OPEN r FOR SELECT * FROM TABLE(f(...));
  --Harus mengembalikan sebuah single row result set.
  SELECT * INTO rec FROM TABLE(g(r));
END;

Dalam kasus ini, table function menutup cursor ketika ia selesai, sehingga program kita sebaiknya tidak berusaha menutup cursor tersebut secara eksplisit.

Contoh: Menggunakan Sebuah Pipelined Table Functions Sebagai Sebuah Aggregate Function

Sebuah table function dapat menghitung hasil-hasil aggregate function menggunakan input ref cursor. Contoh berikut ini menghitung sebuah rata-rata berbobot dengan perulangan di atas sebuah kumpulan baris-baris input.

DROP TABLE gradereport;
CREATE TABLE gradereport (student VARCHAR2(30), subject
VARCHAR2(30), weight NUMBER, grade NUMBER);
INSERT INTO gradereport VALUES('Mark', 'Physics', 4, 4);
INSERT INTO gradereport VALUES('Mark','Chemistry', 4,3);
INSERT INTO gradereport VALUES('Mark','Maths', 3,3);
INSERT INTO gradereport VALUES('Mark','Economics', 3,4);
CREATE OR replace TYPE gpa AS TABLE OF NUMBER;
/

CREATE OR replace FUNCTION weighted_average(input_values
sys_refcursor)
RETURN gpa PIPELINED IS
grade NUMBER;
total NUMBER := 0;
total_weight NUMBER := 0;
weight NUMBER := 0;
BEGIN
  -- Function menerima sebuah ref cursor dan berulang sebanyak seluruh baris input.
  LOOP
    FETCH input_values INTO weight, grade;
    EXIT WHEN input_values%NOTFOUND;
    -- Mengakumulasi rata-rata berbobot.
    total_weight := total_weight + weight;
    total := total + grade*weight;
  END LOOP;
  PIPE ROW (total / total_weight);
  -- Function mengembalikan sebuah hasil tunggal.
  RETURN;
END;
/
show errors;

-- Hasil tersebut kembali sebagai sebuah nested table dengan sebuah baris tunggal.
-- COLUMN_VALUE adalah sebuah keyword yang mengembalikan isi dari nested table.
select weighted_result.column_value from
table(weighted_average(cursor(select weight,grade from
gradereport))) weighted_result;
COLUMN_VALUE
------------
3.5

 

 

Buku ini membahas berbagai macam tip dan trik yang sangat berguna bagi Anda pembaca pengguna database Oracle. Pemula, mahasiswa, programmer, maupun database administrator yang mengharapkan solusi cepat dalam menangani permasalahan-permasalahan dalam penggunaan database Oracle dapat membaca buku ini. Anda tidak harus membaca buku ini secara urut karena buku ini di susun berdasarkan topik permasalahan, dan Anda dapat membaca topik-topik yang sesuai dengan permasalahan yang Anda hadapi. Dapat di beli di toko buku Gramedia, Gunung Agung, Uranus, Karisma dsb. Harga Rp. 24.800,- (157 halaman)

 

• PL/SQL Packages
• PL/SQL Object Types
• Native Dynamic SQL
• Menangani PL/SQL Errors
• Interaksi PL/SQL dengan Oracle

 

Copyright © 2012 Hastinapura. Beranda | Berbagi Cerita | Hastinapura | Kelasik | Langgam | Nunung | Obsesi | Propolis | Serasi | Spektakuler

Switch to our mobile site