Function

Fungsi adalah bagian dari perintah/statement yang merubah beberapa nilai masukan menjadi sebuah nilai baru (keluaran/hasil).
Beberapa fungsi telah tersedia dan tinggal dipakai (fungsi standar) dan dapat pula dibuat sendiri sebagai fungsi baru (user defined function).
Semua fungsi ditulis nama yang diikuti kurung ()
Contoh
Fungsi power(a,b) untuk menghitung a pangkat b
Fungsi left(st,n) untuk mengambil n huruf depan/terkiri dari st

Contoh pemanggilan :
Select power(2,4) akan menampilkan 16
2 pangkat 4, yaitu 2 x 2 x 2 x 2 = 16

Update mhs set inisial=left(namadepan,1)+left(namabelakang,1)
Mengisi kolom inisial pada tabel mhs dengan 2 huruf yang diambil
dari 1 huruf namadepan dan 1 huruf namabelakang

select nama, ‘Hari Lahir ‘=datepart(dw,tgllahir) from mhs
Menampilkan nama dan hari lahir semua data mahasiswa

Fungsi standar yang tersedia, dikelompokkan sebagai berikut
• Fungsi untuk Konfigurasi
• Fungsi untuk manipulasi kursor
• Fungsi untuk tanggal & jam
• Fungsi Matematika
• Fungsi Agregat
• Fungsi Metadata
• Fungsi untuk Security
• Fungsi untuk manipulasi string
• Fungsi untuk Sistem
• Fungsi untuk statistic
• Fungsi untuk teks & gambar

Pengelompokkan di atas dapat dilihat secara lebih jelas pada bagian Function.

No Sintaks Pemanggilan Arti/hasil
FUNGSI STRING
1 ASCII('C') Nomor ASCII dari karakter ‘C’
2 Char(65) Karakter dari ASCII bernomor 65
3 charindex('E','hello') Posisi ‘E’ dalam kata ‘hello’
4 left('HELLO',3) 3 huruf terkiri dari kata ‘HELLO’
5 ltrim(' Hello') Membuang spasi di kiri
6 right('hello',3) 3 huruf terkanan dari kata ‘HELLO’
7 rtrim('Hello ') Membuang spasi di kanan
8 len('Hello') Panjang/jumlah huruf dari kata ‘HELLO’
9 lower('HELLO') Merubah ke huruf kecil
10 patindex('%BOX%','ACTION BOX') Posisi kata ‘BOX’ dalam ‘ACTION BOX’
11 reverse('HELLO') Membalik susunan huruf/depan ke belakang dst
12 space(5) Membentuk spasi sebanyak 5
13 str(123.45,6,0) Merubah ke string dengan 6 digit tanpa spasi
14 stuff('hello',2,2,'i') Mengganti huruf kedua dengan huruf ‘i’
15 substring('hello',2,2) Mengambil 2 huruf mulai huruf ke 2
16 upper('hello') Merubah ke huruf besar

FUNGSI TANGGAL
1 getdate() Mengambil tanggal lengkap hari ini
2 datepart(dd,getdate()) Mengambil bagian tanggal dari hari ini
3 datename(dw,'1980-06-11') Menghasilkan nama hari dari 11 juni 1980
4 dateadd(yy,2,getdate()) Menambah 2 tahun dari hari ini
5 datediff(dd,'1980-06-11',getdate()) Menghitung selisih hari (umur sejak 11 jun 80)

FUNGSI MATEMATIK
1 abs(-25) Mengambil nilai absolute (tanpa negatif)
2 sin(pi()/2) Menghitung sinus sudut 90 derajat (pi=180)
3 exp(1) Menghitung bilangan e pangkat 1
4 degrees(pi()/2) Mengkonversi dari pi/2 radian ke derajat
5 radians(180) Mengkonversi 180 derajat ke radian
6 power(2,4) Menghitung 2 pangkat 4
7 floor(90.7) Membulatkan ke bawah
8 sign(90) Menghasilkan tanda bilangan (pos=1,neg=-1)
9 rand(90) Menghasilkan bil acak dengan bil pembangkit 90
10 round(1234.567,2) Membulatkan ke 2 angka belakang koma

