Bahan 12 - Transaction

1.    Apa itu transaction?

Sebuah transaksi adalah sekelompok perintah yang harus dijalankan, hasilnya berhasil atau gagal untuk semua perintah tersebut

2.    Apa pentingnya transaction?

Lihat contoh berikut : missal diperlukan perubahan stok setiap kali ada penjualan 1 item barang

Update Inventory

Set qty = qty – 1

Where item_id=2012

Insert  Sales_detail (item_id, inv_id, datesold, qty)

Values(2012, 308, current_timestamp, 1)

BEGIN TRAN

Update Inventory

Set qty = qty – 1

Where item_id=2012

Insert  Sales_detail (item_id, inv_id, datesold, qty)

Values(2012, 308, current_timestamp, 1)

COMMIT TRAN

LATIHAN 1:

  --LIHAT DATA SEBELUM DIUPDATE

  select * from FB

  BEGIN TRAN

  --LAKUKAN UPDATE

  update FB

  set tempo=200 where KODEPELANGGAN='S-00002'

 

  update FB

  set tempo=300 where KODEPELANGGAN='S-00003'

 

  --LIHAT DATA SESUDAH DIUPDATE

  select * from FB

  --LAKUKAN ROLLBACK

  ROLLBACK TRAN

  --LIHAT DATA SESUDAH ROLLBACK

  select * from FB

LATIHAN 2 :

  --LIHAT DATA SEBELUM DIUPDATE

  select * from FB

  BEGIN TRAN

  --LAKUKAN UPDATE

  update FB

  set tempo=200 where KODEPELANGGAN='S-00002'

 

  update FB

  set tempo=300 where KODEPELANGGAN='S-00003'

 

  --LIHAT DATA SESUDAH DIUPDATE

  select * from FB

  --LAKUKAN COMMIT

  COMMIT

  --LIHAT DATA SESUDAH COMMIT

  select * from FB

LATIHAN 3 :

select * from fb

BEGIN TRAN

      insert into fb(NoFJ,KodePelanggan,tglFJ,totalfaktur)

      VALUES ('FB-3','S-00002','2007-11-06 12:12:12','10000')

     

      update Supplier

      set Piutang = Piutang+10000 where KODEPELANGGAN ='S-00002'

      select * from fb

      select * from supplier

COMMIT

CREATE TABLE tbCustomer

(

IdCust varchar(10),

Customer varchar(30),

primary key (IdCust)

)

--lihat data sebelum di update

SELECT * FROM tbCustomer --data msh kosong

BEGIN TRAN

--lakukan insert

INSERT tbCustomer VALUES ('001','Nugraha')

INSERT tbCustomer VALUES ('002', 'Agung')

--lihat data sesudah diinsert

SELECT * FROM tbCustomer --data sudah terisi 2 data

ROLLBACK TRAN

--lihat data sesudah dirollback

SELECT * FROM tbCustomer -- data kmbl kosong

--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 --data sudah terisi 2 data

COMMIT TRAN --data tersimpan dan tak bs di rollback

--lihat data sesudah dirollback

SELECT * FROM tbCustomer --menampilkan data terkahir yg diinsert

--lihat data sebelum diupdate

SELECT * FROM tbCustomer

BEGIN TRAN

--lakukan insert

INSERT tbCustomer VALUES ('001','Nugraha') --gagal

INSERT tbCustomer VALUES ('002', 'Agung') --gagal

INSERT tbCustomer VALUES ('003', 'Michael') --data 1 dan 2 tidak dapat terinsert krn sebelumnya sudah diinsert, hny data 3 yg bs diinsert

IF @@ERROR != 0

BEGIN 

PRINT 'ERROR, Insert data gagal'

ROLLBACK TRAN --kalau eror, data akan di rollback

END

ELSE

BEGIN

PRINT 'Berhasil'

COMMIT TRAN --kalau tdk tjd eror maka data akan di commit

END

GO

--lihat data sesudah di rollback

SELECT * FROM tbCustomer

CREATE TABLE tbCustomer1 (

IdCust varchar(10),

Customer varchar(30),

Piutang money default 0,

primary key (IdCust)

) --membuat table baru

CREATE TABLE tbJual(

NoJual varchar(10),

IdCust varchar(10),

Jumlah money,

primary key (NoJual)

) --membuat table baru

CREATE PROCEDURE spInsert --membuat proc insert

@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 --menegecek jika piutang null makan di set nilainy mjd 0

UPDATE  dbo.tbCustomer1 SET Piutang=@piutang + @Jumlah WHERE IdCust=@IdCust --mengupdate isi dari nilai piutang

INSERT dbo.tbJual (NoJual, IdCust, Jumlah) VALUES (@NoJual, @IdCust, @Jumlah) --menginsert data ke dalam table jual

IF @@ERROR != 0 

ROLLBACK TRAN --jika eror maka data akan di rollback

ELSE

COMMIT TRAN --jika tdk eror maka data akan dicommit

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

create table tbNilai (

nim varchar(7),

kodemk varchar(7),

nilai char(1),

primary key (nim,kodemk)

) --membuat table baru

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 --jika nilai tdk sesuai persyaratan maka data akan dirollback

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

Bahan 11 - Backup and Restore

BACKUP :

 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)

Bahan 9 - Scrollablle Cursor

PENGGUNAAN SCROLLABLE CURSOR

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

create procedure Lat_UAS2

as

begin

declare x cursor

for select no_rekening,jenis_tabungan,nama,alamat from tbnasabah

open x

declare @no_rekening varchar(15),@jenis_tabungan varchar(20),@nama varchar(50),@alamat varchar(100)

fetch next from x into @no_rekening,@jenis_tabungan,@nama,@alamat

