Tuesday, November 8, 2011
Thursday, January 20, 2011
TRANSACTION AND ROLLBACK
--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
HASILNYA
--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
HASILNYA :
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
HASILNYA
SCROLL BAR 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
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
MENJALANKAN SP ADA CURSOR
TRIGGER
CREATE trigger [dbo].[trgINSERT_Nilai] on [dbo].[TBNILAI]
for INSERT
as
declare @v1 as char(3)
declare @v2 as char(3)
select @v1=KdMK,@v2=NIM from inserted
update tbNilai
set NAkhir=CONVERT(INT, 0.4*UTS + 0.6*UAS)
where KdMK=@v1 and NIM=@v2return
pengujian
coding BDL
STORE PROCEDURE UPDATE
create procedure SPUpdLowongan
@idperusahaan varchar(10),
@idlowongan varchar(10),
@posisi varchar(50),
@jumlah int,
@jeniskelamin varchar(15),
@rentangumur varchar(20),
@persyaratan varchar(50)
as
begin
if exists(select idlowongan from
TLowongan where IDLowongan=@idlowongan)
begin
if
exists(select IDPerusahaan from TInstansi where IDPerusahaan=@idperusahaan)
begin
update
Tlowongan set
posisi=@posisi,jumlah=@jumlah,jeniskelamin=@jeniskelamin,rentangumur=@rentangumur,persyaratan=@persyaratan
where
@idlowongan = IDLowongan
end
else
begin
print'Data perusahaan tidak ada'
end
end
else
begin
print 'Data lowongan tidak ditemukan'
end
END
select * from tlowongan
exec SPUpdLowongan
'I-0001','LW-0001','satpam','2','L','25-40','Ganteng'
select * from tlowongan
2. CREATE procedure SPUpdPelamar
@idpelamar varchar(10),
@NamaLengkap varchar(50),
@Alamat varchar(100),
@NoTlp int,
@TempatLahir varchar(50),
@TanggalLahir datetime,
@Agama varchar(15),
@JenisKelamin varchar(20),
@PengalamanKerja varchar(200)
as
begin
if exists (select idpelamar from
TPelamar where IDPelamar=@idpelamar)
begin
update TPelamar set
NamaLengkap=@namalengkap,Alamat=@alamat,NoTlp=@notlp,TempatLahir=@tempatlahir,TanggalLahir=@tanggallahir,Agama=@agama,JenisKelamin=@JenisKelamin,PengalamanKerja=@PengalamanKerja
where idpelamar=@idpelamar
end
else
begin
print 'Id pelamar tidak ditemukan'
end
END
select * from tpelamar
exec SPUpdPelamar'P-0002','Ultear','Jlkircon','575687568','bandung','06/05/1989','atheis','P','Nihil'
select * from tpelamar
STORE PROCEDURE INSERT
create procedure SPInsLowongan
@idperusahaan varchar(10),
@posisi varchar(50),
@jumlah int,
@jeniskelamin varchar(15),
@rentangumur varchar(20),
@persyaratan varchar(50)
as
begin
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @Nomor varchar(20)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int,Max(right(IDlowongan,4))) from Tlowongan
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @Nomor = 'LW-' +
RIGHT('00000'+Convert(varchar, @NoBaru), 4)
--3.Insert Data baru
if exists(select idperusahaan from tinstansi where idperusahaan=@idperusahaan)
begin
Insert Tlowongan (idperusahaan,idlowongan,posisi,jumlah,jeniskelamin,rentangumur,persyaratan)
Values(@idperusahaan,@nomor,@posisi,@jumlah,@jeniskelamin,@rentangumur,@persyaratan)
end
else
begin
print 'Data perusahaan tidak ditemukan'
end
END
exec SPInsLowongan'I-0001','Manager','1','L','25-40','Ganteng'
exec SPInsLowongan'I-0002','Admin','1','L','25-40','Ganteng'
exec SPInsLowongan'I-0003','Supervisor','1','L','25-40','Ganteng'
exec SPInsLowongan'I-0003','Personalia','1','P','25-40','Cantik'
exec SPInsLowongan'I-0004','CS','2','P','25-27','Cantik, Penampilan Menarik'
exec SPInsLowongan'I-0005','Marketing','2','L','25-40','Ganteng'
exec SPInsLowongan'I-0006','Sales','4','P','25-30','Cantik,Supel'
select * from tlowongan
CREATE procedure SPInsInstansi
@NamaPerusahaan varchar(50),
@Alamat varchar(200),
@Notlp int
as
begin
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @Nomor varchar(20)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int,
Max(right(IDPerusahaan,4))) From Tinstansi
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @Nomor = 'I-' +
RIGHT('00000'+Convert(varchar, @NoBaru), 4)
--3.Insert Data baru
Insert Tinstansi(idperusahaan,namaperusahaan,alamat,notelp)
Values(@nomor,@namaperusahaan,@alamat,@notlp)
END
exec dbo.SPInsInstansi 'Telkom','JlJapati','022765666'
exec dbo.SPInsInstansi'Telkomsel','JlBKR','022765612'
exec dbo.SPInsInstansi 'BankMandiri','Jl Asia Afrika','022765655'
exec dbo.SPInsInstansi 'Bank Jabar','JlJamika','022765623'
exec dbo.SPInsInstansi'Bank Mega','JlGatsu','022765645'
exec dbo.SPInsInstansi 'Hypermart','JlMerdeka','022765689'
select * from tinstansi
STORE PROCEDURE DELETE
1.alter proc SPDelete_lowongan
@idlowongan varchar(10)
as
begin
delete from tlowongan where
idlowongan=@idlowongan
end
select * from TLowongan
exec SPDelete_lowongan 'LW-0007'
select * from TLowongan
2.alter proc SPDelete_saluran
@idpenyaluran varchar(10)
as
begin
delete from tsaluran where idpenyaluran=@idpenyaluran
end
select * from tsaluran
exec SPDelete_saluran 'PY-0002'
select * from tsaluran
FUNGSI 1
alter function CariUmur
( @nama varchar(50)
)
returns int
as
begin
declare @tgl datetime
declare @umur int
select @tgl=TanggalLahir from TPelamar
where IDPelamar=@nama set @umur=datediff(dd,@tgl,getdate())/365
return @umur
end select dbo.CariUmur (IDPelamar) as Umur from TPelamar
FUNGSI 2
alter function PendidikanTerakhir
(@IDPelamar varchar(10)
)
returns varchar(50)
as
begin
declare @IDPendidikan varchar(10)
declare @NamaPendidikan varchar(50)
declare @ket varchar(50)
select top (1) @ket = NamaPendidikan from TPendidikan
where @IDPelamar = IDPelamar order by IDPendidikan desc
return @ket
end
select distinct dbo.PendidikanTerakhir(IDPelamar) from TPendidikan
FUNGSI CARI
alter PROCEDURE SPCarilamaran
(
@Rentangumur varchar(10),
@kelamin varchar(10),
@rentanglulus1 varchar(10),
@rentanglulus2 varchar(10),
@TargetKerja varchar(10)
)
AS
begin
select * from TLowongan where JenisKelamin = @Kelamin and RentangUmur=@rentangumur
select * from TPendidikan whereTahunKelulusan between @rentanglulus1 and @rentanglulus2
select * from TLamaran whereTargetPekerjaan = @targetkerja
end
TRIGGER 1
CREATE TRIGGER trTbBayar_Insert
ON Bayar
after INSERT
AS
BEGIN
DECLARE@Jumlah int
DECLARE@Idpemasok varchar(5)
SELECT@Idpemasok=Idpemasok, @Jumlah=totalbayar FROM INSERTED
UPDATEPemasok SET Hutang=Hutang-@Jumlah WHERE Idpemasok=@Idpemasok
END
select * from pemasok
insert bayar(idbayar,idpemasok,totalbayar)values('002','001','5000')
select * from bayar
TRIGGER 2
CREATE TRIGGER trTbBeli_Insert
ON Beli
after INSERT
AS
BEGIN
DECLARE@Jumlah int
DECLARE@Idpemasok varchar(5)
SELECT@Idpemasok=Idpemasok, @Jumlah=totalbeli FROM INSERTED
UPDATEPemasok SET Hutang=Hutang+@Jumlah WHERE Idpemasok=@Idpemasok
END
select * from pemasok
insert beli(idbeli,idpemasok,totalbeli)values('002','001','5000')
select * from beli
select * from pemasok
CURSOR 1
1. alter procedure csr_siswa3
as
begin
--1 Mendeklarasikan Cursor
DECLARE CsrMsiswa CURSOR
FOR Select nik,nama from Msiswa where NIK>=1 and NIK<=5
--2 Membuka Cursor
OPEN CsrMsiswa
--3 Memproses Cursor
declare@nomor int
DECLARE @NIK int
DECLARE @NAMAvarchar(10)
set @nomor =1
print 'DAFTAR HADIR SISWA'
print '28-04-2008'
print '================================'
print '|No |NIK |Nama |TTD |'
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
WHILE @@FETCH_STATUS=0
BEGIN
print'|'+convert(varchar,@nomor)+' '+'|'+convert(varchar,@nik)+' '+'|'+@nama+' |'+' |'
set@nomor = @nomor +1
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
END
print '================================'
print '| | |DOSEN: |PARAF: '
print '================================'
--4 Menutup Cursor
CLOSECsrMsiswa
--5 Mendealokasikan Cursor
DEALLOCATECsrMsiswa
end
exec dbo.csr_siswa3
2. alterprocedure csr_siswa3
as
begin
--1 Mendeklarasikan Cursor
DECLARECsrMsiswa CURSOR
FOR Select nik,nama from Msiswa where NIK>=1 and NIK<=5
--2 Membuka Cursor
OPENCsrMsiswa
--3 Memproses Cursor
declare@nomor int
DECLARE @NIK int
DECLARE @NAMAvarchar(10)
set @nomor =1
print 'DAFTAR HADIR SISWA'
print '28-04-2008'
print '========================================================================================'
print '|No |NIK |Nama |1 |2 |3 |4 |5 |Rata-rata |'
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
WHILE @@FETCH_STATUS=0
BEGIN
print'|'+convert(varchar,@nomor)+' '+'|'+convert(varchar,@nik)+' '+'|'+@nama+' |'+' |'+' |'+' |'+' |'+' |'+' |'
set@nomor = @nomor +1
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
END
print '========================================================================================'
print '| | | | | | | | | |'
print '========================================================================================'
--4 Menutup Cursor
CLOSECsrMsiswa
--5 Mendealokasikan Cursor
DEALLOCATECsrMsiswa
end
exec dbo.csr_siswa3
CURSOR 2
alter proc SP_cursor1
as
begin
declare Csrmahasiswa cursor
for select Departemen,count(NIK)
from Msiswa group by departemen
open csrmahasiswa
declare @jumlah int
declare @count int
declare @departemen varchar(20)
set @jumlah=0
print '====================='
print '=DEPARTEMEN = JUMLAH='
print '====================='
FETCH NEXT FROM csrmahasiswa into @departemen, @count
while @@fetch_status=0
begin
print'=' +convert(char(4), @departemen)+' '+' ='+convert(varchar,@count) + ' ' + 'ORANG='
set@jumlah=@jumlah+@count
FETCH NEXT FROM csrmahasiswa into@departemen, @count
end
print '====================='
print '=Total ='+convert(char(2),@jumlah)+'ORANG='
print '====================='
close csrmahasiswa
deallocate csrmahasiswa
end
exec SP_Cursor1
CURSOR NILAI MUTU
alter proc sp_Nilai
@mk varchar(7)
as
begin
declare crJum cursor
for select nilai
from tbnilai1 n join tbmahasiswa m on
n.nrp=m.nrp where mk=@mk
open crJum
declare @a int set @a=0
declare @b int set @b=0
declare @c int set @c=0
declare @d int set @d=0
declare @e int set @e=0
declare @NILAI int
FETCH NEXT FROM crJum into @NILAI
WHILE @@FETCH_STATUS=0
BEGIN
IF @NILAI>84 SET @a=@a+1
ELSE
IF @NILAI>69 SET @b=@b+1
ELSE
IF @NILAI>54 SET @c=@c+1
ELSE
IF @NILAI>39 SET @d=@d+1
ELSE
SET @e=@e+1
FETCH NEXT FROM crJum into @NILAI
END
print('Jumlah nilai A :'+convert(varchar,@a)+' orang')
print('Jumlah nilai B :'+convert(varchar,@b)+' orang')
print('Jumlah nilai C :'+convert(varchar,@c)+' orang')
print('Jumlah nilai D :'+convert(varchar,@d)+' orang')
print('Jumlah nilai E :'+convert(varchar,@e)+' orang')
CLOSE crJum
DEALLOCATE crJum
end
exec sp_Nilai 'MK02'
UAS BDL
ALTER PROCEDURE [dbo].[SP_BUKU_TABUNGAN]
AS
BEGIN
DECLARE CsrMsiswa_ CURSOR
FOR SELECT No_Rekening,Jenis_Tabungan,Nama,alamat FROM tbNasabah
OPEN CsrMsiswa_
DECLARE @NOM varchar(50)
DECLARE @JENIS varchar(50)
DECLARE @NAMA varchar(50)
DECLARE @ALAMAT varchar(50)
FETCH NEXT FROM CsrMsiswa_ into @NOM, @JENIS,@NAMA, @ALAMAT
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE CsrMsiswa CURSOR
FOR SelectTanggal,Jenis_trans,Keterangan,Jumlah,Saldo,NIK from tbTransaksi where No_Rekening = @NOM
--2 Membuka Cursor
OPEN CsrMsiswa
--3 Memproses Cursor
DECLARE @Tanggal datetime
DECLARE @Jenis_ varchar(50)
DECLARE @KET varchar(50)
DECLARE @Jumlah int
DECLARE @Saldo varchar(50)
DECLARE @NIK varchar(50)
DECLARE @DEF int
DECLARE @DEB int
DECLARE @KRE int
SET @DEF =1
print'------------------------------------------------------------------------------------------------------------------------------------------'
print 'No Rekening : '+@NOM
print 'Jenis Tabungan : '+@JENIS
print 'Nama :'+@NAMA
print 'Alamat : '+@ALAMAT
print'------------------------------------------------------------------------------------------------------------------------------------------'
PRINT 'No |'+'Tanggal |'+'NIK |'+'Keterangan |'+'Debet |'+'Kredit |'+'Saldo |'
FETCH NEXT FROM CsrMsiswa into @Tanggal, @Jenis_,@KET, @Jumlah, @Saldo, @NIK
WHILE @@FETCH_STATUS=0
BEGIN
if(@Jenis_= 'Debet')
BEGIN
SET @DEB = CONVERT(INT,@JUMLAH)
SET @KRE = 0
END
ELSE
BEGIN
SET
@KRE = CONVERT(INT,@JUMLAH)
SET @DEB = 0
END
PRINT CONVERT(CHAR(3),@DEF)+'|'+CONVERT(VARCHAR,@Tanggal)+' |'+@NIK+' |'+CONVERT(CHAR(30),@KET)+' |'+CONVERT(CHAR(10),@DEB)+' |'+CONVERT(CHAR(10),@KRE)+' |'+CONVERT(CHAR(20),@Saldo)+'|'
SET @DEF = @DEF+1
FETCH NEXT FROM CsrMsiswa into @Tanggal, @Jenis_,@KET, @Jumlah, @Saldo,@NIK
END
Print '============================================================='
--4 Menutup Cursor
CLOSE CsrMsiswa
--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa
FETCH NEXT FROM CsrMsiswa_ into @NOM, @JENIS,@NAMA, @ALAMAT
END
--4 Menutup Cursor
CLOSE CsrMsiswa_
--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa_
END
TRANSACTION 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
TRANSACTION 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
TRANSACTION 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
TRANSACTION 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
TRANSACTION 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
SCROLL BAR CURSOR 1
--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
SCROLL BAR2
--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, @GTTRANSPORTmoney
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),@GAJ+@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
SCROLLBAL 3
--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
SOAL TRIGGER
Sinkronisasi Stok Barang
1.Table Barang (Kode,nama,stok)
2.Table Jual (NOFJ,tglFJ,kode,QTY,harga)
Buat trigger untuk sinkronisasi stok
1. saat ada insert data pada tabel jual maka stok barang di tabel barang akan berkurang
4.modifikasi tabel barang menjadi(kode,nama,stok,jual01...jual12,QTY1...QTY12)buat integer terjual
1.update jual01 dengan (@QTY * @QTY harga)
saat ada insert/update pada tabel jual bulan januari
2.update QTY01 dengan @QTYsaat ada insert / update pada table jual bulan januari
JAWABAN
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <melihat perubahan pada penjualan>
--=============================================
alter TRIGGER TrInsertJual
ON dbo.tbJual
AFTER INSERT
AS
BEGIN
declare @NoFJ nchar(10)
declare @TGL datetime
declare @Kode char(10)
declare @Qty int
declare @Harga money
select@NoFJ = NoFJ, @TGL = TGL, @Kode = Kode, @Qty = Qty, @Harga = Harga
from inserted
INSERT INTO [SIPP].[dbo].[tbLog]
([TGl]
,[PERINTAH]
,[PENGGUNA]
,[KETERANGAN])
VALUES
(getdate(),'INSERT',Suser_Sname(),@NoFJ+'/'+convert(varchar,@TGL)+'/'+@Kode+'/'+convert(varchar,@Qty)+'/'+convert(varchar,@Harga))
END
GO
LATIHAN
-- NO 1
select * from Vw_Pasien
select * from Vw_Dokter
------------------------------------------------------------------------------
-- NO 2
create proc SP_Lihat_Pasien
@tahunchar(2),
@bulanchar(2)
as
begin
select* from t_pasien where id_pasien like 'P' + @tahun + @bulan + '%'
end
exec SP_Lihat_Pasien '07', '10'
------------------------------------------------------------------------------
-- NO 3
alter proc SP_Lihat_GolDarah
@Gol_Darahchar(2)
as
begin
select* from t_pasien where gol_darah like rtrim(@Gol_Darah)
end
exec SP_Lihat_GolDarah 'B'
------------------------------------------------------------------------------
-- NO 4
create proc SP_Tambah_Pasien
@nama varchar(30),
@alamat varchar(30),
@kota varchar(20),
@kota_lahir varchar(20),
@tgl_lahir datetime,
@gol_darah varchar(2),
@no_telp varchar(15),
@no_hp varchar(15),
@nama_kontak varchar(30),
@no_telp_kontak varchar(15),
@no_hp_kontak vrchar(15)
as
begin
declare @id_pasien varchar(10)
declare @no_akhir int
declare @tahun varchar(2)
declare @bulan varchar(2)
select @tahun = convert(varchar(20), right(datepart(yy,getdate()),2))
select @NoAkhir = convert(int, Max(right(id_pasien,3))
from tblPeg where id_pasien like 'P' + @tahun + @bulan + '%'
insert into t_pasien(id_pasien, nama, alamat, kota, kota_lahir, tgl_lahir, gol_darah,no_telp, no_hp, nama_kontak, no_telp_kontak, no_hp_kontak)
values(@id_pasien,@nama,@alamat,@kota,@kota_lahir,@tgl_lahir,@gol_darah,@no_telp,@no_hp,@nama_kontak,@no_telp_kontak,
@no_hp_kontak)
select*from t_pasien
end
exec SP_Tambah_Pasien 'B'
--------------------------------------------------------------------------------
NO 5a
alter FUNCTION FHitungUmur
( @tgl_lahir datetime )
RETURNS varchar(50)
AS
BEGIN
declare @return varchar(50)
declare @tahun varchar(10)
declare @bulan varchar(10
declare @hari varchar(10)
select @hari = convert(varchar(10), datediff(dd,@tgl_lahir,getdate()))
select @tahun = @hari/365
select @bulan = (@hari/30) - (@hari/365)*12
select @return = @tahun + ' tahun ' + @bulan + ' bulan'
RETURN @return
END
print dbo.FHitungUmur('1989-05-16')
------------------------------------------------------------------------------
-- NO 5b
create FUNCTION FHitungJumlahDokter
( @spesialisasi varchar(30) )
RETURNS int
AS
BEGIN
declare @return int
select @return = count(*) from t_dokter where spesialisasi =@spesialisasi
RETURN @return
END
print dbo.FHitungJumlahDokter('umum')
------------------------------------------------------------------------------
-- NO 5c
alter FUNCTIONFHitungJumlahPasienRawat()
RETURNS int
AS
BEGIN
declare @return int
select @return = count(distinct(id_pasien)) from t_rawat
where tgl_keluar = null
RETURN @return
END
print dbo.FHitungJumlahPasienRawat()
-- NO 1
select namabarang from barang
where namabarang like '%gear%'
update barang set namabarang
=stuff (namabarang, charindex('gear', namabarang), 4, 'GEAR')
where namabarang like '%gear%'
--------------------------------------------------------------------------
-- NO 2
alter FUNCTION JumlahBarang
(
@noFJ nvarchar(12) )
RETURNS int
AS
BEGIN
declare @return int
select @return = count(*) from fjdet where nofj = @nofj
RETURN @return
END
print dbo.JumlahBarang('FJ-0000001')
--------------------------------------------------------------------------
-- NO 3
create FUNCTION TotalQtyHarga
( @noFJ nvarchar(12) )
RETURNS money
AS
BEGIN
declare @return money
select @return = sum(qty*harga) from fjdet where nofj = @nofj
RETURN @return
END
print dbo.TotalQtyHarga('FJ-0000003')
--------------------------------------------------------------------------
-- NO 4
create FUNCTION JumlahNamaBarang
( @NamaBarang nvarchar(60) )
RETURNS int
AS
BEGIN
declare @return int
select @return = count(*) from barang
where namabarang like '%' + @NamaBarang + '%'
RETURN @return
END
print dbo.JumlahNamaBarang('Honda')
--------------------------------------------------------------------------
-- NO 5
create FUNCTION JumlahBarangStokKurang
( @jumlah int )
RETURNS int
AS
BEGIN
declare @return int
select @return = count(*) from barang where stok < @jumlah
RETURN @return
END
print dbo.JumlahBarangStokKurang(1)
--------------------------------------------------------------------------
-- NO 6
create FUNCTION JumlahPelangganPiutang
( @jumlahmoney )
RETURNS int
AS
BEGIN
declare @return int
select @return = count(*) from pelanggan where piutang > @jumlah
RETURN @return
END
print dbo.JumlahPelangganPiutang(100000.00)
FUNCTION
CREATE FUNCTION nofjakhir
(
)
RETURNS varchar (12)
AS
BEGIN
-- Declare the return variable here
DECLARE @NomorAkhir varchar(12)
Declare @X int
-- Add the T-SQL statements tocompute the return value here
select top 1 @NomorAkhir=NoFJ from FJ
order by NOFJ desc
-- Return the result of the function
RETURN @NomorAkhir
END
GO
AUTONUMBER
--LATIHAN AUTONUMBER----
--------------------------------------------------------------------------------------
--1 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(NoPO,5)))
from tbPO
where substring(NoPO,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 (NoPO, TglPO, KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)
END
exec dbo.SP_TambahTBPO '09/17/2008','S-00003'
select * from tbPO
----------------------------------------------------------------------------------
--2 function
drop function NOPOakhir
alter FUNCTION NOPOakhir
(
@tahun varchar (4)
)
RETURNS varchar(50)
AS
BEGIN
--Declare the return variable here
DECLARE @noakhir varchar (50)
declare @result varchar (50)
--Add the T-SQL statements to compute the return value here
select @NoAkhir = convert(int, Max(right(NoPO,5)))
from tbPO
where substring(NoPO,4,4) = convert(char(4), @tahun)
if @noakhir is null
set @noakhir =0
else
Set @result = 'PO-' + convert(char(4), @tahun) + '-' + RIGHT('00000'+Convert(varchar,@noakhir), 5)
--Return the result of the function
RETURN @result
END
GO
print dbo.NOPOakhir ('2008')
---------------------------------------------------------------------------------------------
--2
alter FUNCTION NOPObaru
(
@tahun datetime
)
RETURNS varchar(50)
AS
BEGIN
--Declare the return variable here
DECLARE @noakhir varchar (50)
declare @nobaru varchar (50)
declare @x int
declare @tglpo datetime
DECLARE @NomorPO varchar(13)
--Add the T-SQL statements to compute the return value here
SELECT top 1 @noakhir = NoPO
from TbPO
where substring (nopo,4,4)=@tahun
order by NoPO desc
-- Return the result of the function
Set @x = @NoAkhir + 1
Set @NoBaru ='PO-' + convert(char(4), Datepart(yyyy, @TglPO )) + '-' +RIGHT('00000'+Convert(varchar, @NoBaru), 5)
return @nobaru
END
GO
print dbo.NOPObaru ('2009')
select * from tbpo
-------------------------------------------------------------------------------------------
alter 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(NoPO,5)))
from tbPOBulanan
where substring(NoPO,4,4) =convert(char(4), Datepart(yyyy, @TglPO)) AND convert(int, substring(NoPO,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,datepar(mm,@TglPO)),2) + '-' +RIGHT('00000'+Convert(varchar, @NoBaru),5)
--3.Insert Data baru
Insert tbPOBulanan (NoPO, TglPO,KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)
END
exec SP_TambahTBPOBulanan'09/17/2008','S-0001'
select * from tbpobulanan
------------------------------------------------------------------------------------
alter PROCEDURE SP_TambahGudang
@TglPO datetime, @gudang varchar(50),@KodeSupplier varchar(50)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(20)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int,Max(right(NoPO,5)))
from tbPOtahunan
where substring(NoPO,4,4) =convert(char(4), Datepart(yyyy, @TglPO))and substring (NOPO,9,2)=@gudang
--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 )) + '-' +@gudang+ '-'+RIGHT('00000'+Convert(varchar, @NoBaru), 5)
--3.Insert Data baru
Insert tbpotahunan (NoPO, TglPO,gudang,KodeSupplier) Values (@NomorPO, @TglPO,@gudang,@KodeSupplier)
END
exec SP_TambahGudang'2010-10-01','G10','S-00001'
select * from tbpotahunan
delete from tbpotahunan
-------------------------------------------------------------------------------------------------------
create PROCEDURE SP_Tambahbarang
@Namabarang varchar (50),
@stok int
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @kodebarang varchar(20)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int,Max(right(kodebarang,3)))
from tbcobabarang
where left (kodebarang,1)=left(@namabarang,1)
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @kodebarang = left(@namabarang,1) + right ('00000'+ convert(varchar,@nobaru),3)
--3.Insert Data baru
Insert tbcobabarang(kodebarang,namabarang,stok)values (@kodebarang,@namabarang,@stok)
END
exec SP_Tambahbarang 'faadu',8
select * from tbcobabarang
FUNCTION
create FUNCTION JTotalFaktur
(
@tglAwaldatetime,
@tglAkhir datetime
)
RETURNS money
AS
BEGIN
DECLARE @Result money
SELECT @Result = sum(TotalFaktur) from FJ
where TglFJ between @tglAwal and @tglAkhir
RETURN @Result
END
GO
print dbo.JTotalFaktur ('2005-06-19','2005-08-19')
ALTER FUNCTION JPelanggan
(
@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
GO
print dbo.JPelanggan ('2005-06-19','2005-08-19')
CREATE FUNCTION JBarang
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS int
AS
BEGIN
DECLARE@Result int
SELECT @Result = sum(Qty) from FjDet fj join FJ f on (fj.NoFJ = f.NoFJ)
where TglFJ between @tglAwal and @tglAkhir
RETURN @Result
END
GO
print dbo.JBarang ('2005-06-19','2005-08-19')
create FUNCTION JNilaiBarang
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS money
AS
BEGIN
DECLARE @Result money
SELECT @Result = sum(Qty*Harga) from FjDet fj join FJ f on (fj.NoFJ = f.NoFJ)
where TglFJ between @tglAwal and @tglAkhir
RETURN @Result
END
GO
print dbo.JNilaiBarang ('2005-06-19','2005-08-19')
LATIHAN 2
select convert(varchar,datepart(yyyy,tgl_buat),4) + '/' + convert(varchar,datepart(mm,tgl_buat),2) + '/' +
rtrim([L/P]) +'/' + convert(varchar, datepart(dd,tgl_lahir)) + convert(varchar, datepart(mm,tgl_lahir)) + right(convert(varchar, datepart(yy,tgl_lahir)),2) + '/' +right(convert(varchar,id),5) as No_KTP, tgl_buat, nama, [L/P], tgl_lahir from t_ktp
select * from t_ktp
--------------------------------------------------------------------------------------------------------------
alter PROCEDURE SP_Mahasiswa
@Nama varchar(50),
@LP varchar(1),
@jurusan varchar(2)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
declare @id varchar(14)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(substring(no_daftar,8,4))) from t_univ
--2.Cari NomorBaru
if @NoAkhir is null
Set @Nobaru = 1
else
Set @NoBaru = @NoAkhir + 1
set @id = convert(varchar,datepart(yyyy,getdate())) + '/' +@LP + '-' +right('0000' + Convert(varchar, @NoBaru),4) + '/' +@jurusan
--3.Insert Databaru
Insert into t_univ(no_daftar, Nama, LP, tgl_daftar, jurusan)
Values(@id, @Nama, @LP, getdate(), @jurusan)
select * from t_univ
END
exec SP_Mahasiswa 'richi', 'L', 'IT'
exec SP_Mahasiswa 'asdfsd', 'P', 'SS'
exec SP_Mahasiswa 'sgfdf', 'L', 'AA'
exec SP_Mahasiswa 'wgfe', 'X', 'fg'
exec SP_mahasiswa 'alala', 'L', 'EL'
exec SP_mahasiswa 'abababa', 'P', 'PQ'
create PROCEDURE SP_Mahasiswa
@Nama varchar(50),
@LP varchar(1),
@jurusan varchar(2)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
declare @id varchar(14)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(substring(NoDaftar,8,4))) from Daftar
--2.Cari Nomor Baru
if @NoAkhir is null Set @Nobaru = 1
else
Set @NoBaru = @NoAkhir + 1
set @id = convert(varchar,datepart(yyyy,getdate())) + '/' +@LP + '-' +right('0000' + Convert(varchar, @NoBaru),4) + '/' +@jurusan
--3.Insert Data baru
Insert into Daftar(NoDaftar, Nama, LP, tglDaftar, Jurusan)
Values(@id, @Nama, @LP, getdate(), @jurusan)
select *from Daftar
END
exec SP_Mahasiswa 'richi', 'L', 'IT'
exec SP_Mahasiswa 'asdfsd', 'P', 'SS'
-------------------------------
alter PROCEDURE SPPeg
@Nama varchar(50),
@TahunMasuk datetime
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
declare @id varchar(14)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(left(NoPegawai,5))) from Peg
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru = 1
else
Set @NoBaru = @NoAkhir + 1
set @id = right('00000' + Convert(varchar, @NoBaru),5) + '-'+ convert(varchar,datepart(yyyy,@TahunMasuk)) + '-' +
left(CONVERT(varchar,@Nama),1)
--3.Insert Data baru
Insert into Peg(NoPegawai, TahunMasuk, Nama)
Values(@id, @TahunMasuk, @Nama)
select * from Peg
END
exec SPPeg '01/01/2008', 'Amir'
create procedure SPUpdLowongan
@idperusahaan varchar(10),
@idlowongan varchar(10),
@posisi varchar(50),
@jumlah int,
@jeniskelamin varchar(15),
@rentangumur varchar(20),
@persyaratan varchar(50)
as
begin
if exists(select idlowongan from
TLowongan where IDLowongan=@idlowongan)
begin
if
exists(select IDPerusahaan from TInstansi where IDPerusahaan=@idperusahaan)
begin
update
Tlowongan set
posisi=@posisi,jumlah=@jumlah,jeniskelamin=@jeniskelamin,rentangumur=@rentangumur,persyaratan=@persyaratan
where
@idlowongan = IDLowongan
end
else
begin
print'Data perusahaan tidak ada'
end
end
else
begin
print 'Data lowongan tidak ditemukan'
end
END
select * from tlowongan
exec SPUpdLowongan
'I-0001','LW-0001','satpam','2','L','25-40','Ganteng'
select * from tlowongan
2. CREATE procedure SPUpdPelamar
@idpelamar varchar(10),
@NamaLengkap varchar(50),
@Alamat varchar(100),
@NoTlp int,
@TempatLahir varchar(50),
@TanggalLahir datetime,
@Agama varchar(15),
@JenisKelamin varchar(20),
@PengalamanKerja varchar(200)
as
begin
if exists (select idpelamar from
TPelamar where IDPelamar=@idpelamar)
begin
update TPelamar set
NamaLengkap=@namalengkap,Alamat=@alamat,NoTlp=@notlp,TempatLahir=@tempatlahir,TanggalLahir=@tanggallahir,Agama=@agama,JenisKelamin=@JenisKelamin,PengalamanKerja=@PengalamanKerja
where idpelamar=@idpelamar
end
else
begin
print 'Id pelamar tidak ditemukan'
end
END
select * from tpelamar
exec SPUpdPelamar'P-0002','Ultear','Jlkircon','575687568','bandung','06/05/1989','atheis','P','Nihil'
select * from tpelamar
STORE PROCEDURE INSERT
create procedure SPInsLowongan
@idperusahaan varchar(10),
@posisi varchar(50),
@jumlah int,
@jeniskelamin varchar(15),
@rentangumur varchar(20),
@persyaratan varchar(50)
as
begin
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @Nomor varchar(20)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int,Max(right(IDlowongan,4))) from Tlowongan
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @Nomor = 'LW-' +
RIGHT('00000'+Convert(varchar, @NoBaru), 4)
--3.Insert Data baru
if exists(select idperusahaan from tinstansi where idperusahaan=@idperusahaan)
begin
Insert Tlowongan (idperusahaan,idlowongan,posisi,jumlah,jeniskelamin,rentangumur,persyaratan)
Values(@idperusahaan,@nomor,@posisi,@jumlah,@jeniskelamin,@rentangumur,@persyaratan)
end
else
begin
print 'Data perusahaan tidak ditemukan'
end
END
exec SPInsLowongan'I-0001','Manager','1','L','25-40','Ganteng'
exec SPInsLowongan'I-0002','Admin','1','L','25-40','Ganteng'
exec SPInsLowongan'I-0003','Supervisor','1','L','25-40','Ganteng'
exec SPInsLowongan'I-0003','Personalia','1','P','25-40','Cantik'
exec SPInsLowongan'I-0004','CS','2','P','25-27','Cantik, Penampilan Menarik'
exec SPInsLowongan'I-0005','Marketing','2','L','25-40','Ganteng'
exec SPInsLowongan'I-0006','Sales','4','P','25-30','Cantik,Supel'
select * from tlowongan
CREATE procedure SPInsInstansi
@NamaPerusahaan varchar(50),
@Alamat varchar(200),
@Notlp int
as
begin
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @Nomor varchar(20)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int,
Max(right(IDPerusahaan,4))) From Tinstansi
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @Nomor = 'I-' +
RIGHT('00000'+Convert(varchar, @NoBaru), 4)
--3.Insert Data baru
Insert Tinstansi(idperusahaan,namaperusahaan,alamat,notelp)
Values(@nomor,@namaperusahaan,@alamat,@notlp)
END
exec dbo.SPInsInstansi 'Telkom','JlJapati','022765666'
exec dbo.SPInsInstansi'Telkomsel','JlBKR','022765612'
exec dbo.SPInsInstansi 'BankMandiri','Jl Asia Afrika','022765655'
exec dbo.SPInsInstansi 'Bank Jabar','JlJamika','022765623'
exec dbo.SPInsInstansi'Bank Mega','JlGatsu','022765645'
exec dbo.SPInsInstansi 'Hypermart','JlMerdeka','022765689'
select * from tinstansi
STORE PROCEDURE DELETE
1.alter proc SPDelete_lowongan
@idlowongan varchar(10)
as
begin
delete from tlowongan where
idlowongan=@idlowongan
end
select * from TLowongan
exec SPDelete_lowongan 'LW-0007'
select * from TLowongan
2.alter proc SPDelete_saluran
@idpenyaluran varchar(10)
as
begin
delete from tsaluran where idpenyaluran=@idpenyaluran
end
select * from tsaluran
exec SPDelete_saluran 'PY-0002'
select * from tsaluran
FUNGSI 1
alter function CariUmur
( @nama varchar(50)
)
returns int
as
begin
declare @tgl datetime
declare @umur int
select @tgl=TanggalLahir from TPelamar
where IDPelamar=@nama set @umur=datediff(dd,@tgl,getdate())/365
return @umur
end select dbo.CariUmur (IDPelamar) as Umur from TPelamar
FUNGSI 2
alter function PendidikanTerakhir
(@IDPelamar varchar(10)
)
returns varchar(50)
as
begin
declare @IDPendidikan varchar(10)
declare @NamaPendidikan varchar(50)
declare @ket varchar(50)
select top (1) @ket = NamaPendidikan from TPendidikan
where @IDPelamar = IDPelamar order by IDPendidikan desc
return @ket
end
select distinct dbo.PendidikanTerakhir(IDPelamar) from TPendidikan
FUNGSI CARI
alter PROCEDURE SPCarilamaran
(
@Rentangumur varchar(10),
@kelamin varchar(10),
@rentanglulus1 varchar(10),
@rentanglulus2 varchar(10),
@TargetKerja varchar(10)
)
AS
begin
select * from TLowongan where JenisKelamin = @Kelamin and RentangUmur=@rentangumur
select * from TPendidikan whereTahunKelulusan between @rentanglulus1 and @rentanglulus2
select * from TLamaran whereTargetPekerjaan = @targetkerja
end
TRIGGER 1
CREATE TRIGGER trTbBayar_Insert
ON Bayar
after INSERT
AS
BEGIN
DECLARE@Jumlah int
DECLARE@Idpemasok varchar(5)
SELECT@Idpemasok=Idpemasok, @Jumlah=totalbayar FROM INSERTED
UPDATEPemasok SET Hutang=Hutang-@Jumlah WHERE Idpemasok=@Idpemasok
END
select * from pemasok
insert bayar(idbayar,idpemasok,totalbayar)values('002','001','5000')
select * from bayar
TRIGGER 2
CREATE TRIGGER trTbBeli_Insert
ON Beli
after INSERT
AS
BEGIN
DECLARE@Jumlah int
DECLARE@Idpemasok varchar(5)
SELECT@Idpemasok=Idpemasok, @Jumlah=totalbeli FROM INSERTED
UPDATEPemasok SET Hutang=Hutang+@Jumlah WHERE Idpemasok=@Idpemasok
END
select * from pemasok
insert beli(idbeli,idpemasok,totalbeli)values('002','001','5000')
select * from beli
select * from pemasok
CURSOR 1
1. alter procedure csr_siswa3
as
begin
--1 Mendeklarasikan Cursor
DECLARE CsrMsiswa CURSOR
FOR Select nik,nama from Msiswa where NIK>=1 and NIK<=5
--2 Membuka Cursor
OPEN CsrMsiswa
--3 Memproses Cursor
declare@nomor int
DECLARE @NIK int
DECLARE @NAMAvarchar(10)
set @nomor =1
print 'DAFTAR HADIR SISWA'
print '28-04-2008'
print '================================'
print '|No |NIK |Nama |TTD |'
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
WHILE @@FETCH_STATUS=0
BEGIN
print'|'+convert(varchar,@nomor)+' '+'|'+convert(varchar,@nik)+' '+'|'+@nama+' |'+' |'
set@nomor = @nomor +1
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
END
print '================================'
print '| | |DOSEN: |PARAF: '
print '================================'
--4 Menutup Cursor
CLOSECsrMsiswa
--5 Mendealokasikan Cursor
DEALLOCATECsrMsiswa
end
exec dbo.csr_siswa3
2. alterprocedure csr_siswa3
as
begin
--1 Mendeklarasikan Cursor
DECLARECsrMsiswa CURSOR
FOR Select nik,nama from Msiswa where NIK>=1 and NIK<=5
--2 Membuka Cursor
OPENCsrMsiswa
--3 Memproses Cursor
declare@nomor int
DECLARE @NIK int
DECLARE @NAMAvarchar(10)
set @nomor =1
print 'DAFTAR HADIR SISWA'
print '28-04-2008'
print '========================================================================================'
print '|No |NIK |Nama |1 |2 |3 |4 |5 |Rata-rata |'
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
WHILE @@FETCH_STATUS=0
BEGIN
print'|'+convert(varchar,@nomor)+' '+'|'+convert(varchar,@nik)+' '+'|'+@nama+' |'+' |'+' |'+' |'+' |'+' |'+' |'
set@nomor = @nomor +1
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
END
print '========================================================================================'
print '| | | | | | | | | |'
print '========================================================================================'
--4 Menutup Cursor
CLOSECsrMsiswa
--5 Mendealokasikan Cursor
DEALLOCATECsrMsiswa
end
exec dbo.csr_siswa3
CURSOR 2
alter proc SP_cursor1
as
begin
declare Csrmahasiswa cursor
for select Departemen,count(NIK)
from Msiswa group by departemen
open csrmahasiswa
declare @jumlah int
declare @count int
declare @departemen varchar(20)
set @jumlah=0
print '====================='
print '=DEPARTEMEN = JUMLAH='
print '====================='
FETCH NEXT FROM csrmahasiswa into @departemen, @count
while @@fetch_status=0
begin
print'=' +convert(char(4), @departemen)+' '+' ='+convert(varchar,@count) + ' ' + 'ORANG='
set@jumlah=@jumlah+@count
FETCH NEXT FROM csrmahasiswa into@departemen, @count
end
print '====================='
print '=Total ='+convert(char(2),@jumlah)+'ORANG='
print '====================='
close csrmahasiswa
deallocate csrmahasiswa
end
exec SP_Cursor1
CURSOR NILAI MUTU
alter proc sp_Nilai
@mk varchar(7)
as
begin
declare crJum cursor
for select nilai
from tbnilai1 n join tbmahasiswa m on
n.nrp=m.nrp where mk=@mk
open crJum
declare @a int set @a=0
declare @b int set @b=0
declare @c int set @c=0
declare @d int set @d=0
declare @e int set @e=0
declare @NILAI int
FETCH NEXT FROM crJum into @NILAI
WHILE @@FETCH_STATUS=0
BEGIN
IF @NILAI>84 SET @a=@a+1
ELSE
IF @NILAI>69 SET @b=@b+1
ELSE
IF @NILAI>54 SET @c=@c+1
ELSE
IF @NILAI>39 SET @d=@d+1
ELSE
SET @e=@e+1
FETCH NEXT FROM crJum into @NILAI
END
print('Jumlah nilai A :'+convert(varchar,@a)+' orang')
print('Jumlah nilai B :'+convert(varchar,@b)+' orang')
print('Jumlah nilai C :'+convert(varchar,@c)+' orang')
print('Jumlah nilai D :'+convert(varchar,@d)+' orang')
print('Jumlah nilai E :'+convert(varchar,@e)+' orang')
CLOSE crJum
DEALLOCATE crJum
end
exec sp_Nilai 'MK02'
UAS BDL
ALTER PROCEDURE [dbo].[SP_BUKU_TABUNGAN]
AS
BEGIN
DECLARE CsrMsiswa_ CURSOR
FOR SELECT No_Rekening,Jenis_Tabungan,Nama,alamat FROM tbNasabah
OPEN CsrMsiswa_
DECLARE @NOM varchar(50)
DECLARE @JENIS varchar(50)
DECLARE @NAMA varchar(50)
DECLARE @ALAMAT varchar(50)
FETCH NEXT FROM CsrMsiswa_ into @NOM, @JENIS,@NAMA, @ALAMAT
WHILE @@FETCH_STATUS=0
BEGIN
DECLARE CsrMsiswa CURSOR
FOR SelectTanggal,Jenis_trans,Keterangan,Jumlah,Saldo,NIK from tbTransaksi where No_Rekening = @NOM
--2 Membuka Cursor
OPEN CsrMsiswa
--3 Memproses Cursor
DECLARE @Tanggal datetime
DECLARE @Jenis_ varchar(50)
DECLARE @KET varchar(50)
DECLARE @Jumlah int
DECLARE @Saldo varchar(50)
DECLARE @NIK varchar(50)
DECLARE @DEF int
DECLARE @DEB int
DECLARE @KRE int
SET @DEF =1
print'------------------------------------------------------------------------------------------------------------------------------------------'
print 'No Rekening : '+@NOM
print 'Jenis Tabungan : '+@JENIS
print 'Nama :'+@NAMA
print 'Alamat : '+@ALAMAT
print'------------------------------------------------------------------------------------------------------------------------------------------'
PRINT 'No |'+'Tanggal |'+'NIK |'+'Keterangan |'+'Debet |'+'Kredit |'+'Saldo |'
FETCH NEXT FROM CsrMsiswa into @Tanggal, @Jenis_,@KET, @Jumlah, @Saldo, @NIK
WHILE @@FETCH_STATUS=0
BEGIN
if(@Jenis_= 'Debet')
BEGIN
SET @DEB = CONVERT(INT,@JUMLAH)
SET @KRE = 0
END
ELSE
BEGIN
SET
@KRE = CONVERT(INT,@JUMLAH)
SET @DEB = 0
END
PRINT CONVERT(CHAR(3),@DEF)+'|'+CONVERT(VARCHAR,@Tanggal)+' |'+@NIK+' |'+CONVERT(CHAR(30),@KET)+' |'+CONVERT(CHAR(10),@DEB)+' |'+CONVERT(CHAR(10),@KRE)+' |'+CONVERT(CHAR(20),@Saldo)+'|'
SET @DEF = @DEF+1
FETCH NEXT FROM CsrMsiswa into @Tanggal, @Jenis_,@KET, @Jumlah, @Saldo,@NIK
END
Print '============================================================='
--4 Menutup Cursor
CLOSE CsrMsiswa
--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa
FETCH NEXT FROM CsrMsiswa_ into @NOM, @JENIS,@NAMA, @ALAMAT
END
--4 Menutup Cursor
CLOSE CsrMsiswa_
--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa_
END
TRANSACTION 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
TRANSACTION 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
TRANSACTION 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
TRANSACTION 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
TRANSACTION 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
SCROLL BAR CURSOR 1
--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
SCROLL BAR2
--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, @GTTRANSPORTmoney
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),@GAJ+@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
SCROLLBAL 3
--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
SOAL TRIGGER
Sinkronisasi Stok Barang
1.Table Barang (Kode,nama,stok)
2.Table Jual (NOFJ,tglFJ,kode,QTY,harga)
Buat trigger untuk sinkronisasi stok
1. saat ada insert data pada tabel jual maka stok barang di tabel barang akan berkurang
4.modifikasi tabel barang menjadi(kode,nama,stok,jual01...jual12,QTY1...QTY12)buat integer terjual
1.update jual01 dengan (@QTY * @QTY harga)
saat ada insert/update pada tabel jual bulan januari
2.update QTY01 dengan @QTYsaat ada insert / update pada table jual bulan januari
JAWABAN
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <melihat perubahan pada penjualan>
--=============================================
alter TRIGGER TrInsertJual
ON dbo.tbJual
AFTER INSERT
AS
BEGIN
declare @NoFJ nchar(10)
declare @TGL datetime
declare @Kode char(10)
declare @Qty int
declare @Harga money
select@NoFJ = NoFJ, @TGL = TGL, @Kode = Kode, @Qty = Qty, @Harga = Harga
from inserted
INSERT INTO [SIPP].[dbo].[tbLog]
([TGl]
,[PERINTAH]
,[PENGGUNA]
,[KETERANGAN])
VALUES
(getdate(),'INSERT',Suser_Sname(),@NoFJ+'/'+convert(varchar,@TGL)+'/'+@Kode+'/'+convert(varchar,@Qty)+'/'+convert(varchar,@Harga))
END
GO
LATIHAN
-- NO 1
select * from Vw_Pasien
select * from Vw_Dokter
------------------------------------------------------------------------------
-- NO 2
create proc SP_Lihat_Pasien
@tahunchar(2),
@bulanchar(2)
as
begin
select* from t_pasien where id_pasien like 'P' + @tahun + @bulan + '%'
end
exec SP_Lihat_Pasien '07', '10'
------------------------------------------------------------------------------
-- NO 3
alter proc SP_Lihat_GolDarah
@Gol_Darahchar(2)
as
begin
select* from t_pasien where gol_darah like rtrim(@Gol_Darah)
end
exec SP_Lihat_GolDarah 'B'
------------------------------------------------------------------------------
-- NO 4
create proc SP_Tambah_Pasien
@nama varchar(30),
@alamat varchar(30),
@kota varchar(20),
@kota_lahir varchar(20),
@tgl_lahir datetime,
@gol_darah varchar(2),
@no_telp varchar(15),
@no_hp varchar(15),
@nama_kontak varchar(30),
@no_telp_kontak varchar(15),
@no_hp_kontak vrchar(15)
as
begin
declare @id_pasien varchar(10)
declare @no_akhir int
declare @tahun varchar(2)
declare @bulan varchar(2)
select @tahun = convert(varchar(20), right(datepart(yy,getdate()),2))
select @NoAkhir = convert(int, Max(right(id_pasien,3))
from tblPeg where id_pasien like 'P' + @tahun + @bulan + '%'
insert into t_pasien(id_pasien, nama, alamat, kota, kota_lahir, tgl_lahir, gol_darah,no_telp, no_hp, nama_kontak, no_telp_kontak, no_hp_kontak)
values(@id_pasien,@nama,@alamat,@kota,@kota_lahir,@tgl_lahir,@gol_darah,@no_telp,@no_hp,@nama_kontak,@no_telp_kontak,
@no_hp_kontak)
select*from t_pasien
end
exec SP_Tambah_Pasien 'B'
--------------------------------------------------------------------------------
NO 5a
alter FUNCTION FHitungUmur
( @tgl_lahir datetime )
RETURNS varchar(50)
AS
BEGIN
declare @return varchar(50)
declare @tahun varchar(10)
declare @bulan varchar(10
declare @hari varchar(10)
select @hari = convert(varchar(10), datediff(dd,@tgl_lahir,getdate()))
select @tahun = @hari/365
select @bulan = (@hari/30) - (@hari/365)*12
select @return = @tahun + ' tahun ' + @bulan + ' bulan'
RETURN @return
END
print dbo.FHitungUmur('1989-05-16')
------------------------------------------------------------------------------
-- NO 5b
create FUNCTION FHitungJumlahDokter
( @spesialisasi varchar(30) )
RETURNS int
AS
BEGIN
declare @return int
select @return = count(*) from t_dokter where spesialisasi =@spesialisasi
RETURN @return
END
print dbo.FHitungJumlahDokter('umum')
------------------------------------------------------------------------------
-- NO 5c
alter FUNCTIONFHitungJumlahPasienRawat()
RETURNS int
AS
BEGIN
declare @return int
select @return = count(distinct(id_pasien)) from t_rawat
where tgl_keluar = null
RETURN @return
END
print dbo.FHitungJumlahPasienRawat()
-- NO 1
select namabarang from barang
where namabarang like '%gear%'
update barang set namabarang
=stuff (namabarang, charindex('gear', namabarang), 4, 'GEAR')
where namabarang like '%gear%'
--------------------------------------------------------------------------
-- NO 2
alter FUNCTION JumlahBarang
(
@noFJ nvarchar(12) )
RETURNS int
AS
BEGIN
declare @return int
select @return = count(*) from fjdet where nofj = @nofj
RETURN @return
END
print dbo.JumlahBarang('FJ-0000001')
--------------------------------------------------------------------------
-- NO 3
create FUNCTION TotalQtyHarga
( @noFJ nvarchar(12) )
RETURNS money
AS
BEGIN
declare @return money
select @return = sum(qty*harga) from fjdet where nofj = @nofj
RETURN @return
END
print dbo.TotalQtyHarga('FJ-0000003')
--------------------------------------------------------------------------
-- NO 4
create FUNCTION JumlahNamaBarang
( @NamaBarang nvarchar(60) )
RETURNS int
AS
BEGIN
declare @return int
select @return = count(*) from barang
where namabarang like '%' + @NamaBarang + '%'
RETURN @return
END
print dbo.JumlahNamaBarang('Honda')
--------------------------------------------------------------------------
-- NO 5
create FUNCTION JumlahBarangStokKurang
( @jumlah int )
RETURNS int
AS
BEGIN
declare @return int
select @return = count(*) from barang where stok < @jumlah
RETURN @return
END
print dbo.JumlahBarangStokKurang(1)
--------------------------------------------------------------------------
-- NO 6
create FUNCTION JumlahPelangganPiutang
( @jumlahmoney )
RETURNS int
AS
BEGIN
declare @return int
select @return = count(*) from pelanggan where piutang > @jumlah
RETURN @return
END
print dbo.JumlahPelangganPiutang(100000.00)
FUNCTION
CREATE FUNCTION nofjakhir
(
)
RETURNS varchar (12)
AS
BEGIN
-- Declare the return variable here
DECLARE @NomorAkhir varchar(12)
Declare @X int
-- Add the T-SQL statements tocompute the return value here
select top 1 @NomorAkhir=NoFJ from FJ
order by NOFJ desc
-- Return the result of the function
RETURN @NomorAkhir
END
GO
AUTONUMBER
--LATIHAN AUTONUMBER----
--------------------------------------------------------------------------------------
--1 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(NoPO,5)))
from tbPO
where substring(NoPO,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 (NoPO, TglPO, KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)
END
exec dbo.SP_TambahTBPO '09/17/2008','S-00003'
select * from tbPO
----------------------------------------------------------------------------------
--2 function
drop function NOPOakhir
alter FUNCTION NOPOakhir
(
@tahun varchar (4)
)
RETURNS varchar(50)
AS
BEGIN
--Declare the return variable here
DECLARE @noakhir varchar (50)
declare @result varchar (50)
--Add the T-SQL statements to compute the return value here
select @NoAkhir = convert(int, Max(right(NoPO,5)))
from tbPO
where substring(NoPO,4,4) = convert(char(4), @tahun)
if @noakhir is null
set @noakhir =0
else
Set @result = 'PO-' + convert(char(4), @tahun) + '-' + RIGHT('00000'+Convert(varchar,@noakhir), 5)
--Return the result of the function
RETURN @result
END
GO
print dbo.NOPOakhir ('2008')
---------------------------------------------------------------------------------------------
--2
alter FUNCTION NOPObaru
(
@tahun datetime
)
RETURNS varchar(50)
AS
BEGIN
--Declare the return variable here
DECLARE @noakhir varchar (50)
declare @nobaru varchar (50)
declare @x int
declare @tglpo datetime
DECLARE @NomorPO varchar(13)
--Add the T-SQL statements to compute the return value here
SELECT top 1 @noakhir = NoPO
from TbPO
where substring (nopo,4,4)=@tahun
order by NoPO desc
-- Return the result of the function
Set @x = @NoAkhir + 1
Set @NoBaru ='PO-' + convert(char(4), Datepart(yyyy, @TglPO )) + '-' +RIGHT('00000'+Convert(varchar, @NoBaru), 5)
return @nobaru
END
GO
print dbo.NOPObaru ('2009')
select * from tbpo
-------------------------------------------------------------------------------------------
alter 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(NoPO,5)))
from tbPOBulanan
where substring(NoPO,4,4) =convert(char(4), Datepart(yyyy, @TglPO)) AND convert(int, substring(NoPO,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,datepar(mm,@TglPO)),2) + '-' +RIGHT('00000'+Convert(varchar, @NoBaru),5)
--3.Insert Data baru
Insert tbPOBulanan (NoPO, TglPO,KodeSupplier) Values (@NomorPO, @TglPO, @KodeSupplier)
END
exec SP_TambahTBPOBulanan'09/17/2008','S-0001'
select * from tbpobulanan
------------------------------------------------------------------------------------
alter PROCEDURE SP_TambahGudang
@TglPO datetime, @gudang varchar(50),@KodeSupplier varchar(50)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(20)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int,Max(right(NoPO,5)))
from tbPOtahunan
where substring(NoPO,4,4) =convert(char(4), Datepart(yyyy, @TglPO))and substring (NOPO,9,2)=@gudang
--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 )) + '-' +@gudang+ '-'+RIGHT('00000'+Convert(varchar, @NoBaru), 5)
--3.Insert Data baru
Insert tbpotahunan (NoPO, TglPO,gudang,KodeSupplier) Values (@NomorPO, @TglPO,@gudang,@KodeSupplier)
END
exec SP_TambahGudang'2010-10-01','G10','S-00001'
select * from tbpotahunan
delete from tbpotahunan
-------------------------------------------------------------------------------------------------------
create PROCEDURE SP_Tambahbarang
@Namabarang varchar (50),
@stok int
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @kodebarang varchar(20)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int,Max(right(kodebarang,3)))
from tbcobabarang
where left (kodebarang,1)=left(@namabarang,1)
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1
Set @kodebarang = left(@namabarang,1) + right ('00000'+ convert(varchar,@nobaru),3)
--3.Insert Data baru
Insert tbcobabarang(kodebarang,namabarang,stok)values (@kodebarang,@namabarang,@stok)
END
exec SP_Tambahbarang 'faadu',8
select * from tbcobabarang
FUNCTION
create FUNCTION JTotalFaktur
(
@tglAwaldatetime,
@tglAkhir datetime
)
RETURNS money
AS
BEGIN
DECLARE @Result money
SELECT @Result = sum(TotalFaktur) from FJ
where TglFJ between @tglAwal and @tglAkhir
RETURN @Result
END
GO
print dbo.JTotalFaktur ('2005-06-19','2005-08-19')
ALTER FUNCTION JPelanggan
(
@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
GO
print dbo.JPelanggan ('2005-06-19','2005-08-19')
CREATE FUNCTION JBarang
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS int
AS
BEGIN
DECLARE@Result int
SELECT @Result = sum(Qty) from FjDet fj join FJ f on (fj.NoFJ = f.NoFJ)
where TglFJ between @tglAwal and @tglAkhir
RETURN @Result
END
GO
print dbo.JBarang ('2005-06-19','2005-08-19')
create FUNCTION JNilaiBarang
(
@tglAwal datetime,
@tglAkhir datetime
)
RETURNS money
AS
BEGIN
DECLARE @Result money
SELECT @Result = sum(Qty*Harga) from FjDet fj join FJ f on (fj.NoFJ = f.NoFJ)
where TglFJ between @tglAwal and @tglAkhir
RETURN @Result
END
GO
print dbo.JNilaiBarang ('2005-06-19','2005-08-19')
LATIHAN 2
select convert(varchar,datepart(yyyy,tgl_buat),4) + '/' + convert(varchar,datepart(mm,tgl_buat),2) + '/' +
rtrim([L/P]) +'/' + convert(varchar, datepart(dd,tgl_lahir)) + convert(varchar, datepart(mm,tgl_lahir)) + right(convert(varchar, datepart(yy,tgl_lahir)),2) + '/' +right(convert(varchar,id),5) as No_KTP, tgl_buat, nama, [L/P], tgl_lahir from t_ktp
select * from t_ktp
--------------------------------------------------------------------------------------------------------------
alter PROCEDURE SP_Mahasiswa
@Nama varchar(50),
@LP varchar(1),
@jurusan varchar(2)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
declare @id varchar(14)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(substring(no_daftar,8,4))) from t_univ
--2.Cari NomorBaru
if @NoAkhir is null
Set @Nobaru = 1
else
Set @NoBaru = @NoAkhir + 1
set @id = convert(varchar,datepart(yyyy,getdate())) + '/' +@LP + '-' +right('0000' + Convert(varchar, @NoBaru),4) + '/' +@jurusan
--3.Insert Databaru
Insert into t_univ(no_daftar, Nama, LP, tgl_daftar, jurusan)
Values(@id, @Nama, @LP, getdate(), @jurusan)
select * from t_univ
END
exec SP_Mahasiswa 'richi', 'L', 'IT'
exec SP_Mahasiswa 'asdfsd', 'P', 'SS'
exec SP_Mahasiswa 'sgfdf', 'L', 'AA'
exec SP_Mahasiswa 'wgfe', 'X', 'fg'
exec SP_mahasiswa 'alala', 'L', 'EL'
exec SP_mahasiswa 'abababa', 'P', 'PQ'
create PROCEDURE SP_Mahasiswa
@Nama varchar(50),
@LP varchar(1),
@jurusan varchar(2)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
declare @id varchar(14)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(substring(NoDaftar,8,4))) from Daftar
--2.Cari Nomor Baru
if @NoAkhir is null Set @Nobaru = 1
else
Set @NoBaru = @NoAkhir + 1
set @id = convert(varchar,datepart(yyyy,getdate())) + '/' +@LP + '-' +right('0000' + Convert(varchar, @NoBaru),4) + '/' +@jurusan
--3.Insert Data baru
Insert into Daftar(NoDaftar, Nama, LP, tglDaftar, Jurusan)
Values(@id, @Nama, @LP, getdate(), @jurusan)
select *from Daftar
END
exec SP_Mahasiswa 'richi', 'L', 'IT'
exec SP_Mahasiswa 'asdfsd', 'P', 'SS'
-------------------------------
alter PROCEDURE SPPeg
@Nama varchar(50),
@TahunMasuk datetime
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
declare @id varchar(14)
--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(left(NoPegawai,5))) from Peg
--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru = 1
else
Set @NoBaru = @NoAkhir + 1
set @id = right('00000' + Convert(varchar, @NoBaru),5) + '-'+ convert(varchar,datepart(yyyy,@TahunMasuk)) + '-' +
left(CONVERT(varchar,@Nama),1)
--3.Insert Data baru
Insert into Peg(NoPegawai, TahunMasuk, Nama)
Values(@id, @TahunMasuk, @Nama)
select * from Peg
END
exec SPPeg '01/01/2008', 'Amir'
Thursday, January 13, 2011
Subscribe to:
Posts (Atom)