Selasa, 20 September 2011

Stored Procedures, Functions dan Trigger pada SQL Server

Nim/Nama : 10.41010.0222/Ananthasya Putri Jueffend
Dosen : Tan Amelia
Stored Procedures 
 Merupakan struktur pemrograman yang cukup umum digunakan di dalam database administration dan development. Jujur saja, saya juga baru mulai mempelajari kegunaan dan pemakain fitur ini dalam pemrograman aplikasi. Sebuah stored procedure merupakan sebuah nama yang dapat diasosiasikan dengan batch dari kode pemrograman SQL yang disimpan di server database. Kita juga bisa menganalogikan stored procedure sebagai sebuah fungsi yang bisa dipanggil sewaktu-waktu, tapi jangan termind-set dengan istilah ini ya, karena di SQL Server 2005 juga ada fitur function. 
  • Kegunaan pembuatan stored procedure pada SQL Server :
1. Stored procedure dapat digunakan untuk memanipulasi data
2. Melakukan sebuah pekerjaan (job) secara otomatis
3. Melakukan perawatan (maintenance) secara otomatis
4. Dll

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)
  
Contoh Pembuatan Strore Procedure:
membuat STORE PROCEDURE dengan memakai SQL Server dengan menggunakan fasilitas Query Analyzer.
Untuk memudahkan saya akan berikan contoh kasus sederhana dibawah ini :
Kita mempunyai dua tabel, yang pertama tb_masuk dimana tabel ini berisi 
quantity barang masuk. Dan yang kedua tabel tb_keluar yang berisi data quantity barang keluar. Sekarang hitung jumlah quantity dengan rumus qty_akhir = qtymasuk-qtykeluar

tb_masuk tb_keluar
Kode Qty Kode Qty
1001 50 1002 10
1002 25 1003 11
1001 12 1004 15
1003 15 1006 20
1006 26 1002 5
1004 30 1003 3
1001 25 1001 20
1002 23 1006 2

Pertama kali buka Query Analyzer, dan diharapkan Anda sudah bisa membuat CREATE DATABASE dan CREATE TABLE dengan isinya seperti tabel diatas.
Kemudian pertama-tama bikin perintah seperti dibawah ini
CREATE PROC sp_hitungStok AS
Perintah tersebut di running terlebih dahulu.
Kemudian diedit melalui langkah-langkah berikut :
Klik Object Browser (F8)
sp02

sp03

muncul gambar seperti diatas, pilih database yang digunakan dan di expand, kemudian pilih folder Store Procedure, disini merupakan daftar dari Store Procedure termasuk Store Procedure yang kita bikin diatas. Cari nama Store Procedure yang telah kita bikin yaitu sp_hitungStok. Kemudian klik kanan dan edit…..
sp04

setelah ALTER PROC sp_hitungStok AS sisipkan perintah ini :
create table #tb_hasil(kode char(4), qty float)   insert into #tb_hasil
select kode, qty from tb_masuk
insert into #tb_hasil
select kode, qty from tb_keluar
select kode, sum(qty)from #tb_hasil group by kode order by kode
“create table #tb_hasil(kode char(4), qty float)” merupakan tabel temporary, setelah procedure selesai dijalankan table ini otomatis terhapus.
sp05

Setelah perintah disisipkan silakan di running terlebih dahulu procedure ini dengan menekan tombol Ctrl+E.
Setelah itu ditutup tidak apa-apa, atau masuk dalam window lain dengan menekan tombol Ctrl+W, pilih window sebelumnya atau untitled1.
Berikan perintah untuk memanggil procedure yang dibikin tadi dengan perintah berikut :
exec sp_hitungStok
kemudian perintah ini diblok, dan di running dengan Ctrl+E
Untuk lebih jelas hasilnya dalam bentuk grid Anda harus men-setting menu Query-Result in Grid
Anda akan melihat hasilnya :
sp06

