Transcript Stored Procedure - Putra Pandu A (Hikaru Yuuki) のブログ

Computer Science, University of Brawijaya
Putra Pandu Adikara, S.Kom
Stored Procedure
Basis Data 2
Stored Procedure
 Stored procedure  program yang disimpan dalam
database 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 menerima
parameter 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
Stored Procedure vs Procedure
 Persamaan dengan prosedur dlm pemrograman:
 Menerima parameter input dan mengembalikan beberapa
nilai dalam bentuk parameter output ke pemanggil
prosedure atau batch.
 Berisi statement pemrograman utk operasi dlm database,
termasuk memanggil prosedur lainnya.
 Mengembali nilai status ke pemanggil prosedur atau batch
untuk menunjukkan keberhasilan atau kegagalan (dan
alasan untuk kegagalan).
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 (misal menggunakan
object/komponen seperti ORM)
Kelebihan Stored Procedure
 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 ditransmisikan sehingga mengurangi lalu lintas
client-server.
Kelebihan Stored Procedure
 Waktu Eksekusi lebih cepat
 Bila perlu T-SQL yang banyak dan berulang-ulang, maka
SP bisa lebih cepat drpd batch T-SQL code. SP dicompile
sekali, disimpan dalam memory dan digunakan untuk
pemanggilan berikutnya (tidak perlu dikompilasi ulang).
SQL dapat dioptimasikan oleh DBMS compiler
 Berbagi-pakai kode modular berimbas pada:
 Mengurangi pekerjaan berlebih – penggunaan ulang kode
secara efisien
 Standarisasi pemrosesan
 Spesialisasi antara developer
Dasar Stored Procedure
Membuat Stored Procedure
 Sintaks:
CREATE PROCEDURE NamaProsedur AS
Deklarasi_Variable
.. .. ..
.. .. ..
RETURN
 Contoh:
CREATE PROCEDURE pr_honor AS
SELECT namainstruktur, honor FROM
instruktur
RETURN
Menjalankan Stored Procedure
 dengan EXEC
 EXEC pr_honor
 EXEC sp_help
 dengan EXECUTE
 EXECUTE pr_honor
 EXECUTE sp_help
 langsung nama prosedur
 pr_honor
 sp_help
Mengkoreksi Sebuah Prosedur
 Statement ALTER harus digunakan
ALTER PROCEDURE pr_honor AS
select inisial, namainstruktur, honor
from instruktur
RETURN
Menghapus Stored Procedure
 digunakan statement DROP
 Sintaks:
 DROP PROCEDURE NamaProsedur
 Contoh:
 DROP PROCEDURE pr_honor
 DROP PROCEDURE uspHitungGaji
Mengubah Prosedur Tanpa Menggunakan ALTER
Cek prosedur ada, bila ada di drop, kemudian baru create
USE mydb
IF EXISTS (SELECT name FROM sysobjects
WHERE nama= 'pr_honor' AND type = 'P')
DROP PROCEDURE pr_honor
GO
CREATE PROCEDURE pr_honor
AS
SELECT inisial, namainstruktur, honor
FROM instruktur
GO
Mengubah Prosedur Tanpa Menggunakan ALTER
: SQL Server 2008
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees',
'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees AS
SET NOCOUNT ON;
SELECT LastName, FirstName, Department FROM
HumanResources.vEmployeeDepartmentHistory;
GO
Mengubah Nama Stored Procedure
Gunakan sintaks SP_RENAME
Contoh:
SP_RENAME pr_honor, pr_honor2
Parameter
Parameter
 Prosedur dapat mempunyai parameter berupa variable
yang disuplai oleh program yang memanggilnya
 Sintaks parameter
 @param as datatype = default [OUT/OUTPUT]
 Contoh:
CREATE PROC pr_honor (@p1 int, @p2 char(16), .. )
AS
Deklarasi_Variable
.. .. ..
.. .. ..
Parameter
 Input parameter
 Digunakan untuk memberikan nilai dari pemanggil ke
dalam SP
 Contoh:
• 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 identity
 Contoh:
• CREATE PROC pr_honor (@p1 INT, @p2 CHAR(16),@pout1
INT OUT, @pout2 CHAR(1) OUTPUT) AS …
Output Parameter
 Nilai parameter bisa diolah pada program prosedur dan
untuk kemudian parameter tersebut dapat diubah dan
diberikan nilainya pada program yang memanggilnya.
CREATE PROC m (
@p1 int, @p2 int, @h int OUTPUT)
AS
SELECT @h= @p1 * @p2
Eksekusi Output Parameter
Contoh:
DECLARE @hsl INT
EXEC m 10, 20, @hsl OUTPUT
PRINT 'hasil = ' + str (@hsl)
 Pada contoh ada 3 parameter, yaitu p1 dan p2 sebagai
input parameter dan h sebagai output parameter.
 Saat eksekusi, p1 adalah 10, p2 adalah 20 dan hsl
merupakan 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 SP
 Contoh:
CREATE PROC pr_honor (@p1 INT=5) AS
select nama instruktur, honor from instruktur
where 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 values
EXEC pr_GetTopProducts @EndID=10
RETURN & ERROR
Return
 Digunakan untuk mengembalikan nilai hasil balik berupa
status dari eksekusi sukses atau gagal
 Statement return juga bisa digunakan untuk
menghentikan segala eksekusi
Error
 @@ERROR  Fungsi ini digunakan untuk
mengimplementasikan penanganan kode kesalahan.
 Berisi ID Error yang dihasilkan oleh pernyataan SQL terakhir
yang dijalankan.
 @@ERROR berisi 0  Sukses, selain itu gagal
 Contoh
CREATE PROC Proc1 @ColumnPK INT=NULL AS
BEGIN
INSERT INTO TABLE1 VALUES(@ColumnPK)
IF @@Error <> 0
BEGIN
PRINT 'Error occurred'
END
END
Membangkitkan Error
 Untuk membangkitkan pesan kesalahan gunakan sintaks
RAISEERROR
 Contoh
RAISERROR('demo error', 1, 1)
Studi Kasus
Melewatkan Nama Object sebagai Parameter
 Parameter tidak dapat memuat suatu object kecuali kita
menggunakan object tersebut di dalam SP dengan
statement EXEC (sering disebut dynamic query)
 Contoh: Bad Dynamic Query
CREATE PROC find_data (
@table varchar(128)
) AS
SELECT * FROM @table
GO
 Hasilnya:
 Error Message
 Server: Msg 137, Level 15, State 2,
Procedure find_data, Line 6 Must declare
the variable ‘@table’.
 Pesan kesalahan tersebut nampak ganjil karena parameter
@table telah didefinisikan sebagai variable
Solusi Pemecahan
CREATE PROC find_data (
@table varchar(128)
) AS
EXEC ('select * from ' + @table)
RETURN
GO
TUGAS: MUST READ!
T-SQL
 VARIABLE
 Global Variable, Local Variable
 Declaration
 Assignment
 PROGRAMMING STRUCTURE T-SQL
 IF THEN, CASE WHEN
 WHILE, CONTINUE, BREAK
 TRY…CATCH