PL/SQL: blocos, cursores, procedures × functions, packages, triggers

A ideia que organiza o assunto

Comece pelo tamanho da evidência, porque aqui ele é pequeno e muda o que se deve estudar. Este tópico tem 16 itens no corpus — 9 Certos e 7 Errados. Dezesseis itens não produzem frequência; produzem, no máximo, uma direção. Leia esta nota sabendo disso: o que está medido está marcado como medido, e o resto é a disciplina, ensinada porque a prova pode cobrá-la amanhã sem nunca a ter cobrado ontem.

O título promete blocos, cursores, procedures × functions, packages e triggers, e a medição desmente quase tudo, menos a última palavra:

triggers: 10 dos 16 itens;
procedimentos armazenados: 5, sempre descritos por fora (vantagens, forma de acionamento), nunca por sintaxe;
cursores: 1 item;
functions: 1 item, e ainda assim uma função SQL do PostgreSQL, não uma função PL/SQL;
procedure × function como contraste: nenhum item. A distinção clássica — a função obrigatoriamente retorna valor e pode ser chamada de dentro de um comando SQL, o procedimento não — não decide nenhum item deste corpus;
packages: nenhum item;
a estrutura do bloco — DECLARE, BEGIN, EXCEPTION, END — não decide nenhum item; o único que toca a parte declarativa pergunta se CONSTANT admite NOT NULL.

Vale um segundo aviso sobre o rótulo. O tópico se chama PL/SQL, que é a linguagem procedural da Oracle, mas apenas 3 dos 16 itens chegam a escrever PL/SQL ou Oracle, e só um deles nomeia a Oracle. Os outros se espalham por PostgreSQL (2), SQL Server (1), MySQL (1) e descrições genéricas de SGBD (9). Na prática, o assunto cobrado é programação no lado do servidor, e a sintaxe específica de um produto quase nunca é o que decide.

A ideia que organiza tudo é uma pergunta de três palavras: quem aciona o código? O procedimento e a função são invocados explicitamente — alguém escreve EXECUTE, CALL ou usa a função dentro de uma expressão. O gatilho nunca é invocado: ele é disparado implicitamente pelo evento ao qual está associado, e não existe comando para executá-lo. Dessa única fronteira saem a definição de gatilho, o modelo evento-condição-ação, a impossibilidade de chamá-lo e a razão de ele ser chamado de “procedimento armazenado especial”. Quatro dos sete itens errados do corpus caem só com essa pergunta.

Por que se usa (e o que custa)

Colocar código dentro do banco resolve dois problemas de uma vez. O desempenho: o procedimento fica armazenado já analisado, com plano de execução preparado, e a aplicação envia apenas a chamada e os parâmetros em vez de todo o texto SQL, reduzindo o tráfego de rede e o trabalho de compilação a cada execução. E a integridade: uma regra escrita como gatilho vale para toda alteração da tabela, venha ela da aplicação oficial, de um relatório, de um script de carga ou do próprio DBA — coisa que uma validação escrita na camada de aplicação não garante.

Paga-se em três moedas. Portabilidade: a linguagem procedural é do produto — PL/SQL é Oracle, T-SQL é SQL Server, PL/pgSQL é PostgreSQL —, e por isso esses objetos pertencem ao modelo físico, não ao lógico. Visibilidade: o gatilho executa sem que ninguém o chame, de modo que um efeito colateral inesperado é difícil de rastrear a partir do código da aplicação. Acoplamento em cascata: gatilho que altera tabela cujo gatilho altera outra tabela produz encadeamentos que ninguém desenhou.

A formulação honesta para a prova: o código no servidor troca portabilidade e visibilidade por desempenho e por garantia de que a regra não será contornada.

Como funciona

O gatilho. É uma regra ativa no modelo evento-condição-ação (ECA). O evento é o comando que o dispara — INSERT, UPDATE, DELETE e, em alguns produtos, TRUNCATE e comandos de DDL. A condição é o predicado avaliado quando o evento ocorre. A ação é o bloco executado se a condição for verdadeira.

O tempo do disparo. BEFORE executa antes do comando, e é onde se validam ou se ajustam valores. AFTER executa depois, e é onde se registram auditorias e se propagam efeitos. INSTEAD OF executa em lugar do comando, que não chega a ser executado; o uso típico é tornar atualizável uma visão que o SGBD não atualizaria sozinho. Há ainda o nível: gatilho de linha dispara uma vez por linha afetada e enxerga NEW e OLD; gatilho de comando dispara uma vez por instrução, qualquer que seja o número de linhas.

As variáveis do gatilho. NEW é a linha nova (existe no INSERT e no UPDATE), OLD é a linha antiga (existe no UPDATE e no DELETE). No PostgreSQL, uma função de gatilho em PL/pgSQL recebe ainda variáveis especiais, entre as quais TG_OP, que informa qual operação disparou o gatilho — INSERT, UPDATE, DELETE ou TRUNCATE —, permitindo que um único gatilho trate as três de forma distinta.

