Dalam tutorial ini, anda akan melihat penerangan terperinci mengenai cara membuat dan melaksanakan blok yang dinamakan (prosedur dan fungsi).
Prosedur dan Fungsi adalah subprogram yang dapat dibuat dan disimpan dalam pangkalan data sebagai objek pangkalan data. Mereka boleh dipanggil atau disebut di dalam blok lain juga.
Selain daripada ini, kami akan merangkumi perbezaan utama antara kedua subprogram ini. Juga, kita akan membincangkan fungsi terbina dalam Oracle.
Dalam tutorial Prosedur Tersimpan Oracle ini, anda akan belajar-
- Terminologi dalam Subprogram PL / SQL
- Apakah Prosedur dalam PL / SQL?
- Apa itu Fungsi?
- Persamaan antara Prosedur dan Fungsi
- Prosedur Vs. Fungsi: Perbezaan Utama
- Fungsi terbina dalam PL / SQL
Terminologi dalam Subprogram PL / SQL
Sebelum kita mengetahui mengenai subprogram PL / SQL, kita akan membincangkan pelbagai terminologi yang merupakan bahagian dari subprogram ini. Berikut adalah terminologi yang akan kita bincangkan.
Parameter:
Parameternya berubah-ubah atau placeholder mana-mana jenis data PL / SQL yang sah di mana subprogram PL / SQL bertukar nilai dengan kod utama. Parameter ini memungkinkan untuk memberi input kepada subprogram dan mengekstrak dari subprogram ini.
- Parameter ini harus ditentukan bersama dengan subprogram pada masa penciptaan.
- Parameter ini disertakan dalam penyataan panggilan dari subprogram ini untuk berinteraksi nilai dengan subprogram.
- Jenis data parameter dalam subprogram dan penyataan panggilan harus sama.
- Ukuran jenis data tidak boleh disebut pada saat pengisytiharan parameter, kerana ukurannya dinamis untuk jenis ini.
Berdasarkan tujuan mereka dikelaskan sebagai
- DALAM Parameter
- Parameter KELUAR
- Parameter DALAM KELUAR
DALAM Parameter:
- Parameter ini digunakan untuk memberi input kepada subprogram.
- Ini adalah pemboleh ubah baca sahaja di dalam subprogram. Nilai mereka tidak dapat diubah dalam subprogram.
- Dalam pernyataan panggilan, parameter ini boleh berupa pemboleh ubah atau nilai harfiah atau ungkapan, misalnya, boleh menjadi ungkapan aritmetik seperti '5 * 8' atau 'a / b' di mana 'a' dan 'b' adalah pemboleh ubah .
- Secara lalai, parameternya adalah jenis IN.
Parameter KELUAR:
- Parameter ini digunakan untuk mendapatkan output dari subprogram.
- Ia adalah pemboleh ubah baca-tulis di dalam subprogram. Nilai mereka dapat diubah di dalam subprogram.
- Dalam pernyataan panggilan, parameter ini harus selalu berubah-ubah untuk menyimpan nilai dari subprogram semasa.
DALAM KELUAR Parameter:
- Parameter ini digunakan untuk memberikan input dan untuk mendapatkan output dari subprogram.
- Ia adalah pemboleh ubah baca-tulis di dalam subprogram. Nilai mereka dapat diubah di dalam subprogram.
- Dalam pernyataan panggilan, parameter ini harus selalu berubah-ubah untuk menahan nilai dari subprogram.
Jenis parameter ini harus disebutkan pada saat membuat subprogram.
KEMBALI
RETURN adalah kata kunci yang memerintahkan pengkompil untuk menukar kawalan dari subprogram ke penyataan panggilan. Dalam subprogram RETURN bermaksud kawalan perlu keluar dari subprogram. Setelah pengawal menemui KEMBALI kata kunci dalam subprogram, kod selepas ini akan dilangkau.
Biasanya, blok induk atau utama akan memanggil subprogram, dan kemudian kawalan akan beralih dari blok induk ke subprogram yang disebut. KEMBALI dalam subprogram akan mengembalikan kawalan ke blok induk mereka. Dalam kes fungsi, pernyataan KEMBALI juga mengembalikan nilai. Jenis data dari nilai ini selalu disebut pada saat pengisytiharan fungsi. Jenis data boleh terdiri daripada jenis data PL / SQL yang sah.
Apakah Prosedur dalam PL / SQL?
A Prosedur dalam PL / SQL ialah unit subaturcara yang terdiri daripada sekumpulan penyata PL / SQL yang boleh dipanggil dengan nama. Setiap prosedur di PL / SQL mempunyai nama yang tersendiri yang boleh disebut dan dipanggil. Unit subprogram ini dalam pangkalan data Oracle disimpan sebagai objek pangkalan data.
Catatan: Subprogram tidak lain hanyalah prosedur, dan perlu dibuat secara manual mengikut keperluan. Setelah dibuat mereka akan disimpan sebagai objek pangkalan data.
Berikut adalah ciri-ciri unit subprogram Prosedur dalam PL / SQL:
- Prosedur adalah blok program yang boleh disimpan dalam pangkalan data.
- Panggilan ke prosedur PLSQL ini dapat dilakukan dengan merujuk pada nama mereka, untuk melaksanakan pernyataan PL / SQL.
- Ini terutama digunakan untuk menjalankan proses dalam PL / SQL.
- Ia boleh mempunyai blok bersarang, atau dapat ditentukan dan bersarang di dalam blok atau bungkusan lain.
- Ini berisi bahagian deklarasi (pilihan), bahagian pelaksanaan, bahagian pengendalian pengecualian (pilihan).
- Nilai dapat diteruskan ke prosedur Oracle atau diambil dari prosedur melalui parameter.
- Parameter ini harus dimasukkan dalam penyataan panggilan.
- Prosedur dalam SQL boleh mempunyai pernyataan RETURN untuk mengembalikan kawalan ke blok panggilan, tetapi tidak dapat mengembalikan nilai melalui pernyataan RETURN.
- Prosedur tidak boleh dipanggil langsung dari pernyataan SELECT. Mereka boleh dipanggil dari blok lain atau melalui kata kunci EXEC.
Sintaks:
CREATE OR REPLACE PROCEDURE( … )[ IS | AS ] BEGIN EXCEPTION END;
- PROSEDUR CREATE mengarahkan pengkompil untuk membuat prosedur baru di Oracle. Kata kunci 'ATAU GANTI' mengarahkan kompilasi untuk mengganti prosedur yang ada (jika ada) dengan yang sekarang.
- Nama prosedur mestilah unik.
- Kata kunci 'IS' akan digunakan, apabila prosedur yang tersimpan di Oracle bersarang di beberapa blok lain. Sekiranya prosedur ini berdiri sendiri maka 'AS' akan digunakan. Selain daripada standard pengekodan ini, kedua-duanya mempunyai makna yang sama.
Contoh1: Membuat Prosedur dan memanggilnya menggunakan EXEC
Dalam contoh ini, kita akan membuat prosedur Oracle yang mengambil nama sebagai input dan mencetak mesej selamat datang sebagai output. 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 Kod:
- Baris kod 1 : Membuat prosedur dengan nama 'welcome_msg' dan dengan satu parameter 'p_name' dari jenis 'IN'.
- Baris kod 4 : Mencetak mesej selamat datang dengan menggabungkan nama input.
- Prosedur disusun dengan jayanya.
- Baris kod 7 : Memanggil prosedur menggunakan perintah EXEC dengan parameter 'Guru99'. Prosedur dijalankan, dan mesej dicetak sebagai "Selamat Datang Guru99".
Apa itu Fungsi?
Fungsi adalah subprogram PL / SQL yang berdiri sendiri. Seperti prosedur PL / SQL, fungsi mempunyai nama unik yang dapat disebut. Ini disimpan sebagai objek pangkalan data PL / SQL. Berikut adalah beberapa ciri fungsi.
- Fungsi adalah blok mandiri yang terutama digunakan untuk tujuan pengiraan.
- Fungsi menggunakan KEMBALI kata kunci untuk mengembalikan nilai, dan jenis data ini ditentukan pada masa penciptaan.
- Fungsi harus mengembalikan nilai atau menaikkan pengecualian, iaitu pengembalian adalah wajib dalam fungsi.
- Fungsi tanpa pernyataan DML dapat dipanggil secara langsung dalam pertanyaan SELECT sedangkan fungsi dengan operasi DML hanya dapat dipanggil dari blok PL / SQL lain.
- Ia boleh mempunyai blok bersarang, atau dapat ditentukan dan bersarang di dalam blok atau bungkusan lain.
- Ini berisi bahagian deklarasi (pilihan), bahagian pelaksanaan, bahagian pengendalian pengecualian (pilihan).
- Nilai dapat diteruskan ke fungsi atau diambil dari prosedur melalui parameter.
- Parameter ini harus dimasukkan dalam penyataan panggilan.
- Fungsi PLSQL juga dapat mengembalikan nilai melalui parameter OUT selain menggunakan RETURN.
- Oleh kerana ia akan selalu mengembalikan nilai, dalam penyataan panggilan, ia selalu disertakan dengan operator tugas untuk mengisi pemboleh ubah.
Sintaks
CREATE OR REPLACE FUNCTION( )RETURN [ IS | AS ] BEGIN EXCEPTION END;
- CREATE FUNCTION memerintahkan penyusun untuk membuat fungsi baru. Kata kunci 'ATAU GANTI' mengarahkan pengkompil untuk mengganti fungsi yang ada (jika ada) dengan fungsi semasa.
- Nama Fungsi mestilah unik.
- Jenis data KEMBALI harus disebutkan.
- Kata kunci 'IS' akan digunakan, ketika prosedur disarang ke beberapa blok lain. Sekiranya prosedur ini berdiri sendiri maka 'AS' akan digunakan. Selain daripada standard pengekodan ini, kedua-duanya mempunyai makna yang sama.
Contoh1: Membuat Fungsi dan memanggilnya menggunakan Blok Tanpa Nama
Dalam program ini, kita akan membuat fungsi yang mengambil nama sebagai input dan mengembalikan mesej selamat datang sebagai output. Kami akan menggunakan blok tanpa nama dan pilih pernyataan untuk memanggil fungsi.
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 Kod:
- Baris kod 1 : Membuat fungsi Oracle dengan nama 'welcome_msg_func' dan dengan satu parameter 'p_name' dari jenis 'IN'.
- Baris kod 2 : menyatakan jenis pengembalian sebagai VARCHAR2
- Baris kod 5 : Mengembalikan nilai gabungan 'Selamat Datang' dan nilai parameter.
- Baris kod 8 : Blok tanpa nama untuk memanggil fungsi di atas.
- Baris kod 9 : Menyatakan pemboleh ubah dengan jenis data sama dengan jenis data kembali fungsi.
- Baris kod 11 : Memanggil fungsi dan mengisi nilai kembali ke pemboleh ubah 'lv_msg'.
- Baris kod 12 : Mencetak nilai berubah. Output yang anda akan dapat di sini adalah "Welcome Guru99"
- Baris kod 14 : Memanggil fungsi yang sama melalui pernyataan SELECT. Nilai pulangan diarahkan ke output standard secara langsung.
Persamaan antara Prosedur dan Fungsi
- Kedua-duanya boleh dipanggil dari blok PL / SQL lain.
- Sekiranya pengecualian yang dibangkitkan dalam subprogram tidak dikendalikan di bahagian pengendalian pengecualian subprogram, maka pengecualian akan disebarkan ke blok panggilan.
- Kedua-duanya boleh mempunyai seberapa banyak parameter yang diperlukan.
- Kedua-duanya dianggap sebagai objek pangkalan data dalam PL / SQL.
Prosedur Vs. Fungsi: Perbezaan Utama
| Prosedur | Fungsi |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
Fungsi terbina dalam PL / SQL
PL / SQL mengandungi pelbagai fungsi terbina dalam untuk bekerja dengan rentetan dan jenis data tarikh. Di sini kita akan melihat fungsi yang biasa digunakan dan penggunaannya.
Fungsi Penukaran
Fungsi terbina dalam ini digunakan untuk menukar satu jenis data ke jenis data yang lain.
| Nama Fungsi | Penggunaan | Contohnya |
|---|---|---|
| TO_CHAR | Menukar jenis data lain ke jenis data watak | TO_CHAR (123); |
| TO_DATE (rentetan, format) | Menukar rentetan yang diberikan hingga kini. Rentetan harus sesuai dengan format. | TO_DATE ('2015-JAN-15', 'YYYY-MON-DD'); Keluaran: 15/1/2015 |
| TO_NUMBER (teks, format) | Menukar teks ke jenis nombor dari format yang diberikan. Informat '9' menunjukkan bilangan digit | Pilih TO_NUMBER ('1234', '9999') dari dua; Keluaran: 1234 Pilih TO_NUMBER ('1,234.45', '9,999.99') dari dua; Keluaran: 1234 |
Fungsi Rentetan
Ini adalah fungsi yang digunakan pada jenis data watak.
| Nama Fungsi | Penggunaan | Contohnya |
|---|---|---|
| INSTR (teks, rentetan, permulaan, kejadian) | Memberi kedudukan teks tertentu dalam rentetan yang diberikan.
| Pilih Instr ( 'KAPAL TERBANG', 'E', 2,1) dari dual Output : 2 Pilih Instr ( 'KAPAL TERBANG', 'E', 2,2) dari dual Output: 9 (2 nd berlakunya sesuatu E) |
| SUBSTR (teks, permulaan, panjang) | Memberi nilai substring tali utama.
| pilih substr ('airplane', 1,7) dari dual Output : aeropla |
| UPPER (teks) | Mengembalikan huruf besar teks yang disediakan | Pilih bahagian atas ('guru99') dari dua; Keluaran : GURU99 |
| RENDAH (teks) | Mengembalikan huruf kecil teks yang disediakan | Pilih yang lebih rendah ('kapal terbang') dari dua; Keluaran : kapal terbang |
| INITCAP (teks) | Mengembalikan teks yang diberikan dengan huruf awal dengan huruf besar. | Pilih ('guru99') dari dua Keluaran : Guru99 Pilih ('cerita saya') dari Keluaran dua : Kisah Saya |
| PANJANG (teks) | Mengembalikan panjang rentetan yang diberikan | Pilih PANJANG ('guru99') dari dua; Keluaran : 6 |
| LPAD (teks, panjang, pad_char) | Menempelkan tali di sebelah kiri untuk panjang yang diberikan (rentetan total) dengan watak yang diberikan | Pilih LPAD ('guru99', 10, '$') dari dua; Keluaran : $$$$ guru99 |
| RPAD (teks, panjang, pad_char) | Masukkan tali di sebelah kanan untuk panjang yang diberikan (rentetan total) dengan watak yang diberikan | Pilih RPAD ('guru99', 10, '-') dari dua Keluaran : guru99 ---- |
| LTRIM (teks) | Potong ruang putih utama dari teks | Pilih LTRIM ('Guru99') dari dua; Keluaran : Guru99 |
| RTRIM (teks) | Memotong ruang putih yang tertinggal dari teks | Pilih RTRIM ('Guru99') dari dua; Keluaran ; Guru99 |
Fungsi Tarikh
Ini adalah fungsi yang digunakan untuk memanipulasi dengan tarikh.
| Nama Fungsi | Penggunaan | Contohnya |
|---|---|---|
| ADD_MONTHS (tarikh, bilangan bulan) | Menambah bulan yang diberikan hingga tarikh | ADD_MONTH ('2015-01-01', 5); Keluaran : 05/01/2015 |
| SYADAT | Mengembalikan tarikh dan masa pelayan semasa | Pilih SYSDATE dari dua; Keluaran : 10/4/2015 2:11:43 PTG |
| PERJALANAN | Pusingan pemboleh ubah tarikh ke nilai yang mungkin lebih rendah | pilih sysdate, TRUNC (sysdate) dari dual; Keluaran : 10/4/2015 2:12:39 PM 10/4/2015 |
| BULAN | Membundarkan tarikh ke had terdekat sama ada lebih tinggi atau lebih rendah | Pilih sysdate, ROUND (sysdate) dari dual Output : 10/4/2015 2:14:34 PM 10/5/2015 |
| BULAN_BETWEEN | Mengembalikan bilangan bulan antara dua tarikh | Pilih MONTHS_BETWEEN (sysdate + 60, sysdate) dari dua Keluaran : 2 |
Ringkasan
Dalam bab ini, kita telah mempelajari perkara berikut.
- Cara membuat Prosedur dan pelbagai cara memanggilnya
- Cara membuat Fungsi dan pelbagai cara memanggilnya
- Persamaan dan perbezaan antara Prosedur dan Fungsi
- Parameter dan KEMBALI terminologi umum dalam subprogram PL / SQL
- Fungsi terbina dalam biasa dalam Oracle PL / SQL