Purpose
This statement is used to prepare a statement and assign it a name for later reference. Prepared statements can be executed using the EXECUTE statement and can be deallocated using the DEALLOCATE PREPARE statement.
Note
- Prepared statements are only effective within the current session and are not applicable to other sessions. When a session ends, any prepared statements created in that session no longer exist.
- Prepared statements created within stored routines are global and will not be released after the routine has executed. However, prepared statements within stored program contexts cannot reference parameters or local variables of stored procedures or functions. Otherwise, the statement will not be executable outside the program context.
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 contain string literals or user variables for stored procedure calls, but 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, if the EXECUTE ... USING ... form is used to execute a preprocessed stored procedure call, the output parameter values of the stored procedure cannot be reflected in the variables specified using USING.
Examples
/* Use a SELECT statement to create a prepared 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 currently support explicit result interaction with clients in the current version.