Nim / Nama : 10410100280 / Mochammad ali akbar rafsanjani
Dosen : Tan amelia
Tugas : Contoh-contoh Stored Procedures, Function dan Trigger
STIKOM SURABAYA
PROCEDURE
- Membuat Procedure Untuk Menginputkan data pada tabel Categories
CREATE PROC InsertCategory @CategoryID int, @CategoryName nvarchar(15), @Description ntext, @picture image, @stock int AS INSERT INTO Categories VALUES (@CategoryID, @CategoryName, @Description, @picture, @stock)
EXEC InsertCategory @CategoryID = 8, @CategoryName = 'Fruit' , @Description = 'banana, apple, orange', @picture='' , @stock = 20
- Membuat Procedure untuk mencari data berdasarkan Id Customer dimana contoh dibawah ini menampilkan yg Id Customer bernama RAVIEAKBAR
CREATE PROC ViewOrder @CustomerID nvarchar(5) AS SELECT Orders.CustomerID, OrderID, CompanyName, OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID WHERE Orders.CustomerID = @CustomerID
EXEC ViewOrder @CustomerID = 'Akbar'
- Menampilkan Jumlah Data Product berdasarkan Category
CREATE PROC JmlProduct @CategoryID int AS SELECT Products.CategoryID, CategoryName, count(Products.CategoryID) AS Jumlah from Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID AND Products.CategoryID = @CategoryID GROUP BY Products.CategoryID, CategoryName
EXEC JmlProduct @CategoryID = 2
- Mengupdate data pada tabel Employee dimana yang di update hanya LastName dan FirstName
CREATE PROC UpdateEmployees @LastName nvarchar(20), @FirstName nvarchar(10), @EmployeeID int AS UPDATE Employees SET LastName = @LastName, FirstName = @FirstName WHERE EmployeeID = @EmployeeID SELECT * FROM Employees WHERE EmployeeID = @EmployeeID
EXEC UpdateEmployees @LastName = 'Ravsanjanie', @FirstName = 'Akbar', @EmployeeID = 3
- Menampilkan Data Supplier yang tinggal di Negara Tertentu
CREATE PROC ViewSupplier @Country nvarchar(15) AS SELECT CompanyName, ContactName, ContactTitle, City FROM Suppliers WHERE Country = @Country
Exec ViewSupplier @Country = 'USA'
FUNCTION
- Membuat Function Untuk Menghitung Total Harga Dari Products berdasrkan Stock dan Price
ALTER FUNCTION total (@UnitPrice money, @UnitInStock smallint) returns numeric AS
begin
declare @total numeric;
set @total = @UnitPrice * @UnitInStock;
return @total;
end;
select ProductID, ProductName,UnitPrice, UnitsInStock, dbo.total (UnitPrice, UnitsInStock)AS total from Products
- Membuat Fundtion Untuk mengetahui apakah Stock pada suatu product Kosong atau tidak
ALTER FUNCTION status (@UnitsInStock smallint) returns varchar(15) AS
BEGIN
DECLARE @status varchar(15);
IF (@UnitsInStock <=0)
SET @status = 'KOSONG';
ELSE
SET @status = 'ADA'
RETURN @status;
END;
select ProductID, ProductName, UnitsInStock, dbo.status UnitsInStock)AS total from Products
- Membuat Suatu Function Untuk Menghitung total dari Suatu Invoice yang berasalkan dari Harga dan Jumlahnya dan dilihat tiap Customer
CREATE FUNCTION TotalInvoice(@UnitPrice money, @Quantity smallint)
returns numeric
AS
BEGIN
DECLARE @total numeric;
SET @total = @UnitPrice * @Quantity
RETURN @total;
END;
SELECT CompanyName, ProductName, Quantity, UnitPrice, dbo.TotalInvoice(UnitPrice, Quantity) AS TOTAL FROM Invoices INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID
- Membuat Function Untuk Menghitung Total dari Invoice serta Memberikan suatu diskon yang mana jika Jumlah >= 20 mendapat diskon 10% serta Jika Jumlah >= 50 mendapat diskon 15% dan untuk Jumlah >=100 mendapat diskon 20%
CREATE FUNCTION TotalInvoice(@UnitPrice money, @Quantity smallint) returns numeric
AS
BEGIN
DECLARE @total numeric;
SET @total = @UnitPrice * @Quantity
RETURN @total;
END;
ALTER FUNCTION DiskonInvoice(@Quantity smallint)returns smallint
AS
BEGIN
DECLARE @diskon smallint;
SET
IF(@Quantity >= 20)
SET @diskon = 10;
ELSE IF(@Quantity >= 50)
SET @diskon = 15;
ELSE IF(@Quantity >= 100)
SET @diskon = 20;
ELSE
SET @diskon = 0;
RETURN @diskon
END;
SELECT CompanyName, ProductName, Quantity, UnitPrice, dbo.TotalInvoice(UnitPrice, Quantity) AS TOTAL, dbo.DiskonInvoice(Quantity) AS Diskon FROM Invoices INNER JOIN Customers ON Invoices.CustomerID = Customers.CustomerID
- Membuat Function Untuk Mengetahui apakah Stock dari ProductByCategory ada atau tidak
CREATE FUNCTION StockProductByCategory (@UnitsInStock smallint)returns varchar(10)
AS
BEGIN
DECLARE @stat varchar(15)
IF(@UnitsInStock >0)
SET @stat = 'ADA';
ELSE
SET @stat = 'KOSONG';
RETURN @stat
END;
SELECT *,dbo.StockProductByCategory(UnitsInStock) AS Status FROM [Products by Category]
TRIGGER
- Trigger Insert dari tabel Categories
CREATE TRIGGER InsertCategories ON Categories FOR INSERT AS INSERT INTO Categories (CategoryID, CategoryName, Description) VALUES(9,'Cake','Tart, Bread')
SELECT * FROM Categories
- Trigger Update dari Tabel Categories
CREATE TRIGGER UpdateCategories ON Categories FOR UPDATE AS
DECLARE @CategoryName nvarchar(15);
DECLARE @CategoryID int;
UPDATE Categories SET CategoryName = @CategoryName WHERE CategoryID = @CategoryID
UPDATE Categories SET CategoryName = 'Buah' WHERE CategoryID = 8
- Trigger Menghapus Data pada tabel Categories
CREATE TRIGGER DropCategories ON Categories FOR DELETE AS DELETE FROM Categories WHERE CategoryID = 1
- Trigger Menghapus Data Dari Tabel Customer
CREATE TRIGGER DropCustomers ON Customers FOR DELETE AS
DECLARE @CustomerID nvarchar(5);
DELETE FROM Customers WHERE CustomerID = @CustomerID
- Trigger Update dari tabel Products
CREATE TRIGGER UpdateProducts ON Products FOR UPDATE AS
DECLARE @ProductName nvarchar(40);
DECLARE @UnitPrice money;
DECLARE @UnitsInStock smallint;
DECLARE @ProductID int
UPDATE Products SET ProductName = @ProductName, UnitPrice = @UnitPrice, UnitsInStock = @UnitsInStock WHERE ProductID = @ProductID
