CREATE [OR REPLACE] TEMPORARY PROCEDURE …

Note

This topic applies to version 7.3.9 and later.

Creates a temporary stored procedure, which is a stored procedure that is available during a user's session.

When the session terminates, the temporary stored procedure is no longer available.

Note: In this document, temporary stored procedures are referred to as TSPs. Also, non-temporary stored procedures are referred to as permanent stored procedures.

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

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.

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 |
+-----------+
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:

/* 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:

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.

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:

CALL populate_orders_test_data(2000,500);

Last modified: July 18, 2023

Was this article helpful?