Stored Procedure
Topik
1.
Konsep dan
struktur strored procedure tanpa dan dengan parameter
2.
Manajemen stored
procedure (Create, Create Or Replace, Drop)
Referensi
1. Feuerstein, Steven, and Bill Pribyl. Oracle PL/SQL
Programming, 5th Edition. Sebastopol : O'Reilly Media, 2009.
2. Srivastava, Tulika, and Glenn Stokol. Oracle Database
10g: Develop PL/SQL Program Units 2nd Edition. Boston: Oracle Publisher, 2006.
3. Urman, Scott, Ron Hardman, and Michael Laughlin.
Oracle Database 10g PL/SQL Programming. Boston: McGraw‐Hill, 2004.
4. Rosenzweig, Benjamin and Elena Silvestrova Rakhimov.
Oracle PL/SQL by Example, 4th Edition. Boston:Addison-Wesley.
Kompetensi
Utama
1. Mampu membuat dan mengeksekusi Stored Procedure dengan
parameter.
2. Mampu
menjalankan dan memanfaatkan stored procedure.
1. Pengantar
Store Procedure
1.1 Pengenalan Store Procedure
pada Oracle
Stored Procedure merupakan modul
program yang melakukan satu atau lebih aksi. Stored Procedure tidak membutuhkan
nilai balikan. Stored procedure disimpan secara permanen dalam database.
Ilustrasi dapat dilihat pada gambar berikut:
1.2 Sintax
Store Procedure
a. Procedure tanpa Parameter
CREATE OR REPLACE PROCEDURE name AS [local
declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers] END [name];
b.
Procedure
dengan Parameter
CREATE OR REPLACE PROCEDURE name
[(parameter1
[mode] datatype1, Parameter2 [mode] datatype2, ...)]
IS|AS
[local declarations] BEGIN
executable statements [EXCEPTION
exception handlers] END [name];
1.3 Perintah Memanggil Store
Procedure
Perintah untuk memanggil procedure
adalah sebagai berikut:
1.
Menggunakan
anonymous block
BEGIN
Procedure_name;
END;
2.
Menggunakan
perintah execute
Execute procedure_name;
1.4 Perintah
Menampilkan Seluruh Procedur dalam Schema
SELECT object_name
FROM user_objects
WHERE object_type = 'PROCEDURE';
1.5 Pengenalan Parameter
Parameter disebut juga bentuk spesial
dari lokal variabel, dideklarasikan setelah nama subprogram (prosedur) pada
PL/SQL Header. Fungsi utamanya sebagai jembatan komunikasi antara pemanggil
prosedur dengan subprogram dari prosedur itu sendiri. Penggunaan parameter
hampir sama seperti variabel local akan tetapi bergantung pada mode dalam
pendeklarasiannya, antara lain :
a.
IN
(default jika mode tidak dituliskan) menyediakan nilai yang bisa
diproses didalam subprogram
b.
OUT mengembalikan nilai pada pemanggil prosedur
c.
IN OUT menyediakan nilai masukan dari pemanggil
yang memungkinkan juga untuk dikembalikan keda pemanggil prosedur setelah
dimodifikasi di dalam subprogram
Formal
Parameter merupakan parameter yang dideklarasikan pada isi (body) dari
subprogram sebuah prosedur. Sedangkan Actual Parameter merupakan parameter
yang meneruskan informasi menuju prosedur jadi actual parameter terletak pada
pemanggil prosedur.
2. Latihan
Schema HR merupakan bagian dari
schema contoh yang sudah ada pada saat pertama kali kita melakukan instalasi
oracle. Pada praktikum ini dan seterusnya kita akan menggunakan schema HR untuk
mengerjakan latihan soal. Adapun tabel-tabel yang ada pada schema HR adalah
sebagai berikut:
REGIONS
|
:
|
merupakan tabel yang berisi data
region/ wilayah seperti Americas, Asia, dan lain-lain.
|
COUNTRIES
|
:
|
merupakan tabel yang berisi data
negara yang berhubungan dengan data wilayah.
|
LOCATIONS
|
:
|
merupakan alamat lengkap dari
kantor, gudang, atau tempat produksi dari perusahaan di suatu negara.
|
DEPARTMENTS
|
:
|
merupakan tabel yang berisi data
detail department tempat pegawai bekerja. Tabel ini juga berelasi dengan
tabel employees dalam bentuk penempatan pegawai dan manajer dari department
tersebut.
|
EMPLOYEES
|
:
|
merupakan tabel yang menyimpan data
pegawai yang ada di perusahaan.
|
JOB
|
:
|
merupakan tabel yang menyimpan
detail data jenis-jenis job/ posisi dari pegawai
|
JOB_HISTORY
|
:
|
merupakan tabel yang menyimpan data
riwayat posisi pegawai.
|
2.1 Latihan 1
Buatlah stored procedure dengan nama
hello_world, stored procedure halo dunia akan menampilkan kalimat “halo
dunia!!!” ketika dijalankan!
Solusi :
Pengamatan :
1.
Jalankan
stored procedure hello_world menggunakan anonymous block!
2.
Buatlah
stored procedure lagi dengan nama hello_world yang akan memunculkan teks “Halo
duniaku yang indah!!!”, buat stored procedure tersebut menggunakan perintah
CREATE, jelaskan apa yang terjadi dan berikan solusinya!
3.
Jalankan
DROP PROCEDURE [nama stored procedure] dan ulangi langkah nomor 1, jelaskan apa
yang terjadi!
2.2 Latihan 2
Buatlah stored procedure yang bernama
VIEW_EMPLOYEES yang akan menampilkan seluruh data di tabel country!
Solusi :
2.3 Latihan 3
Buatlah prosedur bernama
KENAIKAN_GAJI yang digunakan untuk memodifikasi data pegawai dengan menaikan gaji pegawai dengan nilai
masukan ID pegawai dan berapa persen kenaikan gajinya.
Solusi :
Pengamatan :
1.
Apa
yang terjadi jika perintah mode “IN” pada pendeklarasian parameter dihapus?
2.
Jelaskan
apa yang terjadi jika prosedur penurunan gaji dipanggil dengan menjalankan
perintah PENURUNAN_GAJI(334,5)?
2.4 Latihan 4
Buatlah prosedur bernama CARI_BENUA
yang digunakan untuk menampilkan nama benua (REGION) dari sebuah Negara
(COUNTRY) yang menjadi masukan dari prosedur.
Solusi :
Jalankan procedure menggunakan
anonymous blok berikut ini :
Pengamatan :
1.
Apa
yang terjadi jika pernyataan “a.REGION_ID=b.REGION_ID” pada query prosedur
CARI_BENUA dihilangkan ?
2.
Gunakan
variabel “a” pada anonymous block yang berfungsi untuk menampung nama Negara
kemudian tentukan variabel mana saja yang termasuk dalam parameter actual
maupun formal?
2.5 Latihan 5
Buatlah prosedur untuk menghitung nilai pangkat dari sebuah
bilangan masukan!
Jalankan procedure menggunakan
anonymous blok berikut ini :
Pengamatan :
1.
Bisakah
prosedur PANGKAT diatas, diakses menggunakan perintah EXECUTE? Jelaskan!
2.
Jika
INPUT_BILANGAN bernilai minus, tentukan keluaran dari nilai faktorialnya?
Jelaskan mengapa demikian?
3. STUDI
KASUS
1.
Buatlah
stored procedure tanpa parameter untuk menampilkan negara-negara yang ada di
region “Asia” dan “Middle East and Africa”!
2.
Ubahlah
anonymous block pada latihan 4 agar nilai negara bisa diinput melalui keyboard!
3.
Buatlah
prosedur untuk menampilkan nama pegawai (FIRST_NAME , LAST_NAME) dan gaji
(SALARY) dimana gaji dari pegawai tersebut lebih besar dari :BILANGAN_INPUT
(BIND VARIABLE) dari tabel pegawai (EMPLOYEES) (*hint : gunakan kursor
eksplisit)
Tidak ada komentar:
Posting Komentar