BlagBligBlugBleg Blog

Kamis, 20 Januari 2011

Transaction

Apa itu transaction?
Sebuah transaksi adalah sekelompok perintah yang harus dijalankan, hasilnya berhasil atau gagal untuk semua perintah tersebut
Apa pentingnya transaction?
Lihat contoh berikut : misal diperlukan perubahan stok setiap kali ada penjualan 1 item barang

lat 1
CREATE TABLE tbCustomer
(
IdCust varchar(10),
Customer varchar(30),
primary key (IdCust)
)

--lihat data sebelum di update
SELECT * FROM tbCustomer
BEGIN TRAN

--lakukan insert
INSERT tbCustomer VALUES ('001','Nugraha')
INSERT tbCustomer VALUES ('002', 'Agung')

--lihat data sesudah diinsert
SELECT * FROM tbCustomer

ROLLBACK TRAN

--lihat data sesudah dirollback
SELECT * FROM tbCustomer

lat 2
--lihat data sebelum diupdate
SELECT * FROM tbCustomer
BEGIN TRAN

--lakukan insert
INSERT tbCustomer VALUES ('001','Nugraha')
INSERT tbCustomer VALUES ('002', 'Agung')

--lihat data sesudah diinsert
SELECT * FROM tbCustomer

COMMIT TRAN

--lihat data sesudah dirollback
SELECT * FROM tbCustomer

lat 3
--lihat data sebelum diupdate
SELECT * FROM tbCustomer
BEGIN TRAN

--lakukan insert
INSERT tbCustomer VALUES ('001','Nugraha')
INSERT tbCustomer VALUES ('002', 'Agung')
INSERT tbCustomer VALUES ('003', 'Michael')

IF @@ERROR != 0
BEGIN
PRINT 'ERROR, Insert data gagal'
ROLLBACK TRAN
END
ELSE
BEGIN
PRINT 'Berhasil'
COMMIT TRAN
END
GO

--lihat data sesudah di rollback
SELECT * FROM tbCustomer

lat 4
CREATE TABLE tbCustomer1 (
IdCust varchar(10),
Customer varchar(30),
Piutang money default 0,
primary key (IdCust)
)

CREATE TABLE tbJual(
NoJual varchar(10),
IdCust varchar(10),
Jumlah money,
primary key (NoJual)
)

CREATE PROCEDURE spInsert
@NoJual varchar(10),
@IdCust varchar(10),
@Jumlah money
AS
DECLARE @piutang money
BEGIN TRAN
SELECT @piutang = piutang from dbo.tbCustomer1 WHERE IdCust = @IdCust
IF @piutang is null SET @piutang=0
UPDATE dbo.tbCustomer1 SET Piutang=@piutang + @Jumlah WHERE IdCust=@IdCust
INSERT dbo.tbJual (NoJual, IdCust, Jumlah) VALUES (@NoJual, @IdCust, @Jumlah)
IF @@ERROR != 0
ROLLBACK TRAN
ELSE
COMMIT TRAN

INSERT dbo.tbCustomer1(IdCust,Customer,Piutang) VALUES ('C001','Antoni',0)
SELECT * FROM dbo.tbCustomer1
exec dbo.spInsert 'J001', 'C001',1000
SELECT * FROM dbo.tbCustomer1

SELECT * FROM dbo.tbCustomer1
exec dbo.spInsert 'J002', 'C001',1000
SELECT * FROM dbo.tbCustomer1

lat 5
create table tbNilai (
nim varchar(7),
kodemk varchar(7),
nilai char(1),
primary key (nim,kodemk)
)

create trigger trTbNilai_Insert on tbNilai
for insert, update
as
declare @nilai char(1)
select @nilai=nilai from inserted
if @nilai not in ('A','B','C','D','E')
begin
raiserror ('nilai harus A-E', 1,1)
ROLLBACK TRANSACTION
end

insert dbo.tbNilai values ('001','IF002','A')
select * from dbo.tbNilai
insert dbo.tbNilai values ('002','IF002','B')
select * from dbo.tbNilai
insert dbo.tbNilai values ('003','IF002','F')
select * from dbo.tbNilai

LATIHAN TRIGGER DAN TRANSACTION HUTANG
1.Buat DBhutang
2.Buat Tabel
a.Pemasok (IDPemasok, Nama, Hutang),
b.Beli (IDBeli, IDPemasok, TotalBeli),
c.Bayar (IDBayar, IDPemasok, TotalBayar)
3.Buat Relasi antara Pemasok dengan Beli dan Pemasok dengan Bayar
4.Buat TrgInsertBeli (menempel pada table Beli) yang fungsinya menambah Hutang pada table Pemasok.
5.dan TrgInsertBayar (menempel pada table Bayar) yang fungsinya mengurangi Hutang pada table Pemasok.
Untuk menguji TrgInsertBeli :
--Data Hutang Pelasok sebelumnya
Select * from Pemasok
--Insert Pembelian
Insert Into Beli …..
--Data Hutang Pemasok sesudahnya
Select * from Beli
Select * from Pemasok

Untuk menguji TrgInsertBayar :
--Data Hutang Pemasok sebelumnya
Select * from Pemasok
--Insert Pembayaran
Insert Into Bayar …..
--Data Hutang Pemasok sesudahnya
Select * from Bayar
Select * from Pemasok

CREATE TRIGGER TrgInsertBeli
ON dbo.Beli
AFTER INSERT
AS
BEGIN

DECLARE @IDPemasok nvarchar(50)
DECLARE @TotalBeli money

SET NOCOUNT ON;

SELECT @IDPemasok=IDPemasok,@TotalBeli=TotalBeli
FROM INSERTED

UPDATE dbo.Pemasok
SET Hutang = Hutang + @TotalBeli
WHERE @IDPemasok=IDPemasok

END
GO

--Data Hutang Pelasok sebelumnya
Select * from Pemasok
Insert Into Pemasok(IDPemasok,Nama,Hutang)
values ('P002','william',10000)
--Insert Pembelian
Insert Into Beli(IDBeli,IDPemasok,TotalBeli)
values ('B003','P001',5000)
--Data Hutang Pemasok sesudahnya
Select * from Beli
Select * from Pemasok

CREATE TRIGGER TrgInsertBayar
ON dbo.Bayar
AFTER INSERT
AS
BEGIN

DECLARE @IDPemasok nvarchar(50)
DECLARE @TotalBayar money

SET NOCOUNT ON;

SELECT @IDPemasok=IDPemasok,@TotalBayar=TotalBayar
FROM INSERTED

UPDATE dbo.Pemasok
SET Hutang = Hutang - @TotalBayar
WHERE @IDPemasok=IDPemasok

END
GO

--Data Hutang Pemasok sebelumnya
Select * from Pemasok
--Insert Pembayaran
Insert Into Bayar(IDBayar,IDPemasok,TotalBayar)
values('B002','P001',20000)
--Data Hutang Pemasok sesudahnya
Select * from Bayar
Select * from Pemasok

1.Buat SP_BukuTabungan menggunakan cursor yang menampilkan isi buku tabungan tiap nasabah dengan format tampilan : (contoh salah satu nasabah… diminta untuk membuat semua nasabah)

------------------------------------------------------------------------------------------------------------------------------------------
No Rekening : 200202050000003
Jenis Tabungan : Giro
Nama : Heidy
Alamat : Cimahi
-------------------------------------------------------------------------------------------------------------------------------------------
No. Tanggal NIK Keterangan Debet Kredit Saldo
-------------------------------------------------------------------------------------------------------------------------------------------
1 9/3/2009 ATM Tunai ATM 200000 0 4050000
2 9/4/2009 ATM Transfer ke 200401010000001 750000 0 3300000
3 9/4/2009 T-0002 Setoran Tunai 0 200000 3500000
-------------------------------------------------------------------------------------------------------------------------------------------
Simpan source codenya dalam SP_BukuTabungan.sql dan simpan screen shot hasil execnya di SPBuku.jpeg (bila tidak muat buat bbrp screen shot, beri nomor spt SPBuku1, SPBuku2, dst) BOBOT : 35

BEGIN
DECLARE csrNasabah CURSOR
FOR SELECT No_Rekening, Jenis_Tabungan, Nama, Alamat FROM tbNasabah

OPEN csrNasabah

DECLARE @NoRek varchar(15)
DECLARE @JenTab varchar(20)
DECLARE @Nama varchar(50)
DECLARE @Alamat varchar(100)

FETCH NEXT FROM csrNasabah into @NoRek, @JenTab, @Nama, @Alamat
WHILE @@FETCH_STATUS=0
BEGIN
PRINT '----------------------------------------------------------------------------------------------------'
PRINT 'No Rekening : '+@NoRek
PRINT 'Jenis Tabungan : '+@JenTab
PRINT 'Nama : '+@Nama
PRINT 'Alamat : '+@Alamat
PRINT '----------------------------------------------------------------------------------------------------'
PRINT 'No. Tanggal NIK Keterangan Debet Kredit Saldo'
PRINT '----------------------------------------------------------------------------------------------------'

DECLARE csrTransaksi CURSOR
FOR SELECT Tanggal, NIK, Keterangan, Jenis_trans, Jumlah, Saldo FROM tbTransaksi WHERE No_Rekening = @NoRek

