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"
$$;
Code in the following example calls the stored procedure myproc
.
CALL myproc('table_a', 'table_b', 5);
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) |
|
JavaScript |
|
Python (using the Snowpark API) |
|
Scala (using the Snowpark API) |
|
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:
Handler locations supported. Not all languages support referring to the handler on a stage (the handler code must instead by in-line). For more information, see Speichern von Handler-Code inline oder in einem Stagingbereich.
Whether the handler results in a UDF that’s sharable. A sharable UDF can be used with the Snowflake Secure Data Sharing feature.
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:
You can help a procedure’s handler code execute securely by following the best practices described in Sicherheitsverfahren für UDFs und Prozeduren
Ensure that sensitive information is concealed from users who should not have access to it. For more information, see Schutz sensibler Informationen mit sicheren UDFs und gespeicherten Prozeduren
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.
Once you have written handler code, you can create a stored procedure by executing the CREATE PROCEDURE statement, specifying the procedure’s handler. For more information, see Erstellen einer gespeicherten Prozedur.
To call a procedure, execute a SQL CALL statement that specifies the procedure. For more information, see Aufrufen einer gespeicherten Prozedur.
To create a temporary procedure that executes only once and is discarded, use WITH…CALL . For more information, see CALL (mit anonymen Prozeduren).