在使用 SQL 创建的存储过程中借助 Scala 返回表格

您可以编写一个过程，以表格形式返回数据。若要编写返回表格数据的过程，请执行以下操作：

  • 在 CREATE PROCEDURE 语句中指定 TABLE(...) 作为过程的返回类型。

    您可以指定返回数据的列名和 :doc:` 类型 </sql-reference-data-types>` （如果您知道）作为 TABLE 参数。如果您在定义过程时不知道返回的列（例如在运行时指定列时），则可以省略 TABLE 参数。如果执行此操作，过程的返回值列将通过其处理程序返回的 DataFrame 中的列进行转换。列数据类型将根据 SQL-Scala 数据类型映射 中指定的映射转换为 SQL。

  • 编写处理程序，使其在 Snowpark DataFrame 中返回表格结果。

    有关 DataFrame 的更多信息，请参阅 在 Snowpark Scala 中使用 DataFrames。

备注

如果满足以下任一条件，则过程会在运行时生成错误：

  • 它声明 TABLE 为其返回类型，但其处理程序不返回 DataFrame。

  • 其处理程序返回 DataFrame，但过程未声明 TABLE 为其返回类型。

示例

本节中的示例演示如何通过筛选列与字符串匹配的行的过程返回表格值。

定义数据

以下示例中的代码创建一个员工表。

CREATE OR REPLACE TABLE employees(id NUMBER, name VARCHAR, role VARCHAR);
INSERT INTO employees (id, name, role) VALUES (1, 'Alice', 'op'), (2, 'Bob', 'dev'), (3, 'Cindy', 'dev');
Copy

声明筛选行的过程

以下两个示例中的代码创建一个存储过程，该存储过程将表名称和角色作为实参，并返回表中角色列值与指定为实参的角色匹配的行。

指定返回列名称和类型

此示例在 RETURNS TABLE() 语句中指定列名称和类型。

CREATE OR REPLACE PROCEDURE filter_by_role(table_name VARCHAR, role VARCHAR)
RETURNS TABLE(id NUMBER, name VARCHAR, role VARCHAR)
LANGUAGE SCALA
RUNTIME_VERSION = '2.12'
PACKAGES = ('com.snowflake:snowpark:latest')
HANDLER = 'Filter.filterByRole'
AS
$$
import com.snowflake.snowpark.functions._
import com.snowflake.snowpark._

object Filter {
   def filterByRole(session: Session, tableName: String, role: String): DataFrame = {
     val table = session.table(tableName)
     val filteredRows = table.filter(col("role") === role)
     return filteredRows
   }
}
$$;
Copy

备注

目前，在 RETURNS TABLE(...) 子句中，您无法指定 GEOGRAPHY 作为列类型。这在创建存储过程和匿名过程中均适用。

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
  RETURNS TABLE(g GEOGRAPHY)
  ...
Copy
WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE(g GEOGRAPHY)
  ...
CALL test_return_geography_table_1();
Copy

如果尝试指定 GEOGRAPHY 为列类型，则调用存储过程会导致该错误：

Stored procedure execution error: data type of returned table does not match expected returned table type
Copy

要解决这个问题，可以省略 RETURNS TABLE() 中的列实参和类型。

CREATE OR REPLACE PROCEDURE test_return_geography_table_1()
  RETURNS TABLE()
  ...
Copy
WITH test_return_geography_table_1() AS PROCEDURE
  RETURNS TABLE()
  ...
CALL test_return_geography_table_1();
Copy

省略返回列名称和类型

以下示例中的代码声明一个过程，该过程允许通过处理程序返回值中的列推断返回值列名称和类型。它会忽略 RETURNS TABLE() 语句中的列名称和类型。

CREATE OR REPLACE PROCEDURE filter_by_role(table_name VARCHAR, role VARCHAR)
   RETURNS TABLE()
   LANGUAGE SCALA
   RUNTIME_VERSION = '2.12'
   PACKAGES = ('com.snowflake:snowpark:latest')
   HANDLER = 'Filter.filterByRole'
   AS
   $$
   import com.snowflake.snowpark.functions._
   import com.snowflake.snowpark._

   object Filter {
      def filterByRole(session: Session, tableName: String, role: String): DataFrame = {
         val table = session.table(tableName)
         val filteredRows = table.filter(col("role") === role)
         return filteredRows
      }
   }
$$;
Copy

调用过程

下面的示例调用存储过程：

CALL filter_by_role('employees', 'dev');
Copy

过程调用生成以下输出：

+----+-------+------+
| ID | NAME  | ROLE |
+----+-------+------+
| 2  | Bob   | dev  |
| 3  | Cindy | dev  |
+----+-------+------+
语言: 中文