Prosedur Tersimpan Oracle PL / SQL & Fungsi dengan Contoh

Daftar Isi:

Anonim

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

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

Selain itu, kami akan membahas perbedaan utama antara kedua subprogram ini. Juga, kita akan membahas fungsi built-in Oracle.

Dalam tutorial Prosedur Tersimpan Oracle ini, Anda akan mempelajari-

  • Terminologi dalam Subprogram PL / SQL
  • Apa Prosedur di PL / SQL?
  • Apa itu Fungsi?
  • Persamaan antara Prosedur dan Fungsi
  • Prosedur Vs. Fungsi: Perbedaan Kunci
  • Fungsi Bawaan di PL / SQL

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 dari tipe data PL / SQL yang valid di mana subprogram PL / SQL menukar nilai dengan kode utama. Parameter ini memungkinkan untuk memberikan masukan ke subprogram dan mengekstrak dari subprogram ini.

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

Berdasarkan parameter tujuan mereka diklasifikasikan sebagai

  1. Parameter IN
  2. Parameter KELUAR
  3. DI OUT Parameter

Parameter IN:

  • Parameter ini digunakan untuk memberi masukan pada subprogram.
  • Ini adalah variabel hanya-baca di dalam subprogram. Nilainya tidak dapat diubah di dalam subprogram.
  • Dalam pernyataan panggilan, parameter ini dapat berupa variabel atau nilai literal atau ekspresi, misalnya, dapat berupa ekspresi aritmatika seperti '5 * 8' atau 'a / b' di mana 'a' dan 'b' adalah variabel .
  • Secara default, parameternya adalah tipe 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 panggilan, parameter ini harus selalu menjadi variabel untuk menampung nilai dari subprogram saat ini.

Parameter MASUK:

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

Jenis parameter ini harus disebutkan pada saat membuat subprogram.

KEMBALI

RETURN adalah kata kunci yang menginstruksikan kompilator untuk mengalihkan kontrol dari subprogram ke pernyataan pemanggil. Dalam subprogram RETURN berarti bahwa kontrol harus keluar dari subprogram. Setelah pengontrol menemukan kata kunci RETURN dalam subprogram, kode setelah ini akan dilewati.

Biasanya, induk atau blok utama akan memanggil subprogram, dan kemudian kontrol akan bergeser dari blok induk tersebut ke subprogram yang disebut. RETURN di subprogram akan mengembalikan kontrol ke blok induknya. Dalam kasus fungsi, pernyataan RETURN juga mengembalikan nilai. Jenis data dari nilai ini selalu disebutkan pada saat deklarasi fungsi. Tipe data dapat berupa tipe data PL / SQL yang valid.

Apa Prosedur di PL / SQL?

Sebuah Prosedur di PL / SQL adalah unit subprogram yang terdiri dari sekelompok pernyataan PL / SQL yang dapat disebut dengan nama. Setiap prosedur di PL / SQL memiliki nama uniknya sendiri yang dapat digunakan untuk merujuk dan memanggil. Unit subprogram ini dalam database Oracle disimpan sebagai objek database.

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

Di bawah ini adalah karakteristik unit subprogram Prosedur dalam PL / SQL:

  • Prosedur adalah blok program yang berdiri sendiri yang dapat disimpan dalam database.
  • Panggilan ke prosedur PLSQL ini dapat dilakukan dengan mengacu pada namanya, untuk menjalankan 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 dapat dimasukkan ke dalam prosedur Oracle atau diambil dari prosedur melalui parameter.
  • Parameter ini harus disertakan dalam pernyataan panggilan.
  • Prosedur dalam SQL dapat memiliki pernyataan RETURN untuk mengembalikan kontrol ke blok pemanggil, tetapi tidak dapat mengembalikan nilai apa pun melalui pernyataan RETURN.
  • Prosedur tidak bisa dipanggil langsung dari pernyataan SELECT. Mereka dapat dipanggil dari blok lain atau melalui kata kunci EXEC.

Sintaksis:

CREATE OR REPLACE PROCEDURE
(
… )[ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE PROCEDURE menginstruksikan compiler untuk membuat prosedur baru di 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 disarangkan ke beberapa blok lain. Jika prosedurnya berdiri sendiri 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 prosedur Oracle 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)ISBEGINdbms_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' tipe 'IN'.
  • Baris kode 4 : Mencetak pesan selamat datang dengan menggabungkan nama input.
  • 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?

Functions adalah subprogram PL / SQL mandiri. 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 dari suatu fungsi.

  • Fungsi adalah blok mandiri yang terutama digunakan untuk tujuan kalkulasi.
  • Fungsi menggunakan kata kunci RETURN untuk mengembalikan nilai, dan tipe data ini ditentukan pada saat pembuatan.
  • Sebuah Fungsi harus mengembalikan nilai atau memunculkan pengecualian, yaitu pengembalian 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 dilewatkan ke dalam fungsi atau diambil dari prosedur melalui parameter.
  • Parameter ini harus disertakan dalam pernyataan panggilan.
  • Fungsi PLSQL juga dapat mengembalikan nilai melalui parameter OUT selain menggunakan RETURN.
  • Karena itu akan selalu mengembalikan nilai, dalam pernyataan panggilan itu selalu disertai dengan operator penugasan untuk mengisi variabel.

Sintaksis