Tabel hasil tersebut merupakan penjumlahan dari tabel tb_masuk dan tb_keluar.
Store Procedure dengan Parameter
Dengan contoh yang sama kita akan membuat suatu store procedure dengan parameter.
Seperti contoh diatas kita harus melakukan pengeditan dengan mengklik kanan Store Procedure yang kita bikin dan berikan perintah berikut :
ALTER PROC sp_hitungStok @kode char(4) AS   create table #tb_hasil(kode char(4), qty float)
insert into #tb_hasil
select kode, qty from tb_masuk where kode = @kode
insert into #tb_hasil
select kode, qty from tb_keluar where kode = @kode
select kode, sum(qty)from #tb_hasil group by kode order by kode
Lengkapnya sbb :
sp07

Kemudian di running, terus untuk memanggilnya :
exec sp_hitungStok isi_paramater
contoh : exec sp_hitung ’1001′
silakan jalankan, kita akan mendapatkan hasil berikut :
sp08

Yang akan tampil hanya kode ’1001′
Anda bisa mencobannya dengan kode lainnya.
 
Function
  adalah suatu procedure yang menghasilkan suatu nilai tertentu. Function di dalam SQL Server dibagi menjadi dua yaitu function yang sudah ada di SQL Server (bawaan) dan function buatan user.
Beberapa function bawaan Ms. SQL Server adalah sbb :

String Functions 

1. ASCII
  • Kegunaan : Menghasilkan nilai ASCII dari suatu karakter
  • Sintaks : ASCII(<expression>)
  • Contoh :
SELECT
ASCII(‘A’) uppercase_a,
ASCII(‘abc’) lowercase_a
uppercase_a lowercase_a
----------- -----------
65              97
 
2. CHAR
  • Kegunaan : Menghasilkan karakter dari suatu angka ASCII.
  • Sintaks : CHAR(<numeric_expression>)
  • Contoh :
SELECT
CHAR(65) uppercase_a,
CHAR(97) lowercase_a
uppercase_a lowercase_a
----------- -----------
A                  a

3. CHARINDEX
  • Kegunaan : Menghasilkan nilai posisi karakter dari suatu karakter/kata/kalimat yang dicari.
  • Sintaks : CHARINDEX(<char_expression1>, <char_expression2>)
  • Contoh :
SELECT
CHARINDEX(‘E’, ‘ABCDEFG’) AS position
position
---------
5
 
4. LEFT & RIGHT
  • Kegunaan : Menghasilkan beberapa karakter yang berawal dari sebelah kiri (left) atau kanan (right) dari suatu karakter/kata/kalimat
  • Sintaks :
LEFT(<char_expression>,<length_integer>)
RIGHT(<char_expression>,<length_integer>)
  • Contoh :
SELECT
RIGHT(‘ABCDEF’, 3) AS three_last,
LEFT(‘ABCDEF’, 3) AS three_first
three_last three_first
---------- ------------
DEF        ABC

5. LEN
  • Kegunaan : Menghasilkan panjang dari suatu karakter/kata/kalimat.
  • Sintaks : LEN(<char_expression>)
  • Contoh :
SELECT LEN(‘ ABCD ‘) AS total_length
total_length
------------
5

6. LTRIM & RTRIM
  • Kegunaan : Menghilangkan spasi sebelah kiri atau kanan dari suatu karakter/kata/kalimat
  • Sintaks :
LTRIM(<char_expression>)
RTRIM(<char_expression>)
  • Contoh :
SELECT
( ‘*’ + LTRIM (‘ ABC ‘) + ‘*’) AS left_trimmed,
( ‘*’ + RTRIM (‘ ABC ‘) + ‘*’) AS right_trimmed
left_trimmed right_trimmed
------------ ------------
*ABC *      * ABC*

7. LOWER
  • Kegunaan : Menghasilkan tampilan huruf kecil dari suatu karakter/kata/kalimat
  • Sintaks : LOWER(<char_expression>)
  • Contoh :
SELECT
LOWER(‘STRING’) lowercase
LOWERCASE
------------
string

