Oracle Prosedur & Fungsi Tersimpan PL/SQL beserta Contohnya
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
- DALAM Parameter
- Parameter KELUAR
- 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.
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.
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.
|
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.
|
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