EXECUTE IMMEDIATE
Runs a SQL query.
You specify the query using a string, which you can build dynamically.
Syntax
EXECUTE IMMEDIATE SQL_string [INTO variable [, ...]]
Arguments
SQL_string
A string expression containing one or more SQL statements. Multiple SQL statements are separated with a semicolon. The rightmost SQL statement may optionally have a semicolon.
variable
A list of variables to be specified with the SELECT … INTO <variable> statement.
Example 1: Feature tracking
Using the parameter customer_id
, the procedure below creates a feature table for the customer and populates the table with the default feature set. The INSERT
statement string is built using QUOTE
, which allows single quotes to be inserted around the feature_id
field values. The ECHO SELECT
statement writes the query results to the console.
DROP DATABASE IF EXISTS singlestore_docs_example; CREATE DATABASE singlestore_docs_example; use singlestore_docs_example; DELIMITER // CREATE PROCEDURE create_feature_table(customer_id VARCHAR(20)) AS DECLARE statement1 TEXT; statement2 TEXT; BEGIN statement1 = CONCAT('CREATE TABLE ', customer_id, '_FEATURE(feature_id TEXT)'); statement2 = CONCAT('INSERT INTO ', customer_id, '_FEATURE VALUES (', QUOTE('F000'), '),(', QUOTE('F001'), '),(', QUOTE('F002'), ')'); EXECUTE IMMEDIATE CONCAT(statement1, ';', statement2); END // DELIMITER ; CALL create_feature_table('C1000'); SELECT * FROM C1000_FEATURE ORDER BY feature_id;
Output:
+------------+ | feature_id | +------------+ | F000 | | F001 | | F002 | +------------+
Example 2: Table analysis
The following procedure retrieves all of the column names for the table_name
located in schema_name
. Using EXECUTE IMMEDIATE
, it returns the number of unique values in each column. It uses the information_schema
database to get the column names. This database contains metadata about the other databases that exist in the SingleStoreDB instance.
DROP DATABASE IF EXISTS singlestore_docs_example; CREATE DATABASE singlestore_docs_example; use singlestore_docs_example; CREATE TABLE t(id INT, color TEXT); INSERT INTO t VALUES (1, 'red'), (2, 'green'), (3, 'blue'), (4, 'blue'), (5, 'red'); DELIMITER // CREATE PROCEDURE analyze_table(_table_schema TEXT, _table_name TEXT) AS DECLARE /* Use static parameterized SQL when declaring 'q' because _table_name and _table_schema are placeholders for constant values. */ qry QUERY(column_name VARCHAR(64)) = SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME =_table_name AND TABLE_SCHEMA = _table_schema; arr ARRAY(RECORD(column_name VARCHAR(64))); _column_name TEXT; exec_string TEXT; BEGIN arr = COLLECT(qry); /* When creating the 'exec_string's below, use dynamic SQL because _table_name and _table_schema are not placeholders for constant values. */ exec_string = CONCAT('ECHO SELECT COUNT(*) FROM ', _table_schema, '.', _table_name); EXECUTE IMMEDIATE exec_string; FOR x IN arr LOOP _column_name = x.column_name; exec_string = CONCAT('ECHO SELECT DISTINCT(', _column_name, ') FROM ', _table_schema, '.', _table_name, ' ORDER BY ', _column_name); EXECUTE IMMEDIATE exec_string; END LOOP; END // DELIMITER ; CALL analyze_table('singlestore_docs_example','t');
Output:
+----------+ | COUNT(*) | +----------+ | 5 | +----------+ +------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ +-------+ | color | +-------+ | blue | | green | | red | +-------+
Example 3: Table mapping
Using EXECUTE IMMEDIATE
, the following example demonstrates how to call functions dynamically.
DROP DATABASE IF EXISTS singlestore_docs_example; CREATE DATABASE singlestore_docs_example; use singlestore_docs_example; CREATE TABLE t1(num INT); CREATE TABLE t2(num INT); INSERT INTO t1 VALUES (5),(9),(11); INSERT INTO t2 VALUES (36),(64),(100); DELIMITER // CREATE FUNCTION sqr(num INT) RETURNS INT AS BEGIN RETURN pow(num,2); END // DELIMITER // CREATE PROCEDURE map(tbl TEXT, func TEXT) AS DECLARE str TEXT; BEGIN str = CONCAT('ECHO SELECT num, ', func , '(num) FROM ', tbl , ' ORDER BY num'); EXECUTE IMMEDIATE str; END //
Call the map
function defined above, applying sqr
to each row of table t1
:
CALL map('t1','sqr');
Output:
+------+----------+ | num | sqr(num) | +------+----------+ | 5 | 25 | | 9 | 81 | | 11 | 121 | +------+----------+
Call the map
function defined above, applying sqrt
to each row of table t2
:
CALL map('t2','sqrt');
Output:
+------+-----------+ | num | sqrt(num) | +------+-----------+ | 36 | 6 | | 64 | 8 | | 100 | 10 | +------+-----------+
Example 4: Initializing variables
The following example assigns values to variables using the EXECUTE IMMEDIATE
statement.
DELIMITER // CREATE OR REPLACE PROCEDURE into_var () AS DECLARE row_c INT; qry VARCHAR(50); BEGIN qry = "SELECT COUNT(*) FROM hrRec"; EXECUTE IMMEDIATE qry INTO row_c; ECHO SELECT row_c AS "Row Count"; END // DELIMITER ; CALL into_var(); **** +-----------+ | Row Count | +-----------+ | 5 | +-----------+
Related Topics