FUNGSI SYSTEM & METADATA
1 host_id() Menghasilkan ID dari host
2 host_name() Menghasilkan nama dari host
3 suser_sname() Menghasilkan system username yg sdg aktif
4 user_id() Menghasilkan user ID yang sedang aktif
5 user_name() Menghasilkan username yang sedang aktif
6 db_id() Menghasilkan database ID yang sedang aktif
7 db_name() Menghasilkan database name yang sedang aktif
8 object_id('Authors') Menghasilkan ID dari object bernama ‘Authors’
9 object_name('629577281') Menghasilkan nama object yang ber ID=…


Untuk dapat memahami penggunaan beberapa fungsi standar dapat dicoba beberapa contoh berikut :

select ' 1. ',ascii('ABC')
select ' 2. ',char(65)
select ' 3. ',charindex('E','hello')
select ' 4. ',left('HELLO',3)
select ' 5. ','A'+ltrim(' Hello')
select ' 6. ',right('hello',3)
select ' 7. ',rtrim('Hello ')+'A'
select ' 8. ',len('Hello')
select ' 9. ',lower('HELLO')
select '10. ',patindex('%BOX%','ACTION BOX')
select '11. ',reverse('HELLO')
select '12. ','A'+space(5)+'B'
select '13. ',str(123.45,6,0)
select '14. ',stuff('hello',2,2,'i')
select '15. ',substring('hello',2,2)
select '16. ',upper('hello')

select '17. ',getdate()
select '18. ',datepart(dd,getdate())
select '19. ',datepart(mm,getdate())
select '20. ',datepart(yy,getdate())
select '21. ',datepart(qq,getdate())
select '22. ',datepart(ww,getdate())
select '23. ',datepart(dy,getdate())
select '24. ',datepart(dw,getdate())
select '25. ',datename(mm,getdate())
select '26. ',datename(dw,getdate())
select '27. ',datename(dw,'1980-06-11')
select '28. ',dateadd(dd,2,getdate())
select '29. ',dateadd(mm,2,getdate())
select '30. ',dateadd(yy,2,getdate())
select '31. ',datediff(dd,'1980-06-11',getdate())
select '32. ',datediff(mm,'1980-06-11',getdate())
select '33. ',datediff(yy,'1980-06-11',getdate())

select '34. ',abs(-25)
select '35. ',sin(pi()/2)
select '36. ',exp(1)
select '37. ',degrees(pi()/2)
select '38. ',radians(180)
select '39. ',power(2,4)
select '40. ',floor(90.7)
select '41. ',sign(90)
select '42. ',rand(90)
select '43. ',round(1234.567,2)

select '44. ',host_id()
select '45. ',host_name()
select '46. ',suser_sname()
select '47. ',user_id()
select '48. ',user_name()
select '49. ',db_id()
select '50. ',db_name()
select '51. ',object_id('authors')
select '52. ',object_name('629577281')

Definisi Join
Join adalah operasi untuk mengambil informasi dari 2 tabel atau lebih dalam 1 waktu. Dengan join baris data dari satu tabel dihubungkan dengan baris data pada tabel lain berdasarkan kolom tertentu.

Klasifikasi Join
• inner join
• outer join
• cross join
• equi join
• natural join
• self join

Dari beberapa macam join tersebut yang banyak digunakan adalah inner join dan outer join


Inner Join

Inner join adalah default dari join, digunakan mendapatkan data dari tabel lain berdasarkan kolom yang dihubungkan. Bila tidak ditemukan maka baris data tersebut dibatalkan.

Syntax :
Select … from tabelA [inner] join tabelB
on tabelA.namakolom operator tabelB.namakolom

Keterangan :
• Kata inner adalah opsional (boleh ditulis ataupun tidak)
• Klausa where, order by dll dapat disertakan

Contoh :
Menampilkan ID buku, Judul buku dan nama penerbitnya dari tabel Title & Publishers
Pada tabel Title tidak ada nama penerbit, tetapi ada kode penerbit/Pub_Id yang namanya dapat diperoleh dari tabel Publishers berdasarkan Pub_Id

Select t.Title_Id, t.Title, p.Pub_name from Titles t join Publishers p
on t.Pub_Id = p.Pub_Id


Outer Join
Outer join adalah join yang digunakan memasangkan data dari satu tabel dengan tabel lain berdasarkan kolom yang dihubungkan walaupun salah satu kolom penghubungnya tidak berpasangan.

Syntax :
Select … from tabelA [left|right] outer join tabelB
on tabelA.namakolom operator tabelB.namakolom

