STORE PROCEDURE

haiii, kali ini aku akan membahas tentang store prosedure :

1.   STORE PROCEDURE 

Store Procedure DB2 SQL For AS/400 memungkinkan sebuah aplikasi SQL mendefinisikan dan kemudian memanggil  prosedur melalui pernyataan SQL. Store procedure dapat digunakan baik aplikasi DB2 SQL  for AS/400 terdistribusi dan tidak terdistribusi. Satu keuntungan besar dalam menggunakan  store procedure adalah bahwa untuk aplikasi terdistribusi, eksekusi satu perintah CALL pada aplikasi dapat 

membentuk sejumlah pekerjaan pada server aplikasi. 

Kita dapat mendefinisikan sebuah prosedur seperti sebuah prosedur SQL atau sebuah prosedure eksternal. Prosedure eksternal dapat mendukung bahasa pemrograman tingkat tinggi (kecuali program dan prosedure System/36 *) atau prosedure REXX. 

Prosedure tersebut tidak harus berisi pernyataan SQL, tetapi mungkin saja berisi pernyataan (statement) SQL. Sebuah prosedure SQL didefinisikan secara keseluruhan di SQL, dan dapat mengandung pernyataan SQL yang mencakup pernyataan control SQL.  

Pembuatan store procedure membutuhkan pemahaman akan hal-hal berikut : 

♦  Store procedure didefinisikan melalui pernyataan CREATE PROCEDURE 

♦  Store procedure dipanggil melalui pernyataan CALL 

♦  Melewatkan parameter sesuai dengan prosedure yang dipanggil 

♦  Metode untuk mengembalikan status selesai ke program pemanggilan prosedur 

Kita dapat mendefinisikan store  procedure menggunakan pernyataan CREATE 

PROCEDURE. Pernyataan CREATE PROCEDURE menambah prosedur dan definisi 

parameter ke tabel-tabel catalog,  SYSPROCS, dan SYSPARMS. Pendefinisian ini 

kemudian dapat diakses dengan pernyataan SQL CALL pada sistem itu.  

2  MENDEFINISIKAN PROSEDUR EKSTERNAL 

Pernyataan CREATE PROCEDURE untuk prosedur eksternal terdiri dari : 

♦  Nama Prosedur 

♦  Definisi parameter dan atribut-atributnya 

♦  Pemberian informasi lain tentang prosedur yang akan digunakan system ketika 

prosedur dipanggil  

Perhatikan contoh berikut : 

EXEC SQL  CREATE PROCEDURE P1 

  (INOUT PARM1 CHAR(10)) 

  EXTERNAL NAME MYLIB.PROC1 

  LANGUAGE C 

GENERAL WITH NULLS ; 

Pernyataan CREATE PROCEDURE di atas maksudnya adalah sebagai berikut: 

♦  Nama prosedur P1 

♦  Mendefinisikan sebuah parameter yang digunakan baik sebagai parameter input dan output. Parameter tersebut adalah sebuah  field character dengan panjang 10. 

Parameter dapat didefinisikan bertipe IN, OUT atau INOUT. Tipe parameter 

menentukan nilai untuk parameter ketika dimasukkan ke dan dari prosedur. 

♦  Mendefinisikan nama program yang berhubungan dengan prosedur tersebut, yaitu 

PROC1 dalam MYLIB. MYLIB.PROC1  adalah program yang dipanggil ketika prosedur dipanggil dengan pernyataan CALL. 

♦  Menunjukkan bahwa prosedur P1 (Program MYLIB.PROC1) ditulis dalam bahasa C. Bahasa pemrograman sangat penting jika ia memberi dampak terhadap tipe parameter yang dilewatkan. Hal ini  juga memberi dampak bagaimana parameter dilewatkan ke prosedur (contoh, untuk  prosedur ILE C, sebuah terminator NULL dilewatkan dalam bentuk parameter character, graphic, date, time dan timestamp). 

♦  GENERAL WITH NULLS menunjukkan bahwa parameter untuk prosedur boleh berisi nilai NULL.  

3  MENDEFINISIKAN PROSEDUR SQL 

Pernyataan CREATE PROCEDURE untuk prosedur SQL : 

♦  Nama prosedur 

♦  Mendefinisikan parameter dan atribute-atributenya

♦ Menyediakan informasi lain tentang prosedur yang akan digunakan ketika prosedur dipanggil

Pertimbangkan contoh sederhana berikut yang mengambil nomor pegawai dan rata-rata sebagai parameter input dan mengubah gaji pegawai itu :

EXEC SQL CREATE PROCEDURE UPDATE_SALARY_1

(IN EMPLOYEE_NUMBER CHAR(10),

IN RATE DECIMAL(6,2))

LANGUAGE SQL MODIFIES SQL DATA

UPDATE CORPDATA.EMPLOYEE

SET SALARY = SALARY * RATE

WHERE EMPNO = EMPLOYEE_NUMBER ;

Pernyataan CREATE PROCEDURE di atas maksudnya adalah :

