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

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.

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;

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

• Chamada de um procedimento armazenado

• Como chamar uma UDF

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:

• Chamada de um procedimento armazenado

• Como chamar uma UDF

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.