Oracle Prosedur & Fungsi Tersimpan PL/SQL beserta Contohnya

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

Prosedur dan Fungsi adalah subprogram yang dapat dibuat dan disimpan dalam database sebagai objek database. Mereka juga dapat dipanggil atau dirujuk ke dalam blok lain.

Selain itu, kami akan membahas perbedaan utama antara kedua subprogram ini. Juga, kita akan membahasnya Oracle fungsi bawaan.

Terminologi dalam Subprogram PL/SQL

Sebelum kita mempelajari subprogram PL/SQL, kita akan membahas berbagai terminologi yang merupakan bagian dari subprogram tersebut. Di bawah ini adalah terminologi yang akan kita bahas.

Parameter

Parameternya adalah variabel atau placeholder yang valid Tipe data PL/SQL melalui mana subprogram PL/SQL menukar nilai dengan kode utama. Parameter ini memungkinkan untuk memberikan masukan ke subprogram dan mengekstrak dari subprogram tersebut.

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

Berdasarkan tujuannya, parameter diklasifikasikan sebagai

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

DALAM Parameter

  • Parameter ini digunakan untuk memberikan masukan pada subprogram.
  • Ini adalah variabel read-only di dalam subprogram. Nilainya tidak dapat diubah di dalam subprogram.
  • Dalam pernyataan pemanggil, parameter ini bisa berupa variabel atau nilai literal atau ekspresi, misalnya bisa berupa ekspresi aritmatika seperti '5*8' atau 'a/b' di mana 'a' dan 'b' adalah variabel .
  • Secara default, parameternya bertipe IN.

Parameter KELUAR

  • Parameter ini digunakan untuk mendapatkan keluaran dari subprogram.
  • Ini adalah variabel baca-tulis di dalam subprogram. Nilainya dapat diubah di dalam subprogram.
  • Dalam pernyataan pemanggilan, parameter ini harus selalu berupa variabel untuk menampung nilai dari subprogram saat ini.

Parameter DALAM KELUAR

  • Parameter ini digunakan untuk memberikan masukan dan mendapatkan keluaran dari subprogram.
  • Ini adalah variabel baca-tulis di dalam subprogram. Nilainya dapat diubah di dalam subprogram.
  • Dalam pernyataan pemanggilan, parameter ini harus selalu berupa variabel untuk menampung nilai dari subprogram.

Tipe parameter ini harus disebutkan pada saat membuat subprogram.

KEMBALI

RETURN adalah kata kunci yang menginstruksikan kompiler untuk mengalihkan kontrol dari subprogram ke pernyataan pemanggil. Dalam subprogram RETURN berarti kontrol harus keluar dari subprogram. Setelah pengontrol menemukan kata kunci RETURN di subprogram, kode setelahnya akan dilewati.

Biasanya, blok induk atau blok utama akan memanggil subprogram, dan kemudian kontrol akan berpindah dari blok induk tersebut ke subprogram yang dipanggil. RETURN di subprogram akan mengembalikan kontrol ke blok induknya. Dalam hal fungsi, pernyataan RETURN juga mengembalikan nilainya. Tipe data dari nilai ini selalu disebutkan pada saat deklarasi fungsi. Tipe datanya bisa berupa tipe data PL/SQL apa pun yang valid.

Apa itu Prosedur di PL/SQL?

A Prosedur dalam PL/SQL adalah unit subprogram yang terdiri dari sekelompok pernyataan PL/SQL yang dapat dipanggil berdasarkan namanya. Setiap prosedur dalam PL/SQL mempunyai nama uniknya sendiri yang dapat digunakan untuk merujuk dan memanggil prosedur tersebut. Unit subprogram ini di Oracle database disimpan sebagai objek database.

Catatan: Subprogram tidak lain hanyalah sebuah prosedur, dan perlu dibuat secara manual sesuai kebutuhan. Setelah dibuat, mereka akan disimpan sebagai objek database.

