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








|
This entry was posted on 07.22 and is filed under . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

0 komentar: