Nim / Nama : 10.41010.0228 / Septian Dwi Jaya A.P
Dosen : Tan Amelia
Tugas : Contoh Stored Procedure, Function, Trigger
Membuat Stored Procedure
Contoh membuat proc barang dengan menampilkan semua data pada table product di database nortwind :
CREATE PROC BARANG AS
SELECT * FROM Products
Disini kita memanggil data product dimana productId=@kode, dan saat menjalankan kita masukkan paramete @kodenya = 2.
create proc data_brg(@kode int)
as
select * from Products
where ProductID = @kode
exec data_brg '2'
Jika dengan 2 parameter
create proc data_brg2
@cat int, @stock int
as
select * from Products
where CategoryID = @cat where CategoryID = @cat
and UnitsInStock < @stock exec data_brg2 @cat='2', @stock='5' execute data_brg2 '2', '5'
@cat int, @stock int
as
select * from Products
where CategoryID = @cat where CategoryID = @cat
and UnitsInStock < @stock exec data_brg2 @cat='2', @stock='5' execute data_brg2 '2', '5'
Mengindikasikan bahwa parameter berupa return parameter. Nilai parameter dpt diubah dan diberikan nilainya pada program pemanggilnya. nilainya pada program pemanggilnya.
create proc lihat_brg
(@kode varchar(5),
@nama varchar(30) output,
@stock decimal output,
@hrgj decimal output)
AS
select @nama=ProductName,
@stock=UnitsInStock,
@hrgj=UnitPrice
From Products
where ProductID = @kode
(@kode varchar(5),
@nama varchar(30) output,
@stock decimal output,
@hrgj decimal output)
AS
select @nama=ProductName,
@stock=UnitsInStock,
@hrgj=UnitPrice
From Products
where ProductID = @kode
Untuk menjalankan, variabel pada program yg memanggil harus dideklarasikan dan saat dieksekusi diikuti kata output
Declare @nm varchar(50), @jml decimal,
@hargaj decimal
Execute lihat_brg '1', @nm output, @jml
output, @hargaj output
Print 'Nama Brg : ' + @nm
Print 'Stock : ' + str(@jml) Print 'Stock : ' + str(@jml)
Print 'Harga Jual : ' + str(@hargaj)
Print 'Total Harga : ' + str(@jml * @hargaj)
@hargaj decimal
Execute lihat_brg '1', @nm output, @jml
output, @hargaj output
Print 'Nama Brg : ' + @nm
Print 'Stock : ' + str(@jml) Print 'Stock : ' + str(@jml)
Print 'Harga Jual : ' + str(@hargaj)
Print 'Total Harga : ' + str(@jml * @hargaj)
Membuat Functions
SUM untuk agregat dan agregat baris
Contoh ini menunjukkan perbedaan antara fungsi agregat dan fungsi agregat baris. Yang pertama menunjukkan fungsi agregat memberikan data ringkasan saja, dan menunjukkan fungsi agregat baris kedua memberikan detail dan data ringkasan.
- USE pubs
- GO
- -- Aggregate functions
- SELECT type, SUM(price), SUM(advance)
- FROM titles
- WHERE type LIKE '%cook'
- GROUP BY type
- ORDER BY type
- GO
- USE pubs
- GO
- -- Row aggregates
- SELECT type, price, advance
- FROM titles
- WHERE type LIKE '%cook'
- ORDER BY type
- COMPUTE SUM(price), SUM(advance) BY type
Contoh menghitung total kelompok dengan lebih dari satu kolom, menghitung jumlah harga dan advance untuk setiap jenis buku.
- USE pubs
- GO
- SELECT type, SUM(price), SUM(advance)
- FROM titles
- GROUP BY type
- ORDER BY type
- GO
Membuat Trigger
tabel yang ada :
TourID | StartDate | EndDate
T001 5/18/2007 5/13/2007
T001 5/18/2007 5/13/2007
Trigger untuk Insert dan Update :
CREATE TRIGGER Tour_Duration_TG
ON Tour
FOR INSERT,UPDATE
AS
SET NOCOUNT ON;
DECLARE @DURATION AS INT
SELECT @DURATION = DATEDIFF(dd,Start_Date, End_Date) FROM Tour
IF ((@DURATION BETWEEN 3 AND 18) OR (@DURATION=0))
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Tour duration must with in 3 to 18!',16,1)
END
GO
DROP TRIGGER Tour_Duration_TG
INSERT INTO Tour
(Tour_ID, Start_Date, End_Date)
VALUES
('T008', 5/11/2007, 5/16/2007)
ON Tour
FOR INSERT,UPDATE
AS
SET NOCOUNT ON;
DECLARE @DURATION AS INT
SELECT @DURATION = DATEDIFF(dd,Start_Date, End_Date) FROM Tour
IF ((@DURATION BETWEEN 3 AND 18) OR (@DURATION=0))
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Tour duration must with in 3 to 18!',16,1)
END
GO
DROP TRIGGER Tour_Duration_TG
INSERT INTO Tour
(Tour_ID, Start_Date, End_Date)
VALUES
('T008', 5/11/2007, 5/16/2007)
Untuk menghapus trigger
Bentuk umum dan contoh menghapus trigger :
CREATE TRIGGER [trigger_delete] |
02 | ON [dbo].[Table_Siswa] |
03 | for DELETE |
04 | AS |
05 | BEGIN |
06 | SET NOCOUNT ON ; |
07 |
08 | declare @ko_sis as char (7) |
09 | declare @na_sis as varchar (50) |
10 | declare @usr varchar (30) |
11 | declare @tgl datetime |
12 |
13 | set @usr = suser_sname() |
14 | set @tgl = getdate() |
15 | select @ko_sis = kode_siswa, @na_sis = nama_siswa from deleted |
16 | insert into Log_insert_delete values (@ko_sis, @na_sis, 'Delete' , @usr, @tgl) |
17 | END |
Tidak ada komentar:
Posting Komentar