Purpose
This statement is used to prepare a statement and assign it a name for later reference. You can use the EXECUTE statement to execute a prepared statement, and use the DEALLOCATE PREPARE or DROP PREPARE statement to release it.
Note
- A prepared statement only takes effect in the current session and is not applicable to other sessions. When the session ends, the prepared statement no longer exists.
- If a prepared statement is created in a stored procedure or function and is global, it will not be released after the program execution is completed. However, prepared statements within stored procedures or functions cannot reference the procedure or function's parameters or local variables, 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. 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 contain string literals or user variables for stored procedure calls, but when using user variables, the output parameter cannot be directly assigned to a user variable. |
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, when executing a prepared stored procedure call in the EXECUTE ... USING ... format, the output parameter values of the stored procedure cannot be reflected into the variables specified by USING.
Examples
Use the SELECT statement to create a prepared statement:
obclient> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Set the user variables a and b:
obclient> SET @a = 3;
obclient> SET @b = 4;
Execute the prepared statement using the values of the user variables:
obclient> EXECUTE stmt1 USING @a, @b;
The query result is as follows:
+------------+
| hypotenuse |
+------------+
| 5 |
+------------+
1 row in set
Release the prepared statement:
obclient> DEALLOCATE PREPARE stmt1;
Note
OceanBase Database does not currently support explicit client interaction results in the current version.