Aula 4 - Stored Procedures e Functions

Módulo 6 · Engenharia de Software · ES06 · Aula 4 de 10

Tópicos de BD
Stored Procedures e Functions

Mover lógica para dentro do banco · T-SQL ao vivo · atividade prática em sala

⚙️ Procedures

📐 Functions (escalar & tabular)

🔄 Transações

📊 Plano de execução

🚀 CI/CD de migrações

DAILY · 15 MIN

15:00

⚙️ 1 query da aula 3 que dói repetir

🔁 1 lógica que vive duplicada nos serviços

❓ 1 dúvida sobre quando "descer" lógica para o BD

🤝 Preciso de ajuda?

Cada aluno em 1 minuto · começamos em ordem alfabética

Autoestudos · Pré-aula 📚

3 tarefas individuais para chegar pronto + leituras sugeridas

📖

Tarefa 1 · Leitura

Material da Aula 4

Leitura completa do material. Saia com o vocabulário do dia: procedure, function escalar, function tabular, plano de execução, parameter sniffing.

📖 Material da aula

🔍

Tarefa 2 · Caça a candidatos

3 queries candidatas a virar procedure

Olhe o backend do projeto e identifique 3 queries que se repetem em mais de um endpoint — essas são candidatas naturais a virar stored procedure.

📓 Caderno de bordo

📚

Tarefa 3 · Pesquisa

Docs Microsoft · CREATE PROCEDURE

Leia a página oficial sobre stored procedures no SQL Server. Anote 1 parâmetro de saída (OUTPUT) e 1 boa prática que fizer sentido para o projeto.

Abrir referência ↗

📚 Leituras sugeridas

CREATE PROCEDURE (Transact-SQL) — Microsoft Docs
CREATE FUNCTION (Transact-SQL) — Microsoft Docs
PL/pgSQL — Procedural Language — PostgreSQL Docs
Use The Index, Luke! — Markus Winand

📌 Material de chegada: caderno de bordo com 3 queries candidatas + 1 dúvida concreta sobre "descer" lógica para o BD. Esses itens alimentam o daily de abertura.

Agenda da Aula

2 horas · teoria + live coding (40 min) seguidos de atividade prática em sala (1h05)

⏱️ 40 min

TEORIA + LIVE CODING

🛠️ Procedures, Functions e Plano de Execução

Por que descer lógica para o BD, anatomia de uma procedure, function escalar vs tabular, como ler um plano de execução e versionar tudo via migrações.

Live coding em T-SQL com saída no estilo notebook
Comparativo procedure × function
Pegadinhas: parameter sniffing e RECOMPILE

⏱️ 65 min

PRÁTICA EM GRUPO · NÃO PONDERADA

🧪 Atividade — `db/procedures.sql`

Cada grupo cria 2 procedures + 2 functions no repositório do projeto, com README curto e exemplos de chamada. Entrega via Merge Request até o fim do dia.

Top-N de recomendações (procedure)
Registrar interação em transação (procedure)
Média de ratings (function escalar)
Itens similares (function tabular)

🎯 Saída do dia: Merge Request feat(db): procedures e functions aberto no repositório do grupo (não é individual). Atividade não ponderada — mas aula 5 (Transactions e Triggers) parte daqui.

Por que mover lógica para o BD? 🎯

Quatro motivos a favor · dois cenários onde NÃO compensa

⚡ 1 round-trip vs N round-trips

App envia 1 chamada (EXEC sp_xxx) em vez de N queries. Em redes com 10ms de latência, cada round-trip evitado é ganho real. Procedures complexas viram uma única ida.

🛡️ Atomicidade e contrato

A procedure expõe um contrato claro (parâmetros + saída) e encapsula a transação. O app não precisa saber quais tabelas são tocadas — só conhece o ponto de entrada.

🔐 Segurança por encapsulamento

Você revoga SELECT/INSERT direto nas tabelas e dá permissão só para EXECUTE da procedure. SQL injection fica radicalmente mais difícil — o app nunca monta SQL livre.

♻️ Reuso entre serviços

Web, mobile, job batch e relatório consomem a mesma procedure. Mudou a regra de negócio do BD? Um lugar só — não 4 cópias da query em 4 stacks.

