CREATE [OR REPLACE] TEMPORARY PROCEDURE …
On this page
This topic applies to version 7.
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.
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
Permanent stored procedures and TSPs cannot call TSPs implicitly, or call TSPs using the
Outside of a stored procedure, you can call a TSP using the
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 PROCEDUREstatement, 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 PROCEDURESshows the stored procedures accessible in the current session.
Routine Lifetimeindicates whether the stored procedure is temporary or permanent.
The same applies to the
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.
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.
DELIMITER //CREATE TEMPORARY PROCEDURE temp_sp1() ASBEGINECHO SELECT '1';END //DELIMITER ;CALL temp_sp1();
+---+ | 1 | +---+ | 1 | +---+
In the following example,
sp1() calls the TSP
s2() does not invoke the TSP because the
EXECUTE IMMEDIATE statement is not used.
DELIMITER //CREATE PROCEDURE test_sp() ASBEGINECHO SELECT 'PERMANENT';END //CREATE TEMPORARY PROCEDURE test_sp() ASBEGINECHO SELECT 'TEMPORARY';END //CREATE PROCEDURE sp1() ASBEGINEXECUTE IMMEDIATE 'CALL test_sp()'; -- Calls temporary test_sp()END //CREATE PROCEDURE sp2() ASBEGINCALL test_sp(); -- Calls permanent test_sp()END //DELIMITER ;CALL sp1();
+-----------+ | TEMPORARY | +-----------+ | TEMPORARY | +-----------+
+-----------+ | PERMANENT | +-----------+ | PERMANENT | +-----------+
Suppose you want to generate test data to test an application that processes orders.
populate_, you populate the table
orders_, which contains randomly generated order regions and order amounts.
/* 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)ASDECLAREregion_numeric INT;region CHAR;BEGINTRUNCATE TABLE orders_test_data; /* Remove all data from the table, if any data exists */FOR i in 1 .. number_of_orders LOOPregion_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 THENregion = 'E';ELSIF region_numeric = 2 THENregion = 'C';ELSEregion = '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:
See the results for 10 orders.
order_ 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.
Last modified: July 18, 2023