EXECUTE IMMEDIATE
适用于: Databricks SQL Databricks Runtime 14.3 及更高版本
执行作为 STRING
提供的 SQL 语句。
该语句可以选择将参数传递给参数标记,并将结果分配给变量。
语法
EXECUTE IMMEDIATE sql_string
[ INTO var_name [, ...] ]
[ USING { arg_expr [ AS ] [alias] } [, ...] ]
为了与其他 SQL 方言兼容,EXECUTE IMMEDIATE
还支持 USING ( { arg_expr [ AS ] [alias] } [, ...] )
参数
sql_string
STRING
文本或变量,生成格式正确的 SQL 语句。不能嵌套
EXECUTE IMMEDIATE
语句。INTO ( var_name [, ...] )
(可选)将单个行查询的结果返回到 SQL 变量中。 如果查询未返回任何行,则结果为
NULL
。如果语句不是查询,Azure Databricks 将引发
INVALID_STATEMENT_FOR_EXECUTE_INTO
错误。如果查询返回多行,Azure Databricks 将引发 ROW_SUBQUERY_TOO_MANY_ROWS 错误。
-
SQL 变量。 变量不能多次引用。
-
USING { arg_expr [ AS ] [alias] } [, ...]
(可选)如果
sql_string
包含参数标记,则值将绑定到参数。
示例
-- 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;
> EXECUTE IMMEDIATE sqlStr INTO sum USING arg1, arg2;
> SELECT sum;
11
-- Using named parameter markers
> SET VAR 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