8. REPLACE
  • Kegunaan : Mengganti suatu karakter/kata/kalimat dengan suatu karakter/kata/kalimat lain berdasarkan posisi tertentu
  • Sintaks :
REPLACE(<string_expression1> , <string_expression2> , <string_expression3>)
  • Contoh :
SELECT
REPLACE(‘ABCDEFG’,’CDE’,’*’) AS no_CDE
no_CDE
-------------
AB*FG

9. REPLICATE
  • Kegunaan : Menduplikasi suatu karakter/kata/kalimat dengan jumlah tertentu.
  • Sintaks : REPLICATE(<expression>,<times_integer>)
  • Contoh :
SELECT
REPLICATE(‘A’,5) AS five_a,
REPLICATE(‘’,5) AS five_blanks,
REPLICATE(5,2) AS two_times_five
five_a five_blanks two_times_five
------ ----------- --------------
AAAAA              55

10. REVERSE
  • Kegunaan : Membalik suatu karakter/kata/kalimat.
  • Sintaks : REVERSE(<expression>)
  • Contoh :
SELECT
REVERSE(‘ABCD’) AS backwards_char,
REVERSE(12345) AS backwards_numeric
backwards_char backwards_numeric
-------------- ----------------
DCBA          54321

11. STR
  • Kegunaan : Menampilkan numerik secara string dengan panjang dan tempat pecahan yang bisa ditentukan
  • Sintaks : STR(<number_float [,<length_integer>[,<decimal_integer]])
  • Contoh :
SELECT
STR(1234.5678, 4) AS four_chars
STR(1234.5678, 7,2) AS seven_chars
STR(1234.5678, 3,1) AS not_enough_space
four_chars seven_chars not_enough_space
---------- ----------- ----------------
1235      1234.57    ***

12. STUFF
  • Kegunaan : Menghapus suatu kata/kalimat pada posisi dan panjang tertentu kemudian diganti dengan karakter/kata/kalimat yang lain
  • Sintaks :
STUFF(<char_expression1> ,<start_integer> , <length_integer> , <char_expression2>)
  • Contoh 1 :
SELECT
STUFF(‘ABCDABCD’,5,4,’EFG’) as alphabet
alphabet
---------
ABCDEFG
  • Contoh 2 :
SELECT
STUFF(‘ABCDABCD’,5,3,NULL) AS remove3,
STUFF(‘ABCDABCD’,5,3,’ ‘) AS blank,
STUFF(‘ABCDABCD’,5,3,’’) AS empty_string
remove3 blank empty_string
------- ------ ------------
ABCDD ABCD D ABCDD

13. SUBSTRING
  • Kegunaan : Menghasilkan potongan kata/kalimat pada posisi dan panjang tertentu dalam suatu karakter/kata/kalimat
  • Sintaks :
SUBSTRING(<expression>,<start_integer>,<length_integer>)
  • Contoh :
SELECT
SUBSTRING(‘ABCDEFG’,1,3) AS first_three,
SUBSTRING(0x001101,1,2) AS first_binary
first_three first_binary
----------- ------------
ABC 0x0011

14. UPPER
  • Kegunaan : Menghasilkan tampilan huruf kapital dari suatu karakter/kata/kalimat
  • Sintaks : UPPER(<numeric expression>)
  • Contoh :
SELECT
UPPER(‘string’) as uppercase
UPPERCASE
-------------
STRING

Date and Time Functions (Fungsi Tanggal dan Waktu)

1. DATEADD
  • Kegunaan : Menghasilkan tanggal dan waktu baru berdasarkan tanggal dan waktu asal/lama yang dijumlah dengan interval yang diinputkan
  • Sintaks :
DATEADD(<datepart>,<how_many_integer>,<add_to_date>)
  • Contoh :
SELECT
DATEADD(month,4, GETDATE()) as four_months_ahead
four_months_ahead
------------------------------------------------------
2006-11-11 09:39:19.747

