Database Stored Operations
Procedures & Triggers

Jerzy Letkowski, Ph.D.

Outline

· Database

· Tables

· Stored Operations

· MySQL Procedure Definition

· Stored Procedure Examples

· MySQL Trigger Definition

· Trigger Examples

· Summary

· References

DATABASE

Some of the procedures presented in this document require the Furniture database. Use the db.sql script to recreate the database if necessary. In class, run the cdb.bat batch command in the lecture/09-StoredProceduresTriggers/Data folder.

TABLES

The following lines describe briefly the tables and fields in the updated Furniture database:

US_State
[id{pk}, state, region, division]

Customer
[cid{pk}, name, address, city, state{fk→US_State.id}, zip]

PurchaseOrder
[poid{pk}, poDate, cid{fk→Customer.cid}]

Product
[pid{pk}, description, finish, unitPrice, onHand]

OrderLine
[quantity,(poid{fk→PurchaseOrder.poid}, pid{fk→Product.poi}){pk}]

Notice that token {pk} stands for a Primary Key and token {fk} represents a Foreign Key. Also notice that keyword fk is followed by the table name and column name to which the foreign key is related. For example, in table Customer, column state is a foreign key associated with primary key US_State.id which is all written as state{fk→US_State.id}.

Some database references use even shorter notation, in which the primary keys are underlined with a solid line and the foreign keys use font italics. Such a notation in more compact and is very helpful in developing SQL queries and stored operations. Here is how the Furniture database can be expressed using such a notation:

US_State
[id, state, region, division]

Customer
[cid, name, address, city, state, zip]

PurchaseOrder
[poid, poDate, cid]

Product
[pid, description, finish, unitPrice, onHand]

OrderLine
[quantity, poid, pid]

Notice that in table OrderLine, columns poid and pid are foreign keys (shown in italics) but together they serve as a composite primary key (shown underlined: poid, pid).

STORED OPERATIONS

Stored procedures and functions are referred to in SQL as Persistent Stored Modules (PMS) [SQL 2003]. They are program modules stored and executed by the DBMS at the database server.

Functions are persistent operations that are expected to return a value (object).

Procedures are usually dedicated to performing complex operations without returning any value (object). Nonetheless, they also can return data indirectly via their arguments.

Compare the following operation signatures:

SQL:
FUNCTION function_name([arg1 data-type1, . . .])
. . .
RETURN . . .

PROCEDURE procedure_name([arg-type1 arg1 data-type1, . . .])

Python:
def function_name ([arg1, . . . ]):
. . .
return . . .

def procedure _name ([arg1, . . . ]):
. . .

Java:

type function_name ([type1 arg1, . . .])

