Banco de Dados Objetos SGBD Visões Rotinas e Gatilhos

Banco de Dados Prof Daniel Callegari Aula 08 Aula 08 Outros objetos de SGBDs Existem muitos outros objetos em um SGBD Veremos alguns deles Visões Rotinas armazenadas Gatilhos triggers Procedimentos procedures Dinâmicas O que você vai aprender nessa aula Parte 1 Outros objetos de SGBDs Parte 2 Visões views Parte 3 Visões e atualizações Parte 4 Rotinas armazenadas Parte 5 Dinâmica Aula 8 Parte 1 Outros objetos de SGBDs Além de Tabelas um SGBD também pode armazenar por exemplo Visões views Gatilhos triggers Procedimentos procedures Sequências sequences Usuários users etc Aula 8 Parte 2 Visões views Visões são como tabelas virtuais Aula 8 Parte 3 Visões e atualizações Como atualizar dados a partir de visões INSERT UPDATE e DELETE aplicados a VIEWs Aula 8 Parte 4 Rotinas armazenadas Código armazenado dentro do SGBD Gatilhos triggers Procedimentos procedures Aula 8 Parte 5 Dinâmica Prática com outros objetos de SGBD O que você vai precisar para acompanhar essa aula livesqloraclecom O que você vai aprender nessa aula Parte 1 Outros objetos de SGBDs Parte 2 Visões views Parte 3 Visões e atualizações Parte 4 Rotinas armazenadas Parte 5 Dinâmica Medico crm nome Especialidade codigo nome Paciente codigo nome cpf data de nascimento sexo email Ficha atende numero data do exame Exame codigo sigla descricao valor prazo de entrega inclui Medico crm nome FKEspecialidadecodigo Especialidade codigo nome Paciente codigo nome cpf datadenascimento sexo email Fichas numero datadoexame FKMedicocrm FKPacientecodigo FichasExames FKFichaatendenumero FKExamecodigo Exames codigo sigla descricao valor prazodeentrega Dinâmica Tabela MEDICOS Tabela ESPECIALIDADES Tabela PACIENTES CODPACIENTE NOME CPF DATANASC SEXO EMAIL 1 Juliana Pires 49281225506 04OCT62 F jupiresemailcom 2 Maria Souza 49638826479 16NOV52 F mariaemailcom 3 Jaques Lutti 37843461388 09AUG70 M jaquesemailcom 4 Carla do Santos 70719177871 13SEP84 F csantosemailcom 5 Raquel Gomes 25513392275 21JAN37 F raqgomesemailcom 6 Katia Fernandes 52616855089 21JUL38 F katia22emailcom 7 Marcelo Cunha 64271911445 19MAR75 M mcemailcom 8 Guilherme Alves 83773468423 27DEC98 M guial89emailcom 9 Marcio Perez 71317480210 03JUL94 M marciopemailcom 10 Lucas Florian 58795952268 30DEC85 M florianemailcom Tabela EXAMES CODEXAME SIGLAEXAME DESCRICAO VALOR PRAZODEENTREGA 1 HEMO Hemograma 50 2 2 COHDL Colesterol HDL 25 2 3 COLDL Colesterol LDL 25 2 4 TRIGL Triglicerídios 20 3 5 ACDUR Ácido Úrico 30 4 6 CREAT Creatinina 35 1 7 TGO Transaminase Oxalacética 27 4 8 TGP Transaminase Pirúvica 36 3 9 TSH Tireoestimulante 40 2 10 PSA Antigeno Prostático Especifico 43 2 Dinâmica Tabela FICHAS Tabela FICHASEXAMES SQL Worksheet select from PACIENTES CODPACIENTE NOME CPF DATANASC SEXO EMAIL 1 Juliana Pires 49281225506 04OCT62 F jupiresemailcom 2 Maria Souza 49638826479 16NOV52 F mariaemailcom 3 Jaques Lutti 37843461388 09AUG70 M jaquesemailcom 4 Carla do Santos 70719177871 13SEP84 F csantosemailcom 5 Raquel Gomes 25513392275 21JAN37 F raqgomesemailcom 6 Katia Fernandes 52616855089 21JUL38 F katia22emailcom 7 Marcelo Cunha 64271911445 19MAR75 M mcemailcom 8 Guilherme Alves 83773468423 27DEC98 M guila189emailcom 9 Marcio Perez 71317480210 03JUL94 M marciopemailcom 10 Lucas Florian 58795952268 30DEC85 M florianemailcom Download CSV 10 rows selected Dinâmica Vamos modificar um pouco o esquema do Estudo de Caso Acrescentaremos um campo de disponibilidade para os exames ALTER TABLE EXAMES ADD disponivel CHAR1 DEFAULT S NOT NULL UPDATE EXAMES SET disponivel N WHERE codexame IN 7 8 Exames TGO e TGP ficam indisponíveis CODEXAME SIGLAEXAME DESCRICAO VALOR PRAZODEENTREGA DISPONIVEL 1 HEMO Hemograma 50 2 S 2 COHDL Colesterol HDL 25 2 S 3 COLDL Colesterol LDL 25 2 S 4 TRIGL Triglicerídios 20 3 S 5 ACDUR Ácido Úrico 30 4 S 6 CREAT Creatinina 35 1 S 7 TGO Transaminase Oxalacética 27 4 N 8 TGP Transaminase Pirúvica 36 3 N 9 TSH Tireoestimulante 40 2 S 10 PSA Antigeno Prostático Especifico 43 2 S Outros objetos em SGBDs Além de Tabelas um SGBD também pode armazenar por exemplo Visões views Gatilhos triggers Procedimentos procedures Sequências sequences Usuários users etc Oracle via SQL Developer Resumo do que vimos até agora Revisão do Estudo de Caso Acréscimo na tabela de exames disponibilidade Um SGBD armazena diversos outros objetos além de tabelas de dados seus relacionamentos e suas restrições de integridade Banco de Dados Prof Daniel Callegari Aula 08 Relembrando o conteúdo do vídeo anterior Revisão do Estudo de Caso Acréscimo na tabela de exames disponibilidade Um SGBD armazena diversos outros objetos além de tabelas de dados seus relacionamentos e suas restrições de integridade O que você vai aprender nessa aula Parte 1 Outros objetos de SGBDs Parte 2 Visões views Parte 3 Visões e atualizações Parte 4 Rotinas armazenadas Parte 5 Dinâmica Visões VIEWs Uma visão é uma espécie de tabela virtual criada a partir de uma consulta a fim de representar a visão de um usuário ou de uma aplicação sobre o banco de dados Uma visão pode ser consultada da mesma forma que uma tabela mas sua atualização está sujeita a determinadas regras Visões VIEWs Visões podem ser criadas para por exemplo Definir a percepção que um usuário ou aplicação possui do banco de dados Simplificar a construção de consultas definindo consultas frequentes ou complexas Possibilitar a independência de dados Fornecer segurança de acesso Visões VIEWs A sintaxe geral para se criar uma visão é CREATE OR REPLACE VIEW nomevisao AS consultaSQL Dica Podese pensar que uma visão é um apelido para um SELECT Importante Embora possua estrutura idêntica a de uma tabela as linhas retornadas por uma visão estão armazenadas nas tabelas originalis tabelasbase Em outras palavras uma visão não armazena dados nem cópias dos dados Dinâmica 1 Vamos criar uma visão para os exames disponíveis CREATE OR REPLACE VIEW ExamesDisponiveis AS SELECT codexame siglaexame descricao valor prazodeentrega FROM Exames WHERE disponivel S Selecionando dados a partir da VIEW como se fosse uma tabela SELECT FROM ExamesDisponiveis Dinâmica 2 Crie visões para consultas comuns a VIEW PACIDADES Paciente nome maiúsculas e idade omitindo informações sensíveis b VIEW MEDESP Médicos crm nome do médico e nome da especialidade c VIEW PACREC Paciente cpf nome e data da ficha mais recente d VIEW EXAMQTD Total de solicitações de cada exame Resumo do que vimos até agora Existem vários outros objetos em SGBDs Visões facilitam consultas comuns podem retornar dados modificados podem ser usadas para proteger certas informações dentre outras vantagens Banco de Dados Prof Daniel Callegari Aula 08 Relembrando o conteúdo do vídeo anterior Existem vários outros objetos em SGBDs Visões facilitam consultas comuns podem retornar dados modificados podem ser usadas para proteger certas informações dentre outras vantagens O que você vai aprender nessa aula Parte 1 Outros objetos de SGBDs Parte 2 Visões views Parte 3 Visões e atualizações Parte 4 Rotinas armazenadas Parte 5 Dinâmica Dinâmica Com as visões da parte 2 dessa aula já criadas a A partir da VIEW PACIDADES Listar os nomes dos pacientes idosos a partir de 60 anos b A partir da VIEW MEDESP Listar os médicos Cardiologistas c A partir da VIEW PACREC Listar os nomes dos pacientes com fichas criadas nos últimos 7 dias d A partir da VIEW EXAMQTD Listar os exames com apenas 1 solicitação realizada Atualizações em Visões Se visões não possuem dados será que é possível realizar modificações nos dados através das visões Atualizações em Visões Inserções INSERT atualizações UPDATE e exclusões DELETE em visões são permitidas desde que sejam obedecidas as seguintes regras Não é possível excluir linhas de visões que possuam funções de agrupamento GROUP BY ou DISTINCT Por que isso ocorre Atualizações em Visões Inserções INSERT atualizações UPDATE e exclusões DELETE em visões são permitidas desde que sejam obedecidas as seguintes regras Não é possível excluir linhas de visões que possuam funções de agrupamento GROUP BY ou DISTINCT Não é possível atualizar linhas em qualquer das situações indicadas pelo item anterior e colunas definidas por expressões e a pseudocoluna ROWNUM Atualizações em Visões Inserções INSERT atualizações UPDATE e exclusões DELETE em visões são permitidas desde que sejam obedecidas as seguintes regras Não é possível excluir linhas de visões que possuam funções de agrupamento GROUP BY ou DISTINCT Não é possível atualizar linhas em qualquer das situações indicadas pelo item anterior e colunas definidas por expressões e a pseudocoluna ROWNUM Não é possível inserir linhas em qualquer das duas situações anteriores e também se existirem colunas NOT NULL nas tabelas de origem que tenham ficado fora da visão Dinâmica Experimente a Adicionar um novo exame através da visão ExamesDisponiveis b Atualizar a idade de um paciente via PACIDADES c Adicionar um novo médico e o nome de sua especialidade através da visão MEDESP d Excluir linhas da visão EXAMQTD Quais atualizações sãoforam possíveis Por quê Atualizações em Visões Revisando Inserções INSERT atualizações UPDATE e exclusões DELETE em visões são permitidas desde que sejam obedecidas as seguintes regras Não é possível excluir linhas de visões que possuam funções de agrupamento GROUP BY ou DISTINCT Não é possível atualizar linhas em qualquer das situações indicadas pelo item anterior e colunas definidas por expressões e a pseudocoluna ROWNUM Não é possível inserir linhas em qualquer das duas situações anteriores e também se existirem colunas NOT NULL nas tabelas de origem que tenham ficado fora da visão Resumo do que vimos até agora Existem vários outros objetos em SGBDs Visões facilitam consultas comuns podem retornar dados modificados podem ser usadas para proteger certas informações dentre outras vantagens É possível alterar dados via visões em situações específicas Banco de Dados Prof Daniel Callegari Aula 08 Relembrando o conteúdo do vídeo anterior Existem vários outros objetos em SGBDs Visões facilitam consultas comuns podem retornar dados modificados podem ser usadas para proteger certas informações dentre outras vantagens É possível alterar dados via visões em situações específicas O que você vai aprender nessa aula Parte 1 Outros objetos de SGBDs Parte 2 Visões views Parte 3 Visões e atualizações Parte 4 Rotinas armazenadas Parte 5 Dinâmica Outros objetos em SGBDs Alguns SGBDs possuem uma linguagem de programação interna além da sua implementação da linguagem SQL No caso do Oracle ela se chama PLSQL e permite que trechos de código sejam criados compilados e executados diretamente no SGBD Blocos de código beginend Declaração de variáveis números texto datas booleanos Instruções de seleção ifelse Estruturas de repetição loop for while Cursores resultados de um select Tratamento de Exceções exceptions Dinâmica Ex Listar os aniversariantes do mês utilizando um objeto cursor Outros objetos Procedimentos Um procedimento é um conjunto de instruções que realiza determinada tarefa Tanto a definição como o funcionamento dos procedimentos são similares à programação em outras linguagens e envolvem basicamente os seguintes itens Identificação do procedimento Definição dos parâmetros Conjunto de instruções do procedimento Submissão do código ao SGBD PUGA S FRANÇA E GOYA M Banco de Dados implementação em SQL PLSQL e Oracle 11g São Paulo Pearson 2014 332p CREATE OR REPLACE PROCEDURE nomeprocedimento parametro IN OUT IN OUT tipoparametro IS BEGIN END Dinâmica Ex Procedimento para reajustar o valor de um exame Outros objetos Gatilhos Gatilhos TRIGGERs são blocos PLSQL acionados ou disparados automaticamente antes ou depois da ocorrência de um evento Este evento pode ser uma operação de inserção alteração ou exclusão de dados ou algum evento associado ao banco como efetuar logon de um usuário Os gatilhos podem ser utilizados para a implementação de regras de negócio geração de valores calculados manutenção da integridade referencial dos dados replicação de dados ou manutenção de registro histórico de alterações ocorridas no banco de dados entre outras PUGA S FRANÇA E GOYA M Banco de Dados implementação em SQL PLSQL e Oracle 11g São Paulo Pearson 2014 332p CREATE OR REPLACE TRIGGER nomegatilho BEFORE AFTER evento ON nometabela DECLARE BEGIN END Outros objetos Gatilhos Algumas razões para usar gatilhos são Fazer log de eventos para depuração ou auditoria Obter estatísticas de acesso a uma tabela Modificar dados de uma tabela quando comandos DML são aplicados a visões Garantir integridade referencial em bancos de dados distribuídos Impedir comandos DML fora de um horário estabelecido Impedir transações inválidas conforme regras de negócio da aplicação Exemplos atualizar estoque de produtos automaticamente calcular folha de pagamento Dinâmica Ex Armazenar automaticamente o histórico de reajustes de exames Dinâmica Ex Impedir que reajustes aconteçam durante o horário comercial Ao executar um UPDATE durante o horário comercial 8h 18h Resumo do que vimos até agora Existem vários outros objetos em SGBDs Visões e Rotinas Armazenadas são objetos importantes em um SGBD Rotinas Armazenadas incluem Gatilhos Procedimentos Banco de Dados Prof Daniel Callegari Aula 08 Relembrando o conteúdo do vídeo anterior Existem vários outros objetos em SGBDs Visões e Rotinas Armazenadas são objetos importantes em um SGBD Rotinas Armazenadas incluem Gatilhos Procedimentos O que você vai aprender nessa aula Parte 1 Outros objetos de SGBDs Parte 2 Visões views Parte 3 Visões e atualizações Parte 4 Rotinas armazenadas Parte 5 Dinâmica Dinâmica 1 Acrescentar aos médicos uma indicação sobre se estão ativos ou aposentados inativos 2 Criar também uma visão com o crm o nome e o código da especialidade dos médicos ativos 3 Em seguida criar um mecanismo para garantir que somente médicos ativos possam solicitar novos exames 4 Depois experimentar Para um médico ativo criar uma nova ficha e solicitar dois exames disponíveis quaisquer Marcar esse médico como aposentado e tentar repetir a ação anterior Resumo do que vimos até agora Dentro de um SGBD existem vários objetos e não apenas Tabelas de dados Visões Views Sequências Sequences Gatilhos Triggers Procedimentos Procedures Usuários Users Papéis Roles Funções Functions etc etc Para Saber Mais Consultar PUGA S FRANÇA E GOYA M Banco de Dados implementação em SQL PLSQL e Oracle 11g São Paulo Pearson 2014 332p Capítulo 10 Documentação e Tutoriais do SGBD Oracle httpsdocsoraclecomendatabaseoracleoracledatabase19lnplstochtm httpswwworaclecombrdatabasetechnologiesappdevplsqlhtml