⚠️ Quando NÃO descer lógica para o BD

Lógica de negócio que muda toda sprint — versionar SQL é mais lento que versionar TypeScript. Deixe no app.
Regra que precisa de chamadas externas — se a regra depende de uma API, processamento heavy ou ML, BD não é o lugar.
Time sem skill em SQL avançado — debug de procedure complexa requer ferramental diferente. Avalie.

Stored Procedure · Anatomia 🧬

Parâmetros de entrada · default · corpo · uma única ida ao banco

SQL [1]

CREATE PROCEDURE sp_top_recomendacoes_usuario -- cria a procedure como objeto persistente no banco @userId INT, -- parametro de entrada obrigatorio: id do usuario @top INT = 10 -- parametro com valor default 10 (omissivel na chamada) AS -- separa a assinatura do corpo da procedure BEGIN -- inicia o bloco de comandos do corpo SET NOCOUNT ON; -- suprime mensagens "X rows affected" no protocolo SELECT TOP (@top) i.id, i.nome, r.score -- traz somente as @top primeiras linhas do result set FROM recomendacao r -- fonte: tabela de recomendacoes (alias r) JOIN item i ON i.id = r.item_id -- junta com o catalogo para trazer o nome do item WHERE r.user_id = @userId -- filtra apenas as recomendacoes daquele usuario ORDER BY r.score DESC; -- ordena do maior para o menor score (top-N por relevancia) END -- encerra o bloco do corpo da procedure

▶ Executando

Out [1]

Commands completed successfully.

EXEC sp_top_recomendacoes_usuario @userId = 42, @top = 3;

id	nome	score
17	Cirque du Soleil — OVO	0.92
03	Bossa Acústica	0.87
21	Hamlet · Cia Teatro	0.81

📌 Lê assim: a procedure recebe @userId e devolve um result set. O app faz 1 EXEC e recebe a lista pronta. Sem montar SQL no backend, sem N round-trips.

🎯 Por que essa procedure importa?

⚡ 1 EXEC vs N round-trips. O cliente faz UMA única chamada de rede; o banco devolve a lista pronta, sem montagem de SQL no back-end.

🛡️ Sem SQL injection. Parâmetros tipados (@userId INT) são escapados pelo driver — nada de concatenação de strings.

♻️ Reuso real. App mobile, painel BI e jobs noturnos invocam a MESMA regra de negócio — uma única fonte de verdade.

🚀 Plano em cache. O SQL Server reaproveita o plano de execução nas chamadas seguintes — latência menor em alta concorrência.

🔒 Encapsulamento. A política de "top recomendações" mora no banco; trocar a regra não exige redeploy do back-end.

Procedure · OUTPUT + Transação 🔄

Registra interação · atualiza contador · devolve o ID gerado · tudo atômico

SQL [2]

CREATE PROCEDURE sp_marcar_assistido -- procedure transacional @userId INT, -- in: usuario @espetaculoId INT, -- in: espetaculo @interacaoId INT OUTPUT -- out: ID gerado AS BEGIN SET NOCOUNT ON; -- protocolo limpo BEGIN TRY -- captura excecao BEGIN TRANSACTION; -- abre transacao INSERT INTO interacao (user_id, espetaculo_id, tipo, data) VALUES (@userId, @espetaculoId, 'ASSISTIU', GETDATE()); SET @interacaoId = SCOPE_IDENTITY(); -- pega ID novo UPDATE estatistica_item SET total_visualizacoes = total_visualizacoes + 1 WHERE item_id = @espetaculoId; COMMIT TRANSACTION; -- confirma tudo END TRY BEGIN CATCH -- pegou erro ROLLBACK TRANSACTION; -- desfaz tudo THROW; -- relanca pro app END CATCH END

▶ Executando

Out [2]

@interacaoId = 91823 · 1 row inserted · 1 row updated · COMMIT

DECLARE @id INT;
EXEC sp_marcar_assistido @userId=42, @espetaculoId=17, @interacaoId=@id OUTPUT;

📌 O que importa: INSERT + UPDATE acontecem juntos ou nenhum acontece. Falha = ROLLBACK automático.

🎯 Por que essa procedure importa?

