如何在Snowflake中根据返回值提交/回滚存储过程？ [英] How to Commit/Rollback stored procedure based on return value in Snowflake?

问题描述

我有一个返回布尔值的javascript存储过程。我希望能够在事务内调用此存储过程，并在提交事务之前测试返回值。

BEGIN;
SET result = CALL my_stored_proc();

IF $result = true 
COMMIT;
ELSE
ROLLBACK;

如果存在异常或根据业务逻辑缺少某些值，则存储过程将返回False。

如何在Snowflake中实现此目标？

---

编辑： 我尝试了以下方法，但没有奏效。事务保持打开状态，在发出显式ROLLBACK语句之前，不会回滚在存储过程中执行的语句。

USE COMMON;


CREATE TABLE LOG
(
LOG_ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_LOG_ID PRIMARY KEY,
LOG_LEVEL VARCHAR (50) NOT NULL,
LOGGER VARCHAR (500) NOT NULL,
MESSAGE VARCHAR NOT NULL,
EXCEPTION VARCHAR NULL,
APPLICATION_NAME VARCHAR(500) NOT NULL,
LOGGED_ON DATETIME DEFAULT TO_TIMESTAMP_NTZ(CURRENT_TIMESTAMP()) NOT NULL,
LOGGED_BY VARCHAR(200) DEFAULT CURRENT_USER() NOT NULL
);

CREATE OR REPLACE PROCEDURE TEST_TRANSACTION() 
RETURNS VARCHAR 
LANGUAGE javascript 
EXECUTE AS CALLER
AS 
$$
snowflake.execute({sqlText:`
INSERT
            INTO
                COMMON.LOG (LOG_LEVEL, LOGGER, MESSAGE, EXCEPTION, APPLICATION_NAME)
            VALUES ('DEBUG', 'TEST_TRANSACTION', 'This is a test transaction.', '', 'DATA LOADER'); 
`});
snowflake.execute({sqlText:`
INSERT
            INTO
                COMMON.LOG (LOG_LEVEL, LOGGER, MESSAGE, EXCEPTION, APPLICATION_NAME)
            VALUES ('DEBUG', 'TEST_TRANSACTION', 'This message should not be in the logs.', '', 'DATA LOADER'); 
`});

throw 'Test error'
$$
;
-- Run the next 3 lines as a batch
BEGIN;
CALL COMMON.TEST_TRANSACTION(); -- This Fails
COMMIT; -- Commit is NOT executed

SELECT * FROM COMMON.LOG WHERE LOGGER = 'TEST_TRANSACTION'; -- The insert is successful & the transaction is still OPEN

如何回滚在存储过程内执行的语句？

推荐答案

所以您可以在存储过程内发出事务控制命令(BEGIN、COMMIT、ROLLBACK)。我可以作为事务，检查存储过程中的返回值，然后决定提交或回滚。

应该有人更新雪花文档。

CREATE OR REPLACE PROCEDURE TEST_TRANSACTION(result BOOLEAN) 
RETURNS BOOLEAN NOT NULL 
LANGUAGE javascript 
EXECUTE AS CALLER
AS 
$$
snowflake.execute({sqlText:`BEGIN;`});

snowflake.execute({sqlText:`
INSERT
            INTO
                COMMON.LOG (LOG_LEVEL, LOGGER, MESSAGE, EXCEPTION, APPLICATION_NAME)
            VALUES ('DEBUG', 'TEST_TRANSACTION', 'This is a test transaction.', '', 'DATA LOADER'); 
`});
snowflake.execute({sqlText:`
INSERT
            INTO
                COMMON.LOG (LOG_LEVEL, LOGGER, MESSAGE, EXCEPTION, APPLICATION_NAME)
            VALUES ('DEBUG', 'TEST_TRANSACTION', 'This message should not be in the logs.', '', 'DATA LOADER'); 
`});

if ( RESULT ) { snowflake.execute( {sqlText: "COMMIT" }) } 
    else {
        snowflake.execute( {sqlText: "ROLLBACK" }) };

return true;
$$
;

这篇关于如何在Snowflake中根据返回值提交/回滚存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！