PREPARE SQL STMT

PREPARE SQL STMT mk Sun, 10/13/2013 - 16:33

The PREPARE statement compiles an SQL statement into its execution plan. The plan is given a name and stored in the query cache. A subsequent EXECUTE command retrieves it from the cache and executes it.

PREPARE statement executions can be given positional arguments to replace any literal constant in the query.  Each argument is denoted with a '?'.

sql>prepare select * from tables where name = ?;
execute prepared statement using: EXEC 15(...)
clk: 4.123 ms
+----------+---------+-------+--------+--------+---------------+
| type     | digits  | scale | schema | table  | column        |
+==========+=========+=======+========+========+===============+
| int      |      32 |     0 |        | tables | id            |
| varchar  |    1024 |     0 |        | tables | name          |
| int      |      32 |     0 |        | tables | schema_id     |
| varchar  |    2048 |     0 |        | tables | query         |
| smallint |      16 |     0 |        | tables | type          |
| boolean  |       1 |     0 |        | tables | system        |
| smallint |      16 |     0 |        | tables | commit_action |
| smallint |      16 |     0 |        | tables | access        |
| tinyint  |       8 |     0 |        | tables | temporary     |
| varchar  |    1024 |     0 | null   | null   | null          |
+----------+---------+-------+--------+--------+---------------+
10 tuples
clk: 4.123 ms

sql>exec 15('_tables');
+------+---------+-----------+-------+------+--------+---------------+--------+-----------+
| id   | name    | schema_id | query | type | system | commit_action | access | temporary |
+======+=========+===========+=======+======+========+===============+========+===========+
| 2067 | _tables |      2000 | null  |   10 | true   |             0 |      0 |         0 |
| 2115 | _tables |      2114 | null  |   10 | true   |             2 |      0 |         0 |
+------+---------+-----------+-------+------+--------+---------------+--------+-----------+
2 tuples
clk: 2.692 ms

sql>exec 15('table_types');
+------+-------------+-----------+-------+------+--------+---------------+--------+-----------+
| id   | name        | schema_id | query | type | system | commit_action | access | temporary |
+======+=============+===========+=======+======+========+===============+========+===========+
| 7322 | table_types |      2000 | null  |   10 | true   |             0 |      1 |         0 |
+------+-------------+-----------+-------+------+--------+---------------+--------+-----------+
1 tuple
clk: 2.364 ms