Passing references for objects and queries to stored procedures¶

In cases in which you call a stored procedure and pass an identifier for a table, view, function, or procedure to a stored procedure, you might need to:

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

In these cases, you can create and pass in a reference to the object (for example, the table, view, function, or procedure). A reference is a unique identifier for an object. Within the stored procedure, when you execute SQL actions on a reference to an object, the actions are performed using the active role or secondary roles of the user who created the reference. In addition, if the object identifier is not fully qualified, the name of the object is resolved by using the current database and schema when the reference was created (in other words, the database and schema of the user who created the reference).

Similarly, if you need to pass in a query to a stored procedure and use that query in the FROM clause of a SELECT statement, you can create and pass in a query reference. Within the stored procedure, the query is performed using the active role or secondary roles of the user who created the query reference. As is the case with references to objects, if the object name in the query is not fully qualified, the name of the object is resolved by using the database and schema that were in use when the query reference was created.

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

Background: The problem with passing objects and queries to stored procedures¶

假设某个所有者权限存储过程旨在将行插入到一个表中，该表通过输入实参指定。以下是用 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

备注

You cannot use the TABLE keyword with the name of a function or procedure.

如果要创建对查询的引用，且该引用只需要对调用范围有效（而不是对整个会话有效），可以使用 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 不支持将引用作为输入实参。
You can only create references to tables, views, functions, and procedures.
在包含引用的查询中，不能使用计划缓存和结果缓存。
对于查询引用：
- 只能为用作内联视图的 SELECT 语句创建查询引用。
- 创建查询引用时，不能指定绑定变量或会话变量。
- 在存储过程中，只能在 SELECT 语句的 FROM 子句中使用查询引用。