Oracle Prosedur & Fungsi Tersimpan PL/SQL beserta Contohnya

Apa itu subprogram PL/SQL?

Dalam tutorial ini, Anda akan melihat penjelasan rinci tentang cara membuat dan menjalankan blok, prosedur, dan fungsi bernama.

Prosedur dan fungsi adalah subprogram yang dapat dibuat dan disimpan dalam basis data sebagai objek basis data. Subprogram dan prosedur juga dapat dipanggil atau dirujuk di dalam blok lain.

Kami juga membahas perbedaan utama antara kedua subprogram ini dan mendiskusikannya. Oracle fungsi bawaan.

Terminologi dalam Subprogram PL/SQL

Sebelum mempelajari subprogram PL/SQL, kita akan membahas berbagai terminologi yang menjadi bagian dari subprogram tersebut.

Parameter

Parameter adalah variabel atau tempat penampung dari nilai valid apa pun. Tipe data PL/SQL Melalui parameter ini, subprogram PL/SQL bertukar nilai dengan kode utama. Parameter ini memungkinkan input ke subprogram dan output.tracpengambilan nilai dari mereka.

• Parameter ini harus ditentukan bersama dengan subprogram pada saat pembuatan.
• Parameter-parameter tersebut disertakan dalam pernyataan pemanggilan untuk berinteraksi dengan subprogram.
• Tipe data parameter dalam subprogram dan pernyataan pemanggilan harus sama.
• Ukuran tipe data tidak perlu disebutkan pada saat deklarasi parameter, karena ukurannya bersifat dinamis.

Berdasarkan tujuannya, parameter diklasifikasikan sebagai:

1. DALAM Parameter
2. Parameter KELUAR
3. Parameter DALAM KELUAR

DALAM Parameter

• Digunakan untuk memberikan masukan kepada subprogram.
• Variabel ini bersifat hanya baca di dalam subprogram; nilainya tidak dapat diubah di dalam subprogram.
• Dalam pernyataan pemanggilan, itu bisa berupa variabel, nilai literal, atau ekspresi, seperti '5*8' atau 'a/b'.
• Secara default, parameter bertipe IN.

Parameter KELUAR

• Digunakan untuk mendapatkan output dari subprogram.
• Ini adalah variabel baca-tulis di dalam subprogram; nilainya dapat diubah di dalam subprogram tersebut.
• Dalam pernyataan pemanggilan, harus selalu berupa variabel untuk menyimpan nilai dari subprogram.

Parameter DALAM KELUAR

• Digunakan baik untuk memberikan input maupun mendapatkan output dari subprogram.
• Ini adalah variabel baca-tulis di dalam subprogram; nilainya dapat diubah di dalam subprogram tersebut.
• Dalam pernyataan pemanggilan, harus selalu berupa variabel untuk menyimpan nilai dari subprogram.

Tipe parameter harus disebutkan pada saat pembuatan subprogram.

KEMBALI

RETURN adalah kata kunci yang menginstruksikan kompiler untuk mengalihkan kendali dari subprogram ke pernyataan pemanggil. Dalam subprogram, RETURN berarti kendali perlu keluar dari subprogram; begitu pengontrol menemukan RETURN, kode setelahnya akan dilewati.

Biasanya, blok induk atau utama memanggil subprogram, dan kendali berpindah dari blok induk ke subprogram yang dipanggil. Pernyataan RETURN dalam subprogram mengembalikan kendali kembali ke blok induk. Dalam kasus fungsi, pernyataan RETURN juga mengembalikan nilai, yang tipe datanya disebutkan pada saat deklarasi fungsi.

Apa itu Prosedur dalam PL/SQL?

A Prosedur Dalam PL/SQL, prosedur adalah unit subprogram yang terdiri dari sekelompok pernyataan PL/SQL yang dapat dipanggil berdasarkan namanya. Setiap prosedur memiliki nama uniknya sendiri dan disimpan dalam... Oracle basis data sebagai objek basis data.

Catatan: Subprogram hanyalah sebuah prosedur, dan perlu dibuat secara manual sesuai kebutuhan. Setelah dibuat, subprogram akan disimpan sebagai objek basis data.

Karakteristik unit subprogram prosedur dalam PL/SQL adalah:

• Prosedur adalah blok mandiri yang dapat disimpan di dalam Database.
• Mereka dapat dipanggil berdasarkan namanya untuk mengeksekusi pernyataan PL/SQL.
• Fungsinya terutama untuk menjalankan suatu proses.
• Blok-blok tersebut dapat memiliki blok bersarang, atau bersarang di dalam blok atau paket lain.
• File tersebut berisi bagian deklarasi (opsional), bagian eksekusi, dan bagian penanganan pengecualian (opsional).
• Nilai dapat diteruskan ke atau diambil dari suatu prosedur melalui parameter.
• Parameter ini harus disertakan dalam pernyataan pemanggil.
• Suatu prosedur dapat memiliki pernyataan RETURN untuk mengembalikan kendali ke blok pemanggil, tetapi prosedur tersebut tidak dapat mengembalikan nilai apa pun melalui RETURN.
• Prosedur tidak dapat dipanggil langsung dari pernyataan SELECT; prosedur dapat dipanggil dari blok lain atau melalui kata kunci EXEC.

Sintaksis

