如何在Snowflake中编写循环更新存储过程并集成至现有SP？

如何在Snowflake中编写存储过程，实现从源表到目标表的列更新操作？

需求格式示例

update t
set boost_combo = 'oxaliplatin'
from source_product t
where category like '%eloxa%'

需要将上述更新逻辑编写为可循环执行的存储过程，其中源表名为PRODUCT_SOURCE，关联表为MDM_REF_SEGMENTATION。

我正尝试为以下更新语句创建存储过程：

update t
set boost_combo = 'oxaliplatin'
from source_product t
where category like '%eloxa%' 

请指导我如何创建该存储过程，同时我已编写了一个存储过程，需要将上述更新需求集成进去，现有存储过程代码如下：

CREATE OR REPLACE PROCEDURE "SP_CHC_D_CATEGORIZING_MULTIPLE_INSERT_SCRIPT"("COLUMN_OPERATOR" ARRAY, "KEY_COLUMNS" ARRAY)
RETURNS VARIANT
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS 
$$
        /* 从INTERMEDIATE_DS_REF_CATEGORY表删除数据 */
        var sql_command_delete="delete from DF_CHC_DEV.PSA_MARKET_SALES.INTERMEDIATE_DS_REF_CATEGORY";

   
    /* 执行SQL命令 */
    var rs_delete = snowflake.execute({sqlText: sql_command_delete});  

    /* 用SCRIPT_FRAME1实现INSERT+SELECT语法，从MDM_MARKET_DEFINITION和MDM_MARKET表加载数据到INTERMEDIATE_DS_REF_CATEGORY */
    let SCRIPT_FRAME1 ="INSERT INTO DF_CHC_DEV.PSA_MARKET_SALES.INTERMEDIATE_DS_REF_CATEGORY(MDM_MARKET_DEFINITION_ID,MARKET_ID,INCLUSION_FLAG,MARKET_DEFINITION_NAME,WHERE_CONDITION) SELECT MDM_MARKET_DEFINITION_ID,MARKET_ID,INCLUSION_FLAG, MARKET_DEFINITION_NAME, 'WHERE '||";
    
    /* SCRIPT_FRAME2包含SELECT查询 */
    let SCRIPT_FRAME2 = " FROM (SELECT MDM_MARKET_DEFINITION_ID,MARKET_ID,INCLUSION_FLAG, MARKET_DEFINITION_NAME,";
    
    /* SCRIPT_FRAME3包含源表列表和关联条件 */
    let SCRIPT_FRAME3=" FROM DF_CHC_DEV.PSA_MARKET_SALES.MDM_MARKET_DEFINITION AS A JOIN DF_CHC_DEV.PSA_MARKET_SALES.MDM_MARKET AS B ON A.MARKET_DEFINITION_NAME = B.MARKET_LEVEL_0 WHERE A.MARKET_DEFINITION_NAME NOT IN ('PV') AND COALESCE(A.CHANNEL_NAME, 'FF') NOT LIKE '%ACTUALS%' ORDER BY MDM_MARKET_DEFINITION_ID ASC);";
    
    /* KEY_COLUMNS1变量根据key_columns的输入值生成动态SQL脚本 */
    let KEY_COLUMNS1=KEY_COLUMNS.map((e,i)=>`ifnull(${KEY_COLUMNS[i]}_, '1=1')`)
    
    /* COLUMN_OPERATOR变量根据业务需求动态生成SQL脚本，使用COLUMN_OPERATOR中的查询逻辑 */
    COLUMN_OPERATOR=COLUMN_OPERATOR.map((elem,i)=>`CASE WHEN A.${COLUMN_OPERATOR[i]} = '=' THEN 'IN' WHEN A.${COLUMN_OPERATOR[i]} = '<>' THEN 'NOT IN' WHEN A.${COLUMN_OPERATOR[i]} = 'START WITH' OR A.${COLUMN_OPERATOR[i]} = 'END WITH'THEN 'LIKE ANY' WHEN A.${COLUMN_OPERATOR[i]} = 'LIKE' OR A.${COLUMN_OPERATOR[i]} = 'NOT LIKE' THEN 'LIKE ANY' ELSE A.${COLUMN_OPERATOR[i]} END AS ${COLUMN_OPERATOR[i]}_,CASE WHEN A.${COLUMN_OPERATOR[i]} = '=' OR A.${COLUMN_OPERATOR[i]} = '<>' THEN '${KEY_COLUMNS[i]} '||${COLUMN_OPERATOR[i]}_||' (\''||REPLACE(REPLACE(${KEY_COLUMNS[i]},'''',''''''),';','\',\'')||'\')' WHEN A.${COLUMN_OPERATOR[i]} ='LIKE' THEN '${KEY_COLUMNS[i]} '||${COLUMN_OPERATOR[i]}_||' (\'%'||REPLACE(REPLACE(${KEY_COLUMNS[i]},'''',''''''),';','%\',\'%')||'%\')' WHEN  A.${COLUMN_OPERATOR[i]} ='NOT LIKE' THEN 'NOT (${KEY_COLUMNS[i]} '||${COLUMN_OPERATOR[i]}_||' (\'%'||REPLACE(REPLACE(${KEY_COLUMNS[i]},'''',''''''),';','%\',\'%')||'%\'))' WHEN A.${COLUMN_OPERATOR[i]} ='START WITH' THEN '${KEY_COLUMNS[i]} '||${COLUMN_OPERATOR[i]}_||' (\''||REPLACE(REPLACE(${KEY_COLUMNS[i]},'''',''''''),';','%\',\'')||'%\')' WHEN A.${COLUMN_OPERATOR[i]} ='END WITH' THEN '${KEY_COLUMNS[i]} '||${COLUMN_OPERATOR[i]}_||' (\''||REPLACE(REPLACE(${KEY_COLUMNS[i]},'''',''''''),';','\',\'%')||'%\')' ELSE ${KEY_COLUMNS[i]} END AS ${KEY_COLUMNS[i]}_`)

    /* 将SCRIPT_FRAME1、SCRIPT_FRAME2、SCRIPT_FRAME3和KEY_COLUMNS1组合成最终的WHERE条件SQL命令 */
    let sql_command_final_where_condition=SCRIPT_FRAME1+KEY_COLUMNS1.join('||\' and \'||')+SCRIPT_FRAME2+COLUMN_OPERATOR.join(',')+SCRIPT_FRAME3
    
    /* 执行SQL命令 */
    var rs_1 = snowflake.execute({sqlText: sql_command_final_where_condition});

    /* 查询INTERMEDIATE_DS_REF_CATEGORY表的所有数据，存入array_of_rows数组 */

    var sql_command ="SELECT * FROM DF_CHC_DEV.PSA_MARKET_SALES.INTERMEDIATE_DS_REF_CATEGORY order by MDM_MARKET_DEFINITION_ID";
                        
                var rs = snowflake.execute({sqlText: sql_command});
                var json_obj = {};
                var array_of_rows = [];
                while (rs.next())  {
                            var json_object = {};
                            json_object["MDM_MARKET_DEFINITION_ID"]=rs.getColumnValue(1);
                            json_object["MARKET_ID"]=rs.getColumnValue(2);
                            json_object["INCLUSION_FLAG"]=rs.getColumnValue(3);
                            json_object["MARKET_DEFINITION_NAME"]=rs.getColumnValue(4);
                            json_object["WHERE_CONDITION"]=rs.getColumnValue(5);                
                            array_of_rows.push(json_object);    
                        }

    /* 通过循环，将所有规则数据插入最终的MDM_MARKET_FACT表 */
                
            for(var m = 0 ; m < array_of_rows.length; m++){
            
                let final_insert_script=`INSERT INTO DF_CHC_DEV.PSA_MARKET_SALES.MDM_MARKET_FACT(SOURCE_PRODUCT_ID, MARKET_ID, MARKET_LEVEL_0) select SP.SOURCE_PRODUCT_ID,${array_of_rows[m].MARKET_ID},\'${array_of_rows[m].MARKET_DEFINITION_NAME}\' from DF_CHC_DEV.PSA_MARKET_SALES.SOURCE_PRODUCT AS SP JOIN DF_CHC_DEV.PSA_MARKET_SALES.PANEL AS PL ON SP.PANEL_ID  = PL.PANEL_ID  ${array_of_rows[m].WHERE_CONDITION}`;
    
    /* 执行SQL命令 */
                var rs_final = snowflake.execute({sqlText: final_insert_script});
                            
    }   

            return "数据插入成功";

    $$              