♦ Mendefinisikan parameter EMPLOYEE_NUMBER yang berfungsi sebagai parameter input dan bertipe character dengan panjang 6 dan parameter RATE sebagai parameter input dan bertipe decimal.

♦ Menunjukkan bahwa prosedur adalah sebuah prosedur SQL yang mengubah data SQL.

♦ Mendefinisikan badan prosedur sebagai sebuah pernyataan tunggal UPDATE. Ketika prosedur dipanggil, pernyataan UPDATE dijalankan menggunakan nilai yang disimpan dalam EMPLOYEE_NUMBER dan RATE.

Sebagai pengganti pernyataan UPDATE, logika dapat ditambahkan ke prosedur SQL dengan menggunakan pernyataan kontrol SQL. Pernyataan kontrol SQL berisi hal-hal berikut :

♦ Pernyataan penugasan

♦ Pernyataan CALL

♦ Pernyataan CASE

♦ Compound statement

♦ Pernyataan FOR

♦ Pernyataan IF

♦  Pernyataan LOOP 

♦  Pernyataan REPEAT 

♦  Pernyataan WHILE 

4  PEMANGGILAN STORED PROCEDURE 

Pernyataan CALL memanggil sebuah stored procedure. Dalam pernyataan CALL, nama dari  stored procedure dan argumennya ditetapkan. Argumen dapat berupa konstanta, register khusus, atau host variable. External Stored Procedure yang ditentukan dalam pernyataan CALL tidak perlu memiliki hubungan dengan pernyataan CREATE PROCEDURE. Program yang dibuat dengan prosedur SQL hanya dapat dipanggil dengan menyebutkan nama prosedur yang  ditentukan pada pernyataan CREATE PROCEDURE. Ada tiga tipe pernyataan CALL yang membutuhkan pengalamatan sejak DB2 SQL for AS/400 memiliki perbedaan aturan untuk masing-masing tipe, yaitu : 

♦  Pernyataan CALL yang  dynamic  atau  embedded dimana sebuah prosedur yang 

didefinisikan telah ada 

♦  Pernyataan CALL embedded dimana tidak ada prosedur yang telah didefinisikan 

♦  Pernyataan CALL dynamic dimana tidak ada CREATE PROCEDURE 

Catatan : Dynamic di sini berdasarkan pada : 

♦  Sebuah persiapan secara dinamik dan dijalankan pernyataan CALL 

♦  Sebuah Pernyataan CALL dikeluarkan dalam sebuah lingkungan yang interaktif 

(contoh melalui STRSQL atau Query Manager) 

♦  Sebuah pernyataan CALL dijalankan  dalam sebuah pernyataan EXECUTE 

IMMEDIATE. 

5  MELEWATKAN PARAMETER yang  BIASA DIGUNAKAN untuk STORED 

PROCEDURE 

Pernyataan CALL dapat melewatkan argumen untuk program yang ditulis dalam seluruh host language dan prosedur REXX yang telah didukung. Masing-masing bahasa mendukung perbedaan tipe data yang disesuaikan  di dalamnya.  Jenis data SQL terdapat dalam kolom paling kiri dari tabel yang mengikutinya. Kolom lain dalam baris tersebut berisi sebuah indikator dimana tipe datanya didukung oleh tipe parameter untuk particular language. Jika kolom mengandung sebuah dash (-), tipe data tidak didukung sebagai sebuah tipe parameter untuk bahasa tersebut. Sebuah deklarasi host variable menunjukkan bahwa  DB2 SQL for AS/400 mendukung tipe data ini sebagai sebuah parameter di bahasa ini. Deklarasi itu menunjukkan bagaimana  host variable harus dideklarasikan untuk menerima dan diatur oleh prosedur secara benar.

Cara Membuat Stored Procedure di DB2 dengan IBM Data Studio :

Untuk membuat Stored Procedure dengan menggunakan IBM Data Studio dapat mengikuti langkah- langkah berikut
1. Buka IBM Data Studio
2. Pilih file -> new -> data development project
3. Beri nama proyek, misalnya Project2
4. Pilih “Use an existing connection”. Klik Finish
5. di Data Project Explorer akan muncul nama Project2
6. Klik kanan -> new -> Stored Procedure
7. Beri nama Stored Procedure, misalnya PROC_3. Pilih next
8. Pada statement detail, ganti isinya dengan query yang anda inginkan. Klik next
9. Klik next
10. Isi specific name. Ini untuk nama alias dari Stored Procedure.
11. Finish

Berikut merupakan hasil yang digenerate oleh IBM Data Studio
CREATE PROCEDURE PROC_3 ( )
DYNAMIC RESULT SETS 1
————————————————————————
– SQL Stored Procedure
————————————————————————
P1: BEGIN
– Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT *
FROM EMPLOYEE;

– Cursor left open for client application
OPEN cursor1;
END P1

Untuk dapat menjalankannya, klik kanan di PROC_3 -> Deploy. Kemudian Run.

Atau bisa juga dijalankan melalui DB2cmd. Kemudian ketik
“db2 connect to sample” kemudian “db2 call proc_3()”