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
Tidak ada komentar:
Posting Komentar