2. DATEDIFF
  • Kegunaan : Menghasilkan selisih hari/bulan/tahun antara dua tanggal dan waktu
  • Sintaks :
DATEDIFF(<datepart>,<date_expression1>,<date_expression2>)
  • Contoh :
SELECT
DATEDIFF (day, ‘1900-01-01’, GETDATE()) AS days
days
-----------
38907

3. DATENAME
  • Kegunaan : Menghasilkan nama hari/bulan atau tahun dari tanggal tertentu
  • Sintaks : DATENAME(<date_expression>)
  • Contoh :
SELECT
GETDATE() AS full_date,
DATENAME( month, GETDATE()) AS month_name
full_date month_name
----------------------- -------------------
2006-07-11 10:18:04.827 July

4. DATEPART
  • Kegunaan : Menghasilkan nama hari/bulan atau tahun dari bagian tanggal
tertentu.
  • Sintaks : DATEPART(<date_part>,<date_expression>)
  • Contoh :
SELECT
DATEPART(year, GETDATE()) as current_year
current_year
------------
2006

5. DAY
  • Kegunaan : Menghasilkan nilai hari dari tanggal tertentu.
  • Sintaks : DAY(<date_expression>)
  • Contoh :
SELECT
DAY(GETDATE()) AS current_day,
GETDATE() AS ‘current_date’
current_day current_date
----------- ------------------------
11 2006-07-11 10:22:14.233

6. GETDATE
  • Kegunaan : Mengambil tanggal dan waktu dari sistem komputer
  • Sintaks : GETDATE()
  • Contoh :
SELECT
GETDATE() AS local_time
local_time
------------------------
2004-03-07 16:33:23.940

7. MONTH
  • Kegunaan : Menghasilkan nilai bulan dari tanggal tertentu.
  • Sintaks : MONTH(<date_expression>)
  • Contoh :
SELECT
MONTH(GETDATE()) AS current_month,
GETDATE() AS ‘current_date’
current_month current_date
------------- ------------------------
7 2006-07-11 10:32:09.810

8. YEAR
  • Kegunaan : Menghasilkan nilai tahun dari tanggal tertentu.
  • Sintaks : YEAR(<date_expression>)
  • Contoh :
SELECT
YEAR(GETDATE()) AS current_year,
GETDATE() AS ‘current_date’
current_year current_date
------------ ------------------------
2006 2006-07-11 10:34:30.043

System Functions (Fungsi Sistem)

1. CAST & CONVERT
  • Kegunaan : Mengubah tipe suatu data ke tipe data yang lain.
  • Sintaks :
CAST(<expression> AS <data_type>)
CONVERT(data_type [ ( length ) ] , expression [ , style ] )
  • Contoh 1 :
SELECT
‘$ ‘ + CAST (100 AS VARCHAR(10)) AS one_hundred_dollars
one_hundred_dollars
-------------------
$100
  • Contoh 2 :
SELECT
CONVERT(VARCHAR(25),GETDATE(),111) AS japanese_style,
CONVERT(VARCHAR(25),GETDATE(),104) AS german_style,
CONVERT(VARCHAR(25),GETDATE(),126) AS ISO8601_style
japanese_style german_style ISO8601_style
-------------------- ------------------ -------------------------
2006/07/11 11.07.2006 2006-07-11T11:03:38.093

2. ISDATE
  • Kegunaan : Memeriksa apakah suatu data merupakan tipe tanggal. Jika benar menghasilkan angka 1 dan jika salah 0
  • Sintaks : ISDATE( )
  • Contoh :
SELECT
ISDATE(GETDATE()) AS getdate_value,
ISDATE (‘07/18/2004’) AS date_value,
ISDATE(‘67/56/07’) AS not_a_date
getdate_value date_value not_a_date
------------- ----------- -----------
1               1             0