🔄 Atomicidade. INSERT + UPDATE confirmam juntos. Falha de um desfaz o outro.

↩️ TRY / CATCH. Exceção dentro do TRY cai no CATCH, ROLLBACK e THROW relança.

📤 OUTPUT param. App recebe o ID na MESMA chamada, sem SELECT extra.

🚫 Sem estado inconsistente. Nunca há "interação registrada mas não contada".

🎯 Regra ao lado dos dados. A política vive perto da fonte da verdade.

Function · Escalar vs Tabular 📐

Escalar retorna 1 valor · tabular retorna SELECT — usa em FROM/JOIN

🔢 Escalar · 1 valor de volta

Você chama em qualquer lugar onde caberia um número/string/data: SELECT, WHERE, ORDER BY, em outra procedure, etc.

SQL [3a]

CREATE FUNCTION fn_score_usuario (@userId INT) RETURNS FLOAT AS BEGIN DECLARE @s FLOAT; SELECT @s = AVG(score) FROM recomendacao WHERE user_id = @userId; RETURN ISNULL(@s, 0); END

Out

SELECT dbo.fn_score_usuario(42); → 0.78

📋 Tabular · um SELECT inteiro

Você usa FROM/JOIN como se fosse uma tabela. Inline (sem BEGIN/END) é a versão mais performática.

SQL [3b]

CREATE FUNCTION fn_itens_similares (@itemId INT) RETURNS TABLE AS RETURN ( SELECT TOP (5) i.id, i.nome, s.similaridade FROM similaridade s JOIN item i ON i.id = s.item_b_id WHERE s.item_a_id = @itemId ORDER BY s.similaridade DESC );

Out

SELECT * FROM dbo.fn_itens_similares(17); → 5 rows

Procedure × Function · Comparação ⚖️

Quando usar cada uma · 6 critérios objetivos

Critério	Procedure	Function
Side-effects (INSERT / UPDATE / DELETE)	✅ Permitido — feita para isso	❌ Bloqueado pelo motor (com raras exceções)
Retorno	Result set, OUTPUT params, código de retorno	1 valor (escalar) ou tabela (TVF)
Uso em `SELECT` / `WHERE` / `FROM`	❌ Não — chamada por `EXEC`	✅ Sim — composta como qualquer expressão
Transações	✅ `BEGIN/COMMIT/ROLLBACK` à vontade	❌ Não controla transação
Tratamento de erro (`TRY/CATCH`)	✅ Suporta TRY/CATCH e `THROW`	⚠️ Limitado (não pode levantar erros arbitrários)
Caso típico	Operação de escrita, fluxo com transação, batch	Cálculo derivado, vizinhos, métrica que entra na query

📌 Regra de bolso: se a operação muda dados, é procedure. Se a operação calcula algo que entra em outra query, é function. Tem dúvida? Comece com procedure — é mais flexível.

Performance · Plano de Execução 📊

Cache do plano · parameter sniffing · quando forçar RECOMPILE

🧠 Cache do plano

Na primeira execução, o motor compila a procedure e guarda o plano em cache. Próximas execuções pulam essa fase. Isso é vantagem — até virar problema.

👃 Parameter sniffing

O plano é otimizado para o primeiro valor que chegou. Se a primeira chamada veio com @userId de poucos itens e a segunda com @userId de milhares, o plano vira lento — porque foi escolhido para o caso pequeno.

♻️ `OPTION (RECOMPILE)`

Use quando o plano varia muito com os parâmetros. Recompila a cada execução — perde-se o cache, ganha-se um plano sob medida. Não use por default; só onde dói.

📐 Índices que sustentam

Procedure boa precisa de índice que cubra o WHERE e o ORDER BY. Sem índice, a procedure só esconde a query lenta — não conserta.

🔍 Como ler o plano

SQL Server: SET SHOWPLAN_ALL ON ou Ctrl+M no SSMS para plano gráfico real.
PostgreSQL: EXPLAIN ANALYZE mostra plano + tempo real de cada nó.
Procure por Table Scan em tabelas grandes — é red flag de índice faltando.
Compare custo total (cost) entre planos antes/depois de criar um índice.

Passo a passo · Supabase 🧪

