III.5
Intro Stored Routine
Stored
routine (function dan procedure) merupakan sekumpulan statement SQL yang dapat
disimpan
dalam server.
Setelah routine disimpan, client tidak perlu memanggil statement individual
terus
menerus,
namun cukup dengan memanggil stored routine.
1. 1.
Membuat function
mysql>DELIMITER //
mysql>CREATE FUNCTION full_name(
in_first_name VARCHAR(15), in_last_name VARCHAR(15))
RETURNS VARCHAR(35)
BEGIN
RETURN CONCAT(in_first_name,'
',in_last_name);
END//
mysql>DELIMITER ;
mysql>SELECT full_name(first_name,
last_name) FROM employee;
1. 2.
Melihat function yang
telah dibuat kemudian menghapus functionnya
mysql>SHOW FUNCTION STATUS;
mysql>DROP FUNCTION full_name;
mysql>SHOW FUNCTION STATUS;
1.
3. Membuat procedure
mysql>DELIMITER //
mysql>CREATE PROCEDURE show_employees()
BEGIN
SELECT * FROM employee;
END //
mysql>DELIMITER ;
mysql>CALL show_employees();
1.
4. Parameter dalam
procedure
1.
Parameter IN
1
mysql>DELIMITER $$
mysql>CREATE PROCEDURE getEmployeeByCity
(IN cityName VARCHAR(255))
BEGIN
SELECT * FROM employee WHERE city LIKE
cityName;
END $$
mysql>DELIMITER ;
mysql>CALL getEmployeeByCity("Vancouver");
1.
5. Parameter OUT dan
Parameter INOUT
mysql>DELIMITER :)
mysql>CREATE PROCEDURE getNumEmployee
(OUT numEmployee INT)
BEGIN
SELECT COUNT(*) INTO numEmployee FROM
employee;
END :)
1.
6. Melihat procedure yang
telah dibuat kemudian menghapusnya
mysql>SHOW PROCEDURE STATUS;
mysql>DROP PROCEDURE increaseSalary;
Variabel
mysql>DELIMITER ^_^
mysql>CREATE FUNCTION addTax(salary
FLOAT(8,2))
RETURNS FLOAT (8,2)
BEGIN
DECLARE tax FLOAT DEFAULT 0.05;
RETURN salary * (1 - tax);
END ^_^
mysql>DELIMITER ;
mysql>SELECT first_name, addTax(salary)
FROM employee;
mysql>DELIMITER **
mysql>CREATE PROCEDURE checkScope()
BEGIN
DECLARE first_name VARCHAR(15) DEFAULT
'bob';
SELECT id, first_name FROM employee WHERE
first_name = first_name;
END **
mysql>DELIMITER ;
mysql>CALL checkScope();
1.
8. Kendali Kondisional
If dan Else pada Trigger
mysql>DELIMITER &&
mysql>CREATE FUNCTION hideSalary(salary
FLOAT(8,2))
RETURNS VARCHAR(20)
BEGIN
DECLARE sal VARCHAR(20);
IF salary < 4000 THEN SET sal = 'Low
Salary';
ELSE SET sal = 'High Salary';
END IF;
RETURN sal;
END &&
mysql>DELIMITER ;
mysql>SELECT first_name, last_name,
hideSalary(salary) FROM employee;
1.
9. Kendali CASE
mysql>DELIMITER >>
mysql>CREATE FUNCTION calcTax2(job
VARCHAR(20))
RETURNS FLOAT(3,2)
BEGIN
DECLARE tax FLOAT(3,2);
CASE
WHEN job = 'Manager' THEN SET tax = 0.1;
WHEN job = 'Programmer' THEN SET tax =
0.07;
WHEN job = 'Tester' THEN SET tax = 0.06;
ELSE SET tax = 0.05;
END CASE;
RETURN tax;
END >>
mysql>DELIMITER ;
mysql>SELECT first_name, last_name,
calcTax2(description) FROM employee;
1.
10. Perulangan
Perulangan WHILE
mysql>DELIMITER //
mysql>CREATE PROCEDURE mod12(IN number
INT(10))
BEGIN
WHILE number MOD 12 > 0 DO
SET number = number + 1;
END WHILE;
SELECT number;
END //
mysql>DELIMITER ;
mysql>CALL mod12(10);
mysql>CALL mod12(24);
1.
11. Perulangan REPEAT …
UNTIL
mysql>DELIMITER /.
mysql>CREATE PROCEDURE repeatDemo(IN
number INT(10))
BEGIN
REPEAT
SET number = number +1;
UNTIL number MOD 12 = 0
END REPEAT;
SELECT number;
END /.
mysql>DELIMITER ;
mysql>CALL repeatDemo(10);
1.
12. Perulangan LOOP
mysql>DELIMITER /?
mysql>CREATE PROCEDURE
iterateDemo(number INT)
BEGIN
label1: LOOP
SET number = number + 1;
IF number MOD 12 > 0 THEN
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
SELECT number;
END /?
mysql>DELIMITER
mysql>CALL iterateDemo(10);
mysql>CALL iterateDemo(20);
BAB IV
PENUTUP
Daftar Pustaka
Sumber dari modul tugas ini adalah dari
elearning ist-akprind Yogyakarta dengan source
SMBD dan dengan modul 1 dan 2 MySQL
0 komentar: