TUGAS Pemograman Basis Data

NIM / NAMA  : 10.41010.0259 / Candra Kurniawan
Dosen : Tan Amelia
Tugas : Contoh; Stored Procedure, Function dan Trigger

1. Stored Procedure

• Dengan Parameter “IN”

CREATE PROCEDURE CostumersCity

@Countryname varchar (10)

AS SELECT CustomerID , CompanyName, ContactName, City, Country

FROM Customers

WHERE Country like @Countryname

EXEC CostumersCity 'UK'

• Dengan Parameter “OUT”

ALTER PROCEDURE CountOrderByStatus

(@total nvarchar(225) OUTPUT)

AS SELECT CategoryName, ProductName, ProductSales, ShippedQuarter

FROM [Product Sales for 1997]

WHERE ShippedQuarter like @total

EXEC CountOrderByStatus 'Qtr 1'

• Dengan Parameter “2 (Dua) OUT”

ALTER PROCEDURE Sales_from_Orders

@total_sales varchar (15) OUTPUT

AS

SELECT OrderID, EmployeeID, SUM (OrderID) as SumOfID

FROM Customers a, Orders b

WHERE a.CustomerID like @total_sales and a.CustomerID = b.CustomerID

group by OrderID, EmployeeID

exec sales_from_orders 'alfki'

DECLARE @total_sales_business int, @avg_sales_business int

EXEC Sales_from_orders @total_sales = @total_sales_business OUTPUT,

@avg_sales = @avg_sales_business OUTPUT

• Dengan Parameter “IN dan OUT”

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

EXEC Coba_product 'UK', '10'

• Dengan Parameter “INOUT”

CREATE PROC ProductSum

@max varchar(25) OUT

as select ProductName,SupplierID ,AVG (ProductID) as Average

from [Products]

where SupplierID like @max

group by ProductName, SupplierID

EXEC ProductSum '1'

2. Function

• Dengan menggunakan Parameter “IN”

Contoh 1:

CREATE FUNCTION lincrement (@l integer)

RETURNS integer

BEGIN

while @l > 100

begin

set @l=@l + 10

end

return @l

END

Contoh 2:

CREATE FUNCTION name (@uang money , @grade char (4))

returns char

begin

if @uang > 60000

begin

set @grade = 'B'

end

else

set @grade = 'C'

return @grade

end

select CategoryName, CategorySales, dbo.name (CategorySales, 'A') as CategoryGrade

from [Northwind].[dbo].[Category Sales for 1997]

3. Trigger

• Delete merupakan suatu trigger yang akan di jalankan ketika sebuah tabel yg di sertai oleh trigger tersebut di kenal operasi delete.

Contoh;

create trigger Delete1

on [Current Product List]

For Delete

As

declare

@IDE int

set @IDE = (select ProductID from [Current Product List])

begin

Delete from [Current Product List] where ProductID = @IDE

End

• Update merupakan suat tirgger yang akan di jalankan ketika sebuah tabel yang disertai oleh trigger tersebut dikenai operasi update.

Contoh;

CREATE TRIGGER For_Update ON dbo.Products

FOR UPDATE

AS

DECLARE

@ID int, @jumlah smallint

set @ID = (select ProductID FROM Products)

set @jumlah = (SELECT UnitsInStock FROM [Products by Category])

BEGIN

UPDATE Products SET UnitsInStock = (@jumlah) WHERE ProductID = @ID

END

• Insert merupakan suatu trigger yang akan di jalankan sebuah tabel yg di sertai oleh trigger tersebut dikenai operasi insert
  CREATE TRIGGER Ins_1 ON dbo.[Products by Category]
  FOR INSERT
  AS
  DECLARE @name nvarchar (40), @jumlah smallint
  set @name =  (SELECT ProductName FROM [Products by Category])
  set @jumlah = (SELECT UnitsInStock FROM [Products by Category])
  BEGIN
  UPDATE  Products  SET Products.UnitsInStock = (Products.UnitsInStock-  @jumlah) FROM INSERTED WHERE Products.ProductName = @name
  END

  

Posted in: