Ch01:
Procedural programming for Database
Database for Mobile Devices & Web
2nd Semester 20202021
Iyad H. Alshami – MOBC 2304 1
SQL Procedural programming
• SQL Procedural programming is a language that extent the Structured Query
Language (SQL).
• PL/SQL @ Oracle brand
• The purpose of procedural programming is to combine database language and
procedural programming language.
• As any programming language the SQL procedural programming has:
• Variables and datatype
• Control statements
• Procedures and Functions
• Stored in database server
Iyad H. Alshami – MOBC 2304 2
Comment Syntax
• From a /* sequence to the following */ sequence.
• From a “#” character to the end of the line.
• From a “-- ” sequence to the end of the line.
• In MySQL, the “-- ” (double-dash) comment style requires the second dash to be
followed by at least one whitespace
• -- Programmer: John Smith
Iyad H. Alshami – MOBC 2304
Variable Declaration
DECLARE variable_name datatype(size) DEFAULT default_value;
• Variable naming rules:
• Identifiers can consist of any alphanumeric characters, plus the characters '_' and '$'.
• Identifiers can start with any character that is legal in an identifier, including a digit.
• However, an identifier cannot consist entirely of digits.
• Data types: A variable can have any MySQL data types. For example
• Character: CHAR(n), VARCHAR(n)
• Number: INT, SMALLINT, DECIMAL(i,j), DOUBLE
• Date: DATE, TIME, DATETIME
• BOOLEAN
• …
• etc
Iyad H. Alshami – MOBC 2304
Variable Declaration
Iyad H. Alshami – MOBC 2304
Variable Declaration - Examples
DECLARE x, y INT DEFAULT 0;
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE ename VARCHAR(50);
Set ename = "Ahmad"
DECLARE no_more_rows BOOLEAN;
SET no_more_rows = TRUE;
Iyad H. Alshami – MOBC 2304
Control Statements
• IF … THEN
• CASE … WHILE
• LOOP
• WHILE … DO
Iyad H. Alshami – MOBC 2304 7
Control Statements
IF… THEN
IF
IF condition THEN
ELSEIF
-- execution code;
END IF;
IF condition THEN
-- execution code;
ELSEIF
ELSE -- execution code;
ELSE
IF condition THEN -- execution code;
-- execution code; END IF;
ELSE
-- execution code;
END IF;
Iyad H. Alshami – MOBC 2304 8
Control Statements
IF… THEN
DECLARE grade INT;
DECLARE level CHAR(1);
SET grade = 87;
IF grade >=90 THEN
SET level = "A";
ELSEIF grade >=80 THEN
SET level = "B";
ELSEIF grade >=70 THEN
SET level = "C";
ELSEIF grade >=60 THEN
SET level = "D";
ELSE
SET level = "F";
END IF;
Iyad H. Alshami – MOBC 2304 9
Control Statements
IF(exp1, exp2, exp3)
• If exp1 is TRUE, neither NULL nor "0" zero, exp2 will be returned otherwise
exp3 will be returned.
• Examples:
• IF(TRUE, 5, 7) # return 5
• IF(1=1 , 5, 7) # return 5
• IF(NULL, 5, 7) # return 7
Iyad H. Alshami – MOBC 2304 10
Control Statements
IFNULL(exp1, exp2)
• If exp1 is NULL, then IFNULL returns exp2 otherwise it returns exp1.
• Examples:
• IFNULL(NULL, 0) # return 0
• IFNULL(10, 0) # return 10
• SELECT bsalary + IFNULL(comm, 0) as "Salary"
• FROM employees;
• #bsalary and comm are attributes in the employees table and
comm can be null.
Iyad H. Alshami – MOBC 2304 11
Control Statements
CASE … WHILE – Simple CASE
• CASE expression … WHILE value THEN
DECLARE grade INT;
DECLARE level CHAR(1);
SET grade = 87;
CASE grade
WHEN 90 THEN
SET level = "A";
WHEN 80 THEN
SET level = "B";
WHEN 70 THEN
SET level = "C";
WHEN 60 THEN
SET level = "D";
ELSE
SET level = "F";
END CASE;
Iyad H. Alshami – MOBC 2304 12
Control Statements
CASE … WHILE – Search CASE
• CASE … WHILE condition THEN
DECLARE grade INT;
DECLARE level CHAR(1);
SET grade = 87;
CASE
WHEN grade >= 90 THEN
SET level = "A";
WHEN grade >= 80 and grade < 90 THEN
SET level = "B";
WHEN grade >= 70 and grade < 80 THEN
SET level = "C";
WHEN grade >= 60 and grade < 70 THEN
SET level = "D";
ELSE
SET level = "F";
END CASE;
Iyad H. Alshami – MOBC 2304 13
Control Statements
IF or CASE ??
• 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.
Iyad H. Alshami – MOBC 2304 14
Control Statements
LOOP
• The LOOP statement allows us to execute one or more statements
repeatedly.
• Typically, you terminate the loop when a condition is satisfied by using
the LEAVE statement. DECLARE sum, count INT;
SET sum = 0;
SET count = 1;
sumloop: LOOP
SET sum = sum + count;
SET count = count +1;
IF count> 10 THEN
LEAVE sumloop;
END IF;
END LOOP;
Iyad H. Alshami – MOBC 2304 15
Control Statements
WHILE … DO
The WHILE loop is a loop statement that executes a block of code repeatedly
as long as a condition is true.
DECLARE sum, count INT;
SET sum = 0;
SET count = 1;
WHIEL count <=10 DO
SET sum = sum + count;
SET count = count +1;
END WHILE;
Iyad H. Alshami – MOBC 2304 16
Stored Procedure
Iyad H. Alshami – MOBC 2304 18
Introduction
• Why Use Stored Programs ?
• The use of stored programs can lead to a more secure database.
• Stored programs offer a mechanism to abstract data access routines, which can
improve the maintainability of code as underlying data structures evolve.
• Stored programs can reduce network traffic, because the program can work on the
data from within the server, rather than having to transfer the data across the
network.
Iyad H. Alshami – MOBC 2304
Introduction
• Why Use Stored Programs ?...
• Stored programs can be used to implement common routines accessible from multiple
applications possibly using otherwise incompatible frameworks executed either within
or from outside the database server.
• Database-centric logic can be isolated in stored programs and implemented by
programmers with more specialized, database experience.
• The use of stored programs can, under some circumstances, improve the portability of
application.
Iyad H. Alshami – MOBC 2304
Stored Routines
• A stored routine is a set of SQL statements that can be stored in the server.
• MySQL supports "routines" and there are two kinds of routines: stored
procedures, or functions whose return values are used in other SQL
statements the same way that you use pre-installed MySQL functions like
pi().
Iyad H. Alshami – MOBC 2304
MySQL – Stored Procedure
• A stored procedure is a program with SQL code which is stored in the
database catalog and can be invoked later by a program, a trigger or even a
stored procedure.
• MySQL supports stored procedure since version 5.0 to allow MySQL more
flexible and powerful.
Iyad H. Alshami – MOBC 2304 22
MySQL – Stored Procedure
Iyad H. Alshami – MOBC 2304 23
MySQL – Stored Procedure
Optional Characteristics
• Type: Procedure/Function
• Language :the default value is SQL.
• Deterministic : If the procedure always returns the same results, given the same input.
• The default value is NOT DETERMINISTIC.
• SQL Security : At call time, check privileges of the user.
• INVOKER is the user who calls the procedure.
• DEFINER is the creator of the procedure, it is the default value.
• Comment : For documentation purposes;
• the default value is ""
Iyad H. Alshami – MOBC 2304
MySQL – Stored Procedure
CREATE PROCEDURE proc_IN (paramList)
BEGIN
Declare var datatype(size) DEFAULT default_value;
#Executable code
#SELECT var1 + 2 AS result;
END
Iyad H. Alshami – MOBC 2304 25
MySQL – Stored Procedure
Parameters
• Parameter list is empty
• CREATE PROCEDURE proc1 () :
• Define input parameter with IN:
• CREATE PROCEDURE proc1 (IN varname DATA-TYPE)
• The word IN is optional because parameters are IN (input) by default.
• Define output parameter with OUT:
• CREATE PROCEDURE proc1 (OUT varname DATA-TYPE)
• Define input and output parameters with INOUT:
• CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE)
Iyad H. Alshami – MOBC 2304
MySQL – Stored Procedure
CREATE PROCEDURE std_gps (IN sid INT(3))
BEGIN
SELECT AVG(grade)
FROM Regist_tbl
WHERE Regist_tbl.sid = sid;
END
Iyad H. Alshami – MOBC 2304 27
Procedure’s parameters
CREATE PROCEDURE proc_IN (IN var1 INT)
BEGIN
SELECT var1 + 2 AS result;
END
CREATE PROCEDURE proc_OUT(OUT var1 VARCHAR(100))
BEGIN
SET var1 = 'This is a test';
END
CREATE PROCEDURE proc_INOUT (IN var1 INT,OUT var2 INT)
BEGIN
SET var2 = var1 * 2;
END
Iyad H. Alshami – MOBC 2304
MySQL – Stored Procedure Calling
Call Procedure_name(par_values);
Example:
Call std_gpa(120);
Iyad H. Alshami – MOBC 2304 29
MySQL – Stored Procedure
• Disadvantages:
• Using many stored procedures, means more memory/CPU usage.
• using large number of logical operations inside stored procedures, will increase CPU usage since
database server is not well-designed for logical operations.
• Stored procedure are not designed for developing complex and flexible business logic.
• It is difficult to debug stored procedures.
• Only a few database management systems allow you to debug stored procedures.
• Unfortunately, MySQL does not provide facilities for debugging stored procedures.
• Developing and maintaining stored procedures often require a specialized skill set that not all
application developers possess.
• This may lead to problems in both application development and maintenance phases.
Iyad H. Alshami – MOBC 2304 30
Stored Functions
Iyad H. Alshami – MOBC 2304 31
MySQL – Stored Function
• A stored function is a special kind stored program that returns a single value.
• Stored functions can be used to encapsulate common formulas or business
rules that are reusable among SQL statements or stored programs.
• Different from a stored procedure stored function can be used a in SQL
statements wherever an expression is used.
• This helps improve the readability and maintainability of the procedural code.
Iyad H. Alshami – MOBC 2304 32
MySQL – Stored Function
Iyad H. Alshami – MOBC 2304 33
MySQL – Stored Function
CREATE FUNCTION function_name(paramList)
RETURNS datatype(size)
BEGIN
Declare var datatype(size);
• #Executable code
#SELECT var1 + 2 AS result;
RETURN var;
END;
• By default, all parameters are IN parameters.
• cannot specify IN , OUT or INOUT modifiers to the parameters.
• Must specify the data type of the return value in the RETURNS statement.
Iyad H. Alshami – MOBC 2304 34
MySQL – Stored Function
CREATE FUNCTION grade_level(grade Float(5,2))
RETURNS Varchar(10)
BEGIN
Declare level Varchar(10);
IF grade >=90 THEN
SET level = "Excellent";
ELSEIF grade >=80 THEN
SET level = "Very Good";
ELSEIF grade >=70 THEN
SET level = "Good";
ELSE
SET level = "Accepted";
END IF;
RETURN level;
END;
Iyad H. Alshami – MOBC 2304 35
MySQL – Stored Function Calling
• Calling a function within a procedure
SET var = grade_level(85);
• Calling a function within a SQL statement
SELECT sid, grade_Level(grade)
FROM Regist_tbl
WHERE sem = 17181;
Iyad H. Alshami – MOBC 2304 36
Triggers
Iyad H. Alshami – MOBC 2304 37
MySQL - Triggers
• A trigger is a program stored in the database and is called automatically when a
triggering event occurs.
• It is associated with a table, and that activates when a particular event occurs for
the table.
• Some uses for triggers are to perform checks of values to be inserted into a table or to
perform calculations on values involved in an update.
• A trigger is defined to activate when an INSERT, DELETE, or UPDATE statement
executes for the associated table.
• A trigger can be set to activate either before or after the triggering statement.
Iyad H. Alshami – MOBC 2304
MySQL - Triggers
Iyad H. Alshami – MOBC 2304 39
MySQL - Triggers
DELIMITER $$
CREATE TRIGGER triggerName
BEFORE|AFTER EVENT ON tableName
#EVENT is: INSERT, UPDATE or DELETE
FOR EACH ROW #optional
BEGIN
#executable code
END
Iyad H. Alshami – MOBC 2304 40
MySQL - Triggers
• BEFORE|AFTER EVENT ON tableName
• BEFORE|AFTER enable the developer to use OLD. and NEW. values
based on the determined EVENT.
• with insert event no OLD values, it just NEW values.
• with update event both of OLD & NEW values are exist.
• with delete event no NEW values, it just OLD values.
• Using BEFORE: means activate this trigger before executing the SQL
event.
• Using AFTER: means activate this trigger after executing the SQL
event.
Iyad H. Alshami – MOBC 2304 41
MySQL - Triggers
CREATE TABLE gpaUpdateLog(
user varchar(20),
udate date,
sid int(3),
oldgpa float(5,2),
newgpa float(5,2) DELIMITER $$
); CREATE TRIGGER gpaUpdate
BEFORE UPDATE ON students
FOR EACH ROW
BEGIN
INSERT INTO gpaUpdateLog
VALUES(USER,
NOW(),
OLD.sid,
OLD.gpa,
NEW.gpa);
END;
Iyad H. Alshami – MOBC 2304 42
Cursors
Iyad H. Alshami – MOBC 2304 43
Cursors
• A cursor is a pointer to a set of records returned by a SQL statement.
• It enables developer to take a set of records and deal with it on a row-by-row
basis.
• Cursor has three important properties:
• A cursor will not reflect changes in its source tables.
• A cursor is not updatable it is read-only
• A cursor is not scrollable, it can be traversed in forward direction only, forward
• Can not skip records from fetching.
Iyad H. Alshami – MOBC 2304
Defining and Using Cursors
• Declare cursor:
• DECLARE cursor-name CURSOR FOR SELECT ...;
• DECLARE CONTINUE HANDLER FOR NOT FOUND
• Specify what to do when no more records found
• DECLARE b INT;
• DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
• Open cursor:
• OPEN cursor-name;
• Fetch data into variables:
• FETCH cursor-name INTO variable [, variable];
• CLOSE cursor:
• CLOSE cursor-name;
Iyad H. Alshami – MOBC 2304
Cursor Example
#using cursor to sum salary for male employees
DELIMITER $$
CREATE Procedure maleSalarySum(OUT sumSalary Decimal(10,2))
BEGIN
DECLARE Sal, sumSal decimal(10,2);
DECLARE continueFlag int default 0;
DECLARE maleCursor CURSOR FOR SELECT Salary FROM emps where sex='M';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET continueFlag = 1;
OPEN maleCursor;
SET Sal = 0;
SET sumSal= 0;
WHILE continueFlag = 0 DO
FETCH maleCursor INTO Sal;
IF continueFlag = 0 THEN
SET sumSal = sumSal+Sal;
END IF;
END WHILE;
CLOSE maleCursor;
SET sumSalary = sumSal;
END
Iyad H. Alshami – MOBC 2304
A procedure to create email list using cursor
DELIMITER $$
DROP PROCEDURE IF EXISTS emailgroup;
CREATE PROCEDURE emailgroup (INOUT emailList varchar(4000))
BEGIN
DECLARE continueFlag INT DEFAULT 0;
DECLARE useremail varchar(100) DEFAULT "";
DECLARE email_cursor CURSOR FOR SELECT email FROM users;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET continueFlag = 1;
OPEN email_cursor;
WHILE continueFlag = 0 DO
FETCH email_cursor INTO useremail;
IF continueFlag = 0 THEN
SET emailList = CONCAT(useremail,";",emaillist);
END IF;
END WHILE;
CLOSE email_cursor;
END
Iyad H. Alshami – MOBC 2304
Assignment 1
• Write a report supported with examples about MySQL Error
Handling and how to use it in storied routines.
• Submission allowed until: Tuesday 25/02/2020 23:55 via Moodle.
Iyad H. Alshami – MOBC 2304 48
That is enough for Procedural SQL Programming
• Next Chapter will be
Ch16: “Disk Storage, Basic File Structures, Hashing, and Modern Storage Architectures”
Iyad H. Alshami – MOBC 2304 49