2022

ADVANCED Structured Query Language

Shabani Bakari Juma

+255 717 387899
+255 688 300078
Muya.shabani@yahoo.co.uk
 ADVANCED Structured Query Language | Shabani Bakari Juma

TABLE OF CONTENTS

Contents
TABLE OF CONTENTS .............................................................................................................. i
SORED PROCEDURES ..............................................................................................................1
Advantages of Stored Procedures .............................................................................................1
Creating a Stored Procedure ......................................................................................................1
Pick a Delimiter.........................................................................................................................1
Calling Stored Procedures .........................................................................................................3
Displaying Characteristics of Stored Procedures ......................................................................3
Displaying Stored Procedure’s Source Code ............................................................................4
Stored Procedure Variables .......................................................................................................4
Declaring Variables ...................................................................................................................4
Assigning Value to Variables ....................................................................................................4
Variables Scope .........................................................................................................................5
Stored Procedure Parameters ....................................................................................................5
The IN Parameter ......................................................................................................................5
The OUT Parameter ..................................................................................................................6
The INOUT Parameter ..............................................................................................................7
Stored Procedures that Return Multiple Values ........................................................................8
MySQL IF Statement Syntax ....................................................................................................9
MySQL IF ELSE Statement ......................................................................................................9
MySQL IF ELSEIF ELSE Statement........................................................................................9
MySQL CASE Statement .......................................................................................................11
Simple CASE Statement .........................................................................................................11
Searched CASE Statement ......................................................................................................13
Hints for Choosing Between IF and CASE Statements ..........................................................15
MYSQL TRIGGERS ..................................................................................................................16
Advantages of Using SQL Triggers ........................................................................................16
Disadvantages of Using SQL Triggers....................................................................................16
MySQL Trigger Syntax ...........................................................................................................17
MySQL Trigger Example........................................................................................................18
MySQL STORED FUNCTION ..................................................................................................21
MySQL Stored Function Syntax .............................................................................................21
MySQL Create Function Example ..........................................................................................21
Calling Stored Function ..........................................................................................................23
MySQL VIEWS ..........................................................................................................................24
Creating A View......................................................................................................................24
Displaying List of Views.........................................................................................................25
i
 ADVANCED Structured Query Language | Shabani Bakari Juma

Renaming a View ....................................................................................................................26

Displaying View Structure ......................................................................................................26
Displaying Create View Statement .........................................................................................27
Dropping a View .....................................................................................................................27
Advantages of MySQL Views ................................................................................................27

ii
 ADVANCED Structured Query Language | Shabani Bakari Juma

SORED PROCEDURES
A procedure (often called a stored procedure) is a subroutine like a subprogram in a regular
computing language, stored in database. A procedure has a name, a parameter list, and SQL
statement(s). All most all relational database system supports stored procedure.

Advantages of Stored Procedures

Typically, stored procedures help increase the performance of the applications. Once
created, stored procedures are compiled and stored in the database. However, MySQL
implements the stored procedures slightly different. MySQL stored procedures are
compiled on demand. After compiling a stored procedure, MySQL puts it into a cache.
And MySQL maintains its own stored procedure cache for every single connection. If an
application uses a stored procedure multiple times in a single connection, the compiled
version is used, otherwise, the stored procedure works like a query.
Stored procedures help reduce the traffic between application and database server
because instead of sending multiple lengthy SQL statements, the application has to send
only name and parameters of the stored procedure.
Stored procedures are reusable and transparent to any applications. Stored procedures
expose the database interface to all applications so that developers don’t have to develop
functions that are already supported in stored procedures.
Stored procedures are secure. The database administrator can grant appropriate
permissions to applications that access stored procedures in the database without giving
any permissions on the underlying database tables.

Creating a Stored Procedure

A procedure is part of database object. Thus, before writing creating it, you have to open a
database using the use <database name> statement.
Let us use our university database to create a simple procedure called getAllDepartments(). The
getAllDepartments() procedure select all departments from the department table.

Pick a Delimiter
The delimiter is the character or string of characters which is used to complete an SQL statement.
By default we use semicolon (;) as a delimiter. But this causes problem in stored procedure
because a procedure can have many statements, and everyone must end with a semicolon. So for
your delimiter, pick a string which rarely occur within statement or within procedure.

We use the command delimiter followed by the delimiter we want to set in order to change the
default delimiter. For example, to change the default delimiter to ## we could write

DELIMETER ## ;

NB:
The statement is terminated by a semicolon (;) because it is the current delimiter in use and there
is a space between the proposed new delimiter (## in this case) and the semicolon (current
delimiter). Failure to put the space will make MySQL wait for the statement termination (current
delimiter) as the semicolon will be counted as part of the proposed new delimiter. However to

1
 ADVANCED Structured Query Language | Shabani Bakari Juma

