EXECUTE IMMEDIATE

EXECUTE IMMEDIATE SQL_string
      [INTO variable [, ...]]

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.

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

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

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

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