OPEN csrTransaksi

DECLARE @No int SET @No = 1
DECLARE @Tanggal smalldatetime
DECLARE @NIK char(10)
DECLARE @Keterangan char(30)
DECLARE @Jenis varchar(20)
DECLARE @Jumlah money
DECLARE @Saldo money
DECLARE @JumlahD money
DECLARE @JumlahK money

FETCH FROM csrTransaksi into @Tanggal, @NIK, @Keterangan, @Jenis, @Jumlah, @Saldo
WHILE @@FETCH_STATUS=0
BEGIN
IF (@Jenis = 'Debet')
BEGIN
SET @JumlahD = @Jumlah
SET @JumlahK = 0
END
ELSE
BEGIN
SET @JumlahD = 0
SET @JumlahK = @Jumlah
END

PRINT CONVERT(char(5),@No)+''+CONVERT(char(12),@Tanggal)+' '+@NIK+' '+@Keterangan+' '+CONVERT(char(12),@JumlahD)+''+CONVERT(char(12),@JumlahK)+''+CONVERT(char(12),@Saldo)

SET @No = @No + 1
FETCH NEXT FROM csrTransaksi into @Tanggal, @NIK, @Keterangan, @Jenis, @Jumlah, @Saldo
END
PRINT '----------------------------------------------------------------------------------------------------'
PRINT ''
PRINT ''

CLOSE csrTransaksi
DEALLOCATE csrTransaksi
FETCH NEXT FROM csrNasabah into @NoRek, @JenTab, @Nama, @Alamat
END
CLOSE csrNasabah
DEALLOCATE csrNasabah
END

-- Create table to work with
CREATE TABLE MyTranTest
(
OrderID INT PRIMARY KEY IDENTITY
)
select * from mytrantest ---------------------(A)

BEGIN TRAN TranStart

INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES

select * from mytrantest ---------------------(B)

SAVE TRAN FirstPoint
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(C)

ROLLBACK TRAN FirstPoint
select * from mytrantest ---------------------(D)

INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(E)

SAVE TRAN SecondPoint
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(F)

ROLLBACK TRAN SecondPoint
select * from mytrantest
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
INSERT INTO MyTranTest
DEFAULT VALUES
select * from mytrantest ---------------------(G)

COMMIT TRAN TranStart
SELECT TOP 3 OrderID
FROM MyTranTest
ORDER BY OrderID ASC ---------------------(H)

Backup and Restore

BACKUP :
1.Manual
2.Otomatis

JENIS BACKUP :
1.FULL : Semua database di backup
2.DIFFERENTIAL : Hanya perubahan teakhir yang dibackup

Saran :
lakukan backup full minimal 1 hari sekali (pada jam tidak sibuk), sisanya lakukan differential berulang-ulang (pada jam sibuk)

Cursor

Cursor adalah suatu perintah pemrograman yang mengijinkan setiap baris (record) diproses secara terpisah terhadap record lainnya. Cursor sangat bermanfaat jika ada suatu proses yang rumit yang ingin dilakukan terhadap suatu record. Proses tersebut bisa dilakukan secara satu per satu, yaitu lewat cursor.

Algoritma CURSOR
1.Mendeklarasikan Cursor
2.Membuka Cursor
3.Memproses Cursor
4.Menutup Cursor
5.Mendealokasikan Cursor

--1 Mendeklarasikan Cursor
DECLARE CsrMsiswa CURSOR
FOR Select * from Msiswa Where NIK>=2 and NIK <=4

--2 Membuka Cursor
OPEN CsrMsiswa

--3 Memproses Cursor
DECLARE @NIK int
DECLARE @NAMA char(30)

FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'NIK :'+CONVERT(CHAR,@NIK)
PRINT 'NAMA :'+@NAMA

FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
END

--4 Menutup Cursor
CLOSE CsrMsiswa

--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa

Ada lima tipe scrollable cursor yang data digunakan saat memproses cursor yaitu :
-Prior
-Next
-First
-Last
-Relative (+/- n)
Untuk mengaktifkan scrollable cursor tambahkan sintak SCROLL saat mendeklarasikan CURSOR, sbb :
--1.Deklarasi cursor
DECLARE CsrKaryawan SCROLL CURSOR
FOR select NIK, NAMA from Karyawan

--2.Buka cursor
Open CsrKaryawan

--3.Proses cursor
DECLARE @nik CHAR(10), @nama VARCHAR(30)

FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

FETCH NEXT FROM csrKaryawan into @nik, @nama
PRINT @NIK + @NAMA

--4.Tutup Cursor
Close CsrKaryawan

--5.Dealokasi Cursor
Deallocate CsrKaryawan

alter procedure SP_CURSOR2 AS
BEGIN

--1 Mendeklarasikan Cursor
DECLARE CsrMsiswa CURSOR
FOR Select NIK,NAMA from Msiswa

--2 Membuka Cursor
OPEN CsrMsiswa

--3 Memproses Cursor
DECLARE @No int
DECLARE @NIK int
DECLARE @NAMA char(30)

SET @No = 1

print 'daftar hadir mahasiswa'
print 'Tanggal : 28-04-2008'
PRINT '-----------------------------------------'
print '|'+'No '+'|'+'NIK '+'|'+'Nama '+ '|'+'TTD '+'|'
PRINT '-----------------------------------------'
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
WHILE @@FETCH_STATUS=0
BEGIN

PRINT '|'+CONVERT(CHAR(10),@No)+'|'+ CONVERT(CHAR(10),@NIK) + '|'+CONVERT(CHAR(10),@NAMA) +'|'+ ' '+'|'

SET @No = @No+1
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
END
PRINT '-----------------------------------------'
print '|'+' '+'|'+' '+'|'+'Dosen: '+ '|'+'Paraf:'+'|'
PRINT '-----------------------------------------'

--4 Menutup Cursor
CLOSE CsrMsiswa

--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa

END
GO

EXEC SP_CURSOR2

ALTER procedure SP_CURSOR4 AS
BEGIN

--1 Mendeklarasikan Cursor
DECLARE CsrMsiswa CURSOR
FOR Select Department,count(NIK) from Msiswa
GROUP BY Department

--2 Membuka Cursor
OPEN CsrMsiswa

--3 Memproses Cursor
DECLARE @Total int
DECLARE @Jumlah int
DECLARE @Count int
DECLARE @Department nvarchar(15)

SET @Jumlah = 0
--set @Total = 0

PRINT '---------------------------'
PRINT '|DEPARTMENT |JUMLAH ORANG|'
PRINT '---------------------------'

FETCH NEXT FROM CsrMsiswa into @Department, @Count
WHILE @@FETCH_STATUS=0
BEGIN
print '|'+convert(char(3),@Department) + ' |' + convert(char(5),@count) + ' orang'+' |'
set @Jumlah = @Jumlah+@Count

FETCH NEXT FROM CsrMsiswa into @Department, @Count
END
PRINT '---------------------------'
PRINT '|'+'total |'+convert(char(5),@Jumlah)
PRINT '---------------------------'
--4 Menutup Cursor
CLOSE CsrMsiswa

--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa

END
GO

EXEC SP_CURSOR4

alter procedure SP_CURSOR3 AS
BEGIN

--1 Mendeklarasikan Cursor
DECLARE CsrMsiswa CURSOR
FOR Select NIK,NAMA,Gaji,Department from Msiswa

--2 Membuka Cursor
OPEN CsrMsiswa

--3 Memproses Cursor
DECLARE @No int
DECLARE @NIK int
DECLARE @NAMA char(30)
DECLARE @Department nvarchar(15)
declare @Gaji money

SET @No = 1

PRINT '----------------------------------------------------'
print '|'+'No '+'|'+'NIK '+'|'+'Nama '+ '|'+'GAJI '+'|'+'Department|'
PRINT '----------------------------------------------------'
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA, @Gaji,@Department
WHILE @@FETCH_STATUS=0
BEGIN

PRINT '|'+CONVERT(CHAR(10),@No)+'|'+ CONVERT(CHAR(10),@NIK) + '|'+CONVERT(CHAR(10),@NAMA) +'|'+ convert(char(7),@Gaji)+'|'+convert(char(3),@Department)+' |'

SET @No = @No+1
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA,@Gaji,@Department
END
PRINT '----------------------------------------------------'

--4 Menutup Cursor
CLOSE CsrMsiswa

--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa

END
GO

EXEC SP_CURSOR3

alter procedure SP_CURSOR3 AS
BEGIN

--1 Mendeklarasikan Cursor
DECLARE CsrMsiswa CURSOR
FOR Select NIK,NAMA,Gaji,Department from Msiswa

--2 Membuka Cursor
OPEN CsrMsiswa

--3 Memproses Cursor
DECLARE @No int
DECLARE @NIK int
DECLARE @NAMA char(30)
DECLARE @Department nvarchar(15)
declare @Gaji money

SET @No = 1

PRINT '-----------------------------------------------------------------'
print '|'+'No '+'|'+'NIK '+'|'+'Nama '+ '|'+'1 '+'|2 '+'|3 '+'|4 '+'|5 '+'|Rata-2|'
PRINT '-----------------------------------------------------------------'
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA, @Gaji,@Department
WHILE @@FETCH_STATUS=0
BEGIN

