Resume PBD 2 Pert 10 & 11

Functions 

Tinjauan Stored Functions
• Fungsi adalah sebuah blok PL / SQL bernama yang mengembalikan nilai.
• Sebuah fungsi dapat disimpan dalam database sebagai objek skema untuk eksekusi diulang.
• Suatu fungsi disebut sebagai bagian dari suatu ekspresi

Sintaks untuk Membuat Functions

CREATE [OR REPLACE] FUNCTION function_name

[(parameter1 [mode1] datatype1,

parameter2 [mode2] datatype2,

. . .)]

RETURN datatype

IS|AS

PL/SQL Block;

Blok PL / SQL harus memiliki minimal satu pernyataan RETURN.

Membuat Stored Function dengan Menggunakan iSQL * Plus

1. Masukkan teks pernyataan CREATE FUNCTION dalam editor dan menyimpannya sebagai file script SQL.
2. Jalankan file script untuk menyimpan kode sumber dan mengkompilasi fungsi.
3. Gunakan SHOW ERRORS untuk melihat kesalahan kompilasi.
4. Ketika berhasil dikompilasi, memanggil FUNCTION.

Contoh

CREATE OR REPLACE FUNCTION get_sal

(p_id IN employees.employee_id%TYPE)

RETURN NUMBER

IS

v_salary employees.salary%TYPE :=0;

BEGIN

SELECT salary

INTO v_salary

FROM employees

WHERE employee_id = p_id;

RETURN v_salary;

END get_sal;

/

get_salary.sql

Eksekusi Functions

• Memanggil function sebagai bagian dari ekspresi PL / SQL.
• Buat sebuah variabel untuk menampung nilai yang dikembalikan.
• Jalankan function. Variabel akan diisi dengan nilai yang dikembalikan melalui RETURN
pernyataan

Contoh

1. Memuat dan menjalankan file get_salary.sql untuk membuat function

VARIABLE g_salary NUMBER

EXECUTE :g_salary := get_sal(117)

PRINT g_salary

Keuntungan dari User-Defined Functions di SQL Expressions

• Perluas SQL di mana kegiatan yang terlalu rumit, terlalu canggung, atau tidak tersedia dengan SQL
• Dapat meningkatkan efisiensi ketika digunakan dalam klausa WHERE untuk menyaring data, yang bertentangan dengan penyaringan data dalam aplikasi
• Dapat memanipulasi karakter string

Menjalankan Functions di SQL Expressions: Contoh

CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER)

RETURN NUMBER IS

BEGIN

RETURN (p_value * 0.08);

END tax;

/

SELECT employee_id, last_name, salary, tax(salary)

FROM employees

WHERE department_id = 100;

Lokasi ke Call User-Defined Functions

• Select list of a SELECT command

• Condition of the WHERE and HAVING clauses

• CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses

• VALUES clause of the INSERT command

• SET clause of the UPDATE command

Pembatasan panggilan dari Function SQL Expressions

Untuk callable dari ekspresi SQL, fungsi user-defined harus:

• Jadi stored function

• Menerima hanya IN parameters

• Menerima hanya valid SQL data types, bukan PL / SQL jenis tertentu, sebagai parameter

• Kembalikan tipe data yang sesuai tipe data SQL, bukan PL / SQL jenis tertentu

• Fungsi dipanggil dari ekspresi SQL tidak dapat berisi pernyataan-pernyataan DML.
• Fungsi dipanggil dari UPDATE / DELETE laporan pada T tabel tidak dapat berisi DML yang sama dengan table T.
• Fungsi dipanggil dari sebuah UPDATE atau DELETE pada pernyataan tabel T tidak bisa query dari tabel yang sama.
• Fungsi dipanggil dari pernyataan SQL tidak dapat berisi pernyataan-pernyataan yang akhir dari transaksi.
• Panggilan ke subprogram yang melanggar pembatasan sebelumnya tidak diperbolehkan dalam fungsi.

Pembatasan Memanggil dari SQL

CREATE OR REPLACE FUNCTION dml_call_sql (p_sal NUMBER)

RETURN NUMBER IS

BEGIN

INSERT INTO employees(employee_id, last_name, email,

hire_date, job_id, salary)

VALUES(1, 'employee 1', 'emp1@company.com',

SYSDATE, 'SA_MAN', 1000);

RETURN (p_sal + 100);

END;

/

UPDATE employees SET salary = dml_call_sql(2000)

WHERE employee_id = 170;

Hapus Functions

Syntax:

DROP FUNCTION function_name

Contoh:

DROP FUNCTION get_sal;

• Semua hak akses yang diberikan pada fungsi dicabut saat fungsi dihapus.
• Sintaks CREATE OR REPLACE adalah ekivalen dengan menghapus fungsi dan menciptakan kembali. Hak istimewa yang diberikan pada fungsi tetap sama ketika sintaks ini digunakan.

Perbandingan Procedures dan Functions

Procedures	Functions
Bisa dijalankan sesuai PL/SQL statement	Memanggil sebagai bagian dari ekpresi
Tidak mengandung klausa RETURN di header	Harus mengandung klausa RETURN di header
Dapat mengembalikan tidak ada,satu atau banyak nilai	Pasti mengembalikan satu nilai
Dapat mengandung pernyataan RETURN	Pasti paling sedikit mengembalikan satu RETURN

Manfaat Stored Prosedur dan Fungsi
• Peningkatan kinerja
• Mudah pemeliharaan
• Peningkatan keamanan data dan integritas
• Peningkatan kejelasan kode