CREATE OR REPLACE PROCEDURE
<procedure_name>
(
<parameter1 IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• Perintah CREATE PROCEDURE menginstruksikan kompiler untuk membuat prosedur baru. Kata kunci 'OR REPLACE' menginstruksikan kompiler untuk mengganti prosedur yang ada (jika ada) dengan prosedur yang ada saat ini.
• Nama prosedur harus unik.
• Kata kunci 'IS' digunakan ketika stored procedure bersarang di dalam blok lain. Jika prosedur tersebut berdiri sendiri, 'AS' digunakan. Terlepas dari standar pengkodean ini, keduanya memiliki arti yang sama.

Contoh 1: Membuat Prosedur dan memanggilnya menggunakan EXEC. Dalam contoh ini, kita membuat sebuah Oracle Prosedur yang menerima nama sebagai input dan mencetak pesan selamat datang sebagai output, menggunakan perintah EXEC untuk memanggilnya.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)
IS
BEGIN
dbms_output.put_line ('Welcome '|| p_name);
END;
/
EXEC welcome_msg ('Guru99');

Code Penjelasan:

• Code baris 1: Membuat prosedur dengan nama 'welcome_msg' dan satu parameter 'p_name' dengan tipe 'IN'.
• Code baris 4: Mencetak pesan selamat datang dengan menggabungkan nama yang dimasukkan.
• Prosedur telah berhasil disusun.
• Code baris 7: Memanggil prosedur menggunakan EXEC dengan parameter 'Guru99'. Prosedur dieksekusi dan mencetak "Selamat datang" Guru99. "

Apa itu Fungsi?

Fungsi adalah subprogram PL/SQL yang berdiri sendiri. Seperti halnya prosedur, fungsi memiliki nama unik dan disimpan sebagai objek basis data PL/SQL. Karakteristiknya adalah:

• Fungsi adalah blok mandiri yang digunakan terutama untuk perhitungan.
• Suatu fungsi menggunakan kata kunci RETURN untuk mengembalikan nilai, yang tipe datanya ditentukan pada saat pembuatannya.
• Suatu fungsi harus mengembalikan nilai atau menimbulkan pengecualian; pengembalian nilai bersifat wajib dalam suatu fungsi.
• Fungsi tanpa pernyataan DML dapat dipanggil langsung dalam kueri SELECT, sedangkan fungsi dengan pernyataan DML hanya dapat dipanggil dari blok PL/SQL lainnya.
• Ia dapat memiliki blok bersarang, atau bersarang di dalam blok atau paket lain.
• Ini berisi bagian deklarasi (opsional), bagian eksekusi, dan bagian penanganan pengecualian (opsional).
• Nilai dapat diteruskan ke atau diambil dari fungsi melalui parameter.
• Parameter ini harus disertakan dalam pernyataan pemanggil.
• Selain menggunakan RETURN, sebuah fungsi juga dapat mengembalikan nilai melalui parameter OUT.
• Karena selalu mengembalikan nilai, pernyataan pemanggilan selalu menggunakan operator penugasan untuk mengisi variabel.

Sintaksis

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• Perintah 'CREATE FUNCTION' menginstruksikan kompiler untuk membuat fungsi baru. Perintah 'OR REPLACE' menginstruksikan kompiler untuk mengganti fungsi yang ada (jika ada) dengan fungsi yang ada saat ini.
• Nama fungsi harus unik.
• Tipe data RETURN harus disebutkan.
• Kata kunci 'IS' digunakan ketika fungsi tersebut bersarang di dalam blok lain. Jika fungsi tersebut berdiri sendiri, maka 'AS' digunakan.

Contoh 1: Membuat Fungsi dan memanggilnya menggunakan blok anonim. Dalam program ini, kita membuat sebuah fungsi yang menerima nama sebagai input dan mengembalikan pesan selamat datang, menggunakan blok anonim dan pernyataan SELECT untuk memanggilnya.

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN ('Welcome '|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func ('Guru99');
dbms_output.put_line(lv_msg);
END;
/
SELECT welcome_msg_func('Guru99') FROM DUAL;

Code Penjelasan:

• Code baris 1: Membuat fungsi dengan nama 'welcome_msg_func' dan satu parameter 'p_name' bertipe 'IN'.
• Code baris 2: Menyatakan tipe kembalian sebagai VARCHAR2.
• Code baris 5: Mengembalikan nilai gabungan 'Welcome' dan nilai parameter.
• Code baris 8: Blok anonim untuk memanggil fungsi di atas.
• Code baris 9: Mendeklarasikan variabel dengan tipe data yang sama dengan tipe kembalian fungsi.
• Code baris 11: Memanggil fungsi dan mengisi nilai kembalian ke dalam variabel 'lv_msg'.
• Code baris 12: Mencetak nilai variabel. Outputnya adalah “Selamat datang” Guru99. "
• Code baris 14: Memanggil fungsi yang sama melalui pernyataan SELECT. Nilai kembaliannya diarahkan ke output standar.

Persamaan Antara Prosedur dan Fungsi

• Keduanya bisa dipanggil dari blok PL/SQL lainnya.
• Jika pengecualian yang muncul di dalam subprogram tidak ditangani di dalamnya penanganan pengecualian bagian tersebut, kemudian menyebar ke blok pemanggil.
• Keduanya dapat memiliki parameter sebanyak yang diperlukan.
• Keduanya diperlakukan sebagai objek database di PL/SQL.

Prosedur vs Fungsi: Perbedaan Utama

Fungsi Bawaan di PL/SQL

PL / SQL Berisi berbagai fungsi bawaan untuk bekerja dengan tipe data string dan tanggal. Di sini kita akan melihat fungsi-fungsi yang umum digunakan dan cara penggunaannya.

Fungsi Konversi

Fungsi bawaan ini mengkonversi satu tipe data ke tipe data lainnya.

Fungsi String

Fungsi-fungsi ini digunakan pada tipe data karakter.

Fungsi Tanggal

Fungsi-fungsi ini digunakan untuk memanipulasi tanggal.

Pertanyaan Umum Demo Slot