Tugas Materi Stored procedures, Function dan Trigger Pada SQL Server

NIM/NAMA       : 10410100239 / ABDURRAHMAN FATTAH
Dosen                  : Tan Amelia
Tugas                   : Tugas Materi Stored procedures, Function dan Trigger Pada SQL Server

Mengenal Stored Procedure pada SQL Server

Bagi Anda yang tengah mendalami pemrograman T-SQL SQL Server, mungkin Anda sudah tidak asing mendengar istilah stored procedure. Stored Procedure, atau kadang disingkat sproc, merupakan salah satu fungsi pemrograman prosedural yang tersedia pada SQL Server. Prinsip pembuatan sproc pada dasarnya mirip dengan pembuatan fungsi atau class pada pemrograman berbasis objek (OOP).

Pada DBMS lain seperti Oracle, DB2, atau PostScript stored procedure juga dikenal sebagai function (dimana pada SQL Server juga disediakan keyword function yang dapat mengembalikan nilai tertentu sesuai parameter yang telah ditentukan sebelumnya).

Lantas, apa kegunaan pembuatan stored procedure pada SQL Server?

Jawabannya tentu bisa bermacam-macam tergantung tujuan yang ingin dicapai. Stored procedure dapat digunakan untuk memanipulasi data, melakukan sebuah pekerjaan (job) secara otomatis, melakukan perawatan (maintenance) secara otomatis, dll.