Procedure + Trigger + INSERT — cole no SQL Editor e veja acontecendo no banco

1

Criar as 2 tabelas

CREATE TABLE nota ( id SERIAL PRIMARY KEY, aluno TEXT, valor NUMERIC(4,2) ); CREATE TABLE nota_log ( id SERIAL PRIMARY KEY, nota_id INT, valor_antigo NUMERIC(4,2), valor_novo NUMERIC(4,2), alterado_em TIMESTAMP DEFAULT NOW() );

2

Trigger function · registra histórico

CREATE OR REPLACE FUNCTION fn_log_nota() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN INSERT INTO nota_log (nota_id, valor_antigo, valor_novo) VALUES (OLD.id, OLD.valor, NEW.valor); RETURN NEW; END; $$;

3

Trigger · dispara em UPDATE

CREATE TRIGGER trg_nota_log AFTER UPDATE OF valor ON nota FOR EACH ROW EXECUTE FUNCTION fn_log_nota();

4

Procedure · corrige a nota

CREATE OR REPLACE PROCEDURE sp_corrigir_nota(p_id INT, p_novo NUMERIC) LANGUAGE plpgsql AS $$ BEGIN UPDATE nota SET valor = p_novo WHERE id = p_id; END; $$;

5

Popular + chamar a procedure

-- INSERT inicial INSERT INTO nota (aluno, valor) VALUES ('Maria', 7.5); -- corrige a nota → trigger dispara! CALL sp_corrigir_nota(1, 8.5); CALL sp_corrigir_nota(1, 9.0);

6

Conferir o histórico gerado

SELECT * FROM nota_log ORDER BY alterado_em;

id	nota	antigo	novo	quando
1	1	7.50	8.50	10:01
2	1	8.50	9.00	10:02

📌 Supabase: Dashboard → SQL Editor → cole cada bloco. Abra Database → Tables → nota_log e veja as linhas surgindo sozinhas a cada CALL — quem inseriu foi o trigger. Procedure faz; trigger reage.

🎯 Quando usar OPTION (RECOMPILE)

Trade-off: cache de plano (rápido sempre) vs plano sob medida (rápido para aquele valor)

✅ USE quando

A distribuição dos dados é muito desigual entre valores do parâmetro — alguns @userId têm 3 itens, outros têm 1 milhão.
A query roda com pouca frequência (poucas vezes por minuto). O custo da recompilação é pago em ms — diluído por chamada.
Os outros tunings (índices, estatísticas, reescrita da query) já foram feitos e o problema de plano ruim ainda persiste.

❌ NÃO USE quando

A procedure roda milhares de vezes por segundo. O custo de compilar repetidamente fica mais caro do que o ganho do plano sob medida.
A distribuição dos dados é uniforme (todos os @userId têm número parecido de linhas). O cache do plano é exatamente o que você quer aqui.
Você ainda não confirmou que tem um problema real de parameter sniffing — não é mágica, é trade-off.

📌 Em uma frase: OPTION (RECOMPILE) é a opção nuclear contra parameter sniffing — toda execução é uma compilação fresca. Use só quando o plano realmente varia muito entre os parâmetros, porque você está trocando cache eficiente por plano sob medida.

O que é a Arquitetura MVVM? 🧱

Padrão de 3 camadas que separa responsabilidades · base do que vem na aula 6 (FrontEnd Mobile)

📱

View · Visão

A interface do usuário

Componentes visuais: telas, botões, textos, listas. Não processa dados — só exibe o que a ViewModel manda e avisa quando o usuário clica.

              <FlatList
  data={recos}
  onRefresh={refetch}
/>
            

🧠

ViewModel · Cérebro

O "cérebro" da tela

Busca dados na Model, transforma e prepara para a View. No React Native = Custom Hook (estado + funções utilitárias). Gerencia loading, error, data.

              const {
  recos, loading, refetch
} = useRecommendations(42);
            

🗄️

Model · Dados

Dados + regras de negócio

Tipos/classes que descrevem a forma dos dados + requisições para APIs ou bancos locais. É aqui que mora a chamada que aciona a stored procedure no servidor.

              RecoRepo.topN(userId, 10)
  → fetch('/users/.../recos')
  → CALL sp_top_...
            