O procedimento armazenado. Bloco nomeado, armazenado e pré-compilado no banco, executado por chamada explícita. Aceita parâmetros de entrada, de saída e de entrada e saída, e não precisa retornar valor. No SQL Server, os procedimentos de sistema, cujos nomes começam por sp_, residem no banco de dados padrão e podem ser executados a partir de qualquer banco, para tarefas administrativas.

A função. Difere do procedimento em um ponto que a prova até hoje não cobrou neste corpus, mas que é a distinção clássica: a função obrigatoriamente retorna um valor e, por isso, pode ser usada dentro de uma expressão SQL — no SELECT, no WHERE. No PostgreSQL, a função declara ainda a sua volatilidade: IMMUTABLE garante que ela não modifica o banco e devolve sempre o mesmo resultado para os mesmos argumentos, o que autoriza o otimizador a avaliá-la previamente; STABLE pode consultar o banco, mas não alterá-lo; VOLATILE pode tudo, e é o padrão.

O bloco PL/SQL. Estrutura em três partes: DECLARE (opcional, declara variáveis, constantes e cursores), BEGIN (obrigatório, executa) e EXCEPTION (opcional, trata erros), fechado por END. Na declaração, CONSTANT impede a reatribuição posterior e admite, na mesma linha, a restrição NOT NULL; ambas exigem inicialização na própria declaração.

O cursor. É a área de trabalho que dá acesso ao conjunto de linhas retornado por um SELECT, mantendo um ponteiro para a linha corrente. O ciclo é fixo: DECLARE define a consulta, OPEN executa e posiciona o ponteiro antes da primeira linha, FETCH traz a linha corrente para as variáveis e avança, e CLOSE libera a área. O cursor implícito é aberto pelo próprio SGBD a cada comando SQL de um bloco; o explícito é o declarado pelo programador quando é preciso percorrer o resultado linha a linha.

O pacote (package). Agrupa procedimentos, funções, cursores, tipos e variáveis em uma unidade, dividida em especificação (a interface pública) e corpo (a implementação). Permite encapsular, sobrecarregar nomes e manter estado entre chamadas de uma mesma sessão.

O que decide os itens

Quem aciona — o eixo que decide mais itens do que todos os outros somados:

	gatilho (trigger)	procedimento / função
acionamento	implícito, pelo evento	explícito, por quem chama
pode ser invocado?	não	sim
o que o dispara	`INSERT`, `UPDATE`, `DELETE` na tabela	`EXECUTE`, `CALL`, uso em expressão
associado a	uma tabela ou visão	nenhum objeto em particular
parâmetros	não recebe	recebe
como se impede a execução	desabilitando o gatilho	não chamando

O gatilho é espécie, não gênero: dizer que o gatilho é um procedimento armazenado especial é Certo; dizer que o procedimento armazenado é um gatilho é Errado. A relação só vale em um sentido.

O tempo do disparo:

tempo	executa	uso típico
`BEFORE`	antes do comando	validar ou ajustar valores
`AFTER`	depois do comando	auditoria, propagação de efeito
`INSTEAD OF`	em lugar do comando, que não ocorre	tornar visão atualizável

As siglas e as variáveis que caem:

símbolo	significa
ECA	evento-condição-ação — nunca extração, carga ou qualquer termo de ETL
`NEW`	a linha nova (`INSERT`, `UPDATE`)
`OLD`	a linha antiga (`UPDATE`, `DELETE`)
`TG_OP`	no PostgreSQL, a operação que disparou o gatilho

Cursor × FETCH — a confusão que o único item de cursor cobrou:

	o cursor	o `FETCH`
é	a área com o conjunto de linhas e o ponteiro	a operação que traz a linha corrente
quantas linhas	todas as do resultado	uma por vez

Modelo lógico × modelo físico:

lógico	físico
entidades, atributos, relacionamentos, chave primária e estrangeira	gatilhos, procedimentos, funções, índices, tablespaces, particionamento
sobrevive à troca de SGBD	morre com ela

Volatilidade de função no PostgreSQL:

categoria	pode consultar o banco	pode alterar o banco
`IMMUTABLE`	não	não
`STABLE`	sim	não
`VOLATILE`	sim	sim

Como a CEBRASPE derruba você aqui

Medido sobre os 16 itens do tópico: 9 Certo e 7 Errado. Sete itens errados não desenham distribuição nenhuma — não há percentual honesto a citar aqui —, mas a forma deles é uniforme o bastante para ser decorada, e a medição autoriza uma afirmação forte: nenhum item deste corpus se decide por sintaxe escrita. Não há bloco para completar, não há CREATE TRIGGER para corrigir, não há %ROWTYPE nem EXCEPTION WHEN cobrados de memória. O erro está sempre em uma afirmação sobre comportamento — quem aciona o código, que nome tem o comportamento descrito, ou o que o produto permite fazer. Os sete errados caem em três famílias, e duas delas se defendem com a mesma pergunta.

