如何在雪花中基于返回值提交/回滚存储过程？

Question

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

BEGIN;
SET result = CALL my_stored_proc();

IF $result = true 
COMMIT;
ELSE
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

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

Answer 1

因此，可以在存储过程中发出事务控制命令(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;
$$
;