使用存储过程¶

借助存储过程，用户能够将多条 SQL 语句与过程式逻辑组合起来，以创建可以包含复杂业务逻辑的模块化代码。

本主题内容：

备注

要创建和调用匿名过程，请使用 CALL（使用匿名过程）。创建和调用匿名过程不需要具有 CREATE PROCEDURE 架构权限的角色。

存储过程的命名约定¶

必须按照 Snowflake 强制实施的约定来命名过程。

有关更多信息，请参阅命名和重载过程和 UDFs。

事务管理¶

存储过程不是原子的；如果存储过程中的一条语句失败，则存储过程中的其他语句不一定会回滚。

可以将存储过程与事务结合使用，以使一组语句原子化。有关详细信息，请参阅存储过程和事务。

一般提示¶

对称代码¶

如果您熟悉汇编语言编程，可能会发现以下类比很有帮助。在汇编语言中，函数通常以对称方式创建和撤消其环境。例如：

-- Set up.
push a;
push b;
...
-- Clean up in the reverse order that you set up.
pop b;
pop a;

Copy

您可能希望在存储过程中使用此方法：

如果存储过程对会话进行了临时变更，则该过程应在返回之前撤消这些变更。
如果存储过程使用了异常处理或分支，或者使用了可能对执行哪些语句有影响的其他逻辑，则无论在特定调用期间选择了哪个分支，都需要清理创建的任何内容。

例如，您的代码可能类似于下面所示的伪代码：

CREATE PROCEDURE f() ...
    $$
    set x;
    set y;
    try  {
       set z;
       -- Do something interesting...
       ...
       unset z;
       }
    catch  {
       -- Give error message...
       ...
       unset z;
       }
    unset y;
    unset x;
    $$
    ;

Copy

调用存储过程¶

使用 SQL 命令来调用存储过程。有关调用存储过程的更多信息，请参阅调用存储过程。

从存储过程中进行选择¶

在 SELECT 语句的 FROM 子句中调用存储过程。有关从存储过程中进行选择的更多信息，请参阅从存储过程中进行选择。

权限¶

存储过程使用两种类型的权限：

对存储过程本身的直接权限。
对存储过程访问的数据库对象（例如表）的权限。

存储过程的权限¶

与其他数据库对象（表、视图、UDFs 等）类似，存储过程由角色拥有，并具有可授予其他角色的一个或多个权限。

目前，以下权限适用于存储过程：

USAGE
OWNERSHIP

要让角色使用存储过程，角色必须是存储过程的所有者，或者已获授予对存储过程的 USAGE 权限。

存储过程访问的数据库对象的权限¶

此主题在了解调用方权限和所有者权限存储过程中介绍。

存储过程注意事项¶

尽管存储过程允许嵌套和递归，但对于用户定义的存储过程，嵌套调用的当前最大堆栈深度为 16（包括顶层存储过程）。如果调用链中的单个存储过程消耗了大量资源，则该深度可能会更小。
在极少数情况下，同时调用过多存储过程可能会导致死锁。

在 Snowsight 中使用存储过程¶

您可以在 SQL 或在 Snowsight 中使用存储过程。

For any stored procedure in Snowflake, you can open Catalog » Database Explorer and search for or browse to the stored procedure. Select the stored procedure to review details and manage the procedure.

您必须具有相关权限来访问和管理 Snowsight 中的存储过程。

Explore stored procedure details in Snowsight¶

在 Snowsight 中打开存储过程后，您可以执行以下操作：

确定过程的创建时间，以及有关该过程的任何注释。您可以将鼠标悬停在时间详细信息上以查看确切的创建日期和时间。
查看有关存储过程的其他详细信息，包括：
- 存储过程采用的实参（如果适用）。
- 过程结果的数据类型。
- 过程是否为聚合函数。
- 过程是否为安全函数。
- 过程是否为表函数。
- 编写存储过程时使用的语言。例如 JavaScript。
查看 Procedure definition 部分中存储过程的 SQL 定义。
查看 Privileges 部分中具有存储过程权限的角色。

Manage a stored procedure in Snowsight¶

您可以在 Snowsight 中对存储过程执行以下基本管理任务：

To edit the stored procedure name or add a comment, select » Edit.
To drop the stored procedure, select » Drop.
To transfer ownership of the stored procedure to another role, select » Transfer Ownership

SQL 注入¶

存储过程可以动态创建 SQL 语句并执行它。但是，这可能允许进行 SQL 注入攻击，尤其是在使用来自公共源或不受信任源的输入创建 SQL 语句时。

要最大程度降低 SQL 注入攻击的风险，可以绑定参数而不是连接文本。有关绑定变量的示例，请参阅绑定变量。

如果选择使用连接，则在使用来自公共源的输入动态构造 SQL 时，应仔细检查输入。另外还可能需要采取其他预防措施，例如，查询时使用权限受限制（如只读权限或只能访问某些表或视图）的角色。

有关 SQL 注入攻击的更多信息，请参阅 SQL 注入 (link removed) （维基百科）。