PRINT '|'+CONVERT(CHAR(10),@No)+'|'+ CONVERT(CHAR(10),@NIK) + '|'+CONVERT(CHAR(10),@NAMA) + '| '+'| '+'| '+'| '+'| '+'| |'

SET @No = @No+1
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA,@Gaji,@Department
END
PRINT '-----------------------------------------------------------------'
PRINT '|'+' '+ ' ' + ' '+' '+' '+' '+' '+' |'+' |'
PRINT '-----------------------------------------------------------------'

--4 Menutup Cursor
CLOSE CsrMsiswa

--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa

END
GO

EXEC SP_CURSOR3

Buat CURSOR dalam Store Procedure untuk menampilkan data statistik nilai mahasiswa terdiri dari :
Jumlah Nilai A : …. Orang
Jumlah Nilai B : …. Orang
Jumlah Nilai C : …. Orang
Jumlah Nilai D : …. Orang
Jumlah Nilai E : …. Orang

Dengan ketentuan :

Nilai Angka Nilai Mutu
85 – 90 A
70 – 84 B
55 – 69 C
40– 54 D
0 - 39 E

Kode mata makuliah dimasukan melalui parameter

Buat store procedure dan transact sql untuk ekesekusinya.

ALTER PROCEDURE SpStatistik
@MK varchar(5)
AS
BEGIN

DECLARE csrNilai CURSOR
FOR SELECT tbNilai.NILAI FROM tbNilai
WHERE MK=@MK

OPEN csrNilai

DECLARE @NRP varchar(7)
DECLARE @NILAI int
DECLARE @MUTU varchar(1)

FETCH NEXT FROM csrNilai INTO @NILAI
DECLARE @1 INT,@2 INT,@3 INT,@4 INT,@5 INT
SET @1=0
SET @2=0
SET @3=0
SET @4=0
SET @5=0
WHILE @@FETCH_STATUS=0
BEGIN
IF(@NILAI>85 AND @NILAI<90)
SET @1 = @1+1
IF (@NILAI>70 AND @NILAI<84)
SET @2 = @2+1
IF (@NILAI>55 AND @NILAI<69)
SET @3 = @3+1
IF (@NILAI>40 AND @NILAI<54)
SET @4 = @4+1
IF (@NILAI>0 AND @NILAI<39)
SET @5 = @5+1
FETCH NEXT FROM csrNilai INTO @NILAI
END

CLOSE csrNilai
DEALLOCATE csrNilai

print 'Jumlah nilai A:'+CONVERT(CHAR(5),@1)+'orang'
print 'Jumlah nilai B:'+CONVERT(CHAR(5),@2)+'orang'
print 'Jumlah nilai C:'+CONVERT(CHAR(5),@3)+'orang'
print 'Jumlah nilai D:'+CONVERT(CHAR(5),@4)+'orang'
print 'Jumlah nilai E:'+CONVERT(CHAR(5),@5)+'orang'

END
GO

EXEC SpStatistik 'MK01'