terminate it with the current delimiter is option, you can leave it unterminated. After execution
of that command, all SQL statement will be terminated by ## instead of semicolon.
SELECT deptName FROM department##

Figure 1; changing default delimiter and using the new delimiter

To switch back to semicolon as default delimiter, you need to run the delimiter command again
specifying semicolon as the new delimiter as follows:
DELIMITER ;

Let us now create our getAllDepartmentNames() procedure as follows:

DELIMITER //
CREATE PROCEDURE getAllDepartmentNames()
BEGIN
SELECT deptName FROM department;
END //
DELIMITER ;

Figure 2; creating getAllDepartmentNames procedure

2
 ADVANCED Structured Query Language | Shabani Bakari Juma

Explanations
The first command (DELIMITER //) changes the default delimiter to two backslashes instead of
semicolon. The last command (DELIMITER ;) changes the default delimiter back to semicolon
instead of two backslashes used by the procedure. These commands are not related to our
procedure.
We use the CREATE PROCEDURE statement to create a new stored procedure. We specify the
name of stored procedure after the CREATE PROCEDURE statement. In this case, the name of
the stored procedure is getAllDepartmentNames. We put the parentheses after the name of the
stored procedure.

The section between BEGIN and END is called the body of the stored procedure. You put the
declarative SQL statements in the body to handle business logic. In this stored procedure, we use
a simple SELECT statement to query data from the department table.

Calling Stored Procedures

In order to call a stored procedure, you use the following SQL command:
CALL PROCEDURE_NAME();

For example, to call our getAllDepartmentNames procedure we could write:

CALL getAllDepartmentNames();

Figure 3; calling getAllDepartmentNames procedure

Displaying Characteristics of Stored Procedures

Use the statement
SHOW PROCEDURE STATUS
if you want to list all procedures available in the MySQL server. That statement will list all the
properties.
Specify the name of the database using where clause in the above statement if you want to list
procedures available in a specific database. For example, if you want to list procedures available
in the university database use
SHOW PROCEDURE STATUS WHERE DB = ‘university’;

3
 ADVANCED Structured Query Language | Shabani Bakari Juma

Displaying Stored Procedure’s Source Code

To display source code of a particular stored procedure, you use the SHOW CREATE
PROCEDURE followed by the name of the procedure. For example, to display the source codes
of the procedure named getAllDepartmentNames we would write:
SHOW CREATE PROCEDURE getAllDepartmentNames;

Stored Procedure Variables

A variable is a named data object whose value can change during the stored procedure execution.
We typically use the variables in stored procedures to hold the immediate results. These variables
are local to the stored procedure. You must declare a variable before you can use it.

Declaring Variables
To declare a variable inside a stored procedure, you use the DECLARE statement as follows:
DECLARE VARIABLE_NAME DATATYPE(SIZE) DEFAULT DEFAULT_VALUE;

MySQL allows you to declare two or more variables that share the same data type using a single
DECLARE statement as following:
DECLARE x, y INT DEFAULT 0;

We declared two integer variables x and y, and set their default values to zero.

Assigning Value to Variables

Once you declared a variable, you can start using it. To assign a variable another value, you use
the SET statement, for example:
DECLARE total_department INT DEFAULT 0;
SET total_department = 9;

The value of the total_department variable is 9 after the assignment.

Besides the SET statement, you can use the SELECT INTO statement to assign the result of a
query, which returns a scalar value, to a variable. See the following example:
DECLARE total_department INT DEFAULT 0
SELECT COUNT(*) INTO total_department
FROM department

In the example above:

• First, we declare a variable named total_department and initialize its value to 0.
• Then, we used the SELECT INTO statement to assign the total_department variable the
number of products that we selected from the department table in the university database.

4
 ADVANCED Structured Query Language | Shabani Bakari Juma

Variables Scope
A variable has its own scope that defines its lifetime. If you declare a variable inside a stored
procedure, it will be out of scope when the END statement of stored procedure reached.

If you declare a variable inside BEGIN END block, it will be out of scope if the END is reached.
You can declare two or more variables with the same name in different scopes because a variable
is only effective in its own scope. However, declaring variables with the same name in different
scopes is not good programming practice.

A variable that begins with the @ sign is session variable. It is available and accessible until the
session ends.

Stored Procedure Parameters

Almost stored procedures that you develop require parameters. The parameters make the stored
procedure more flexible and useful. In MySQL, a parameter has one of three modes:
IN, OUT, or INOUT.

▪ IN – is the default mode. When you define an IN parameter in a stored procedure, the
calling program has to pass an argument to the stored procedure. In addition, the value
of an IN parameter is protected. It means that even the value of the IN parameter is
changed inside the stored procedure, its original value is retained after the stored
procedure ends. In other words, the stored procedure only works on the copy of the IN
parameter.
▪ OUT – the value of an OUT parameter can be changed inside the stored procedure and
its new value is passed back to the calling program. Notice that the stored procedure
cannot access the initial value of the OUT parameter when it starts.
▪ INOUT – an INOUT parameter is the combination of IN and OUT parameters. It means
that the calling program may pass the argument, and the stored procedure can modify the
INOUT parameter and pass the new value back to the calling program.

The syntax of defining a parameter in the stored procedures is as follows:

MODE PARAM_NAME PARAM_TYPE(PARAM_SIZE)

The IN Parameter
The following example illustrates how to use the IN parameter in the getStudentsByProgram
stored procedure that select students doing a particular program.
DELIMITER ##
CREATE PROCEDURE getStudentByProgram(IN programoOfStudy VARCHAR(50))
BEGIN
SELECT studentID, fullName, gender, YoE FROM student WHERE program =
programoOfStudy;
END ##
DELIMITER ;

5
 ADVANCED Structured Query Language | Shabani Bakari Juma

The program is the IN parameter of the stored procedure. Inside the stored procedure, we select
all students taking program specified by the programoOfStudy parameter.

Suppose, we want to get all students doing BCICT program, we just need to pass a value
(BCICT) to the stored procedure as follows:

CALL getStudentByProgram('BCICT');

Figure 4; calling getStudentByProgram procedure with parameter

The OUT Parameter

The following stored procedure returns the number of modules by student ID and academic year.
It has three parameters:

registrationNumber : the IN parameter that is the registration number of the student that
we want to count his/her modules in a given academic year.
academicYear: the IN parameter that is the academic year we want to count the student’s
modules.
totalModule : the OUT parameter that stores the number of modules of the specified
student in the specified academic year.

DELIMITER //
CREATE PROCEDURE totalModuleYearly(
IN registrationNumber VARCHAR(25),
IN academicYear VARCHAR(12),
OUT totalModule INT)
BEGIN
SELECT COUNT(courseCode) INTO totalModule FROM enrollment WHERE
studentID = registrationNumber AND aYear = academicYear;
END //
DELIMITER ;

6
 ADVANCED Structured Query Language | Shabani Bakari Juma

To get the number of enrolled modules, we call the totalModuleYearly stored procedure and
pass the studentID as registrationNumber, aYear as academicYear and also pass an argument (
@total ) to get the return value.

CALL totalModuleYearly('NIT/BCFCF/2016/2028', '2016/2017', @total);

SELECT @total;

Figure 5; calling totalModuleYearly procedure with IN and OUT parameters

It is possible to use AS keyword to rename the variable that return the output in the select
statement. For example, in the above query, we would rename the total to total modules per year
as follows:

CALL totalModuleYearly('NIT/BCFCF/2016/2028', '2016/2017', @total);

SELECT @total AS ‘Total Modules per Year’;

Figure 6; renaming the output parameter using AS keyword

The INOUT Parameter

The following example demonstrates how to use an INOUT parameter in the stored procedure.
DELIMITER $$
CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))
BEGIN
SET count = count + inc;
END$$
DELIMITER ;

