雪花脚本中的简单存储过程

Question

我需要使用sql在雪花存储过程中创建一个表。下面是代码

create or replace procedure sp(tmp_table varchar,col_name varchar,d_type varchar ) 
returns varchar not null
as
$$
BEGIN
drop table if exists identifier(:tmp_table);
create table identifier(:tmp_table) (identifier(:col_name)  identifier(:d_type));
END;
$$;

我得到的错误是

syntax error line 4 at position 24 unexpected '('. (line 4)

你能帮我解决这个问题吗?

Answer 1

列中不支持绑定变量，这就是脚本失败的原因。您可以使用EXECUTE IMMEDIATE来生成动态SQL来克服这个问题:

create or replace procedure sp(tmp_table varchar,col_name varchar,d_type varchar ) 
returns varchar not null
as
$$
BEGIN
drop table if exists identifier(:tmp_table);
execute immediate 'create table ' || :tmp_table || '(' || :col_name || ' ' || :d_type || ')' ;
END;
$$;

Answer 2

遗憾的是，使用Snowflake Scripting[1]无法以这种方式动态命名列。作为另一种选择，您可以动态地将SQL语句生成为文本，然后执行。

我已经将drop table替换为create or replace，因为它具有相同的功能，但在一个命令中。

create or replace procedure sp(tmp_table varchar, col_name varchar, d_type varchar) 
returns table (result varchar)
language sql
as
DECLARE
sql_text varchar;
rs resultset;

invalid_input EXCEPTION (-20001, 'Input contains whitespace.');
BEGIN
IF ((SELECT :tmp_table regexp '(^\S*$)')=FALSE) THEN
RAISE invalid_input;
END IF;

IF ((SELECT :col_name regexp '(^\S*$)')=FALSE) THEN
RAISE invalid_input;
END IF;

IF ((SELECT :d_type regexp '(^\S*$)')=FALSE) THEN
RAISE invalid_input;
END IF;
sql_text := 'create or replace table ' || :tmp_table || '(' || :col_name || ' ' || :d_type || ')' ;
rs := (execute immediate :sql_text);
return table(rs);
END;

注意:在上面的例子中，我包含了一些代码来检查输入中的空白，以尽量减少SQL注入的可能性。这对于阻止用户滥用该过程非常重要。额外的检查将是谨慎的。您还应该确保此存储过程作为尽可能小的特权角色运行，以进一步减少滥用的范围。

JavaScript示例(没有SQL注入保护):

CREATE OR REPLACE procedure sp(TMP_TABLE varchar, COL_NAME varchar, D_TYPE varchar) 
RETURNS varchar not null
LANGUAGE javascript
AS
$$
var sql_cmd = "DROP TABLE IF EXISTS " + TMP_TABLE + ";";
var stmt = snowflake.createStatement(
{sqlText: sql_cmd}
);
var res = stmt.execute();

sql_cmd = "CREATE TABLE " + TMP_TABLE + " (" + COL_NAME + " " + D_TYPE + ");";
stmt = snowflake.createStatement(
{sqlText: sql_cmd}
);
res = stmt.execute();
res.next();
return res.getColumnValue(1);
$$
;