Databases Reference
In-Depth Information
The SQL Interface to Prepared Statements
A SQL interface to prepared statements is available in MySQL 4.1 and newer. It lets
you instruct the server to create and execute prepared statements, but doesn't use the
binary protocol. Here's an example of how to use a prepared statement through SQL:
mysql>
SET @sql := 'SELECT actor_id, first_name, last_name
->
FROM sakila.actor WHERE first_name = ?';
mysql>
PREPARE stmt_fetch_actor FROM @sql;
mysql>
SET @actor_name := 'Penelope';
mysql>
EXECUTE stmt_fetch_actor USING @actor_name;
+----------+------------+-----------+
| actor_id | first_name | last_name |
+----------+------------+-----------+
| 1 | PENELOPE | GUINESS |
| 54 | PENELOPE | PINKETT |
| 104 | PENELOPE | CRONYN |
| 120 | PENELOPE | MONROE |
+----------+------------+-----------+
mysql>
DEALLOCATE PREPARE stmt_fetch_actor;
When the server receives these statements, it translates them into the same operations
that would have been invoked by the client library. This means that you don't have to
use the special binary protocol to create and execute prepared statements.
As you can see, the syntax is a little awkward compared to just typing the
SELECT
state-
ment directly. So what's the advantage of using a prepared statement this way?
The main use case is for stored procedures. In MySQL 5.0, you can use prepared state-
ments in stored procedures, and the syntax is similar to the SQL interface. This means
you can build and execute “dynamic SQL” in stored procedures by concatenating
strings, which makes stored procedures much more flexible. For example, here's a
sample stored procedure that can call
OPTIMIZE TABLE
on each table in a specified
database:
DROP PROCEDURE IF EXISTS optimize_tables;
DELIMITER //
CREATE PROCEDURE optimize_tables(db_name VARCHAR(64))
BEGIN
DECLARE t VARCHAR(64);
DECLARE done INT DEFAULT 0;
DECLARE c CURSOR FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = db_name AND TABLE_TYPE = 'BASE TABLE';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c;
tables_loop: LOOP
FETCH c INTO t;
IF done THEN
LEAVE tables_loop;
END IF;
SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name, ".", t);
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
