将表、视图、函数和查询的引用传递给存储过程¶

如果调用存储过程，并将表、视图或函数的标识符传递给存储过程，您可能需要：

允许存储过程对对象执行 SQL 操作，即使存储过程使用所有者权限也是如此。
如果标识符并非限定标识符或部分限定标识符，则允许存储过程解析对象的完全限定名称。

在此类情况下，您可以创建并传入对表、视图或函数的引用。引用是表、视图或函数的唯一标识符。在存储过程中，对于对象的引用执行 SQL 操作时，这些操作将使用创建引用的用户的活动角色或辅助角色执行。此外，如果表、视图或函数不是完全限定的，则使用创建引用时的当前数据库和架构（即创建引用的用户的数据库和架构）来解析对象名称。

同样，如果需要将查询传入存储过程，并在 SELECT 语句的 FROM 子句中使用该查询，则可以创建并传入查询引用。在存储过程中，查询使用创建查询引用的用户的活动角色或辅助角色执行。与对表、视图和函数的引用一样，如果查询中的对象名称并非完全限定形式，则使用创建查询引用时在使用的数据库和架构来解析对象名称。

本主题介绍如何创建和使用引用。

背景：与向存储过程传递表、视图和查询有关的问题¶

假设某个所有者权限存储过程旨在将行插入到一个表中，该表通过输入实参指定。以下是用 Snowflake Scripting 和 JavaScript 编写的示例：

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE insert_row(table_identifier VARCHAR)
RETURNS TABLE()
LANGUAGE SQL
AS
$$
BEGIN
  LET stmt VARCHAR := 'INSERT INTO ' || table_identifier || ' VALUES (10)';
  LET res RESULTSET := (EXECUTE IMMEDIATE stmt);
  RETURN TABLE(res);
END;
$$;

Copy

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE insert_row(table_identifier VARCHAR)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
  let res = snowflake.execute({
    sqlText: "INSERT INTO IDENTIFIER(?) VALUES (10);",
    binds : [TABLE_IDENTIFIER]
  });
  res.next()
  return res.getColumnValue(1);
$$;

Copy

假设您需要为另外一个角色拥有的表调用此过程：

USE ROLE table_owner;

CREATE OR REPLACE TABLE table_with_different_owner (x NUMBER) AS SELECT 42;

Copy

如您调用存储过程并传入表的名称，存储过程会失败，因为存储过程的所有者没有足够的权限来访问此表：

USE ROLE table_owner;

CALL insert_row('table_with_different_owner');

Copy

002003 (42S02): Uncaught exception of type 'STATEMENT_ERROR' on line 4 at position 25 : SQL compilation error:
Table 'TABLE_WITH_DIFFERENT_OWNER' does not exist or not authorized.

若要使存储过程能够以调用方身份对表执行 SQL 操作，请创建对表的引用并传入该引用，而非表名。

创建引用¶

若要创建引用，请调用 SYSTEM$REFERENCE 函数。例如：

USE ROLE table_owner;

CALL insert_row(SYSTEM$REFERENCE('TABLE', 'table_with_different_owner', 'SESSION', 'INSERT'));

Copy

上面的示例将以下实参传递给 SYSTEM$REFERENCE 函数：

'TABLE' （表示对象的类型）。
'table_with_different_owner' （表示表的名称）。
'SESSION' 指示引用范围应限定为会话。
'INSERT' 作为对于对象执行操作所需的权限。

备注

如果您需要创建对不打算修改的对象的引用（例如，如果您传递了存储过程将查询的表）并且希望该引用在调用范围内有效（而不是整个会话），您可以使用 TABLE 关键字，而非调用 SYSTEM$REFERENCE。有关详细信息，请参阅使用 TABLE 关键字创建对表、视图或查询的引用。

指定引用范围¶

在传递引用的调用持续时间或会话的持续时间内，引用保持有效。创建引用的上下文决定了其范围：

