Stored Procedure (create, calling SP dari Oracle dan dari JAVA) Oleh : Tikaa (http://black9innocent.wordpress.com/)
Mari mengingat stored procedure lagi #Demi belajar :D Disini saya akan mencoba menjelaskan sedikit tentang stored procedure dan cara pemanggilannya dari class Java. Stored Procedure adalah suatu modul progam yang berisi kumpulan pernyataan SQL untuk melaksanakan kegiatan atau tugas tertentu dan dapat dipanggil oleh program lain atau melalui SQL prompt yang disimpan di server. Stored procedure ditulis dalam bentuk suatu script. Stored procedure memungkinkan business logic disimpan didalam database. Dengan menggunakan stored procedure maka aplikasi akan terdistribusi. Eksekusi satu perintah CALL pada aplikasi dapat membentuk sejumlah pekerjaan pada server aplikasi. Store procedure memiliki kemampuan antara lain : Menerima parameter input dan mengembalikan beberapa nilai parameter output dari procedure yang dipanggil. Mengandung statement pemrograman yang melakukan operasi terhadap database termasuk memanggil store procedure lain. Memberikan status yang mengindikasikan keberhasilan atau kegagalan eksekusi store procedure. Keuntungan pemakaian store procedure adalah : Merupakan pemrograman modular. Store procedure dibuat sekali dan dapat dipakai berulang untuk komputasi yang sama. Memungkinkan eksekusi lebih cepat. Ketika pertama kali dijalankan store procedure akan tersimpan dalam memori. Sehingga memungkinkan eksekusi berikutnya lebih cepat. Mengurangi trafik jaringan client/server. Barisan kode transact SQL yang seharusnya dilewatkan jaringan, akan digantikan dengan statement tunggal pemanggilan store procedure. Kompilasi dilakukan di database (kadang disebut “pre-compilation”) sehingga mengurangi traffic. Dapat digunakan sebagai mekanisme security Pengguna dimungkinkan mengeksekusi Store procedure, walau mungkin tidak dapat mengeksekusi procedure didalamnya secara langsung. Adanya pemisahan antara database akses logic dengan application logic, hal ini berakibat program aplikasi dapat menjadi lebih sederhana dan lebih ringkas. Kekurangannya, karena semua query berjalan di server maka akibatnya server menjadi lebih terbebani.
Create Stored Procedure di Database Berikut contoh stored procedure untuk menampilkan tanggal hari ini. CREATE OR REPLACE PROCEDURE today_is AS BEGIN -- menampilkan current system date dalam long format DBMS_OUTPUT.PUT_LINE( 'Today is ' || TO_CHAR(SYSDATE, 'DL') ); END today_is; /
Keterangan : CREATE OR UPDATE
: CREATE perintah untuk membuat stored procedure. Seandainya stored procedure telah ada maka stored procedure akan digantikan dengan stored procedure yang baru dibuat.
today_is
: Nama stored procedure.
AS
: AS menyatakan deklarasi. Tulis variable setelah AS (jika ada).
BEGIN.. END
: Penempatan business logic atau operasi lainnya.
DBMS_OUTPUT.PUT_LINE : Untuk menampilkan result.
Menjalankan Stored Procedure begin system.today_is(); end;
Result yang ditampilkan Today is Thursday, January 12, 2012
Contoh Stored Procedure(SP) untuk Select, Insert, Update, Delete Row di table 1. Create Table Sebelum membuat stored procedure, pertama-tama coba buat table. Sebagai contoh, buatlah 2 table, testSystem dan testPayment. Table testSystem create table testSystem ( systemNo varchar(20), invoiceNo varchar(20), seller varchar(20), ); insert into testSystem(systemNo, invoiceNo, seller) values('SY001', 'IV001', 'seller1'); insert into testSystem(systemNo, invoiceNo, seller) values('SY002', 'IV002', 'seller2');
Table testPayment create table testPayment ( paymentNo varchar(20), paymentSource varchar(20), systemNo varchar(20) ) insert into testPayment(paymentNo, paymentSource, systemNo) values('PY001', 'Buyer A', 'SY001'); insert into testPayment(paymentNo, paymentSource, systemNo) values('PY001', 'Buyer B', '12323231', 'SY002'); insert into testPayment(paymentNo, paymentSource, systemNo) values('PY002', 'Buyer1', 'SY001');
2. SP Select Menampilkan system no dan payment no. --create stored procedure --untuk comment gunakan (--) --jangan lupa akhiri setiap statement dengan (;) --sysNo = parameter yang di input CREATE OR REPLACE PROCEDURE cekTesting(sysNo IN varchar2) AS --declare variable dan tipe datanya pPaymentNo varchar2(20); pSystemNo varchar2(20); dataNotFound EXCEPTION; --untuk exception begin --menampilkan invoiceNo
SELECT systemNo, paymentNo INTO pSystemNo, pPaymentNo from testPayment where systemNo = sysNo; --sesuai dengan sysNo yang di input --kondisi untuk menangkap exception IF pInvoiceNo IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('System No ' || pSystemNo || ' - ' || 'PaymentNo '|| ' - ' || pPaymentNo); ELSE RAISE dataNotFound; END IF; end cekTesting;
--calling stored procedure begin cekTesting('SY001'); --SY001 = systemNo yang diinput end;
3. SP Remove Menghapus row dari table testSystem. --create stored procedure CREATE OR REPLACE PROCEDURE removeSysNo (sysNo varchar) AS tot NUMBER; BEGIN DELETE FROM testSystem WHERE testSystem.systemNo = removeSysNo.sysNo; tot := tot - 1; END;
--calling stored procedure begin removeSysNo('SY002'); end;
4. SP Update Update nilai seller di table testSystem --create stored procedure CREATE OR REPLACE PROCEDURE updateSeller (sysNo varchar, seller1 varchar) AS BEGIN UPDATE testSystem SET seller = seller1 WHERE testSystem.systemNo = sysNo; END;
--calling stored procedure BEGIN system.updateSeller('SY001', 'Seller Coba'); END; atau call updateSeller('SY001', 'Seller Coba');
5. SP Insert Insert hasil join table testSystem dengan testPayment ke table baru (table test). Untuk itu, kita harus create table test terlebih dahulu. -create table test yang akan di-insert oleh SP create table test ( systemNo varchar(20), invoiceNo varchar(20), seller varchar(20), paymentNo varchar(20), paymentSource varchar(20) )
--create stored procedure CREATE OR REPLACE PROCEDURE InsertSystemNo (sysNo varchar) AS pSystemNo varchar(20); pInvoiceNo varchar(20); pSelller varchar(20); pPaymentNo varchar(20); pPaymentSource varchar(20); BEGIN select s.systemNo, s.invoiceNo, s.SELLER, pay.paymentNo, pay.paymentSource INTO pSystemNo, pInvoiceNo, pSelller, pPaymentNo, pPaymentSource from system.testSystem s join system.testPayment pay on s.systemNo = pay.systemNo where s.systemNo = sysNo; INSERT INTO TEST VALUES (pSystemNo, pInvoiceNo, pSelller, pPaymentNo, pPaymentSource); END;
--calling stored procedure BEGIN InsertSystemNo('SY002'); END;
Calling Stored Procedure dari Java Sebelum menulis syntax untuk memanggil stored procedure, hal pertama yang harus dimasukkan ke project java adalah library ojdbc.jar. Disini, kita mencoba untuk memanggil stored procedure updateSeller. Package Test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.sql.Types; public class CallStoredProcedureFromDB { public static void main(String[] args) { try { Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@Tikaa-PC:1521:xe", "uname", "password"); //call stored procedure update seller // terdiri dari 2 parameter, makanya ada 2 ? CallableStatement csmt = conn.prepareCall("{call updateSeller(?,?)}"); csmt.setString(1, "SY002"); // set parameter yang bertipe String csmt.setString(2, "Seller dua"); // jika tipenya int, maka setInteger csmt.execute(); // jalankan statement // Get database metadata //DatabaseMetaData dbmd = conn.getMetaData(); // // Get all stored procedures in any schema and catalog //ResultSet resultSet = dbmd.getProcedures(null, null, "%"); // //Get stored procedure names from the result set //while (resultSet.next()) { //String procName = resultSet.getString(3); //System.out.println(procName); //} } catch (Exception e) { e.printStackTrace(); } } }
