Como trabalhar com procedimentos armazenados¶

Os procedimentos armazenados permitem aos usuários criar códigos modulares que podem incluir lógica comercial complexa, combinando múltiplas instruções SQL com lógica processual.

Neste tópico:

Nota

Para criar e chamar um procedimento anônimo, use CALL (com procedimento anônimo). Criar e chamar um procedimento anônimo não exige uma função com privilégios de esquema CREATE PROCEDURE.

Convenções de nomeação para procedimentos armazenados¶

Você deve nomear os procedimentos de acordo com as convenções aplicadas pelo Snowflake.

Para obter mais informações, consulte Como nomear e sobrecarregar procedimentos e UDFs.

Gerenciamento de transações¶

Os procedimentos armazenados não são atômicos; se uma instrução em um procedimento armazenado falhar, as outras instruções no procedimento armazenado não são necessariamente revertidas.

Você pode usar procedimentos armazenados com transações para fazer um grupo de instruções atômicas. Para obter mais detalhes, consulte Transações e procedimentos armazenados.

Dicas gerais¶

Código simétrico¶

Se você está familiarizado com programação em linguagem assembly, você pode achar útil a seguinte analogia. Em linguagem assembly, as funções frequentemente criam e desfazem seus ambientes de forma simétrica. Por exemplo:

-- Set up.
push a;
push b;
...
-- Clean up in the reverse order that you set up.
pop b;
pop a;

Copy

Você pode usar esta abordagem em seus procedimentos armazenados:

Se um procedimento armazenado fizer alterações temporárias em sua sessão, então esse procedimento deve desfazer essas alterações antes de retornar.
Se um procedimento armazenado utiliza o tratamento de exceções ou ramificações, ou outra lógica que possa impactar quais instruções são executadas, você precisa limpar o que tiver criado, independentemente de quais ramificações você use durante uma determinada invocação.

Por exemplo, seu código pode ser semelhante ao pseudocódigo mostrado abaixo:

create procedure f() ...
    $$
    set x;
    set y;
    try  {
       set z;
       -- Do something interesting...
       ...
       unset z;
       }
    catch  {
       -- Give error message...
       ...
       unset z;
       }
    unset y;
    unset x;
    $$
    ;

Copy

Chamada de um procedimento armazenado¶

Você chama um procedimento armazenado usando um comando SQL. Para obter mais informações sobre como chamar os procedimentos armazenados, consulte Chamada de um procedimento armazenado.

Privilégios¶

Os procedimentos armazenados utilizam dois tipos de privilégios:

Privilégios diretamente relacionados ao próprio procedimento armazenado.
Privilégios relacionados aos objetos de banco de dados (por exemplo, tabelas) que o procedimento armazenado acessa.

Privilégios relacionados a procedimentos armazenados¶

Similar a outros objetos de banco de dados (tabelas, exibições, UDFs etc.), os procedimentos armazenados são propriedade de uma função e têm um ou mais privilégios que podem ser concedidos a outras funções.

Atualmente, os seguintes privilégios se aplicam aos procedimentos armazenados:

USAGE
OWNERSHIP

Para que uma função utilize um procedimento armazenado, a função deve ser a proprietária ou ter recebido privilégio USAGE em relação ao procedimento armazenado.

Privilégios em relação aos objetos de banco de dados acessados pelo procedimento armazenado¶

Este assunto é tratado em Understanding Caller’s Rights and Owner’s Rights Stored Procedures.

Considerações sobre procedimentos armazenados¶

Embora os procedimentos armazenados permitam aninhamento e recursão, a profundidade máxima atual da pilha de chamadas aninhadas para procedimentos armazenados definidos pelo usuário é 5 (incluindo o procedimento armazenado de nível superior), e pode ser menor se os procedimentos armazenados individuais na cadeia de chamadas consumirem grandes quantidades de recursos.
Em casos raros, chamar muitos procedimentos armazenados ao mesmo tempo pode causar um deadlock.

Injeção de SQL¶

