Purpose
This statement is used to prepare a statement and assign it a name for later reference. You can use EXECUTE to execute the prepared statement, and use DEALLOCATE PREPARE or DROP PREPARE to release it.
Note
- Prepared statements take effect only in the current session and are not applicable to other sessions. When a session ends, the created prepared statements cease to exist.
- A prepared statement created within a stored procedure or function is global and will not be released after the program execution completes. However, a prepared statement within a stored procedure or function cannot reference parameters or local variables of that 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 | Specifies the name of the prepared statement. Statement names are case-insensitive. |
| preparable_stmt | Specifies the SQL statement to be prepared and executed. This parameter can be a string literal or a user variable containing the SQL statement text. The text must represent a single statement, not multiple statements. This parameter can also contain a string literal or user variable for a stored procedure call, but when interacting with user variables, output parameters cannot be directly assigned to user variables. |
Supported SQL syntax 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, if you execute a prepared stored procedure call in the EXECUTE ... USING ... form, the output parameter values of the stored procedure cannot be reflected in the variables specified by USING.
Examples
Construct a prepared statement using the SELECT statement:
obclient> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
Set user variables a and b:
obclient> SET @a = 3;
obclient> SET @b = 4;
Execute prepared statements using the values of application user variables:
obclient> EXECUTE stmt1 USING @a, @b;
The query result is as follows:
+------------+
| hypotenuse |
+------------+
| 5 |
+------------+
1 row in set
Release prepared statements:
obclient> DEALLOCATE PREPARE stmt1;
Note
The interactive client of OceanBase AI Database does not support explicit results in the current version.