;


CALL SP_CHC_D_CATEGORIZING_MULTIPLE_INSERT_SCRIPT (ARRAY_CONSTRUCT('COUNTRY_NAME_OPERATOR','PANEL_NAME_OPERATOR','CHANNEL_NAME_OPERATOR','CLASS_TYPE_OPERATOR','ATC4_OPERATOR','PRODUCT_LOCAL_OPERATOR','PRODUCT_OPERATOR','MANUFACTURER_OPERATOR','CORPORATION_LOCAL_OPERATOR','CORPORATION_OPERATOR','MOLECULE_LIST_LOCAL_OPERATOR','MOLECULE_LIST_OPERATOR','PACK_LOCAL_OPERATOR','PACK_OPERATOR','NFC123_LOCAL_OPERATOR','NFC123_OPERATOR','CHC_CLASS_OPERATOR','CHC_FORM_OPERATOR','RX_OPERATOR'),ARRAY_CONSTRUCT('COUNTRY_NAME','PANEL_NAME','CHANNEL_NAME','CLASS_TYPE','ATC4','PRODUCT_LOCAL','PRODUCT','MANUFACTURER','CORPORATION_LOCAL','CORPORATION','MOLECULE_LIST_LOCAL','MOLECULE_LIST','PACK_LOCAL','PACK','NFC123_LOCAL','NFC123','CHC_CLASS','CHC_FORM','RX_STATUS'));

内容的提问来源于stack exchange，提问作者Jerry Arnold