part 14 store proceduree

www.rahmadani.net 1

Store ProcedureStore ProcedurePertemuan ke-14

www.rahmadani.net 2

PengertianPengertian……

• Stored procedure program yang disimpan dalamdatabase dan dikompilasi bila digunakan– Dalam Oracle, dapat ditulis dalam PL / SQL atau Java– Dalam SQL Server, dapat ditulis dalam Transact-SQL atau CLR

• Stored Procedure yang disimpan dapat menerimaparameter input dan dapat mengembalikan hasil

• Stored Procedure yang disimpan dapat dipanggil dari– Program yang ditulis dalam bahasa standar, misalnya: Java, C #– Scripting bahasa, misalnya: JavaScript, VBScript– SQL command prompt, misalnya: sqlcmd, SQL Plus, Query

Analyzer

www.rahmadani.net 3

Penggunaan Stored ProcedurePada saat SQL Server 2005 dijalankan, Bahasa T-SQLadalah hal utama yang digunakan sebagai penghubungantara Aplikasi dengan Sistem database.

Saat menggunakan Bahasa T-SQL, ada dua metode yangdijalankan untuk mnyeyimpan dan menjalankan StoreProcedure.1. Aplikasi mengirim request dan database meresponse

hasil berupa store procedure.2. Program Sebagai Store Procedure pada SQL Server

dan buat aplikasi untuk menjalankan procedure danmemproses hasil

www.rahmadani.net 4

Ilustrasi.. Request;

Response;

www.rahmadani.net 5

Lanjt….

www.rahmadani.net 6

Stored Procedure vs Procedure

Persamaan dengan prosedur dlm pemrograman:– Menerima parameter input dan mengembalikan

beberapa nilai dalam bentuk parameter output kepemanggil prosedure.

– Berisi statement pemrograman utk operasi dlmdatabase, termasuk memanggil prosedur lainnya.

– Mengembalikan nilai status ke pemanggil proseduruntuk menunjukkan keberhasilan atau kegagalan (danalasan untuk kegagalan).

www.rahmadani.net 7

Tipe Stored Procedure

• System Stored Procedure:– Nama dimulai dengan sp_ atau xp_ (extended stored procedur

akan dihilangkan)– Dibuat dalam database master– Tujuannya untuk pengaplikasian dalam database apapun– Sering digunakan oleh sysadmin

• Local Stored Procedure:– Ditetapkan di database lokal– Nama sering dimulai dengan pr_ atau usp

• Dalam prakteknya lebih baik gunakan nama yang mudah:– Tanpa prefix– Sesuaikan dengan yang ada di aplikasi

www.rahmadani.net 8

Kelebihan Stored Procedure(1)

• Keamanan lebih– stored procedure selalu tersimpan pada

database server– Memberikan grant user permission utk

menjalankan SP

• Mengurangi lalu lintas jaringan– SP dapat mengurangi query SQL panjang

menjadi satu baris yang ditransmisikansehingga mengurangi lalu lintas client-server.

www.rahmadani.net 9

Kelebihan Stored Procedure(2)

• Waktu Eksekusi lebih cepat– Bila perlu T-SQL yang banyak dan berulang-ulang,

maka SP bisa lebih cepat drpd batch T-SQL code. SPdicompile sekali, disimpan dalam memory dandigunakan untuk pemanggilan berikutnya (tidak perludikompilasi ulang). SQL dapat dioptimasikan olehDBMS compiler

• Berbagi-pakai kode modular berimbas pada:– Mengurangi pekerjaan berlebih – penggunaan ulang

kode secara efisien– Standarisasi pemrosesan– Spesialisasi antara developer

www.rahmadani.net 10

Dasar Stored Procedure


Membuat Stored ProcedureSintaks:CREATE PROCEDURE NamaProsedur ASDeklarasi_Variable.. .. .... .. ..

RETURNContoh:CREATE PROCEDURE pr_honor ASSELECT namainstruktur, honor FROMinstrukturRETURN


Menjalankan Stored Procedure

• dengan EXECEXEC pr_honorEXEC sp_help

• dengan EXECUTEEXECUTE pr_honorEXECUTE sp_help

• langsung nama prosedurpr_honorsp_help


Mengkoreksi Sebuah Prosedur