Keterangan :
• Kata outer harus ditulis dan didahului kata left atau right
• Kata left dipilih bila nama tabel disebelah kiri kata join yang menjadi acuan
• Kata right dipilih bila nama tabel disebelah kanan kata join yang menjadi acuan
• Klausa where, order by dll dapat disertakan

Contoh :
Menampilkan daftar penerbit & nama-nama pengarang yang berada di kota yang sama (semua penerbit ditampilkan walaupun tidak ada nama pengarang yang satu kota)

Select p.Pub_name, a.Au_Lname, a.Au_Fname from Publishers p
left outer join Authors a on p.City = a.City

dalam syntax lain :

Select p.Pub_name, a.Au_Lname, a.Au_Fname from Publishers p, Authors a
where p.City *= a.City





Membuat Fungsi Sendiri
Ada beberapa jenis fungsi yang dapat kita buat sendiri (User Defined Function), yaitu :
1. Inline Table – valued Function
2. Multi-statement– valued Function
3. Scalar– valued Function

SUBQUERY

Definisi Subquery
Subquery adalah query yang menjadi bagian / ditulis dalam statement lain. Statement bisa berupa SELECT, INSERT, UPDATE ataupun DELETE. Satu atau lebih subquery yang berada dalam query yang lain disebut nested query.

Syntax untuk SELECT statement yang mengandung subquery:
Select … from namatabel
where namakolom operator [ALL|ANY] (select … from namatabel where …)

atau

Select … from namatabel
where [NOT] EXISTS (select … from namatabel where …)

Keterangan :
• Subquery ditulis dalam tanda kurung (…)
• Subquery tidak boleh menggunakan ORDER BY atau COMPUTE BY
• Bila subquery dengan hasil nilai tunggal digunakan operator: =, >, <, <=, >=, !=
• Bila subquery dengan hasil nilai tidak tunggal maka ALL|ANY harus disertakan (seperti: >ALL, >ANY, =ANY, <>ANY, <>ALL dll)
• =ANY dapat diganti dengan IN (sama dengan salah satu)
• <>ANY dapat diganti dengan NOT IN (tidak sama dengan salah satupun)
• [NOT] EXISTS untuk pengecekan, bila benar maka statement dikerjakan, semisal dg
If exists (select … from namatabel where …) Select … from namatabel

Contoh :
Menampilkan ID buku, Judul buku yang harganya lebih mahal dari buku yang berjudul ”Sushi, Anyone?” (ID=”TC7777”)

Select Title_Id, Title from Titles
where price > (select price from Titles where Title_Id=’TC7777’)



1. ganti kata Gear -> GEAR pada namabarang
2. cari jumlah barang di faktur tertentu => jumlahbarang(NoFJ) -> integer
3. cari total (qty*harga) di tabel FJDet => Total QtyHarga(NoFJ) ->money
4. jumlah nama barang mengandung kota tertentu pada tabel barang =>jumlahbarang (NamaBarang)->integer
5. jumlah barang yang stoknya kurang dari x => jumlah barang stok kurang(10)->integer
6. jumlah pelanggan yang piutang lebih besar x => jumlah pelanggan piutang (1000)->integer

1. ALTER FUNCTION UpperGear
(
@P1 varchar(50)
)
RETURNS varchar(50)
AS
BEGIN

DECLARE @Result varchar(50)


SELECT @Result =replace(@P1,'Gear','GEAR') from Barang
WHERE @P1 like '%gear%'

RETURN @Result

END
GO

SELECT dbo.UpperGear(namabarang) from Barang
where namabarang like '%gear%'
3. CREATE FUNCTION function3
(
@P1 nvarchar(12)
)
RETURNS money
AS
BEGIN

DECLARE @Result money


SELECT @Result =sum(qty*harga) from FJDet
WHERE NoFJ = @P1

RETURN @Result

END
GO

SELECT distinct dbo.FUNCTION3('FJ-0000001') from FJDet

4.alter FUNCTION function4
(
@P1 varchar(50)
)
RETURNS int

AS
BEGIN

DECLARE @Result int


SELECT @Result =count(KodeBarang) from barang
WHERE NamaBarang like '%' + @P1 + '%'

RETURN @Result

END
GO

SELECT dbo.FUNCTION4('et')

5.CREATE FUNCTION FuncNo5 (@p1 int)
RETURNS int
AS
BEGIN
DECLARE @pHasil int

SELECT @pHasil = COUNT(KodeBarang)
FROM Barang
WHERE Stok < @p1

