Procedimento armazenado - Stored procedure
Um procedimento armazenado (também denominado proc , storp , sproc , StoPro , StoredProc , StoreProc , sp ou SP ) é uma sub - rotina disponível para aplicativos que acessam um sistema de gerenciamento de banco de dados relacional (RDBMS). Esses procedimentos são armazenados no dicionário de dados do banco de dados .
Os usos para procedimentos armazenados incluem validação de dados (integrado ao banco de dados) ou mecanismos de controle de acesso . Além disso, os procedimentos armazenados podem consolidar e centralizar a lógica que foi implementada originalmente nos aplicativos. Para economizar tempo e memória, o processamento extenso ou complexo que requer a execução de várias instruções SQL pode ser salvo em procedimentos armazenados, e todos os aplicativos chamam os procedimentos. Pode-se usar procedimentos armazenados aninhados executando um procedimento armazenado de dentro de outro.
Os procedimentos armazenados podem retornar conjuntos de resultados , ou seja, os resultados de uma SELECT
instrução. Esses conjuntos de resultados podem ser processados usando cursores , por outros procedimentos armazenados, associando um localizador de conjunto de resultados ou por aplicativos. Os procedimentos armazenados também podem conter variáveis declaradas para processamento de dados e cursores que permitem que ele percorra várias linhas em uma tabela. Demonstrações de fluxo de controle-procedimento armazenado tipicamente incluir IF
, WHILE
, LOOP
, REPEAT
, e CASE
declarações, e mais. Os procedimentos armazenados podem receber variáveis, retornar resultados ou modificar variáveis e retorná-las, dependendo de como e onde a variável é declarada.
Implementação
Os procedimentos armazenados são semelhantes às funções definidas pelo usuário (UDFs). A principal diferença é que os UDFs podem ser usados como qualquer outra expressão nas instruções SQL, enquanto os procedimentos armazenados devem ser chamados usando a CALL
instrução.
CALL procedure(...)
ou
EXECUTE procedure(...)
A implementação exata e correta de procedimentos armazenados varia de um sistema de banco de dados para outro. A maioria dos principais fornecedores de banco de dados oferece suporte de alguma forma. Dependendo do sistema de banco de dados, os procedimentos armazenados podem ser implementados em uma variedade de linguagens de programação , por exemplo SQL , Java , C ou C ++ . Os procedimentos armazenados escritos em linguagens não SQL podem ou não executar instruções SQL eles próprios.
A crescente adoção de stored procedures levou à introdução de elementos procedurais na linguagem SQL nos padrões SQL: 1999 e SQL: 2003 na parte SQL / PSM . Isso fez do SQL uma linguagem de programação imperativa . A maioria dos sistemas de banco de dados oferece extensões proprietárias e específicas do fornecedor, excedendo SQL / PSM. Existe uma especificação padrão para procedimentos armazenados Java , bem como SQL / JRT .
Sistema de banco de dados | Linguagem de implementação |
---|---|
CUBRID | Java |
IBM DB2 | SQL PL (próximo ao padrão SQL / PSM ) ou Java |
Firebird | PSQL (Fyracle também oferece suporte a partes do PL / SQL da Oracle) |
Informix | Java |
Interbase | Procedimento armazenado e linguagem de gatilho |
Microsoft SQL Server | Transact-SQL e várias linguagens .NET Framework |
MySQL | próprios procedimentos armazenados, aderindo estritamente ao padrão SQL / PSM |
NuoDB | SQL ou Java |
OpenLink Virtuoso | Procedimentos Virtuoso SQL (VSP); também extensível via Java, C e outras linguagens de programação |
Oráculo | PL / SQL ou Java |
PostgreSQL | PL / pgSQL , também pode usar linguagens de função próprias, como PL / Perl ou PL / PHP |
SAP HANA | SQLScript ou R |
SAP ASE | Transact-SQL |
SAP SQL Anywhere | Transact-SQL , Watcom SQL |
SQLite | Não suportado |
Comparação com SQL estático
- A sobrecarga
- Como as instruções de procedimento armazenado são armazenadas diretamente no banco de dados, elas podem remover todo ou parte da sobrecarga de compilação que normalmente é necessária em situações em que os aplicativos de software enviam consultas SQL (dinâmicas) em linha para um banco de dados. (No entanto, a maioria dos sistemas de banco de dados implementa caches de instruções e outros métodos para evitar a compilação repetitiva de instruções SQL dinâmicas.) Além disso, embora evitem algum SQL pré-compilado, as instruções aumentam a complexidade de criar um plano de execução ideal, porque nem todos os argumentos do SQL declaração são fornecidos em tempo de compilação. Dependendo da implementação e configuração específicas do banco de dados, resultados de desempenho mistos serão vistos de procedimentos armazenados versus consultas genéricas ou funções definidas pelo usuário.
- Evitando o tráfego de rede
- Uma grande vantagem dos procedimentos armazenados é que eles podem ser executados diretamente no mecanismo de banco de dados . Em um sistema de produção, isso normalmente significa que os procedimentos são executados inteiramente em um servidor de banco de dados especializado, que tem acesso direto aos dados acessados. O benefício aqui é que os custos de comunicação de rede podem ser evitados completamente. Isso se torna mais importante para séries complexas de instruções SQL.
- Encapsulando a lógica de negócios
- Os procedimentos armazenados permitem que os programadores incorporem a lógica de negócios como uma API no banco de dados, o que pode simplificar o gerenciamento de dados e reduzir a necessidade de codificar a lógica em outro lugar nos programas clientes. Isso pode resultar em uma probabilidade menor de corrupção de dados por programas clientes com defeito. O sistema de banco de dados pode garantir a integridade e consistência dos dados com a ajuda de procedimentos armazenados.
- Delegar direitos de acesso
- Em muitos sistemas, os procedimentos armazenados podem receber direitos de acesso ao banco de dados que os usuários que executam esses procedimentos não têm diretamente.
- Alguma proteção contra ataques de injeção de SQL
- Os procedimentos armazenados podem ser usados para proteger contra ataques de injeção. Os parâmetros de procedimento armazenado serão tratados como dados, mesmo se um invasor inserir comandos SQL. Além disso, alguns DBMS verificarão o tipo do parâmetro. No entanto, um procedimento armazenado que, por sua vez, gera SQL dinâmico usando a entrada ainda é vulnerável a injeções de SQL, a menos que sejam tomadas as devidas precauções.
Outros usos
Em alguns sistemas, os procedimentos armazenados podem ser usados para controlar o gerenciamento de transações; em outros, os procedimentos armazenados são executados dentro de uma transação de forma que as transações sejam efetivamente transparentes para eles. Os procedimentos armazenados também podem ser chamados a partir de um acionador de banco de dados ou de um manipulador de condição. Por exemplo, um procedimento armazenado pode ser disparado por uma inserção em uma tabela específica ou atualização de um campo específico em uma tabela, e o código dentro do procedimento armazenado seria executado. Escrever procedimentos armazenados como manipuladores de condição também permite que os administradores de banco de dados rastreiem erros no sistema com mais detalhes usando procedimentos armazenados para capturar os erros e registrar algumas informações de auditoria no banco de dados ou em um recurso externo como um arquivo.
Comparação com funções
- Uma função é um subprograma escrito para realizar certos cálculos.
- Uma função escalar retorna apenas um valor (ou NULL), enquanto uma função de tabela retorna uma tabela (relacional) composta por zero ou mais linhas, cada linha com uma ou mais colunas.
- As funções devem retornar um valor (usando a
RETURN
palavra - chave), mas para procedimentos armazenados isso não é obrigatório. - Os procedimentos armazenados podem usar
RETURN
palavras-chave, mas nenhum valor é transmitido. - Funções podem ser usadas em
SELECT
instruções, desde que não façam nenhuma manipulação de dados. No entanto, os procedimentos não podem ser incluídos nasSELECT
declarações. - Um procedimento armazenado pode retornar vários valores usando o
OUT
parâmetro ou não retornar nenhum valor. - Um procedimento armazenado economiza o tempo de compilação da consulta.
- Um procedimento armazenado é um objeto de banco de dados.
- Um procedimento armazenado é um objeto material.
Comparação com declarações preparadas
As instruções preparadas pegam uma instrução ou consulta comum e a parametrizam para que diferentes valores literais possam ser usados posteriormente. Como procedimentos armazenados, eles são armazenados no servidor para eficiência e fornecem alguma proteção contra ataques de injeção de SQL. Embora mais simples e declarativas, as instruções preparadas não são normalmente escritas para usar lógica procedural e não podem operar em variáveis. Por causa de sua interface simples e implementações do lado do cliente, as instruções preparadas são mais amplamente reutilizáveis entre DBMS.
Comparação com contratos inteligentes
Contrato inteligente é um termo aplicado ao código executável armazenado em um blockchain em oposição a um RDBMS. Apesar dos mecanismos de consenso de resultados de execução de redes públicas de blockchain diferindo em princípio de bancos de dados tradicionais privados ou federados, eles desempenham ostensivamente a mesma função que procedimentos armazenados, embora geralmente com um senso de transação de valor.
Desvantagens
- As linguagens de procedimento armazenado geralmente são específicas do fornecedor. A mudança de fornecedores de banco de dados geralmente requer a reescrita de procedimentos armazenados existentes.
- As alterações em procedimentos armazenados são mais difíceis de controlar em um sistema de controle de versão do que em outro código. As alterações devem ser reproduzidas como scripts a serem armazenados no histórico do projeto a ser incluído, e as diferenças nos procedimentos podem ser mais difíceis de mesclar e rastrear corretamente.
- Os erros em procedimentos armazenados não podem ser detectados como parte de uma compilação ou etapa de construção em um IDE de aplicativo - o mesmo é verdadeiro se um procedimento armazenado faltou ou foi acidentalmente excluído.
- Linguagens de procedimento armazenado de diferentes fornecedores têm diferentes níveis de sofisticação.
- Por exemplo, o pgpsql do Postgres tem mais recursos de linguagem (especialmente por meio de extensões) do que o T-SQL da Microsoft.
- O suporte de ferramenta para escrever e depurar procedimentos armazenados geralmente não é tão bom quanto para outras linguagens de programação, mas isso difere entre fornecedores e linguagens.
- Por exemplo, PL / SQL e T-SQL têm IDEs e depuradores dedicados. PL / PgSQL pode ser depurado de vários IDEs.