Statement ALTER harus digunakan;ALTER PROCEDURE pr_honor ASselect inisial,namainstruktur, honorfrom instruktur

RETURN


Menghapus Stored Procedure

• digunakan statement DROPSintaks:DROP PROCEDURE NamaProsedurContoh:DROP PROCEDURE pr_honorDROP PROCEDURE uspHitungGaji


Mengubah Prosedur TanpaMenggunakan ALTER

Cek prosedur ada, bila ada di drop, kemudian baru create;USE mydbIF EXISTS (SELECT name FROM sysobjects

WHERE nama= 'pr_honor' AND type = 'P')DROP PROCEDURE pr_honor

GOCREATE PROCEDURE pr_honorASSELECT inisial, namainstruktur, honorFROM instruktur

GO


Mengubah Nama Stored Procedure

Gunakan sintaks SP_RENAMEContoh:SP_RENAME pr_honor, pr_honor2


Parameter..


Maksudnya..• Prosedur dapat mempunyai parameter berupa

variable yang disuplai oleh program yangmemanggilnya

• Sintaks parameter– @param as datatype = default [OUT/OUTPUT]

Contoh:CREATE PROC pr_honor (@p1 int, @p2 char(16), .. )ASDeklarasi_Variable

.. .. ..

.. .. ..


Model Parameter• Input parameter

– Digunakan untuk memberikan nilai dari pemanggil kedalam SPContoh:CREATE PROC pr_honor (@p1 INT) AS …

• Output parameter– Digunakan untuk mengembalikan nilai non-recordset

dari SP ke pemanggil.– Misalnya SP untuk insert, mengembalikan nilai hasil

identityContoh:

CREATE PROC pr_honor (@p1 INT, @p2 CHAR(16),@pout1INT OUT, @pout2 CHAR(1) OUTPUT) AS …


Output Parameter

• Nilai parameter bisa diolah pada programprosedur dan untuk kemudian parametertersebut dapat diubah dan diberikan nilainyapada program yang memanggilnya.

CREATE PROC m (@p1 int, @p2 int, @h int OUTPUT)

ASSELECT @h= @p1 * @p2


Eksekusi Output Parameter

Contoh:DECLARE @hsl INTEXEC m 10, 20, @hsl OUTPUTPRINT 'hasil = ' + str (@hsl)

– Pada contoh ada 3 parameter, yaitu p1 dan p2sebagai input parameter dan h sebagai outputparameter.

– Saat eksekusi, p1 adalah 10, p2 adalah 20 dan hslmerupakan hasil perkalian.


Parameter dgn Default Value

• Default value– Untuk menghindari null karena nilai tidak diberikan– Merupakan nilai default yang otomatis diberikan bila

tidak dispesifikasikan ketika pemanggilan SPContoh:

CREATE PROC pr_honor (@p1 INT=5) ASselect nama instruktur, honor from instrukturwhere inisial=@ini

Return

• Urutan parameter– Tempatkan parameter dengan default value di akhir dari

daftar parameter untuk penggunaan fleksibel


Eksekusi Procedure Berparameter• Pada saat eksekusi, parameter diberikan

setelah nama SP• Berdasarkan nama:

EXEC pr_GetTopProducts@StartID = 1, @EndID = 10

• Berdasarkan posisi:EXEC pr_GetTopProducts 1, 10

• Leveraging Default valuesEXEC pr_GetTopProducts @EndID=10


Case Sederhana…

Pada Database MYAKADEMIK Anda,buatkan sebuat Store Procedure untukmenampilkan Jenkel Laki-laki pada TabelMahasiswa.??


Step 1; Membuat Store Procedure

1. Pada Object Explorer databaseMYAKADEMIK, buka Folder Programibilitykemudian pilih Folder Store Procedure.

2. Pada Folder Store Procedure, klik kanankemudian pilih New Store Procedure.

3. Maka akan ditampilkan template pembuatanStore Procedure yang siap diedit sesuaikebutuhan.

4. Lihat Demo…


Case 2;Membuat SP dengan Variabel via Query Analyzer

Buat sebuah SP dengan nama; LuasBidangDatardengan variabel @panjang dan @lebar dengan typedata numeric

part 14 store proceduree

Education