Tugas 3 (Membuat contoh-contoh Stored Procedures, Funtions, Trigger)

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

@n

5

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;

@tlp

(021) 234-569

·         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;

@n

3

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