7
 ADVANCED Structured Query Language | Shabani Bakari Juma

How it works

The set_counter stored procedure accepts one INOUT parameter (count) and one IN
parameter (inc).
Inside the stored procedure, we increase the counter (count) by the value of the inc
parameter.

See how we call the set_counter stored procedure:

Stored Procedures that Return Multiple Values

MySQL stored function returns only one value. To develop stored programs that return multiple
values, you need to use stored procedures with INOUT or OUT parameters.

For example, the getTotalModulePerYearPerSemester stored procedure below accept two IN

parameters registrationNumber and academicYear and two OUT parameters semesterOne and
semeseterTwo which will return the number of modules in semester one and in semester two
respectively of a given academic year.

DELIMITER //
CREATE PROCEDURE getTotalModulePerYearPerSemester (
IN registrationNumber VARCHAR(25),
IN academicYear VARCHAR(12),
OUT semesterOne INT,
OUT semesterTwo INT)

BEGIN

/* semester one courses */

SELECT COUNT(courseCode) INTO semesterOne
FROM enrollment WHERE studentID = registrationNumber
AND aYear = academicYear and semester = 1;

/* semester two courses */

SELECT COUNT(courseCode) INTO semesterTwo
FROM enrollment WHERE studentID = registrationNumber
AND aYear = academicYear and semester = 2;

END //
DELIMITER ;

8
 ADVANCED Structured Query Language | Shabani Bakari Juma

Figure 7; calling stored procedure that return multiple values

MySQL IF Statement Syntax

The following illustrates the syntax of the IF statement:

IF expression THEN
statements;
END IF;

If the expression evaluates to TRUE, then the statements will be executed, otherwise, the control
is passed to the next statement following the END IF.

MySQL IF ELSE Statement

In case you want to execute statements when the expression evaluates to FALSE, you use the IF
ELSE statement as follows:

IF expression THEN
statements;
ELSE
else-statements;
END IF;

MySQL IF ELSEIF ELSE Statement

If you want to execute statements conditionally based on multiple expressions, you use the IF
ELSEIF ELSE statement as follows:

IF expression THEN
statements;
ELSEIF elseif-expression THEN
elseif-statements;
...
ELSE
else-statements;
END IF;

9
 ADVANCED Structured Query Language | Shabani Bakari Juma

If the expression evaluates to TRUE, the statements in the IF branch executes. If the expression
evaluates to FALSE, MySQL will check the elseif-expression and execute the elseif-statements
in the ELSEIF branch if the elseif_expression evaluates to TRUE.

The IF statement may have multiple ELSEIF branches to check multiple expressions. If no
expression evaluates to TRUE, the else-statements in the ELSE branch will execute.
The procedure departmentLevel below count the number of programs in a particular department
and use that number (value) to rate the department as silver department, or gold department or
platinum department depending on that number. The procedure have on IN parameter,
departmentNumber and one OUT parameter departmentLevel. Inside the procedure we declare
one variable numberOfPrograms that will store the value to be used in decision making (else –
ifelse).

DELIMITER &&
CREATE PROCEDURE departmentLevel(
IN departmentNumber VARCHAR(15),
OUT departmentLevel VARCHAR(50)
)

BEGIN

DECLARE numberOfPrograms INT;

SELECT COUNT(*) INTO numberOfPrograms FROM

program WHERE department = departmentNumber;

IF numberOfPrograms < 5 THEN

SET departmentLevel = 'SILVER DEPARTMENT';
ELSEIF numberOfPrograms < 12 THEN
SET departmentLevel = 'GOLD DEPARTMENT';
ELSE
SET departmentLevel = 'PLATNUM DEPARTMENT';
END IF;

END &&
DELIMITER ;

10
 ADVANCED Structured Query Language | Shabani Bakari Juma

Figure 8; calling stored procedure that make decision using IF---ELSEIF---

MySQL CASE Statement

Besides the IF statement, MySQL provides an alternative conditional statement called CASE.
The MySQL CASE statement makes the code more readable and efficient.
There are two forms of the CASE statements: simple and searched CASE statements.

Simple CASE Statement

Let’s take a look at the syntax of the simple CASE statement:
CASE case_expression
WHEN when_expression_1 THEN commands
WHEN when_expression_2 THEN commands
...
ELSE commands
END CASE;

You use the simple CASE statement to check the value of an expression against a set of unique
values.

The case_expression can be any valid expression. We compare the value of the case_expression
with when_expression in each WHEN clause e.g., when_expression_1, when_expression_2, etc.
If the value of the case_expression and when_expression_n are equal, the commands in
the corresponding WHEN branch executes.

In case none of the when_expression in the WHEN clause matches the value of the
case_expression, the commands in the ELSE clause will execute. The ELSE clause is optional.
If you omit the ELSE clause and no match found, MySQL will raise an error.

The getStudentCountry procedure below demonstrate how we can use a simple case statement.
The procedure have one IN parameter registrationNumber and one OUT parameter

11
 ADVANCED Structured Query Language | Shabani Bakari Juma

studentCountry. Inside the procedure, we use case to assign value to the OUT parameter. In case
the student’s nationality is one of the five East African Countries, we assign the name of that
country to the OUT parameter, else we set the student country as foreign.
DELIMITER //
CREATE PROCEDURE getStudentCountry (
IN registrationNumber VARCHAR(20), OUT studentCountry VARCHAR(100)
)
BEGIN
DECLARE uraia VARCHAR(100);

SELECT nationality INTO uraia FROM student WHERE studentID = registrationNumber;

CASE uraia
WHEN 'Tanzanian' THEN
SET studentCountry = 'Tanzania';
WHEN 'Kenyan' THEN
SET studentCountry = 'Kenya';
WHEN 'Ugandan' THEN
SET studentCountry = 'Uganda';
WHEN 'Rwandan' THEN
SET studentCountry = 'Rwanda';
WHEN 'Burundian' THEN
SET studentCountry = 'Burundi';
ELSE
SET studentCountry = 'Foreigner';
END CASE;
END //
DELIMITER ;

12
 ADVANCED Structured Query Language | Shabani Bakari Juma

Figure 9; calling stored procedure that make decision using simple CASE

In the output shown in figure 9 (above), the last procedure call passed the value
NIT/BCFCF/2016/2027 as student registration number. The nationality of that student was
updated to Jamaican and that is why the output shows that the student is a foreigner.

Searched CASE Statement

The simple CASE statement only allows you match a value of an expression against a set of
distinct values. In order to perform more complex matches such as ranges, you use the searched
CASE statement. The searched CASE statement is equivalent to the IF statement, however, its
construct is much more readable.

