Create temporary table in MySQL procedure?

The syntax to create a temporary table in a MySQL stored procedure is as follows:

CREATE PROCEDURE yourProcedureName()
   BEGIN
      CREATE TEMPORARY TABLE yourTemporaryTableName SELECT yourValue;
   END

Let us implement the above syntax to create a temporary table and insert some records in the table. Following is the query to create a stored procedure and a temporary table in it −

mysql> DELIMITER //
mysql> CREATE PROCEDURE create_Temporary_Table()
   -> BEGIN
   ->    CREATE TEMPORARY TABLE tmpDemoTable SELECT 500;
   -> END//
Query OK, 0 rows affected (0.15 sec)

The following is the query to insert records in the table:

mysql> CREATE PROCEDURE insert_Record_InTempTable()
   -> BEGIN
   ->    INSERT INTO tmpDemoTable VALUES (300);
   -> END//
Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER

Now you can call the above stored procedure to create a temporary table −

mysql> call create_Temporary_Table();
Query OK, 1 row affected (0.00 sec)

mysql> call insert_Record_InTempTable();
Query OK, 1 row affected (0.00 sec)

Use the select statement to display all the records in the table −

mysql> select *from tmpDemoTable;

Output

This will produce the following Output −

+-----+
| 500 |
+-----+
| 500 |
| 300 |
+-----+
2 rows in set (0.00 sec)

The above is the detailed content of Create temporary table in MySQL procedure?. For more information, please follow other related articles on the PHP Chinese website!