Projeto de
Banco de Dados - Aula 3
Stored
Programs
Stored
Programs é um termo genérico para:
-
Stored Procedures
-
Stored Functions
-
Triggers
Benefícios:
- Segurança
- Integridade
- Melhoria de performance
- Manutenibilidade.
Stored
Programs algumas vezes conhecido como Stored
Module ou Stored Routine – é um
programa de computador (uma série de instruções associadas a um nome) que é
salvo e executado dentro de um servidor de Banco de Dados.
O código-fonte e a versão compilada quase sempre
estão no Banco de Dados.
Quando o programa é executado, é executado dentro
de um endereço de memória de um processo ou thread no Banco de Dados.
Stored
Procedure
É o tipo mais comum de Stored Program. Um Store
Procedure é uma unidade de um programa genérico que é executado sob demanda
e que pode aceitar múltiplas entradas e saidas.
Stored
Functions
Similar a Stored
Procedures, o resultado de sua execução resulta no retorno de um único
valor. Pode ser utilizada dentro de uma declaração SQL, permitindo o programador
estender as capacidades da linguagem SQL.
Triggers
São Stored
Programs que são ativados em resposta a algum evento ou a alguma atividade
no Banco de Dados.
Tipicamente um trigger
será invocada em resposta a uma operação DML (INSERT, UPDATE, DELETE) em uma
tabela. Podem ser utilizadas para a validação de dados ou para automação de
desnormalização.
Por que
usar?
Stored
Programs oferece as seguintes vantagens:
- Pode levar a uma maior segurança;
- Oferece um mecanismo para abstrair as rotinas de
acesso a dados, melhorando a manutenibilidade do código;
- Reduz o tráfego de rede, devido ao programa
trabalhar os dados dentro do servidor, ao invés de transferir os dados através
da rede.
- Pode ser utilizado para implementar rotinas
comuns acessíveis por multiplas aplicações, executadas dentro ou fora do
servidor.
- Isolar as implementações relativas ao dados do
negócio da aplicação.
- Pode sob certas circunstâncias, melhorar a
portabilidade das aplicações.
Não é
necessário mover todas as aplicações para Stored Programs, somente por causa das vantagens. Deve-se
analisar o negócio primeiro e sua necessidade de mudança.
O MySQL implementa Stored Programs como um subconjunto do ANSI SQL:2003 SQL/PSM
(Persistent Stored Module) specification.
MySQL Stored Programs – procedures, functions e
triggers – estão em acordo com o padrão ANSI.
Integração
com SQL
Stored
Programs é altamente integrado com o SQL, sem a necessidade de software
intermediário como JDBC ou ODBC para construir ou executar instruções
em SQL.
Como uma alternativa na escrita INSERT, UPDATE,
DELETE ou SELECT diretamente. Podemos
escrever:
1.CREATE
PROCEDURE example1()
2.
BEGIN
3.
DECLARE l_book_count INTEGER;
4.
5.
SELECT COUNT(*)
6.
INTO l_book_count
7.
FROM books
8.
WHERE author LIKE '%HARRISON,GUY%';
9.
10.
SELECT CONCAT('Guy has written (or co-written) ',
11.
l_book_count ,
12.
' books.');
13.
14.
-- Oh, and I changed my name, so...
15.
UPDATE books
16.
SET author = REPLACE (author, 'GUY', 'GUILLERMO')
17.
WHERE author LIKE '%HARRISON,GUY%';
18. END
Lógica de Controle Condicional
A Stored
Program Language permite o uso de comandos de controle condicionais, para o
controle de quais linhas podem executar dada uma certa condição.
Declarações IF e CASE
Ambos implementam lógica condicional com diferentes
estruturas. Permite expressarmos lógica como, por exemplo:
“Se a quantidade de páginas de um livro for maior
que 1000, então...”
Loop e Controles de Iteração
Incluem
LOOP, WHILE e REPEAT UNTIL.
CREATE
PROCEDURE pay_out_balance (account_id_in INT)
BEGIN
DECLARE
l_balance_remaining NUMERIC(10,2);
payout_loop:LOOP
SET
l_balance_remaining = account_balance(account_id_in);
FUNCTION
IF
l_balance_remaining < 1000 THEN
LEAVE
payout_loop;
ELSE
CALL
apply_balance(account_id_in, l_balance_remaining);
PROCEDURE
END
IF;
END
LOOP;
END
Stored
Functions
Podemos usar nossa stored function dentro de um
comando SQL.
SELECT
firstname, surname, date_of_birth, f_age(date_of_birth)
AS age FROM employees;
3.1
Triggers (gatilho)
Um trigger é um stored program que é
automaticamente chamado em resposta a um evento dentro do banco de dados.
Um trigger pode automaticamente calcular
valores derivados ou desnormalizados.
CREATE
TRIGGER employees_trg_bu
BEFORE
UPDATE ON employees
FOR
EACH ROW
BEGIN
IF
NEW.salary <50000 THEN
SET
NEW.contrib_401K=500;
ELSE
SET
NEW.contrib_401K=500+(NEW.salary-50000)*.01;
END IF;
END
Criando Procedures
Podemos
criar Stored Program com CREATE PROCEDURE, CREATE FUNCTION, ou CREATE TRIGGER. É
possível utilizá-los diretamente na linha de comando do MySQL ou MySQL Query
Browser.
3.2
3.3
Variáveis
Variáveis locais podem ser declaradas dentro de
stored procedures utilizando o comando DECLARE. É possível definir valores
DEFAULT e atribuir novos valores utilizando o comando SET.
CREATE
PROCEDURE variable_demo()
BEGIN
DECLARE
my_integer INT; /* 32-bit integer */
DECLARE
my_big_integer BIGINT; /* 64-bit integer */
DECLARE
my_currency NUMERIC(8,2); /* Number with 2 decimals*/
DECLARE
my_pi FLOAT /* Floating point number*/
DEFAULT
3.1415926; /* initialized as PI */
DECLARE
my_text TEXT; /* huge text */
DECLARE
my_dob DATE
DEFAULT
'1960-06-21'; /* My Birthday */
DECLARE
my_varchar VARCHAR(30)
DEFAULT
'Hello World!'; /* 30 bytes of text*/
SET
my_integer=20;
SET
my_big_integer=POWER(my_integer,3);
END;
Parâmetros
A maioria dos stored programs utilizam um ou mais
parâmetros, o que os tornam mais flexíveis e mais úteis.
Modos de Parâmetros
Parâmetros podem ser definidos como IN, OUT ou
INOUT.
IN – Modo padrão. Indica que o parâmetro
pode ser passado para o programa mas qualquer modificação não é retornada para
o programa que iniciou a chamada.
OUT – A modificação é retornada ao programa
que iniciou a chamada.
INOUT – Neste modo o programa pode ler o
parâmetro e o programa-chamador pode ver qualquer modificação que o programa
fez no parâmetro.
3.4
3.5
3.6
Nenhum comentário:
Postar um comentário