Baixe o app para aproveitar ainda mais
Prévia do material em texto
Sistema Gerenciador de Banco de Dados Aula 3 Professor Martin José Fagonde Morães Conversa Inicial A aplicação de procedures e trigger agregam muita performance nos sistemas e segurança aos dados. Estudaremos como criá-los e utilizá-los, bem como as particularidades que os diferenciam. No vídeo disponível no material on-line, você confere os comentários iniciais do professor Martin. Contextualizando Por meio de uma trigger, podemos implementar a atualização do estoque toda vez que uma venda for realizada. As procedures e as trigger reduzem o tráfego na rede e disponibilizam dados atualizados em menor tempo em relação aos aplicativos. Na videoaula disponível no material on-line, o professor Martin faz uma contextualização dos temas dessa aula. Tema 1 - Introdução Os procedimentos, as funções e os gatilhos são códigos criados dentro do banco de dados, utilizando uma linguagem de procedural do SGDB juntamente com os comandos SQL, visando automatizar operações. Eles são referenciados como programas armazenados, ou seja, quando se queira falar dos três de forma genérica utiliza-se a expressão programas armazenados. O objetivo é trazer para dentro do banco de dados operações que não precisam ser realizadas nos aplicativos, diminuindo o tráfego de dados na rede, diminuindo o processamento nas estações de trabalho, entre outros benefícios. Cada SGDB implementa a sua própria linguagem de programação, comumente denominadas de PL, acrônimo para “Procedural Language”. Esta linguagem implementa laços de repetição, controle de fluxo e outros recursos. A variação desta linguagem entre os principais SGDB é pequena. Para desenvolvimento desse estudo, é importante conhecer os conceitos a seguir: Procedures: são uma sequência de códigos acionadas pelos aplicativos ou por console de instruções SQL. Elas podem receber parâmetros para serem utilizadas em seu processamento; Functions: se assemelham às procedures e podem ser chamadas de locais, que a procedures não podem. Triggers: se a semelham às procedures e são executadas automaticamente antes ou depois de um dos comandos de DML (insert, update e delete), conforme definido na sua criação. Neste estudo, tomaremos por base o SGDB MySQL para nossos exemplos. No vídeo disponível no material on-line, o professor Martin traz mais informações sobre os programas armazenados. Tema 2 - Delimitadores As procedures, functions e triggers (programas armazenados) são constituídas de instruções SQL. Toda instrução SQL tem que terminar com um caractere delimitador, geralmente está definido o caractere ponto e vírgula (;). A criação de um destes programas armazenados também é uma instrução SQL do tipo CREATE e precisa ser encerrado com um delimitador. Se for usado o mesmo delimitador na criação de um programa armazenado e na definição das instruções de cada programa armazenado, o interpretador tentará executar todos e gerará um erro no interpretador. Para evitar esta situação e conseguir criar os programas armazenados, é necessário alterar o delimitar na criação do recurso. Depois do recurso criado, retorna o delimitador para o caractere padrão. Nas instruções SQL que farão parte do programa armazenado, tem que ser aplicado o caractere delimitar, que ficará como o padrão. Para ficar mais claro, vamos considerar a criação de uma procedure. A procedure se chamará “totaliza” e executará três instruções SQL. O delimitador padrão é o ponto e vírgula (;) e vamos usar como delimitador temporário o caractere asterisco (*). seq Código Explicação 1 Delimitador * Primeiramente, alteramos o delimitador padrão para o delimitador temporário. 2 Cria procedure totaliza() INÍCIO Instrução SQL 1; Instrução SQL 2; Instrução SQL 3; FIM* Escreve-se o código de criação da procedure utilizando em cada instrução SQL o delimitador padrão. Para terminar a instrução de criação da procedure, utiliza-se o delimitador temporário. 3 Delimitador ; Volta-se a definir o delimitador com o caractere padrão. Ficou com alguma dúvida a respeito dos delimitadores? No vídeo disponível no material on-line, o professor Martin traz mais informações. Tema 3 - Variáveis As variáveis em programas armazenados são utilizadas para armazenar valores de tipos definidos e são aplicadas como se utiliza variáveis em outras linguagens de programação. Nos programas armazenados, pode-se definir variáveis utilizando a instrução “DECLARE” e a variável tem de ter um nome e um tipo válido. Isso é feito dentro do bloco de instruções, ou seja, entre o BEGIN e o END dos programas armazenados. A sintaxe é: DECLARE var_name [, var_name] ... type [DEFAULT value] var_name: é o nome da variável. Pode ser definido mais de uma variável na mesma declaração, separando-as por vírgula; type: é o tipo da variável. Tem de ser um dos tipos válidos do SGDB. DEFAULT value: definir um valor inicial para a variável é opcional. value: é o valor. DEFAULT: é uma instrução. Exemplos: 1. Para declarar uma variável do tipo inteiro de nome “vlr”, faz-se o seguinte: DECLARE vlr int; 2. Para declarar uma variável do tipo inteiro de nome “vlr” com valor inicial de 5, faz-se o seguinte: DECLARE vlr int DEFAULT 5; Atribuição As variáveis criadas que não tenham definido um valor na sua criação são de valor NULL, até ter um valor atribuído. Para atribuir um valor a uma variável após sua criação, utiliza- se a instrução SET; o operador de atribuição é o caractere igual (=). Para atribuir o valor 10 à variável “vlr”, faz-se o seguinte: SET vlr = 10; Atribuição no SELECT Um valor retornado por um SELECT pode ser atribuído a uma variável utilizando a instrução INTO. Os seguintes cuidados também têm de serem tomados: A instrução SELECT só pode retornar um registro; A quantidade de variáveis tem de ser a mesma de colunas indicadas no SELECT; A instrução INTO tem de estar logo após aos nomes das colunas selecionadas. Exemplo: No exemplo a seguir, está sendo selecionado o menor e o maior valor do ID dos alunos e atribuído respectivamente as variáveis num1 e num2: SELECT MIN(id), MAX(id) INTO num1, num2 FROM alunos; Retornando o valor de uma variável Para retornar o valor de uma ou mais variáveis de dentro de um programa de armazenamento, basta chamar o SELECT e as respectivas variáveis. Exemplo: No exemplo anterior, as variáveis num1 e num2 ficaram, respectivamente, com o valor do menor e do maior ID dos alunos. Para fazer esses valores retornarem, faz-se da seguinte forma: SELECT num1, num2; Ao executar um SELECT de retorno de variáveis, o programa de armazenamento encerra para sua atividade. Para mais informações sobre as variáveis, confira no material on-line a videoaula do professor Martin. Tema 4 - Controle de fluxo Os controles de fluxo funcionam como nas demais linguagens de programação. Existem outros além dos que veremos aqui, estes são os mais usados e os mais comuns em todos os SGDB. Você pode aprofundar seu conhecimento a respeito do MySQL clicando no botão a seguir: http://dev.mysql.com/doc/refman/5.5/en/flow-control- statements.html IF Com o IF, testa-se uma condição com os operadores condicionais e lógicos. Se a condição for verdadeira (THEN), há uma instrução, ou conjunto delas, a ser executada. Se a condiçãofor falsa (ELSE / ELSEIF), pode-se ter um novo teste de condição com o ELSEIF ou aplicar uma instrução, ou um conjunto delas, após o ELSE. A instrução IF termina com um END IF. Sintaxe: IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF Exemplo: No seguinte exemplo de IF, temos dois testes: IF num1 = 1 THEN SELECT "UM"; ELSEIF num1 > 1 THEN SELECT "MAIOR"; END IF; Explicação do código: Código Explicação IF num1 = 1 THEN testando se a variável num1 é igual a 1. SELECT "UM"; se for verdadeiro, esta instrução será executada. ELSEIF num1 > 1 THEN se num1 não for igual a 1, então é feito um outro teste, se num1 é maior que um. SELECT "MAIOR"; se for verdadeiro, esta instrução será executada. END IF; Fim da instrução IF. CASE O CASE parte de um valor que é procurado em suas possibilidades, se encontrado um correspondente, as instruções são executadas e o bloco CASE é encerrado. Sintaxe: CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE case_value: é o valor a ser encontrado nas possibilidades; when_value: é o valor de possibilidade. Se for igual ao case_value (valor procurado), então (THEN) são executadas as respectivas instruções. Exemplo: No exemplo seguinte, caso a variável num1 tenha o valor 0, será executado SELECT “ZERO” e assim sucessivamente. CASE num1 WHEN 0 THEN SELECT "ZERO"; WHEN 1 THEN SELECT "UM"; WHEN 2 THEN SELECT "DOIS"; END CASE; A instrução CASE encerra com o END CASE. O controle de fluxo CASE pode conter a possibilidade ELSE. Ou seja, se nenhum valor correspondente ao procurado for encontrado, serão executadas as instruções contidas na opção ELSE. WHILE O WHILE é um controle de fluxo onde uma instrução, ou conjunto delas, é repetida se a condição for verdadeira. Sintaxe: WHILE search_condition DO statement_list END WHILE search_condition: teste de condição, se o resultado for verdadeiro as instruções que estão entre o DO e o END WHILE serão executadas. Exemplo: No exemplo a seguir, o WHILE testa se num2 é maior que 10. Se for, ele executa a instrução de fazer com que seja subtraído 100 de num2 e testa novamente. Realiza esta operação até que num2 seja menor ou igual a 10. WHILE num2 > 10 DO SET num2 = num2 - 100; END WHILE; REPEAT O REPEAT é um controle de fluxo onde uma instrução, ou um conjunto delas, é repetida até que a condição do teste seja verdadeira. Sintaxe: REPEAT statement_list UNTIL search_condition END REPEAT search_condition: teste de condição, se a condição não for satisfeita as instruções serão executadas novamente. Exemplo: No exemplo a seguir, o REPEAT subtrairá 10 da variável num2 e testará se num2 é menor que 10, se for menor o REPEAT será encerrado, do contrário o processo será executado mais uma vez. REPEAT SET num2 = num2 - 10; UNTIL num2 < 10 END REPEAT; Para mais informações sobre o controle de custos, confira no material on-line a videoaula do professor Martin. Tema 5 - Programas armazenados Os programas armazenados são rotinas que ficam armazenadas (salvas) dentro da base de dados. Esses programas (rotinas) utilizam os recursos do SGDB como o SQL e os recursos da base de dados, como os dados que estão nas tabelas, as próprias tabelas, os índices etc. Os programas armazenados podem ser as procedures, as triggers e as functions. Na sequência, vamos estudar as procedures e as triggers, analisando as vantagens em cada situação. Procedimentos - Procedure As procedures são executadas por chamadas, ou seja, no aplicativo ou em uma trigger se faz a chamada da procedure e ele executa o código programado. Sintaxe: CREATE PROCEDURE sp_name ([proc_parameter[,...]]) routine_body Utiliza-se o comando CREATE PROCEDURE seguido de um nome para a procedure e entre os parênteses as definições dos parâmetros (proc_parameter), que são opcionais. Após este cabeçalho, usa-se o corpo da procedure (routine_body) com a sequência de comandos a serem executados. Estas instruções ficam dentro de um bloco BEGIN ... END. No código seguinte, temos um exemplo de criação da procedure lista_prof_aluno. Essa procedure lista os professores e os respectivos alunos. Ela não recebe parâmetros e nem utiliza variáveis. CREATE PROCEDURE lista_prof_aluno() BEGIN SELECT p.nome, a.nome from aula left join alunos a on aula.idaluno = a.id left join professores p on aula.idprofessor = p.id; END Executar a procedure Para executar, ou seja, chamar uma procedure, é necessário fazer a instrução CALL. Sintaxe: Instrução CALL mais o nome da procedure, se tiver parâmetros incluí-los dentro de parêntese. CALL sp_name([parameter[,...]]) CALL sp_name[()] Exemplo: Para chamar a procedure lista_prof_aluno(), pode ser utilizada uma das linhas abaixo: CALL lista_prof_aluno(); CALL lista_prof_aluno; Não tendo parâmetro, não há necessidade de utilizar os parênteses. Especificando parâmetros Para as procedures, existem três opções de parâmetros: IN: são utilizados somente para introduzir valores a serem utilizados na procedure; OUT: são utilizados somente como armazenamento temporário de valores externos à procedure (isso vai ficar mais claro); INOUT: podem ser utilizados tanto para receber valores na procedure como para disponibilizar valores fora da procedure. Parâmetros IN Os parâmetros definidos como IN são utilizados somente para entrada de dados que serão utilizados na procedure. Vamos usar parâmetros IN. No nosso cenário da procedure lista_prof_aluno(), está listando todos os professores e os respectivos alunos relacionados na tabela “aula”. Vamos alterar esta procedure para que liste os alunos do professor que for indicado no parâmetro. A aplicação deste parâmetro é só de entrada de dados na procedure, então definiremos do tipo IN. Definir um parâmetro do tipo IN consiste em especificar na criação da procedure, entre os parênteses, que o referido parâmetro é IN, ou seja, antes do nome do parâmetro coloca- se a diretiva IN, depois o nome do parâmetro e o tipo do dado que ele terá. No nosso caso o tipo do dado é inteiro, pois se refere ao tipo de dado que está sendo armazenado o idprofessor. Exemplo: CREATE PROCEDURE `lista_prof_aluno`(in idProf int) BEGIN SELECT p.nome, a.nome from aula left join alunos a on aula.idaluno = a.id left join professores p on aula.idprofessor = p.id where aula.idprofessor = idProf; END Na cláusula WHERE, estamos restringindo a seleção ao valor recebido no parâmetro – idProf. Para chamar esta procedure, é necessário indicar um valor do tipo inteiro que será utilizado na cláusula WHERE. Exemplo: call aula. Lista_prof_aluno(101); Quando na criação de um parâmetro não é explicitado como IN, OUT ou INOUT, por default é IN. Parâmetros OUT Os parâmetros definidos como OUT são utilizados somente para disponibilizarem valores em variáveis externas a procedure, que poderão ser recuperados por outros selects. Eles utilizam recursos de variáveis de ambiente do usuário. Estas variáveis ficam ativas durante a sessão do usuário e poderão ser acessadas por todos os recursos que fizerem referência a ela. Para definir/criar uma variávelna sessão do usuário, basta utilizar a instrução SET seguido do nome da variável que tem de estar precedido de arroba e atribuir um valor para a variável, veja os exemplos da próxima página. Exemplo: No seguinte exemplo, está sendo criado duas variáveis no ambiente do usuário uma denominada @NUM e a outra @nome e estão recebendo valores: SET @NUM = 10, @nome = "MARTIN"; Observe que essas variáveis de ambiente não têm o tipo do dado definido, mas a característica indispensável delas é o arroga como prefixo do nome. Para acessar o valor de uma destas variáveis, basta utilizar a instrução SELECT e o nome da variável. Exemplo: SELECT @NUM, @nome; A resposta ao SET anterior e a este SELECT será como no quadro abaixo: @NUM, @nome 10 MARTIN Vamos aplicar o parâmetro OUT na procedure lista_prof_aluno_TOTAL, que é uma variação de exemplo que já utilizamos. Essa procedure lista todos os alunos de um professor, determinado pelo parâmetro IN “idProf”. Estamos acrescentando o parâmetro OUT de nome “total” e do tipo int. O parâmetro “total” receberá a quantidade de alunos, que são do professor, cujo “idProf” veio por parâmetro: CREATE PROCEDURE `lista_prof_aluno_TOTAL`(in idProf int, out total int) BEGIN SELECT p.nome, a.nome from aula left join alunos a on aula.idaluno = a.id left join professores p on aula.idprofessor = p.id where aula.idprofessor = idProf; SELECT count(*) into total from aula left join alunos a on aula.idaluno = a.id left join professores p on aula.idprofessor = p.id where aula.idprofessor = idProf; END Vamos executar a procedure “lista_prof_aluno_TOTAL”. Observe o código a seguir: 1. Primeiramente, está sendo definido e inicializado com valor zero a variável de sessão “@total_registros”; 2. A próxima instrução está chamando a procedure “lista_prof_aluno_TOTAL” e passando os parâmetros referentes ao ID do professor. O outro parâmetro é a variável de ambiente “@total_registros” para onde será atribuído o total de alunos do respectivo professor; 3. A instrução “select @total_registros;” está mostrando o valor guardado na variável “@total_registros”. set @total_registros = 0; call aula.lista_prof_aluno_TOTAL(101, @total_registros); select @total_registros; 1 º SELEC T 2 º SELECT Parâmetro INOUT Os parâmetros definidos como INOUT indicam que podem ser utilizados como valor de entrada e podem guardar novos valores. Utilizando o código do exemplo anterior, vamos alterar para utilizar uma única variável, que será do tipo INOUT. Criamos o parâmetro “inout idProf_total int”, que começará com um valor definido na chamada e na segunda select receberá um novo valor. CREATE PROCEDURE `lista_prof_aluno_TOTAL`(inout idProf_total int) BEGIN SELECT p.nome, a.nome from aula left join alunos a on aula.idaluno = a.id left join professores p on aula.idprofessor = p.id where aula.idprofessor = idProf_total; SELECT count(*) into idProf_total from aula left join alunos a on aula.idaluno = a.id left join professores p on aula.idprofessor = p.id where aula.idprofessor = idProf_total; END A chamada da procedure “lista_prof_aluno_TOTAL” fica assim: 1. Na primeira instrução, criamos e inicializamos a variável de ambiente “@total_registros” com o valor do ID do professor; 2. Na segunda instrução, fizemos a chamada da procedure passando no parâmetro a variável de ambiente; 3. Na terceira instrução, pedimos para mostrar o novo valor da variável de ambiente. Neste ponto, ela contém a quantidade de registros e não mais o valor do ID do professor. 1 º SELEC T 2 º SELEC T set @total_registros = 101; call aula.lista_prof_aluno_TOTAL(@total_registros); select @total_registros; Para mais informações sobre os procedimentos (procedures), confira no material on-line a videoaula do professor Martin. Tema 6: Gatilhos - triggers Os gatilhos são programas armazenados que são executados conforme o que for definido na sua criação. Os gatilhos podem ser programados para serem executados ou antes ou depois de uma instrução SQL de manipulação de dados – DML (insert, update ou delete) de uma tabela específica. Os gatilhos não têm parâmetros e não retornam valores após sua chama, mas se utilizam de todos os demais conceitos e recursos que vimos até aqui. São muito úteis para disparar eventos, registrar log, fazer tratamento nos dados e para outras situações decorrentes de operações DML em uma tabela específica. Na criação de uma trigger define-se: Um nome: trigger tem de ter um nome único, seguindo as mesmas regras dos nomes de tabelas, procedures etc.; Ocasião: definir quando ela deve ser executada, se antes ou depois do(s) evento(s) ao(s) qual (quais) está vinculada. Para definir que seja executada antes da ocorrência de um determinado evento, usa-se a palavra “BEFORE”; para que seja executada depois da ocorrência do evento usa-se a palavra “AFTER”; Eventos: definir que ela será executada na ocorrência das instruções “insert” e/ou “update” e/ou “delete”; Tabela: definir à qual tabela está vinculado a trigger. A definição é feita indicando o nome da tabela; Ordem: é opcional, mas podemos definir a ordem em que as triggers serão definidas. Isto é para as situações em que temos mais de uma trigger definida para a mesma tabela, o mesmo evento e a mesma ocasião. A seguir, temos a sintaxe da instrução de criação de uma trigger: CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body Exemplo: A trigger denominada “contador”, após cada inserção na tabela “professores”, soma mais um (1) na variável de ambiente “@Total”. DELIMITER // CREATE TRIGGER contador AFTER INSERT ON professores FOR EACH ROW BEGIN SET @Total = @Total+1; END// DELIMITER ; Com a trigger criada, ao inserirmos valores na tabela professores como a instrução seguinte, a variável @Total vai sendo incrementada. Nesta instrução, estamos inserindo três novos professores: INSERT INTO professores(nome) VALUES("Marcos"),("Maicon"),("Adriana"); Com “SELECT @Total;” podemos acompanhar o valor da variável @Total. OLD e NEW Os operadores “OLD” e “NEW” nos auxiliam a acessar os dados dos campos envolvidos nas operações DML. Considerando a nossa trigger “contador”, podemos acessar o valor dos nomes que estão sendo inseridos na tabela professores utilizando o operador NEW seguido do nome da coluna, ficando assim: NEW.nome. Vamos alterar nossa trigger para utilizar o operador NEW, guardaremos os novos nomes em uma variável de ambiente, separados por ponto e vírgula. Exemplo: Observe a nova linha. Estamos atribuindo a variável de ambiente “@NOMES” à concatenação que a função “CONCAT_WS” está retornando. Estamos dizendo para a função “CONCAT_WS”, concatenar o que tem na variável @NOMES com o dado que veio no NEW.nome e é para separá-los utilizando o primeiro parâmetro ‘;’. CREATE TRIGGER contador AFTER INSERT ON professores FOR EACH ROW BEGIN SET @Total = @Total+1; SET @NOMES = CONCAT_WS(';', @NOMES, NEW.nome); END Ao executar as seguintes instruções, estamos definindo uma variável de ambiente com o valor “A” e executando uma instrução INSERT com três registros a serem inseridos e, por fim, estamospedindo para mostrar o conteúdo da variável @NOMES; SET @NOMES = "A"; INSERT INTO professores(nome) VALUES("Aurélio"),("David"),("João"); SELECT @NOMES; Ao executar estas instruções, teremos como retorno o conteúdo de @NOMES e os nomes inseridos e tudo separado por ponto e vírgula. # @NOMES 'A;Aurélio;David;João' Em uma operação de INSERT só tem um novo registro (NEW), não tem um antigo registro (OLD); Em uma operação de DELETE só tem um antigo registro (OLD), não tem um novo registro (NEW); Em uma operação de UPDATE, temos um novo registro (NEW) e um antigo registro (OLD). Para mais informações sobre gatilhos (triggers), confira no material on-line a videoaula do professor Martin. Trocando Ideias Compartilhe no fórum suas ideias de quando utilizar procedure e de quando usar trigger! Na prática No contexto de variáveis de ambiente e dos parâmetros de uma procedure, qual a melhor alternativa para o seguinte cenário: O desenvolvedor (programador) precisa do valor resultante de uma das operações realizadas em uma procedure, que realiza várias operações. Como deve ser a procedure e como deve proceder o programador para ter acesso ao referido valor? Depois de responder à questão, veja o comentário do professor Martin: A procedure deve receber um parâmetro do tipo OUT ou INOUT, e atribuir a este parâmetro o valor da referida operação no enunciado, ou seja, o valor esperado pelo programador. O programador deve fazer a chamada da procedure por meio da instrução CALL e passar na posição do parâmetro OUT ou INOUT uma referência a uma variável de ambiente. Para acessar o valor da variável, após a chamada da procedure na forma apropriada o programador deve executar um SELECT na referida variável de ambiente, que ele mesmo forneceu. Síntese Ter rotinas rodando dentro da base de dados traz alguns benefícios, principalmente os envolvendo performance e segurança. Os programas armazenados, que são as procedures, triggers e functions, estão disponíveis nos principais SGDB que dispõem de facilidades diferentes entre eles. Variáveis e controle de fluxo são os recursos mais utilizados e comuns nos SGDB. Confira no vídeo disponível no material on-line a síntese do professor Martin. Referências AMADEU, Claudia Vicci (Org.). Banco de dados. São Paulo: Pearson Education do Brasil, 2014. (Coleção Bibliografia Universitária Pearson). ELMASRI, Ramez et al. (Orgs.). Sistemas de banco de dados. 6. ed. São Paulo: Pearson Education do Brasil, 2011. ORACLE Corporation e / ou suas afiliadas. Como o MySQL Utiliza Índices. Disponível em: <https://dev.mysql.com/doc/refman/5.7/en/mysql- indexes.html>. Acesso em: 17 nov. 2015.
Compartilhar