Menerapkan stored procedure pada SQL Server pada dasarnya tidaklah terlalu sulit. Terlebih lagi, dikarenakan fungsi ini telah tersedia semenjak SQL Server 7.0. Berikut adalah sintaks pemrograman dari sebuah stored procedure:

  1: CREATE PROCEDURE|PROC <sproc name>

  2: [<parameter name> [schema.]<data type> [VARYING] [= <default value>]  [OUT

  3: [PUT]][,

  4: <parameter name> [schema.]<data type> [VARYING] [= <default value>]

  5: [OUT[PUT]][,

  6: ...

  7: ...

  8: ]]

  9: [WITH

 10: RECOMPILE| ENCRYPTION | [EXECUTE AS { CALLER|SELF|OWNER|<’user name’>}]

 11: [FOR REPLICATION]

 12: AS

 13: <code> | EXTERNAL NAME <assembly name>.<assembly class>

Untuk mengakses stored procedure yang telah dibuat, maka dapat menggunakan perintah seperti ini:

  1: EXEC <sproc name>

Jika diperhatikan, sintaks tersebut pada dasarnya menggunakan logika pemrograman prosedural standar dari sebuah fungsi, dimana ketika kita mendefinisikan sebuah variabel maka kita perlu mendefinisikan tipe datanya terlebih dahulu.

Untuk lebih jelasnya, kita akan mencoba membuat sebuah stored procedure yang bertujuan untuk memasukkan data siswa baru dan memberikan ID secara otomatis dengan format sebagai berikut:

inisial nama depan (1 karakter) + inisial nama belakang (1 karakter) + tahun (4 karakter) + bulan (2 karakter) + nomor urut (4 karakter)

Berikut adalah implementasi dari pembuatan stored procedure yang bertujuan untuk memanipulasi data secara otomatis:

  1: CREATE PROC sp_add_siswa

  2: @id_siswa   varchar(20),

  3: @namaDepan           varchar(50),

  4: @namaBelakang       varchar(50),

  5: @tgl_lahir    date

  6: AS

  7:       DECLARE @kunci            varchar(20)

  8:       DECLARE @urut varchar(20)

  9:

 10:      --keyword SET berfungsi untuk mendefinisikan nilai dari variabel

 11:      --nilai dari variabel @kunci bertujuan untuk menghasilkan format   inisial, tahun, dan bulan

 12:      SET @kunci = UPPER(SUBSTRING(LTRIM(@namaDepan),1,1)) +

 13:                   UPPER(SUBSTRING(LTRIM(@namaBelakang),1,1)) +

 14:                   CONVERT(varchar,DATEPART(YEAR,@tgl_lahir)) +

 15:                   RIGHT('0' + CONVERT(varchar,DATEPART(MONTH,@tgl_lahir)),2)

 16:

 17:      --sintaks ini bertujuan untuk menghasilkan nomor urut yang akan

 18:      --bertambah secara otomatis

 19:    SELECT @urut = ISNULL(MAX(RIGHT(id_siswa,4)),0) + 1 FROM Siswa WHERE id_siswa LIKE @kunci + '%'

 20:      SET @urut = RIGHT('000' + @urut,4)

 21:

 22:      SET @kunci = @kunci + @urut

 23:

 24:      INSERT INTO Siswa VALUES(@kunci,@nama,@tgl_lahir)

Untuk mengeksekusi stored procedure yang telah dibuat, maka cukup digunakan perintah EXEC [nama_stored_procedure] disertai dengan urutan nilai yang ingin dimasukkan ke dalam basis data:

  1: --bagian yang dikosongkan adalah bagian id_siswa yang nilainya

  2: --akan dihasilkan secara otomatis oleh sproc

  3: EXEC sp_add_number '', 'Unyil', 'Bocil', '1988-2-16'

Jika perintah ini dijalankan, maka dapat dilihat bahwa stored procedure akan menghasilkan ID dari siswa dengan nilai: ‘UB2011030001’.

Function (Fungsi) Pada SQL Server

Function (fungsi) 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 sebagai berikut :

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

----------- -----------

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

------------

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

------------------------------------------------------

2011-09-21 09:40: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

-----------

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

----------------------- -------------------

2011-09-21 10:18:04.827 September

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

----------- ------------------------

 2011-09-21 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 server

1. Trigger adalah blok PL/SQL atau prosedur yang berhubungan dengan table, view, skema atau database yang dijalankan secara implicit pada saat terjadi sebuah event. 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.
Tipe dari trigger adalah :
· Application trigger : diaktifkan pada saat terjadi event yang berhubungan dengan sebuah aplikasi
· Database trigger : diaktifkan pada saat terjadi event yang berhubungan dengan data (seperti operasi DML) atau event yang berhubungan dengan sistem (semisal logon atau shutdown) yang terjadi pada sebuah skema atau database.

Trigger perlu dibuat pada saat :
· Membentuk sebuah aksi tertentu terhadap suatu event
· Memusatkan operasi global

Trigger tidak perlu dibuat, jika :
· Fungsionalitas yang diperlukan suatu ada pada Oracle server
· Duplikat atau sama dengan fungsi trigger yang lain.

Sintak penulisan dari database trigger, berisi komponen berikut :
1. Trigger timing :
a. Untuk tabel : BEFORE, AFTER
b. Untuk view : INSTEAD OF
2. Trigger event : INSERT, UPDATE atau DELETE
3. Nama tabel : yaitu nama tabel atau view yang berhubungan dengan trigger
4. Tipe trigger : Baris atau Pernyataan (statement)
5. klausa WHEN : untuk kondisi pembatasan
6. trigger body : bagian prosedur yang dituliskan pada trigger

Trigger timing adalah waktu kapan trigger diaktifkan. Ada tiga macam trigger timing, yaitu :
· BEFORE : trigger dijalankan sebelum DML event pada tabel
· AFTER : trigger dijalankan setelah DML event pada tabel
· INSTEAD OF : trigger dijalankan pada sebuah view.

Trigger event ada 3 kemungkinan : INSERT, UPDATE atau DELETE.
Pada saat trigger event UPDATE, kita dapat memasukkan daftar kolom untuk mengidentifikasi kolom mana yang berubah untuk mengaktifkan sebuah trigger (contoh : UPDATE OF salary ... ). Jika tidak ditentukan, maka perubahannya akan berlaku untuk semua kolom pada semua baris.

Tipe trigger ada 2 macam, yaitu :
. Statement : trigger dijalankan sekali saja pada saat terjadi sebuah event. Statement trigger juga dijalankan sekali, meskipun tidak ada satupun baris yang dipengaruhi oleh event yang terjadi.
· Row : trigger dijalankan pada setiap baris yang dipengaruhi oleh terjadinya sebuah event. Row trigger tidak dijalankan jika event dari trigger tidak berpengaruh pada satu baris pun.
Trigger body mendefinisikan tindakan yang perlu dikerjakan pada saat terjadinya event yang mengakibatkan sebuah trigger menjadi aktif.