Stored Procedures, Functions dan Trigger pada SQL Server
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 :
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.
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)
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…..
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 |
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 :
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 |
Kemudian di running, terus untuk memanggilnya :
exec sp_hitungStok isi_paramater
contoh : exec sp_hitung ’1001′
silakan jalankan, kita akan mendapatkan hasil berikut :
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 :
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
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
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
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>)
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
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
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>)
RTRIM(<char_expression>)
- Contoh :
SELECT
( ‘*’ + LTRIM (‘ ABC ‘) + ‘*’) AS left_trimmed,
( ‘*’ + RTRIM (‘ ABC ‘) + ‘*’) AS right_trimmed
left_trimmed right_trimmed
------------ ------------
*ABC * * ABC*
( ‘*’ + 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
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
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
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
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 ***
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
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
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
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
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
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
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
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
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
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
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
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
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 ] )
CONVERT(data_type [ ( length ) ] , expression [ , style ] )
- Contoh 1 :
SELECT
‘$ ‘ + CAST (100 AS VARCHAR(10)) AS one_hundred_dollars
one_hundred_dollars
-------------------
$100
‘$ ‘ + 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
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
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
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
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”
0 komentar:
Posting Komentar