如果在单独一条语句中创建并传递了对存储过程的引用，则该引用的可见性与在存储过程的最外层块中声明的变量相同：
```
CALL select_from_table(SYSTEM$REFERENCE('TABLE', 'my_table');
```
Copy
如果创建引用并将引用分配给会话变量，即便取消设置了会话变量，引用在会话期间依然保持有效：
```
SET tableRef = (SELECT SYSTEM$REFERENCE('TABLE', 'my_table'));

SELECT * FROM IDENTIFIER($tableRef);
```
Copy

若要将引用的范围指定为会话的持续时间，而不考虑创建引用的上下文，请将 'SESSION' 作为 SYSTEM$REFERENCE 函数的第三个实参 (session_scope) 传递。

CALL insert_row(SYSTEM$REFERENCE('TABLE', 'table_with_different_owner', 'SESSION', 'INSERT'));

Copy

在引用中授予额外的权限¶

默认情况下，引用根据所引用对象的类型授予权限的某个子集。例如，对表的引用为创建引用的用户的活动角色或辅助角色授予该表的 SELECT 权限。默认权限取决于对象类型。有关支持的对象、权限和默认权限的列表，请参阅支持的引用对象类型和权限。

若要授予额外权限，请将这些权限指定为 SYSTEM$REFERENCE 函数的附加实参。例如，要授予表的 INSERT、UPDATE 和 TRUNCATE 权限，请执行以下操作：

SELECT SYSTEM$REFERENCE('TABLE', 'table_with_different_owner', 'SESSION', 'INSERT', 'UPDATE', 'TRUNCATE');

Copy

请注意，不能指定 OWNERSHIP 或 ALL 作为权限。

创建引用后，对引用创建者的权限所做的更改将体现在与引用关联的权限中。例如，如果为引用创建者撤销了 INSERT 权限，该 INSERT 权限将不再与引用关联。

将对表和视图的引用与掩码策略结合使用¶

使用对于具有掩码策略的表或视图的引用时，引用角色是调用者角色（INVOKER_ROLE 返回的角色），而不论引用是在查询、存储过程还是用户定义的函数中使用。

使用引用并不会更改当前角色（CURRENT_ROLE 返回的角色）。

在存储过程中创建引用¶

如果要编写所有者权限存储过程，请不要在存储过程的正文中创建引用。

在所有者权限存储过程中创建的引用会使用存储过程所有者的角色。引用应使用调用存储过程的用户的角色。对于所有者权限存储过程，应该由调用存储过程的用户创建引用，并将其传递给存储过程。

如果要编写调用者权限存储过程，则可以在存储过程的正文中创建引用。

使用查询引用¶

如果需要传入在存储过程中 SELECT 语句的 FROM 子句中使用的查询，请创建并传入查询引用。

例如，假设存储过程传入一个 SELECT 语句，该语句的目的是在另一个 SELECT 语句的 FROM 子句中使用。在下面的示例中，查询实参应该是 SELECT 语句。此示例使用 Snowflake Scripting 和 JavaScript：

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
  RETURNS INTEGER
  LANGUAGE SQL
  AS
  DECLARE
    row_count INTEGER DEFAULT 0;
    stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
    res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
    cur CURSOR FOR res;
  BEGIN
    OPEN cur;
    FETCH cur INTO row_count;
    RETURN row_count;
  END;

Copy

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
  let res = snowflake.execute({
    sqlText: "SELECT COUNT(*) FROM (" + QUERY + ");",
  });
  res.next()
  return res.getColumnValue(1);
$$;

Copy

存储过程会使用所有者的权限。如果存储过程所有者没有在 SELECT 语句中查询表的权限，则对存储过程的调用将失败。

USE ROLE table_owner;
CREATE OR REPLACE TABLE table_with_different_owner (x NUMBER) AS SELECT 42;

CALL get_num_results('SELECT x FROM table_with_different_owner');

Copy

002003 (42S02): Uncaught exception of type 'STATEMENT_ERROR' on line 4 at position 29 : SQL compilation error:
Object 'TABLE_WITH_DIFFERENT_OWNER' does not exist or not authorized.

