Tugas 3 (Membuat contoh-contoh Stored Procedures, Funtions, Trigger)
NIM/NAMA : 10410100239 / ABDURRAHMAN FATTAH
Dosen
:
Tan Amelia
Tugas
: Tugas 3 Membuat contoh-contoh dan pembahasannya
Tugas M3 adalah membuat contoh2x beserta pembahasannya tentang :
1. Stored Procedures (5 contoh, termasuk penggunaan parameter IN, OUT dan INOUT)
2. Functions (5 contoh, termasuk penggunaan parameter IN, OUT dan INOUT)
3. Trigger (5 contoh, trigger Insert, Delete, Update)
1. Stored Procedure
Dengan mengunakan parameter "IN"
Contoh sintaksnya:
CREATE PROC SCountry
@Cityname varchar(15)
AS SELECT SupplierID, CompanyName, ContactName, City, Country, Phone
FROM Suppliers
WHERE Country like @Cityname
EXEC SCountry 'USA'
Dengan menggunakan parameter "OUT"
Contoh sintaksnya:
CREATE PROC CountOrderByStatus
(@total INT OUTPUT )
AS
SELECT ShipName,CustomerID,UnitPrice,Quantity , count(OrderID)
as SumOfProduct
FROM Invoices
WHERE Quantity like @total
group by ShipName,CustomerID,UnitPrice,Quantity
EXEC CountOrderByStatus '24'
Cara update data mengunakan stored prosedure
Contoh sintaksnya:
CREATE PROC update_employee
@EmployeeID varchar (10),
@city varchar (15)
AS
UPDATE Employees
SET City=@city
where EmployeeID=@EmployeeID
EXEC update_employee '9','Surabaya'
2. Function
Contoh dengan Database Northwind
Dengan mengunakan parameter "IN"
Contoh sintaksnya:
CREATE PROC SCountry
@Cityname varchar(15) AS SELECT SupplierID, CompanyName, ContactName, City, Country, Phone
FROM Suppliers
WHERE Country like @Cityname
EXEC SCountry 'USA'
Dengan menggunakan parameter "OUT"
Contoh sintaksnya:
CREATE PROC CountOrderByStatus (@total INT OUTPUT ) AS
SELECT ShipName,CustomerID,UnitPrice,Quantity , count(OrderID)
as SumOfProduct
FROM Invoices
WHERE Quantity like @total
group by ShipName,CustomerID,UnitPrice,Quantity
EXEC CountOrderByStatus '24'
Cara update data mengunakan stored prosedure
Contoh sintaksnya:
CREATE PROC update_employee
@EmployeeID varchar (10),
@city varchar (15)
AS
UPDATE Employees
SET City=@city
where EmployeeID=@EmployeeID
EXEC update_employee '9','Banyuangi'
Contoh penggunaan function
CREATE FUNCTION firstName
(@fname char (20))
returns char
begin
return @fname
end
select FirstName, LastName,
dbo.FirstName (FirstName) from Employees
1. Stored Procedures
· Contoh dengan parameter “IN”
- Misal dibuat Stored Procedure untuk menampilkan data pegawai untuk jabatan tertentu saja, sehingga saat dipanggil deperlukan parameter berupa kode jabatan yang akan ditampilkan
CREATE PROCEDURE sp_peg_jabatan (kdjabat char (6))
select nip,namapeg,p.kodejabat,namajabat
from pegawai p left join jabatan j
on j.kodejabat = p.kodejabat
WHERE p.kodejabat = kd.jabat;
Untukk memanggil :
Call sp_peg_jabatan(‘02’);
- Parameter berupa kode jabatan berupa nilai 02
· Contoh dengan parameter “OUT”
- Misal dibuat Stored Procedure untk mengetahui jumlah pegawai
- Diperlukan parameter OUT untuk menampung hasil perhitungan jumlah pegawai
CREATE PROCEDURE sp_jum_peg (OUT jum int)
SELECT count(*) INTO jum FROM pegawai;
QUERY OK, 0 rows affected (0.00 sec)
call sp_jum_peg(@n);
selcet @n
1 row in set (0.08 sec)
· Contoh dengan parameter “INOUT”
- Misal dibuat Stored Procedure untuk merubah tampilan No Telepon, dibutuhkan parameter masukan notelp yang sekaligus akan digunakan sebagai hasil keluaran
CREATE PROCEDURE
sp_telpon (INOUT notelp varchar (20))
SELECT LONCAT(“(“,left(notelp,3),”)”,
substring(notelp,4,3),”-”,substring(notelp,9))
INTO notelp;
SET @tlp = ‘021234569’;
call sp_telpon(@tlp);
select @tlp;
· Contoh dengan parameter “IN dan OUT”
- Misal akan dibuat Stored Procedure untuk mengetahui Jumlah Pegawai untuk jabatan tertentu, maka dibutuhkan parameter yang dikirimkan berupa kode jabatan (kdjabat) dan juga parameter untuk menampung hasil perhitungan (jum)
CREATE PROCEDUREsp_jum_peg_jabat(IN kdjabat char(2), OUT jum int)
SELECT count(*) INTO jum FROM pegawai
WHERE kodejabat = kdjabat;
call sp_jum_peg_jabat(’02’,@n);
select @n;
2. Function
Contoh penggunaan function
Contoh 1:
CREATE FUNCTION firstName
(@fname char (20))
returns char
begin
return @fname
end
select FirstName, LastName,
dbo.FirstName (FirstName) from Employees
Contoh 2:
CREATE FUNCTION AdditionOK(@Number1 Decimal(3,7))
RETURNS Decimal(3,7)
BEGIN
RETURN @Number1 + 1450
END
Contoh 3:
CREATE FUNCTION AdditionTWO(@Number1 Decimal(3,7),
@Number2 Decimal(3,7))
RETURNS Decimal(3,7)
BEGIN
DECLARE @Result Decimal(3,7)
SET @Result = @Number1 + @Number2
RETURN @Result
END
3. Trigger
Contoh Insert
Tabel “Persons”
P_Id | LastName | FirstName | Address | City |
1 | Utami | Melita | Jl. Cendana 58 | Jakarta Pusat |
2 | Bujangga | Rizky | Jl. Sentosa 09 | Bandung |
3 | Hantomo | Riko | Jl. Cindrawasih 67 | Surabaya |
Sekarang kita ingin menambahkan baris baru dalam tabel "Orang".
Kita gunakan SQL berikut:
INSERT INTO Persons
VALUES (4,'Agustinus', 'Tri', 'Jl.Demak 56', 'Pandaan')
P_Id | LastName | FirstName | Address | City |
1 | Utami | Amelia | Jl. Cendana 58 | Jakarta Pusat |
2 | Bujangga | Rizky | Jl. Sentosa 09 | Bandung |
3 | Hantomo | Riko | Jl. Cindrawasih 67 | Surabaya |
4 | Agustinus | Tri | Jl.Demak 56 | Pandaan |
Contoh 1. Update
Tabel “matakuliah”
Kd_mk* | Nm_mk | Sks |
4101001 | Sistem Operasi | 2 |
4101002 | Bahasa Pemrograman | 3 |
4101003 | Kewirausahaan | 3 |
4101004 | Algoritma | 2 |
Misalkan saja, kita ingin mengubah nama mata kuliah algoritma menjadi algoritma pemrograman dan jumlah SKS mata kuliah Algoritma untuk tahun ini adalah 3. Sekarang kita harus meng-update-nya dari algoritma menjadi Logika Algoritma dan dari 2 menjadi 3.
UPDATE matakuliah SET Nm_mk = 'Logika Algoritma ', Sks = '3'
WHERE Kd_mk = '4101004'
Maka akan berubah sepert itabel dibawah
Kd_mk* | Nm_mk | Sks |
4101001 | Sistem Operasi | 2 |
4101002 | Bahasa Pemrograman | 3 |
4101003 | Kewirausahaan | 3 |
4101004 | Logika Algoritma | 3 |
Contoh 2. Update
Tabel”pegawai”
Kd_pg* | Nm_pegawai | No_tlp |
001 | Ali | 0812345675 |
002 | Rizki | 031347689 |
003 | Mustofa | 0897654328 |
004 | Mubarok | 08564327890 |
Misalkan saja, kita ingin mengubah nama no tlp pegawai Kd_pg 003 dengan no tlp yang baru 08983814408.
UPDATE matakuliah SET No_tlp = ‘08983814408'
WHERE Kd_mk = '003'
Maka no tlp pada Kd-pg 003 akan terupdate dengan no tlp yang baru
Kd_pg* | Nm_pegawai | No_tlp |
001 | Ali | 0812345675 |
002 | Rizki | 031347689 |
003 | Mustofa | 08983814408 |
004 | Mubarok | 08564327890 |
Contoh 1. Delete
Tabel “matakuliah”
Kd_mk* | Nm_mk | Sks |
4101001 | Sistem Operasi | 2 |
4101002 | Bahasa Pemrograman | 3 |
4101003 | Kewirausahaan | 3 |
4101004 | Algoritma | 2 |
Misalkan kita ingin menghapus baris nomor tiga, yaitu Sistem Database. Nah, perintah untuk menghapus baris tersebut adalah sebagai berikut:
DELETE FROM matakuliah WHERE Kd_mk = '4101003'
Hasil;
Tabel “matakuliah”
Kd_mk* | Nm_mk | Sks |
4101001 | Sistem Operasi | 2 |
4101002 | Bahasa Pemrograman | 3 |
4101004 | Algoritma | 2 |
Hati-hatilah ketika menggunakan perintah DELETE. Jika kalian menggunakan perintah DELETE tanpa mengisi perintah WHERE, itu artinya kalian akan menghapus semua data dalam tabel, atau sama artinya kalian mengosongkan tabel!
Contoh 2. Delete
Tabel “persons”
P_Id | LastName | FirstName | Address | City |
1 | Utami | Melita | Jl. Cendana 58 | Jakarta Pusat |
2 | Bujangga | Rizky | Jl. Sentosa 09 | Bandung |
3 | Hantomo | Riko | Jl. Cindrawasih 67 | Surabaya |
Misalkan kita ingin menghapus baris nomor tiga. Nah, perintah untuk menghapus baris tersebut adalah sebagai berikut:
DELETE FROM persons WHERE P_Id = '3'
Hasil Delete no 3
P_Id | LastName | FirstName | Address | City |
1 | Utami | Melita | Jl. Cendana 58 | Jakarta Pusat |
2 | Bujangga | Rizky | Jl. Sentosa 09 | Bandung |