VIEW DAN TRIGGER
View adalah
query tersimpan yang menghasilkan result set ketika dipanggil. View bertindak
sebagai
tabel virtual.
1.
Pembuatan
view
CREATE [OR REPLACE] [<algorithm attributes>] VIEW [database.]<
name> [(<columns>)] AS <SELECT statement> [WITH <LOCAL | CASCADE> <check options>]
mysql>
CREATE VIEW view1 AS
-> SELECT CONCAT(first_name, "
", last_name), city FROM employee;
mysql>
SELECT * FROM view1;
mysql>
CREATE VIEW view2 (name, place) AS
->
SELECT CONCAT(first_name, " ", last_name), city FROM employee;
mysql>
SELECT * FROM view2;
2.
Updatable
View
mysql> UPDATE view2 SET
place = "Canberra "
WHERE place =
"Toronto ";
mysql> select * from
employee where city = 'Canberra ';
mysql> UPDATE view2 SET
place = "Toronto "
WHERE place =
"Canberra ";
3.
Klausa
WITH CHECK OPTION
mysql> CREATE VIEW v1 AS
-> SELECT first_name, salary, city
FROM employee WHERE salary <
3000
-> WITH CHECK OPTION;
mysql> CREATE VIEW v2 AS
SELECT * FROM v1 WHERE salary > 0
WITH LOCAL CHECK OPTION;
mysql> CREATE VIEW v3 AS
SELECT * FROM v1 WHERE salary > 0
WITH CASCADED CHECK OPTION;
mysql> INSERT INTO v2
VALUES ("Doe", 3500, "Indonesia ");
4.
Merubah
View
mysql> ALTER VIEW view1
(name, city) AS
-> SELECT CONCAT(first_name, "
", last_name), city FROM
employee;
5.
Melihat
definisi pembuatan view
mysql> SHOW CREATE VIEW
view1\G
6.
Menghapus
View
DROP VIEW view1;
TRIGGER
Trigger
adalah sebuah objek database yang diasosiasikan dengan sebuah tabel. Trigger
diaktifkan ketika sebuah event terjadi pada tabel yang diasosiasikan. Tabel
yang diasosiasikan dengan trigger haruslah sebuah tabel yang permanen dan bukan
temporary tabel.
1.
Membuat
trigger
CREATE TRIGGER <trigger_name> <trigger_time>
<trigger_event>
ON <table> FOR EACH ROW <trigger_body statements>
2.
Before
Trigger
mysql>
DELIMITER //
mysql>
CREATE TRIGGER before_insert BEFORE INSERT ON employee
->
FOR EACH ROW
->
BEGIN
->
IF NEW.salary IS NULL OR NEW.salary = 0 THEN
->
SET NEW.salary = 1000;
->
ELSE
->
SET NEW.salary = NEW.salary + 100;
->
END IF;
-> END //
mysql>
DELIMITER ;
mysql>
insert into employee(id,first_name, last_name, start_date, end_Date,
salary,
City, Description)
-> values (1,'John', 'Doe',
'19960725', '20060725', 0, 'Canberra ',
'Programmer');
mysql>
DELIMITER &&
mysql>
CREATE TRIGGER before_update BEFORE UPDATE
ON
employee
->
FOR EACH ROW
->
BEGIN
->
UPDATE employee SET salary = salary+(NEW.salary - OLD.salary)
->
WHERE id = NEW.id;
-> END &&
mysql> DELIMITER ;
3.
After
Trigger
mysql>
CREATE TABLE trans_log(
->
user_id VARCHAR(15),
->
description VARCHAR(50)
-> );
mysql>
DELIMITER $$
mysql>
CREATE TRIGGER log_salary AFTER UPDATE
-> ON employee
-> FOR EACH ROW
-> BEGIN
-> INSERT INTO trans_log
->
VALUES (user(), CONCAT('merubah akun ',NEW.id,' dari ',OLD.salary, ' to
',NEW.salary));
-> END $$
mysql> DELIMITER ;
mysql> UPDATE employee SET salary =
salary + 1000;
mysql> SELECT * FROM trans_log;
4.
Melihat
trigger yang sudah dibuat
mysql> SHOW TRIGGERS IN PRAK2\G
5.
Menghapus
Trigger
mysql> DROP TRIGGER before_insert;
Screenshot / lanjutan ada di :
MODUL 5
FUNCTION DAN PROCEDURE
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.
Function
Sebuah
function dapat digunakan secara langsung dalam statement SELECT, UPDATE, dan
DELETE. Hasil dari function dapat dikembalikan sebagai output. Sebuah function
hanya dapat mengembalikan sebuah nilai saja.
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;
2.
Melihat
function yang telah dibuat
mysql> SHOW FUNCTION STATUS;
3.
Menghapus
function
mysql> DROP FUNCTION full_name;
Procedure
Procedure
dapat berisi statement SQL (INSERT, UPDATE, DELETE, SELECT) atau operasi lain
yang disimpan dalam database. Sebuah procedure dapat dipanggil menggunakan
statement CALL nama_procedure disertai parameter yang diperlukan.
1.
Membuat
procedure
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
routine_body
mysql>
DELIMITER //
mysql>
CREATE PROCEDURE show_employees()
->
BEGIN
->
SELECT * FROM employee;
-> END //
mysql>
DELIMITER ;
mysql> CALL show_employees();
2. Parameter dalam procedure
proc_parameter:
[ IN | OUT | INOUT ]
param_name type
Parameter IN
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 ");
Parameter OUT
mysql> DELIMITER :)
mysql> CREATE PROCEDURE getNumEmployee (OUT numEmployee
INT)
-> BEGIN
-> SELECT COUNT(*) INTO numEmployee FROM employee;
->
END :)
mysql> DELIMITER ;
mysql> CALL getNumEmployee(@num);
mysql> SELECT @num;
Parameter INOUT
mysql> DELIMITER ^^
mysql> CREATE PROCEDURE increase(INOUT number INT)
-> BEGIN
-> SET number = number + 15;
->
END ^^
mysql> DELIMITER ;
mysql> SET @num = 100;
mysql> CALL increase(@num);
mysql> SELECT @num;
3.
Melihat
procedure yang telah dibuat
mysql> SHOW PROCEDURE STATUS;
4.
Menghapus
procedure
mysql> DROP PROCEDURE
increaseSalary;
Pemrograman di Function dan Procedure
Di
dalam function dan procedure, kita bisa memasukkan logika pemrograman. Ada beberapa karakteristik
pemrograman yang didukung oleh MySQL. Beberapa di antaranya adalah penggunaan variabel,
kendali kondisional, dan perulangan.
1.
Variabel
DECLARE var_name [, var_name] ...
type [DEFAULT
value]
DECLARE total_sale INT
DECLARE x, y INT DEFAULT 0
SET total_sale = 50;
SELECT COUNT(*) INTO numEmployee FROM employee;
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();
2.
Kendali Kondisional
Kendali IF
IF search_condition THEN statement_list
[ELSEIF
search_condition
THEN statement_list] …
[ELSE
statement_list]
END IF;
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;
Kendali CASE
CASE case_value
WHEN when_value THEN statement_list
[WHEN
when_value THEN statement_list] ...
[ELSE
statement_list]
END CASE
mysql>
DELIMITER ##
mysql>
CREATE FUNCTION calcTax(job VARCHAR (20)))
-> RETURNS FLOAT(3,2)
-> BEGIN
-> DECLARE tax FLOAT(3,2) DEFAULT 0.05;
-> CASE job
-> WHEN 'Manager' THEN SET tax = 0.1;
-> WHEN 'Programmer' THEN set tax = 0.07;
-> WHEN 'Tester' THEN set tax = 0.06;
-> ELSE SET tax = 0.05;
-> END CASE;
-> RETURN tax;
-> END ##
mysql>
delimiter ;
mysql> SELECT first_name, last_name,
calcTax(description) FROM
employee;
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END 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;
3.
Perulangan
Perulangan WHILE
WHILE search_condition DO
statement_list
END 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> CALL mod12(10);
mysql> CALL mod12(24);
Perulangan REPEAT … UNTIL
REPEAT
statement_list
UNTIL search_condition
END REPEAT
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);
Perulangan LOOP
[begin_label:] LOOP
statement_list
END LOOP [end_label]
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);
Tidak ada komentar:
Posting Komentar