Escolha se deseja escrever um procedimento armazenado ou uma função definida pelo usuário

Este tópico descreve as principais diferenças entre os procedimentos armazenados e UDFs, incluindo diferenças em como cada um deles pode ser invocado e em qual eles podem.

Em um nível alto, os procedimentos armazenados e UDFs diferem na forma como são normalmente utilizados, como descrito abaixo.

Finalidade do procedimento armazenado

Finalidade da função definida pelo usuário

Geralmente para realizar operações administrativas, executando instruções SQL. O corpo de um procedimento armazenado é permitido, mas não exigido, para retornar explicitamente um valor (como um indicador de erro).

Calcule e retorne um valor. Uma função sempre retorna um valor explicitamente ao especificar uma expressão. Por exemplo, o corpo de uma JavaScript UDF deve ter uma instrução return que retorne um valor.

Neste tópico:

Quando criar um procedimento armazenado ou uma UDF

Em geral, ao decidir se deve ser criado um procedimento armazenado ou um UDF, considere as seguintes recomendações:

Crie um procedimento armazenado quando…

Crie um UDF quando…

  • Você está migrando um procedimento armazenado existente de outro aplicativo/sistema.

  • Você precisar realizar operações de banco de dados:

    • Consultas típicas e DML, como SELECT, UPDATE etc.

    • Tarefas administrativas, incluindo DDL, como excluir tabelas temporárias, apagar dados mais antigos que N dias ou adicionar usuários.

  • Se você estiver migrando um UDF existente de outro aplicativo/sistema.

  • Você precisa de uma função que possa ser chamada como parte de uma instrução SQL e que deve retornar um valor que será usado na instrução.

  • Sua saída precisa incluir um valor para cada linha de entrada ou para cada grupo. Por exemplo:

    select MyFunction(col1) from table1;
    
    select MyAggregateFunction(col1) from table1 group by col2;
    
    Copy

Linguagens de manipulador suportadas

Quando você escreve um procedimento ou UDF, você escreve sua lógica como um manipulador em uma das linguagens suportadas. A tabela a seguir lista as linguagens suportadas.

Procedimentos armazenados

Funções definidas pelo usuário

Java

Java

JavaScript

JavaScript

Python

Python

Scala

Script Snowflake

SQL

Diferenças de uso e comportamento

As seções seguintes descrevem as diferenças específicas nos comportamentos suportados por procedimentos e UDFs.

UDFs retornam um valor; Procedimentos armazenados não precisam

Procedimentos armazenados e funções podem ambos retornar um valor. No entanto:

  • O corpo de um procedimento armazenado é permitido, mas não exigido, para retornar explicitamente um valor (como um indicador de erro). Isso porque a finalidade de um procedimento armazenado geralmente é realizar operações administrativas executando instruções SQL.

    Note que toda instrução CREATE PROCEDURE deve incluir uma cláusula RETURNS que especifica um tipo de retorno, mesmo que o procedimento não retorne nada explicitamente. Se um procedimento não retorna explicitamente um valor, ele retorna implicitamente NULL.

    O código no exemplo a seguir declara um tipo de retorno para o procedimento com uma cláusula RETURNS, mas um valor só é retornado no caso de um erro. Em outras palavras, nem todo caminho de código retorna um valor.

    create or replace procedure do_stuff(input number)
    returns varchar
    language sql
    as
    $$
    declare
      error varchar default 'Bad input. Number must be less than 10.';
    
    begin
      if (input > 10) then
        return error;
      end if;
    
      -- Perform an operation that doesn't return a value.
    
    end;
    $$
    ;
    
    Copy

    Se um procedimento não retorna explicitamente um valor, ele retorna implicitamente NULL.

  • O UDF sempre retorna um valor explicitamente, especificando uma expressão. Isto porque o objetivo de um UDF é calcular e retornar um valor. Por exemplo, o corpo de uma JavaScript UDF deve ter uma instrução return que retorne um valor.

Valores de retorno de UDF podem ser usados diretamente em SQL; os valores de retorno do procedimento armazenado podem não ser

O valor retornado por um procedimento armazenado, ao contrário do valor retornado por uma função, não pode ser usado diretamente em SQL.

Embora um procedimento armazenado possa retornar um valor, a sintaxe do comando CALL não fornece (para a maioria das linguagens do manipulador) um lugar para armazenar o valor retornado ou uma maneira de operar sobre ele ou passar o valor para outra operação. Em SQL, não há como construir uma instrução como esta:

y = stored_procedure1(x);                         -- Not allowed.
Copy

Há maneiras indiretas de utilizar o valor de retorno de um procedimento armazenado:

  • Você pode chamar o procedimento armazenado dentro de outro procedimento armazenado. Por exemplo, quando o manipulador do procedimento armazenado estiver escrito em JavaScript, o JavaScript no procedimento armazenado externo pode recuperar e armazenar a saída do procedimento armazenado interno. Lembre-se, entretanto, que o procedimento armazenado externo (e cada procedimento armazenado interno) ainda é incapaz de devolver mais de um valor a seu chamador.

  • Você pode chamar o procedimento armazenado e depois chamar a função RESULT_SCAN e passar a ela o ID da instrução gerada para o procedimento armazenado.

  • Você pode armazenar um resultado definido em uma tabela temporária ou tabela permanente, e usar essa tabela após retornar da chamada de procedimento armazenado.

  • Se o volume de dados não for muito grande, você pode armazenar várias linhas e várias colunas em um VARIANT (por exemplo, como um valor JSON) e retornar esse VARIANT.

As UDFs podem ser chamadas no contexto de outra instrução; os procedimentos armazenados são chamados de forma independente

Um procedimento armazenado não avalia para um valor e não pode ser usado em todos os contextos nos quais uma expressão geral pode ser usada. Por exemplo, não é possível executar SELECT my_stored_procedure()....

Um UDF avalia para um valor e pode ser usado em contextos nos quais uma expressão geral pode ser usada (por exemplo, SELECT my_function() ...).

Um procedimento armazenado é chamado como uma instrução independente. O código abaixo ilustra a diferença entre chamar um procedimento armazenado e chamar uma função:

CALL MyStoredProcedure_1(argument_1);

SELECT MyFunction_1(column_1) FROM table1;
Copy

Para obter mais detalhes sobre a chamada de funções e procedimentos, consulte o seguinte:

Várias UDFs podem ser chamadas dentro de uma única instrução; um único procedimento armazenado é chamado com uma instrução

Uma única instrução executável pode chamar apenas um procedimento armazenado. Em contraste, uma única instrução SQL pode chamar várias funções.

Da mesma forma, um procedimento armazenado, ao contrário de uma função, não pode ser chamado como parte de uma expressão.

Entretanto, dentro de um procedimento armazenado, o procedimento armazenado pode chamar outro procedimento armazenado ou chamar a si mesmo recursivamente. Um exemplo disso é mostrado na seção de exemplos de código Exemplos.

Para obter mais detalhes sobre a chamada de funções e procedimentos, consulte o seguinte:

UDFs podem não acessar o banco de dados; os procedimentos armazenados podem

Dentro de um procedimento armazenado, você pode executar operações de banco de dados, como SELECT, UPDATE e CREATE:

  • Por exemplo, em um procedimento armazenado JavaScript, você pode usar o API JavaScript para realizar estas operações.

    O exemplo abaixo mostra como um procedimento armazenado pode criar e executar uma instrução SQL que chama outro procedimento armazenado. O $$ indica o início e o fim do código JavaScript do manipulador no procedimento armazenado.

    create procedure ...
      $$
      // Create a Statement object that can call a stored procedure named
      // MY_PROCEDURE().
      var stmt1 = snowflake.createStatement( { sqlText: "call MY_PROCEDURE(22)" } );
      // Execute the SQL command; in other words, call MY_PROCEDURE(22).
      stmt1.execute();
      // Create a Statement object that executes a SQL command that includes
      // a call to a UDF.
      var stmt2 = snowflake.createStatement( { sqlText: "select MY_UDF(column1) from table1" } );
      // Execute the SQL statement and store the output (the "result set") in
      // a variable named "rs", which we can access later.
      var rs = stmt2.execute();
      // etc.
      $$;
    
    Copy
  • Em um procedimento armazenado do Script Snowflake, você pode executar instruções SQL.

    O exemplo abaixo mostra como um procedimento armazenado pode criar e executar uma instrução SQL que chama outro procedimento armazenado. O $$ indica o início e o fim do código Script Snowflake no procedimento armazenado.

    CREATE PROCEDURE ...
      -- Call a stored procedure named my_procedure().
      CALL my_procedure(22);
      -- Execute a SQL statement that includes a call to a UDF.
      SELECT my_udf(column1) FROM table1;
    
    Copy

Ao contrário dos procedimentos armazenados, UDFs não têm acesso a uma API que pode realizar operações de banco de dados.