存储过程的设计提示¶

下面是设计存储过程的一些提示：

此存储过程需要哪些资源（例如表）？
需要哪些权限？

思考要访问哪些数据库对象，哪些角色将运行存储过程，以及这些角色需要哪些权限。

如果该过程应为调用方的权限存储过程，则可能需要创建一个角色，以便运行该特定过程或一组相关过程中的任何一个。然后，可以向该角色授予任何必需的权限，之后将该角色授予适当的用户。
存储过程应使用调用方权限还是所有者权限运行？有关此主题的更多信息，请参阅了解调用方权限和所有者权限存储过程。
过程应如何处理错误？例如，如果缺少必需的表或者实参无效，过程应如何处理？
存储过程是否应通过写入日志表等方式来记录其活动或错误？
另请参阅有关何时使用存储过程与何时使用 UDF 的讨论：选择是编写存储过程还是用户定义函数。

记录存储过程¶

存储过程通常编写为可重用，并且经常被共享。记录存储过程可以使存储过程更易于使用和维护。

下面是记录存储过程的一些一般建议。

通常，至少有两个受众想要了解存储过程：

用户/调用方。
程序员/作者。

对于用户（和程序员），请记录以下各项：

存储过程的名称。

存储过程（数据库和架构）的“位置”。

存储过程的用途。

每个输入参数的名称、数据类型和含义。

返回值的名称、数据类型和含义。如果返回值是复杂类型（例如包含子字段的 VARIANT ），请记录这些子字段。

如果存储过程依赖于其环境中的信息（例如会话变量或会话参数），请记录具体名称、用途和有效值。

返回的错误、引发的异常等。

运行该过程所需的角色或权限。（有关此主题的更多信息，请参阅存储过程的设计提示中的角色讨论。）

过程是调用方的权限过程还是所有者的权限过程。

任何先决条件，例如在调用过程之前必须存在的表。

任何输出（包括返回值），例如创建的新表。

任何“副作用”，例如权限变更、删除旧数据等。与函数不同，大多数存储过程是专门针对其“副作用”而不是返回值调用的，因此请确保记录这些“副作用”。

如果在运行存储过程后需要清理，请记录该清理。

过程是否可以作为多语句事务的一部分调用（使用 AUTOCOMMIT=FALSE），或者是否应该在事务外部运行（使用 AUTOCOMMIT=TRUE）。

调用的示例和返回的内容的示例。

限制（如果适用）。例如，假设过程读取一个表，并返回一个包含表中每行信息的 VARIANT。该 VARIANT 可能会变得比 VARIANT 的法定大小上限更大，因此，您可能需要让调用方了解过程访问的表中的最大行数。

警告（如果适用）。

故障排除提示。

对于程序员：

作者。

解释将过程创建为调用方权限过程或所有者权限过程的原因 – 原因可能并不明显。

可以嵌套存储过程，但嵌套深度受到限制。如果存储过程调用其他存储过程，并且其本身可能被其他存储过程调用，则可能需要指定存储过程调用堆栈的最大已知深度，以便调用方了解调用存储过程是否可能会超过最大调用堆栈深度。

调试提示。

此信息的位置和格式由您决定。例如，可以将此信息以 HTML 格式存储在内部网站中。在决定存储在何处之前，请思考组织将其他产品的类似信息存储在何处，或者将其他 Snowflake 功能（如视图、用户定义的函数等）的类似信息存储在何处。

其他提示：

在源代码中包含注释，就像对待几乎任何一段源代码那样。
- 请记住，逆向推导出代码的含义是很困难的。不仅要描述算法是如何工作的，还要描述该算法的用途。
存储过程允许添加可选的 COMMENT，您可以使用 CREATE PROCEDURE 或 ALTER PROCEDURE 语句指定此注释。其他人可以通过运行 SHOW PROCEDURES 命令来阅读此注释。
如果可行，请考虑在源代码控制系统中保留每个存储过程的 CREATE PROCEDURE 命令的主副本。Snowflake 的 Time Travel 功能不适用于存储过程，因此，必须在 Snowflake 外部查找存储过程的旧版本。如果源代码控制系统不可用，则可以在一定程度上模拟该系统，方法是：将 CREATE PROCEDURE 命令存储在表中的 VARCHAR 字段内，并且添加每个新版本（但不替换旧版本）。
考虑使用命名约定来帮助提供有关存储过程的信息。例如，名称中的前缀或后缀可能指示过程是调用方权限存储过程还是所有者权限存储过程。（例如，可以使用 cr_ 作为前缀来指示调用方权限。）
要查看输入实参的数据类型和顺序以及注释，可以使用 SHOW PROCEDURES 命令。但请注意，该命令仅显示实参的名称和数据类型；它并没有解释实参。
如果您具有适当的权限，则可以使用 DESCRIBE PROCEDURE 命令查看以下信息：
- 实参的名称和数据类型。
- 过程的主体，以及过程是以所有者身份还是以调用方身份执行。
- 返回值的数据类型。
- 其他有用信息。