Oracle 5.0, Reference Manual

Page: 1154 / 3232

SQL Syntax for Prepared Statements
1134
SQL syntax for prepared statements is based on three SQL statements:
•
PREPARE
prepares a statement for execution (see Section 13.5.1, “
PREPARE
Syntax” ).
•
EXECUTE
executes a prepared statement (see Section 13.5.2, “
EXECUTE
Syntax” ).
•
DEALLOCATE PREPARE
releases a prepared statement (see Section 13.5.3, “
DEALLOCATE
PREPARE
Syntax” ).
The following examples show two equivalent ways of preparing a statement that computes the
hypotenuse of a triangle given the lengths of the two sides.
The first example shows how to create a prepared statement by using a string literal to supply the text
of the statement:
mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;
The second example is similar, but supplies the text of the statement as a user variable:
mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> PREPARE stmt2 FROM @s;
mysql> SET @a = 6;
mysql> SET @b = 8;
mysql> EXECUTE stmt2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
| 10 |
+------------+
mysql> DEALLOCATE PREPARE stmt2;
Here is an additional example which demonstrates how to choose the table on which to perform a
query at runtime, by storing the name of the table as a user variable:
mysql> USE test;
mysql> CREATE TABLE t1 (a INT NOT NULL);
mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80);
mysql> SET @table = 't1';
mysql> SET @s = CONCAT('SELECT * FROM ', @table);
mysql> PREPARE stmt3 FROM @s;
mysql> EXECUTE stmt3;
+----+
| a |
+----+
| 4 |
| 8 |
| 11 |
| 32 |
| 80 |
+----+
mysql> DEALLOCATE PREPARE stmt3;
A prepared statement is specific to the session in which it was created. If you terminate a session
without deallocating a previously prepared statement, the server deallocates it automatically.
A prepared statement is also global to the session. If you create a prepared statement within a stored
routine, it is not deallocated when the stored routine ends.