SQLQueryDB-PTABC
USE [PTABC]
GO
/****** Object: Table [dbo].[Karyawan] Script Date: 05/07/2008 09:30:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Karyawan](
[NIK] [char](4) NOT NULL,
[NAMA] [varchar](30) NULL,
[GAJI] [money] NULL,
[TRANSPORT] [money] NULL,
[DEPARTEMEN] [nchar](10) NULL,
CONSTRAINT [PK_Karyawan] PRIMARY KEY CLUSTERED
(
[NIK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO KARYAWAN (NIK, NAMA, GAJI, TRANSPORT, DEPARTEMEN)
VALUES ('1001','A',1000,500,'HRD')
GO
INSERT INTO KARYAWAN (NIK, NAMA, GAJI, TRANSPORT, DEPARTEMEN)
VALUES ('1002','B',1000,500,'HRD')
GO
INSERT INTO KARYAWAN (NIK, NAMA, GAJI, TRANSPORT, DEPARTEMEN)
VALUES ('1003','C',2000,750,'IT')
GO
INSERT INTO KARYAWAN (NIK, NAMA, GAJI, TRANSPORT, DEPARTEMEN)
VALUES ('1004','D',2000,750,'IT')
GO
SET ANSI_PADDING OFF
GO
/****** Object: StoredProcedure [dbo].[SP_StrukGaji] Script Date: 05/07/2008 09:30:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[SP_StrukGaji]
@Departemen nchar(10)
AS
BEGIN

--1.Deklarasi cursor
DECLARE CsrKaryawan CURSOR
FOR select * from Karyawan
Where Departemen = @Departemen

--2.Buka cursor
Open CsrKaryawan

--3.Proses cursor
DECLARE @nik CHAR(10), @nama VARCHAR(30), @gaji MONEY, @transport MONEY, @dep CHAR(10)
FETCH NEXT FROM csrKaryawan
into @nik, @nama, @gaji, @transport, @dep

WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'PERIODE :'+CONVERT(CHAR,MONTH(GETDATE())) + CONVERT(CHAR,YEAR(GETDATE()))
PRINT 'NIK :'+@NIK
PRINT 'NAMA :'+@NAMA
PRINT 'DEPARTEMEN :'+@DEP
PRINT 'GAJI :'+CONVERT(CHAR(12),@GAJI)
PRINT 'TRANSPORT :'+CONVERT(CHAR(12),@TRANSPORT)
PRINT '------------------------------- +'
PRINT 'TOTAL :'+CONVERT(CHAR(12),@GAJI+@TRANSPORT)
PRINT ' '

FETCH NEXT FROM csrKaryawan
into @nik, @nama, @gaji, @transport, @dep
END
--4.Tutup Cursor
Close CsrKaryawan

--5.Dealokasi Cursor
Deallocate CsrKaryawan

END
GO

SQLQueryREKAPGAJI
--Ambil dari cursor
--DepLama =’’
--Jika DEP BARU :
--- Cetak lengkap
--- Jumlahkan SubTotal
--- Jumlahkan GrandTotal
--Jika DEP SAMA DENGAN SEBELUMNYA
--- Cetak lengkap, kecuali DEP
--- Jumlahkan SubTotal
--- Jumlahkan GrandTotal
--Jika DEP TIDAK SAMA SEBELUMNYA
--- Cetak Subtotal
--- Cetak lengkap
--- Subtotal reset
--- Jumlahkan Subtotal
--- Jumlahkan GrandTotal
--Jika data habis (eof)
--- Cetak Subtotal
--- Cetak GrandTotal

--1.Deklarasi cursor
DECLARE CsrKaryawan CURSOR
FOR select * from Karyawan
--2.Buka cursor
Open CsrKaryawan
--3.Proses cursor
DECLARE @nik CHAR(10), @nama VARCHAR(30), @gaji MONEY, @transport MONEY, @dep CHAR(10)
DECLARE @STGaji money, @GTGaji money
DECLARE @DEPLAMA char(10), @DEPBARU char(10)
DECLARE @NO INT

SET @STGaji=0
SET @GTGaji=0
SET @DEPLAMA=''
SET @DEPBARU=''
SET @NO=1

FETCH NEXT FROM csrKaryawan into @nik, @nama, @gaji, @transport, @dep
SET @DEPBARU=@DEP

WHILE @@FETCH_STATUS=0
BEGIN
if @NO=1 --DEP BARU
PRINT @DEP + @NIK + @NAMA+ CONVERT(CHAR(12),@GAJI)+CONVERT(CHAR(12),@TRANSPORT)
else
if @deplama<>@depbaru
begin
PRINT '----------' + 'subtotal '+CONVERT(CHAR(12),@STGAJI)
PRINT ' '
SET @STGAJI = 0
PRINT @dep + @NIK + @NAMA+ CONVERT(CHAR(12),@GAJI)+CONVERT(CHAR(12),@TRANSPORT)
end
else
PRINT ' ' + @NIK + @NAMA+ CONVERT(CHAR(12),@GAJI)+CONVERT(CHAR(12),@TRANSPORT)

SET @STGaji = @STGaji + @gaji
SET @GTGaji = @GTGaji + @gaji

FETCH NEXT FROM csrKaryawan
into @nik, @nama, @gaji, @transport, @dep
SET @DEPLAMA = @DEPBARU
SET @DEPBARU = @DEP
SET @NO=@NO+1
END
PRINT '----------' + 'subtotal '+CONVERT(CHAR(12),@STGAJI)
PRINT '==========' + 'GRANDtotal '+CONVERT(CHAR(12),@GTGAJI)

--4.Tutup Cursor
Close CsrKaryawan

--5.Dealokasi Cursor
Deallocate CsrKaryawan

SQLQueryREKAPGAJI2

--1.Deklarasi cursor
DECLARE CsrKaryawan CURSOR FOR select * from Karyawan
--2.Buka cursor
Open CsrKaryawan
--3.Proses cursor
DECLARE @nik CHAR(10), @nama VARCHAR(30), @gaji MONEY, @transport MONEY, @depARTEMEN CHAR(10)
DELETE REKAPGAJI

FETCH NEXT FROM csrKaryawan into @nik, @nama, @gaji, @transport, @departemen

WHILE @@FETCH_STATUS = 0
BEGIN

IF EXISTS (select * from RekapGaji where DEPARTEMEN=@DEPARTEMEN)
UPDATE REKAPGAJI
SET JLHKARYAWAN = JLHKARYAWAN + 1, GAJI = GAJI + @GAJI,
TRANSPORT = TRANSPORT + @TRANSPORT
WHERE DEPARTEMEN = @DEPARTEMEN
ELSE
INSERT REKAPGAJI (DEPARTEMEN, JLHKARYAWAN, GAJI, TRANSPORT)
VALUES(@DEPARTEMEN, 1, @GAJI, @TRANSPORT)

FETCH NEXT FROM csrKaryawan into @nik, @nama, @gaji, @transport, @departemen
END
--4.Tutup Cursor
Close CsrKaryawan

--5.Dealokasi Cursor
Deallocate CsrKaryawan

SELECT * FROM REKAPGAJI

SQLQueryREKAPGAJI-LENGKAP
--OUTPUT :
--=========================================================
--NIK NAMA GAJI TRANSPORT TOTAL
--=========================================================
--HRD 1001 A 1000.00 500.00 1500.00
-- 1002 B 1000.00 500.00 1500.00
------------subtotal 2000.00 1000.00 3000.00
--
--IT 1003 C 2000.00 750.00 2750.00
-- 1004 D 2000.00 750.00 2750.00
------------subtotal 4000.00 1500.00 5500.00
--==========GRANDtotal 6000.00 2500.00 8500.00

--Ambil dari cursor
--DepLama =’’
--Jika DEP BARU :
--- Cetak lengkap
--- Jumlahkan SubTotal
--- Jumlahkan GrandTotal
--Jika DEP SAMA DENGAN SEBELUMNYA
--- Cetak lengkap, kecuali DEP
--- Jumlahkan SubTotal
--- Jumlahkan GrandTotal
--Jika DEP TIDAK SAMA SEBELUMNYA
--- Cetak Subtotal
--- Cetak lengkap
--- Subtotal reset
--- Jumlahkan Subtotal
--- Jumlahkan GrandTotal
--Jika data habis (eof)
--- Cetak Subtotal
--- Cetak GrandTotal

--1.Deklarasi cursor
DECLARE CsrKaryawan CURSOR
FOR select * from Karyawan
--2.Buka cursor
Open CsrKaryawan
--3.Proses cursor
DECLARE @nik CHAR(10), @nama VARCHAR(30), @gaji MONEY, @transport MONEY, @dep CHAR(10)
DECLARE @STGaji money, @GTGaji money, @STTRANSPORT money, @GTTRANSPORT money
DECLARE @DEPLAMA char(10), @DEPBARU char(10)
DECLARE @NO INT

SET @STGaji=0
SET @GTGaji=0
SET @STTransport=0
SET @GTTransport=0

SET @DEPLAMA=''
SET @DEPBARU=''
SET @NO=1
PRINT '========================================================='
PRINT 'NIK NAMA GAJI TRANSPORT TOTAL'
PRINT '========================================================='

FETCH NEXT FROM csrKaryawan into @nik, @nama, @gaji, @transport, @dep
SET @DEPBARU=@DEP

WHILE @@FETCH_STATUS=0
BEGIN
if @NO=1 --DEP BARU
PRINT @DEP + @NIK + @NAMA+ CONVERT(CHAR(12),@GAJI)+CONVERT(CHAR(12),@TRANSPORT)
+CONVERT(CHAR(12),@GAJI+@TRANSPORT)
else
if @deplama<>@depbaru
begin
PRINT '----------' + 'subtotal '+CONVERT(CHAR(12),@STGAJI)
+CONVERT(CHAR(12),@STTRANSPORT)+CONVERT(CHAR(12),@STGAJI+@STTRANSPORT)
PRINT ' '
SET @STGAJI = 0
SET @STTRANSPORT = 0

PRINT @dep + @NIK + @NAMA+ CONVERT(CHAR(12),@GAJI)+CONVERT(CHAR(12),@TRANSPORT)
+CONVERT(CHAR(12),@GAJI+@TRANSPORT)
end
else
PRINT ' ' + @NIK + @NAMA+ CONVERT(CHAR(12),@GAJI)+CONVERT(CHAR(12),@TRANSPORT)
+CONVERT(CHAR(12),@GAJI+@TRANSPORT)

SET @STGaji = @STGaji + @gaji
SET @GTGaji = @GTGaji + @gaji
SET @STTRANSPORT = @STTRANSPORT + @TRANSPORT
SET @GTTRANSPORT = @GTTRANSPORT + @TRANSPORT

FETCH NEXT FROM csrKaryawan
into @nik, @nama, @gaji, @transport, @dep
SET @DEPLAMA = @DEPBARU
SET @DEPBARU = @DEP
SET @NO=@NO+1
END
PRINT '----------' + 'subtotal '+CONVERT(CHAR(12),@STGAJI)
+CONVERT(CHAR(12),@STTRANSPORT)+CONVERT(CHAR(12),@STGAJI+@STTRANSPORT)

PRINT '==========' + 'GRANDtotal '+CONVERT(CHAR(12),@GTGAJI)
+CONVERT(CHAR(12),@GTTRANSPORT)+CONVERT(CHAR(12),@GTGAJI+@GTTRANSPORT)

--4.Tutup Cursor
Close CsrKaryawan

--5.Dealokasi Cursor
Deallocate CsrKaryawan

SQLQuery-STRUKTURREKAPGAJI
USE [PTABC]
GO
/****** Object: Table [dbo].[REKAPGAJI] Script Date: 05/14/2008 09:53:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[REKAPGAJI](
[DEPARTEMEN] [char](10) NULL,
[JLHKARYAWAN] [int] NULL,
[GAJI] [money] NULL,
[TRANSPORT] [money] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Trigger

Definisi Trigger
Seringkali dibutuhkan program yang dapat berjalan sendiri (dijalankan oleh system) sehingga tidak perlu dieksekusi oleh user. Program tersebut biasanya dibuat menggunakan timer yang diatur waktu untuk eksekusi sebuah program. Pada database SQL Server, untuk menjalan program secara otomatis (dieksekusi oleh system) dapat dibuat dengan mudah karena ada fasilitas Trigger. Dengan Trigger ini, program dapat dijalankan karena ada pemicu ketika insert/update/delete terhadap sebuah table. Program yang akan dijalankan otomatis dapat disimpan di Trigger berjenis Insert jika pemicunya adalah Insert, disimpan di Trigger berjenis Delete jika pemicunya adalah Delete, dan disimpan di Trigger berjenis Update jika pemicunya adalah Delete.
Trigger adalah batch/sekumpulan perintah yang secara otomatis dikerjakan ketika terjadi penyisipan (insert), pengubahan (update) atau penghapusan data (delete) pada sebuah tabel.

Trigger :
-Program seperti Store Procedure atau Function yang dijalankan oleh system
-Program ini menempel pada sebuah Tabel, berbeda dengan SP dan Function menempel pada Database
-Dijalankan oleh system jika ada pemicu yaitu Insert terhadap table, Delete ataupun Update.

Tabel Inserted dan Deleted
Saat sebuah trigger Insert dijalankan, maka akan diciptakan sebuah table “Inserted”. Tabel Inserted ini strukturnya akan sama dengan table induknya. Isi table Inserted ini adalah data yang sedang diinsertkan di table. Dengan demikian, kita dapat mengetahui data apa yang sedang diinsertkan ke dalam melalui table ini.
Untuk melihat apa yang di table Inserted dapat dilakukan perintah Select * from Inserted. Perintah ini harus ada didalam trigger Insert.
Nilai getdate() adalah fungsi untuk mengetahui tanggal dan jam system computer saat terjadi insert data pada table, Suser_Sname() adalah pengguna yang menginsert data.

sinkronisasi stok barang
1.TABEL BARANG(Kode(PK),Nama,Stok)
2.tabel jual (nofj,tglfj,kode(fk),qty,harga)
buat trigger untuk sinkronisasi stok
1. saat ada insert data pada tabel jual maka stok barang di tabel barang akan berkurang
2. update
3. delete
4. modifikasi tabel barang menjadi (Kode,Nama,Stok,Jual01,Jual02,Jual03,...,Jual12,
Qty01,Qty02,Qty03,...,Qty12)

buat trigger TrJual
1. update Jual01 dengan @Qty*@Harga saat ada insert/update pada tabel_jual bulan januari
2. update Qty01 dengan @Qty saat ada insert/update pada tabel_jual bulan januari

INSERT INTO [db_sinkronisasi_stok_barang].[dbo].[Tabel_Barang]
([Kode]
,[Nama]
,[Stok])
VALUES
('C','CCC','100')

select * from [dbo].[Tabel_Barang]

CREATE TRIGGER [dbo].[TrInsertData]
ON [dbo].[Tabel_Jual]
AFTER Insert
AS
BEGIN

declare @Kode NVARCHAR(10)
declare @Qty INT

SET NOCOUNT ON;

-- APA YG DIINSERT OLEH USER
SELECT @Kode=Kode,@Qty=Qty
FROM inserted

UPDATE Tabel_Barang
set Stok = Stok - @Qty
where Kode = @Kode

END
GO

insert into Tabel_Jual
values

CREATE TRIGGER [dbo].[TrUpdateData]
ON [dbo].[Tabel_Jual]
AFTER update
AS
BEGIN

declare @Kode NVARCHAR(10)
declare @QtyLama INT
declare @QtyBaru INT

SET NOCOUNT ON;

-- APA YG DIUPDATE OLEH USER
SELECT @QtyLama=Qty
FROM deleted
SELECT @QtyBaru=Qty,@Kode=Kode
FROM inserted

UPDATE Tabel_Barang
set Stok = Stok + @QtyLama - @QtyBaru
where Kode = @Kode

END
GO

update Tabel_Jual
set Qty = 5
where NoFJ = 'FJ3'

SELECT * FROM Tabel_Jual

SELECT * FROM Tabel_Barang

CREATE TRIGGER [dbo].[TrDeleteData]
ON [dbo].[Tabel_Jual]
AFTER delete
AS
BEGIN

declare @Kode NVARCHAR(10)
declare @Qty INT

SET NOCOUNT ON;

SELECT @Kode=Kode,@Qty=Qty
FROM deleted

UPDATE Tabel_Barang
set Stok = Stok + @Qty
where Kode = @Kode

END
GO

delete from Tabel_Jual
where NoFJ='FJ2'
SELECT * FROM Tabel_Jual
SELECT * FROM Tabel_Barang

4.
USE [db_sinkronisasi_stok_barang]
GO
/****** Object: Trigger [dbo].[TrJual] Script Date: 01/13/2011 11:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TrJual]
ON [dbo].[Tabel_Jual]
AFTER Insert
AS
BEGIN

declare @Kode NVARCHAR(10)
declare @Qty INT
declare @Harga MONEY
declare @TglFJ DATETIME

SET NOCOUNT ON;

SELECT @Kode=Kode,@Qty=Qty,@Harga=Harga,@TglFJ=TglFJ
FROM inserted

if(datepart(mm,@TglFJ)=1)
begin
update Tabel_Barang
set Jual01=Jual01+(@Qty*@Harga),Qty01=Qty01+@Qty
where Kode = @Kode
end

if(datepart(mm,@TglFJ)=2)
begin
update Tabel_Barang
set Jual02=Jual02+(@Qty*@Harga),Qty02=Qty02+@Qty
where Kode = @Kode
end

if(datepart(mm,@TglFJ)=3)
begin
update Tabel_Barang
set Jual03=Jual03+(@Qty*@Harga),Qty03=Qty03+@Qty
where Kode = @Kode
end

if(datepart(mm,@TglFJ)=4)
begin
update Tabel_Barang
set Jual04=Jual04+(@Qty*@Harga),Qty04=Qty04+@Qty
where Kode = @Kode
end

if(datepart(mm,@TglFJ)=5)
begin
update Tabel_Barang
set Jual05=Jual05+(@Qty*@Harga),Qty05=Qty05+@Qty
where Kode = @Kode
end

if(datepart(mm,@TglFJ)=6)
begin
update Tabel_Barang
set Jual06=Jual06+(@Qty*@Harga),Qty06=Qty06+@Qty
where Kode = @Kode
end

if(datepart(mm,@TglFJ)=7)
begin
update Tabel_Barang
set Jual07=Jual07+(@Qty*@Harga),Qty07=Qty07+@Qty
where Kode = @Kode
end

if(datepart(mm,@TglFJ)=8)
begin
update Tabel_Barang
set Jual08=Jual08+(@Qty*@Harga),Qty08=Qty08+@Qty
where Kode = @Kode
end

if(datepart(mm,@TglFJ)=9)
begin
update Tabel_Barang
set Jual09=Jual09+(@Qty*@Harga),Qty09=Qty09+@Qty
where Kode = @Kode
end

if(datepart(mm,@TglFJ)=10)
begin
update Tabel_Barang
set Jual10=Jual10+(@Qty*@Harga),Qty10=Qty10+@Qty
where Kode = @Kode
end

if(datepart(mm,@TglFJ)=11)
begin
update Tabel_Barang
set Jual11=Jual11+(@Qty*@Harga),Qty11=Qty11+@Qty
where Kode = @Kode
end

if(datepart(mm,@TglFJ)=12)
begin
update Tabel_Barang
set Jual12=Jual12+(@Qty*@Harga),Qty12=Qty12+@Qty
where Kode = @Kode
end

END

insert into tabel_jual (NoFJ,TglFJ,Kode,Qty,Harga)
values ('FJ2',getdate(),'A',10,5000)
select * from Tabel_Jual
select * from Tabel_Barang

4.2
alter TRIGGER [dbo].[TrJualUpdate]
ON dbo.Tabel_Jual
AFTER update
AS
BEGIN

declare @NoFJ varchar(50)
declare @TglFJ datetime
declare @Kode varchar(50)
declare @Qty int
declare @QtyBaru int
declare @Harga money
declare @HargaBaru money

Select @QtyBaru = Qty, @HargaBaru =Harga
from inserted

select @TglFJ=TglFJ, @Kode=Kode, @Qty=Qty, @Harga=Harga
from deleted
if (datepart(mm,@TglFJ)=1)
begin
update Tabel_Barang
set Jual01=Jual01 +@QtyBaru*@HargaBaru - @Qty*@Harga, Qty01=Qty01+@QtyBaru - @Qty
where kode= @Kode
end
if (datepart(mm,@TglFJ)=2)
begin
update dbo.Tabel_Barang
set Jual02=Jual02 +@QtyBaru*@HargaBaru - @Qty*@Harga, Qty02=Qty02+@QtyBaru - @Qty
where kode= @Kode
end
if (datepart(mm,@TglFJ)=3)
begin
update dbo.Tabel_Barang
set Jual03=Jual03 +@QtyBaru*@HargaBaru - @Qty*@Harga, Qty03=Qty03+@QtyBaru - @Qty
where kode= @Kode
end
if (datepart(mm,@TglFJ)=4)
begin
update dbo.Tabel_Barang
set Jual04=Jual04 +@QtyBaru*@HargaBaru - @Qty*@Harga, Qty04=Qty04+@QtyBaru - @Qty
where kode= @Kode
end
if (datepart(mm,@TglFJ)=5)
begin
update dbo.Tabel_Barang
set Jual05=Jual05 +@QtyBaru*@HargaBaru - @Qty*@Harga, Qty05=Qty05+@QtyBaru - @Qty
where kode= @Kode
end
if (datepart(mm,@TglFJ)=6)
begin
update dbo.Tabel_Barang
set Jual06=Jual06 +@QtyBaru*@HargaBaru - @Qty*@Harga, Qty06=Qty06+@QtyBaru - @Qty
where kode= @Kode
end
if (datepart(mm,@TglFJ)=7)
begin
update dbo.Tabel_Barang
set Jual07=Jual07 +@QtyBaru*@HargaBaru - @Qty*@Harga, Qty07=Qty07+@QtyBaru - @Qty
where kode= @Kode
end
if (datepart(mm,@TglFJ)=8)
begin
update dbo.Tabel_Barang
set Jual08=Jual08 +@QtyBaru*@HargaBaru - @Qty*@Harga, Qty08=Qty08+@QtyBaru - @Qty
where kode= @Kode
end
if (datepart(mm,@TglFJ)=9)
begin
update dbo.Tabel_Barang
set Jual09=Jual09 +@QtyBaru*@HargaBaru - @Qty*@Harga, Qty09=Qty09+@QtyBaru - @Qty
where kode= @Kode
end
if (datepart(mm,@TglFJ)=10)
begin
update dbo.Tabel_Barang
set Jual10=Jual10 +@QtyBaru*@HargaBaru - @Qty*@Harga, Qty10=Qty10+@QtyBaru - @Qty
where kode= @Kode
end
if (datepart(mm,@TglFJ)=11)
begin
update dbo.Tabel_Barang
set Jual11=Jual11 +@QtyBaru*@HargaBaru - @Qty*@Harga, Qty11=Qty11+@QtyBaru - @Qty
where kode= @Kode
end
if (datepart(mm,@TglFJ)=12)
begin
update dbo.Tabel_Barang
set Jual12=Jual12 +@QtyBaru*@HargaBaru - @Qty*@Harga, Qty12=Qty12+@QtyBaru - @Qty
where kode= @Kode
end

END
GO

UPDATE Tabel_Jual
SET [Qty] =50
WHERE NoFJ='FJ2'and Kode = 'A'

select * from Tabel_Jual
select * from Tabel_Barang

1.buat db+tabel
2.buat fungsi
-cek barang(Barang)
-cek pelanggan(pelanggan)
-cek rekap jual(Penjualan barang)
-cek fj(nofj)
3.buat trigger insert/update
trinsertfj/update
*update pelanggan.totaljual
trinsertfjdet/update
*update fj.totalfaktur
*update rekap jual(jan s/d des)
*update barang.stok

tbrekap jual(penjualan barang,jan,...,des)
tb fj(nofj,tglfj,pelanggan,totalfaktur)
tb fjdet(nofj,noseq,barang,qty,harga)
tb pelanggan(pelanggan,totaljual)
tb barang(barang,stok,total jual)

CREATE FUNCTION fCekbarang(@pBarang)
RETURNS bit
AS
BEGIN
DECLARE @pHasil bit
IF EXISTS (SELECT Barang FROM Barang WHERE Barang = @pBarang)
SET @pHasil = 1
ELSE
SET @pHasil = 0

RETURN @pHasil
END

CREATE FUNCTION fCekpelanggan(@pPelanggan)
RETURNS bit
AS
BEGIN
DECLARE @pHasil bit
IF EXISTS (SELECT Pelanggan FROM Pelanggan WHERE Pelanggan = @pPelanggan)
SET @pHasil = 1
ELSE
SET @pHasil = 0

RETURN @pHasil
END

CREATE FUNCTION fCekpelanggan(@pBarang)
RETURNS bit
AS
BEGIN
DECLARE @pHasil bit
IF EXISTS (SELECT Barang FROM RekapJual WHERE Barang = @pBarang)
SET @pHasil = 1
ELSE
SET @pHasil = 0

RETURN @pHasil
END

CREATE FUNCTION fCekpelanggan(@pNoFJ)
RETURNS bit
AS
BEGIN
DECLARE @pHasil bit
IF EXISTS (SELECT NoFJ FROM FJ WHERE NoFJ = @pNoFJ)
SET @pHasil = 1
ELSE
SET @pHasil = 0

RETURN @pHasil
END
CREATE TRIGGER trUpdateFJ
ON FJ
AFTER UPDATE
AS
BEGIN
DECLARE @Pelanggan varchar(2), @TotalLama money, @TotalBaru money
SELECT @TotalLama = Total, @Pelanggan = Pelanggan
FROM DELETED
SELECT @TotalBaru = Total
FROM INSERTED

UPDATE Pelanggan
SET Total = Total - @TotalLama + @TotalBaru
WHERE Pelanggan = @Pelanggan
END

CREATE TRIGGER trInsertFJDet
ON FJDet
AFTER INSERT
AS
BEGIN
DECLARE @NoFJ varchar(2), @Barang varchar(5), @Qty int, @Harga money, @TglFJ datetime
SELECT @NoFJ = NoFJ, @Barang = Barang, @Qty = Qty, @Harga = Harga
FROM INSERTED
SELECT @TglFJ = TglFJ FROM FJ WHERE NoFJ = @NoFJ

UPDATE FJ
SET Total = Total + (@Qty*@Harga)
WHERE NoFJ = @NoFJ

UPDATE Barang
SET Stok = Stok - @Qty, Total = Total + (@Qty*@Harga)
WHERE Barang = @Barang

IF NOT EXISTS(SELECT Barang FROM RekapJual WHERE Barang=@Barang)
BEGIN
INSERT INTO RekapJual(Barang)
VALUES (@Barang)
END

IF (datepart(mm, @TglFJ) = 1)
BEGIN
UPDATE RekapJual
SET Jan = Jan + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 2)
BEGIN
UPDATE RekapJual
SET Feb = Feb + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 3)
BEGIN
UPDATE RekapJual
SET Mar = Mar + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 4)
BEGIN
UPDATE RekapJual
SET Apr = Apr + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 5)
BEGIN
UPDATE RekapJual
SET May = May + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 6)
BEGIN
UPDATE RekapJual
SET Jun = Jun + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 7)
BEGIN
UPDATE RekapJual
SET Jul = Jul + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 8)
BEGIN
UPDATE RekapJual
SET Aug = Aug + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 9)
BEGIN
UPDATE RekapJual
SET Sep = Sep + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 10)
BEGIN
UPDATE RekapJual
SET Oct = Oct + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 11)
BEGIN
UPDATE RekapJual
SET Nov = Nov + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 12)
BEGIN
UPDATE RekapJual
SET Dec = Dec + @Qty
WHERE Barang = @Barang
END
END

CREATE TRIGGER trUpdateFJ
ON FJ
AFTER UPDATE
AS
BEGIN
DECLARE @Pelanggan varchar(2), @TotalLama money, @TotalBaru money
SELECT @TotalLama = Total, @Pelanggan = Pelanggan
FROM DELETED
SELECT @TotalBaru = Total
FROM INSERTED

UPDATE Pelanggan
SET Total = Total - @TotalLama + @TotalBaru
WHERE Pelanggan = @Pelanggan
END

CREATE TRIGGER trInsertFJDet
ON FJDet
AFTER INSERT
AS
BEGIN
DECLARE @NoFJ varchar(2), @Barang varchar(5), @Qty int, @Harga money, @TglFJ datetime
SELECT @NoFJ = NoFJ, @Barang = Barang, @Qty = Qty, @Harga = Harga
FROM INSERTED
SELECT @TglFJ = TglFJ FROM FJ WHERE NoFJ = @NoFJ

UPDATE FJ
SET Total = Total + (@Qty*@Harga)
WHERE NoFJ = @NoFJ

UPDATE Barang
SET Stok = Stok - @Qty, Total = Total + (@Qty*@Harga)
WHERE Barang = @Barang

IF NOT EXISTS(SELECT Barang FROM RekapJual WHERE Barang=@Barang)
BEGIN
INSERT INTO RekapJual(Barang)
VALUES (@Barang)
END

IF (datepart(mm, @TglFJ) = 1)
BEGIN
UPDATE RekapJual
SET Jan = Jan + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 2)
BEGIN
UPDATE RekapJual
SET Feb = Feb + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 3)
BEGIN
UPDATE RekapJual
SET Mar = Mar + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 4)
BEGIN
UPDATE RekapJual
SET Apr = Apr + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 5)
BEGIN
UPDATE RekapJual
SET May = May + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 6)
BEGIN
UPDATE RekapJual
SET Jun = Jun + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 7)
BEGIN
UPDATE RekapJual
SET Jul = Jul + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 8)
BEGIN
UPDATE RekapJual
SET Aug = Aug + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 9)
BEGIN
UPDATE RekapJual
SET Sep = Sep + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 10)
BEGIN
UPDATE RekapJual
SET Oct = Oct + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 11)
BEGIN
UPDATE RekapJual
SET Nov = Nov + @Qty
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 12)
BEGIN
UPDATE RekapJual
SET Dec = Dec + @Qty
WHERE Barang = @Barang
END
END

CREATE TRIGGER trUpdateFJDet
ON FJDet
AFTER UPDATE
AS
BEGIN
DECLARE @NoFJ varchar(2), @Barang varchar(5), @QtyLama int, @QtyBaru int, @HargaLama money, @HargaBaru money, @TglFJ datetime
SELECT @QtyLama = Qty, @HargaLama = Harga
FROM DELETED
SELECT @NoFJ = NoFJ, @Barang = Barang, @QtyBaru = Qty, @HargaBaru = Harga
FROM INSERTED
SELECT @TglFJ = TglFJ FROM FJ WHERE NoFJ = @NoFJ

UPDATE FJ
SET Total = Total - (@QtyLama*@HargaLama) + (@QtyBaru*@HargaBaru)
WHERE NoFJ = @NoFJ

UPDATE Barang
SET Stok = Stok + @QtyLama - @QtyBaru, Total = Total - (@QtyLama*@HargaLama) + (@QtyBaru*@HargaBaru)
WHERE Barang = @Barang

IF NOT EXISTS(SELECT Barang FROM RekapJual WHERE Barang=@Barang)
BEGIN
INSERT INTO RekapJual(Barang)
VALUES (@Barang)
END

IF (datepart(mm, @TglFJ) = 1)
BEGIN
UPDATE RekapJual
SET Jan = Jan - @QtyLama + @QtyBaru
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 2)
BEGIN
UPDATE RekapJual
SET Feb = Feb - @QtyLama + @QtyBaru
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 3)
BEGIN
UPDATE RekapJual
SET Mar = Mar - @QtyLama + @QtyBaru
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 4)
BEGIN
UPDATE RekapJual
SET Apr = Apr - @QtyLama + @QtyBaru
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 5)
BEGIN
UPDATE RekapJual
SET May = May - @QtyLama + @QtyBaru
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 6)
BEGIN
UPDATE RekapJual
SET Jun = Jun - @QtyLama + @QtyBaru
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 7)
BEGIN
UPDATE RekapJual
SET Jul = Jul - @QtyLama + @QtyBaru
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 8)
BEGIN
UPDATE RekapJual
SET Aug = Aug - @QtyLama + @QtyBaru
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 9)
BEGIN
UPDATE RekapJual
SET Sep = Sep - @QtyLama + @QtyBaru
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 10)
BEGIN
UPDATE RekapJual
SET Oct = Oct - @QtyLama + @QtyBaru
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 11)
BEGIN
UPDATE RekapJual
SET Nov = Nov - @QtyLama + @QtyBaru
WHERE Barang = @Barang
END

IF (datepart(mm, @TglFJ) = 12)
BEGIN
UPDATE RekapJual
SET Dec = Dec - @QtyLama + @QtyBaru
WHERE Barang = @Barang
END
END

Kamis, 13 Januari 2011

Autonumber

Autonumber adalah field yang isinya akan secara otomatis terisi menurut urutan tertentu.

Field Autonumber dapat dibuat dengan 2 cara :
1. Menggunakan field Identity (Autonumber). Field ini harus bertipe Int atau Numeric, kemudian tentukan Seed (Nilai dimulai dari berapa), Increment (Pertambahan Nilai)
2. Menggunakan StoreProcedure / Trigger / Function. Field ini akan dibuat oleh program dengan algoritma sbb :
1. Cari Nomor Akhir (Nomor terakhir yang ada di tabel)
2. Buat Nomor Baru (Nomor Akhir ditambah 1, dengan asumsi incrementnya 1)
3. Insert data dengan pada Field Autonumber dengan Nomor Baru

BUAT STOREPROCEDURE UNTUK INSERT DATA BERIKUT :

Studi kasus 1:
Berikut pola penomoran reset per tahun, setiap gudang punya nomor urut sendiri (Gudang G1 dan G2)

TbPOKasus1
NomorPO TGLPO GUDANG KODESUPPLIER
PO-2008-G1-00001 16/09/2008 G1 S-00001
PO-2008-G1-00002 16/09/2008 G1 S-00001
PO-2008-G2-00001 17/09/2008 G2 S-00002
PO-2008-G2-00002 17/10/2008 G2 S-00002
PO-2009-G1-00001 17/10/2009 G1 S-00002
PO-2009-G2-00001 17/10/2009 G2 S-00002

Studi kasus 2 :
Berikut pola penomoran reset per Awalan Nama Barang, Untuk Awalan Barang A nomornya adalah A001 sd A999, Awalan Barang B adalah B001 sd/ B999, dst untuk awalan lainnya.

TbPOKasus1
Nama Barang Kode Barang Stok
Meja Biro M001 5
Meja Makan M002 3
Kursi Tamu K001 2
Kursi Goyang K002 4
Karpet K003 3
Mantel M003 4
Bath Tube Toto 234 B001 2

create procedure InsertTbPOKasus1
@tglpo datetime,
@gudang varchar(10),
@kodesupplier varchar(10)
as
begin
declare @x int
declare @y varchar(20)
select @x = max(right(nomorpo,5))
from TbPOKasus1
where substring(nomorpo,4,4) = convert(varchar(4),datepart(yy,@tglpo))
and substring(nomorpo,9,2) = @gudang
if @x is null
set @x=1
else
set @x = @x+1
set @y='PO-'+convert(varchar(4),datepart(yy,@tglpo))+'-'+@gudang+'-'+right('00000'+convert(varchar,@x),5)
insert into TbPOKasus1(nomorpo,tglpo,gudang,kodesupplier)
values (@y,@tglpo,@gudang,@kodesupplier)
end

exec InsertTbPOKasus1 '2008/9/16', 'G1', 'S-00001'
exec InsertTbPOKasus1 '2008/9/16', 'G1', 'S-00001'
exec InsertTbPOKasus1 '2008/9/17', 'G2', 'S-00002'
exec InsertTbPOKasus1 '2008/10/17', 'G2', 'S-00002'
exec InsertTbPOKasus1 '2009/10/17', 'G1', 'S-00002'
exec InsertTbPOKasus1 '2009/10/17', 'G2', 'S-00002'

select * from TbPOKasus1

alter procedure InsertTbPOKasus2
@namabarang varchar(20),
@stok int
as
begin
declare @x int
declare @y varchar(10)
select @x = max(right(kodebarang,3))
from TbPOKasus2
where left(kodebarang,1)=left(@namabarang,1)
if @x is null
set @x=1
else
set @x=@x+1
set @y = left(@namabarang,1)+right('000'+convert(varchar,@x),3)
insert into TbPOKasus2(kodebarang,namabarang,stok)
values (@y,@namabarang,@stok)
end

exec InsertTbPOKasus2 'Meja Biro',5
exec InsertTbPOKasus2 'Meja Makan',3
exec InsertTbPOKasus2 'Kusi Tamu',2
exec InsertTbPOKasus2 'Kursi Goyang',4
exec InsertTbPOKasus2 'Karpet',3
exec InsertTbPOKasus2 'Mantel',4
exec InsertTbPOKasus2 'Bath Tube',2

select * from TbPOKasus

LAT no_ktp

SELECT TahunTglBuat + '/' + BulanTglBuat + '/' + [L/P] + '/' + CONVERT(varchar(2), DATEPART(dd, TglLahir)) + CONVERT(varchar(2), DATEPART(mm, TglLahir))
+ RIGHT(CONVERT(varchar(4), DATEPART(yy, TglLahir)), 2) + '/' + CONVERT(varchar(5), RIGHT(No, 5)) AS NoKTP
FROM dbo.Table_ktp

nodaftar nama l/p tgldaftar jurusan
2011/L-0001/IT A L 2011-01-11 IT
2011/P-0002/SS B P 2011-01-11 SS

alter PROCEDURE SP_TambahMhs
@pNama nvarchar(50),@pKelamin char(1),@pTglDaftar datetime,@pJurusan char(2)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NODaftar varchar(50)

select @NoAkhir = convert(int, Max(substring(NoDaftar,8,4)))
from Table_Daftar

if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

set @NODaftar = convert(varchar(4), datepart(yy,@pTglDaftar))+ '/' + @pKelamin + '-' + RIGHT('000'+Convert(varchar(4), @NoBaru), 4) + '/' + @pJurusan

Insert into Table_Daftar (NoDaftar,Nama,Kelamin,TglDaftar,Jurusan)
Values (@NODaftar, @pNama,@pKelamin,@pTglDaftar,@pJurusan)
END

EXEC dbo.SP_TambahMhs 'william','L','01/01/2011','IT'
select * from Table_Daftar

NoPegawai ThnMasuk Nama
00001-2008-A 2008-1-1 Amir
00002-2008-B 2008-1-2 Badu
00001-2008-F 2009-3-3 Filbert

alter PROCEDURE SP_TambahPegawai
@pThnMasuk datetime,@pNama nvarchar(50)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NOPegawai varchar(50)

select @NoAkhir = convert(int, max(left(NoPegawai,5)))
from Table_Pegawai

if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

set @NOPegawai = RIGht('000'+Convert(varchar(5), @NoBaru), 5) +'-' + convert(varchar(4), datepart(yy,@pThnMasuk))+ '-' + left(@pNama,1)

Insert into Table_Pegawai (NoPegawai,ThnMasuk,Nama)
Values (@NOPegawai,@pThnMasuk ,@pNama)
END

EXEC dbo.SP_TambahPegawai '01/01/2011','william'
select * from Table_Pegawai

buat function
1.NoPOTerakhir (@Tahun) -> varchar
ex:select nopoterakhir(2008)
2.nopobaru(@tahun) -> varchar
ex:select nopobaru(2010)

1.alter FUNCTION NoPOTerakhir
(
@Tahun datetime
)
returns varchar (20)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(13)

select @NoAkhir = convert(int, Max(right(@NomorPO,5)))
from tbPO
where substring(@NomorPO,4,4) = convert(char(4), Datepart(yy, @Tahun))

if @NoAkhir is null
Set @NoAkhir=0

Set @NomorPO = 'PO-' + convert(char(4), Datepart(yy, @Tahun )) + '-' +
RIGHT('00000'+Convert(varchar, @NoAkhir), 5)

return @NomorPO

END

select dbo.NoPOTerakhir ('01/01/2008')

2.alter FUNCTION NoPOBaru
(
@Tahun datetime
)
returns varchar (20)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(13)

select @NoAkhir = convert(int, Max(right(@NomorPO,5)))
from tbPO
where substring(@NomorPO,4,4) = convert(char(4), Datepart(yy, @Tahun))

if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @NomorPO = 'PO-' + convert(char(4), Datepart(yy, @Tahun )) + '-' +
RIGHT('00000'+Convert(varchar, @NoBaru), 5)

return @NomorPO

END

select dbo.NoPOBaru ('01/01/2008')

CREATE PROCEDURE SP_TambahPelanggan
@NamaPelanggan varchar(50)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @KodePelanggan varchar(10)

--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(KodePelanggan,5)))
from Pelanggan

--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @KodePelanggan = ‘C-‘ + RIGHT(‘00000’+Convert(varchar, @NoBaru), 5)

--3.Insert Data baru
Insert Pelanggan (KodePelanggan, NamaPelanggan) Values (@KodePelanggan, @NamaPelanggan)
END

Execute SP_TambahPelanggan sbb :
--menambahkan data dengan nama Teddy
EXEC dbo.SP_TambahPelanggan 'Teddy'
select * from Pelanggan order by KodePelanggan DESC

Store Procedure Insert dengan Nomor Urut Reset Tahunan

Untuk Pembuatan Store Procedure Insert pada table TbPO sbb :
CREATE PROCEDURE SP_TambahTBPO
@TglPO datetime, @KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(13)

--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(NomorPO,5)))
from tbPO
where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO)))

--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @NomorPO = ‘PO-‘ + convert(char(4), Datepart(yyyy, @TglPO )) + ‘-‘ +
RIGHT(‘00000’+Convert(varchar, @NoBaru), 5)

--3.Insert Data baru
Insert tbPO (NomorPO, TglPO, KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)
END

Store Procedure Insert dengan Nomor Urut Reset Bulanan

Untuk Pembuatan Store Procedure Insert pada table TbPOBulanan sbb :
CREATE PROCEDURE SP_TambahTBPOBulanan
@TglPO datetime, @KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(15)

--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(NomorPO,5)))
from tbPOBulanan
where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO)) AND
convert(int, substring(NomorPO,8,2)) = Datepart(mm, @TglPO)

--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @NomorPO = 'PO-' + convert(char(4), Datepart(yyyy, @TglPO )) +
right('0'+convert(varchar, datepart(mm,@TglPO)),2) + '-' +
RIGHT('00000'+Convert(varchar, @NoBaru), 5)

--3.Insert Data baru
Insert tbPOBulanan (NomorPO, TglPO, KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)
END

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

Store Procedure

Store Procedure adalah kumpulan Pre-defined Transact-SQL yang digunakan untuk melakukan tugas/task khusus. Dalam Store Procedure dapat berisi beberapa statement dan setiap statement di kelompokan untuk satu object database.

Store Procedure terdiri dari:
1. Statemen CREATE PROC {PROCEDURE}
2. Nama Procedure;
3. Parameter list
4. SQL statement.

Banyak option lainnya dalam mendefinisikan store procedure, dalam tulisan ini hanya beberapa saja yang disebutkan, hanya sebagai gambaran awal dalam membuat store procedure.

Penggunaan store procedure dalam sebuah pemrograman database memiliki beberapa keuntungan atau kelebihan sebagai berikut:
1. Performance
2. Security
3. Modifications/Maintenance
4. Minimal processing at the client.
5. Network traffic

CREATE PROCEDURE SP_LihatBarangA
AS
BEGIN
select * FROM dbo.Barang
END

CREATE PROCEDURE SP_LihatBarangAwalaan
<@awalan varchar(20)
AS
BEGIN
select * FROM dbo.Barang
where NamaBarang like @awalan + '%'
END

CREATE PROCEDURE SP_LihatBarangKataTertentu
@kata_tertentu varchar(50)
AS
BEGIN
select * FROM dbo.Barang
where NamaBarang like @kata_tertentu + '%'
END

exec SP_LihatBarangKataTertentu 'as'

soal1

@x=jumlah barang dgn harga beli < 100000
@y=jumlah barang dgn harga beli 100000-500000
@z=jumlah barang dgn harga beli > 500000

CREATE PROCEDURE SP_Penjualan
@tahun int
AS
BEGIN

declare @jan_sd_mar decimal(18,2)
declare @apr_sd_jun decimal(18,2)
declare @jul_sd_sep decimal(18,2)
declare @okt_sd_des decimal(18,2)

select @jan_sd_mar = SUM (TotalFaktur) FROM dbo.FJ
where TglFJ between '01/01/' + convert(varchar(4),@tahun) and '03/31/' + convert(varchar(4),@tahun)+' 23:59:59'
select @apr_sd_jun = SUM (TotalFaktur) FROM dbo.FJ
where TglFJ between '04/01/' + convert(varchar(4),@tahun) and '06/30/' + convert(varchar(4),@tahun) +' 23:59:59'
select @jul_sd_sep = SUM (TotalFaktur) FROM dbo.FJ
where TglFJ between '07/01/' + convert(varchar(4),@tahun) and '09/30/' + convert(varchar(4),@tahun)+' 23:59:59'
select @okt_sd_des = SUM(TotalFaktur) FROM dbo.FJ
where TglFJ between '10/01/' + convert(varchar(4),@tahun) and '12/31/' + convert(varchar(4),@tahun)+' 23:59:59'

select @jan_sd_mar,@apr_sd_jun,@jul_sd_sep,@okt_sd_des
END

exec SP_Penjualan 2005

SPJumlahJuni2005 dalam satu storeprocedure(soal 3)
alter PROCEDURE SP_PenjualanJuni
AS
BEGIN
declare @a int
declare @b int
declare @x int
declare @y money

select @a = COUNT(DISTINCT KodePelanggan) FROM dbo.FJ
where TglFJ between '06/01/2005' and '06/30/2005 23:59:59'

select DISTINCT @b = COUNT(DISTINCT b.NamaBarang)FROM dbo.FJDet fd join FJ f on (fd.NoFJ = f.NoFJ)join Barang b on(fd.KodeBarang = b.KodeBarang)
where TglFJ between '06/01/2005' and '06/30/2005 23:59:59'

select @x = SUM(fd.Qty)FROM FJDet fd join FJ f on (fd.NoFJ = f.NoFJ)
where TglFJ between '06/01/2005' and '06/30/2005 23:59:59'

select @y = SUM(TotalFaktur)FROM FJ
where TglFJ between '06/01/2005' and '06/30/2005 23:59:59'

select @a ,@b ,@x ,@y
END

exec SP_PenjualanJuni

Studi kasus :
Berikut pola penomoran reset per tahun, setiap gudang punya nomor urut sendiri (Gudang G1 dan G2)

TbPOKasus1
NomorPO TGLPO GUDANG KODESUPPLIER
PO-2008-G1-00001 16/09/2008 G1 S-00001
PO-2008-G1-00002 16/09/2008 G1 S-00001
PO-2008-G2-00001 17/09/2008 G2 S-00002
PO-2008-G2-00002 17/10/2008 G2 S-00002
PO-2009-G1-00001 17/10/2009 G1 S-00002
PO-2009-G2-00001 17/10/2009 G2 S-00002

alter PROCEDURE SP_POKasus1
@TglPO datetime,@Gudang char(2) ,@KodeSupplier nvarchar(50)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(50)

select @NoAkhir = convert(int, Max(right(NomorPO,5)))
from dbo.TbPOKasus1
where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO)) AND
substring(NomorPO,9,2) = @Gudang

if @NOAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @NomorPO = 'PO-' + convert(char(4), Datepart(yy, @TglPO )) + '-' + @Gudang + '-' +
RIGHT('00000'+Convert(varchar(20), @NoBaru), 5)

Insert into tbPOKasus1 (NomorPO, TglPO, Gudang,KodeSupplier)
Values (@NomorPO, @TglPO,@Gudang ,@KodeSupplier)
END

exec dbo.SP_POKasus1 '09/16/2009','G1','S-00001'
--delete from dbo.tbPOKasus1
select * from dbo.TbPOKasus1

create PROCEDURE SP_POKasus2
@NamaBarang nvarchar(50) ,@Stok int
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @KodeBarang varchar(50)

select @NoAkhir = convert(int,max(right(KodeBarang,3)))
from dbo.TbPOKasus2
where left(@NamaBarang,1) = left(KodeBarang,1)

if @NOAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @KodeBarang = left(@NamaBarang,1)+ RIGHT('000'+Convert(varchar(20), @NoBaru), 4)

Insert into tbPOKasus2 (NamaBarang, KodeBarang,Stok)
Values (@NamaBarang, @KodeBarang ,@Stok)
END

exec dbo.SP_POKasus2 'Meja Biro','5'

select * from dbo.TbPOKasus2

Buat SPInsertPelanggan, Update dan Delete
CREATE PROCEDURE SP_InsertFJ
@NoFJ nvarchar(12),
@KodePelanggan nvarchar(12),
@TotalFaktur money
AS
BEGIN

if exists (SELECT KodePelanggan
from dbo.Pelanggan where KodePelanggan = @KodePelanggan)
begin

if exists (SELECT NoFJ
from dbo.FJ where NoFJ = @NoFJ)

begin
select 'data sudah ditemukan, proses insert gagal'
end

else

begin
INSERT INTO [SIPP].[dbo].[FJ]
(NoFJ
,KodePelanggan
,TotalFaktur)

VALUES
(@NoFJ
,@KodePelanggan
,@TotalFaktur)

UPDATE [SIPP].[dbo].[Pelanggan]
SET [KodePelanggan] = @kodepelanggan
,[Piutang] = piutang+@TotalFaktur
WHERE KodePelanggan = @KodePelanggan

select 'data berhasil terisi'
end
end

else

begin

select 'kode pelanggan tidak terdaftar'

end

END
GO

EXEC SP_InsertFJ

use SIPP
CREATE PROCEDURE SP_DeletePelanggan
@pKodePelanggan nvarchar(10)
AS
BEGIN
IF EXISTS (SELECT KodePelanggan FROM Pelanggan
where KodePelanggan = @pKodePelanggan)
BEGIN
DELETE from Pelanggan

WHERE KodePelanggan = @pKodePelanggan
SELECT 'Delete berhasil'
END

ELSE

BEGIN
SELECT 'Data pelanggan TIDAK ada,delete gagal!'
END
END
use SIPP
exec SP_DeletePelanggan 'C-00001'

alter PROCEDURE SP_UpdatePelanggan
@pKodePelanggan nvarchar(10),
@pNamaPelanggan nvarchar(30),
@pPiutang money

AS
BEGIN
IF EXISTS (SELECT KodePelanggan FROM Pelanggan
where KodePelanggan = @pKodePelanggan)
BEGIN
UPDATE Pelanggan

SET

NamaPelanggan = @pNamaPelanggan,
Piutang = @pPiutang

WHERE

KodePelanggan = @pKodePelanggan
SELECT 'Update berhasil'
END

ELSE

BEGIN
SELECT 'Data pelanggan TIDAK ada,update gagal!'
END
END

exec SP_UpdatePelanggan 'C-00001','Kartika Motor',1