若要使存储过程能够以调用者身份执行查询，请为 SELECT 语句创建查询引用，并传入该引用而非 SELECT 语句。

要创建查询引用，可以调用 SYSTEM$QUERY_REFERENCE 函数。

备注

如果需要创建对调用范围（而不是整个会话）有效的查询引用，可以使用 TABLE 关键字，而不是调用 SYSTEM$QUERY_REFERENCE。有关详细信息，请参阅使用 TABLE 关键字创建对表、视图或查询的引用。

如果调用 SYSTEM$QUERY_REFERENCE 函数，请传入：

'SELECT x FROM table_with_different_owner' （作为查询）。

请注意，如果 SELECT 语句包含任何单引号或其他特殊字符（例如换行符），则必须使用反斜杠对这些字符进行转义。
true （指示查询引用范围应限定为会话）。

例如：

USE ROLE table_owner;

CALL get_num_results(
  SYSTEM$QUERY_REFERENCE('SELECT x FROM table_with_different_owner', true)
);

Copy

+-----------------+
| GET_NUM_RESULTS |
|-----------------|
|               1 |
+-----------------+

在存储过程中，可以添加对查询的 FROM 子句的查询引用。例如：

snowflake.execute({
  sqlText: "SELECT COUNT(*) FROM (" + QUERY + ");"
});

Copy

有关此函数的详细信息，请参阅 SYSTEM$QUERY_REFERENCE。

有关创建和使用查询引用的限制，请参阅当前限制。

使用 TABLE 关键字创建对表、视图或查询的引用¶

如果您需要创建对您没有修改且存储过程应查询的表、视图或安全视图的引用，并且希望该引用对调用范围有效（而不是对整个会话有效），请使用采用以下语法的 TABLE 关键字：

TABLE( [[<database_name>.]<schema_name>.]<object_name> )

Copy

TABLE("<object_name_that_requires_double_quotes>")

Copy

TABLE(IDENTIFIER('string_literal_for_object_name'))

Copy

TABLE 关键字为调用表或视图的 SYSTEM$REFERENCE 函数提供了更简单的语法，无需指定对象类型的实参。使用 TABLE 关键字时，引用只授予 SELECT 权限，引用的作用范围是调用（而不是会话）。

以下示例调用存储过程 my_procedure 并传入对表和视图的引用：

CALL my_procedure(TABLE(my_table));

Copy

CALL my_procedure(TABLE(my_database.my_schema.my_view));

Copy

CALL my_procedure(TABLE("My Table Name"));

Copy

CALL my_procedure(TABLE(IDENTIFIER('my_view')));

Copy

如果要创建对查询的引用，且该引用只需要对调用范围有效（而不是对整个会话有效），可以使用 TABLE 关键字作为调用 SYSTEM$QUERY_REFERENCE 函数的替代方法。要使用 TABLE 关键字，请使用以下语法：

TABLE(<select_statement>)

Copy

例如：

CALL my_procedure(TABLE(SELECT * FROM my_view));

Copy

CALL my_procedure(TABLE(WITH c(s) as (SELECT $1 FROM VALUES (1), (2)) SELECT a, count(*) FROM T, C WHERE s = a GROUP BY a));

Copy

请注意以下事项：

不得在对象名称或查询中使用绑定变量。
TABLE 关键字创建的引用在调用期间有效。不得为引用指定其他范围。
引用具有向对象类型授予的默认权限。

当前限制¶

目前，引用具有以下限制：

GET_DDL 和 SYSTEM$GET_TAG 不支持将引用作为输入实参。
只能创建对表、视图和函数的引用。
在包含引用的查询中，不能使用计划缓存和结果缓存。
对于查询引用：
- 只能为用作内联视图的 SELECT 语句创建查询引用。
- 创建查询引用时，不能指定绑定变量或会话变量。
- 在存储过程中，只能在 SELECT 语句的 FROM 子句中使用查询引用。