如何在Snowflake中根据返回值提交/回滚存储过程? [英] How to Commit/Rollback stored procedure based on return value in Snowflake?
本文介绍了如何在Snowflake中根据返回值提交/回滚存储过程?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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屋!
查看全文