1 / 31

Stored Procedure

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

tansy
Télécharger la présentation

Stored Procedure

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Stored Procedure Basis Data 2

  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-SQLatau CLR • Stored Procedure yang disimpan dapat menerima parameter input dan dapatmengembalikanhasil • 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

  3. Stored Procedure vs Procedure • Persamaandenganprosedurdlmpemrograman: • Menerimaparameter input dan mengembalikanbeberapa nilai dalam bentuk parameter output ke pemanggil prosedureatau batch. • Berisi statement pemrograman utkoperasi dlmdatabase, termasuk memanggil prosedur lainnya. • Mengembali nilai status kepemanggilprosedur atau batch untuk menunjukkan keberhasilan atau kegagalan (dan alasan untuk kegagalan).

  4. Tipe Stored Procedure • System Stored Procedure: • Nama dimulai dengan sp_atauxp_ (extended stored procedurakandihilangkan) • Dibuat dalam database master • Tujuannya untuk pengaplikasiandalam database apapun • Sering digunakan oleh sysadmin • Local Stored Procedure: • Ditetapkan di database lokal • Nama sering dimulai dengan pr_atauusp • Dalamprakteknyalebihbaikgunakannama yang mudah: • Tanpa prefix • Sesuaikandengan yang ada di aplikasi (misalmenggunakan object/komponenseperti ORM)

  5. Kelebihan Stored Procedure • Keamanan lebih • stored procedure selalu tersimpan pada database server • Memberikan grant user permission utkmenjalankan SP • Mengurangilalu lintas jaringan • SP dapatmengurangi query SQL panjang menjadisatubarisyang ditransmisikan sehinggamengurangilalu lintas client-server.

  6. Kelebihan Stored Procedure • WaktuEksekusilebihcepat • Bilaperlu T-SQL yang banyakdanberulang-ulang, maka SP bisalebihcepatdrpd batch T-SQL code. SP dicompilesekali, disimpandalam memory dandigunakanuntukpemanggilanberikutnya (tidakperludikompilasiulang). SQL dapat dioptimasikan oleh DBMS compiler • Berbagi-pakaikode modular berimbaspada: • Mengurangipekerjaanberlebih – penggunaanulangkodesecaraefisien • Standarisasipemrosesan • Spesialisasi antara developer

  7. Dasar Stored Procedure

  8. Membuat Stored Procedure • Sintaks: CREATE PROCEDURENamaProsedur AS Deklarasi_Variable .. .. .. .. .. .. RETURN • Contoh: CREATE PROCEDUREpr_honor AS SELECTnamainstruktur, honorFROM instruktur RETURN

  9. Menjalankan Stored Procedure • denganEXEC • EXECpr_honor • EXECsp_help • dengan EXECUTE • EXECUTEpr_honor • EXECUTEsp_help • langsungnamaprosedur • pr_honor • sp_help

  10. MengkoreksiSebuahProsedur • Statement ALTER harusdigunakan ALTER PROCEDUREpr_honorAS selectinisial, namainstruktur, honor frominstruktur RETURN

  11. Menghapus Stored Procedure • digunakan statement DROP • Sintaks: • DROP PROCEDURE NamaProsedur • Contoh: • DROP PROCEDURE pr_honor • DROP PROCEDURE uspHitungGaji

  12. MengubahProsedurTanpaMenggunakan ALTER Cekprosedurada, bilaada di drop, kemudianbaru 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

  13. MengubahProsedurTanpaMenggunakan ALTER: SQL Server 2008 USE AdventureWorks2008R2; GO IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.uspGetAllEmployees; GO CREATE PROCEDURE HumanResources.uspGetAllEmployeesAS SET NOCOUNT ON; SELECT LastName, FirstName, Department FROM HumanResources.vEmployeeDepartmentHistory; GO

  14. MengubahNama Stored Procedure • GunakansintaksSP_RENAME • Contoh: SP_RENAMEpr_honor, pr_honor2

  15. Parameter

  16. Parameter • Prosedurdapatmempunyai parameter berupa variable yang disuplaioleh program yang memanggilnya • Sintaks parameter • @param as datatype = default [OUT/OUTPUT] • Contoh: CREATE PROC pr_honor(@p1 int, @p2 char(16), .. ) AS Deklarasi_Variable .. .. .. .. .. ..

  17. Parameter • Input parameter • Digunakanuntukmemberikannilaidaripemanggilkedalam SP • Contoh: • CREATE PROC pr_honor(@p1 INT) AS … • Output parameter • Digunakanuntukmengembalikannilai non-recordsetdari SP kepemanggil • Misalnya SP untuk insert, mengembalikannilaihasil identity • Contoh: • CREATE PROC pr_honor(@p1 INT, @p2 CHAR(16),@pout1 INT OUT, @pout2 CHAR(1) OUTPUT) AS …

  18. Output Parameter • Nilai parameter bisadiolahpada program prosedurdanuntukkemudian parameter tersebutdapatdiubahdandiberikannilainyapada program yang memanggilnya. CREATE PROC m ( @p1 int, @p2 int, @h intOUTPUT) AS SELECT @h= @p1 * @p2

  19. Eksekusi Output Parameter Contoh: DECLARE@hslINT EXEC m 10, 20, @hslOUTPUT PRINT 'hasil= ' + str (@hsl) • Padacontohada 3 parameter, yaitu p1 dan p2 sebagai input parameter dan h sebagai output parameter. • Saateksekusi, p1 adalah 10, p2 adalah 20 danhslmerupakanhasilperkalian.

  20. Parameter dgn Default Value • Default value • Untukmenghindari null karenanilaitidakdiberikan • Merupakannilai default yang otomatisdiberikanbilatidakdispesifikasikanketikapemanggilan SP • Contoh: CREATE PROC pr_honor (@p1 INT=5) AS select namainstruktur, honorfrom instruktur where inisial=@ini Return • Urutan parameter • Tempatkan parameter dengan default value di akhirdaridaftar parameter untukpenggunaanfleksibel

  21. Eksekusi Procedure Berparameter • Padasaateksekusi, parameter diberikansetelahnama SP • Berdasarkannama: EXECpr_GetTopProducts @StartID = 1, @EndID = 10 • Berdasarkanposisi: EXECpr_GetTopProducts 1, 10 • Leveraging Default values EXECpr_GetTopProducts @EndID=10

  22. RETURN & ERROR

  23. Return • Digunakanuntukmengembalikannilaihasilbalikberupa status darieksekusisuksesataugagal • Statement returnjugabisadigunakanuntukmenghentikansegalaeksekusi

  24. 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, selainitugagal • Contoh CREATEPROC Proc1 @ColumnPK INT=NULL AS BEGIN INSERTINTO TABLE1 VALUES(@ColumnPK) IF @@Error <> 0 BEGIN PRINT 'Error occurred' END END

  25. Membangkitkan Error • UntukmembangkitkanpesankesalahangunakansintaksRAISEERROR • Contoh RAISERROR('demo error', 1, 1)

  26. StudiKasus

  27. MelewatkanNama Object sebagai Parameter • Parameter tidakdapatmemuatsuatu object kecualikitamenggunakan object tersebut di dalamSP denganstatement EXEC (seringdisebutdynamic query) • Contoh: Bad Dynamic Query CREATEPROCfind_data ( @table varchar(128) ) AS SELECT* FROM @table GO

  28. Hasilnya: • Error Message • Server: Msg 137, Level 15, State 2, Procedure find_data, Line 6 Must declare the variable ‘@table’. • Pesankesalahantersebutnampakganjilkarena parameter @table telahdidefinisikansebagai variable

  29. SolusiPemecahan CREATEPROCfind_data ( @table varchar(128) ) AS EXEC ('select * from ' + @table) RETURN GO

  30. TUGAS: MUST READ!

  31. T-SQL • VARIABLE • Global Variable, Local Variable • Declaration • Assignment • PROGRAMMING STRUCTURE T-SQL • IF THEN, CASE WHEN • WHILE, CONTINUE, BREAK • TRY…CATCH

More Related