The following illustrates the syntax of the searched CASE statement:

CASE
WHEN condition_1 THEN commands
WHEN condition_2 THEN commands
...
ELSE commands
END CASE;

13
 ADVANCED Structured Query Language | Shabani Bakari Juma

In the example below, we rewrite the departmentLevel procedure whose output is in figure 8 to
allow it to use searched case in deciding whether the department is silver, gold or platinum.
Since that procedure exists in our MySQL server, we append case at the end of that name so that
they can have distinct name in the server. Thus, the name of our new procedure become
departmentLevelCase.

DELIMITER &&
CREATE PROCEDURE departmentLevelCase(
IN departmentNumber VARCHAR(15), OUT departmentLevel VARCHAR(50)
)
BEGIN
DECLARE numberOfPrograms INT;

SELECT COUNT(*) INTO numberOfPrograms FROM

program WHERE department = departmentNumber;

CASE
WHEN numberOfPrograms < 5 THEN
SET departmentLevel = 'SILVER DEPARTMENT';
WHEN numberOfPrograms < 12 THEN
SET departmentLevel = 'GOLD DEPARTMENT';
WHEN numberOfPrograms >= 12 THEN
SET departmentLevel = 'PLATNUM DEPARTMENT';
END CASE;

END &&
DELIMITER ;

Figure 10; calling stored procedure that make decision using Searched CASE

14
 ADVANCED Structured Query Language | Shabani Bakari Juma

Hints for Choosing Between IF and CASE Statements

MySQL provides both IF and CASE statements to enable you to execute a block of SQL code
based on certain conditions, which is known as flow control. So what statement should you use?
For the most developers, choosing between IF and CASE is just a matter of personal preference.
However, when you decide to use IF or CASE, you should take the following points into the
consideration:

A simple CASE statement is more readable than the IF statement when you compare a
single expression against a range of unique values. In addition, the simple CASE
statement is more efficient than the IF statement.
When you check complex expressions based on multiple values, the IF statement is easier
to understand.
If you choose to use the CASE statement, you have to make sure that at least one of the
CASE condition is matched. Otherwise, you need to define an error handler to catch the
error. Recall that you don’t have to do this with the IF statement.
In most organization, there is always something called development guidelines document
that provides developers with naming convention and guidelines on programming style.
You should refer to this document and follow the development practices.
In some situations, mixing between IF and CASE make your stored procedure more
readable and efficient.

15
 ADVANCED Structured Query Language | Shabani Bakari Juma

MYSQL TRIGGERS

The database trigger is powerful tool for protecting the integrity of the data in your MySQL
databases. In addition, it is useful to automate some database operations such as logging,
auditing, etc.

A SQL trigger is a set of SQL statements stored in the database catalog. A SQL trigger is
executed or fired whenever an event associated with a table occurs e.g., insert, update or delete.

A SQL trigger is a special type of stored procedure. It is special because it is not called directly
like a stored procedure. The main difference between a trigger and a stored procedure is that a
trigger is called automatically when a data modification event is made against a table whereas a
stored procedure must be called explicitly.

It is important to understand SQL trigger’s advantages and disadvantages so that you can use it
appropriately. In the following sections, we will discuss the advantages and disadvantages of
using SQL triggers.

Advantages of Using SQL Triggers

SQL triggers provide an alternative way to check the integrity of data.

SQL triggers can catch errors in business logic in the database layer.
SQL triggers provide an alternative way to run scheduled tasks. By using SQL triggers,
you don’t have to wait to run the scheduled tasks because the triggers are invoked
automatically before or after a change is made to the data in the tables.
SQL triggers are very useful to audit the changes of data in tables.

Disadvantages of Using SQL Triggers

SQL triggers only can provide an extended validation and they cannot replace all the
validations. Some simple validations have to be done in the application layer. For
example, you can validate user’s inputs in the client side by using JavaScript or in the
server side using server-side scripting languages such as JSP, PHP, ASP.NET, Perl, etc.
SQL triggers are invoked and executed invisible from the client applications, therefore,
it is difficult to figure out what happen in the database layer.
SQL triggers may increase the overhead of the database server.

Triggers or stored procedures? It is recommended that if you have no way to get the work done
with stored procedure, think about SQL trigger.

In MySQL, a trigger is a set of SQL statements that is invoked automatically when a change is
made to the data on the associated table. A trigger can be defined to be invoked either before or
after the data is changed by INSERT, UPDATE or DELETE statement. Before MySQL version
5.7.2, you can to define maximum six triggers for each table.

16
 ADVANCED Structured Query Language | Shabani Bakari Juma

i. BEFORE INSERT – activated before data is inserted into the table.

