Scribd
Upload
133 views

02-View, Stored Procedure, Function, and Trigger

Uploaded by

21020075
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
133 views

02-View, Stored Procedure, Function, and Trigger

Uploaded by

21020075
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 29

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

You might also like