Snowflake动态SQL:相当于DBMS_SQL/sp_executesql

Question

我正在搜索sp_executesql/DBMS_SQL的等价物，它将允许执行动态SQL(最好使用绑定参数(

背后的理由：生成要在SQL中运行并从SQL执行的代码

我知道Snowflake没有包含控制流语法(WHILE/IF-THEN/TRY-CATCH(的过程SQL组件，并且可以通过存储过程中的JavaScript代码来减轻这种构造。

---

示例场景：

生成任意SQL：此处为表生成

SELECT create_table_sql FROM ( 
SELECT LISTAGG(REPLACE(CHAR(13) || ',a<index> INT DEFAULT UNIFORM(1, 10000, RANDOM())', '<index>', seq8()+1),'')
WITHIN GROUP(ORDER BY seq8()) AS column_list

,REPLACE(REPLACE(
'CREATE OR REPLACE TABLE <table_name>(id INT  <column_list>);'
,'<table_name>', 'wide5')
,'<column_list>', column_list) AS create_table_sql
FROM TABLE(GENERATOR(rowcount => 5))
);

输出：

CREATE OR REPLACE TABLE wide5(id INT  
,a1 INT DEFAULT UNIFORM(1, 10000, RANDOM())
,a2 INT DEFAULT UNIFORM(1, 10000, RANDOM())
,a3 INT DEFAULT UNIFORM(1, 10000, RANDOM())
,a4 INT DEFAULT UNIFORM(1, 10000, RANDOM())
,a5 INT DEFAULT UNIFORM(1, 10000, RANDOM()));

现在的目标是从WebUI执行它。我的第一个想法是将其分配给变量。由于尺寸限制(轻微倒退(，它失败了：

SET sql_text = (SELECT create_table_sql FROM ...);

未完成对"SQL_TEXT"的赋值，因为值超过了变量的大小限制。它的尺寸是260；限制是256(以字节为单位的内部存储大小(。

这里应该有类似EXECUTE IMMEDIATE/EXEC或其他RDBMS中已知的参数化对应物。

....(generated_code)

---

我创建了自己的过于简单的版本：

CREATE OR REPLACE PROCEDURE execute_immediate(sql_param STRING)
RETURNS VARCHAR
LANGUAGE javascript
AS
$$
var rs = snowflake.execute( { sqlText: SQL_PARAM});   
return 'Done.';
$$;

并直接将子查询作为参数传递(此处Snowflake闪耀(：

CALL execute_immediate(subquery);

示例：

CALL execute_immediate(
SELECT create_table_sql 
FROM ( 
SELECT LISTAGG(REPLACE(CHAR(13) || ',a<index> INT DEFAULT UNIFORM(1, 10000, RANDOM())', '<index>', seq8()+1),'') 
WITHIN GROUP(ORDER BY seq8()) AS column_list

,REPLACE(REPLACE(
'CREATE OR REPLACE TABLE <table_name>(id INT  <column_list>);'
,'<table_name>', 'wide5')
,'<column_list>', column_list) AS create_table_sql
FROM TABLE(GENERATOR(rowcount => 5)))
);

在创建表时检查SELECT查询：SELECT * FROM wide5。

它正在发挥作用，但我相信它可以做得更好。

---

我尝试过但发现不完全满意的其他考虑因素和替代方案：

• 生成SQL脚本、执行查询、复制输出、粘贴和执行(需要手动步骤(
• 在JavaScript存储过程中编写代码生成部分(可能，如果真的没有其他可用的方法的话(

动态SQL可能很难编写，而且容易出错，但使用参数绑定/引用标识符和限制用户输入仍然是安全的。

Answer 1

可以使用雪花脚本：

DROP TABLE IF EXISTS wide5;
-- to be run with Snowsight
DECLARE
SQL STRING;
BEGIN
SELECT create_table_sql 
INTO :SQL 
FROM ( 
SELECT LISTAGG(REPLACE(CHAR(13) || ',a<index> INT DEFAULT UNIFORM(1, 10000, RANDOM())'
, '<index>', seq8()+1),'')
WITHIN GROUP(ORDER BY seq8()) AS column_list
,REPLACE(REPLACE(
'CREATE OR REPLACE TABLE <table_name>(id INT  <column_list>);'
,'<table_name>', 'wide5')
,'<column_list>', column_list) AS create_table_sql
FROM TABLE(GENERATOR(rowcount => 5))
);
EXECUTE IMMEDIATE :SQL;
END;

SELECT * FROM wide5;
-- ID   A1  A2  A3  A4  A5