ii. AFTER INSERT – activated after data is inserted into the table.
iii. BEFORE UPDATE – activated before data in the table is updated.
iv. AFTER UPDATE – activated after data in the table is updated.
v. BEFORE DELETE – activated before data is removed from the table.
vi. AFTER DELETE – activated after data is removed from the table.

However, from MySQL version 5.7.2+, you can define multiple triggers for the same trigger
event and action time.

When you use a statement that does not use INSERT, DELETE or UPDATE statement to change
data in a table, the triggers associated with the table are not invoked. For example, the
TRUNCATE statement removes all data of a table but does not invoke the trigger associated
with that table.

There are some statements that use the INSERT statement behind the scenes such as REPLACE
statement or LOAD DATA statement. If you use these statements, the corresponding triggers
associated with the table are invoked.

You must use a unique name for each trigger associated with a table. However, you can have the
same trigger name defined for different tables though it is not a good practice.

You should name the triggers using the following naming convention:

(BEFORE | AFTER)_tableName_(INSERT| UPDATE | DELETE)

For example, before_enrollment_update is a trigger invoked before a row in the enrollment table
is updated.

The following naming convention is as good as the one above.

tablename_(BEFORE | AFTER)_(INSERT| UPDATE | DELETE)

For example, enrollment_before_update is the same as before_enrollment_update trigger above.

MySQL Trigger Syntax

In order to create a new trigger, you use the CREATE TRIGGER statement. The following
illustrates the syntax of the CREATE TRIGGER statement:
CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
……..
END;

17
 ADVANCED Structured Query Language | Shabani Bakari Juma

Let’s examine the syntax above in more detail.

You put the trigger name after the CREATE TRIGGER statement. The trigger name
should follow the naming convention [trigger time]_[table name]_[trigger event], for
example before_employees_update.
Trigger activation time can be BEFORE or AFTER. You must specify the activation time
when you define a trigger. You use the BEFORE keyword if you want to process action
prior to the change is made on the table and AFTER if you need to process action after
the change is made.
The trigger event can be INSERT, UPDATE or DELETE. This event causes the trigger to
be invoked. A trigger only can be invoked by one event. To define a trigger that is invoked
by multiple events, you have to define multiple triggers, one for each event.
A trigger must be associated with a specific table. Without a table trigger would not exist
therefore you have to specify the table name after the ON keyword.
You place the SQL statements between BEGIN and END block. This is where you define
the logic for the trigger.

MySQL Trigger Example

Let’s start creating a trigger in MySQL to log the changes of the student table in our university
database. Before updating student table, the trigger will copy the data of the row to be updated
into the student_audit table. First of all, let us create the student_audit table.
create table student_audit
(
ID INT AUTO_INCREMENT PRIMARY KEY,
studentID VARCHAR(100) NOT NULL,
fullname text NOT NULL,
dob DATE NOT NULL,
nationality VARCHAR(100),
maritualStatus VARCHAR(100),
program VARCHAR(100),
YoE INT(4) not null,
changeTime DATETIME NOT NULL,
action VARCHAR(100)NOT NULL
)engine=innoDB;

Then, we create the trigger called before_student_update as follows:

DELIMITER ??
CREATE TRIGGER before_student_update
BEFORE UPDATE ON student
FOR EACH ROW
BEGIN
INSERT INTO student_audit
SET action = 'update',
studentID =OLD.studentID,
fullname = OLD.fullname,

18
 ADVANCED Structured Query Language | Shabani Bakari Juma

dob =OLD.dob,
nationality = OLD.nationality,
maritualStatus = OLD.maritualStatus,
program = OLD.program,
YoE = OLD.YoE,
changeTime = NOW();
END ??
DELIMITER ;

Inside the body of the trigger, we used the OLD keyword to access stdudentID, fullname, dob,
nationality, maritualStatus, program and YoE column of the row affected by the trigger.

Notice that in a trigger defined for INSERT, you can use NEW keyword only. You cannot use
the OLD keyword. However, in the trigger defined for DELETE, there is no new row so you can
use the OLD keyword only. In the UPDATE trigger, OLD refers to the row before it is updated
and NEW refers to the row after it is updated.

Then, to view all triggers in the current database, you use SHOW TRIGGERS statement.

To delete a particular trigger in the current database we use the DROPTRIGGER statement. For
example, to drop the above trigger, we could write:
DROP TRIGGER IF EXISTS before_student_update;

After that, update the employees table to check whether the trigger is invoked.

UPDATE student SET fullname='Prosper Magayane', nationality='Kenyan',

maritualStatus='Married' WHERE studentID='NIT/BCLTM/2016/1472';

Finally, to check if the trigger was invoked by the UPDATE statement, you can query the
student_audit table using the following query:

SELECT * FROM student_audit;

Figure 14; Checking if after update trigger was activated

Now let us check example of after insert trigger. Assuming that once we insert a student in a
student table, then we have to create login information of that student in the userLogin table. Let
say, by default the username of that student will be his/her student ID, and the password will be
his/her date of birth encrypted using message digest five (MD5) algorithm.
Let us first create the userLogin table.

