从存储过程中进行选择

Some stored procedures return tabular data. To select and manipulate this tabular data, you can call these stored procedures in the FROM clause of a SELECT statement.

运行具有 TABLE 关键字的 SELECT 语句¶

When calling the stored procedure, omit the CALL command. Instead, use the TABLE keyword, and name the procedure inside parentheses:

SELECT ... FROM TABLE( <stored_procedure_name>( <arg> [ , <arg> ... ] ) );

从存储过程中进行选择的示例

此示例使用下表中的数据：

CREATE OR REPLACE TABLE orders (
  order_id INT,
  u_id VARCHAR,
  order_date DATE,
  order_amount NUMBER(12,2));

INSERT INTO orders VALUES (1, 'user_id_001', current_date, 500.00);
INSERT INTO orders VALUES (2, 'user_id_003', current_date, 225.00);
INSERT INTO orders VALUES (3, 'user_id_001', current_date, 725.00);
INSERT INTO orders VALUES (4, 'user_id_002', current_date, 150.00);
INSERT INTO orders VALUES (5, 'user_id_002', current_date, 900.00);

以下存储过程会返回基于用户 ID 的订单信息：

CREATE OR REPLACE PROCEDURE find_orders_by_user_id(user_id VARCHAR)
RETURNS TABLE (
  order_id INT, order_date DATE, order_amount NUMBER(12,2)
)
LANGUAGE SQL AS
DECLARE
  res RESULTSET;
BEGIN
  res := (SELECT order_id, order_date, order_amount FROM orders WHERE u_id = :user_id);
  RETURN TABLE(res);
END;

Note: If you use Snowflake CLI, SnowSQL, the Classic Console, or the execute_stream or execute_string method in Python Connector code, use this example instead (see Using Snowflake Scripting in Snowflake CLI, SnowSQL, and Python Connector):

CREATE OR REPLACE PROCEDURE find_orders_by_user_id(user_id VARCHAR)
RETURNS TABLE (
  order_id INT, order_date DATE, order_amount NUMBER(12,2)
)
LANGUAGE SQL AS
$$
DECLARE
  res RESULTSET;
BEGIN
  res := (SELECT order_id, order_date, order_amount FROM orders WHERE u_id = :user_id);
  RETURN TABLE(res);
END;
$$
;

以下 SELECT 语句将检索存储过程的结果：

SELECT * FROM TABLE(find_orders_by_user_id('user_id_001'));

+----------+------------+--------------+
| ORDER_ID | ORDER_DATE | ORDER_AMOUNT |
|----------+------------+--------------|
|        1 | 2024-08-30 |       500.00 |
|        3 | 2024-08-30 |       725.00 |
+----------+------------+--------------+

从存储过程中进行选择的限制

以下限制适用于从存储过程中进行选择：

Only stored procedures that perform SELECT, SHOW, DESCRIBE, or CALL statements can be placed in the FROM clause of a SELECT statement. Stored procedures that make modifications using DDL or DML operations aren’t allowed. For stored procedures that issue CALL statements, these limitations apply to the stored procedures that are called.
Only stored procedures that return tabular data with a static output schema can be placed in the FROM clause of a SELECT statement. The output columns must be named and typed. For example, a stored procedure with the following RETURNS clause is supported:
```
RETURNS TABLE (col1 INT, col2 STRING)
```

不支持具有以下 RETURNS 子句的存储过程，因为它不返回表格数据：

RETURNS STRING

不支持具有以下 RETURNS 子句的存储过程，因为它不提供固定的输出架构：

RETURNS TABLE()

必须使用以下语句之一的 SELECT 块的 FROM 子句调用存储过程：
- SELECT
- INSERT, UPDATE, DELETE, or MERGE
- CREATE TABLE AS SELECT
The stored procedure can’t accept correlated input arguments from their outer scope, such as a reference to any CTE defined outside of the SELECT statement.
如果实参包含子查询，则该子查询不能使用 WITH 子句定义的 CTE。
A SELECT statement containing a stored procedure call can’t be used in the body of a view, a user-defined function (UDF), a user-defined table function (UDTF), or in objects such as row access policies and data masking policies.
You can’t use bind variables in a SELECT statement that calls a stored procedure. For example, the following SELECT statements aren’t allowed:
```
SELECT * FROM TABLE(my_stored_procedure(?));

SELECT * FROM TABLE(my_stored_procedure('a')) WHERE my_var = :var2;
```