PREPARE

Description

This statement is used to prepare a statement and assign it a name for later reference. You can use EXECUTE to execute a prepared statement and DEALLOCATE PREPARE to release it.

info

• Prepared statements only take effect in the current session and are not applicable to other sessions. When the session ends, the prepared statements created no longer exist.
• If a prepared statement is created within a stored routine and has global scope, it will not be released after the routine execution is completed. However, prepared statements within the context of stored programs cannot reference parameters or local variables of stored procedures or functions. Otherwise, the statement cannot be executed outside the program.

Syntax

PREPARE stmt_name FROM preparable_stmt

Parameter Description

Parameter	Description
stmt_name	Specifies the name of the prepared statement. The statement name is case-insensitive.
preparable_stmt	Specifies the SQL statement to be prepared for execution. This parameter is a string literal or a user variable containing the text of the SQL statement. The text must represent a single statement, not multiple statements. This parameter can include string literals or user variables for stored procedure calls, but when using user variables, output parameters cannot be directly assigned to user variables.

SQL syntax allowed in prepared statements

The following SQL statements can be used as prepared statements:

ALTER TABLE
ALTER USER
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE MASTER
COMMIT
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW
DELETE
DO
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
  | LOGS | STATUS | DES_KEY_FILE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
RENAME TABLE
REPAIR TABLE
REPLACE
RESET {QUERY CACHE}
REVOKE
SELECT
SET
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {MASTER | BINARY} LOGS
TRUNCATE TABLE
UNINSTALL PLUGIN
UPDATE

info

When using the PREPARE statement with the CALL statement, if the stored procedure call is executed in the EXECUTE ... USING ... format, the output parameter values of the stored procedure cannot be reflected in the variables specified by USING.

Examples

/* Use the SELECT statement to create a prepared statement */
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Query OK, 0 rows affected (0.001 sec)

/* Set user variables a and b */
SET @a = 3;
Query OK, 0 rows affected (0.001 sec)

SET @b = 4;
Query OK, 0 rows affected (0.001 sec)

/* Execute the prepared statement using the values of the user variables */
EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
1 row in set (0.001 sec)

info

seekdb does not support interactive client explicit results in the current version.