PBD - Contoh dari Stored Procedures, Functions dan Trigger pada SQL Server.

NIM/ NAMA : 10410100218/ Achmad Vierdan Habibi
Tugas : Tugas PBD
Dosen : Tan Amelia

1)
Procedure Penghitungan Total Pendapatan
create procedure TotalPendapatan
as
begin
declare @total1 numeric;
declare @total2 numeric;

set @total1 = (select sum(harga) 'Total Desember 2010'
from peminjaman
where tanggalpinjam between '12/1/2010' and '12/31/2010')

set @total2 = (select sum(harga) 'Total Januari 2011'
from peminjaman
where tanggalpinjam between '1/1/2011' and '1/31/2011')

print 'Total Pendapatan Bulan Desember 2010 = ' + convert(varchar, @total1)
print 'Total Pendapatan Bulan Januari 2011 = ' + convert(varchar, @total2)
end

exec TotalPendapatan

2)
-- Procedure Penentuan Mobil Yang Paling Diminati
alter procedure Minat
as
begin
declare @minat numeric;
declare @nomor varchar(10);
declare @nama varchar(10);

select @nomor=nomorpolisi, @minat=count(nomorpolisi)
from peminjaman
group by nomorpolisi
having count(nomorpolisi) = (select max(Hitung)
from (select nomorpolisi,count(nomorpolisi) 'Hitung'
from peminjaman
group by NomorPolisi) Tabel)

set @nama = (select namamobil from mobil where nomorpolisi = @nomor)

print 'Mobil yang paling diminati = ' + @nama + ' sebanyak ' + convert(varchar, @minat)
end

exec Minat

3)

CREATE PROCEDURE SupplierCountry
@Country varchar(15)
AS SELECT SupplierID, CompanyName, Country
FROM [Suppliers]
WHERE Country = @Country

EXEC SupplierCountry 'UK'

4)
ALTER PROC Coba_product
@CountryName VARCHAR(25), @SumOfStock INT OUTPUT
AS
SELECT Suppliers.SupplierID, Suppliers.Country,Products.UnitsInStock,
SUM (ProductID) AS SumOfProducts
FROM Suppliers, Products
WHERE Country like @CountryName and UnitsInStock like@SumOfStock
GROUP BY Suppliers.SupplierID, Country, UnitsInStock

5)

ALTER PROCEDURE QuantityOrder
(@total SMALLINT OUTPUT )
AS
SELECT OrderID,ProductID,UnitPrice,Quantity,count(Quantity)
as SumOfOrderDetails
FROM [Order Details]
WHERE Quantity like @total
group by OrderID,ProductID,UnitPrice, Quantity


EXEC QuantityOrder '15'

Function
1)

SET SERVEROUTPUT ON
DECLARE
X VARCHAR2(20);
BEGIN
X := tulis_teks;
DBMS_OUTPUT.PUT_LINE(X);
END;

2)

SET SERVEROUTPUT ON
DECLARE
H INTEGER;
BEGIN
H := pangkat(2, 3);
DBMS_OUTPUT.PUT_LINE(‘Hasil = ‘ || TO_CHAR(H));
END;

3)

CREATE OR REPLACE FUNCTION kuadrat (X NUMBER)
RETURN NUMBER AS
HASIL NUMBER(10);
BEGIN
HASIL := X * X;
RETURN HASIL;
END;

4)

Trigger

1)

CREATE TRIGGER tr_status ON daftarnilai
FOR INSERT, UPDATE
AS
DECLARE @kode char(4)
DECLARE @nilai float
SELECT @kode = kode, @nilai = nilai FROM daftarNilai
IF @nilai >= 60
UPDATE daftarSiswa SET status = ‘Lulus’ WHERE kode=@kode
ELSE UPDATE daftarSiswa SET status = ‘Tidak Lulus’ WHERE kode=@kode
go