💡 Exemplo concreto · Tela de perfil: a View renderiza foto e nome · a ViewModel tem a lógica de carregar e gerenciar o loading · a Model é quem vai no servidor buscar o JSON do usuário (que por baixo executa uma procedure como a nossa).

📌 Por que isso importa pra aula de hoje: a procedure que você está escrevendo agora vai virar a chamada na Model da aula 6. O contrato (parâmetros + retorno) que você fechar hoje é o que o ViewModel vai consumir.

Arquitetura MVVM · Diagrama 🗺️

Fluxo de eventos e dados entre as 3 camadas · até onde sua procedure aparece

📌 Lê assim · de cima para baixo (pedido) · de baixo para cima (resposta): usuário toca em "atualizar" na View → dispara refetch na ViewModel → que chama o Repository (Model) → que faz HTTP na API → que executa a Procedure. O resultado volta pelo mesmo caminho e a View re-renderiza. Toda a aula 4 está dentro daquele retângulo do servidor — é o que a aula 6 vai conectar do lado do app.

Como funciona o React Native? ⚙️

JavaScript que vira componente nativo · Hermes · Bridge · um único código para Android + iOS

📝 Você escreve JS/TS · ele entrega nativo

React Native não renderiza um site dentro do celular. Ele traduz seu código JavaScript em componentes nativos de cada SO. Resultado: app com a cara, velocidade e performance de um app tradicional.

Mesma base de código → Android e iOS
Reaproveita a biblioteca React e hooks que você já conhece
Componentes UI viram widgets nativos do SO

🔧 O motor por trás · Nova Arquitetura

Hoje o RN roda em um motor JS super rápido — o Hermes — e usa uma estrutura que comunica o JS diretamente com código nativo em C++, Java e Objective-C.

Hermes: JS engine otimizado para mobile (startup rápido, baixo uso de memória)
JSI / Bridge: ponte direta JS ↔ nativo, sem serialização pesada
Fabric + TurboModules: renderização e módulos nativos sob demanda

🔀 Quando você escreve <Text>Olá</Text> ...

Seu código

<Text>Olá</Text>

JS / TS · 1 arquivo

→

Hermes + Bridge

JS ↔ C++ ↔ SO

Traduz em chamada nativa

→

🤖 Android

TextView

🍎 iOS

UITextView

📌 Lê assim: você escreveu 1 linha de JSX, o motor Hermes executa, a ponte chama o widget nativo do SO. Mesmo binário, duas plataformas, performance nativa. A View do MVVM (slide anterior) vive nessa camada.

Como e onde compilar um APK? 📦

Android Studio · Expo (recomendado) · React Native CLI · qual escolher e por quê

⚠️ PESADO

🐘

Android Studio · emulador

IDE oficial do Google + AVD (Android Virtual Device). Ótimo para debug profundo, mas o emulador é caro: precisa de SDK, HAXM/Hyper-V, ~10 GB de imagem, RAM gorda e CPU livre.

Boot do AVD: 1–3 minutos
Trava em máquinas modestas
Útil quando precisa de logs nativos / perfil

⭐ RECOMENDADO

🚀

Expo · resolve quase tudo

Você não precisa do Android Studio para começar. O Expo entrega: Expo Go (app nativo no seu celular que abre o seu projeto via QR code) e EAS Build (compila o APK na nuvem deles).

Roda no seu celular real, sem emulador
Hot reload via Wi-Fi enquanto você edita
Acessa câmera, GPS, notificações de forma nativa

CONTROLE TOTAL

🔧

React Native CLI · puro

Sem Expo. Você compila localmente, então precisa do Android Studio + JDK instalados e configurados. Vale quando você precisa de módulos nativos customizados que o Expo não cobre.

Setup mais demorado
Mais flexibilidade no nativo
Build 100% local com Gradle

Expo
EAS

🚀 Build na nuvem · APK pronto pra instalar

# 1. instala o CLI npm install -g eas-cli # 2. faz login na conta Expo eas login # 3. configura o projeto (uma vez) eas build:configure # 4. gera APK de teste na nuvem deles eas build --platform android --profile preview # → link de download pro .apk ao terminar

RN
CLI

🔧 Build local · Gradle direto

