Linguagem SQL Avançada

Disciplina: Projeto de Banco de Dados

Linguagem SQL Avanada

Prof. Izaac [email protected]

Restries de integridade embutidas nos sistemas de informao formam um componente importante de programas de aplicao, que podem ser distribudas por todo o sistema.

TRIGGER, STORED PROCEDURE e FUNES

Existem fortes argumentos para implementao centralizada das restries de integridade relevantes do sistema de informao no banco de dados, como procedimentos armazenados ("stored procedures"), gatilhos("triggers") e funes definidas por usurios.

TRIGGER, STORED PROCEDURE e FUNES

Procedimento armazenado um mdulo de lgica procedimental mantido, administrado e executado pelo SGBD.

A razo primria para ser utilizado mover as regras de negcio da aplicao para o banco de dados. Um procedimento armazenado no fisicamente associado a outro objeto do banco de dados, como os gatilhos de banco de dados, podendo acessar e modificar uma ou mais tabelas.

STORED PROCEDURE

Ronaldo Celso Messias Correia [email protected]

Stored Procedures So funes escritas usando SQL e ficam armazenadas no servidor Podem receber parmetros e executar funes complexas, retornando

ou no as informaes para o usurio Vantagens:

Minimizam o trfego da rede So mais rpidos, aproveitam a capacidade do servidor e da otimizao do

SGBD Facilitam a manuteno. Pode ser acessada por diversos programas, se

houver alguma modificao a ser feita, basta faz-la no BD A resposta curta , sempre que voc puder. No existem desvantagens em se

usar stored procedures. Limitaes:

Passar qualquer informao varivel para a stored procedure como parmetros ou coloca-las em uma tabela que a stored procedure possa acessar.

A linguagem de escrita de stored procedures e triggers pode ser muito limitada para operaes mais complexas.

CREATE [OR REPLACE] PROCEDURE nomeproc [ (parmetro,...) ]IScorpo do procedimento: [declaraes](bloco PL/SQL) BEGIN

[EXCEPTION]END;

onde parmetro: nomepar [IN | OUT | IN OUT] tipo

[ { := | DEFAULT } expresso ]

Oracle PL/SQL

CREATE PROCEDURE ELIMINA_CLIENTE

(IDCLIENTE IN INTEGER)

AS ULTIMO VARCHAR2(50);

PRIMEIRO VARCHAR2(50);

BEGIN

SELECT SOBRENOME, NOME INTO ULTIMO, PRIMEIRO

FROM CLIENTE WHERE ID = IDCLIENTE;

INSERT INTO HISTORICO_CLIENTE

VALUES (IDCLIENTE, ULTIMO, PRIMEIRO);

DELETE FROM CLIENTE WHERE ID = IDCLIENTE;

EXCEPTION

WHEN NO_DATA_FOUND THEN

EXIBE_MSG_ERRO (ID DE CLIENTE INVLIDA);

END ELIMINA_CLIENTE;

STORED PROCEDURE


Stored Procedures Sintaxe MySQLCREATE PROCEDURE sp_name ([parameter[,...]]) [characteristic ...]BEGIN

END

