Kamis, 13 Januari 2011

Bahan 5 - Function

Definisi Fungsi

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

Kelompok Fungsi
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.



Berikut ini ditabelkan kegunaan dari beberapa fungsi yang penting untuk diketahui

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')



Join

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

Untuk membuat, klik pada bagian Function sbb :



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’)
a.    FHitungJumlahDokter : menghitung jumlah dokter jika dimasukkan spesialisasi.  Keluaran : xx dalam bentuk integer
alter function No5b
          (@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

select dbo.No5b ('umum')

b.    FHitungJumlahPasienRawat : menghitung jumlah pasien yang sedang dirawat di RS, tanpa input.  Keluaran xx dalam bentuk integer.
create function No5c ()
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.No5c()


1.     Ganti kata Gear menjadi GEAR pada nama barang
create function No1
(
          @p1 varchar(50)
)
returns varchar(50)
as
begin
declare @result varchar(50)
select @result = replace(@p1, 'Gear', 'GEAR')
return @result

END
GO

select dbo.No1(NamaBarang) from Barang
where NamaBarang like '%Gear%'

2.    Cari jumlah barang di faktur tertentu  dengan parameter NoFJ(tipenya integer)
create function No2
          (@p1 varchar(50))
returns int
as
begin
declare @result int
select @result = COUNT(KodeBarang) from FJDet
where NoFJ = @p1
return @result

END
GO

select DISTINCT dbo.No2('FJ-0000001')
from FJDet

3.    Cari Total Qty*Harga dari tabel FJDet, parameternya NoFJ(tipenya  money)

create function No3
          (@p1 varchar(50))
returns money
as
begin
declare @result money
select @result = SUM(Qty*Harga) from FJDet
where NoFJ = @p1
return @result

END
GO

select DISTINCT dbo.No3('FJ-0000001')
from FJDet

4.    Jumlah Nama Barang mengandung kata tertentu pada tabel Baran, parameternya Nama Barang (tipenya integer)
alter function No4
          (@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.No4('gear')



LATIHAN FUNGSI

DIKETAHUI SEBUAH DATABASE SIPP, berisi data penjualan dan pembelian.
1.    Seorang operator ingin mengetahui nomor pelanggan terakhir dan nomor pelanggan yang baru.  Cari di table Pelanggan.  Hasil disimpan di variable @NomorAkhir dan @NomorBaru
Jawaban model 1 :
Declare @NomorAkhir nvarchar(10), @NomorBaru nVarchar(10)
Declare @X int

select top 1 @NomorAkhir=kodepelanggan
from pelanggan
order by kodepelanggan desc

PRINT @NomorAkhir

Set @x = right(@NomorAkhir,5) + 1
print @x

Set @NomorBaru = 'C-'+   right('00000' + CONVERT(varchar,@X),5)
PRINT @NomorBaru


Jawaban Model 2 (menggunakan max):

Declare @NomorAkhir nvarchar(10), @NomorBaru nVarchar(10)
Declare @X int

select @NomorAkhir=max(kodepelanggan)
from pelanggan

PRINT @NomorAkhir

Set @x = right(@NomorAkhir,5) + 1
print @x

Set @NomorBaru = 'C-'+   right('00000' + CONVERT(varchar,@X),5)

PRINT @NomorBaru

2.    Seorang operator ingin mengetahui nomor bukti penjualan terakhir dan nomor yang baru.  Cari di table FJ.  Hasil disimpan di variable @NomorAkhir dan @NomorBaru
Declare @NomorAkhir nvarchar(10), @NomorBaru nVarchar(10)
Declare @X int

select @NomorAkhir=max(noFJ)
from FJ

PRINT @NomorAkhir

Set @x = right(@NomorAkhir,7) + 1
print @x

Set @NomorBaru = 'FJ-'+   right('0000000' + CONVERT(varchar,@X),7)

PRINT @NomorBaru


3.    Munculkan semua transaksi penjualan yang hari selasa
a.    Tampilkan semua transaksi tersebut
b.    Ada Berapa jumlah transaksi
c.    Berapa totaltransaksi (dilihat dari TotalFaktur)
--Day of Week : 1=Minggu, 2=Senin, 3=Selasa
select NoFJ, TglFJ, TotalFaktur
from FJ
Where datepart(dw,tglfj)=3

Declare @JumlahFJ money, @TotalFJ money
select @JumlahFJ=Count(*), @TotalFJ=sum(TotalFaktur)
from FJ
Where datepart(dw,tglfj)=3

Select @JumlahFJ as JumlahFaktur, @TotalFJ as TotalFaktur

4.    Seorang operator ingin mengetahui INISIAL PELANGGAN (Diambil dari huruf pertama dari setiap kata pada NamaPelanggan
Jawaban : mencari posisi spasi menggunakan charindex

select namapelanggan, left(namapelanggan,1) +
             substring( namapelanggan, charindex(' ', namapelanggan)+1, 1)
from pelanggan


5.    Tampilan TotalPenjualan  Tanggal 1, 2, 3, 4, 5, 6, dan 7 Juli 2005.   Simpan di variable @Total1, @Total2, @Total3, @Total4, @Total5, @Total6 dan @Total7
Declare @Total1 money, @Total2 money, @Total3 money, @Total4 money, @Total5 money, @Total6  money, @Total7 money

Select @Total1=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/01/2005' and '07/01/2005 23:59:59'

Select @Total2=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/02/2005' and '07/02/2005 23:59:59'

Select @Total3=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/03/2005' and '07/03/2005 23:59:59'

Select @Total4=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/04/2005' and '07/04/2005 23:59:59'

Select @Total5=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/05/2005' and '07/05/2005 23:59:59'

Select @Total6=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/06/2005' and '07/06/2005 23:59:59'

Select @Total7=Sum(TotalFaktur)
From FJ
Where TglFJ between '07/02/2005' and '07/02/2005 23:59:59'

Select @Total1 as Tg1, @Total2 as Tg2, @Total3 as Tg3, @Total4 as Tg4, @Total5 as Tg5, @Total6 as Tg6, @Total7 as Tg7



alter function No1

( @tglAwal datetime, @tglAkhir datetime)
returns money
as
begin
      declare @result money
      select @result = SUM(TotalFaktur)
      from FJ
      where TglFJ between @tglAwal and @tglAkhir + ' 23:59:59'
      return @result
end

select dbo.No1 ('6/1/2005', '6/30/2005')


alter function No2
( @tglAwal datetime, @tglAkhir datetime)
returns int
as
begin
      declare @result int
      select @result = COUNT (DISTINCT(KodePelanggan))
      from FJ
      where TglFJ between @tglAwal and @tglAkhir + ' 23:59:59'
      return @result
end

select dbo.No2 ('6/1/2005', '6/30/2005')


alter function No3
( @tglAwal datetime, @tglAkhir datetime)
returns int
as
begin
      declare @result int
      select @result = sum(fd.Qty)
      from FJ f join FJDet fd on (f.NoFJ = fd.NoFJ)
      where f.TglFJ between @tglAwal and @tglAkhir + ' 23:59:59'
      return @result
end

select dbo.No3 ('6/1/2005', '6/30/2005')


create function No4
( @tglAwal datetime, @tglAkhir datetime)
returns money
as
begin
      declare @result money
      select @result = sum(fd.Qty*fd.Harga)
      from FJ f join FJDet fd on (f.NoFJ = fd.NoFJ)
      where f.TglFJ between @tglAwal and @tglAkhir + ' 23:59:59'
      return @result
end

select dbo.No4 ('6/1/2005', '6/30/2005')

Tidak ada komentar:

Posting Komentar