Stored Procedures

- « Prepared Statements

- Multiple Statements »

- PHP Manual
- Quick start guide
- Stored Procedures

## Stored Procedures

The MySQL database supports stored procedures. A stored procedure is a subroutine stored in the database catalog. Applications can call and execute the stored procedure. The `CALL` SQL statement is used to execute a stored procedure.

Parameter

Stored procedures can have `IN`, `INOUT` and `OUT` parameters, depending on the MySQL version. The mysqli interface has no special notion for the different kinds of parameters.

IN parameter

Input parameters are provided with the `CALL` statement. Please, make sure values are escaped correctly.

Example #1 Calling a stored procedure

    <?php

    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    $mysqli = new mysqli("example.com", "user", "password", "database");

    $mysqli->query("DROP TABLE IF EXISTS test");
    $mysqli->query("CREATE TABLE test(id INT)");

    $mysqli->query("DROP PROCEDURE IF EXISTS p");
    $mysqli->query("CREATE PROCEDURE p(IN id_val INT) BEGIN INSERT INTO test(id) VALUES(id_val); END;");

    $mysqli->query("CALL p(1)");

    $result = $mysqli->query("SELECT id FROM test");

    var_dump($result->fetch_assoc());

The above example will output:

    array(1) {
      ["id"]=>
      string(1) "1"
    }

INOUT/OUT parameter

The values of `INOUT`/`OUT` parameters are accessed using session variables.

Example #2 Using session variables

    <?php

    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    $mysqli = new mysqli("example.com", "user", "password", "database");

    $mysqli->query("DROP PROCEDURE IF EXISTS p");
    $mysqli->query('CREATE PROCEDURE p(OUT msg VARCHAR(50)) BEGIN SELECT "Hi!" INTO msg; END;');

    $mysqli->query("SET @msg = ''");
    $mysqli->query("CALL p(@msg)");

    $result = $mysqli->query("SELECT @msg as _p_out");

    $row = $result->fetch_assoc();
    echo $row['_p_out'];

The above example will output:

    Hi!

Application and framework developers may be able to provide a more convenient API using a mix of session variables and databased catalog inspection. However, please note the possible performance impact of a custom solution based on catalog inspection.

Handling result sets

Stored procedures can return result sets. Result sets returned from a stored procedure cannot be fetched correctly using mysqli::query(). The mysqli::query() function combines statement execution and fetching the first result set into a buffered result set, if any. However, there are additional stored procedure result sets hidden from the user which cause mysqli::query() to fail returning the user expected result sets.

Result sets returned from a stored procedure are fetched using mysqli::real_query() or mysqli::multi_query(). Both functions allow fetching any number of result sets returned by a statement, such as `CALL`. Failing to fetch all result sets returned by a stored procedure causes an error.

Example #3 Fetching results from stored procedures

    <?php

    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    $mysqli = new mysqli("example.com", "user", "password", "database");

    $mysqli->query("DROP TABLE IF EXISTS test");
    $mysqli->query("CREATE TABLE test(id INT)");
    $mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");

    $mysqli->query("DROP PROCEDURE IF EXISTS p");
    $mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');

    $mysqli->multi_query("CALL p()");

    do {
        if ($result = $mysqli->store_result()) {
            printf("---\n");
            var_dump($result->fetch_all());
            $result->free();
        }
    } while ($mysqli->next_result());

The above example will output:

    ---
    array(3) {
      [0]=>
      array(1) {
        [0]=>
        string(1) "1"
      }
      [1]=>
      array(1) {
        [0]=>
        string(1) "2"
      }
      [2]=>
      array(1) {
        [0]=>
        string(1) "3"
      }
    }
    ---
    array(3) {
      [0]=>
      array(1) {
        [0]=>
        string(1) "2"
      }
      [1]=>
      array(1) {
        [0]=>
        string(1) "3"
      }
      [2]=>
      array(1) {
        [0]=>
        string(1) "4"
      }
    }

Use of prepared statements

No special handling is required when using the prepared statement interface for fetching results from the same stored procedure as above. The prepared statement and non-prepared statement interfaces are similar. Please note, that not every MYSQL server version may support preparing the `CALL` SQL statement.

Example #4 Stored Procedures and Prepared Statements

    <?php

    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    $mysqli = new mysqli("example.com", "user", "password", "database");

    $mysqli->query("DROP TABLE IF EXISTS test");
    $mysqli->query("CREATE TABLE test(id INT)");
    $mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");

    $mysqli->query("DROP PROCEDURE IF EXISTS p");
    $mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');

    $stmt = $mysqli->prepare("CALL p()");

    $stmt->execute();

    do {
        if ($result = $stmt->get_result()) {
            printf("---\n");
            var_dump($result->fetch_all());
            $result->free();
        }
    } while ($stmt->next_result());

The above example will output:

    ---
    array(3) {
      [0]=>
      array(1) {
        [0]=>
        int(1)
      }
      [1]=>
      array(1) {
        [0]=>
        int(2)
      }
      [2]=>
      array(1) {
        [0]=>
        int(3)
      }
    }
    ---
    array(3) {
      [0]=>
      array(1) {
        [0]=>
        int(2)
      }
      [1]=>
      array(1) {
        [0]=>
        int(3)
      }
      [2]=>
      array(1) {
        [0]=>
        int(4)
      }
    }

Of course, use of the bind API for fetching is supported as well.

Example #5 Stored Procedures and Prepared Statements using bind API

    <?php

    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    $mysqli = new mysqli("example.com", "user", "password", "database");

    $mysqli->query("DROP TABLE IF EXISTS test");
    $mysqli->query("CREATE TABLE test(id INT)");
    $mysqli->query("INSERT INTO test(id) VALUES (1), (2), (3)");

    $mysqli->query("DROP PROCEDURE IF EXISTS p");
    $mysqli->query('CREATE PROCEDURE p() READS SQL DATA BEGIN SELECT id FROM test; SELECT id + 1 FROM test; END;');

    $stmt = $mysqli->prepare("CALL p()");

    $stmt->execute();

    do {
        if ($stmt->store_result()) {
            $stmt->bind_result($id_out);
            while ($stmt->fetch()) {
                echo "id = $id_out\n";
            }
        }
    } while ($stmt->next_result());

The above example will output:

    id = 1
    id = 2
    id = 3
    id = 2
    id = 3
    id = 4

See also

- mysqli::query()
- mysqli::multi_query()
- mysqli::next_result()
- mysqli::more_results()