Store Procedure Database SQL

1. buat store procedure untuk menampilkan name (gabungan dari firstname, lastname), email, city dari karyawan(employee)

prosedur memiliki parameter untuk penyaringan berdasarkan

kota(city)

Jawab :

DELIMITER /

CREATE PROCEDURE karyawan(IN city varchar(50))

BEGIN

SELECT CONCAT(firstName,' ',lastName) as Name, email, city

from employees, offices WHERE city=('Boston');

END

/

Memanggil PROCEDURE karyawan :

CALL karyawan('Boston');

/

2. buat store procedure untuk menghapus data produk berdasarkan productCode

Jawab:

CREATE PROCEDURE produk(IN productCode_param varchar(15))

BEGIN

DELETE FROM products WHERE productCode = productCode_param;

END;

/

Memanggil PROCEDURE produk :

CALL produk('S72_3212')

/

3. buat store procedure untuk memasukkan data offices

Jawab :

CREATE PROCEDURE tambahData(IN officeCode varchar(10),city varchar(50),phone varchar(50),addressLine1 varchar(50),addressLine2 varchar(50),state varchar(50),country varchar(50),postalCode varchar(15),territory varchar(10))

BEGIN

INSERT INTO offices values(officeCode,city,phone,addressline1,addressline2,state,country,postalCode,territory);

END;

/

Memanggil tambahData :

CALL tambahData(9,'bantul',2483,'jogja','sleman','jawa','ina',234,'dunia');

/

4.buat store procedure untuk mencari jumlah pembayaran terkecil, terbesar dan rata2 (payments)

Jawab :

CREATE PROCEDURE pembayaran(IN customerNumber int(11))

BEGIN

SELECT MIN(amount)AS Pembayaran_Terkecil,MAX(amount)AS Pembayaran_Terbesar,AVG(amount)AS Pembayaran_Rata_Rata from payments;

END

/

Memanggil pembayaran :

CALL pembayaran('');

/

Mode 2

Nomor 1

DELIMITER //
CREATE PROCEDURE nama()
BEGIN
SELECT CONCAT(firstName,' ',lastName) "Nama",email,city
FROM employees INNER JOIN offices USING (officeCode) ORDER BY (city);
END //
DELIMITER ;

CALL nama();

Nomor 2

DELIMITER //
CREATE PROCEDURE hapusproduk(IN Code VARCHAR(15))
BEGIN
DELETE FROM products WHERE productCode=Code;
END //
DELIMITER ;

CALL hapusproduk('S10_1678');

Nomor 3

DELIMITER //
CREATE PROCEDURE inputOffices(IN inputOfficeCode VARCHAR(15),
inputCity VARCHAR(50),
inputPhone varchar(50),
inputAddressLine1 varchar(50),
inputAddressLine2 varchar(50),
inputState varchar(50),
inputCountry varchar(50),
inputPostalCode varchar(15),
inputTerritory varchar(10)
)
BEGIN
INSERT INTO `offices`(`officeCode`,`city`,`phone`,`addressLine1`,`addressLine2`,`state`,`country`,`postalCode`,`territory`)
VALUES (inputOfficeCode,inputCity,inputPhone,inputAddressLine1,inputAddressLine2,inputState,inputCountry,inputPostalCode,inputTerritory);
END //
DELIMITER ;

CALL inputOffices('x','xx','xxx','xxxx','xxxxx','xxxxxx','xxxxxx','xxxxxxx','xxxxxxxx');

4. buat store procedure untuk mencari jumlah pembayaran terkecil, terbesar
dan rata-rata (payments)

mysql> DELIMITER //
mysql> CREATE PROCEDURE GetPayment()
-> BEGIN
-> SELECT MAX(amount) AS Terbesar, MIN(amount) AS Terkecil, AVG(amount) AS Rata FROM payments;
-> END //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL GetPayment();
-> //
+-----------+----------+-----------------+
| Terbesar | Terkecil | Rata |
+-----------+----------+-----------------+
| 120166.58 | 615.45 | 32431.645531136 |
+-----------+----------+-----------------+
1 row in set (0.00 sec)