メインコンテンツまでスキップ

EXECUTE IMMEDIATE

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 14.3 and above

Executes a SQL statement provided as a STRING. The statement optionally passes arguments to parameter markers and assigns the results to variables.

Syntax​

EXECUTE IMMEDIATE sql_string
        [ INTO var_name [, ...] ]
        [ USING { arg_expr [ AS ] [alias] } [, ...] ]

For compatibility with other SQL dialects, EXECUTE IMMEDIATE also supports USING ( { arg_expr [ AS ] [alias] } [, ...] )

Parameters​

  • sql_string

    A STRING constant expression, producing a well-formed SQL statement.

    If sql_string contains parameter markers they must be all positional (?) or all named (:parm1).

    Prior to Databricks Runtime 17.3 sql_string must a literal or a variable. And you cannot nest EXECUTE IMMEDIATE statements.

  • INTO ( var_name [, ...] )

    Optionally returns the results of a single row query into SQL variables. If the query returns no rows the result is NULL.

    If the statement is not a query, Databricks raises INVALID_STATEMENT_FOR_EXECUTE_INTO error.

    If the query returns more than one row, Databricks raises ROW_SUBQUERY_TOO_MANY_ROWS error.

    • var_name

      A SQL variable. A variable may not be referenced more than once.

  • USING { arg_expr [ AS ] [alias] } [, ...]

    Optionally, if sql_string contains parameter markers, binds in values to the parameters.

    • arg_expr

      A constant expression that binds to a parameter marker. If the parameter markers are unnamed, the binding is by position. For named parameter markers, binding is by name.

    Prior to Databricks Runtime 17.3 arg_expr must a literal or a variable.

    • alias

      Overrides the name used to bind arg_expr to a named parameter marker. Each named parameter marker must be matched once. Not all arg_expr must be matched.

Examples​

SQL
-- A self-contained execution using a literal string
> EXECUTE IMMEDIATE 'SELECT SUM(c1) FROM VALUES(?), (?) AS t(c1)' USING 5, 6;
  11

-- A SQL string composed in a SQL variable
> DECLARE sqlStr = 'SELECT SUM(c1) FROM VALUES(?), (?) AS t(c1)';
> DECLARE arg1 = 5;
> DECLARE arg2 = 6;
> EXECUTE IMMEDIATE sqlStr USING arg1, arg2;
  11

-- Using the INTO clause
> DECLARE sum INT;
> DECLARE sqlStr = 'SELECT SUM(c1) FROM VALUES(:first), (:second) AS t(c1)';
> EXECUTE IMMEDIATE sqlStr INTO sum USING arg1, arg2;
> SELECT sum;
  11

-- Using named parameter markers
> DECLARE sum INT;
> DECLARE sqlStr = 'SELECT SUM(c1) FROM VALUES(:first), (:second) AS t(c1)';
> EXECUTE IMMEDIATE sqlStr INTO sum USING (5 AS first, arg2 AS second);
> SELECT sum;
  11

-- Using constant expressions
> DECLARE func STRING DEFAULT 'sum';
> EXECUTE IMMEDIATE 'SELECT ' || func || '(c1) FROM VALUES(:first), (:second) AS t(c1)'
  USING 5 + 7 AS first, length('hello') AS second;
 19
に最終更新