Negar uma capacidade que existe — 3 dos 7. É a família mais numerosa e a mais fácil de reconhecer, porque a frase vem em forma de limitação. “a declaração CONSTANT não pode impor a restrição NOT NULL” — pode, e a sintaxe da Oracle prevê as duas cláusulas juntas. “ele não possui suporte para triggers”, dito do MySQL, que os suporta desde a versão 5.0. E a inversão de acionamento: “é possível invocar explicitamente um trigger” — aqui a banca fez o movimento contrário, afirmando uma capacidade que não existe. A defesa é a mesma nos dois sentidos: pergunte se o recurso negado é básico do produto, ou se a capacidade afirmada contraria a natureza do objeto. Afirmação de que um SGBD consolidado não tem recurso elementar costuma estar errada ou desatualizada.

Nomear um recurso e descrever o vizinho — 3 dos 7. “o recurso de programação do tipo procedimento” que rodaria “de forma automática e implícita sempre que ocorre um evento” — a descrição é impecável e pertence ao gatilho. “o modelo extração-carga-ação (ECA), em que a extração identifica o momento de ativação do trigger” — a sigla é mantida e a expansão é importada do vocabulário de ETL, que é assunto de outro tópico do mesmo edital. “uma única linha do comando de SELECT”, dito do cursor, descreve o que o FETCH devolve, não o que o cursor guarda. A defesa é ler de trás para diante: identifique o que está descrito e só depois confira o nome que o item lhe deu.

Inventar a finalidade — 1 dos 7. O bloqueio que existiria “para contornar o conflito de consulta simultânea de tabelas por um usuário do aplicativo desenvolvido”. Concorrência supõe mais de uma transação, e leitura com leitura não conflita. É também o único item errado do tópico que não trata de programação no servidor: bloqueio entrou aqui pelo enunciado, que juntava gatilhos, procedimentos armazenados e gerência de bloqueios no mesmo bloco. Quando o item declarar a finalidade de um mecanismo, confira quantos agentes a situação descrita envolve e que tipo de operação está em disputa.

O que os 9 Certos ensinam. Todos são descrições sóbrias de capacidade, sem palavra de exclusão: o gatilho é ECA; o gatilho pode disparar antes, depois ou em substituição ao comando; o procedimento reduz tráfego de rede e melhora o desempenho; TG_OP identifica a operação; IMMUTABLE impede a modificação do banco. Nenhum item Certo deste corpus nega uma função. O único Certo que contém uma negação nega uma restrição declarada pela própria documentação — a função IMMUTABLE, que por definição não pode alterar o banco —, e não uma capacidade do produto. Com nove casos isso é direção, não regra — mas é consistente com o restante do corpus de TI: é a frase que restringe que costuma carregar o erro, e não a frase que enumera.

Erros clássicos

Achar que um gatilho pode ser chamado. Não pode. Para executá-lo, provoca-se o evento; para impedi-lo, desabilita-se o gatilho. Não existe comando que o invoque diretamente.

Achar que um procedimento dispara sozinho. É o mesmo erro pelo outro lado. O procedimento só roda quando alguém o chama — aplicação, outro bloco ou agendamento.

Expandir ECA errado. Evento-condição-ação. A banca mantém a sigla e troca a expansão por termos de ETL, que pertencem a outro assunto.

Trocar NEW por OLD. NEW é a linha que está entrando; OLD, a que está saindo. No INSERT não existe OLD; no DELETE não existe NEW.

Reduzir o cursor a uma linha. O cursor é o conjunto e o ponteiro; quem traz uma linha por vez é o FETCH. Confundir os dois é confundir a estrutura com a operação que a percorre.

Esquecer o INSTEAD OF. Item que afirme que o gatilho sempre executa depois do comando, ou que jamais pode substituí-lo, ignora um dos três tempos de disparo e está errado.

Pôr gatilho e procedimento no modelo lógico. Eles dependem da linguagem procedural do produto e, por isso, pertencem ao modelo físico. Relacionamentos e chaves é que são lógicos.

Supor que a função pode tudo o que o procedimento pode. A função obrigatoriamente retorna valor e, em compensação, pode ser usada dentro de uma expressão SQL. Esta distinção não decidiu nenhum item medido aqui, mas é a primeira que a banca cobra quando resolve sair dos gatilhos.

Tomar a restrição declarada pelo próprio produto por uma limitação inventada. Que uma função IMMUTABLE não possa modificar o banco é a definição da categoria, e o item que afirma isso é Certo. Que um SGBD inteiro não suporte gatilhos é limitação inventada, e o item é Errado. A pergunta é sempre de quem é a restrição.