MySQL Stored Procedures Explained

What is a MySQL Stored Procedure?

A Stored Procedure is a set of SQL statements stored in the database catalogue. A Stored Procedure can be invoked by triggers, other stored procedures, and applications such as Java, PHP, Python, etc. A stored procedure that call itself is called recursive stored procedure.

Stored Procedures Advantages:

• Performance: procedure can reduce network traffic. Instead of sending multiple lengthy SQL statements over the network, the application has to send only name and parameters of the stored procedure.
• Faster Query Execution: procedure allows faster execution. They are parsed and optimised when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means DBMS does not need to be re-parsed and re-optimised the query every time
• Security/Access Control: procedures are secure. The database admin can grant appropriate permissions to applications that access stored procedures without giving any permissions on the underlying database tables
• Modularity: procedures allow modular programming. Create once, stored it in the database, and call it any number of times in your program.
• Maintainability: procedure can be maintained easily on server without maintaining applications calls.

Stored Procedures Disadvantages:

• Load on DB: increases load on the database server, because, the database is not well-designed for logical operations. Because when we use a large number of logical operations, it will increase CPU usage resulting slowing down the application.
• Maintaining Complexity: developing and maintaining stored procedures can be a fairly specialised task that not all application developers possess. It is not designed for developing complex or flexible business logic. Constructs of stored procedures make it more difficult to develop it.
• Difficult to Debug: testing and debugging stored procedures is hard. Only few DBMS allow you to debug stored procedures.
• Migration Complexity: migrating to a different DBMS (DB2, SQL Server, etc) may potentially be more difficult.

Syntax of creating Stored Procedure:

CREATE 
[DEFINER = {user | CURRENT_USER}]
PROCEDURE procedure_name ( [proc_parameter [, …]] ) 
[characteristic …] routine_body

proc_parameter:

[IN | OUT | INOUT] param_name type

type:

Any valid MySQL data type

characteristic:

COMMENT ‘string’ 

| LANGUAGE SQL 

| [NOT] DETERMINISTIC 

