CREATE TEMPORARY PROCEDURE

Remarks

CREATE [OR REPLACE] TEMPORARY PROCEDURE ... has the same syntax and semantics as CREATE [OR REPLACE] PROCEDURE ... (which is used to create a permanent stored procedure), except as noted in the remainder of this document.
The commands, functions, and data types that can be used inside of stored procedures, as shown in Procedural SQL Reference, apply to TSPs also.
Both permanent stored procedures and TSPs can call TSPs using the EXECUTE IMMEDIATE statement. Permanent stored procedures and TSPs cannot call TSPs implicitly, or call TSPs using the CALL statement.
Outside of a stored procedure, you can call a TSP using the CALL or ECHO statements.
A user who creates and executes a TSP must have at least one permission on the database where the TSP was created. This can be any database permission, such as USAGE (a minimal permission). Additionally, the user who executes a TSP must have permission to execute any SQL queries, permanent stored procedures, or user-defined functions that are in the TSP.
When called outside of a stored procedure or inside of a stored procedure using EXECUTE IMMEDIATE, TSPs override any permanent stored procedures having the same name.
When a TSP is dropped (explicitly via a DROP PROCEDURE statement, or when a user session is terminated), the TSP will be removed (garbage collected) automatically. When such a TSP is dropped, plans for queries and statements that were part of the TSP are not dropped from the in-memory or the on-disk plancache.
SHOW PROCEDURES shows the stored procedures accessible in the current session. The field Routine Lifetime indicates whether the stored procedure is temporary or permanent. The same applies to the information_schema.ROUTINES and information_schema.PARAMETERS tables.

Caching of TSPs

When a TSP is first invoked, the TSP's shape is compiled and saved in the plancache if a plan with the same shape does not already exist in the plancache. The shape encapsulates the TSP’s name, arguments (including their default values), and body. Subsequent invocations of the same TSP (or a similar TSP having the same shape) will be faster because the cached TSP will be used; the TSP is not recompiled.

Because the plancache is global to a node (and not local to a particular TSP), a user session can reuse a compiled TSP plan from a plan that was generated during a different session.

See Understanding the Plancache for details on how the plancache works, including the engine variables that control the expiration of plans.

Examples

Defining and Calling a Simple TSP

SQL

DELIMITER //

CREATE TEMPORARY PROCEDURE temp_sp1() AS
   BEGIN
      ECHO SELECT '1';
   END //

DELIMITER ;

CALL temp_sp1();

+---+
| 1 |
+---+
| 1 |
+---+

Calling a TSP from Within a Stored Procedure

In the following example, sp1() calls the TSP test_sp(). By contrast, the call to test_sp() in s2() does not invoke the TSP because the EXECUTE IMMEDIATE statement is not used.

SQL

DELIMITER //

CREATE PROCEDURE test_sp() AS
BEGIN
   ECHO SELECT 'PERMANENT';
END //

CREATE TEMPORARY PROCEDURE test_sp() AS
BEGIN
   ECHO SELECT 'TEMPORARY';
END //

CREATE PROCEDURE sp1() AS
BEGIN
   EXECUTE IMMEDIATE 'CALL test_sp()'; -- Calls temporary test_sp()
END //

CREATE PROCEDURE sp2() AS
BEGIN
   CALL test_sp(); -- Calls permanent test_sp()
END //

DELIMITER ;

CALL sp1();

+-----------+
| TEMPORARY |
+-----------+
| TEMPORARY |
+-----------+

SQL

CALL sp2();

+-----------+
| PERMANENT |
+-----------+
| PERMANENT |
+-----------+

Use Case: Generating Test Data

Suppose you want to generate test data to test an application that processes orders. Using the TSP populate_orders_test_data, you populate the table orders_test_data, which contains randomly generated order regions and order amounts. The TSP and table are defined below:

SQL

/* order_region in the table below stores 'E','C','W' which represent the eastern region, central region, and western region respectively. */

CREATE TABLE orders_test_data(order_region CHAR, order_amount FLOAT);

DELIMITER //

CREATE OR REPLACE TEMPORARY PROCEDURE populate_orders_test_data(number_of_orders INT, max_order_amount FLOAT)
AS
DECLARE
	region_numeric INT;
   region CHAR;
BEGIN
	TRUNCATE TABLE orders_test_data; /* Remove all data from the table, if any data exists */
	FOR i in 1 .. number_of_orders LOOP
		region_numeric = CEIL(RAND() * 3); /* Generate a random integer between 1 and 3 */
		/* Convert to the region prefix for east, central, or west region */
    	IF region_numeric = 1 THEN
      	region = 'E';
     	ELSIF region_numeric = 2 THEN
       	region = 'C';
      ELSE
       	region = 'W';
    	END IF;
		INSERT INTO orders_test_data(order_region, order_amount) VALUES (region, ROUND(RAND() * max_order_amount,2));
	END LOOP;
END //

DELIMITER ;

Populate the test table with 1000 orders where 100 is the maximum amount for each order:

SQL

CALL populate_orders_test_data(1000,100);

See the results for 10 orders. Your results will vary, as order_region and order_amount are randomly generated.

SQL

SELECT * FROM orders_test_data LIMIT 10;

+--------------+--------------+
| order_region | order_amount |
+--------------+--------------+
| W            |        26.51 |
| C            |        31.71 |
| E            |         1.38 |
| C            |        16.81 |
| C            |        25.28 |
| W            |        56.74 |
| C            |        54.17 |
| C            |         21.9 |
| W            |         7.75 |
| C            |        15.77 |
+--------------+--------------+

Later, you need to generate more test orders. Populate the test table with 2000 more orders where 500 is the maximum amount for each order:

SQL

CALL populate_orders_test_data(2000,500);