VIEW, STORED PROCEDURE,
FUNCTION AND TRIGGER
Lê Hồng Hải
UET-VNUH
Today’s Overview
1 • View
2 • Stored Procedure,
Function
3 • Trigger
2
View Definition
A view is a named query stored in the
database catalog
3
Advantages of Views
Simplify complex query
◼ You can create a view and reference to the
view by using a simple SELECT statement
instead of typing the query all over again
Add extra security layers
◼ A table may expose a lot of data including
sensitive data such as personal and banking
information
◼ By using views and privileges, you can limit
which data users can access by exposing only
the necessary data to them
4
View in MySQL & PostgreSQL
5
Create a View
CREATE VIEW staff_list
AS
SELECT *
FROM staff AS s JOIN address AS a ON s.address_id =
a.address_id JOIN city ON a.city_id = city.city_id
JOIN country ON city.country_id =
country.country_id;
6
UsingView
SELECT first_name, last_name, address
FROM staff_list
7
Today’s Overview
1 • View
2 • Stored Procedure,
Function
3 • Trigger
8
Stored procedure
When you use MySQL Workbench or mysql
shell to send the SQL query to MySQL
Server
If you want to save this query on the
database server for execution later, one
way to do it is to use a stored procedure
9
Stored Procedure Example
CREATE PROCEDURE film_in_stock(IN p_film_id INT, IN
p_store_id INT, OUT p_film_count INT)
READS SQL DATA
BEGIN
SELECT inventory_id
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND inventory_in_stock(inventory_id);
SELECT FOUND_ROWS() INTO p_film_count;
END
10
Stored Procedure Example
CREATE FUNCTION inventory_in_stock(p_inventory_id INT) RETURNS BOOLEAN
READS SQL DATA
BEGIN
DECLARE v_rentals INT;
DECLARE v_out INT;
SELECT COUNT(*) INTO v_rentals
FROM rental
WHERE inventory_id = p_inventory_id;
IF v_rentals = 0 THEN
RETURN TRUE;
END IF;
SELECT COUNT(rental_id) INTO v_out
FROM inventory LEFT JOIN rental USING(inventory_id)
WHERE inventory.inventory_id = p_inventory_id
AND rental.return_date IS NULL;
IF v_out > 0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
11
END $$
Stored Procedure
Set value for variable
◼ Using SET or SELECT INTO.
Call SP:
◼ Call film_in_stock(1,1, @film_count);
◼ Select @film_count;
12
Stored Procedure
A stored procedure can have parameters
so you can pass values to it and get the
result back
Also, a stored procedure may contain
control flow statements such as IF, CASE,
and LOOP
A stored procedure can call other stored
procedures or stored functions, which
allows you to organize your code more
effectively
13
Stored Procedures Advantages
Reduce network traffic
◼ Applications have to send only the name and parameters
of stored procedures.
Centralize business logic in the database
◼ You can use the stored procedures to implement business
logic that is reusable by multiple applications
Make the database more secure
◼ The database administrator can grant appropriate
privileges to applications that only access specific stored
procedures without giving any privileges to the underlying
tables.
14
Stored Procedures Disadvantages
Lack of Portability
SQLServer uses T-SQL
Oracle uses PL-SQL
Developing and maintaining stored
procedures often requires a specialized
skill
15
SP Syntax in MySQL
BEGIN
◼ DECLARE variables;
◼ DECLARE cursors;
◼ DECLARE conditions;
◼ DECLARE handler;
◼ other SQL commands;
END;
16
IF THENclause
IF condition THEN
commands;
[ELSE IF condition THEN
commands;]
[ELSE commands;]
END IF;
17
CASEclause
CASE expression
WHEN value1 THEN commands;
[WHEN value2 THEN commands;]
[ELSE commands;]
END CASE;
18
REPEAT UNTILclause
[loopname:]
REPEAT commands;
UNTIL condition
END REPEAT [loopname];
19
WHILEclause
[loopname:]
WHILE condition DO commands;
END WHILE [loopname];
20
Cursor
The cursor is used to iterate through the
rows of results returned by the query and
process each row individually
21
Cursor Syntax
DECLARE cursor_name CURSOR FOR
SELECT_statement;
OPEN cursor_name;
Extract each record and move to the next
record using the FETCH command
FETCH cursor_name INTO variable list;
CLOSE cursor_name;
22
Today’s Overview
1 • View
2 • Stored Procedure,
Function
3 • Trigger
23
Trigger
a trigger is a stored program invoked
automatically in response to an event such
as insert, update, or delete that occurs in
the associated table
24
Trigger Example
CREATE TRIGGER `upd_film` AFTER UPDATE ON `film`
FOR EACH ROW BEGIN
IF (old.title != new.title) or (old.description !=
new.description)
THEN
UPDATE film_text
SET title=new.title,
description=new.description,
film_id=new.film_id
WHERE film_id=old.film_id;
END IF;
END
25
Trigger
◼ OLD is the row before being updated or
deleted
◼ NEW is the row to insert or update into
the table
26
Advantages of Triggers
Helps us to automate the data alterations
Helps us to detect errors on the database
level
Allows easy auditing of data
27
Disadvantages of Triggers
Triggers can be difficult to troubleshoot
because they execute automatically in the
database
Triggers may increase the overhead of the
MySQL server
28
THANKS YOU