Os procedimentos armazenados podem criar dinamicamente uma instrução SQL e executá-la. Entretanto, isso pode permitir ataques de injeção de SQL, principalmente se você criar a instrução SQL usando a entrada de uma fonte pública ou não confiável.

Você pode minimizar o risco de ataques de injeção de SQL por meio da vinculação de parâmetros, em vez da concatenação de texto. Para um exemplo de variáveis vinculantes, consulte Variáveis de vinculação.

Se você optar por utilizar a concatenação, você deve verificar cuidadosamente as entradas ao construir o SQL dinamicamente usando entradas de fontes públicas. Você também pode tomar outras precauções, como consultar usando uma função que tenha privilégios limitados (por exemplo, acesso somente leitura ou acesso somente a certas tabelas ou exibições).

Para obter mais informações sobre ataques de injeção de SQL, consulte injeção de SQL (na Wikipédia).

Dicas de concepção para procedimentos armazenados¶

Aqui estão algumas dicas para criar um procedimento armazenado:

De que recursos (por exemplo, tabelas) este procedimento armazenado precisa?
Que privilégios são necessários?

Pense em quais objetos de banco de dados serão acessados, quais funções executarão seu procedimento armazenado e de quais privilégios essas funções precisarão?

Se o procedimento tiver que ser um procedimento armazenado de direitos do chamador, você pode criar uma função para executar esse procedimento específico ou qualquer um de um grupo de procedimentos relacionados. Você pode conceder os privilégios necessários a essa função, e então conceder essa função aos usuários apropriados.
O procedimento armazenado deve ser executado com os direitos do chamador ou com os direitos do proprietário? Para obter mais informações sobre este tópico, consulte Understanding Caller’s Rights and Owner’s Rights Stored Procedures.
Como o procedimento deve tratar os erros, por exemplo, o que o procedimento deve fazer se uma tabela exigida estiver faltando ou se um argumento for inválido?
O procedimento armazenado deve registrar suas atividades ou erros, por exemplo, gravando-os em uma tabela de registro?
Consulte também a discussão sobre quando usar um procedimento armazenado vs. quando usar uma UDF: Escolha se deseja escrever um procedimento armazenado ou uma função definida pelo usuário.

Documentação de procedimentos armazenados¶

Os procedimentos armazenados são geralmente escritos para serem reutilizados e muitas vezes para serem compartilhados. A documentação dos procedimentos armazenados pode tornar os procedimentos armazenados mais fáceis de usar e de manter.

Abaixo estão algumas recomendações gerais para documentar os procedimentos armazenados.

Normalmente, há pelo menos dois grupos que querem saber sobre um procedimento armazenado:

Usuários/chamadores.
Programadores/autores.

Para usuários (e programadores), documente cada um dos seguintes itens:

O nome do procedimento armazenado.

O “local” do procedimento armazenado (banco de dados e esquema).

A finalidade do procedimento armazenado.

O nome, tipo de dados e o significado de cada parâmetro de entrada.

O nome, tipo de dados e o significado do valor de retorno. Se o valor de retorno for um tipo complexo, como um VARIANT que contém subcampos, documente esses subcampos.

Se o procedimento armazenado se baseia em informações de seu ambiente, por exemplo, variáveis de sessão ou parâmetros de sessão, documente os nomes, finalidades e valores válidos dos mesmos.

Erros retornados, exceções lançadas etc.

Funções ou privilégios necessários para a execução do procedimento. (Para obter mais informações sobre este tópico, consulte a discussão sobre funções em Dicas de concepção para procedimentos armazenados).

Se o procedimento é um procedimento de direitos do chamador ou um procedimento de direitos do proprietário.

Qualquer pré-requisito, por exemplo, tabelas que devem existir antes que o procedimento seja chamado.

Quaisquer saídas (além do valor de retorno), por exemplo, novas tabelas que são criadas.

Quaisquer “efeitos colaterais”, por exemplo, alterações nos privilégios, exclusões de dados antigos etc. A maioria dos procedimentos armazenados (ao contrário das funções) são chamados especificamente por seus efeitos colaterais, não para seus valores de retorno, portanto, certifique-se de documentar esses efeitos.

