snowflake-cloud-data-platform - 用于更改数据库或模式中所有表的 Snowflake SQL 过程

Question

我正在尝试编写一个雪花 SQL 存储过程来更改数据库中的所有表。我知道我们可以从查询信息模式中获取表名例如:select table_name from DEMO_DB.INFORMATION_SCHEMA.TABLES;

我可以为一张表做到这一点，如下所示

create or replace procedure enable_change_tracking(TABLE_NAME varchar)
  returns varchar
  language javascript
  as     
  $$  
    var my_sql_command = "ALTER table "+ TABLE_NAME +" SET CHANGE_TRACKING = TRUE;"
    
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
 
       
  return 'Done.'; 
  $$
  ;'''

call enable_change_tracking('table_name');

如何将 '''select table_name from DEMO_DB.INFORMATION_SCHEMA.TABLES;''' 的结果集传递给上面的存储过程？

Answer 1

正如 Greg 提到的，您需要循环遍历 SP 中的结果:

create or replace procedure enable_change_tracking()
returns varchar
language javascript
as     
$$  
    var my_sql_command = "select table_name from DEMO_DB.INFORMATION_SCHEMA.TABLES"
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var r = statement1.execute();

    while(r.next()) {
        table_name = r.getColumnValue('TABLE_NAME');
        sub_q = "ALTER table "+ table_name +" SET CHANGE_TRACKING = TRUE;"
        
        // run your query here
    }
return a;
$$
;