void procedure _name (([type1 arg1, . . .])

Note that the square brackets ([ ]) signify optionality of the arguments.

Advantages:

· PSMs can be invoked by programs written in different languages.

· PSMs save bandwidth and increase communication speed between applications and DBMS.

· PSMs provide access to complex and tested database operations that otherwise would require very high level of SQL and programming expertise.

Disadvantages:

· PSMs put more workload at the database server.

· PSMs require better (advanced) [programming] expertise.

MySQL PROCEDURE DEFINITION

A stored procedure is a named program capable to perform database operations within the database system. It supports typical programming structures like variables, statements, blocks, decisions (conditional execution) and repetitions (loops).

The following is a minimized definition of the MySQL procedure:

CREATE PROCEDURE PROCEDURE_NAME([argument_list])
BEGIN
procedure_body
END

The argument_list consists of triples:
arg_type, arg_name, arg_data_type

The arg_type element is one of the following:
IN, OUT, or INOUT.

The arg_name element is the name of a local variable that can do one of the following:
- receive a value from a calling statement (IN)
- return a value back to the calling statement (OUT).
- do both, receive and return a value (INOUT).

The arg_data_type element defined the data type of the argument (INT, FLOAT, CHAR, VARCHAR, etc.).

The procedure_body segment consists of SQL-procedure statements such as:
- declaration, for example:
    DECLARE v_name VARCHAR(64);
- assignment, for example:
    SET v_name = 'John';
- SQL retrieval statement, for example:
SELECT name INTO v_name FROM Customer WHERE pid=5;
FETCH cursor_name INTO variable_name;
- control structures such as:
      IF – THEN – ELSE - END IF
      IF – THEN – ELSEIF – . . . – ELSE – END IF
      CASE – WHEN – ELSE – END CASE
      loop_id:LOOP – LEAVE loop_id – END LOOP loop_id
      loop_id:REPEAT – UNTIL - END REPEAT loop_id
      loop_id:WHILE – DO - END WHILE loop_id.

PROCEDURE EXAMPLE 1

Purpose: Update the unit price (unitPrice) in table Product for a given product ID (pid). This procedure uses one table:

Product
[pid, description, finish, unitPrice, onHand]

Implementation 1

Perform an unconditional update!

USE Furniture;

DROP PROCEDURE IF EXISTS pCHANGE_PRICE;

DELIMITER $$$

CREATE PROCEDURE pCHANGE_PRICE (

IN a_pid INT,

IN a_price FLOAT

)

BEGIN

UPDATE PRODUCT SET unitPrice =a_price WHERE pid=a_pid;

END

$$$

DELIMITER ;

Notice that the name the procedure is [conventionally] prefixed with letter p (for procedure).

Let us try this procedure. We are going to change the unit price (unitPrice) in table Product for product ID = 3 (pid = 3). First check the current record of the product:

SELECT * FROM PRODUCT WHERE pid=3;

pid description finish unitPrice onHand

3 Computer Desk Natural Ash 375 5

Now, change unit price of the product, having ID = 3, to 349.99:

CALL pCHANGE_PRICE(3,349.99);

Finally, check the record of the product again:

SELECT * FROM PRODUCT WHERE pid=3;

pid description finish unitPrice onHand

3 Computer Desk Natural Ash 349.99 5

The change has been successfully executed.

Implementation 2

Suppose that there is a business rule, stating that the unit product price can’t exceed $10,000. The following procedure invocation would violate the rule:

CALL pCHANGE_PRICE(3,12780.99);

Perform a conditional update that will defend the business rule!

USE Furniture;

DROP PROCEDURE IF EXISTS UPDATE_PRICE;

DELIMITER $$$

Notice that the procedure’s definition will be terminated with this delimiter (here $$$).

CREATE PROCEDURE pUPDATE_PRICE (

IN a_pid INT,

IN a_price FLOAT

)

BEGIN

IF a_price > 0 AND a_price <= 10000 THEN

UPDATE PRODUCT

SET unitPrice = a_price
WHERE pid = a_pid;

ELSE

SELECT a_price AS 'invalidPrice',
a_pid AS 'productID';

END IF;

END

$$$

Restore the delimiter back to the semi-colon (;).

DELIMITER ;

-- Attempt the following call:

CALL pUPDATE_PRICE(3,-100.99);

invalidPrice productID

-100.99 3

-- Also try the following call:

CALL pUPDATE_PRICE(3,11000.99);

invalidPrice productID

11000.99 3

-- Also try the following call:

CALL pUPDATE_PRICE(3,328.5);

Query OK, 1 row affected (0.17 sec)

Discussion for Procedure Implementation 2

Suppose that you want to update the product price attribute (Product.unitPrice). For example, product ID = 8 should have price 250.

Using plain SQL:

UPDATE PRODUCT SET unitPrice = 250.99 WHERE pid = 8;

This is not a very complex SQL. Nonetheless, it is somewhat long. Needless to say, accidents happen so we might do this (set the price to a negative value):

UPDATE PRODUCT SET unitPrice = -250.99 WHERE pid = 8;

DBMS blindly accepts such an update.

Doing it via a stored procedure, you can filter out obvious mistakes. Plus it is simpler to invoke a stored procedure:

CALL pUPDATE_PRICE (8,250.99);

Let us dissect the procedure line by line:

DROP PROCEDURE IF EXISTS pUPDATE_PRICE;

This statement is optional but convenient. It is useful if you want to create the same procedure more than once.

DELIMITER $$$

Statements inside procedures are separated by a semicolon. Therefore we need a different separator to delimit the entire procedure.

Note: I believe MySQL did not have to “incorporate” such a statement. Oracle gets away without it. See for example at:

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-plsql.html

CREATE PROCEDURE pUPDATE_PRICE(
IN a_pid INT,
IN a_price FLOAT
)

This is a signature of the procedure. It defines the procedure's name and arguments. The name is used later to invoke the procedure, the arguments allow us to pass data to and from the procedure. In this example both the arguments are INPUT (IN) arguments. The procedure receives data via these arguments and uses them internally. It does not return anything back to the environment from which is was invoked. The arguments are forgotten right after the procedure is terminated.

Generally, a procedure may have IN, OUT or INOUT arguments. In addition to the data flow specification, each argument must have a valid SQL type. In the above example, argument a_pid is of type INT and argument a_price is of type FLOAT. Notice that the name of the arguments start with characters a_. This is an informal convention that helps identify arguments (a) inside the procedure. Such an approach to naming variables is based to some extend on the so call Hungarian notation (https://en.wikipedia.org/wiki/Hungarian_notation). (A fully blown Hungarian notation would also indicate the data type. For example a_float_price would indicate that this variable in an argument of the FLOAT type.)

BEGIN

This is the beginning of the procedure’s body (code).

END

This is the end of the procedure’s body (code).

IF logical_expression THEN
. . .

ELSE

. . .

END IF;

This is a two-branch decision statement. If the logical_expression is TRUE, the statement(s) between IF and ELSE are executed. Otherwise, the statement(s) between ELSE and END IF; are carried out.

An IF statement can be nested. The ELSE block is optional. Without it, the IF statement is a one-branch decision structure.

UPDATE PRODUCT

SET unitPrice = a_price
WHERE pid = a_pid

The above Update statement takes the value of the unit price and the value of the product ID from the procedure’s arguments a_price and a_pid, respectively. For example, if you invoke the procedure as

CALL UPDATE_PRICE(8,300)

then the update statement will become:

UPDATE PRODUCT SET unitPrice=300 WHERE pid=8;

If the new price submitted by argument a_price is not positive or it exceeds 10000 then the update is not carried out and an error message is displayed, for example:

invalidPrice productID

11000.99 3

This message is produced by the following SELECT statement:

SELECT a_price AS 'invalidPrice',
a_pid AS 'productID';

Finally, the logical expression that validates the input arguments is an intersection of two relational expression:

a_price > 0 AND a_price <= 10000

It states that the value of argument a_price must be positive and (in the same time) less than or equal to 10000.

IF a_price > 0 AND a_price <= 10000 THEN

UPDATE PRODUCT

SET unitPrice = a_price
WHERE pid = a_pid;

ELSE

SELECT a_price AS 'invalidPrice',
a_pid AS 'productID';

END IF;

The entire IF – THEN - ELSE – END IF control structure can be describe in a simple English as:

If the price value submitted to the procedure via argument a_price is positive and up to 10000, then update the unit price for the product identified by the value of argument a_pid, otherwise display an error message.

Notice that in Microsoft Access, the procedure can be implemented as a saved parameter query:

UPDATE PRODUCT

SET unitPrice = [a_price]

WHERE pid = [a_pid];

Names in square brackets are interpreted in Access as parameters. Learn more about it in this Web page: Use parameters to ask for input when running a query.

Implementation 3

Perform a conditional update and indicate whether or not the update was carried out. Use an OUT argument to pass the state of the update back to the invoker.

USE Furniture;

DROP PROCEDURE IF pEXISTS MODIFY_PRICE;

DELIMITER $$$

CREATE PROCEDURE pMODIFY_PRICE (

IN a_pid INT,

IN a_price FLOAT,

OUT a_feedback BOOLEAN)

BEGIN

SET a_feedback = FALSE;

IF a_price > 0 AND a_price <= 10000 THEN

UPDATE PRODUCT

SET unitPrice = a_price
WHERE pid = a_pid;

SET a_feedback = TRUE;

END IF;

END

$$$

DELIMITER ;

Invocation example 1:

CALL pMODIFY_PRICE(10,129.99,@fbk);

SELECT @fbk;

@fbk

Invocation example 2:

CALL pMODIFY_PRICE(10,-129.99,@fbk);

SELECT @fbk;

@fbk

Notice that in MySQL, BOOLEAN TRUE is equivalent to 1 and FALSE—to 0.

PROCEDURE EXAMPLE 2

Purpose: Return a value via a parameter (IN and OUT).

Let us create this procedure in a new database called MYPROC. (This procedure does not use any tables.)

CREATE DATABASE IF NOT EXISTS MYPROC;

USE MYPROC;

DROP PROCEDURE IF EXISTS pROUND_TO_INTEGER;

DELIMITER ~~~

CREATE PROCEDURE pROUND_TO_INTEGER (

IN a_x DECIMAL(12,4),

OUT a_rounded_x DECIMAL(12,4))

BEGIN

SET a_rounded_x = Round(a_x,0);

END

~~~

DELIMITER ;

SET @x = 3.5;

SET @y = 0.0;

SELECT @x, @y;

CALL pROUND_TO_INTEGER (@x, @y);

SELECT @x, @y;

It is a good idea to save all your stored procedures in a plain-text file so you can review and, if necessary, modify any of your procedures. MySQL can also show procedures, using a SHOW CREATE PROCEDURE statement. For example:

SHOW CREATE PROCEDURE pROUND_TO_INTEGER \G

MySQL replies:

*************************** 1. row ***************************

Procedure: ROUND_TO_INTEGER

sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `ROUND_TO_INTEGER`(

IN a_x DECIMAL(12,4),

OUT a_rounded_x DECIMAL(12,4))

BEGIN

SET a_rounded_x = Round(a_x,0);

END

character_set_client: cp850

collation_connection: cp850_general_ci

Database Collation: utf8_general_ci

Notice the way the statement is terminated (\G). Why don’t you try to terminate it with a semi-colon? You will get a table-like output.

This procedure (pROUND_TO_INTEGER) takes input from argument a_x and returns output via parameter a_rounded_x.

Note that @x and @y are local variables that can be used to carry some data. You use the SET statement to assign values to such variables. You can then recycle them in other statements. The SET statement is also used inside procedures to assign values to variables.

PROCEDURE EXAMPLE 3 (a challenge)

Purpose: Write a stored procedure that will take two INPUT parameters:

- hourly rate (a_hr)

- hours worked (a_hw)

and return the weekly pay via OUT parameter a_ pay.

Here is how one would calculate such a pay in Excel:

=a_hr*a_hw+IF(a_hw>40,0.5*a_hr*(a_hw-40),0.0)

Note that this formula applies a 50% higher hourly rate for the overtime.

. . .

PROCEDURE EXAMPLE 4

Purpose: Submit and return a value via an INOUT parameter.

USE MYPROC;

DROP PROCEDURE IF EXISTS pROUND_TO_INT;

DELIMITER $$$

CREATE PROCEDURE pROUND_TO_INT (

INOUT a_x double

)

BEGIN

SET a_x = ROUND(a_x,0);

END$$$

DELIMITER ;

SET @x = 5.5;

SELECT @x;

CALL pROUND_TO_INT(@x);

SELECT @x;

This procedure takes input from argument a_x of type INOUT. The rounded value of this variable is set back to itself and thus it is returned back to the caller of this procedure.

PROCEDURE EXAMPLE 5

Purpose: Insert a record into table Product. This procedure uses one table:

Product
[pid, description, finish, unitPrice, onHand]

It performs an unconditional INSERT.

USE Furniture;

DROP PROCEDURE IF EXISTS pADD_PRODUCT;

DELIMITER $$$

CREATE PROCEDURE pADD_PRODUCT (

IN a_descr VARCHAR(50),

IN a_fin VARCHAR(40),

IN a_price FLOAT,

IN a_qoh INT)

BEGIN

DECLARE v_prid INT;

SELECT MAX(pid) INTO v_prid FROM Product;

SET v_prid = v_prid + 1;

INSERT INTO
Product (pid, description, finish, unitPrice, onHand)

VALUES(v_prid, a_descr, a_fin, a_price, a_qoh);

END$$$

DELIMITER ;

SELECT * FROM Product;

Call pADD_PRODUCT('Funny Chair','Alu',245.99,10);

Call pADD_PRODUCT('Comfy Chair','Pine',145.99,12);

SELECT * FROM Product;

This procedure only takes input from arguments a_descr, a_fin, a_price, a_qoh. It then executes an INSERT statement.

Note the role of this procedure is to simplify the syntax. One special feature of this procedure is to figure out the next product ID.

To find out the next product ID, procedure pADD_PRODUCT declares an internal (local) variable:

DECLARE v_prid INT;

Notice that inside the procedure’s body you do not have to use the “@” character.

Next, the procedure uses a special syntax (INTO) in order to select the highest value of pid. The Select statement can redirect output to a variable. Here:

SELECT MAX(pid) INTO v_prid FROM Product;

Finally, the variable is raised by 1:

SET v_prid = v_prid + 1;

Along with the argument values, the variable is used to produce the INSERT statement:

INSERT INTO
Product (pid, description, finish, unitPrice, onHand)

VALUES(v_prid, a_descr, a_fin, a_price, a_qoh);

Note that this technique is probably a better way to implement an AUTO_INCREMENT.

PROCEDURE EXAMPLE 6

Purpose: Insert a record into table OrderLine. This procedure uses three tables:

PurchaseOrder
[poid, poDate, cid]

Product
[pid, description, finish, unitPrice, onHand]

OrderLine
[quantity, poid, pid]

Using values of three input (IN) arguments (a_quantity, a_poid, a_pid) the procedure is expected to add a record to table OrderLine. It performs a conditional INSERT statement that satisfies the following business rule:

If the values of arguments a_poid, a_pid are valid and the quantity ordered (a_quantity) is less than or equal to the quantity on hand, Product.onHand, then the order line record gets inserted into table OrderLine and the quantity on hand is decreased by the value of the quantity ordered (a_quantity). Otherwise a message is returned, indicating that the transaction can’t be executed. Arguments a_poid, a_pid are valid if they match purchasing order ID (PurchaseOrder.poid) and product ID (Product.pid), respectively.

The state of the procedure’s execution is returned by output (OUT) argument a_feedback.

Let’s first do some analysis of the conditions imposed by the business rule and find out how the conditions can be checked using just SQL queries.

How do we know if some value of the purchasing order ID is valid? There are a few ways to find it out. For example, let’s check poid=1:

SELECT * FROM PurchaseOrder WHERE poid=1;

MySQL replies:
Empty set (0.00 sec)

We could also use the COUNT() function:

SELECT count(*) FROM PurchaseOrder WHERE poid=1;

MySQL replies:
count(*)
0
Thus, if the count() function returns 0 then the value of poid is invalid. Otherwise it is valid (it exists).

Here is another way to run this query:
SELECT count(*) INTO @v_po_cnt
FROM PurchaseOrder
WHERE poid=1;
We can now check variable @v_po_cnt:

SELECT @v_po_cnt;

MySQL replies ():
@v_po_cnt
0

Notice that 0 means here FALSE. @v_po_cnt = 0 indicates that the purchasing order ID is invalid.

Another way of running the test would be to use an existential query:

SELECT EXISTS

(SELECT * FROM PurchaseOrder WHERE poid=1001)

INTO @poid_exists;

The second and third methods are actually better options as they can be easily adopted by the procedure.

In a similar way we can check if there is a record in table Product for a given product ID (pid). Using the count method we have (for pid = 1):

SELECT count(*) INTO @v_prd_cnt
FROM Product
WHERE pid=1;

We can now examine variable @v_prd_cnt:

SELECT @v_prd_cnt;

MySQL replies:
@v_prd_cnt
1

Finally, the third part of the business rule states that the quantity on hand must be at least equal to the order quantity. Suppose that the order quantity is 2 and the product ID is 3. Using an existential query, we check this rule as:

SELECT EXISTS

(SELECT * FROM Product WHERE pid=3 and onHand >= 2)

INTO @order_doable;

We can examine variable @order_doable:

SELECT @order_doable;

The specific values (3 for pid and 2 for onHand ) can be replaced with variables:

SET @v_pid = 3;

SET @v_qty = 2;

SELECT EXISTS

(SELECT * FROM Product WHERE pid = @v_pid and onHand >= @v_qty)

INTO @order_doable;

SELECT @order_doable;

MySQL replies:
@order_doable
1

Thus ordering quantity for product ID = 3 is feasible.

Here is how we can wrap the above tests by a stored procedure:

USE Furniture;

DROP PROCEDURE IF EXISTS pADD_ORDER_LINE;

DELIMITER $$$

CREATE PROCEDURE pADD_ORDER_LINE (

IN a_qty INT,

IN a_poid INT,

IN a_pid INT,

OUT a_feedback VARCHAR(32)

)

paol:BEGIN

-- Declare local variables to capture outcomes of

-- the testing queries.

DECLARE v_po_cnt INT;

DECLARE v_prd_cnt INT;

DECLARE v_order_doable BOOLEAN;

-- Set the feedback variable optimistically to 'OK'

SET a_feedback = 'OK';

-- Check if the purchasing order exists:

SELECT count(*) INTO v_po_cnt

FROM PurchaseOrder WHERE poid = a_poid;

IF v_po_cnt = 0 THEN

SET a_feedback = CONCAT('poid=',a_poid,' is invalid!');

LEAVE paol;
END IF;

-- Check if the product exists:

SELECT count(*) INTO v_prd_cnt

FROM Product WHERE pid = a_pid;

IF v_prd_cnt = 0 THEN

SET a_feedback = CONCAT('pid=',a_pid,' is invalid!');

LEAVE paol;
END IF;

-- Check if the order transaction is doable.

SELECT EXISTS

(SELECT * FROM Product WHERE pid = a_pid and onHand >= a_qty)

INTO v_order_doable;

IF NOT v_order_doable THEN

SET a_feedback = CONCAT('order-qty=', a_qty,' is too high!');

LEAVE paol;
END IF;

-- At this point we can roll the transaction:

UPDATE Product

SET onHand = onHand - a_qty WHERE pid = a_pid;

INSERT INTO OrderLine(quantity, poid, pid)

VALUES(a_qty, a_poid, a_pid);

END$$$

DELIMITER ;

Test the procedure.

-- Run a few invalid calls:

Call pADD_ORDER_LINE (2,1,5,@feedback);

SELECT @feedback;

Call pADD_ORDER_LINE (2,1008,20,@feedback);

SELECT @feedback;

Call pADD_ORDER_LINE (15,1008,5,@feedback);

SELECT @feedback;

-- Check the state of involved records before the call:

SELECT * FROM PurchaseOrder WHERE poid = 1008;

SELECT * FROM Product WHERE pid = 5;

SELECT * FROM OrderLine WHERE poid = 1008;

-- Call the procedure to order 2 products
-- within purchase order 1008, for pid = 5:

Call pADD_ORDER_LINE (2,1008,5,@feedback);

-- Find out if the transaction succeeded.

SELECT @feedback;

-- Check the state of involved records after the call:

SELECT * FROM PurchaseOrder WHERE poid = 1008;

SELECT * FROM Product WHERE pid = 5;

SELECT * FROM OrderLine WHERE poid = 1008;

-- Undo the above transaction

UPDATE Product SET onHand = 6 WHERE pid = 5;

DELETE FROM OrderLine WHERE poid=1008 AND pid=5;

Notice how the procedure is terminated when an invalid state is detected:

LEAVE paol;
Such an exit statement uses an identifier of the procedure’s body that is defined right before the BEGIN statement:

paol:BEGIN

This is a user-defined identifier (here it stands for pADD_ORDER_LINE).

The last two SQL statements of the procedure (UPDATE and INSERT) are the core of this procedure. They would suffice if one could be sure that all input values are always valid. The entire procedure would then be reduced to:

CREATE PROCEDURE pADD_ORDER_LINE (

IN a_qty INT,

IN a_poid INT,

IN a_pid INT,

OUT a_feedback VARCHAR(32)

)

BEGIN

UPDATE Product

SET onHand = onHand - a_qty WHERE pid = a_pid;

INSERT INTO OrderLine(quantity, poid, pid)

VALUES(a_qty, a_poid, a_pid);

END

As you can see validation code makes the procedure more complicated but, in the end, it is absolutely necessary.

PROCEDURE EXAMPLE 7

Purpose: Get the named day of the week, using control statement IF-THEN-ELSEIF-END IF.

Let us create this procedure in a new database called MYPROC. (This procedure does not use any tables.)

CREATE DATABASE IF NOT EXISTS MYPROC;

USE MYPROC;

DROP PROCEDURE IF EXISTS pNAME_OF_DAY;

DELIMITER ~~~

CREATE PROCEDURE pNAME_OF_DAY(

IN a_day_num INT,

OUT a_day_name VARCHAR(9)

)

BEGIN

IF a_day_num = 1 THEN

SET a_day_name = 'Monday';

ELSEIF a_day_num = 2 THEN

SET a_day_name = 'Tuesday';

ELSEIF a_day_num = 3 THEN

SET a_day_name = 'Wednesday';

ELSEIF a_day_num = 4 THEN

SET a_day_name = 'Thursday';

ELSEIF a_day_num = 5 THEN

SET a_day_name = 'Friday';

ELSEIF a_day_num = 6 THEN

SET a_day_name = 'Saturday';

ELSEIF a_day_num = 7 THEN

SET a_day_name = 'Sunday';

ELSE

SET a_day_name = NULL;

END IF;

END~~~

DELIMITER ;

CALL pNAME_OF_DAY(3,@nd);

SELECT @nd;

This one is an example for using a nested If statement. Why don’t you try to implement it without ELSEIF.

This procedure takes input from argument a_day_num and returns output via argument a_day_name. It returns NULL if the day number (a_day_num) is incorrect.

PROCEDURE EXAMPLE 8

Purpose: Get the named day of the week, using control statement CASE.

Let us create this procedure in a new database called MYPROC. (This procedure does not use any tables.)

CREATE DATABASE IF NOT EXISTS MYPROC;

USE MYPROC;

DROP PROCEDURE IF EXISTS pDAY_NAME;

DELIMITER ~~~

CREATE PROCEDURE pDAY_NAME(

IN a_day_num INT,

OUT a_day_name VARCHAR(9)

)

BEGIN

CASE a_day_num

WHEN 1 THEN

SET a_day_name = 'Monday';

WHEN 2 THEN

SET a_day_name = 'Tuesday';

WHEN 3 THEN

SET a_day_name = 'Wednesday';

WHEN 4 THEN

SET a_day_name = 'Thursday';

WHEN 5 THEN

SET a_day_name = 'Friday';

WHEN 6 THEN

SET a_day_name = 'Saturday';

WHEN 7 THEN

SET a_day_name = 'Sunday';

ELSE

SET a_day_name = NULL;

END CASE;

END~~~

DELIMITER ;

CALL pDAY_NAME(3,@nd);

SELECT @nd;

This example is similar to the previous one. Instead of statement IF-THEN-ELSEIF-END IF, it uses statement CASE–WHEN–ELSE–END CASE.

This procedure takes input from argument a_day_num and returns output via argument a_day_name. It returns NULL if the day number (a_day_num) is incorrect.

Question:
Do we need control statements to figure out the named day of the week. How would you do it with an input argument of type DATETIME?

Complete the following procedure:

CREATE DATABASE IF NOT EXISTS MYPROC;

USE MYPROC;

DROP PROCEDURE IF EXISTS pWEEKDAY_NAME;

DELIMITER ~~~

CREATE PROCEDURE pWEEKDAY_NAME(

IN a_day_num DATETIME,

OUT a_day_name VARCHAR(9)

)

BEGIN

SET a_day_name = ?

END~~~

DELIMITER ;

CALL pDAY_NAME(3,@nd);

SELECT @nd;

MYSQL TRIGGER DEFINITION

A trigger is a specialized stored procedure that is executed in response to a database change (record insert, delete or update) event.

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name trigger_time trigger_event

ON tbl_name FOR EACH ROW trigger_stmt

trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after each row to be modified.

trigger_event indicates the kind of statement that activates the trigger. The trigger_event can be one of the following: INSERT, DETETE, UPDATE.

trigger_stmt is the statement block to execute when the trigger activates.

To find out which triggers are defined in the current database enter command:

SHOW TRIGGERS;

“Triggers can be very great things and very dangerous things. Dangerous in the sense that they are tricky to debug, but powerful because no update to a table with a trigger can easily escape the trigger.”[4]

TRIGGER EXAMPLE 1

Purpose: Backup a record that is being removed from table Product.

First, create a backup table, OldProduct:

CREATE TABLE OldProduct LIKE Product;

Now, define the trigger:

DELIMITER ~~~

Drop TRIGGER IF EXISTS tDELETE_PRODUCT;

CREATE TRIGGER tDELETE_PRODUCT

BEFORE DELETE ON Product FOR EACH ROW

BEGIN

INSERT INTO OldProduct

VALUES(Old.pid, Old.description,
Old.finish, Old.unitPrice, Old.onHand);

END~~~

DELIMITER ;

Show the existing Product record, having ID = 10:

SELECT * FROM Product WHERE pid = 10;

MySQL replies:
pid description finish unitPrice onHand

10 Computer Chair Oak 129.99 6

Show the existing OldProduct record, having ID = 10:

SELECT * FROM OldProduct WHERE pid = 10;

MySQL replies:
Empty set (0.00 sec)

Attempt to delete Product record, having ID = 10 (this action should automatically invoke the trigger):

DELETE FROM Product WHERE pid = 10;

MySQL replies:
Query OK, 1 row affected (0.13 sec)

Show the Product record, having ID = 10:

SELECT * FROM Product WHERE pid = 10;

MySQL replies:
Empty set (0.00 sec)

Show the existing OldProduct record, having ID = 10:

SELECT * FROM OldProduct WHERE pid = 10;

MySQL replies:
pid description finish unitPrice onHand

10 Computer Chair Oak 129.99 6

Thus the trigger works just fine!

Optionally, undo this change:

INSERT INTO Product
SELECT * FROM OldProduct WHERE pid = 10;

DELETE FROM OldProduct WHERE pid = 10;

TRIGGER EXAMPLE 2

Purpose: Validate data entry for table PurchaseOrder. Make sure that the purchase order date (poDate) is equal to the current date or the day before.

Define the trigger associated with an INSERT operations:

DELIMITER ~~~

Drop TRIGGER IF EXISTS tINSERT_PO;

CREATE TRIGGER tINSERT_PO

BEFORE INSERT ON PurchaseOrder FOR EACH ROW

BEGIN

DECLARE v_dd INT;

SET v_dd = DATEDIFF(CURRENT_DATE(),New.poDate);

IF v_dd < 0 OR v_DD > 1 THEN

Set New.poid = NULL;

END IF;

END~~~

DELIMITER ;

This trigger (stored procedure) is executed before an INSERT statement for table PurchaseOrder is carried out. It first calculates the difference between the current date returned by function CURRENT_DATE() and the date provided by the INSERT statement. Notice that object New captures the values of the INSERT statement. In particular the purchase order date (poDate) is stored as New.poDate.

If the date difference is negative or greater than 1, the new date is invalid and the INSERT statement should be ignored. Since there is no direct way to force MySQL not to execute the INSERT statement, a simple trick of attempting to set the primary key to NULL (Set New.poid = NULL;) is applied. Since a primary key can’t be set to NULL, the INSERT statement fails (mission accomplished).

Let us examine this trigger!

Show the existing PurchaseOrder record, having ID = 1013:

SELECT * FROM PurchaseOrder WHERE poid = 1013;

MySQL replies:
Empty set (0.00 sec)

Why don’t you try to add a new record to table PurchaseOrder, having ID = 1013 and a wrong date:

INSERT INTO PurchaseOrder(poid, poDate, cid)
VALUES(1013, CURRENT_DATE() + INTERVAL 1 DAY, 13);

MySQL replies:
ERROR 1048 (23000): Column 'poid' cannot be null

Why don’t you try to add a new record to table PurchaseOrder, having ID = 1013 and a correct date:

INSERT INTO PurchaseOrder(poid, poDate, cid)
VALUES(1013, CURRENT_DATE() - INTERVAL 1 DAY, 13);

MySQL replies:
Query OK, 1 row affected . . .

Let us retrieve this record from table PurchaseOrder:

SELECT * FROM PurchaseOrder WHERE poid = 1013;

MySQL replies:
poid poDate cid

1013 2016-11-20 00:00:00 13

The trigger did not complain this time.

When a trigger catches an error is will cause MySQL to issue an error message (like the one above: ERROR 1048 (23000): Column 'poid' cannot be null). There is a way to take over such a response by handling error cases in your own way. Reference [5] shows how to do it in MySQL stored procedures.

TRIGGER EXAMPLE 3

Purpose: Validate an update statement for changing the value of column quantity in table OrderLine. Make sure that the new value does not exceed the quantity on hand stored in the Product table.

Define the trigger associated with an INSERT operations:

DELIMITER ~~~

Drop TRIGGER IF EXISTS tUPDATE_QTY;

CREATE TRIGGER tUPDATE_QTY

BEFORE UPDATE ON OrderLine FOR EACH ROW

BEGIN

DECLARE v_qty, v_on_hand, v_diff INT;

SELECT onHand INTO v_on_hand

FROM Product WHERE pid = New.pid;

SELECT quantity INTO v_qty

FROM OrderLine WHERE pid = New.pid AND poid = New.poid;

SET v_diff = New.quantity - v_qty;

IF v_diff > 0 AND v_diff > v_on_hand THEN

Set New.poid = NULL;

Set New.pid = NULL;

ELSE

UPDATE Product SET onHand = onHand - v_diff

WHERE pid = New.pid;

END IF;

END~~~

DELIMITER ;

This trigger (stored procedure) is executed before an UPDATE statement for table OrderLine is carried out. It first calculates the difference between the new order quantity and the old order quantity
SET v_diff = New.quantity - v_qty;
If the difference is positive and it exceed the quantity on hand for this product (Product.onHand), then the UPDATE statement must be aborted (not enough inventory to satisfy the additional order). Otherwise related records of tables Product and OrderLine must be updated. Notice that a change in order quantity triggers a change in the quantity on hand.

Let’s explore a few cases.

Decrease the order quantity by one item for order line, having poid=1001 and pid=2. The current order line is:

SELECT * FROM OrderLine WHERE poid = 1001 AND pid = 2;

MySQL replies:
quantity poid pid

2 1001 2

The current level of inventory (Product.onHand) for product 2 is:

SELECT onHand FROM Product WHERE pid = 2;

MySQL replies:
onHand

Thus the update is feasible. In order to increase the order by 1, we need to run the following UPDATE statement:

UPDATE OrderLine SET quantity = 3

WHERE pid = 2 and poid = 1001;

MySQL replies:
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Let’s check the record in table OrderLine:

SELECT * FROM OrderLine WHERE poid = 1001 AND pid = 2;

MySQL replies:
quantity poid pid

3 1001 2

Let’s check the record in table Product:

SELECT onHand FROM Product WHERE pid = 2;

MySQL replies:
onHand

What if we tried to UPDATE the quantity to 10?

UPDATE OrderLine SET quantity = 10

WHERE pid = 2 and poid = 1001;

Since the quantity can’t be satisfied by the inventory level, this UPDATE statement gets ignored:

ERROR 1048 (23000): Column 'poid' cannot be null

Let’s undo the changes:

UPDATE OrderLine SET quantity = 2

WHERE pid = 2 and poid = 1001;

UPDATE Product SET onHand = 4

WHERE pid = 2;

Now, you know how to write basic triggers for SQL INSERT, DELETE, and UPDATE.

SUMMARY

This presentation covers basic MySQL procedure applications that involve assignment, query and control statements except for the repetitive statements (LOOP, REPEAT, WHILE). Applications of the loop statements are covered by the presentation about stored functions.

REFERENCES

[1] MySQL Workbench, Data Modeling & Development by Michael McLaughlin. Chapter 5, Creating and Managing Routines (p. 159 – 172)

[2] MySQL-Stored-Procedures-Part-1

[3] MySQL-Stored-Procedures-Part-2

[4] Choice Between Stored Procedures, Functions, Views, Triggers, Inline SQL

[5] MySQL Error Handling in Stored Procedures