RETURN @pHasil
END

SELECT dbo.FunctNo5(10)

6.CREATE FUNCTION FuncNo6 (@p1 money)
RETURNS int
AS
BEGIN
DECLARE @pHasil int

SELECT @pHasil = COUNT(KodePelanggan)
FROM Pelanggan
WHERE Piutang > @p1

RETURN @pHasil
END

SELECT dbo.FuncNo6(1000)

1. Buat View :
a. Vw_Pasien untuk menampilkan seluruh pasien
b. Vw_Dokter untuk menampilkan seluruh dokter
2. SP_Lihat_Pasien (@Tahun char(2), @Bulan char(2)) untuk menampilkan pasien yang terdaftar pada tahun dan bulan tsb (sesuai parameter) Catatan : Format ID_Pasien sbb : ‘P’+ Tahun +Bulan + Nomor Urut per Bulan
a. Buat SQL untuk membuat Store Procedure tsb
b. Buat SQL untuk menjalankan SP tersebut
3. SP_Lihat_GolDarah (@Gol_Darah char(2)) untuk menampilkan data pasien yang bergolongan darah tertentu
a. Buat SQL untuk membuat Store Procedure tsb
b. Buat SQL untuk menjalankan SP tersebut
4. SP_Tambah_Pasien, parameter semua field kecuali ID_PASIEN dibuat otomatis (autonumber) , Nomor Urut Reset per Bulan.
a. Buat SQL untuk membuat Store Procedure tsb
b. Buat SQL untuk menjalankan SP tersebut
5. Buat Fungsi untuk menghitung :
a. FHitungUmur : menghitung umur pasien jika dimasukkan tanggal lahir. Keluaran : xx Tahun xx Bulan dalam bentuk karakter.
b. FHitungJumlahDokter : menghitung jumlah dokter jika dimasukkan spesialisasi. Keluaran : xx dalam bentuk integer
c. FHitungJumlahPasienRawat : menghitung jumlah pasien yang sedang dirawat di RS, tanpa input. Keluaran xx dalam bentuk integer.

1a.SELECT id_pasien, nama, alamat, tgl_lahir, no_hp
FROM dbo.t_pasien



1b. SELECT id_dokter, nama, spesialisasi, biaya
FROM dbo.t_dokter


2a. CREATE PROCEDURE SP_Lihat_Pasien
@Tahun char(2),
@Bulan char(2)
AS
BEGIN

SELECT *
FROM dbo.t_pasien
WHERE id_pasien like 'P'+@Tahun+@Bulan+'%'
END
GO

2b. exec dbo.SP_Lihat_Pasien '07','10'




3a. CREATE PROCEDURE SP_Lihat_GolDarah
@Gol_Darah char(2)
AS
BEGIN

SELECT *
FROM dbo.t_pasien
WHERE gol_darah = @Gol_Darah
END
GO

3b. exec dbo.SP_Lihat_GolDarah 'A'

4a. CREATE PROCEDURE SP_Tambah_Pasien
@Nama char(2)
AS
BEGIN
INSERT INTO dbo.t_pasien(Nama)
VALUES (@Nama)
END
GO
4b. exec dbo.SP_Tambah_Pasien 'Ujang Nurdin'
go
SELECT * FROM dbo.t_pasien


5a. CREATE FUNCTION FHitungUmur
(
@P1 datetime
)
RETURNS int
AS
BEGIN

DECLARE
@Result varchar(50),
@Tahun char(2),
@Bulan char(2)


SELECT @Tahun = datediff(dd,@P1,getdate())/360/30
SELECT @Bulan = datediff(dd,@P1,getdate())/360
SELECT @Result = @Tahun + @Bulan


RETURN @Result

END
GO
5b. CREATE FUNCTION FHitungJumlahDokter
(
@P1 varchar(50)
)
RETURNS int
AS
BEGIN

DECLARE @Result int


SELECT @Result =count(id_dokter) from dbo.t_dokter
WHERE spesialisasi = @P1

RETURN @Result

END
GO

5c. CREATE FUNCTION FHitungJumlahPasienRawat
(
)
RETURNS int
AS
BEGIN

DECLARE @Result int


SELECT @Result =count(id_pasien) from dbo.t_rawat
WHERE tgl_keluar IS NULL

RETURN @Result

END
GO

select dbo.FHitungJumlahPasienRawat()