WHILE @@FETCH_STATUS=0

BEGIN

print '----------------------------------------------------------------------------------------------------------------------'

print 'No Rekening    : '+@no_rekening

print 'Jenis Tabungan : '+@jenis_tabungan

print 'Nama       : '+@nama

print 'Alamat         : '+@alamat

print '----------------------------------------------------------------------------------------------------------------------'

print 'No Tanggal               NIK Keterangan                Debet  Kredit     Saldo'

print '----------------------------------------------------------------------------------------------------------------------'

begin

declare y cursor

for select tanggal,nik,keterangan,jenis_trans,jumlah,saldo from tbtransaksi where no_rekening=@no_rekening

open y

declare @tanggal smalldatetime,@nik varchar(10),@keterangan varchar(30),@jenis_trans varchar(20),@jumlah money,@saldo money

declare @no int

declare @debet money

declare @kredit money

set @debet=0

set @kredit=0

set @no=1

fetch next from y into @tanggal,@nik,@keterangan,@jenis_trans,@jumlah,@saldo

WHILE @@FETCH_STATUS=0

BEGIN

set @debet=0

set @kredit=0

if @jenis_trans='Debet'

set @debet=@jumlah

else

set @kredit=@jumlah

print convert(char(5),@no)+convert(char(30),left(convert(varchar(30),@tanggal),11))+convert(char(10),@nik)+convert(char(20),@keterangan)+convert(char(15),@debet)+convert(char(15),@kredit)+convert(char(15),@saldo)

set @no=@no+1

fetch next from y into @tanggal,@nik,@keterangan,@jenis_trans,@jumlah,@saldo

END

close y

deallocate y

end

print '----------------------------------------------------------------------------------------------------------------------'

fetch next from x into @no_rekening,@jenis_tabungan,@nama,@alamat

END

close x

deallocate x

end

Bahan 8 - cursor

Apa itu 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, yatiu lewat cursor

Algoritma CURSOR ?

1.    Mendeklarasikan Cursor

2.    Membuka Cursor

3.    Memproses Cursor

4.    Menutup Cursor

5.    Mendealokasikan Cursor

Penggunaan CURSOR yang sederhana

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

LATIHAN :

1.    Buat SP_Cursor2, hasilnya seperti berikut :

DAFTAR HADIR MAHASISWA

Tanggal : 28-04-2008

No	NIK	Nama	TTD
1 2 3 4 5	1 2 3 4 5	A B C D E
		DOSEN :	PARAF :

alter proc 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 = 0

      print 'Data Hadir Mahasiswa'

      print 'Tanggal:' + convert(varchar, getdate())

      print '========================================='

      print 'No   |NIK        |Nama             |TTD   '

      print '========================================='

  FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA

  WHILE @@FETCH_STATUS=0

  BEGIN

      set @No = @No + 1

     PRINT convert(varchar, @No)+ ' |'+CONVERT(CHAR(1),@NIK) +'               |'+ convert(varchar(5), @NAMA)+'              |'+'        |'

     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

2.    Buat SP_Cursor3, hasilnya seperti berikut :

DAFTAR NILAI  MAHASISWA

MATA KULIAH :

DOSEN :

No	NIK	Nama	1	2	3	4	5	RATA-2
1 2 3 4 5	1 2 3 4 5	A B C D E

3.    Ubah struktur tabelnya, kemudian isikan datanya sbb :

No	NIK	Nama	GAJI	DEPARTEMEN
1 2 3 4 5	1 2 3 4 5	A B C D E	1000 1000 2000 2000 3000	IT IT HRD HRD PPIC

alter proc SP_cursor4

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 @Gaji money

  declare @dept nvarchar(15)

 

      set @No = 0

      print 'Data Nilai Mahasiswa'

      print '=================================================='

      print 'No   |NIK  |Nama |Gaji       |Departement|'

      print '=================================================='

  FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA, @Gaji, @dept

  WHILE @@FETCH_STATUS=0

  BEGIN

      set @No = @No + 1

     PRINT convert(varchar, @No)+'  |'+CONVERT(CHAR(1),@NIK) + '        |'+convert(varchar(5), @NAMA)+'     |'+convert(char(7), @Gaji)+'  |'+convert(char(10), @dept)+'    |'

     FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA, @Gaji, @dept

  END

      print '=================================================='

--4  Menutup Cursor

  CLOSE CsrMsiswa

--5  Mendealokasikan Cursor

  DEALLOCATE CsrMsiswa

end

go

exec dbo.SP_cursor4

Buat SP Cursor4, hasilnya seperti berikut :

DEPARTEMEN	JUMLAH
IT HRD PPIC	2 ORANG 2 ORANG 1 ORANG
TOTAL	5 ORANG

alter proc SP_cursor5

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 @jml int

  DECLARE @count int

  declare @dept nvarchar(15)

 

      set @jml = 0

      print 'Hasil:'

      print '========================'

      print 'Dept |Jumlah Orang|'

      print '========================'

  FETCH NEXT FROM CsrMsiswa into @dept, @Count

  WHILE @@FETCH_STATUS=0

  BEGIN

     PRINT convert(char(8),@Dept)+'|'+convert(varchar(5),@count)+' orang'

     set @jml = @jml + @count

     FETCH NEXT FROM CsrMsiswa into @dept, @count

  END

  print'========================='

  print 'Jumlah:  ' +convert(char(2), @jml) + 'orang'

  print'========================='

--4  Menutup Cursor

  CLOSE CsrMsiswa

--5  Mendealokasikan Cursor

  DEALLOCATE CsrMsiswa

end

go

exec dbo.SP_cursor5

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