Berikut adalah ciri-ciri unit subprogram Prosedur pada PL/SQL:

  • Prosedur adalah blok mandiri dari suatu program yang dapat disimpan di Database.
  • Panggilan ke prosedur PLSQL ini dapat dilakukan dengan mengacu pada namanya, untuk mengeksekusi pernyataan PL/SQL.
  • Ini terutama digunakan untuk menjalankan proses di PL/SQL.
  • Itu bisa memiliki blok bersarang, atau bisa didefinisikan dan disarangkan di dalam blok atau paket lain.
  • Ini berisi bagian deklarasi (opsional), bagian eksekusi, bagian penanganan pengecualian (opsional).
  • Nilai-nilai tersebut dapat diteruskan Oracle prosedur atau diambil dari prosedur melalui parameter.
  • Parameter ini harus disertakan dalam pernyataan pemanggil.
  • Prosedur dalam SQL dapat memiliki pernyataan RETURN untuk mengembalikan kontrol ke blok pemanggil, namun tidak dapat mengembalikan nilai apa pun melalui pernyataan RETURN.
  • Prosedur tidak dapat dipanggil langsung dari pernyataan SELECT. Mereka dapat dipanggil dari blok lain atau melalui kata kunci EXEC.

Sintaksis

CREATE OR REPLACE PROCEDURE 
<procedure_name>
	(
	<parameterl IN/OUT <datatype>
	..
	.
	)
[ IS | AS ]
	<declaration_part>
BEGIN
	<execution part>
EXCEPTION
	<exception handling part>
END;
  • CREATE PROCEDURE memerintahkan compiler untuk membuat prosedur baru Oracle. Kata kunci 'OR REPLACE' memerintahkan kompilasi untuk mengganti prosedur yang ada (jika ada) dengan yang sekarang.
  • Nama prosedur harus unik.
  • Kata kunci 'IS' akan digunakan, ketika prosedur tersimpan di Oracle bersarang di beberapa blok lain. Jika prosedurnya mandiri maka 'AS' akan digunakan. Selain standar pengkodean ini, keduanya memiliki arti yang sama.

Contoh1: Membuat Prosedur dan memanggilnya menggunakan EXEC

Dalam contoh ini, kita akan membuat Oracle prosedur yang mengambil nama sebagai masukan dan mencetak pesan selamat datang sebagai keluaran. Kami akan menggunakan perintah EXEC untuk memanggil prosedur.

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

Penjelasan Kode:

  • Baris kode 1: Membuat prosedur dengan nama 'welcome_msg' dan dengan satu parameter 'p_name' bertipe 'IN'.
  • Baris kode 4: Mencetak pesan selamat datang dengan menggabungkan nama masukan.
  • Prosedur berhasil dikompilasi.
  • Baris kode 7: Memanggil prosedur menggunakan perintah EXEC dengan parameter 'Guru99'. Prosedur dijalankan, dan pesan dicetak sebagai “Selamat Datang Guru99”.

Apa itu Fungsi?

Fungsi adalah subprogram PL/SQL yang berdiri sendiri. Seperti prosedur PL/SQL, fungsi memiliki nama unik yang dapat digunakan untuk merujuknya. Ini disimpan sebagai objek database PL/SQL. Di bawah ini adalah beberapa ciri-ciri fungsi.

  • Fungsi adalah blok mandiri yang terutama digunakan untuk tujuan perhitungan.
  • Fungsi menggunakan kata kunci RETURN untuk mengembalikan nilai, dan tipe datanya ditentukan pada saat pembuatan.
  • Suatu Fungsi harus mengembalikan nilai atau memunculkan pengecualian, yaitu pengembalian adalah wajib dalam fungsi.
  • Fungsi tanpa pernyataan DML dapat langsung dipanggil dalam kueri SELECT, sedangkan fungsi dengan operasi DML hanya dapat dipanggil dari blok PL/SQL lainnya.
  • Itu bisa memiliki blok bersarang, atau bisa didefinisikan dan disarangkan di dalam blok atau paket lain.
  • Ini berisi bagian deklarasi (opsional), bagian eksekusi, bagian penanganan pengecualian (opsional).
  • Nilai dapat diteruskan ke fungsi atau diambil dari prosedur melalui parameter.
  • Parameter ini harus disertakan dalam pernyataan pemanggil.
  • Fungsi PLSQL juga dapat mengembalikan nilai melalui parameter OUT selain menggunakan RETURN.
  • Karena akan selalu mengembalikan nilai, dalam pernyataan pemanggilan ia selalu disertai dengan operator penugasan untuk mengisi variabel.

