Event Sequence Project in MySQL

Programming in MySQL – Part 11

MySQL Course

Foreword: In this part of the series, I describe an event sequence project in MySQL.

By: Chrysanthus Date Published: 27 May 2015

Introduction

This is part 11 of my series, Programming in MySQL. In this part of the series, I describe an event sequence project in MySQL. You should have read the previous parts of the series before reaching here, as this is a continuation.

Project Description
We are still dealing with the wholesale database. The tables involve in this project are the Sales table, the SaleDetails and the Products table. For simplicity, let us assume that when a customer requests different products, if for each product, all the quantity is not available, then the customer will not buy anything. That is, if this situation occurs, the customer will not buy any of the products.

Commenting in MySQL
You have learned how to write SQL statements. When the SQL statements are many, you need to type comments among them. A comment enables you to remember what a code segment is doing. If you start a line among SQL statements with #, then everything typed on that line after the # symbol, is a comment and is ignored by the DBMS (MySQL Server). There will be many comments in the program (code) below.

Code Description
The code has 5 stored procedures. A stored procedure by its name is created and automatically stored in the database. When a customer demands his products, the sales clerk will input his request into the computer. This request will call all the procedures in a particular order. There is also a trigger, which is the trigger we had in one of the previous parts of the series. The trigger is for the Products table. We shall allow that trigger as it is.

For simplicity assume that the product names are unique.

Table Notations
Here are the table notations of the tables involved:

Sales(SaleID, DateAndTime, EmployeeID, CustomerID)
SaleDetails(SaleID, ProductID, Quantity, SoldPrice)
Products(ProductID, ProductName, Category, Quantity, ReorderLever, CostPrice, SellingPrice)

Code Details

Input Data
When a customer buys, the sales clerk types the information into a form of the computer screen. This is a SQL tutorial and there is no such form. So, I will give you the input data as SQL variables. The code for the input section is:

# The Sale data
SET @EmployeeIDVar = 3;
SET @CustomerIDVar = 2;
# SaleDetails data
SET @productNameVar1 = 'Bowl';
SET @QtyVar1 = 5;
SET @productNameVar2 = 'Pen';
SET @QtyVar2 = 12;
SET @productNameVar3 = 'Plate';
SET @QtyVar3 = 7;

SET @numberOfRows = 3;
# Create a variable to indicate shortage of product
SET @short = 0;

The ID of the sales employee clerk is indicated. The customer ID is also indicated. When a customer comes and buys some products, that is a sale. This needs a saleID. The saleID will be given by the DBMS, as it is AUTO_INCREMENT. You have the variables for the names and quantities of the products bought (above). You have a variable for the number of rows that should go into the SaleDetails table; this is the number of different products that the customer wants to buy.

The customer may ask for a quantity of a particular product and the quantity of that product in stock may not be enough. This means the quantity for that product is short. We shall use the variable, @short to indicate whether the quantity for a product is short or not. If a quantity is short, this variable will be given the integer, 1. If the quantity is not short, this variable will be given the integer, 0. Now, at the beginning, we assume that the quantity is not short, so the @short variable is assigned the value, zero. Read through the above code if you have not already done so.

Temporary Table and its Associated Code
There is a temporary table. When the data from the sales clerk’s computer arrives at the server, it is placed into this temporary table. Since the data of interest for this tutorial are in variables, these data will be copied into this temporary table, in this project. The code to do all that is:

# SaleDetails temporary table
CREATE TEMPORARY TABLE SaleDetailsTemp
(
    ID SMALLINT NOT NULL AUTO_INCREMENT,
    ProductName CHAR(40),
    QtyRequest INTEGER,
    ProductID INTEGER,
    QtyAvail INTEGER,
    QtyRemain INTEGER,
    SoldPrice DECIMAL(19,2),

    PRIMARY KEY (ID ASC)
);

INSERT INTO SaleDetailsTemp (ProductName, QtyRequest) VALUES (@productNameVar1, @QtyVar1);
INSERT INTO SaleDetailsTemp (ProductName, QtyRequest) VALUES (@productNameVar2, @QtyVar2);
INSERT INTO SaleDetailsTemp (ProductName, QtyRequest) VALUES (@productNameVar3, @QtyVar3);

