Tabellarische Rückgabe mit Scala in mit SQL gespeicherten Prozeduren¶

Sie können eine Prozedur schreiben, die Daten in tabellarischer Form zurückgibt. Gehen Sie wie folgt vor, um eine Prozedur zu schreiben, die Tabellendaten zurückgibt:

Geben Sie in der CREATE PROCEDURE-Anweisung TABLE(...) als Rückgabetyp der Prozedur an.

Als TABLE-Parameter können Sie die Spaltennamen und Typen der zurückgegebenen Daten angeben, sofern Sie diese kennen. Wenn Sie die zurückgegebenen Spalten bei der Definition der Prozedur nicht kennen – z. B. wenn sie zur Laufzeit angegeben werden –, können Sie die TABLE-Parameter weglassen. Wenn Sie dies tun, werden die Spalten des Rückgabewerts der Prozedur aus den Spalten des Datenframes konvertiert, der vom Handler zurückgegeben wird. Spaltendatentypen werden gemäß der unter Zuordnung von Datentypen zwischen SQL und Scala angegebenen Zuordnung in SQL konvertiert.
Schreiben Sie den Handler so, dass er das tabellarische Ergebnis in einem Snowpark-Datenframe zurückgibt.

Weitere Informationen zu Datenframes finden Sie unter Verwenden von DataFrames in Snowpark Scala.

Bemerkung

Eine Prozedur generiert zur Laufzeit einen Fehler, wenn eine der folgenden Bedingungen zutrifft:

TABLE Ist als Rückgabetyp deklariert, aber sein Handler gibt keinen Datenframe zurück.
Der Handler gibt einen Datenframe zurück, aber die Prozedur deklariert TABLE nicht als Rückgabetyp.

Beispiel¶

Die Beispiele in diesem Abschnitt veranschaulichen die Rückgabe von Tabellenwerten aus einer Prozedur, die Zeilen herausfiltert, in denen eine Spalte mit einer Zeichenfolge übereinstimmt.

Definieren der Daten¶

Der Code im folgenden Beispiel erstellt eine Tabelle der Mitarbeiter.

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

Deklarieren einer Prozedur zum Filtern von Zeilen¶

Der Code in den folgenden beiden Beispielen erstellt eine gespeicherte Prozedur, die den Tabellennamen und die Rolle als Argumente verwendet und die Zeilen der Tabelle zurückgibt, deren Rollenspaltenwert mit der als Argument angegebenen Rolle übereinstimmt.

Angeben von Namen und Typen der Rückgabespalten¶

In diesem Beispiel werden Spaltennamen und -typen in der RETURNS TABLE()-Anweisung angegeben.

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

Bemerkung

Derzeit können Sie in der RETURNS TABLE(...)-Klausel den Datentyp GEOGRAPHY als Spaltentyp angeben. Dies gilt unabhängig davon, ob Sie eine gespeicherte oder eine anonyme Prozedur erstellen.

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

Wenn Sie versuchen, GEOGRAPHY als Spaltentyp anzugeben, führt der Aufruf der gespeicherten Prozedur zu einem Fehler:

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

Copy

Um dieses Problem zu umgehen, können Sie die Spaltenargumente und -typen in RETURNS TABLE() weglassen.

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

Weglassen von Namen und Typen der Rückgabespalten¶

Der Code im folgenden Beispiel deklariert eine Prozedur, mit der Spaltennamen und -typen des Rückgabewerts aus den im Rückgabewert des Handlers enthaltenen Spalten extrapoliert werden können. Die Spaltennamen und -typen aus der RETURNS TABLE()-Anweisung werden weggelassen.

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

Aufrufen der Prozedur¶

Im folgenden Beispiel wird die gespeicherte Prozedur aufgerufen:

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

Copy

Der Prozeduraufruf erzeugt die folgende Ausgabe:

+----+-------+------+
| ID | NAME  | ROLE |
+----+-------+------+
| 2  | Bob   | dev  |
| 3  | Cindy | dev  |
+----+-------+------+