mirror of
https://github.com/boostorg/mysql.git
synced 2025-05-12 14:11:41 +00:00
139 lines
5.4 KiB
Plaintext
139 lines
5.4 KiB
Plaintext
[/
|
|
Copyright (c) 2019-2025 Ruben Perez Hidalgo (rubenperez038 at gmail dot com)
|
|
|
|
Distributed under the Boost Software License, Version 1.0. (See accompanying
|
|
file LICENSE_1_0.txt or copy at http://www.boost.org/LICENSE_1_0.txt)
|
|
]
|
|
|
|
[section:multi_resultset Multi-resultset: stored procedures and multi-queries]
|
|
|
|
[heading Using stored procedures]
|
|
|
|
[mysqllink create-procedure.html Stored procedures] can be called using the `CALL` SQL statement.
|
|
You can use `CALL` statements from both text queries and prepared statements, in a similar way
|
|
to other SQL statements. Contrary to other statements, `CALL` may generate more than one resultset.
|
|
|
|
For example, given a stored procedure like this:
|
|
|
|
__sp_get_employees__
|
|
|
|
A statement like `CALL get_employees('my_company')` will generate three resultsets:
|
|
|
|
* A first resultset containing the company matched by the first `SELECT`.
|
|
* A second resultset containing the employees matched by the second `SELECT`.
|
|
* A third, empty resultset containing information about the `CALL` statement.
|
|
|
|
Every resultset contains its own rows, metadata, last insert ID, affected rows and so on.
|
|
|
|
|
|
[heading Calling procedures using the dynamic interface]
|
|
|
|
The same [reflink results] class we've been using supports storing more than one resultset.
|
|
You can execute a `CALL` statement as any other SQL statement:
|
|
|
|
[multi_resultset_call_dynamic]
|
|
|
|
In this context, `results` can be seen as a random-access collection of resultsets. You can access
|
|
resultsets by index using [refmem results at] and [reflink2 results.operator__lb__rb_ results::operator[]].
|
|
These operations yield a [reflink resultset_view], which is a lightweight object pointing into memory owned by the `results`
|
|
object. You can take ownserhip of a `resultset_view` using the [reflink resultset] class. For example:
|
|
|
|
[multi_resultset_results_as_collection]
|
|
|
|
[heading Calling procedures using the static interface]
|
|
|
|
The [reflink static_results] class supports operations that return multiple
|
|
resultsets, too. As with other SQL statements, we need to define the row types
|
|
in our resultsets in advance:
|
|
|
|
[describe_stored_procedures]
|
|
|
|
We can now use `static_results`, passing it as many template arguments as
|
|
resultsets we expect. The library will check that the correct number of
|
|
resultsets are actually returned by the server, and will parse them into
|
|
the row types that we provided:
|
|
|
|
[multi_resultset_call_static]
|
|
|
|
|
|
Use [refmem static_results rows] with an explicit index to access each resultset's data.
|
|
You can also use explicit indices with the other accessor functions,
|
|
like [refmem static_results meta] and [refmem static_results last_insert_id].
|
|
|
|
For more information about the static interface, please refer to [link mysql.static_interface this section].
|
|
|
|
[heading Determining the number of resultsets]
|
|
|
|
To know the number of resultsets to expect from a `CALL` statement, use these rules:
|
|
|
|
* For every statement that retrieves data (e.g. a `SELECT` statement), a resultset is sent.
|
|
`SELECT ... INTO <variables>` statements don't cause a resultset to be sent.
|
|
* Statements that don't retrieve columns (e.g. `UPDATE`, `DELETE`) don't cause a resultset to be sent.
|
|
* An empty resultset containing information about the `CALL` statement execution is always sent last.
|
|
|
|
Some examples:
|
|
|
|
[!teletype]
|
|
```
|
|
-- Calling proc1 produces only 1 resultset because it only contains statements that
|
|
-- don't retrieve data
|
|
CREATE PROCEDURE proc1(IN pin_order_id INT, IN pin_quantity INT)
|
|
BEGIN
|
|
START TRANSACTION;
|
|
UPDATE orders SET quantity = pin_quantity WHERE id = pin_order_id;
|
|
INSERT INTO audit_log (msg) VALUES ("Updated order...");
|
|
COMMIT;
|
|
END
|
|
```
|
|
|
|
[!teletype]
|
|
```
|
|
-- Calling proc2 produces 3 resultsets: one for the orders SELECT, one for the
|
|
-- line_items SELECT, and one for the CALL statement
|
|
CREATE PROCEDURE proc2(IN pin_order_id INT)
|
|
BEGIN
|
|
START TRANSACTION READ ONLY;
|
|
SELECT * FROM orders WHERE id = pin_order_id;
|
|
SELECT * FROM line_items WHERE order_id = pin_order_id;
|
|
COMMIT;
|
|
END
|
|
```
|
|
|
|
[heading Output parameters]
|
|
|
|
You can get the value of `OUT` and `INOUT` parameters in stored procedures by using
|
|
prepared statement placeholders for them. When doing this, you will receive another resultset
|
|
with a single row containing all output parameter values. This resultset is located after
|
|
all resultsets generated by `SELECT`s, and before the final, empty resultset.
|
|
|
|
For example, given this procedure:
|
|
|
|
__sp_create_employee__
|
|
|
|
You can use:
|
|
|
|
[multi_resultset_out_params]
|
|
|
|
[refmem results out_params] simplifies the process.
|
|
|
|
[warning
|
|
Due to a bug in MySQL, some `OUT` parameters are sent with wrong types.
|
|
Concretely, string parameters are always sent as blobs, so you will have to
|
|
use [refmem field_view as_blob] instead of [refmem field_view as_string].
|
|
]
|
|
|
|
|
|
[heading:multi_queries Semicolon-separated queries]
|
|
|
|
It is possible to run several semicolon-separated text queries in a single [refmem connection execute] call.
|
|
For security, this capability is disabled by default. Enabling it requires setting [refmem handshake_params multi_queries]
|
|
before connecting:
|
|
|
|
[multi_resultset_multi_queries]
|
|
|
|
Note that statements like `DELIMITER` [*do not work] using this feature. This is because
|
|
`DELIMITER` is a pseudo-command for the `mysql` command line tool, not actual SQL.
|
|
|
|
You can also use the static interface with multi-queries. It works the same as with stored procedures.
|
|
|
|
[endsect] |