CREATE [OR REPLACE] TEMPORARY PROCEDURE ...
This command is available in SingleStore DB 7.3.9 and later. This command is available in SingleStore Managed Service.
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.
CREATE [OR REPLACE] TEMPORARY PROCEDURE ...has the same syntax and semantics as CREATE 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 IMMEDIATEstatement. 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. 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.
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
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 on the next page.
/* 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 10 orders where 100 is the maximum amount for each order:
See the results. Your results will vary, as
order_amount are randomly generated.
SELECT * FROM orders_test_data; **** +--------------+--------------+ | 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 | +--------------+--------------+