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 : |
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 |
Buat SP Cursor4, hasilnya seperti berikut :
| DEPARTEMEN | JUMLAH |
| IT HRD PPIC | 2 ORANG 2 ORANG 1 ORANG |
| TOTAL | 5 ORANG |
JAWABAN
ALTER PROCEDURE SP_Cursor2
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 @NIK int
DECLARE @NAMA varchar(20)
DECLARE @Number int
SET @Number = 1
PRINT 'NO' + ' |' + 'NIK' + '|' + 'NAMA' + ' |' + 'TTD'
PRINT '=================================='
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
WHILE @@FETCH_STATUS=0
BEGIN
PRINT CONVERT(CHAR(3),@NUMBER) + '|' + CONVERT(CHAR(3),@NIK) + '|' + @NAMA + ' |'
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
SET @Number = @Number + 1
END
PRINT '=================================='
PRINT CONVERT(CHAR(3),' ') + '|' + CONVERT(CHAR(3),' ') + '|' + 'DOSEN : ' + ' |' + 'PARAF : '
--4 Menutup Cursor
CLOSE CsrMsiswa
--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa
END
GO
ALTER PROCEDURE SP_Cursor3
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 @NIK int
DECLARE @NAMA varchar(20)
DECLARE @Number int
SET @Number = 1
PRINT 'NO' + ' |' + 'NIK' + '|' + 'NAMA' + ' |' + '1 |' + '2 |' + '3 |' + '4 |' + '5 |' + 'Rata-rata'
PRINT '===================================================================='
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
WHILE @@FETCH_STATUS=0
BEGIN
PRINT CONVERT(CHAR(3),@NUMBER) + '|' + CONVERT(CHAR(3),@NIK) + '|' + @NAMA + ' |' + ' |' + ' |' + ' |' + ' |' + ' |'
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA
SET @Number = @Number + 1
END
PRINT '===================================================================='
PRINT CONVERT(CHAR(47),' ') + '|'
--4 Menutup Cursor
CLOSE CsrMsiswa
--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa
END
GO
ALTER PROCEDURE SP_Cursor4
AS
BEGIN
--1 Mendeklarasikan Cursor
DECLARE CsrMsiswa CURSOR
FOR Select NIK, NAMA, GAJI, DEPARTMEN from Msiswa Where NIK>=1 and NIK <=5
--2 Membuka Cursor
OPEN CsrMsiswa
--3 Memproses Cursor
DECLARE @NIK int
DECLARE @NAMA varchar(20)
DECLARE @Number int
DECLARE @Gaji money
DECLARE @Departmen varchar(50)
SET @Number = 1
PRINT 'NO' + ' |' + 'NIK' + '|' + 'NAMA' + ' |' + 'GAJI ' + '|' + 'DEPARTMEN '
PRINT '===================================================================='
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA, @GAJI, @Departmen
WHILE @@FETCH_STATUS=0
BEGIN
PRINT CONVERT(CHAR(3),@NUMBER) + '|' + CONVERT(CHAR(3),@NIK) + '|' + @NAMA + ' |' + CONVERT(char(8),@GAJI) + ' |' + CONVERT(char(5),@DEPARTMEN)
FETCH NEXT FROM CsrMsiswa into @NIK, @NAMA,@GAJI, @Departmen
SET @Number = @Number + 1
END
PRINT '===================================================================='
--4 Menutup Cursor
CLOSE CsrMsiswa
--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa
END
GO
ALTER PROCEDURE SP_Cursor5
AS
BEGIN
--1 Mendeklarasikan Cursor
DECLARE CsrMsiswa CURSOR
FOR Select DISTINCT DEPARTMEN, COUNT(Departmen) from Msiswa Where NIK>=1 and NIK <=5
GROUP BY DEPARTMEN
--2 Membuka Cursor
OPEN CsrMsiswa
--3 Memproses Cursor
DECLARE @NIK int
DECLARE @Departmen varchar(50)
DECLARE @Jml int
DECLARE @NOMORLAMA int
DECLARE @NOMORBARU int
DECLARE @Total int
SET @NOMORLAMA = 1
SET @NOMORBARU = 0
SET @Total = 1
PRINT 'DEPARTEMEN' + '|' + 'JUMLAH'
PRINT '=============================='
FETCH NEXT FROM CsrMsiswa into @Departmen, @Jml
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@NomorLAMA <> @NOMORBARU)
PRINT CONVERT(Char(8),@Departmen) + ' |' + CONVERT(Char(4),@Jml) + ' Orang'
FETCH NEXT FROM CsrMsiswa into @Departmen, @Jml
SET @NOMORBARU = @NOMORLAMA
SET @NOMORLAMA = @NOMORLAMA + 1
SET @Total = @Total + @Jml
END
PRINT '=============================='
PRINT 'TOTAL |' + convert(char(4), @Total)+ ' Orang'
--4 Menutup Cursor
CLOSE CsrMsiswa
--5 Mendealokasikan Cursor
DEALLOCATE CsrMsiswa
END
GO
Tidak ada komentar:
Posting Komentar