CREATE OR REPLACE FUNCTION
(
)RETURN [ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE FUNCTION menginstruksikan kompilator untuk membuat fungsi baru. Kata kunci 'OR REPLACE' menginstruksikan kompilator untuk mengganti fungsi yang ada (jika ada) dengan yang sekarang.
  • Nama Fungsi harus unik.
  • Jenis data RETURN harus disebutkan.
  • Kata kunci 'IS' akan digunakan, ketika prosedur disarangkan ke beberapa blok lain. Jika prosedurnya berdiri sendiri maka 'AS' akan digunakan. Selain standar pengkodean ini, keduanya memiliki arti yang sama.

Contoh1: Membuat Fungsi dan memanggilnya menggunakan Anonymous Block

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 memilih pernyataan untuk memanggil fungsi tersebut.

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2ISBEGINRETURN (‘Welcome ‘|| p_name);END;/DECLARElv_msg VARCHAR2(250);BEGINlv_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 fungsi Oracle 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 yang dikembalikan dari fungsi.
  • Baris kode 11 : Memanggil fungsi dan mengisi nilai kembali 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 kembali diarahkan ke keluaran standar secara langsung.

Persamaan antara Prosedur dan Fungsi

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

Prosedur Vs. Fungsi: Perbedaan Kunci

Prosedur Fungsi
  • Digunakan terutama untuk menjalankan proses tertentu
  • Digunakan terutama untuk melakukan beberapa kalkulasi
  • Tidak dapat memanggil dalam pernyataan SELECT
  • Fungsi yang tidak berisi pernyataan DML bisa dipanggil dalam pernyataan SELECT
  • Gunakan parameter OUT untuk mengembalikan nilai
  • Gunakan RETURN untuk mengembalikan nilai
  • Tidak wajib mengembalikan nilai
  • Nilai tersebut wajib dikembalikan
  • RETURN hanya akan keluar dari kontrol dari subprogram.
  • RETURN akan keluar dari kontrol dari subprogram dan juga mengembalikan nilainya
  • Jenis data yang dikembalikan tidak akan ditentukan pada saat pembuatan
  • Jenis data pengembalian wajib diisi pada saat pembuatan

Fungsi Bawaan di PL / SQL

PL / SQL berisi berbagai fungsi built-in 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 lain.

Nama Fungsi Pemakaian Contoh
TO_CHAR Mengonversi tipe data lain ke tipe data karakter TO_CHAR (123);
TO_DATE (string, format) Mengonversi string yang diberikan menjadi tanggal. String harus sesuai dengan format. TO_DATE ('2015-JAN-15', 'YYYY-MON-DD'); Keluaran: 15/1/2015
TO_NUMBER (teks, format) Mengonversi teks menjadi tipe angka dari format yang diberikan. Informat '9' menunjukkan jumlah digit Pilih TO_NUMBER ('1234', '9999') dari dual; Output: 1234 Pilih TO_NUMBER ('1,234.45', '9,999.99') dari dual; Keluaran: 1234

Fungsi String

Ini adalah fungsi yang digunakan pada tipe data karakter.

Nama Fungsi Pemakaian Contoh
INSTR (teks, string, mulai, 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 ('AIRPLANE', 'E', 2,1) dari dual Output : 2 Pilih INSTR ('AIRPLANE', 'E', 2,2) dari dual Output: 9 (2 nd occurance of E)
SUBSTR (teks, awal, panjang) Memberikan nilai substring dari string utama.
  • teks - string utama
  • start - posisi awal
  • panjang - panjang menjadi sub senar
 pilih substr (' aircraft ', 1,7) dari dual Output : aeropla
UPPER (teks) Menampilkan huruf besar dari teks yang disediakan Pilih atas ('guru99') dari ganda; Keluaran : GURU99
RENDAH (teks) Menampilkan huruf kecil dari teks yang disediakan Pilih lebih rendah ('pesawat') dari ganda; Output : pesawat terbang
INITCAP (teks) Mengembalikan teks yang diberikan dengan huruf awal dalam huruf besar. Pilih ('guru99') dari dual Output : Guru99 Select ('my story') dari dual Output : My Story
PANJANG (teks) Mengembalikan panjang string yang diberikan Pilih PANJANG ('guru99') dari ganda; Hasil : 6
LPAD (teks, panjang, pad_char) Mengisi string di sisi kiri untuk panjang tertentu (string total) dengan karakter yang diberikan Pilih LPAD ('guru99', 10, '$') dari dual; Keluaran : $$$$ guru99
RPAD (teks, panjang, pad_char) Mengisi string di sisi kanan untuk panjang tertentu (string total) dengan karakter yang diberikan Pilih RPAD ('guru99', 10, '-') dari dual Output : guru99 ----
LTRIM (teks) Memangkas ruang putih terdepan dari teks Pilih LTRIM ('Guru99') dari ganda; Hasil : Guru99
RTRIM (teks) Memangkas spasi kosong dari teks Pilih RTRIM ('Guru99') dari ganda; Output ; Guru99

Fungsi Tanggal

Ini adalah fungsi yang digunakan untuk memanipulasi tanggal.

Nama Fungsi Pemakaian Contoh
ADD_MONTHS (tanggal, jumlah bulan) Menambahkan bulan yang ditentukan ke tanggal ADD_MONTH ('2015-01-01', 5); Keluaran : 05/01/2015
SYSDATE Mengembalikan tanggal dan waktu server saat ini Pilih SYSDATE dari dual; Keluaran : 10/4/2015 14:11:43
TRUNC Putaran variabel tanggal ke nilai yang mungkin lebih rendah pilih sysdate, TRUNC (sysdate) dari dual; Keluaran : 10/4/2015 2:12:39 10/4/2015
BULAT Membulatkan tanggal ke batas terdekat, baik lebih tinggi atau lebih rendah Pilih sysdate, ROUND (sysdate) dari dual Output : 10/4/2015 2:14:34 10/5/2015
MONTHS_BETWEEN Menampilkan jumlah bulan antara dua tanggal Pilih MONTHS_BETWEEN (sysdate + 60, sysdate) dari dual Output : 2

Ringkasan

Dalam bab ini, kita telah mempelajari yang berikut ini.

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