The routine definition the default is not

the routine definition, the default is NOT DETERMINISTIC
•
Determinism is important because there are restrictions in how
functions (use defined and inbuilt), views and procedures can be used.
Determinism has implications for database Indexing and security.

Selection: IF
•
Conditionally execute code
IF expression THEN
statements ;
ELSE
else-statements ;
END IF
•
OR
IF expression THEN
statements ;
ELSEIF elseif-expression THEN
elseif-statements ;
...
ELSE
else-statements ;
END IF;
•
OR (MySQL)
SELECT IF ( expression , value_if_true , value_if_false )

Selection: IF examples
•
PL/SQL
I F monthly_value <= 4000 THEN
ILevel := 'Low Income';
ELSIF monthly_value > 4000 and monthly_value <= 7000 THEN
ILevel := 'Avg Income';
ELSIF monthly_value > 7000 and monthly_value <= 15000 THEN
ILevel := 'Moderate Income';
ELSE
ILevel := 'High Income';
END IF;
•
Transact-SQL
IF DATENAME (weekday, GETDATE())
IN (N'Saturday', N'Sunday’)
SELECT 'Weekend’;
ELSE
SELECT 'Weekday';
•
MySQL
SELECT IF(500<1000, "YES", "NO");

Selection: CASE
•
Conditionally execute code
CASE case_expression
WHEN when_expression_1 THEN commands
WHEN when_expression _2 THEN commands
...
ELSE commands
END CASE;

Selection: CASE example
•
MySQL, PL/SQL and Transact-SQL
SELECT SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN "The quantity is
greater than 30"
WHEN Quantity = 30 THEN "The quantity is 30"
ELSE "The quantity is under 30"
END
FROM OrderDetails;

Iteration: WHILE
•
Repeatedly execute code
WHILE expression DO
statements ;
END WHILE
•
Or
REPEAT
statements ;
UNTIL expression
END REPEAT
•
Or (PL/SQL)
WHILE condition
LOOP
{...statements...}
END LOOP;

Selection: while example
•
Transact-SQL
WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300
BEGIN
UPDATE Production.Product SET ListPrice = ListPrice * 2
IF (SELECT MAX(ListPrice) FROM Production.Product) > $500
BREAK
ELSE
CONTINUE
END
•
PL/SQL
WHILE monthly_value <= 4000
LOOP
monthly_value := daily_value * 31;
END LOOP;
•
MySQL
WHILE
v1 > 0
DO
...
SET v1 = v1 - 1;
END WHILE ;

Overview
•
Review
•
Sub queries
•
SQL Functions and Relational Operators
•
Virtual Tables – Creating views
•
Transaction Control Language (TCL) - Transactions
•
Stored Procedures and Functions
•
Procedural Language
•
Stored Procedures
•
User defined Functions
•
IF, CASE and WHILE
•
Demonstrations
30

Demonstrations
31

References
•
Coronel, C. & Morris, S. 2018, Database Systems: Design Implementation and
Management , 13
th
Edn, Cengage Learning
•
Elmasri, R. & Navathe, S., 2011, Fundamentals of Database Systems , 6
th
Edn, Pearson
•
MySQL Reference Manual
•
W3schools.com
• Microsoft Transact-SQL Reference
•
Tech on the Net – Oracle / PLSQL
32