#Call procedure to feed the SaleDetailsTemp table with more data
CALL feedSaleDetailsTemp (@numberOfRows);
#Call procedure to determine shortage of any product
CALL checkshortage (@numberOfRows);
#Call procedure to inform user of any shortages
CALL informUser (@numberOfRows, @short);
#Call procedure to call the insertUpdate procedure if there is no shortage
CALL callInsertUpdate (@short, @numberOfRows);

The first segment above creates the temporary table. There are many columns to this table and I will explain them as we go along. One of the columns is for the product name (ProductName). Another is for the quantity requested (QtyRequest). The next code segment inserts the product names and quantities requested into the temporary table. The last code segment above calls all the stored procedures.

The Temporary table is the basis on which the procedures work.

The Stored Procedures

When a procedure is typed and executed, it is stored automatically in the database. Any day that you need it, you call it and it comes from the database.

The feedSaleDetailsTemp Procedure
The feedSaleDetailsTemp procedure takes as input parameter, @numberOfRows, which is the number of the different products the customer requested and it is the number of rows that would be inserted into the SaleDetails table. The code for the feedSaleDetailsTemp procedure is:

#procedure to feed the SaleDetailsTemp table with more data
delimiter //
CREATE PROCEDURE feedSaleDetailsTemp
(
    IN numberOfRows INTEGER
)
BEGIN
    # loop counter variable
    DECLARE i INTEGER DEFAULT 1;
    #variable for product name
    DECLARE PNVar CHAR(40);
    #productID variable from products table
    DECLARE PIDVar INTEGER;
    #sold price variable for the SaleDetails table
    DECLARE SoldPriceVar DECIMAL(19,2);
    #variable for available quantity in the products table
    DECLARE availQtyVar INTEGER;

    WHILE i <= numberOfRows DO
        SELECT ProductName
        FROM SaleDetailsTemp
        WHERE ID = i
        INTO PNVar;
        SELECT ProductID, SellingPrice, Quantity
        FROM Products
        WHERE ProductName = PNVar
        INTO PIDVar, SoldPriceVar, availQtyVar;

        UPDATE SaleDetailsTemp SET ProductID=PIDVar, QtyAvail=availQtyVar, SoldPrice=SoldPriceVar WHERE ID = i;

        SET i = i + 1;
    END WHILE;
END;//
delimiter ;

The compound statement of this code has two sections: the declaration section and the WHILE loop section. The WHILE loop feeds the product name and quantity requested, row by row into the temporary table. The other columns are filled by other procedures. Read through the above code if you have not already done so.

The checkshortage Procedure
The checkshortage procedure has as input parameter, @numberOfRows. It is the number of different products that the customer wants. It is the number of new rows that would go into the SaleDetails table. The code is:

#procedure to determine shortage of any product
delimiter //
CREATE PROCEDURE checkshortage
(
    IN numberOfRows INTEGER
)
BEGIN
    # loop counter variable
    DECLARE i INTEGER DEFAULT 1;
    #variable for quantity requested
    DECLARE QtyRequestVar INTEGER;
    #variable for available quantity in the products table
    DECLARE QtyAvailVar INTEGER;
    #variable for quantity that would remain
    DECLARE QtyRemainVar INTEGER;

    WHILE i <= numberOfRows DO
        SELECT QtyRequest, QtyAvail
        FROM SaleDetailsTemp
        WHERE ID = i
        INTO QtyRequestVar, QtyAvailVar;

        SET QtyRemainVar = QtyAvailVar – QtyRequestVar;

        UPDATE SaleDetailsTemp SET QtyRemain= QtyRemainVar WHERE ID = i;

        SET i = i + 1;
    END WHILE;
END;//
delimiter ;

Here the compound statement has two sections: the declaration section and a WHILE loop. The WHILE loop determines the difference between the quantity of a product requested and the corresponding quantity in stock (Products table). This difference is placed in the corresponding cell in the temporary table.

The informUser Procedure
The informUser procedure has as input parameters, @numberOfRows and @short. You already know the use of @numberOfRows. @short was mentioned above. It can have the value, zero or 1. When it has zero, it means that at that time we assume, that each of the quantity of the products the customer has requested, is available. This procedure will check if that is so for each of the quantities requested. If it is not so for any requested quantity, then the value of @short will be changed to 1 (in this procedure).

The compound statement of the procedure has two sections: the declaration section and a WHILE loop section. The code for the procedure is:

#procedure to inform user of any shortage
delimiter //
CREATE PROCEDURE informUser
(
    IN numberOfRows INTEGER,
    OUT short INTEGER
)
BEGIN
    # loop counter variable
    DECLARE i INTEGER DEFAULT 1;
    #variable for quantity that would remain
    DECLARE QtyRemainVar INTEGER;

    WHILE i <= numberOfRows DO
        SELECT QtyRemain
        FROM SaleDetailsTemp
        WHERE ID = i
        INTO QtyRemainVar;

        IF QtyRemainVar < 0 THEN
            SELECT ProductName, ' is short by ' AS Incident, (QtyRemain * -1) AS QuantityRemaining
            FROM SaleDetailsTemp
            WHERE ID = i;
            SET short = 1;
        ELSE
            SET short = 0;
        END IF;

        SET i = i + 1;
    END WHILE;
END;//
delimiter ;

The subtraction of the quantity of the product in stock from the quantity requested has already been done (in the previously described procedure), and the results placed in corresponding cells in the temporary table. The WHILE loop here checks if any of these values in the product table is less than zero (negative). If it is, it sets the value of @short to 1 (through, short, locally declared in the compound statement). If there is any shortage, the WHILE loop informs the user using the SELECT statement.

The callInsertUpdate Procedure
This procedure takes as input parameters, @short and @numberOfRows. The code is:

#procedure to call the insertUpdate procedure if there is no shortage
Delimiter //
CREATE PROCEDURE callInsertUpdate
(
    IN short INTEGER,
    IN numberOfRows INTEGER
)
BEGIN
    IF short != 1 THEN
        CALL insertUpdate(numberOfRows);
    END IF;
END;//
delimiter ;

This procedure basically checks if the value of @short is not 1; that is, if it is zero. If it is zero it means that there is no shortage; that is the checkshortage stored procedure has not detected any shortage. In this case the stored procedure, callInsertUpdate calls another stored procedure to insert one row in the Sales table and the three rows in the SaleDetails table.

The insertUpdate Procedure
One sale means that one row has to be inserted into the Sales table and corresponding rows for the products inserted into the SaleDetails table. The SaleID for the row that goes into the Sales table is given automatically by the DBMS. The EmployeeID and customerID have to be given by you (or some code that you have written). The DateAndTime (timestamp) value of the Sales row is also given automatically by the DBMS. For the SaleDetails table you have to type in the Quantity and SoldPrice. The insertUpdate stored procedure will determine the SaleID and ProductID. The SaleID just inserted into the Sales table, is the maximum (integer) number in the SaleID column, everything being equal. This is the code for the insertUpdate procedure;

#procedure to insert into SaleDetails and update Products
delimiter //
CREATE PROCEDURE insertUpdate
(
    IN numberOfRows INTEGER
)
BEGIN
    # loop counter variable
    DECLARE i INTEGER DEFAULT 1;
    #productID variable from SaleDetailsTemp table
    DECLARE PIDVar INTEGER;
    #sold price variable for the SaleDetails table
    DECLARE SoldPriceVar DECIMAL(19,2);
    #variable for quantity requested
    DECLARE QtyRequestVar INTEGER;
    #variable for SaleID from Sales table
    DECLARE SaleIDVar INTEGER;
    #variable for quantity that would remain
    DECLARE QtyRemainVar INTEGER;
    #variable for available quantity in the products table
    DECLARE QtyAvailVar INTEGER;

    SELECT MAX(SaleID)
    FROM Sales
    INTO SaleIDVar;

    WHILE i <= numberOfRows DO
    SELECT QtyRequest, ProductID, QtyAvail, QtyRemain, SoldPrice
    FROM SaleDetailsTemp
    WHERE ID = i
    INTO QtyRequestVar, PIDVar, QtyAvailVar, QtyRemainVar, SoldPriceVar;
    INSERT INTO SaleDetails (SaleID, ProductID, Quantity, SoldPrice) VALUES (SaleIDVar, PIDVar, QtyRequestVar, SoldPriceVar);
    SET QtyRemainVar  = QtyAvailVar -  QtyRequestVar;
    UPDATE Products SET Quantity = QtyRemainVar WHERE ProductID = PIDVar;

        SET i = i + 1;
    END WHILE;
END;//

The main thing this procedure does is to get data from the temporary table and insert into the SaleDetails table; the one row of the Sales table is also inserted. Read the code if you have not already done so.

The complete code in the order in which you should type, can be downloaded at (link below):

Well, it has been a long ride. Let us end here.

Chrys

An Event Sequence Project in MySQL

Broad Network

Related Articles