Stored Procedures Overview

You can write stored procedures to extend the system with procedural code that executes SQL. In a stored procedure, you can use programmatic constructs to perform branching and looping. Once you create a stored procedure, you can reuse it multiple times.

You write a procedure’s logic – its handler – in one of the supported languages. Once you have a handler, you can create a procedure with a CREATE PROCEDURE command, then call the procedure with a CALL statement.

From a stored procedure, you can return a single value or (where supported with the handler language) tabular data. For more information about supported return types, see CREATE PROCEDURE.

Bemerkung

To both create and call an anonymous procedure, use CALL (mit anonymen Prozeduren). Creating and calling an anonymous procedure does not require a role with CREATE PROCEDURE schema privileges.

Bemerkung

A stored procedure is like a UDF, but the two differ in important ways. For more information, see Entscheidung über das Schreiben einer gespeicherten Prozedur oder einer benutzerdefinierten Funktion.

In this Topic:

What is a Stored Procedure?

A stored procedure contains logic you write so you can call it from SQL. A stored procedure’s logic typically performs database operations by executing SQL statements.

With a stored procedure, you can also:

  • Dynamically create and execute SQL statements.

  • Execute code with the privileges of the role that owns the procedure, rather than with the privileges of the role that runs the procedure.

    This allows the stored procedure owner to delegate the power to perform specified operations to users who otherwise could not do so. However, there are limitations on these owner’s rights stored procedures.

You might want to use a stored procedure to automate a task that requires multiple SQL statements and is performed frequently. For example, imagine that you want to clean up a database by deleting data older than a specified date. You can write multiple DELETE statements, each of which deletes data from a specific table. You can put all of those statements in a single stored procedure and pass a parameter that specifies the cut-off date. Then you can simply call the procedure to clean up the database. As your database changes, you can update the procedure to clean up additional tables; if there are multiple users who use the cleanup command, they can call one procedure, rather than remember every table name and clean up each table individually.

Stored Procedure Example

Code in the following example creates a stored procedure called myproc with a Python handler called run.

create or replace procedure myproc(from_table string, to_table string, count int)
  returns string
  language python
  runtime_version = '3.8'
  packages = ('snowflake-snowpark-python')
  handler = 'run'
as
$$
def run(session, from_table, to_table, count):
  session.table(from_table).limit(count).write.save_as_table(to_table)
  return "SUCCESS"
$$;
Copy

Code in the following example calls the stored procedure myproc.

CALL myproc('table_a', 'table_b', 5);
Copy

Supported Languages

You write a procedure’s handler – its logic – in any of several programming languages. Each language allows you to manipulate data within the constraints of the language and its runtime environment. Regardless of the handler language, you create the procedure itself in the same way using SQL, specifying your handler and handler language.

You can write a handler in any of the following languages:

Language

Developer Guide

Java (using the Snowpark API)

Schreiben von gespeicherten Prozeduren in Java

JavaScript

Schreiben von gespeicherten Prozeduren in JavaScript

Python (using the Snowpark API)

Schreiben von gespeicherten Prozeduren in Python

Scala (using the Snowpark API)

Schreiben von gespeicherten Prozeduren in Scala

Snowflake Scripting (SQL)

Schreiben von gespeicherten Prozeduren in Snowflake Scripting

Language Choice

You write a procedure’s handler – its logic – in any of several programming languages. Each language allows you to manipulate data within the constraints of the language and its runtime environment.

You might choose a particular language if:

  • You already have code in that language.

    For example, if you already have a Java method that will work as a handler, and the method’s object is in a .jar file, you could copy the .jar to a stage, specify the handler as the class and method, then specify the language as Java.

  • The language has capabilities that others don’t have.

  • The language has libraries that can help you do the processing that you need to do.

When choosing a language, consider also the following:

Language

Handler Location

Sharable

Java

In-line or staged

No 1

JavaScript

In-line

Yes

Python

In-line or staged

No 2

Scala

In-line or staged

No 2

Snowflake Scripting

In-line

No 2
1

For more information about limits on sharing Java UDFs, see Allgemeine Einschränkungen.

2(1,2,3)

For more information about limits on sharing Python UDFs, see Allgemeine Einschränkungen.

Developer Guides

Guidelines and Constraints

For tips on writing stored procedures, see Verwenden von gespeicherten Prozeduren.

You can ensure stability within the Snowflake environment by developing within Snowflake constraints. For more information, see Entwerfen von Handlern unter Berücksichtigung der Snowflake-bedingten Einschränkungen

Be sure to name procedures in a way that avoids collisions with other procedures. For more information, see Namenskonventionen für gespeicherte Prozeduren und UDFs

Security

Whether you choose to have a stored procedure run with caller’s rights or owner’s rights can impact the information it has access to and the tasks it may be allowed to perform. For more information, see Erläuterungen zu gespeicherten Prozeduren mit Aufruferrechten und Eigentümerrechten.

Stored procedures share certain security concerns with user-defined functions (UDFs). For more information, see the following:

Handler Code Deployment

When creating a procedure, you can specify its handler – which implements the procedure’s logic – as code in-line with the CREATE PROCEDURE statement or as code external to the statement, such as compiled code packaged and copied to a stage.

For more information, see Speichern von Handler-Code inline oder in einem Stagingbereich.

Data Type Mappings

For each handler language, there’s a separate set of mappings between the language’s data types and the SQL types used for arguments and return values. For more about the mappings for each language, see Zuordnung von Datentypen zwischen SQL und Handler-Sprachen.

Create and Call Procedures

You use SQL to create and call a procedure.

Sprache: Deutsch