Purpose
This statement is used to prepare a statement and assign a name to it for later reference. You can use EXECUTE to execute a prepared statement and use DEALLOCATE PREPARE to release it.
Note
- A prepared statement is only effective in the current session and cannot be used in other sessions. When the session ends, the prepared statement no longer exists.
- If a prepared statement created in a stored routine is global, it will not be released after the routine is executed. However, a prepared statement within the context of a stored program cannot reference the parameters or local variables of the stored procedure or function. Otherwise, the statement will not be executable outside the program.
Syntax
PREPARE stmt_name FROM preparable_stmt
Parameters
| Parameter | Description |
|---|---|
| stmt_name | Specifies the name of the prepared statement. Statement names are case-insensitive. |
| preparable_stmt | Specifies the SQL statement to be prepared for execution. This parameter can be a string literal or a user variable containing the text of the SQL statement. The text must represent a single statement, not multiple statements. |
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
Examples
/* Prepare a statement using a SELECT statement */
obclient> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Query OK, 0 rows affected
/* Set the values of 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-side explicit results in the current version.