PL/SQL Subprograms

8.15. Menerima dan Mengembalikan Banyak Baris dengan Table Functions

Bagian ini mempelajari tentang table functions. Juga menjelaskan tentang tipe-tipe data generik ANYTYPE, ANYDATA, dan ANYDATASET, yang kemungkinan besar akan digunakan bersama dengan table functions.

Topik-topik utama yang akan kita bahas adalah:

• Pengenalan Table Functions
• Menulis Sebuah Pipelined Table Functions
• Parallelizing Table Functions
• Bagaimana Table Functions Mengalirkan Input Data Mereka

8.15.1. Pengenalan Table Functions

Table functions adalah functions yang menghasilkan sebuah kumpulan baris data (baik sebuah nested table atau sebuah varray) yang dapat di-query seperti halnya database table atau diberikan kepada sebuah collection variable PL/SQL. Kita dapat menggunakan sebuah table function seperti nama dari sebuah database table, di dalam klausa FROM dari sebuah query, atau seperti sebuah kolom di dalam daftar SELECT dari sebuah query.

Sebuah table function dapat menerima sebuah kumpulan baris data sebagai input. Parameter pengumpul input dapat berupa sebuah collection type (seperti halnya sebuah VARRAY atau sebuah PL/SQL table) atau sebuah REF CURSOR.

Streaming, pipelining, dan parallel execution dari table functions dapat meningkatkan performa:

• Dengan meng-enable-kan multi-threaded, concurrent execution dari table functions
• Dengan mengurangi intermediate staging diantara proses
• Dengan meningkatkan waktu respon dari query: Dengan non-pipelined table functions, seluruh kumpulkan yang dihasilkan oleh sebuah table function harus dibangun dan dikembalikan ke server sebelum query dapat menghasilkan hasil baris data tunggal. Pipelining memungkinkan baris-baris data dihasilkan secara berulang, bersama ketika mereka dihasilkan. Hal ini juga mengurangi memory yang dibutuhkan oleh sebuah table function, karena object cache tidak diperlukan untuk mewujudkan seluruh kumpulan (collection).
• Dengan secara iteratif menyediakan baris-baris data hasil dari collection yang dihasilkan oleh sebuah table function bersama dengan ketika baris-baris data dihasilkan dibandingkan dengan menunggu hingga seluruh collection berada di tables atau memory dan kemudian mengembalikan seluruh collection.

Gambar 8-2 menunjukkan skenario pemrosesan data yang biasa dilakukan dimana data berjalan melalui beberapa (dalam kasus ini, tiga) transformasi, diimplementasikan oleh table functions, sebelum pada akhirnya di-load ke dalam database. Dalam skenario ini, table functions tidak terparalel, dan seluruh hasil collection harus dikumpulkan dengan menggunakan sebuah table sementara setelah setiap transformasi.

Gambar 8-2 Pemrosesan Data Khas dengan Unparallelized, Unpipelined Table Functions

Secara kontras, Gambar 8-3 di bawah ini, menunjukkan bagaimana streaming dan parallel execution dapat mempersingkat skenario yang sama.

Gambar 8-3 Pemrosesan Data Menggunakan Pipelining dan Parallel Execution

8.15.2. Apa itu Table Functions?

Table functions menghasilkan kumpulan type instance yang merepresentasikan baris-baris data di dalam sebuah table. Mereka dapat di-query seperti halnya sebuah table dengan memanggil function tersebut di dalam klausa FROM pada sebuah query, diapit dengan keyword TABLE. Mereka dapat diberikan ke sebuah PL/SQL collection variable dengan memanggil function tersebut di dalam daftar SELECT pada sebuah query.

Contoh: Melakukan Query Terhadap Sebuah Table Function

Contoh berikut ini menunjukkan sebuah table function Get_Books yang menerima CLOB sebagai input dan menghasilkan sebuah instance dari collection type BookSet_t. Kolom CLOB menyimpan sebuah daftar katalog dari buku-buku di dalam beberapa format (baik proprietary ataupun mengikuti sebuah standard seperti sebagai XML). Table function menghasilkan seluruh katalog-katalog dan daftar-daftar bukunya yang terkait.

Collection type BookSet_t didefinisikan sebagai:

CREATE TYPE Book_t AS OBJECT
( name VARCHAR2(100), author VARCHAR2(30), abstract VARCHAR2(1000));
CREATE TYPE BookSet_t AS TABLE OF Book_t;

CLOBs disimpan di dalam sebuah table Catalogs:

CREATE TABLE Catalogs( name VARCHAR2(30), cat CLOB);

Function GetBooks didefinisikan sebagai berikut:

CREATE FUNCTION GetBooks(a CLOB) RETURN BookSet_t;

Query di bawah ini menghasilkan seluruh katalog-katalog dan daftar-daftar bukunya.

SELECT c.name, Book.name, Book.author, Book.abstract
FROM Catalogs c, TABLE(GetBooks(c.cat)) Book;

Contoh: Memberikan Hasil dari Sebuah Table Function

Contoh berikut ini menunjukkan bagaimana kita memberikan hasil dari sebuah table function kepada sebuah PL/SQL collection variable. Karena table function dipanggil dari daftar SELECT dari query, kita tidak memerlukan keyword TABLE.

create type numset_t as table of number;
/
create function f1(x number) return numset_t pipelined is
begin
  for i in 1..x loop
    pipe row(i);
  end loop;
  return;
end;
/

-- pipelined function dalam klausa from
select * from table(f1(3));
COLUMN_VALUE
------------
1
2
3

3 rows selected.

-- pipelined function dalam daftar select
select f1(3) from dual;
F1(3)
---------------------------------
NUMSET_T(1, 2, 3)

-- Karena function menghasilkan sebuah collection, kita dapat memberikan
-- hasilnya ke sebuah PL/SQL variable.

declare func_result numset_t;
begin
  select f1(3) into func_result from dual;
end;
/