Fungsi di PL/SQL

Sintaksis

CREATE OR REPLACE FUNCTION 
<procedure_name>
(
<parameterl IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part> 
EXCEPTION
<exception handling part>
END;
  • CREATE FUNCTION memerintahkan kompiler untuk membuat fungsi baru. Kata kunci 'OR REPLACE' memerintahkan compiler untuk mengganti fungsi yang ada (jika ada) dengan fungsi saat ini.
  • Nama Fungsi harus unik.
  • Tipe data RETURN harus disebutkan.
  • Kata kunci 'IS' akan digunakan, ketika prosedur disarangkan ke beberapa blok lain. Jika prosedurnya mandiri maka 'AS' akan digunakan. Selain standar pengkodean ini, keduanya memiliki arti yang sama.

Contoh1: Membuat Fungsi dan memanggilnya menggunakan Blok Anonim

Dalam program ini, kita akan membuat fungsi yang mengambil nama sebagai masukan dan mengembalikan pesan selamat datang sebagai keluaran. Kami akan menggunakan blok anonim dan pernyataan pilih untuk memanggil fungsi tersebut.

Fungsi di PL/SQL

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2
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;

Penjelasan Kode:

  • Baris kode 1: Membuat Oracle berfungsi dengan nama 'welcome_msg_func' dan dengan satu parameter 'p_name' bertipe 'IN'.
  • Baris kode 2: mendeklarasikan tipe pengembalian sebagai VARCHAR2
  • Baris kode 5: Mengembalikan nilai gabungan 'Selamat Datang' dan nilai parameter.
  • Baris kode 8: Blok anonim untuk memanggil fungsi di atas.
  • Baris kode 9: Mendeklarasikan variabel dengan tipe data yang sama dengan tipe data kembalian fungsi.
  • Baris kode 11: Memanggil fungsi dan mengisi nilai kembalian ke variabel 'lv_msg'.
  • Baris kode 12: Mencetak nilai variabel. Output yang akan Anda dapatkan di sini adalah “Selamat Datang Guru99”
  • Baris kode 14: Memanggil fungsi yang sama melalui pernyataan SELECT. Nilai kembalian diarahkan ke output standar secara langsung.

Persamaan antara Prosedur dan Fungsi

  • Keduanya bisa dipanggil dari blok PL/SQL lainnya.
  • Jika pengecualian yang dimunculkan di subprogram tidak ditangani di subprogram penanganan pengecualian bagian, maka itu akan menyebar ke blok panggilan.
  • Keduanya dapat memiliki parameter sebanyak yang diperlukan.
  • Keduanya diperlakukan sebagai objek database di PL/SQL.

Prosedur Vs. Fungsi: Perbedaan Utama

Prosedur fungsi
Digunakan terutama untuk menjalankan proses tertentu Digunakan terutama untuk melakukan beberapa perhitungan
Tidak dapat memanggil pernyataan SELECT Fungsi yang tidak berisi pernyataan DML dapat dipanggil dalam pernyataan SELECT
Gunakan parameter OUT untuk mengembalikan nilai Gunakan RETURN untuk mengembalikan nilainya
Mengembalikan nilainya tidak wajib Mengembalikan nilainya adalah wajib
RETURN hanya akan keluar dari kontrol dari subprogram. RETURN akan keluar dari kontrol dari subprogram dan juga mengembalikan nilainya
Tipe data pengembalian tidak akan ditentukan pada saat pembuatan Tipe data pengembalian adalah wajib pada saat pembuatan

Fungsi Bawaan di PL/SQL

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

Fungsi Konversi

Fungsi bawaan ini digunakan untuk mengonversi satu tipe data ke tipe data lainnya.

Nama Fungsi penggunaan Example
TO_CHAR Mengonversi tipe data lain menjadi tipe data karakter TO_CHAR(123);
TO_DATE ( string, format ) Mengonversi string yang diberikan menjadi tanggal. String harus sesuai dengan formatnya.

TO_DATE('2015-JAN-15', 'YYYY-MON-DD');

Keluaran: 1 / 15 / 2015

TO_NUMBER (teks, format)

Mengonversi teks menjadi tipe angka dalam format tertentu.

Informasi '9' menunjukkan jumlah digit

Pilih TO_NUMBER('1234′,'9999') dari ganda;

Keluaran: 1234

Pilih TO_NUMBER('1,234.45′,'9,999.99') dari ganda;

Keluaran: 1234

Fungsi String

Ini adalah fungsi yang digunakan pada tipe data karakter.

Nama Fungsi penggunaan Example
INSTR(teks, string, awal, kejadian) Memberikan posisi teks tertentu dalam string yang diberikan.

  • teks – String utama
  • string – teks yang perlu dicari
  • start – posisi awal pencarian (opsional)
  • sesuai – kemunculan string yang dicari (opsional)
Pilih INSTR('AEROPLANE','E',2,1) dari ganda

Keluaran: 2

Pilih INSTR('AEROPLANE','E',2,2) dari ganda

Keluaran: 9 (2nd terjadinya E)

SUBSTR (teks, awal, panjang) Memberikan nilai substring dari string utama.

  • teks – string utama
  • mulai – posisi awal
  • length – panjang yang akan disub string
pilih substr('pesawat',1,7) dari ganda

Keluaran: aeropla

ATAS ( teks ) Mengembalikan huruf besar dari teks yang disediakan Pilih atas('guru99') dari ganda;

Keluaran: GURU99

RENDAH ( teks ) Mengembalikan huruf kecil dari teks yang disediakan Pilih lebih rendah ('AerOpLane') dari ganda;

Keluaran: pesawat terbang

INITCAP ( teks) Mengembalikan teks tertentu dengan huruf awal dalam huruf besar. Pilih ('guru99') dari ganda

Keluaran: Guru99

Pilih ('cerita saya') dari ganda

Keluaran: Ceritaku

PANJANG (teks) Mengembalikan panjang string yang diberikan Pilih PANJANG ('guru99') dari ganda;

Keluaran: 6

LPAD (teks, panjang, pad_char) Memasukkan string di sisi kiri untuk panjang tertentu (total string) dengan karakter tertentu Pilih LPAD('guru99', 10, '$') dari ganda;

Keluaran: $$$$guru99

RPAD (teks, panjang, pad_char) Memasukkan string di sisi kanan untuk panjang tertentu (total string) dengan karakter tertentu Pilih RPAD('guru99′,10,'-') dari ganda

Keluaran: guru99—-

LTRIM ( teks ) Memangkas spasi putih di depan teks Pilih LTRIM(' Guru99') dari ganda;

Keluaran: Guru99

RTRIM ( teks ) Memangkas spasi tambahan dari teks Pilih RTRIM('Guru99 ') dari ganda;

Keluaran; Guru99

Fungsi Tanggal

Ini adalah fungsi yang digunakan untuk memanipulasi tanggal.

Nama Fungsi penggunaan Example
ADD_MONTHS (tanggal, jumlah bulan) Menambahkan bulan tertentu ke tanggal ADD_MONTH('2015-01-01',5);

Keluaran: 05 / 01 / 2015

SYSDATE Mengembalikan tanggal dan waktu server saat ini Pilih SYSDATE dari ganda;

Keluaran: 10/4/2015 2:11:43

TRUNK Bulatkan variabel tanggal ke nilai serendah mungkin pilih sysdate, TRUNC(sysdate) dari ganda;

Keluaran: 10/4/2015 2:12:39 10/4/2015

ROUND Membulatkan tanggal ke batas terdekat, baik lebih tinggi atau lebih rendah Pilih sysdate, ROUND(sysdate) dari ganda

Keluaran: 10/4/2015 2:14:34 10/5/2015

BULAN_BETWEEN Mengembalikan jumlah bulan antara dua tanggal Pilih MONTHS_BETWEEN (sysdate+60, sysdate) dari ganda

Keluaran: 2

Kesimpulan

Dalam bab ini, kita telah mempelajari hal berikut.

  • Cara membuat Prosedur dan berbagai cara menyebutnya
  • Cara membuat Fungsi dan berbagai cara memanggilnya
  • Persamaan dan perbedaan Prosedur dan Fungsi
  • Parameter dan RETURN terminologi umum dalam subprogram PL/SQL
  • Fungsi bawaan umum di Oracle PL / SQL