19
 ADVANCED Structured Query Language | Shabani Bakari Juma

CREATE TABLE userLogin

(
userID VARCHAR(100) NOT NULL PRIMARY KEY,
password TEXT NOT NULL,
active ENUM('yes','no')NOT NULL DEFAULT 'yes'
)engine=innoDB;

Then, let us create the after_student_insert trigger.

DELIMITER $$
CREATE TRIGGER after_student_insert
AFTER INSERT ON student
FOR EACH ROW
BEGIN
INSERT INTO userLogin
SET
userID =NEW.studentID,
password = MD5(NEW.dob);
END $$
DELIMITER ;

Then, let us insert a new student into student table.

INSERT INTO student (studentID, fullname, gender, dob, nationality, maritualStatus, program,
YoE, active, status) VALUES ('NIT/BCFCF/2016/2073', 'Isihakha Muya', 'male', '1995-09-08',
'Tanzanian', 'Single', 'BCICT', 2016, 'yes', 'continuing');

Then
Finally, to check if the trigger was invoked by the INSERT statement, you can query the
userLogin table using the following query:

SELECT * FROM userLogin;

Figure 15; Checking if after insert trigger was activated

20
 ADVANCED Structured Query Language | Shabani Bakari Juma

MySQL STORED FUNCTION

A stored function is a special kind stored program that returns a single value. Typically, you use
stored functions to encapsulate common formulas or business rules that are reusable among SQL
statements or stored programs.

Different from a stored procedure, you can use a stored function in SQL statements wherever an
expression is used. This helps improve the readability and maintainability of the procedural code.

MySQL Stored Function Syntax

DELIMITER ##
CREATE FUNCTION functionName (parameter_list)
RETURNS return_Data_Type
[NOT] DETERMINISTIC
BEGIN
……function body statement
END##
DELIMITER ;

NOTE:
Parameter_list is a list of input parameters that will be passed during a function call
A function can be deterministic or not deterministic. A function is considered
deterministic if it always returns the same result set when it's called with the same
set of input values. A function is considered nondeterministic if it doesn't return the
same result set when it's called with the same set of input values.
If you don’t use DETERMINISTIC or NOT DETERMINISTIC, MySQL uses the NOT
DETERMINISTIC option by default.

MySQL Create Function Example

Let as write a query that will the first 10 NTA level 8 semester 2 modules (with module code
numbered 82). The query and its output will look like the following.
SELECT courseID, courseCode, courseName, credit FROM module WHERE courseCode like
'%82%' limit 10;

21
 ADVANCED Structured Query Language | Shabani Bakari Juma

Figure 16; Listing the first 10 NTA level 8 semester 2 modules

Suppose that in the last column (credit) on figure 16 above we need to print a so-called module
status where a module with 9 credit will be rated Elective Module, the one with 12 credits will
be rated regular Core Module and the 15 credit module will be rated Super Core Module. In that
case, we will write a function and we will call it moduleStatus. The function will receive one
parameter which is the credit value. Then inside the function we will use either a switch case or
if…..else…if to assign the corresponding module rating to a variable declared inside the function
and later the variable. The function will look like:
DELIMITER ##
CREATE FUNCTION moduleStatus (idadiYaCredit int)
RETURNS varchar(100)

NOT DETERMINISTIC
BEGIN
DECLARE statusYaModule VARCHAR(100);

IF idadiYaCredit = 9 THEN
SET statusYaModule ='Fundamental Module';
ELSEIF idadiYaCredit = 12 THEN
SET statusYaModule ='Regular Core Module';
ELSE
SET statusYaModule ='Super Core Module';
END IF;

RETURN(statusYaModule);

END##
DELIMITER ;

you can view all stored functions in the current classicmodels database by using the SHOW
FUNCTION STATUS as follow:
SHOW FUNCTION STATUS WHERE db=‘databaseName’;

For example

22
 ADVANCED Structured Query Language | Shabani Bakari Juma

SHOW FUNCTION STATUS WHERE DB='university';

Calling Stored Function

A stored function is called inside a select statement the same way we MySQL built in functions
like count, sum etc.
SELECT courseID, courseCode, courseName, moduleStatus(credit) AS STATUS FROM module
WHERE courseCode like '%82%' limit 10;

Figure 17; MySQL select statement that call a stored function

23
 ADVANCED Structured Query Language | Shabani Bakari Juma

MySQL VIEWS
A view is a named query stored in the database catalog. Normally, a view is associated with a
select statement. Once a view has been created, you can query data by referencing (selecting) the
view. A single view can select data from a single table or from multiple tables.

Consider the query below and its output