3. ISNULL
  • Kegunaan : Memeriksa apakah suatu data bertipe null. Jika benar maka data tersebut akan diganti dengan data lainnya
  • Sintaks : ISNULL(<check_expression>,<replacement_value>)
  • Contoh :
SELECT

ISNULL(NULL, ‘it is NULL’) AS null_value,
ISNULL(‘not NULL’, ‘it is NULL’) AS not_null
null_value not_null
---------- --------
it is NULL not NULL


4. ISNUMERIC
  • Kegunaan : Memeriksa apakah suatu data merupakan tipe numerik. Jika benar menghasilkan angka 1 dan jika salah 0
  • Sintaks : ISNUMERIC( )
  • Contoh :
SELECT
ISNUMERIC(‘12345’) AS num_value,
ISNUMERIC(‘12@345’) AS not_num_value
num_value not_num_value
----------- -------------
1                  0



Trigger pada SQL
 Trigger merupakan store procedure yang dijalankan secara automatis saat user melakukan modifikasi data pada tabel. Modifikasi data yang dilakukan pada tabel yaitu berupa perintah INSERT, UPDATE, dan DELETE.

INSERT , UPDATE dan DELETE bisa digabung jadi satu trigger yang dinamakan Multiple Trigger.

Bentuk dasar perintahnya :

CREATE TRIGGER nama_trigger
ON nama_tabel
FOR INSERT, UPDATE, DELETE
AS
isi statement-statement Anda disini.
GO

Saya akan memberi contoh sederhana untuk mudah memahami bagaimana cara kerja trigger ini.

Misalkan kita punya data di table DaftarSiswa

DaftarSiswa
Kode Status
1001
1002
1003

Di tabel yang lain kita juga punya tabel Daftar Nilai :

DaftarNilai
Kode Nilai

Daftar nilai ini belum ada isinya yang nantinya akan kita isi dengan perintah INSERT.

Kolom Status pada tabel DaftarSiswa akan kita isi dengan “Lulus” dan “Tidak Lulus” secara automatis saat kita melakukan INSERT dan UPDATE pada tabel DaftarNilai.

Cara membuat dan mengisi kedua tabel diatas :

CREATE TABLE daftarSiswa (kode char(4), status char(10))

CREATE TABLE daftarnilai (kode char(4), nilai float)

Pada tabel DaftarSiswa diisi dengan perintah berikut :

INSERT INTO daftarSiswa VALUES(’1001′,”)

INSERT INTO daftarSiswa VALUES(’1002′,”)




INSERT INTO daftarSiswa VALUES(’1003′,”)

Setelah semuanya dijalankan di Query Analyzer, berikutnya kita akan membuat suatu trigger di tabel daftarNilai.

CREATE TRIGGER tr_status      ON daftarnilai
FOR INSERT, UPDATE
AS
DECLARE @kode char(4)
DECLARE @nilai float
SELECT @kode = kode, @nilai = nilai FROM daftarNilai
IF @nilai >= 60
UPDATE daftarSiswa SET status = ‘Lulus’ WHERE kode=@kode
ELSE UPDATE daftarSiswa SET status = ‘Tidak Lulus’ WHERE kode=@kode
go

Untuk menganalisa lihat isi kedua tabel diatas dengan perintah

SELECT* FROM daftarSiswa

SELECT* FROM daftarNilai

Setelah Anda melihat hasilnya kemudian jalankan perintah dibawah ini :

INSERT INTO daftarNilai values(’1001′,70)

INSERT INTO daftarNilai values(’1002′,50)

INSERT INTO daftarNilai values(’1003′,80)

Setelah dijalankan Anda bisa lihat kembali isi tabel daftarSiswa dan daftarNilai.

DaftarSiswa
Kode Status
1001 Lulus
1002 Tidak Lulus
1003 Lulus

DaftarNilai
Kode Nilai
1001 70
1002 50
1003 80

Disini terlihat saat kita melakukan perintah INSERT di tabel daftarNilai secara otomatis program trigger melakukan pengisian pada tabel daftarSiwa pada kolom “status”

Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)