exec
NAME
exec(): Executes a given query or semicolon delimited list of queries.TYPE
ProcedureDESCRIPTION
This procedure will invoke a list of queries (one or more), by utilizing dynamic SQL. It calls upon exec_single() for each query.
Queries may be of any valid type, that is allowed to invoke from within a prepared statement:
- DML (e.g. INSERT, UPDATE, ...)
- DDL (e.g. CREATE, ALTER, ...)
- Other (e.g. KILL, SHOW, ...)
The procedure is used as the underlying execution mechanism for other common_schema routines, such as foreach(), repeat_exec() and exec_file(). Users will often not use this routine directly. Since it relies on exec_single(), it respects the same input configuration (see following).
Invoker of this procedure must have the privileges required for execution of given queries.
SYNOPSIS
exec(IN execute_queries TEXT CHARSET utf8) MODIFIES SQL DATA
Input:
- execute_queries: one or more queries to execute.
- Queries must be separated by a semicolon (";").
- Last (or single) query may optionally be terminated by a semicolon, but it does not have to.
- A semicolon may appear within quoted strings in queries.
- Empty queries are discarded silently.
- @common_schema_dryrun: when 1, queries are not executed, but rather printed.
- @common_schema_verbose: when 1, queries are verbosed.
- Whatever output the queries may produce.
- @common_schema_rowcount: number of rows affected by execution of the last query.
EXAMPLES
Execute sequence of commands, both DDL and DML:
call exec('CREATE TABLE test.t(id INT); INSERT INTO test.t VALUES (2),(3),(5); SELECT SUM(id) FROM test.t INTO @result;');
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT @result;
+---------+
| @result |
+---------+
| 10 |
+---------+
Execute queries from server-side file (see also exec_file()).
mysql> call exec(LOAD_FILE('/tmp/statements.sql'));