循环的雪花存储过程

问题描述

我在和Snowflake合作 我创建了这个存储过程，它的目的是执行以下步骤：

当我调用存储过程时，它运行时没有错误，但不执行任何操作(相关记录未被删除)。

CREATE OR REPLACE PROCEDURE DWH.sp_testing()
RETURNS string
LANGUAGE javascript
strict
EXECUTE AS owner
AS
$$

try 
{
    var application_list = ` SELECT  application_id
                             FROM PUBLIC.my_source_table_name
                             WHERE my_flag = 1
                           `

    var query_statement = snowflake.createStatement({sqlText: application_list});
    var application_list_result = query_statement.execute();

    for (i = 1; i <= query_statement.getRowCount(); i++) 
    {
        application_list_result.next()
        application_id = application_list_result.getColumnValue(1)   
         

        var delete_daily_records = `
                                DELETE FROM PUBLIC.my_target_table_name AS target
                                WHERE target.application_id = ${application_id}
                            `

        snowflake.execute({sqlText: delete_daily_records});
    }

}

catch (err) 
{
    throw "Failed: " + err; 
}

$$
;

CALL DWH.sp_testing();

推荐答案

是否确定查询正在返回数据？另外，您确定目标表具有与您的DELETE语句匹配的数据吗？

以下测试适用于我使用您的存储过程：

select count(*) from citibike_trips where end_station_id=6215;

退货：14565

现在，我创建了您的存储进程：

CREATE OR REPLACE PROCEDURE sp_testing()
RETURNS string
LANGUAGE javascript
strict
EXECUTE AS owner
AS
$$

try 
{
    var application_list = `SELECT  end_station_id
                            FROM citibike_trips
                            WHERE end_station_id=6215 limit 10
                           `

    var query_statement = snowflake.createStatement({sqlText: application_list});
    var application_list_result = query_statement.execute();

    for (i = 1; i <= query_statement.getRowCount(); i++) 
    {
        application_list_result.next()
        end_station_id = application_list_result.getColumnValue(1)
        
         

        var delete_daily_records = `
                                DELETE FROM citibike_trips AS target
                                WHERE target.end_station_id = ${end_station_id}
                            `

        snowflake.execute({sqlText: delete_daily_records});
    }
}
catch (err) 
{
    throw "Failed: " + err; 
}

$$
;

运行它：

CALL SP_TESTING();

将结果显示为空(因为它不返回任何内容，因此是预期的)。 但当我再次查看表格时：

select count(*) from citibike_trips where end_station_id=6215;

退货：0