| { CONTAINS SQL | READS SQL DATA | NO SQL | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

routine_body:

Valid SQL routine statement

Example:

DELIMITER //
CREATE PROCEDURE getAllProducts()
BEGIN
    SELECT * FROM product;
END//

CREATE PROCEDURE getTotalProduct(OUT param1 INT)
BEGIN
    SELECT COUNT(*) INTO param1 FROM product;
END//
DELIMITER ;

CALL getAllProducts();
CALL getTotalProduct(@a);
SELECT @a;

Stored Procedure Variables:

• A variable is called data object whose value can be changed during the stored procedure execution.
• Typically, we use variables in stored procedures to hold the immediate results.
• These variables are local to stored procedure.
• You must declare a variable before you are going to use it.
• A variable is declared using DECLARE statement as follows:

DECLARE variable_name data_type(size) DEFAULT default_value;
DECLARE total_count INT DEFAULT 0;

MySQL allows you to declare multiple variables using single DECLARE statement as follows:

DECLARE x, y INT DEFAULT 0;

You can assign value for declared variables using SET statement as follows:

SET x = 10;
SET y = 20;

Besides the SET statement, you can use the SELECT INTO statement to assign the result of a query, which returns a scalar value, to a variable:

DECLARE total_cunt INT DEFAULT 0;
SELECT COUNT(*) INTO total_count FROM product;

Scope of Stored Procedure Variables:

• A variable has its own scope which defines its lifetime.
• f you declare any variable inside a stored procedure, it will be out of scope when the END statement of the stored procedure reached.
• You can declare two or more variables with the same name in different scopes because a variable is only effective in its own scope.
• A variable that begins with the @ sign is the session variable.
• Session variable is available and accessible until the session ends.

Stored Procedure Parameters:

Almost all the stored procedures that you develop require parameters.
The parameters make the stored procedures more flexible and useful.
In MySQL, a parameter has one of three modes: IN, OUT, or INOUT.

IN Parameter:

• This is default mode. When you define an IN parameter, the calling program has to pass an argument to the stored procedure.
• The value of an IN parameter is protected.
• It means that even the value of IN parameter is changed inside the stored procedure, the original value is retained after the stored procedure ends.
• In other words, the stored procedure only works on the copy of IN parameter.

OUT Parameter:

• It is also possible to change the value of an OUT parameter inside the stored procedure and its new value can be passed back to the calling program.
• Notice that the a stored procedure cannot access the initial value of any OUT parameter when it starts.

INOUT Parameter:

• An INOUT parameter in the stored procedure is the combination of IN and OUT parameters.
• It means that the calling program can pass the argument, and the stored procedure can modify the INOUT parameter and pass back the new value to the calling program.

Syntax:

MODE param_name param_type(param_size)

Example:

DELIMITER //
CREATE PROCEDURE getOfficeByCountry(IN country_name VARCHAR(255))
BEGIN
    SELECT * FROM office WHERE country = country_name;
END//

CREATE PROCEDURE countOrderByStatus(IN order_status VARCHAR(25), OUT total INT)
BEGIN
    SELECT COUNT(order_number) INTO total FROM orders WHERE status = order_status;
END//

CREATE PROCEDURE setCounter(INOUT count_value INT(4), IN inc INT(4))
BEGIN
    SET count_value = count_value + inc;
END//
DELIMITER ;

CALL getOfficeByCountry(‘USA’);
CALL getOfficeByCountry(‘INDIA’);
CALL countOrderByStatus(‘Shipped’, @total);
SELECT @total; or SELECT @total AS total_shipped;

SET @counter = 1;
CALL setCounter(@counter, 1);  // 2
CALL setCounter(@counter, 2);  // 4
CALL setCounter(@counter, 5);  // 9
SELECT @counter;   // 9

Explain how Stored Procedure can return multiple values?

To develop stored procedure that returns multiple values, You need to use INOUT or OUT parameter.
The following stored procedure accepts customer number and returns the total number of orders that was shipped, canceled, resolved, and disputed:

DELIMITER //
CREATE PROCEDURE getOrderByCustomer(IN cust_no INT, OUT shipped INT, OUT canceled INT, OUT resolved INT, OUT disputed INT)
BEGIN
— shipped
SELECT COUNT(*) INTO shipped FROM orders WHERE customer_no = cust_no AND status = ‘Shipped’;
— canceld
SELECt COUNT(*) INTO canceled FROM orders WHERE customer_no = cust_no AND status = ‘Canceled’;
— resolved
SELECt COUNT(*) INTO resolved FROM orders WHERE customer_no = cust_no AND status = Resolved;
— disputed
SELECt COUNT(*) INTO disputed FROM orders WHERE customer_no = cust_no AND status = Disputed;
END//
DELIMITER ;
CALL getOrderByCustomer(120, $shipped, $canceled, @resolved, @disputed);
SELECT $shipped AS Disputed, $canceled AS Canceled, @resolved AS Resolved, @disputed AS Disputed;

PHP – Calling Stored Procedure that returns multiple values:

<?php
/**
* Call stored procedure that return multiple values
* @param $customerNumber
*/
function myProcedure($customerNumber) {
    try {
        $pdo = new PDO("mysql:host=localhost;dbname=testdb", 'root', 'mypass');
        $sql = "CALL getOrderByCustomer(:no,@shipped,@canceled,@resolved,@disputed)";
        $stmt = $pdo->prepare($sql);
        $stmt->bindParam(':no', $customerNumber, PDO::PARAM_INT);
        $stmt->execute();
        $stmt->closeCursor();

        // execute the second query to get values from OUT parameter
        $r = $pdo->query("SELECT @shipped,@canceled,@resolved,@disputed")->fetch(PDO::FETCH_ASSOC);
        if ($r) {
            printf('Shipped: %d, Canceled: %d, Resolved: %d, Disputed: %d', $r['@shipped'], $r['@canceled'], $r['@resolved'], $r['@disputed']);
        }
    } catch (PDOException $e) {
        die("Error occurred: " . $e->getMessage());
    }
}
myProcedure(100001);
?>