1 KONSTRUK PROGRAM PLSQL AS atau DECLARE Konstruk Tool EXCEPTION Konstruk Database Server PROSEDUR Danny Kriestanto Based on Oracle 9i: Program with ...
KONSTRUK PROGRAM PL/SQL IS | AS atau DECLARE BEGIN
PROSEDUR Danny Kriestanto
Konstruk Tool
EXCEPTION
Konstruk Database Server
Blok Anonim
END;
Blok Anonim
Application procedures atau Application functions
Stored Procedures atau Stored Functions
Application packages
Stored Packages
Application trigger
Database triggers
Tipe Objek
Tipe Objek
Based on Oracle 9i: Program with PL/SQL Student Guide 1
OVERVIEW MENGENAI SUBPROGRAM Subprogram adalah: Blok PL/SQL yang memiliki nama, dapat menerima parameter, dan dapat dipanggil dari ruang lingkup pemanggilnya Terdiri atas dua tipe: p
Sebuah prosedur yang melakukan sebuah action Sebuah fungsi yang menghitung sebuah nilai
Berdasarkan struktur blok PL/SQL standar Menyediakan modularity, reusability, extensibility, dan maintainability. Memungkinkan kemudahan dalam maintenance, meningkatkan performa, dan meningkatkan kejelasan kode.
STRUKTUR BLOK PADA SUBPROGRAM PL/SQL IS | AS
STRUKTUR BLOK PADA BLOK ANONIM PL/SQL DECLARE Deklarasi objek PL/SQL BEGIN (harus ada) Tentukan pernyataan executable EXCEPTION (opsional) Tentukan action yang terjadi ketika error muncul END; (harus ada)
SUBPROGRAM PL/SQL
Spesifikasi Subprogram
Bagian deklarasi BEGIN Bagian eksekusi EXCEPTION (opsional)
Bagian Tubuh subprogram
Bagian eksepsi END;
Bagian IS | AS bersifat mandatory untuk subprogram
1
5/6/2010
KEUNTUNGAN SUBPROGRAM
Maintenance mudah Modifikasi rutin secara online tanpa mengganggu user lain Modifikasi satu rutin akan mempengaruhi banyak aplikasi Modifikasi satu rutin untuk menghilangkan testing yang berulang-ulang
Meningkatkan keamanan dan integritas data Mengontrol akses tidak langsung terhadap objek database dari user yang tidak memiliiki hak akses Memastikan bahwa serangkaian action yang berhubungan dilakukan bersama-sama dengan mengarahkan aktifitas terhadap tabel-tabel yang berhubungan melalui satu jalur
Meningkatkan performa Menghindari parsing berulang-ulang terhadap banyak user dengan menggunakan area SQL yang bersifat shared. Menghindari parsing PLP/SQL pada saat run-time dengan melakukan parsing sewaktu di compile.
Meningkatkan kejelasan kode: dengan menggunakan nama identifier untuk menjelaskan action dari rutin mengurangi penggunaan komentar
APAKAH PROSEDUR ITU? Prosedur adalah tipe subprogram yang melakukan sebuah aksi. Sebuah prosedur dapat disimpan di dalam database, sebagai objek skema, untuk eksekusi berulang ulang. berulang-ulang
SINTAKS PROSEDUR
PEMBUATAN PROSEDUR
CREATE [OR REPLACE] PROCEDURE nama_prosedur [(parameter1 [mode1] tipedata1), parameter2 [mode2] tipedata2, . . .] IS | AS Blok PL/SQL;
Opsi REPLACE mengindikasikan bahwa jika prosedur tersebut sudah ada, prosedur yang lama akan di-drop dan digantikan dengan yang baru. Blok PL/SQL dimulai dengan BEGIN atau deklarasi variabel lokal dan berakhir dengan END atau END nama_prosedur.
PARAMETER FORMAL VS PARAMETER AKTUAL
Tuliskan prosedur yang akan dikerjakan Jalankan sintaks tersebut dengan iSQL*Plus atau aplikasi Oracle lain. Source code akan di-compile menjadi P code. server Keterangan: source code akan disimpan di server, tapi yang dijalankan adalah P code. Eksekusi prosedur.
MODE-MODE PARAMETER PROSEDURAL
Parameter formal: variabel dideklarasikan di dalam daftar parameter dari sebuah subprogram. CREATE PROCEDURE naik_gaji(p_id NUMBER, p_jum NUMBER) . . . END naik_gaji;
Parameter aktual: variabel atau ekspresi yang direferensikan di dalam daftar parameter dari pemanggilan subprogram Contoh: naik_gaji(v_id, 2000)
2
5/6/2010
MEMBUAT PROSEDUR DENGAN PARAMETER IN Mode Default
OUT
IN OUT
Harus dispesifikasikan
Harus dispesifikasikan
Value diberikan kepada Value dikembalikan ke subprogram pemanggilnya
Value diberikan kepada subprogram; lalu dikembalikan ke pemanggilnya
Parameter formal Variabel yang tidak yang bertindak sebagai diinisialisasi (tidak ada konstanta nilai awal)
Variabel yang diinisialisasi
Parameter aktual dapat Harus berupa variabel berupa literal, ekspresi, konstanta, atau variabel yang diinisialisasi
Harus berupa variabel
Dapat di-assign sebagai nilai default
Tidak dapat di-assign sebagai nilai default
Tidak dapat di-assign sebagai nilai default
CONTOH PARAMETER OUT
p_id SMITH
p_nama
7400
p_gaji
0.15
p_komisi
MELIHAT ISI PARAMETER OUT
p_id
176
CREATE OR REPLACE PROCEDURE naik_gaji (p_id IN employees.employee_id%TYPE) IS BEGIN UPDATE employees SET salary = salary * 1.10; WHERE employee_id = p_id; END naik_gaji; /
CONTOH PARAMETER OUT
171
CONTOH PARAMETER IN
Jalankan query tersebut untuk menciptakan prosedur dengan nama query_peg Deklarasikan variabel host, eksekusi prosedur query_peg, dan tampilkan hasil variabel global g_nama
VARIABLE g_nama VARCHAR2(25) VARIABLE g_gaji NUMBER VARIABLE g_komisi NUMBER EXECUTE query_peg(171, :g_nama, :g_gaji, :g_komisi) PRINT g_nama
CREATE OR REPLACE PROCEDURE query_peg (p_id IN employees.employee_id%TYPE, p_nama OUT employees.last_name%TYPE, p_gaji OUT employees.salary%TYPE, p_komisi OUT employees commission pct%TYPE) employees.commission_pct%TYPE) IS BEGIN SELECT last_name, salary, commission_pct INTO p_nama, p_gaji, p_komisi FROM employees WHERE employee_id = p_id; END query_peg; /
IN OUT PARAMETER Calling Environment ‘8006330575’
prosedur format_telp ‘(800)633-0575’
p_telp
CREATE OR REPLACE PROCEDURE format_telpon p (p_telp IN OUT VARCHAR2) IS BEGIN p_telp := ‘(’ || SUBSTR(p_telp,1,3) || ‘)’ || SUBSTR(p_telp,4,3) || ‘-’ || SUBSTR(p_telp,7); END format_telpon; /
3
5/6/2010
MELIHAT ISI PARAMETER IN OUT
METODE MELEWATKAN PARAMETER
VARIABLE g_telp VARCHAR2(25)
BEGIN END; / PRINT g_telp EXECUTE format_telp(:g_telp) PRINT g_telp
Posisi: daftarkan parameter aktual dalam urutan yang sama dengan parameter formal Penamaan: daftarkan parameter secara sembarang, sesuaikan dengan parameter yang bersesuaian Kombinasi: mendaftarkan beberapa parameter aktual secara posisi maupun secara penamaan
OPSI DEFAULT UNTUK PARAMETER
CONTOH MELAKUKAN PASSING PARAMETER
CREATE OR REPLACE PROCEDURE tambah_dept (p_nama IN departments.department_name%TYPE DEFAULT ‘Tidak diketahui’, p_lokasi IN departments.location_id%TYPE DEFAULT ‘1700’) IS BEGIN INSERT INTO departments(department_id, department_name, location_id) VALUES (departments_seq.NEXTVAL, p_name, p_lokasi) END tambah_dept; /
BEGIN tambah_dept; tambah_dept(‘TRAINING’,2500); tambah_dept(p_lokasi => 2400, p_nama =>’EDUCATION’); tambah_dept(p p p_lokasi => 1200); END; /
MENDEKLARASIKAN SUBPROGRAM CREATE OR REPLACE PROCEDURE keluar_emp (p_kode IN employees.employee_id%TYPE) IS PROCEDURE log_exec IS BEGIN INSERT INTO log_table(user_id, log_date) VALUES (USER, SYSDATE); END log_exec; BEGIN DELETE FROM employees WHERE employee_id = p_id; log_exec; END; /
MEMANGGIL PROSEDUR DARI SEBUAH BLOK PL/SQL ANONIM DECLARE v_id NUMBER := 163; BEGIN naik_gaji(v_id); -- memanggil prosedur COMMIT; END; /
4
5/6/2010
MEMANGGIL PROSEDUR DARI PROSEDUR LAIN CREATE OR REPLACE PROCEDURE proses_peg IS CURSOR peg_cursor IS SELECT employee_id FROM employees; BEGIN FOR peg_rec IN peg_cursor LOOP naik_gaji(peg_rec.employee_id); END LOOP; COMMIT; END proses_peg; /
MENGHAPUS PROSEDUR Menghapus stored procedure di dalam database Sintak: DROP PROCEDURE nama_prosedur