Se for necessária uma limpeza após a execução do procedimento armazenado, documente essa limpeza.

Se o procedimento pode ser chamado como parte de uma transação com múltiplas instruções (com AUTOCOMMIT=FALSE), ou se deve ser executado fora de uma transação (com AUTOCOMMIT=TRUE).

Um exemplo de uma chamada e um exemplo do que é retornado.

Limitações (se aplicável). Por exemplo, suponha que o procedimento leia uma tabela e retorne um VARIANT que contenha informações de cada linha da tabela. É possível que o VARIANT fique maior que o tamanho máximo legal de um VARIANT, então você pode precisar dar ao chamador alguma ideia sobre o número máximo de linhas na tabela que o procedimento acessa.

Alertas (se aplicável).

Dicas de solução de problemas.

Para programadores:

O(s) autor(es).

Explique porque o procedimento foi criado como um procedimento de direitos do chamador ou um procedimento de direitos do proprietário. O motivo pode não ser óbvio.

Os procedimentos armazenados podem ser aninhados, mas há um limite para a profundidade do aninhamento. Se seu procedimento armazenado chama outros procedimentos armazenados, e é provável que ele mesmo seja chamado por outros procedimentos armazenados, então você pode especificar a profundidade máxima conhecida da pilha de chamadas do seu procedimento armazenado para que os chamadores possam entender se, ao chamar seu procedimento armazenado, pode-se exceder a profundidade máxima da pilha de chamadas.

Dicas de depuração.

O local e o formato dessas informações são de sua responsabilidade. Você pode armazenar as informações no formato HTML em um site interno na internet, por exemplo. Antes de decidir onde armazená-las, pense onde sua organização armazena informações semelhantes para outros produtos, ou informações semelhantes para outros recursos do Snowflake, como exibições, funções definidas pelo usuário etc.

Outras dicas:

Inclua comentários no código-fonte, o que você deve fazer para quase qualquer parte de código-fonte.
- Lembre-se de que aplicar a engenharia reversa para entender o código é difícil. Descreva não apenas como seu algoritmo funciona, mas também a finalidade desse algoritmo.
Os procedimentos armazenados permitem um COMMENT opcional que pode ser especificado com a instrução CREATE PROCEDURE ou ALTER PROCEDURE. Outras pessoas podem ler esse comentário executando o comando SHOW PROCEDURES.
Se for prático, considere manter uma cópia mestra do comando CREATE PROCEDURE de cada procedimento armazenado em um sistema de controle de código-fonte. O recurso Time Travel do Snowflake não se aplica a procedimentos armazenados, portanto, a pesquisa de versões antigas de procedimentos armazenados deve ser feita fora do Snowflake. Se um sistema de controle de código-fonte não estiver disponível, você pode simular um parcialmente com o armazenamento dos comandos CREATE PROCEDURE em um campo VARCHAR em uma tabela e a adição de cada nova versão (sem substituir as versões mais antigas).
Considere o uso de uma convenção de nomenclatura para ajudar a fornecer informações sobre os procedimentos armazenados. Por exemplo, um prefixo ou sufixo no nome pode indicar se o procedimento é um procedimento armazenado de direitos do chamador ou um procedimento armazenado de direitos do proprietário. (Por exemplo, você pode usar cr_ como um prefixo para Direitos do Chamador).
Para ver os tipos de dados e a ordem dos argumentos de entrada, assim como o comentário, você pode usar o comando SHOW PROCEDURES. Lembre-se, entretanto, de que isso mostra apenas os nomes e tipos de dados dos argumentos; não explica os argumentos.
Se você tiver privilégios apropriados, você pode usar o comando DESCRIBE PROCEDURE para ver:
- Os nomes e tipos de dados dos argumentos.
- O corpo do procedimento e se o procedimento executa como proprietário ou chamador.
- O tipo de dados do valor de retorno.
- Outras informações úteis.