STORED PROCEDURES
STORED PROCEDURES
� Database program modules that are stored and executed by
the DBMS at the server
DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
SELECT * FROM products;
END //
DELIMITER ;
WHY STORED PROCEDURES
� Reduces Duplication of effort and improves software modularity
⚫ Multiple applications can use the stored procedure vs. the SQL statements
being stored in the application language (PHP)
Reduces communication and data transfer cost between client and
server (in certain situations)
⚫ Instead of sending multiple lengthy SQL statements, the application
only has to send the name and parameters of the Stored Procedure
Can be more secure than SQL statements
⚫ Permission can be granted to certain stored procedures without
granting access to database tables
DISADVANTAGES OF STORED PROCEDURES
� Difficult to debug
⚫ MySQL does not provide ways for debugging stored procedures
Many stored procedures can increase memory use
⚫ The more stored procedures you use, the more memory is used
Can be difficult to maintain and develop stored procedures
⚫ Another programming language to learn
CREATING STORED PROCEDURES
DELIMITER //
CREATE PROCEDURE NAME
BEGIN
SQL STATEMENT
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
SELECT * FROM products;
END //
DELIMITER ;
STORED PROCEDURE IN WORKBENCH
STORED PROCEDURE IN WORKBENCH
CALLING STORED PROCEDURES
CALL STORED_PROCEDURE_NAME
CALL GetAllProducts();
VARIABLES
�A variable is a name that refers to a value
A name that represents a value stored in the computer
memory
PHP
$name = “Jason”
$age = 5;
MySQL
DECLARE name VARCHAR(255)
DECLARE age INT
PARAMETERS
� There may be times where you want to pass information to
the stored procedures
⚫ Getting user input from a form and using that input in a SQL
statement
THREE TYPES OF PARAMETERS
� IN
⚫ Default
� OUT
� INOUT
IN PARAMETER
� Calling program has to pass an argument to the stored
procedure.
ARGUMENTS AND PARAMETERS
Defining
DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
BEGIN
SELECT * FROM offices WHERE country = countryName;
END //
DELIMITER ;
Calling
CALL GetOfficeByCountry('USA')
The values being copied from the calling stored procedure are calling arguments.
The variables being copied into are called parameters.
THREE TYPES OF PARAMETERS
� IN
⚫ Default
� OUT
� INOUT
OUT 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
� OUT is a keyword
OUT PARAMETER
Defining
DELIMITER //
CREATE PROCEDURE CountOrderByStatus(IN orderStatus VARCHAR(25),
OUT total INT)
BEGIN
SELECT count(orderNumber) INTO total FROM orders WHERE status =
orderStatus;
END//
DELIMITER ;
Calling
CALL CountOrderByStatus('Shipped',@total);
SELECT @total;
The out parameter is used outside of the stored
procedure.
THREE TYPES OF PARAMETERS
� IN
⚫ Default
� OUT
� INOUT
CONDITIONALS
$sql = "select * from products WHERE quantityInStock <
'".$quantityInStock ."’;
$sql = "select * from products WHERE quantityInStock >
'".$quantityInStock ."’;
Could we have one call to the database instead of two?
CONDITIONALS
THE “IF” STATEMENT
Mysql Syntax
IF if_expression THEN commands
[ELSEIF elseif_expression THEN commands]
[ELSE commands]
END IF;
First line is known as the IF clause
Includes the keyword IF followed by condition followed by the
keyword THEN
When the IFstatement executes, the condition is tested, and if it
is true the block statements are executed. Otherwise, block
statements are skipped
“IFEXPRESSION”: BOOLEAN EXPRESSIONS AND
OPERATORS
IF STATEMENT
DELIMITER //
CREATE PROCEDURE
GetProductsInStockBasedOnQuantitityLevel(IN p_operator
VARCHAR(255), IN p_quantityInStock INT)
BEGIN
IF p_operator = "<" THEN
select * from products WHERE quantityInStock <
p_quantityInStock;
ELSEIF p_operator = ">" THEN
select * from products WHERE quantityInStock >
p_quantityInStock;
END IF;
END //
DELIMITER ;
IF STATEMENT
� CREATE PROCEDURE
GetProductsInStockBasedOnQuantitityLevel
(IN p_operator VARCHAR(255), IN p_quantityInStock INT)
The ooperator > or < The number in stock
THE IF STATEMENT
IF p_operator = "<" THEN
select * from products WHERE quantityInStock <
p_quantityInStock;
ELSEIF p_operator = ">" THEN
select * from products WHERE quantityInStock >
p_quantityInStock;
END IF;
LOOPS
� While
� Repeat
� Loop
Repeats a set of commands until some condition is met
Iteration: one execution of the body of a loop
If a condition is never met, we will have an infinite
loop
WHILE LOOP
WHILE expression DO
Statements
END WHILE
The expression must evaluate to
true or false
while loop is known as a pretest loop
Tests condition before performing an iteration
Will never execute if condition is false to start with
Requires performing some steps prior to the loop
INFINITE LOOPS
� Loops must contain within themselves a way to terminate
⚫ Something inside a while loop must eventually make the
condition false
� Infinite loop: loop that does not have a way of stopping
⚫ Repeats until program is interrupted
⚫ Occurs when programmer forgets to include stopping
code in the loop
WHILE LOOP
DELIMITER //
CREATE PROCEDURE WhileLoopProc()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
WHILE x <= 5 DO
SET str = CONCAT(str,x,',');
SET x = x + 1;
END WHILE;
SELECT str;
END//
DELIMITER ;
WHILE LOOP
� Creating Variables
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
WHILE LOOP
WHILE x <= 5 DO
SET str = CONCAT(str,x,',');
SET x = x + 1;
END WHILE;