select s.studentID `Namba ya Mwanafunzi`, s.fullname `Jina la mwanafunzi`, s.gender
Jinsi, concat(c.courseName,', ', c.courseCode) `Taarifa za Moduli`, c.credit `Uzito wa
Moduli`, e.ayear `Mwaka wa Masomo` from enrollment as e inner join student as s on
e.studentID=s.studentID inner join courses as c on e.courseCode=c.courseCode where
e.courseCode='FCT 04208' AND ayear='2016/2017' order by s.DoB DESC, s.fullName
ASC;

Figure 18; MySQL select statement

Next time if you want the same information from the database, you need to issue the same query.
However, if you created a view based on the above query, then you will need only to reference
the view as a table.

Creating A View
In order to create a view, u use the create view statement with the following syntax;

CREATE [OR REPLACE] VIEW [db_name.]view_name [(column_list)]

AS
select-statement;

or simply

CREATE VIEW view_name

AS
select-statement;

24
 ADVANCED Structured Query Language | Shabani Bakari Juma

In the first syntax, OR REPLACE is an optional keyword that replace/drop an existing view with
the same name as the newly created view. If you attempt to create a view with the same name as
another existing view without OR REPLACE clause, the DBMS will issue error message the
new view will not be created. That is to say, within the same database, view names are distinct.
The COLUMN_LIST option allows you to create a view with explicitly columns. It is an option.
You can create a view that has no column.

Let’s now use the second syntax to create a view based on the above query. Our view will be
called viewFCTstudents.

CREATE VIEW viewFCTstudents AS

select s.studentID `Namba ya Mwanafunzi`, s.fullname `Jina la mwanafunzi`, s.gender
Jinsi, concat(c.courseName,', ', c.courseCode) `Taarifa za Moduli`, c.credit `Uzito wa
Moduli`, e.ayear `Mwaka wa Masomo` from enrollment as e inner join student as s on
e.studentID=s.studentID inner join courses as c on e.courseCode=c.courseCode where
e.courseCode='FCT 04208' AND ayear='2016/2017' order by s.DoB DESC, s.fullName
ASC;

In order to execute the view, one now need to select the view name with the ALL (*) option
(except for a view with explicit column list, you can select some columns) as follow;

Figure 19; MySQL select statement that reference viewFCTstudent View

Displaying List of Views

The list of views in a given database is shown alongside the list of table. The command to do this
is
Show full tables

25
 ADVANCED Structured Query Language | Shabani Bakari Juma

Figure 20; Listing All Tables of the University Database

From figure 20 above, you can see the last column (Table_type) shows that the last entry
(viewFCTstudents) is a view (not base table as the rest).

Alternatively, you can specify to list only view using the show full tables statement by specifying
the Table_type and using FROM or IN keyword as follow

SHOW FULL TABLES FROM university WHERE TABLE_TYPE = 'VIEW';

Figure 21; Specifying List of Views Only

NOTE: In a database, Base table and views share the same namespace. Hence, most of the
commands that run on a table, also run on a view. Likewise, A table and a view can not have the
same name.

Renaming a View
You can use SQL rename table statement to rename a view as they share the namespace. For
example, to rename our previous viewFCTstudents to a FCTstudentsView, we use the following
statement;

Rename Table viewFCTstudents to FCTstudentView

Displaying View Structure

Like a table, you can use the DESC or DESCRIBE command to sow the structure of your view.

26
 ADVANCED Structured Query Language | Shabani Bakari Juma

Figure 22; Displaying the Structure of a View

As you can see from the figure above, the fields of your view are the names displayed in your
view output while their data type are either from their original base table datatype or depending
on the output if the data is a result of applying some function(s).

Displaying Create View Statement

To display the statement used to create a view, use the SHOW CREATE VIEW command as
follow

Figure 23; Displaying the Create View Statement

Dropping a View
To remove a view from the database, use the DROP VIEW statement with the view name to be
dropped. For example, if we want to drop the above FCTstudentView, we can write

DROP VIEW FCTstudentView;

Advantages of MySQL Views

Simplify Complex Query

Views help simplify complex queries. If you have any frequently used complex query, you can
create a view based on it so that you can reference to the view by using a simple SELECT
statement instead of typing the query all over again.

27
 ADVANCED Structured Query Language | Shabani Bakari Juma

Make the Business Logic Consistent

Suppose you have to repeatedly write the same formula in every query. Or you have a query that
has complex business logic. To make this logic consistent across queries, you can use a view to
store the calculation and hide the complexity.

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.

Enable Backward Compatibility

In legacy systems, views can enable backward compatibility. Suppose, you want to normalize a
big table into many smaller ones. And you don’t want to impact the current applications that
reference the table. In this case, you can create a view whose name is the same as the table based
on the new tables so that all applications can reference the view as if it were a table. Note that a
view and table cannot have the same name so you need to drop the table first before creating a
view whose name is the same as the deleted table.

28