CSE 480: Database Systems

Lecture 13: Views, Stored Procedures,

Functions, and Triggers

1
Views in SQL

 A view is a “virtual” table that is derived from other tables

 Allows for limited update operations

– Since the table may not physically be stored

 Allows full query operations

2
SQL Views: An Example
 Create a view for Department Managers:

CREATE VIEW MANAGER AS

SELECT FNAME, LNAME, DName, Dnumber, SALARY
FROM EMPLOYEE, DEPARTMENT
WHERE SSN=MGRSSN AND DNO=DNUMBER;

 Find employees who earn more than their managers

SELECT E.FNAME, E.LNAME

FROM EMPLOYEE E, MANAGER M
WHERE E.DNO=M.DNUMBER AND E.SALARY > M.SALARY;

 When no longer needed, a view can be dropped:

DROP VIEW MANAGER;

3
View Implementation

 There are two ways to implement a view:

 Approach 1: Query modification
– Modify the view query into a query on the underlying base tables
– Example:
SELECT * FROM Manager WHERE Salary > 100000
becomes
SELECT Fname, Lname, Dname, Dnumber, Salary
FROM EMPLOYEE, DEPARTMENT
WHERE SSN=MgrSSN AND Salary > 100000
– Disadvantage:
 Inefficient for views defined via complex queries

4
View Implementation

 Approach 2: View materialization

– Involves physically creating and keeping a temporary table
– Concerns:
 Maintaining correspondence between the base table and the view
when the base table is updated

 ORACLE
CREATE MATERIALIZED VIEW or CREATE SNAPSHOT

5
Update Views

 Update on a view can be implemented by mapping it to an update on

the underlying base table
UPDATE MANAGER
SET Salary = 1.1*Salary
WHERE Dname = ‘Research’;
– Becomes:
UPDATE EMPLOYEE
SET Salary = 1.1*Salary
WHERE SSN in (SELECT MgrSSN
FROM DEPARTMENT
WHERE DName = ‘Research’);

 Updating views involving joins are not always possible

– Views defined using groups and aggregate functions are not updateable
 For mySQL, the keyword “WITH CHECK OPTION” must be added to the
view definition if the view is to be updated

6
Stored Procedures in MySQL

 A stored procedure contains a sequence of SQL commands stored in

the database catalog so that it can be invoked later by a program

 Stored procedures are declared using the following syntax:

Create Procedure <proc-name>
(param_spec1, param_spec2, …, param_specn )
begin
-- execution code
end;

where each param_spec is of the form:

[in | out | inout] <param_name> <param_type>
– in mode: allows you to pass values into the procedure,
– out mode: allows you to pass value back from procedure to the calling
program
7
Example

 Suppose we want to keep track of the total salaries of employees

working for each department

We need to write a procedure

to update the salaries in
the deptsal table
8
Example

Step 1: Change the delimiter (i.e., terminating character) of

SQL statement from semicolon (;) to something else (e.g., //)

So that you can distinguish between the semicolon of the

SQL statements in the procedure and the terminating
character of the procedure definition

9
Example

Step 2:

1. Define a procedure called updateSalary which takes as

input a department number.

2. The body of the procedure is an SQL command to update

the totalsalary column of the deptsal table.

3. Terminate the procedure definition using the delimiter you

had defined in step 1 (//)

10
Example

Step 3: Change the delimiter back to semicolon (;)

11
Example

Step 4: Call the procedure to update the totalsalary for each

department

12
Example

Step 5: Show the updated total salary in the deptsal table

13
Stored Procedures in MySQL

 Use show procedure status to display the list of stored

procedures you have created

 Use drop procedure to remove a stored procedure

14
Stored Procedures in MySQL

 You can declare variables in stored procedures

 You can use flow control statements (conditional IF-

THEN-ELSE or loops such as WHILE and REPEAT)

 MySQL also supports cursors in stored procedures.

– A cursor is used to iterate through a set of rows returned by a
query so that we can process each individual row.

 To learn more about stored procedures, go to:

http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

15
Example using Cursors

 The previous procedure updates one row in deptsal table

based on input parameter
 Suppose we want to update all the rows in deptsal
simultaneously
– First, let’s reset the totalsalary in deptsal to zero

16
Example using Cursors

Drop the old procedure

Use cursor to iterate the rows

17
Example using Cursors

 Call procedure

18
Another Example

 Create a procedure to give a raise to all employees

19
Another Example

20
Another Example

21
Functions

 Functions are declared using the following syntax:

function <function-name> (param_spec1, …, param_speck)

returns <return_type>
[not] deterministic allow optimization if same output
for the same input (use RAND not deterministic )
Begin
-- execution code
end;

where param_spec is:

[in | out | in out] <param_name> <param_type>

– You need ADMIN privilege to create functions on mysql-user server

22
Example of Functions

23
Example of Functions

24
SQL Triggers

 To monitor a database and take a corrective action when a condition

occurs
– Examples:
 Charge $10 overdraft fee if the balance of an account after a withdrawal
transaction is less than $500
 Limit the salary increase of an employee to no more than 5% raise

CREATE TRIGGER trigger-name

trigger-time trigger-event
ON table-name
FOR EACH ROW
trigger-action;

– trigger-time  {BEFORE, AFTER}

– trigger-event  {INSERT,DELETE,UPDATE}

25
SQL Triggers: An Example

 We want to create a trigger to update the total salary of a

department when a new employee is hired

26
SQL Triggers: An Example

 Create a trigger to update the total salary of a department

when a new employee is hired:

 The keyword “new” refers to the new row inserted

27
SQL Triggers: An Example

totalsalary increases by 90K

totalsalary did not change

28
SQL Triggers: An Example
 A trigger to update the total salary of a department when an
employee tuple is modified:

29
SQL Triggers: An Example

30
SQL Triggers: An Example
 A trigger to update the total salary of a department when an
employee tuple is deleted:

31
SQL Triggers: An Example

32
SQL Triggers

 To list all the triggers you have created:

mysql> show triggers;

33