Sélection à partir d’une procédure stockée

Certaines procédures stockées renvoient des données tabulaires. Pour sélectionner et manipuler ces données tabulaires, vous pouvez appeler ces procédures stockées dans la clause FROM d’une instruction SELECT.

Exécuter une instruction SELECT avec le mot clé TABLE

Lors de l’appel de la procédure stockée, omettez la commande CALL. Utilisez plutôt le mot-clé TABLE et nommez la procédure entre parenthèses :

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

Exemple de sélection à partir d’une procédure stockée

Cet exemple utilise les données du tableau suivant :

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);

La procédure stockée suivante renvoie des informations de commande en fonction d’un ID utilisateur :

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;
$$
;

L’instruction SELECT suivante récupère les résultats de la procédure stockée :

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 |
+----------+------------+--------------+

Limitations de la sélection à partir d’une procédure stockée

Les limitations suivantes s’appliquent à la sélection à partir d’une procédure stockée :

  • Seules les procédures stockées qui exécutent les instructions SELECT, SHOW, DESCRIBE, ou CALL peuvent être placées dans la clause FROM d’une instruction SELECT. Les procédures stockées qui effectuent des modifications à l’aide d’opérations DDL ou DML ne sont pas autorisées. Pour les procédures stockées qui émettent des instructions CALL, ces limitations s’appliquent aux procédures stockées qui sont appelées.

  • Seules les procédures stockées qui renvoient des données tabulaires avec un schéma de sortie statique peuvent être placées dans la clause FROM d’une instruction SELECT. Les colonnes de sortie doivent être nommées et typées. Par exemple, une procédure stockée avec la clause RETURNS suivante est prise en charge :

    RETURNS TABLE (col1 INT, col2 STRING)

    Une procédure stockée avec la clause RETURNS suivante n’est pas prise en charge car elle ne renvoie pas de données tabulaires :

    RETURNS STRING

    Une procédure stockée avec la clause RETURNS suivante n’est pas prise en charge car elle ne fournit pas de schéma de sortie fixe :

    RETURNS TABLE()

  • La procédure stockée doit être appelée dans la clause FROM d’un bloc SELECT dans l’une des instructions suivantes :

  • La procédure stockée ne peut pas accepter d’arguments d’entrée corrélés provenant de leur portée externe, comme une référence à un CTE défini en dehors de l’instruction SELECT.

  • Si un argument contient une sous-requête, cette sous-requête ne peut pas utiliser de CTE défini par la clause WITH.

  • Une instruction SELECT contenant un appel de procédure stockée ne peut pas être utilisée dans le corps d’une vue, une fonction définie par l’utilisateur (UDF), une fonction de table définie par l’utilisateur (UDTF), ou dans des objets tels que des politiques d’accès aux lignes et des politiques de masquage des données.

  • Vous ne pouvez pas utiliser de variables de liaison dans une instruction SELECT qui appelle une procédure stockée. Par exemple, les instructions SELECT suivantes ne sont pas autorisées :

    SELECT * FROM TABLE(my_stored_procedure(?));

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