# 1. entra na pasta android do projeto cd android # 2. roda o gradle pra gerar APK de debug ./gradlew assembleDebug # 3. APK fica em: # android/app/build/outputs/apk/debug/app-debug.apk # → instala no celular via USB ou arraste no emulador

📌 Recomendação pra esse projeto: comece com Expo + Expo Go. Você testa direto no seu celular via QR code, sem esperar emulador, e acessa câmera/GPS de forma nativa. Quando o APK final for necessário, eas build entrega na nuvem. Android Studio só se você precisar mesmo.

Caminho de uma requisição 🔁

Pull-to-refresh na tela → procedure roda no banco → 1 round-trip · 4 camadas de código

View
.tsx

// 📱 RecommendationsScreen.tsx — só JSX, sem fetch export function RecommendationsScreen() { const { recos, loading, refetch } = useRecommendations(42); return ( <FlatList data={recos} refreshControl={ <RefreshControl refreshing={loading} onRefresh={refetch} /> } renderItem={({ item }) => <RecoCard item={item} />} /> ); }

View-
Model

// 🧠 useRecommendations.ts — estado + side effect export function useRecommendations(userId: number) { const [recos, setRecos] = useState([]); const [loading, setLoading] = useState(false); const refetch = useCallback(async () => { setLoading(true); const data = await RecoRepo.topN(userId, 10); setRecos(data); setLoading(false); }, [userId]); useEffect(() => { refetch(); }, [refetch]); return { recos, loading, refetch }; }

Model
+ API

// 🔌 RecoRepo.ts (mobile) — só fala HTTP export const RecoRepo = { topN: async (userId, top) => { const r = await fetch(`${API}/users/${userId}/recos?top=${top}`); return r.json(); } }; // 🔌 server.ts (Express) — controller fino chama a procedure app.get('/users/:id/recos', async (req, res) => { const { rows } = await db.query( 'CALL sp_top_recomendacoes_usuario($1, $2)', [req.params.id, req.query.top ?? 10] ); res.json(rows); });

SQL

-- 🗄️ Procedure que vimos no slide 6 CREATE PROCEDURE sp_top_recomendacoes_usuario @userId INT, @top INT = 10 AS BEGIN SELECT TOP (@top) i.id, i.nome, r.score FROM recomendacao r JOIN item i ON i.id = r.item_id WHERE r.user_id = @userId ORDER BY r.score DESC; END -- 1 EXEC = 1 round-trip = lista pronta

📌 Leia de cima para baixo: View chama o hook · hook chama o Repository · Repository chama a API · API faz CALL da procedure. Se você trocar PostgreSQL por SQL Server, só a linha do db.query muda. A View nem sabe que existe banco. Aula 6 vai construir esse stack do lado do mobile.

🛠️

Mão na massa · Atividade em sala

65 minutos para entregar db/procedures.sql + db/README.md via Merge Request. Em grupo. Cada minuto conta.

1️⃣ Branch feat/db-procedures

2️⃣ Procedure 1 · Top-N recomendações

3️⃣ Procedure 2 · Registrar interação (transação)

4️⃣ Function escalar · Média de ratings

5️⃣ Function tabular · 5 itens similares

6️⃣ README com exemplos de chamada

7️⃣ ⭐ TRY/CATCH + índice de suporte

8️⃣ Abrir MR feat(db): procedures e functions

💡 Estratégia: 2 pessoas atacam as procedures, 2 as functions, 1 escreve o README enquanto os outros codam. Não tente ser perfeito — entregue.

🎯

A aula 4 me ensinou que...

Procedure e function não são "truques" — são contratos que encapsulam regras críticas dentro do BD e viram a Model do nosso app React Native (MVVM). Aula 5: Transactions e Triggers. Aula 6: FrontEnd Mobile + MVVM consome esse contrato.

✅ Quando descer lógica para o BD

✅ Anatomia de uma procedure

✅ OUTPUT params + transação

✅ Function escalar & tabular

✅ Procedure × Function

✅ Plano de execução · sniffing

✅ Migrações idempotentes

✅ MVVM · View / ViewModel / Model

✅ React Native · Hermes & APK (Expo)

Módulo 6 · Engenharia de Software · Aula 4 de 10