Purpose
You can use this statement to prepare and name a statement for future reference. You can use EXECUTE to execute the prepared statement and use DEALLOCATE PREPARE to release the statement.
Note
- The prepared statement takes effect only in the current session and does not apply to other sessions. When the session ends, the prepared statement no longer exists.
- If the prepared statement created in a stored routine is global, it is not released after the program is executed. However, the prepared statement in the context of the stored program cannot reference the parameters or local variables of the stored procedure or function; 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 statement to be prepared. The statement name is case-insensitive. |
| preparable_stmt | The SQL statement to be executed. This parameter is either a string literal or a user variable that contains the text of the SQL statement. The text must represent a single statement, rather than multiple statements. This parameter can be used to call a stored procedure and pass corresponding arguments. However, you cannot directly assign the output of the stored procedure to user variables during variable interaction; instead, you can only obtain the output through other methods. |
SQL syntax allowed in a prepared statement
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 PREPARE is used to prepare a CALL statement and the prepared statement is executed by EXECUTE ... USING ..., the output of the stored procedure in the CALL statement cannot be assigned to the variables specified by USING.
Examples
/* Use the SELECT statement to generate a prepared statement. */
obclient> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Query OK, 0 rows affected
/* Set the user variables a and b. */
obclient> SET @a = 3;
Query OK, 0 rows affected
obclient> SET @b = 4;
Query OK, 0 rows affected
/* Apply the user variables when executing the prepared statement. */
obclient> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 5 |
+------------+
1 row in set
Note
The current version of OceanBase Database does not support explicit results for interactive clients.