Purpose
This statement is used to prepare a statement and assign a name to it for later reference. You can use EXECUTE to execute the prepared statement and DEALLOCATE PREPARE to release it.
Note
- Prepared statements are effective only in the current session and are not applicable to other sessions. When the session ends, the prepared statements created in the session no longer exist.
- Prepared statements created in stored routines are global and will not be released after the routine execution is completed. However, prepared statements within stored program contexts cannot reference the 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
Parameters
| Parameter | Description |
|---|---|
| stmt_name | The name of the prepared statement. Statement names are case-insensitive. |
| preparable_stmt | The SQL statement to be prepared for execution. This parameter can be a string literal or a user variable containing the text of an SQL statement. The text must represent a single statement, not multiple statements. This parameter can also contain string literals or user variables representing stored procedure calls. However, 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
CHECKSUM {TABLE | TABLES}
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 | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
RENAME TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE | QUERY CACHE}
REVOKE
SELECT
SET
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
TRUNCATE TABLE
UNINSTALL PLUGIN
UPDATE
Note
When using the PREPARE statement with the CALL statement, the output parameter values of a stored procedure cannot be reflected in the variables specified by USING when executing a prepared stored procedure call in the EXECUTE ... USING ... format.
Examples
/* Create a prepared statement using a SELECT statement */
obclient> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Query OK, 0 rows affected
/* Set user variables a and b */
obclient> SET @a = 3;
Query OK, 0 rows affected
obclient> SET @b = 4;
Query OK, 0 rows affected
/* Execute the prepared statement using the values of the user variables */
obclient> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 5 |
+------------+
1 row in set
Note
OceanBase Database does not support interactive client explicit results in the current version.