类别：

系统函数 （系统信息）

SYSTEM$SET_RETURN_VALUE

显式设置任务的返回值。

在 任务图 中，任务可以调用此函数来设置返回值。将此任务标识为前置任务的另一个任务（在任务定义中使用 AFTER 关键字）可以使用 SYSTEM$GET_PREDECESSOR_RETURN_VALUE 检索前置任务设置的返回值。

语法

SYSTEM$SET_RETURN_VALUE( '<string_expression>' )
Copy

实参

string_expression

要设置为返回值的字符串。字符串大小必须 <= 10 kB（使用 UTF8 编码时）。

示例

创建设置返回值的任务。创建在前置任务任务完成后运行的第二个子任务。子任务检索由前置任务设置的返回值（通过调用 SYSTEM$GET_PREDECESSOR_RETURN_VALUE）并将其插入表行：

-- create a table to store the return values.
create or replace table return_values (str varchar);

-- create a task that sets the return value for the task.
create task set_return_value
  warehouse=return_task_wh
  schedule='1 minute' as
  call system$set_return_value('The quick brown fox jumps over the lazy dog');

-- create a task that identifies the first task as the predecessor task and retrieves the return value set for that task.
create task get_return_value
  warehouse=return_task_wh
  after set_return_value
  as
    insert into return_values values(system$get_predecessor_return_value());


-- Note that if there are multiple predecessor tasks that are enabled, you must specify the name of the task to retrieve the return value for that task.
create task get_return_value_by_pred
  warehouse=return_task_wh
  after set_return_value
  as
    insert into return_values values(system$get_predecessor_return_value('SET_RETURN_VALUE'));

-- resume task (using ALTER TASK ... RESUME).
-- wait for task to run on schedule.

select distinct(str) from return_values;
+-----------------------------------------------+
|                      STR                      |
+-----------------------------------------------+
|  The quick brown fox jumps over the lazy dog  |
+-----------------------------------------------+

select distinct(RETURN_VALUE)
  from table(information_schema.task_history())
  where RETURN_VALUE is not NULL;

+-----------------------------------------------+
|                  RETURN_VALUE                 |
+-----------------------------------------------+
|  The quick brown fox jumps over the lazy dog  |
+-----------------------------------------------+
Copy

与第一个示例类似，但设置任务的返回值，并通过调用单独的存储过程来检索它：

-- create a table to store the return values.
create or replace table return_values_sp (str varchar);

-- create a stored procedure that sets the return value for the task.
create or replace procedure set_return_value_sp()
returns string
language javascript
execute as caller
as $$
  var stmt = snowflake.createStatement({sqlText:`call system$set_return_value('The quick brown fox jumps over the lazy dog');`});
  var res = stmt.execute();
$$;

-- create a stored procedure that inserts the return value for the predecessor task into the 'return_values_sp' table.
create or replace procedure get_return_value_sp()
returns string
language javascript
execute as caller
as $$
var stmt = snowflake.createStatement({sqlText:`insert into return_values_sp values(system$get_predecessor_return_value());`});
var res = stmt.execute();
$$;

-- create a task that calls the set_return_value stored procedure.
create task set_return_value_t
warehouse=warehouse1
schedule='1 minute'
as
  call set_return_value_sp();

-- create a task that calls the get_return_value stored procedure.
create task get_return_value_t
warehouse=warehouse1
after set_return_value_t
as
  call get_return_value_sp();

-- resume task.
-- wait for task to run on schedule.

select distinct(str) from return_values_sp;
+-----------------------------------------------+
|                      STR                      |
+-----------------------------------------------+
|  The quick brown fox jumps over the lazy dog  |
+-----------------------------------------------+

select distinct(RETURN_VALUE)
  from table(information_schema.task_history())
  where RETURN_VALUE is not NULL;

+-----------------------------------------------+
|                  RETURN_VALUE                 |
+-----------------------------------------------+
|  The quick brown fox jumps over the lazy dog  |
+-----------------------------------------------+
Copy
语言: 中文