[parameter: [ IN | OUT | INOUT ] nome_parametro tipotipo: Qualquer tipo de dados vlidos no MySQL

characteristic: LANGUAGE SQL [NOT] DETERMINISTIC SQL SECURITY {DEFINER | INVOKER} COMMENT string

Corpo:Declaraes de procedimento em SQL vlida

- o nome da store procedure

Param1, Param2 parmetros VendaDatas(Data1 Date, Data2 Date)

Cada parmetro um parmetro IN por default IN Parmetro de entrada OUT Parmetro de sada (ser

aassumido NULL como valor de entrada)

INOUT Entrada e Sada

Select * from INFORMATION_SCHEMA.ROUTINES


Stored Procedures MYSQL Exemplo 1

DELIMITER $$DROP PROCEDURE IF EXISTS simpleproc $$CREATE PROCEDURE simpleproc()BEGIN

SELECT MINHA PROCEDURE';

END $$DELIMITER ;

Para executar o procedimento CALL simpleproc()

O exemplo usa o comando delimiter para alterar o delimitador de instruo para antes da definio do procedure. Isto permite que o delimitador ; (padro) usado no corpo de procedure seja passado para o servidor em vez de ser interpretado pelo MySQL



DELIMITER //CREATE PROCEDURE inserecliente(v_nome VARCHAR(60), v_endereco VARCHAR(20))

BEGINIF ((v_nome != '') && (v_endereco != '')) THENINSERT INTO cliente (nome, endereco) VALUES (v_nome, v_endereco);

ELSESELECT 'NOME e ENDEREO devem ser fornecidos para o cadastro!' AS

Msg;END IF;

END;

Para executar o procedimento CALL inserecliente (jose da silva', Rua das flores');

CREATE TABLE cliente ( id int auto_increment primary key, nome varchar(60) not null, endereco varchar(40) not null, sexo varchar(1), data_cadastro timestamp default current_timestamp);


Stored Procedures MYSQL Exemplo 3DELIMITER //CREATE PROCEDURE updatecliente(v_id int, v_nome VARCHAR(60), v_endereco VARCHAR(20))

BEGIN IF ((v_id > 0) && (v_id !='' ) && (v_nome != '') && (v_endereco != '')) THEN UPDATE cliente SET nome = v_nome, endereco=v_endereco WHERE id = v_id;ELSE SELECT 'NOME e ENDEREO devem ser fornecidos para o cadastro!' AS Msg;END IF;

END;

Para executar o procedimento CALL updatecliente (10,jose da silva xavier', Rua do Amor Perfeito');



DELIMITER //CREATE PROCEDURE deletecliente(v_id int)BEGIN

IF ((v_id > 0) && (v_id !='' )) THENDELETE FROM cliente WHERE id = v_id;

ELSESELECT ID no informado AS Msg;

END IF; END;

Para executar o procedimento CALL deletecliente (10);


Stored Procedures MYSQL Exemplo 5Exemplo de utilizao de parmetro tipo IN

CREATE PROCEDURE sp_in (p VARCHAR(10)) SET @X = P;

Exemplo de utilizao de parmetro tipo OUTCREATE PROCEDURE sp_out (OUT p VARCHAR(10)) SET P = ola;

Exemplo de utilizao de parmetro tipo INOUTCREATE PROCEDURE sp_inout (INOUT p int)BEGIN SET @X = P * 2; SET P = @X;END;


Variveis de Usurio MYSQL

SET @varivel= { expressao inteira | expressao real | expressao string }[,@varivel= ...]

O MySQL suporta variveis especficas da conexo com a sintaxe @nomevarivel

As variveis no precisam ser inicializadas. Elas contm NULL por padro e podem armazenar um valor inteiro, real

ou uma string. Todas as variveis de uma thread so automaticamente liberadas quando

uma thread termina. Variveis de usurios devem ser utilizadas em expresses onde so

permitidas. O tipo padro de uma varivel baseada no tipo da varivel no incio da

instruo. (Assume-se que uma varivel no atribuda possui o valor NULL e do tipo STRING).


Variveis de Usurio MYSQL Para atribuir um valor a uma varivel em outras instrues diferentes de

SET utilizar o operador de atribuio := em vez de =, porque = reservado para comparaes em instrues diferentes de SET:

SET @t1=0, @t2=0, @t3=0;mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;+----------------------+------+------+------+| @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 |+----------------------+------+------+------+| 5 | 5 | 1 | 4 |+----------------------+------+------+------+mysql> SET @X=oi;mysql> SELECT @X


Variveis Locais MYSQL

DELIMITER //CREATE PROCEDURE SP_TEST(IN num INT)BEGIN

DECLARE x INT DEFAULT 0;SET x = num;

END;//

Uma varivel local somente ser vlida durante a execuo de um procedimento armazenado, seja ele uma Stored Procedure, uma Trigger ou Stored Function, sendo que, aps o trmino da execuo de tais procedimentos, esta varivel destruda da memria, juntamente com seu respectivo valor.

Para ser declarada, precisa estar entre os chamados compound statements, ou comandos aninhados dentro de um procedimento qualquer, que por sua vez tambm so chamados de Stored Routines.

Para se declarar uma varivel local, necessrio estarmos posicionados entre BEGIN ... END


Variveis Locais

Variveis locais no so case sensitives, ou seja, VAR, var, VaR e vAr so a mesma coisa. O que devemos nos atentar quanto ao seu escopo.

O escopo das variveis locais no MySQL definido pela sua posio, aninhada em blocos de uma procedimento armazenado no servidor de bancos de dados MySQL.

CREATE PROCEDURE SP_ESCOPO() BEGIN

DECLARE x INT DEFAULT 0; BEGIN

DECLARE x INT DEFAULT 0; BEGIN

DECLARE x INT; END; SET x = NULL;

END; SELECT x AS Var;

END;


Estudo de Caso Sistema para Aluguel de Carros


Stored Procedures MYSQLExemplo 6 Sistema para aluguel de carros - LOCAO

DELIMITER //CREATE PROCEDURE registrarlocacao(v_diarias int, v_datalocacao DATE, v_carro int, v_sede int)BEGIN

// Inserindo registro de nova reservaINSERT INTO reservas (diarias, datalocacao, carro_reserva, sedelocacao) VALUES (v_diarias, v_datalocacao, v_carro, v_sede)

// Atualizando a situao do carroUPDATE carros SET situacao = alugado WHERE id = v_carro

END;


Stored Procedures MYSQL Exemplo 7 Sistema para aluguel de carros - DEVOLUO

DELIMITER //CREATE PROCEDURE registrardevolucao(v_reserva int, v_dataretorno DATE, v_quilometrosrodados float(8,2), v_multa float(10,2), v_total float(10,2), v_sedelocacao int, v_sededevolucao int))BEGIN

// Atualizando registro de nova reservaUPDATE reservas SET dataretorno = v_dataretorno, quilometrosrodados = v_quilometrosrodados, multa = v_multa, total = v_total, sededevolucao = v_sededevolucao WHERE id = v_reserva;

// Atualizando a situao do carroIF (v_sedelocacao != v_sededevolucao) THEN UPDATE carros SET situacao = fora do ponto de origem WHERE id = v_carro;ELSE UPDATE carros SET situacao = disponivel WHERE id = v_carro;END IF

END;


Desvios Condicionais e Iteraes

IF THEN ELSE

create procedure sp_lista_clientes(in opcao integer)begin if opcao = 0 then select * from cliente where sexo = F; else if opcao = 1 then select * from cliente where sexo = M; else select * from cliente; end if; end if; end $$

IF condition THEN statement/;s ELSE statement/s;ENDIF



CASE

ENDIF

create procedure sp_lista_clientes(in opcao integer)begin CASE opcao WHEN 0 THEN select * from cliente where sexo = F; WHEN 1 THEN select * from cliente where sexo = M; else select * from cliente; END CASE;END

CASE variable WHEN condio1 statement/s; WHEN condio2 statement/s;ELSE statement/sEND CASE



REPEAT UNTIL

ENDIF

create procedure sp_repeat(in var1 integer)begin REPEAT SELECT var1; set var1 = var1 + 1; UNTIL var > 5; END REPEAT;END;

REPEAT statement/s;UNTIL condioEND REPEAT


Desvios Condicionais e Iteraes WHILE

ENDIF

create procedure sp_while(in var1 integer)begin WHILE (var1 < 20) DO SELECT var1; set var1 = var1 + 1; END WHILE;END;

WHILE condio DO statement/s;END WHILE


Manipuladores e Cursores Com o advento das stored procedures, certas condies podem

exigir tratamento especfico. Estas condies podem ser relacionadas a erros, bem como controle de fluxo geral dentro da rotina.

Manipuladores permitem executar declaraes caso certa condio esteja presente

Cursores permitem interar atravs de um resultset, processando-o linha a linha.

Cursores: O termo um acrnimo para CURrent Set Of Records (conjunto de registros corrente) So utilizados para posicionar um ponteiro em uma linha

especfica e podem permitir atualizaes para as linhas com base na posio atual (O MySQL no suporta)

ENDIF


Tratamento de Erros O tratamento de erros dentro de procedimentos armazenados no MySQL

baseado em condies. Caso uma determinada condio for atendida, um erro que podemos

personalizar, baseado nos tipos de condies existentes, ser disparado. Erros: violao de uma chave primria ou ndice nico, violao de integridade

referencial ou mesmo um WARNING em meio ao processamento do procedimento.

Esta instruo especifica condies que necessitaro de tratamento especial. Ela associa um nome com uma condio de erro especfica. O nome pode ser subsequentemente usado em uma instruo DECLARE HANDLER.

Alm dos valores SQLSTATE, cdigos de erro do MySQL tambm so suportados.

DECLARE condition_name CONDITION FOR condition_value

condition_value: SQLSTATE [VALUE] sqlstate_value | mysql_error_code


Tratamento de Erros

H dois tipo de manipuladores suportados pelo MySQL EXIT: encerram imediatamente o bloco BEGIN/END corrente CONTINUE: permite ao processo continuar depois que as

aes do manipulador foram executados

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statementhandler_type: CONTINUE | EXIT | UNDOcondition_value: SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code


Tratamento de Erros Exemplo 1

declarada uma condio chamada coluna_desconhecida, que ir surgir quando for atingido o SQLSTATE 42S22, que ocorre quando uma coluna desconhecida

O manipulador EXIT exibe a mensagem de erro. No corpo da procedure a declarao SELECT coluna (para ativar o cdigo de erro)

e SELECT continua, que nunca ser executada, pois a procedure ser encerrada assim que a condio estiver presente

CREATE PROCEDURE manipula1()BEGIN DECLARE coluna_desconhecida CONDITION FOR SQLSTATE '42S22'; DECLARE EXIT HANDLER FOR coluna_desconhecida BEGIN SELECT 'erro de coluna desconhecida'; END; SELECT coluna; SELECT continua';END;



O SQLSTATE aparecer sempre que um erro for enviado a um usurio quando este executa uma operao ilegal ou que viole a integridade dos dados.

Quando tentar inserir uma informao duplicada em uma coluna que chave primria de uma tabela, o erro 1062 com o SQLSTATE 23000 retornado

CREATE PROCEDURE violacao_chave(IN v_id INT)BEGIN DECLARE EXIT HANDLER FOR SQLSTATE '23000' BEGIN SELECT 'Violao de chave primria!' AS Msg; END; INSERT INTO cliente SET id =v_id;END;



Ao enviar um dado do tipo CHAR para ser inserido em uma coluna do tipo INT, causar um WARNING e uma mensagem de truncamento dos dados, condio declarada no HANDLER com a condio EXIT que encerrar o procedure.

CREATE PROCEDURE test.sp_2(IN num CHAR(1))BEGIN

DECLARE EXIT HANDLER FOR SQLWARNINGBEGIN

SELECT 'O dado foi truncado!' AS Msg;END;INSERT INTO test.tbl_1 SET id =num;

END;


Cursores em MySQL O MySQL no suporta todas as recursos dos cursores Os Cursores no MySQL so no sensitivos (no devemos atualizar uma tabela

enquanto estamos usando um cursor); so somente leitura (no podemos fazer atualizaes usando a posio do cursor); e s podemos avanar para o prximo registro e no para traz e/ou para frente

So usados para acessar um resultset que possa recuperar uma ou mais linhas. So usados para posicionar um ponteiro em uma linha especfica

Vrios cursores podem ser declarados num mesmo procedureDECLARE cursor_name CURSOR FOR sql_statement


Cursores em MySQL Exemplo 1

OPEN sp_cursos1: ativa o cursor previamente declarado FETCH: retorna a prxima linha do resultsetr atual. Os resultados devem ser

armazenados em algum lugar As variveis x e y armazenam as duas colunas retornadas pelo SELECT id, nome

FROM cliente que compe o cursor CLOSE sp_cursos1: fecha o cursor No exemplo acima apenas a primeira linha do resultset retornada

CREATE PROCEDURE exemplo_cursor1 (OUT rid INT, OUT rnome INT)BEGIN

DECLARE x,y INT; DECLARE sp_cursor1 CURSOR

FOR SELECT id, nome FROM cliente;OPEN sp_cursor1;

FETCH sp_cursor1 INTO x, y;CLOSE sp_cursor1;SET rid = x;SET rnome = y;

END |


CREATE PROCEDURE exemplo_cursor2 (OUT rid INT, OUT rnome VARCHAR(60))BEGIN

DECLARE x, z INT;DECLARE y VARCHAR(60);DECLARE sp1_cursor CURSOR

FOR SELECT id, nome FROM cliente;DECLARE CONTINUE HANDLER FOR NOT FOUND SET z = 1;OPEN sp1_cursor;REPEAT

FETCH sp1_cursor INTO x, y;UNTIL (z=1)

END REPEAT;CLOSE sp1_cursor;SET rid = x;SET rnome = y;

END



Para iterar pelo resultset inteiro e retornar os resultados utilizar um REPEAT UNTIL

No exemplo 2 um manipulador declarado com a condio NOT FOUND e atribui 1 varivel Z, sendo justamente z=1 a condio testada pelo REPEAT UNTIL

A condio NOT FOUND inclui todos os erros com SQLSTATE que comeam com 02, um dos quais o erro NO DATA TO FETCH

Outros tipos de condies tambm podem ser abordadas na criao de um HANDLER ou CONDITION: Declarar explicitamente um cdigo de SQLSTATE para tratar o erro; Declarar o SQLWARNING e todos os SQLSTATES iniciados com 01 sero tratados; Declarar NOT FOUND, mais comum em Cursors e Stored Functions, para

tratamento de SQLSTATES iniciados com 02; Declarar o SQLEXCEPTION que tratar erros de SQLWARNING ou NOT FOUND.


CREATE PROCEDURE curdemo()BEGIN DECLARE done INT DEFAULT 0; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1; DECLARE cur2 CURSOR FOR SELECT i FROM test.t2; DECLARE a CHAR(16); DECLARE b,c INT; OPEN cur1; OPEN cur2; REPEAT FETCH cur1 INTO a, b; FETCH cur2 INTO c; IF NOT done THEN IF b < c THEN INSERT INTO test.t3 VALUES (a,b); ELSE INSERT INTO test.t3 VALUES (a,c); END IF; END IF; UNTIL done END REPEAT; CLOSE cur1; CLOSE cur2;END

-- exibe os CPFs de empregados cujos salarios so maiores do que os salarios de seus supervidores

DECLARE v_salario NUMBER; v_salsuper NUMBER; v_CPF CHAR (9); v_CPFsuper CHAR (9); CURSOR c_salario IS SELECT CPF, salario, supervisor FROM empregado;

BEGIN OPEN c_salario; LOOP

FETCH c_salario INTO v_CPF, v_salario, v_CPFsuper; EXIT WHEN c_salario%NOTFOUND; IF v_CPFsuper IS NOT NULL THEN

SELECT salario INTO v_salsuper FROM empregado WHERE CPF = v_CPFsuper; IF v_salario> v_salsuper THEN

DBMS_OUTPUT.PUT_LINE(v_CPF); END IF;

END IF; END LOOP; IF c_salario%ISOPEN THEN CLOSE c_salario;

EXCEPTION WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE (Erro no CPF | | v_CPF); IF c_salario%ISOPEN THEN CLOSE c_salario;

END;

Gatilhos (triggers) so procedimentos especiais dirigidos por eventos, armazenados e executados em um SGBD, vinculados a uma tabela. Ocorre no momento de uma insero, atualizao ou excluso em uma tabela ou em uma view.

Um gatilho no pode ser invocado diretamente pelo usurio, mas apenas disparado automaticamente pelo SGBD, devido ocorrncia de um evento resultante de uma ao, normalmente, de modificao da tabela associada ao gatilho.

TRIGGER (Gatilhos)


Triggers - GatilhosDiferem da stored procedures pelo fato de no serem chamados diretamente pelo usurio: quando ocorre um determinado evento na tabela, eles so executadosPossibilitam:Manter a integridade dos dados: atualizar tabelas associadasCria logs do sistema: a cada inclusoNotificaes de alteraes no banco aos usurios Validao de restries de integridade mais complexas que as suportadas diretamente pelo SGBD


Triggers - Sintaxe

CREATE [DEFINER = {user | CURRENT_USER} TRIGGER nome_trigger tempo_trigger evento_triggerON nome_tabela FOR EACH ROWBEGIN declarao_trigger END


Triggers - Sintaxe

DEFINER: Quando o TRIGGER for disparado, esta opo ser checada para checar com quais privilgios este ser disparado. Utilizar os privilgios do usurio informado em user (ronaldo@localhost) ou os privilgios do usurio atual (CURRENT_USER). Caso essa sentena seja omitida da criao do TRIGGER, o valor padro desta opo CURRENT_USER();

Nome_trigger: define o nome do procedimento, por exemplo, trg_test; Tempo_trigger: define se o TRIGGER ser ativado antes (BEFORE) ou depois

(AFTER) do comando que o disparou; Evento_trigger: aqui se define qual ser o evento, INSERT, REPLACE, DELETE ou

UPDATE; nome_tabela: nome da tabela onde o TRIGGER ficar "pendurado" aguardando o

trigger_event; declaraes_trigger: as definies do que o o TRIGGER dever fazer quando for

disparado.


Trigger Exemplo 1

Trigger que atualiza o campo VALOR da tabela VENDAS cada vez que se alterar a tabela de itens

delimiter //CREATE TRIGGER InsItem AFTER INSERT ON ItensFOR EACH ROWBEGIN UPDATE Vendas set valor = valor + New.ValorTotal WHERE Pedido = New.Pedido;END //

O alias NEW indica o registro que est sendo inserido


Trigger Exemplo 2

Sistema para aluguel de carros - Atualizao da quilometragem rodada por um carro aps sua devoluo

DELIMITER //

CREATE TRIGGER tr_kmrodados after update on reservas

FOR EACH ROW

UPDATE carros SET quilometragem = quilometragem + NEW.quilometrosrodados WHERE NEW.carro_reserva = carros.id;


Trigger Exemplo 3

Excluso de um item da tabela

delimiter //CREATE TRIGGER DelItem AFTER DELETE ON ItensBEGIN UPDATE Vendas set valor = valor - OLD.ValorTotal WHERE Pedido = OLD.Pedido;END //

O alias OLD indica o registro que est sendo apagado


Trigger Exemplo 4

Trigger para atualizao de dadosSet term # ;CREATE TRIGGER AtuItem AFTER UPDATE ON ItensBEGIN IF (New.ValTotal OLD.ValTotal) THEN UPDATE Vendas set valor = valor - OLD.ValorTotal +

New.ValTotal WHERE Pedido = OLD.Pedido;END //


Generators (MySQL) ouSequence (Oracle)

Generator um gerador de nmeros sequencias, incrementados automaticamente

Vantagens: Criao de chaves na insero na tabela Gerar nmeros sequencias para numerar titulos enviados ao banco para

cobrana Gerar nomes de aquivos sequencias


Generators - Sintaxe

CREATE GENERATOR

Nome nico em todo o banco de dados

SET GENERATOR TO

Pode ter qualquer valor inteiro entre 264 e 264 1

GEN_ID(,)

Retorna o valo do generator. Para obter o valor atual do generator, usar a funo com passo 0


Trigger com Generator

CREATE GENERATOR CodProduto

SET GENERATOR CodProduto To )

Set term # ;CREATE TRIGGER InsProduto FOR ProdutosBEFORE INSERTBEGIN IF (New.Codigo IS NULL) THEN New.Codigo = GEN_ID(CodProduto,1);END #Set term ; #

Oracle PL/SQL

CREATE [OR REPLACE] TRIGGER nome_do_trigger

tempo do trigger: BEFORE ou AFTERevento disparador: DELETE ou INSERT ou UPDATE [OF coluna]nome da tabela: ON tabelatipode trigger: de comando ou de linha [FOR EACH ROW]condio: WHEN clusulacorpo do trigger: [declaraes](bloco PL/SQL) BEGIN

[EXCEPTION]END;

CREATE TRIGGER DELETA_CLIENTEBEFORE DELETE ON CLIENTEFOR EACH ROWBEGIN

INSERT INTO HISTORICO_CLIENTEVALUES (:OLD.ID, :OLD.SOBRENOME, :OLD.NOME);

END DELETA_CLIENTE;

OBS: Referncias a atributos em eventosDELETE :OLD (valores deletados)INSERT :NEW (valores inseridosUPDATE :OLD :NEW

TRIGGER (Gatilhos)


Exerccios

1 Considerando a tabela auditoria_salario com os seguintes atributos:func_codigo: cdigo do funcionriosalrio_inicial: salrio antes de ser alteradosalrio_alterado: novo salrio do funcionariodata_alterao: data da alterao do salrio nome_usurio: usurio que realizou a alterao do salrio do funcionrio

Criar um trigger que, ao alterar o salrio de um empregado, registrar corretamente na tabela auditoria_salario as atualizaes.


Exerccios

2- Cria uma store procedure chamada alteraSalFunc, que altera o salrio de um funcionrio de acordo com o nmero de dependentes que ele possui. Por exemplo, se o funcionrio possuir 1 dependente, ele ter um aumento de 10%; se o funcionrio possuir 2 dependentes, ele ter um aumento de 20%; e assim por diante. Deve ser passado como parmetro para a funo o nome completo do funcionrio, e a funo deve retornar a porcentagem de aumento do salrio do empregado, alm de atualizar o salrio do funcionrio de forma adequada no banco de dados.

Uma funo definida pelo usurio computa e retorna um resultado baseado num conjunto de valores de entrada. Uma vez definida e criada dentro do SGBD, torna-se disponvel para ser utilizada at mesmo em sentenas SQL

Funes

CREATE [OR REPLACE]FUNCTION nomefunc [ (parmetro,...) ]RETURN tiporetornoIS resultado tiporetorno

corpo da funo: [declaraes](bloco PL/SQL) BEGIN

............RETURN resultado[EXCEPTION]END;

onde o parmetro do tipo IN

Funo com Oracle

CREATE FUNCTION procuraid(primeiro IN CHAR(50), ultimo IN CHAR(50))RETURN INTEGER ASidcli INTEGER;BEGIN

SELECT id INTO idcli FROM clienteWHERE sobrenome=ultimo AND renome=primeiro;RETURN idcli;

EXCEPTIONWHEN NO_DATA_FOUND THENraise_application_error(-20130,'ID de Cliente Invlida');

END procuraid;

SELECT *FROM pedidosWHERE id_cliente=procuraid('Renata','Terra');

Funes Oracle

IS 257 Fall 2009

MySQL FunoCREATE FUNCTION saudacao (s CHAR(20)) RETURNS CHAR(50) -RETURN CONCAT(Boa Noite ',s,'!');

Executando > SELECT saudacao(Turma');

Resposta da Funo: Boa Noite Turma!

Definio de vises

CREATE VIEW [ (lista-de-colunas) ]

AS

CREATE VIEW empregados_sede

AS SELECT (Nome, Endereco, Sexo, Data-nasc)

FROM EMPREGADO, DEPARTAMENTO

WHERE EMPREGADO.Dept = DEPARTAMENTO.Numero

AND DEPARTAMENTO.Nome = Sede;

Slide 1TRIGGER, STORED PROCEDURE e FUNESTRIGGER, STORED PROCEDURE e FUNESSTORED PROCEDUREStored ProceduresSlide 6STORED PROCEDUREStored Procedures Sintaxe MySQLStored Procedures MYSQL Exemplo 1Stored Procedures MYSQL Exemplo 2Stored Procedures MYSQL Exemplo 3Stored Procedures MYSQL Exemplo 4Stored Procedures MYSQL Exemplo 5Variveis de Usurio MYSQLVariveis de Usurio MYSQLVariveis Locais MYSQLVariveis LocaisEstudo de Caso Sistema para Aluguel de CarrosStored Procedures MYSQL Exemplo 6Stored Procedures MYSQL Exemplo 7Desvios Condicionais e IteraesDesvios Condicionais e IteraesDesvios Condicionais e IteraesDesvios Condicionais e IteraesManipuladores e CursoresTratamento de ErrosTratamento de ErrosTratamento de Erros Exemplo 1Tratamento de Erros Exemplo 2Tratamento de Erros Exemplo 3Cursores em MySQLCursores em MySQL Exemplo 1Cursores em MySQL Exemplo 2Cursores em MySQL Exemplo 2Cursores em MySQL Exemplo 3Slide 36TRIGGER (Gatilhos)Triggers - GatilhosTriggers - SintaxeTriggers - SintaxeTrigger Exemplo 1Trigger Exemplo 2Trigger Exemplo 3Trigger Exemplo 4Generators (MySQL) ou Sequence (Oracle)Generators - SintaxeTrigger com GeneratorSlide 48TRIGGER (Gatilhos)ExercciosExercciosFunesSlide 53Slide 54MySQL FunoDefinio de vises

Documents

Linguagem SQL Avançada