Banco de Dados II

BANCO DE DADOS II Professor Me William Roberto Pelissari Professor Esp Carlos Danilo Luz Professor Esp Jeferson Kaiser GRADUAÇÃO Unicesumar C397 CENTRO UNIVERSITÁRIO DE MARINGÁ Núcleo de Educação a Distância PELISSARI William Roberto LUZ Carlos Danilo KAISER Jeferson Banco de Dados II William Roberto Pelissari Carlos Danilo Luz Jeferson Kaiser MaringáPr UniCesumar 2018 Reimpresso em 2021 171 p Graduação EaD 1 Banco 2 Dados 3 EaD I Título ISBN 9788545907077 CDD 22 ed 00575 CIP NBR 12899 AACR2 Ficha catalográfica elaborada pelo bibliotecário João Vivaldo de Souza CRB8 6828 Impresso por Reitor Wilson de Matos Silva ViceReitor Wilson de Matos Silva Filho PróReitor Executivo de EAD William Victor Kendrick de Matos Silva PróReitor de Ensino de EAD Janes Fidélis Tomelin Presidente da Mantenedora Cláudio Ferdinandi NEAD Núcleo de Educação a Distância Diretoria Executiva Chrystiano Mincof James Prestes Tiago Stachon Diretoria de Graduação e Pósgraduação Kátia Coelho Diretoria de Permanência Leonardo Spaine Diretoria de Design Educacional Débora Leite Head de Produção de Conteúdos Celso Luiz Braga de Souza Filho Head de Curadoria e Inovação Jorge Luiz Vargas Prudencio de Barros Pires Gerência de Produção de Conteúdo Diogo Ribeiro Garcia Gerência de Projetos Especiais Daniel Fuverki Hey Gerência de Processos Acadêmicos Taessa Penha Shiraishi Vieira Gerência de Curadoria Giovana Costa Alfredo Supervisão do Núcleo de Produção de Materiais Nádila Toledo Supervisão Operacional de Ensino Luiz Arthur Sanglard Coordenador de Conteúdo Fabiana de Lima Qualidade Editorial e Textual Daniel F Hey Hellyery Agda Design Educacional Isabela Ventura Ana Claudia Salvadego Agnaldo Ventura Iconografia Isabela Soares Silva Projeto Gráfico Jaime de Marchi Junior José Jhonny Coelho Arte Capa Arthur Cantareli Silva Editoração Robson Yuiti Saito Revisão Textual Pedro Afonso Barth Ilustração Bruno Cesar Pardinho Viver e trabalhar em uma sociedade global é um grande desafio para todos os cidadãos A busca por tecnologia informação conhecimento de qualidade novas habilidades para liderança e so lução de problemas com eficiência tornouse uma questão de sobrevivência no mundo do trabalho Cada um de nós tem uma grande responsabilida de as escolhas que fizermos por nós e pelos nos sos farão grande diferença no futuro Com essa visão o Centro Universitário Cesumar assume o compromisso de democratizar o conhe cimento por meio de alta tecnologia e contribuir para o futuro dos brasileiros No cumprimento de sua missão promover a educação de qualidade nas diferentes áreas do conhecimento formando profissionais cidadãos que contribuam para o desenvolvimento de uma sociedade justa e solidária o Centro Universi tário Cesumar busca a integração do ensinopes quisaextensão com as demandas institucionais e sociais a realização de uma prática acadêmica que contribua para o desenvolvimento da consci ência social e política e por fim a democratização do conhecimento acadêmico com a articulação e a integração com a sociedade Diante disso o Centro Universitário Cesumar al meja ser reconhecido como uma instituição uni versitária de referência regional e nacional pela qualidade e compromisso do corpo docente aquisição de competências institucionais para o desenvolvimento de linhas de pesquisa con solidação da extensão universitária qualidade da oferta dos ensinos presencial e a distância bemestar e satisfação da comunidade interna qualidade da gestão acadêmica e administrati va compromisso social de inclusão processos de cooperação e parceria com o mundo do trabalho como também pelo compromisso e relaciona mento permanente com os egressos incentivan do a educação continuada Seja bemvindoa caroa acadêmicoa Você está iniciando um processo de transformação pois quando investimos em nossa formação seja ela pessoal ou profissional nos transformamos e consequentemente transformamos também a sociedade na qual estamos inseridos De que forma o fazemos Criando oportu nidades eou estabelecendo mudanças capazes de alcançar um nível de desenvolvimento compatível com os desafios que surgem no mundo contemporâneo O Centro Universitário Cesumar mediante o Núcleo de Educação a Distância oa acompanhará durante todo este processo pois conforme Freire 1996 Os homens se educam juntos na transformação do mundo Os materiais produzidos oferecem linguagem dialógica e encontramse integrados à proposta pedagógica con tribuindo no processo educacional complementando sua formação profissional desenvolvendo competên cias e habilidades e aplicando conceitos teóricos em situação de realidade de maneira a inserilo no mercado de trabalho Ou seja estes materiais têm como principal objetivo provocar uma aproximação entre você e o conteúdo desta forma possibilita o desenvolvimento da autonomia em busca dos conhecimentos necessá rios para a sua formação pessoal e profissional Portanto nossa distância nesse processo de cresci mento e construção do conhecimento deve ser apenas geográfica Utilize os diversos recursos pedagógicos que o Centro Universitário Cesumar lhe possibilita Ou seja acesse regularmente o AVA Ambiente Virtual de Aprendizagem interaja nos fóruns e enquetes assista às aulas ao vivo e participe das discussões Além dis so lembrese que existe uma equipe de professores e tutores que se encontra disponível para sanar suas dúvidas e auxiliáloa em seu processo de aprendiza gem possibilitandolhe trilhar com tranquilidade e segurança sua trajetória acadêmica AUTORES Professor Esp Jeferson Kaiser Especialista em Administração de Banco de Dados OracleDB2 pelo Centro de Ensino Superior Cesumar UNICESUMAR2015 e em Contabilidade Gerencial Controladoria e Auditoria pela Faculdade de Jandaia do Sul FAFIJAN2013 É graduado em Ciência da Computação pela Faculdade de Filosofia Ciências e Letras de Mandaguari FAFIMAN2012 Atualmente é professor mediador do Centro Universitário de Maringá e analista de sistemas jr da empresa Matera Systems Tem experiência na área de Ciência da Computação com ênfase em Arquitetura de Sistemas de Computação Para informações mais detalhadas sobre sua atuação profissional pesquisas e publicações acesse seu currículo disponível no endereço a seguir httplattescnpqbr1926432986663027 Professor Me William Roberto Pelissari Mestre em Desenvolvimento de Tecnologia pelo Instituto de Tecnologia para o Desenvolvimento Lactec2014 Especialista em Aplicação para Internet e Dispositivos Móveis pela Universidade Paranaense UNIPAR2014 Especialista em Administração de Produção e Logística pela Faculdade Estadual de Educação Ciências e Letras de Paranavaí FAFIPA2010 Especialista em Engenharia de Software pela Universidade Norte do Paraná UNOPAR1997 Graduado em Tecnologia em Processamento de Dados pela Unopar 1995 Graduação em andamento em Processos Gerenciais pela Unipar Atualmente atua na Gestão Educacional da Virtual Age by TOTVS É professor e coordenador da pósgraduação da Unipar e professor no Ensino a Distância EAD do Centro Universitário Cesumar Unicesumar Tem experiência na área de Ciência da Computação com ênfase em Projetos de Software Engenharia de Software Análise e Desenvolvimento de Software e Gerência Governança e Infraestrutura de TI Para informações mais detalhadas sobre sua atuação profissional pesquisas e publicações acesse seu currículo disponível no endereço a seguir httplattescnpqbr1381263791825712 Professor Esp Carlos Danilo Luz Especialista em Educação a Distância EAD e as Tecnologias Educacionais pelo Centro Universitário Cesumar UniCesumar2016 Graduado em Redes de Computadores pelo UniCesumar 2009 Especializações em andamento de Gestão Estratégica de Pessoas e Docência no Ensino Superior pela mesma instituição Atualmente é professor da Secretaria de Educação do Estado do Paraná Experiência de 9 anos em Desenvolvimento de Sistemas Web e Projetos Para informações mais detalhadas sobre sua atuação profissional pesquisas e publicações acesse seu currículo disponível no endereço a seguir httplattescnpqbr7063667454769099 SEJA BEMVINDOA Caro a aluno a seja bemvindo a Este material visa permitir que você possa compreender diversos conceitos avançados em Banco de Dados Nas unidades serão abordadas desde funcionalidades específicas até controles de permissões Sendo assim um Administrador de Banco de Dados deve ter o conhecimento relacionado à estrutura e também conhecimento de comandos para a manipulação de um Sistema de Gerenciamento de Banco de Dados SGBD Os conceitos aplicados neste material têm como base o Banco de Dados Oracle e MyS QL por serem considerados grandes líderes de mercado neste segmento As unidades que serão abordadas neste livro têm como intuito preparar você alunoa para se tornar um profissional apto a manipular as informações em um SGBD Será apre sentada na Unidade I a linguagem Structured Query Language SQL a mesma é consi derada como a linguagem padrão dos SGBDs que tem como base o modelo de dados relacional além de funcionalidades que irão proporcionar a você alunoa trabalhar com o banco de dados Na Unidade II vamos conhecer melhor os comandos SQL utilizando o SGBD MySQL Sendo assim iremos trabalhar com a manipulação de funções SQL distribuídas da se guinte forma criação de uma base de dados criação e alteração de tabelas inserção e alteração de dados realização de consultas além de estudarmos sobre controle de dados e transações Como sequência a unidade III aborda comandos avançados em SQL assim explorando melhor as funcionalidades de um banco de dados Por sua vez a unidade IV foi elabora da pensando em proporcionar a você alunoa conhecimentos sobre o Procedural Lan guageStructured Query Language PLSQL utilizando o SGBD Oracle vamos também nos aprofundar nos conceitos e exemplos de Procedures Funções e Pacotes Por fim na Unidade V iremos trabalhar com Triggers na qual vamos abrir um grande leque de situações a serem trabalhadas em um Banco de Dados Além disso devemos focar nossos olhos na segurança das informações sabendo que elas são consideradas a alma de uma empresa Com base nessa informação tornase necessário termos um controle de acesso às informações de nosso Banco de Dados Desejamos um bom proveito do material e ótimo estudo APRESENTAÇÃO BANCO DE DADOS II SUMÁRIO 09 UNIDADE I CRIANDO UM BANCO DE DADOS 15 Introdução 16 Schema 20 Tipos de Dados 22 Criação e Alteração de Tabelas 25 Chave Primária 28 Populando as Tabelas Criadas 31 Comando Describe 33 Considerações Finais 37 Referências 38 Gabarito UNIDADE II A LINGUAGEM SQL 41 Introdução 42 História da SQL 44 DQL Linguagem de Consulta de Dados 49 DML Linguagem de Manipulação de Dados 51 DDL Linguagem de Definição de Dados 56 DCL Linguagem de Transação de Dados 59 DTL Linguagem de Transação de Dados SUMÁRIO 10 61 Considerações Finais 65 Referências 66 Gabarito UNIDADE III MANIPULAÇÃO DE DADOS 69 Introdução 70 Extrair Dados de uma Tabela 75 Agrupando a Exibição dos Dados 76 Ordenação na Exibição dos Dados 77 Criando Consultas com Filtros Específicos 79 Valores Nulos 79 Rename 81 Consulta Utilizando Mais de uma Tabela 82 Consultas com Subqueries 83 Teste de Relações Vazias 84 Alterando os Dados com Update 86 Removendo os Dados com Delete 87 Rollback e Commit 88 Truncate e Drop SUMÁRIO 11 89 Considerações Finais 93 Referências 94 Gabarito UNIDADE IV PROGRAMAÇÃO EM SQL 99 Introdução 100 Visão Geral Sobre PLSQL 104 Procedures 119 Functions 124 Packages 130 Considerações Finais 137 Referências 138 Gabarito SUMÁRIO 12 UNIDADE V CONTROLANDO ACESSOS 143 Introdução 144 Triggers Gatilhos 149 Segurança do Banco 150 Controle de Acesso ao Usuário 156 Criar e Acessar Vínculos de Banco de Dados 158 Gerenciando Senhas e Recursos 161 Gerenciando Usuários 162 Auditoria 164 Considerações Finais 169 Referências 170 Gabarito 171 CONCLUSÃO UNIDADE I Professor Esp Jeferson Kaiser CRIANDO UM BANCO DE DADOS Objetivos de Aprendizagem Conhecer como se cria um Banco de Dados Compreender a Criação e alteração de tabelas na prática Verificar quais tipos de dados utilizar nos campos das tabelas Conhecer chave primária na prática Inserir dados nas tabelas Plano de Estudo A seguir apresentamse os tópicos que você estudará nesta unidade Schema Tipos de Dados Criação e Alteração de Tabelas Chave Primária Populando as Tabelas Criadas Comando Describe INTRODUÇÃO Olá caro a alunoa Nesta unidade iremos abordar a criação de um Banco de Dados no MySQL ilustrando o passo a passo para a criação de um Banco de Dados de fato criação com a alteração e remoção das tabelas Iremos também popular as tabelas criadas Nossa intenção é fazer com que você acadêmico a verifique na prática a manipulação desde a criação de um schema até popular dados nas tabelas Alertamos que neste momento não é necessário a preocupação com a consulta de dados pois esse assunto será abor dado na próxima unidade Vamos praticar pois com a prática você irá entender melhor todos os conceitos vistos até o momento Logo quando falamos em Banco de Dados a primeira coisa que nos vêm à cabeça é a linguagem Structured Query Language SQL Essa linguagem é muito madura e instável no mercado de Banco de Dados Fique muito atento com os detalhes dos exemplos de comandos trabalhados nesta unidade pois um pequeno detalhe fará com que seu comando não execute corretamente na ferramenta que iremos utilizar Assim você será impossibilitado de concluir todos os comandos Nossa ideia para este livro é que se você executar todos os passos das unida des será criado um cadastro de pessoa completo contendo dados para que seja possível você colocar em prática todos os assuntos abordados no livro Ótimo estudo Introdução Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 15 CRIANDO UM BANCO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 I U N I D A D E 16 SCHEMA Antes do SQL92 não existia o conceito de schema em que todas as tabelas e demais arquivos do Banco de Dados eram criados dentro de um mesmo ambiente não existindo assim um agrupamento de tabelas Um schema é representado por uma coleção de vários objetos de um ou mais usuários de Banco de Dados como exemplo tabelas sequências índices etc São associados a um Banco de Dados em razão de vários esquemas para um Banco de Dados facilitando a adminis tração dos objetos e dos dados O comando a seguir é responsável pela criação de um schema CREATE SCHEMA UniCesumar Em que UniCesumar é o nome do schema a ser criado pelo comando Uma das principais vantagens da utilização de schemas em Banco de Dados são as per missões que se pode atribuir e revogar ao usuário pois podemos dessa maneira ter vários schemas e vários usuários em um Banco de dados Entretanto deter minados usuários têm acesso a somente um schema no Banco de Dados Schema Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 17 BANCO DE DADOS MYSQL O MySQL é um Sistema de Gerenciamento de Banco de Dados SGBD que uti liza a linguagem SQL como interface com o usuário É também um dos Bancos de Dados mais populares para a Web É rápido confiável e fácil de utilizar Em todo o nosso desenvolvimento vamos utilizar o MySQL Workbench que é a ferramenta oficial do MySQL É um ambiente completo que permite além de realizar consultas criar diagramas e trabalhar com engenharia reversa Com o Workbench devidamente instalado chegou a hora de nos conectar mos à base de dados e criamos um novo schema para que possamos a partir dele criar as nossas tabelas de uma forma mais organizada Para que seja possível a criação deveremos efetuar o login na base de dados e após estarmos dentro do Workbench devemos clicar no botão create new schema conforme a Figura 1 Figura 1 Workbench Fonte os autores Após clicar neste botão será possível darmos um nome ao nosso novo schema de dados conforme a Figura 2 Figura 2 Nome do Schema Fonte os autores Conforme vemos o nome do nosso novo schema será UniCesumar vale lembrar que essa notação será utilizada em todo o nosso desenvolvimento Para que seja criado de fato esse novo schema será necessário clicar em Apply em que será mos trado uma tela com o comando SQL a ser aplicado no Banco de Dados para a criação CRIANDO UM BANCO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 I U N I D A D E 18 Figura 3 Tela com o comando SQL Fonte os autores Dessa maneira basta clicar em Apply e depois em Finish para que o SGBD aplique o comando no Banco de Dados e crie o nosso novo schema que pode ser consul tado por meio dos schemas presentes do lado esquerdo inferior no Workbench conforme a Figura 4 Figura 4 Workbench Fonte os autores A consulta aos schemas criados dentro do Banco de Dados pode acontecer de várias formas Uma forma bem simples de verificar a criação do schema seria o Schema Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 19 seguinte comando Show schemas Esse comando deverá ser executado com script SQL no Workbench para que seja aberto devemos clicar no botão logo abaixo de File Após o editor de SQL aberto basta digitar o comando e clicar no botão para que o Workbench exe cute os comandos após a execução Os dados serão apresentados logo a seguir conforme vemos na Figura 5 Figura 5 Exemplo da execução Fonte os autores Dessa forma caro a aluno a você pode se perguntar do que se trata a ferra menta Workbench Ela é uma interface gráfica que traduz comandos feitos pelo usuário em linguagem SQL para que assim seja possível sua aplicação no Banco de Dados Todas as ações feitas dentro do workbench podem ser substituídas de fato por comandos via terminal no prompt do dos por exemplo CRIANDO UM BANCO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 I U N I D A D E 20 TIPOS DE DADOS Para que possamos efetuar a criação de uma tabela primeiro necessitamos saber mais um pouco sobre os tipos de dados Em seguida identificar os principais tipos de dados do MySQL que será a ferramenta adotada para o desenvolvi mento de todo o nosso conteúdo Conforme Passos 2010 online1 Alguns campos numéricos possuem a opção UNSIGNED Isso quer dizer que o número não pode ser negativo Por exemplo o campo TINYINT com a opção UNSIGNED vai de 0 até 255 sem a opção UN SIGNED vai de 128 até 127 A opção ZEROFILL completa o campo com zeros Por exemplo se você cadastrar 65 em um campo INT5 ou seja inteiro com 5 dígitos com a opção ZEROFILL habilitada o MySQL irá cadastrar 00065 Sem a opção ZEROFILL habilitada o MySQL irá cadastrar 65 Tipos de Dados Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 21 Tabela 1 Tipos numéricos Fonte adaptado de Passos 2010 online1 CRIANDO UM BANCO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 I U N I D A D E 22 CRIAÇÃO E ALTERAÇÃO DE TABELAS O comando para a criação de tabe las é um comando Data Definition Language DDL Dessa forma ire mos trabalhar com as tabelas de dados formando um cadastro de pessoa assim podemos representar graficamente conforme a Figura 6 Ranking de sistemas de bancos de dados mais usados em 20152016 Sabemos dos inúmeros Bancos de Dados e seus SGBDs existentes no merca do mas sempre vem aquela curiosidade em saber qual é o Banco de Dados mais utilizado Para matar essa curiosidade a DBEngines criou esse ranking DBEngines é uma iniciativa de recolher e apresentar informações sobre os sistemas de gerenciamento de Banco de Dados O Ranking DBEngines é uma lista de SGBDs classificados por sua atual po pularidade A lista é atualizada mensalmente As propriedades mais impor tantes de numerosos sistemas são mostrados na visão geral de sistemas de gestão de Banco de Dados Você pode examinar as propriedades de cada sistema e pode comparálos lado a lado Os termos e conceitos sobre Banco de Dados são explicados na enciclopédia acessível no site DBEngines foi criada e é mantida por solid IT Fonte adaptado de DBEngines 2016 online2 Criação e Alteração de Tabelas Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 23 Figura 6 Comandos para criação de tabelas Fonte os autores De acordo com a figura vamos ter em nossa base de dados um cadastro de pessoas que poderão ter vários endereços e vários telefones No caso de você alunoa sugiro que siga todos os passos deste livro utili zando o MySQL pois no final estaremos todos com os mesmos dados no Banco de Dados Nosso próximo passo agora é a criação e a alteração das tabelas em nosso Banco de Dados A criação de tabelas se dá a um comando DDL Para evidenciar melhor na prática como é de fato a criação e a alteração de tabelas em um Banco de Dados os comandos a seguir podem ser executados de fato em seu Banco de Dados a fim de que nossa disciplina fique mais prática A sequência de coman dos a seguir irá criar em nossa base de dados uma tabela chamada pessoa a qual estará ligada com outras duas tabelas endereços e telefones Seguindo o raciocí nio nossa intenção é ter uma pessoa cadastrada em nossa base que possa conter vários endereços e vários telefones CRIANDO UM BANCO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 I U N I D A D E 24 Criação da tabela pessoa CREATE TABLE unicesumarpessoa ꞌidpessoa INT NOT NULL nome VARCHAR200 NOT NULL sobrenome VARCHAR200 NOT NULL datanascimento DATE NULL Criação da tabela endereço CREATE TABLE unicesumarendereco idendereco INT NOT NULL cep INT8 NOT NULL logradouro VARCHAR200 NULL numero VARCHAR20 NULL compemento VARCHAR200 NULL Criação da tabela telefone CREATE TABLE unicesumartelefone idtelefone INT NOT NULL numero BIGINT12 NOT NULL Chave Primária Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 25 CHAVE PRIMÁRIA As criações de nossas tabelas foram feitas sem a identificação de uma chave primá ria a função da chave primária em uma tabela no Banco de Dados é a de que nunca haverá a repetição de um mesmo valor para esse campo Visto tal conceito iremos alterar as nossas tabelas modificando os campos que iniciam com id para que sejam nossas chaves primárias Alteração da tabela pessoa ALTER TABLE unicesumarpessoa ADD PRIMARY KEY idpessoa Alteração da tabela endereço ALTER TABLE unicesumarendereco ADD PRIMARY KEY idendereco Alteração da tabela telefone ALTER TABLE unicesumartelefone ADD PRIMARY KEY idtelefone CRIANDO UM BANCO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 I U N I D A D E 26 Agora com os comandos executados em seu Banco de Dados temos as três tabe las devidamente criadas mas sem dependência entre elas Ou seja todas estão distintas no Banco de Dados Para solucionar esses problemas e trabalharmos melhor com a integridade dos dados iremos criar mais uma coluna idpes soa nas tabelas de endereço e telefone As tabelas mencionadas irão fazer parte da criação de nossa foreign key sendo referenciadas pelo campo identificador da tabela de pessoa Alteração da tabela de endereço ALTER TABLE unicesumarendereco ADD COLUMN idpessoa INT NOT NULL Alteração da tabela de telefone ALTER TABLE unicesumartelefone ADD COLUMN idpessoa INT NOT NULL Agora com as tabelas prontas podemos criar referências entre as tabelas ende reço e telefone com a tabela de pessoa Para isso devemos efetuar a criação da foreign key na tabela de endereço e telefone referenciando a tabela de pessoa Criação da foreign key na tabela de endereço ALTER TABLE unicesumarendereco ADD CONSTRAINT pessoafkendereco FOREIGN KEY idpessoa REFERENCES unicesumarpessoa idpessoa Chave Primária Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 27 Criação de foreign key na tabela de telefone ALTER TABLE unicesumartelefone ADD CONSTRAINT pessoafktelefone FOREIGN KEY idpessoa REFERENCES unicesumarpessoa idpessoa Dessa maneira estamos garantindo a integridade de nosso Banco de Dados a fim de que todas as ligações tenham efeito Antes de todas as alterações nas tabelas tínhamos as tabelas distribuídas em nosso Banco de Dados conforme a Figura 7 Figura 7 Exemplo Fonte os autores Após todas as alterações aplicadas em nossa base de dados criando assim a integridade em nossos dados eles que antes não tinham nenhum tipo de ligação agora formam o Modelo Entidade Relacionamento MER da Figura 8 Figura 8 MER Fonte os autores CRIANDO UM BANCO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 I U N I D A D E 28 POPULANDO AS TABELAS CRIADAS O comando INSERT faz parte dos coman dos de Data Manipulation Language DML Nesta etapa iremos popular nossas tabelas de acordo com a criação e respeitando a integri dade dos dados Sempre que gravamos dados em tabelas devemos obedecer a ordem hie rárquica delas Veremos a seguir o que seria essa ordem Para inserir os dados nas tabelas vamos iniciar pela tabela de pessoa em que iremos inserir um registro somente INSERT INTO ꞌunicesumarpessoa ꞌidpessoa nome sobrenome datanascimento VALUES 1 campo idpessoa João campo nome Pereira campo sobrenome 19830915 campo datanascimento Com os dados inseridos em nossa base na tabela de pessoa agora iremos adi cionar dois endereços referenciando essa pessoa cadastrada Com a integridade entre as tabelas criadas adequadamente podemos ter vários endereços para uma única pessoa e um endereço poderá estar ligado a apenas uma pessoa Temos assim uma cardinalidade de N1 partindo da tabela de pessoa Atenção no código a seguir temos comentários sobre os campos do código que estão entre Populando as Tabelas Criadas Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 29 Inserindo o endereço 1 INSERT INTO unicesumarendereco idendereco cep logradouro numero compemento idpessoa VALUES 1 campo idendereco 86890880 campo cep Av Paraná campo logradouro 27356 campo numero Casa campo complemento 1 campo idpessoa FK com a tabela de pessoa Inserindo o endereço 2 INSERT INTO unicesumarendereco idendereco cep logradouro numero compemento idpessoa VALUES 2 campo idendereco 86890880 campo cep Av Brasil campo logradouro 412 sala 2 campo numero Comerical campo complemento 1 campo idpessoa FK com a tabela de pessoa CRIANDO UM BANCO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 I U N I D A D E 30 Agora nós já temos uma pessoa em nossa base que possui dois endereços Por final iremos inserir os telefones pertencentes a essa pessoa Inserindo o telefone 1 INSERT INTO unicesumartelefone idtelefone numero idpessoa VALUES 1 campo idtelefone 4499786543 campo numero 1 campo idpessoa FK com a tabela de pessoa Inserindo o telefone 2 INSERT INTO unicesumartelefone idtelefone numero idpessoa VALUES 2 campo idtelefone 4499786523 campo numero 1 campo idpessoa FK com a tabela de pessoa A partir dos dados inseridos nas tabelas podemos tirar algumas conclusões A pessoa por nome de João possui dois endereços um na Av Paraná e outra na Av Brasil sendo o primeiro o endereço de sua casa e outro de sua empresa Além disso ele possui 2 números de telefone Um forte elemento para a aprendizagem da linguagem SQL é a prática Desse modo sugiro a você que efetue mais inserções no Banco de Dados seguindo os comandos exemplificados anteriormente sempre lembrado de que não é possí vel ter um valor de uma chave primária repetida na tabela Comando Describe Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 31 COMANDO DESCRIBE É de extrema importância consultar a estrutura das tabelas já criadas em um Banco de Dados Essa consulta é efetuada por meio do comando describe Com ele é possível visualizar as colunas e os tipos de colunas de uma tabela em específico Para o uso deste comando é necessário efetuar a interpretação dos dados retornados pois será a partir desses dados que você saberá tudo sobre a tabela Destes dados retornados podemos evidenciar colunas de índices NOT NULL NULL PRIMARY KEY quais os campos compõem a PRIMARY KEY chave primária Também qual é o valor default padrão para determinada coluna se a PRIMARY KEY é auto incrementada pelo próprio Banco de Dados Vale lem brar que o comando DESC é um sinônimo do comando DESCRIBE Para utilizar esse comando devemos obedecer a sintaxe a seguir DESCRIBE nomedatabela Vamos exemplificar esse comando com a tabela pessoa a qual já criamos nesta mesma unidade O comando a ser executado deverá ser describe pessoa CRIANDO UM BANCO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 I U N I D A D E 32 O retorno dos dados para esse comando deverá ser uma linha para cada coluna da tabela conforme Figura 9 FIELD TYPE NULL KEY DEFAULT EXTRA idpessoa int11 NO PRI NULL nome varchar200 NO NULL sobrenome varchar200 NO NULL datanascimento date YES NULL Figura 9 Modelo de uma linha para cada coluna Fonte os autores As informações mais importantes sobre o retorno desse comando são Field essa informação representa o nome das colunas presentes na tabela do Banco de Dados Type indica o tipo de dado de cada coluna da tabela Null o conceito é bem simples indica a obrigatoriedade da coluna ou seja se ela aceita valores nulos representados pelo retorno YES e NO Como no exem plo anterior apenas o campo datanascimento poderá ser nulo os demais são campos obrigatórios KEY esse indicador poderá retornar apenas 3 valores PRI UNI MUL PRI indica que a coluna é uma chave primária da tabela ou faz parte da composição juntamente com outros campos caso seja uma chave pri mária composta o valor PRI estará presente em mais campos da tabela referenciadas na coluna KEY UNI é um campo UNIQUE NOT NULL esse tipo de campo é muito parecido com a chave primária da tabela pois toda chave primária da tabela não pode ser nula e deve ser única Esse conceito de UNI é utilizado para que não existam repetições de valores nesse campo presente na tabela MUL é a definição de uma coluna que é ou é parte de um índice não único que aceitam valores múltiplos e NULL Considerações Finais Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 33 DEFAULT é utilizado para que seja possível o Banco de Dados inserir um valor padrão caso esse valor não seja informado no momento do insert Apesar de um comando muito simples o DESCRIBE é um dos recursos dis poníveis mais importantes do MySQL Por isso conhecêlo e saber interpretar os seus resultados é de extrema importância No dia a dia dos programadores de Banco de Dados esse é um dos comandos mais utilizados CONSIDERAÇÕES FINAIS Prezadoa alunoa Nesta unidade procuramos demonstrar na prática a uti lidade dos conceitos até então abordados Trabalhamos na prática os comando DDL e DML Espero que essa prática tenha evidenciado a você como é praze roso trabalhar com Banco de Dados compreendendo os princípios teóricos que abrangem este grande mercado de trabalho Dessa maneira procuramos exem plos da prática Sugiro que caso não tenha seguidos os passos execute como exercício cada comando destacado em itálico nos quadros desta unidade pois o passo a passo criará sua base de dados as tabelas e irá populálas Todos os conceitos abordados fazem parte do dia a dia de um programa dor e é essencial para o desenvolvimento de qualquer programa que trabalhe em conjunto com um Banco de Dados Nos vemos na próxima unidade até lá Um Banco de Dados que é elaborado sem levar em consideração as regras de negócio da empresa poderá passar por problemas futuramente 34 1 O comando alter table se encontra em qual definição a DML b DTL c DQL d DDL e DCL 2 Quais as vantagens da criação de um schema 3 O comando insert se encontra em qual definição a DML b DTL c DQL d DDL e DCL 4 Sobre os tipos de dados assinale verdadeiro ou falso para os itens relacionados abaixo DATETIME combinação de data e hora no formato YEAR ano com 4 dígitos LONGBLOB string com até 4Gb DECIMAL não armazena números inteiros a V V V F b V F V F c V V F F d F V F V e F F V V 5 Popule mais dados da tabela pessoa seguindo o item 5 desta unidade Material Complementar MATERIAL COMPLEMENTAR Projeto de Banco de Dados Vol4 2008 Carlos Alberto Heuser Editora Bookman Sinopse em sua sexta edição e adotado por faculdades de todo o Brasil Projeto de Banco de Dados aborda as duas primeiras etapas do ciclo de vida de um Banco de Dados modelagem conceitual e projeto lógico REFERÊNCIAS REFERÊNCIAS REFERÊNCIAS ONLINE 1 Em httpblogtiagopassoscomauthoradminpage37 Acesso em 18 nov 2016 2 Em httpdbenginescomen Acesso em 18 nov 2016 36 REFERÊNCIAS 37 GABARITO 1 A 2 Agrupamento de vários objetos de um ou mais usuários de Banco de Dados como exemplo tabelas sequências índices etc São associados a um Banco de Dados na razão de vários esquemas para um Banco de Dados facilitando a admi nistração dos objetos e dos dados Principais vantagens da utilização de schemas em Banco de Dados são as permissões que se pode atribuir e revogar ao usuário Pois podemos dessa maneira ter vários schemas e vários usuários em um Banco de Dados mas determinados usuários terão acesso a somente um schema no Banco de Dados 3 A 4 A V V V F 5 INSERT INTO unicesumarpessoa idpessoa nome sobrenome datanascimento 6 VALUES select maxidpessoa 1 from pessoa Maria campo nome Pereira campo sobrenome 19870915 campo datanascimento UNIDADE II Professor Esp Jeferson Kaiser A LINGUAGEM SQL Objetivos de Aprendizagem Compreender cada divisão da linguagem SQL Importância e ligações entre as divisões da linguagem SQL Plano de Estudo A seguir apresentamse os tópicos que você estudará nesta unidade História da SQL Linguagem de Consulta de Dados DQL Linguagem de Manipulação de Dados DML Linguagem de Definição de Dados DDL Linguagem de Controle de Dados DCL Linguagem de Transação de Dados DTL Introdução Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 41 INTRODUÇÃO Olá caroa alunoa Nesta unidade serão descritas as linguagens formais que irão proporcionar uma notação concreta para o desenvolvimento de consultas alterações e remoções em um Banco de Dados Contudo os Banco de Dados comerciais necessariamente precisam de uma linguagem fácil para o usuário Nesta unidade do livro iremos trazer para você os principais conceitos da linguagem SQL A sigla SQL que vem do inglês Structured Query Language Linguagem de Consulta Estruturada Embora estamos falando da linguagem SQL como uma linguagem de con sulta de dados ela tem várias outras funções Ela também é utilizada para a definição da estrutura de dados modificações do Banco de Dados especifica ções de segurança dentre outras Neste livro não temos a intenção de passar detalhadamente tudo sobre SQL para você vamos apenas trabalhar os construtores e os principais conceitos da lin guagem SQL Pois essa linguagem certamente é a linguagem padrão dos Bancos de Dados relacionais devido a sua simplicidade e facilidade de uso A SQL é a linguagem de mais alto nível para a manipulação de dados dentro do modelo relacional SILBERSCHATZ KORTH SUDARSHAN 1999 Bom estudo A LINGUAGEM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 II U N I D A D E 42 HISTÓRIA DA SQL Tudo começou no início dos anos 70 por meio de uma pesquisa da empresa International Business Machine IBM com o Dr E F Codd o qual levou ao desen volvimento de um produto chamado SEQUEL ou Linguagem de Consulta em Inglês Estruturado posteriormente a SEQUEL veio a se transformar na SQL ou Linguagem de Consulta Estruturada A IBM e os demais fornecedores de Banco de Dados relacionais querem padronizar a forma de acessar e manipular seus dados em um Banco de Dados relacional Embora a IBM tenha sido a primeira a desenvolver esse tipo de tec nologia foi a Oracle quem lançou comercialmente o Banco de Dados relacional SQL COMO UMA LINGUAGEM PADRÃO Um grande padrão de Banco de Dados é a linguagem SQL decorrente de sua sim plicidade e facilidade de uso Diferenciase das demais linguagens de consulta de Banco de Dados no sentido em que se é especificado a forma do resultado sem ter nenhum tipo de preocupação com o percurso percorrido para se chegar ao resultado A SQL tem o seu ciclo de aprendizado menor que as demais lingua gens de programação pois ela é uma linguagem declarativa o que se opõem às demais linguagens de programação procedurais Embora a SQL tenha sido criada pela IBM ocorreram várias derivações dessa linguagem criadas por outros autores Visto o crescimento da linguagem História da Sql Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 43 foi necessário a criação de uma padronização para a linguagem Essa tarefa ficou a cargo do American National Standards Institute ANSI no ano de 1986 e pela International Organization for Standards ISO em 1987 A SQL foi revista em 1992 e essa versão recebeu o nome de SQL92 revisto novamente em 1999 levando o nome de SQL99 ou também chamado de SQL3 Embora existam essas padro nizações ANSI e ISO ela ainda possui variações e extensões produzidas pelos diferentes fabricantes de Sistema de Gerenciamento de Banco de Dados SGBD A linguagem SQL pode ser definida em várias partes DML Data Manipulation Language Linguagem de Manipulação de Dados Esses comandos são utilizados para realizar inclusões exclu sões e alterações de dados os quais são utilizados a partir dos comandos INSERT UPDATE e DELETE WATSON RAMKLASS 2012 DDL Data Definition Language Linguagem de Definição de Dados Conjunto de comandos dentro da SQL que permitem ao desenvolvedor utilizálas para a definição das estruturas de dados contendo instruções as quais permitem a criação modificação e remoção de tabelas bem como criação alteração e remoção de elementos associados às tabelas SILBERSCHATZ KORTH SUDARSHAN 1999 DCL Data Control Language Linguagem de Controle de Dados São os comandos que permitem ao administrador do Banco de Dados geren ciar as autorizações dos dados e licenças dos usuários para controlar o acesso de quem pode ver ou manipular dados dentro do banco de dados Os comandos mais utilizados são GRANT REVOKE SET e LOCK DTL Data Transaction Language Linguagem de Transação de Dados São os comandos utilizados para gerenciar as mudanças feitas nos dados do Banco de Dados através de um comando DML ele permite que as ins truções sejam agrupadas por transações Os principais comandos são ROLLBACK COMMIT e SAVEPOINT DQL Doctrine Query Language Linguagem de Consulta de Dados Embora com apenas um comando se não o mais importantes de todos os comandos utilizados na SQL O SELECT permite ao usuário efetuar uma consulta no Banco de Dados Esse comando é composto de várias cláusulas e opções possibilitando das consultas mais simples às consul tas mais complexas DOCPLAYER online 20161 A LINGUAGEM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 II U N I D A D E 44 DQL LINGUAGEM DE CONSULTA DE DADOS A estrutura de um select simples consiste em três cláusulas select from e where Cláusula SELECT é responsável por relacionar os atributos desejados no resultado de uma consulta ou seja ele permite recuperar os dados de uma tabela do Banco de Dados Esse comando corresponde à operação da álgebra relacional Cláusula FROM é obrigatória em toda instrução select pois é ela a res ponsável por associar as relações que serão pesquisadas durante a evolução da expressão é ela a responsável por ligar o campo que iremos recuperar no select com a tabela no Banco de Dados Essa cláusula corresponde à operação de produto cartesiano da álgebra relacional Cláusula WHERE é responsável por estabelecer uma condição de pes quisa ou seja tem a função de filtrar os dados a serem recuperados do Banco de Dados A cláusula where também é utilizada nos comandos de UPDATE DELETE Ela corresponde à seleção do predicado da álgebra relacional Dql Linguagem de Consulta de Dados Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 45 CLÁUSULA SELECT A declaração SELECT é um mecanismo extremamente elegante fácil de ser trabalhado e altamente extensível O mecanismo foi criado com o intuito de recu perar os dados existentes no Banco de Dados afinal de contas não teria lógica ter dados gravados em um Banco de Dados se não fosse possível fazer a leitura deles As cláusulas do SELECT vem do inglês e se pensarmos a partir da tradu ção ficará mais simples ainda a compreensão As cláusulas são SELECT em português selecionar FROM em português a partir de WHERE em português onde Os dados retornados por uma consulta SQL são naturalmente uma relação pensando em uma consulta simples Segue o exemplo de um SELECT simples SELECT nomepessoa FROM pessoa A partir desta consulta iremos encontrar o nome de todas as pessoas da tabela pessoa do Banco de Dados Esse resultado é uma relação de um campo simples titulado de nomepessoa na tabela pessoa A linguagem SQL em sua maioria permite duplicidade em suas relações podendo trazer assim dados duplicados no resultado de sua consulta no caso para forçarmos a eliminação dos resulta dos duplicados utilizamos a instrução distinct depois da cláusula select e que se reescrevermos a consulta anterior ficará da seguinte maneira SELECT DISTINCT nomepessoa FROM pessoa A LINGUAGEM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 II U N I D A D E 46 No mesmo formato anterior também é possível a utilização do asterisco Ele é usado para denotar que o select deverá trazer todos os campos disponíveis para consulta A consulta com o uso do asterisco ficará da seguinte maneira SELECT DISTINCT FROM pessoa Dessa maneira estamos solicitando ao Banco de Dados que seja apresentado todos os campos disponíveis na tabela pessoa e que não possua dados repetidos WHERE O WHERE é utilizado para delimitar os dados a serem retornados pela nossa consulta os filtros a serem aplicados se restringem às linhas utilizando opera dores de comparação em um conjunto de campos e valores literários Os filtros trabalham com os operadores booleanos que fornecem um mecanismo para especificar condições múltiplas para filtrar as linhas a serem retornadas Segue o exemplo de como ficaria a implementação do where na consulta simples SELECT FROM pessoa WHERE codigopessoa 1 SELECT FROM pessoa WHERE nomepessoa ꞌJOAOꞌ Dql Linguagem de Consulta de Dados Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 47 O primeiro exemplo trata de uma consulta para trazer os dados da tabela pessoa desde que exista uma pessoa com o código da pessoa que contenha o número um No segundo exemplo o resultado depende extremamente do nome da pes soa ser JOAO Condições baseadas em Números As condições devem ser propriamente utilizadas de acordo com o tipo de dados a serem filtrados Vale lembrar que nas condições numéricas não é necessário colocar a restrição entre aspas simples Segue um quadro com os possíveis ope radores para as condições numéricas Quadro 1 Possíveis operadores para as condições numéricas OPERADOR DESCRIÇÃO Menor que Maior que Menor que ou igual a Maior que ou igual a Não igual Não igual Igual a Fonte os autores A partir desse quadro podemos elaborar os filtros que se aplicam a nossa necessidade Condições baseadas em Caracteres As condições determinantes para as linhas selecionadas baseadas em caracte res se dão pelo fato de delimitar os caracteres dentro de aspas únicas em que a não utilização das aspas gera um erro em sua execução Lembre que existe a distinção entre maiúsculos e minúsculos Exemplo where nome joão é dife rente de where nome João A LINGUAGEM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 II U N I D A D E 48 Condições baseadas em Data Os campos de tipo data são muito úteis para o armazenamento de datas e horas As datas quando utilizadas nos filtros devem seguir o mesmo padrão utilizados para os caracteres sendo delimitadas por aspas única FROM Finalmente vamos falar da cláusula FROM ela é responsável por fazer a ligação com a tabela que vamos efetuar a consulta Em resumo ela especifica as tabelas que possuem as colunas listadas na cláusula SELECT Por exemplo para trazer mos em uma consulta todos os dados da tabela pessoa teríamos que especificar a tabela pessoa na cláusula FROM do SELECT conforme exemplo a seguir SELECT FROM pessoa Nesse exemplo estamos trazendo todos os dados da tabela pessoa Dml Linguagem de Manipulação de Dados Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 49 DML LINGUAGEM DE MANIPULAÇÃO DE DADOS Caroa alunoa como vimos anteriormente DML é a Linguagem de manipu lação de dados e são os comandos dentro da linguagem SQL utilizados para a inclusão remoção e alteração de dados em um Banco de Dados Como já vimos os principais comandos são INSERT UPDATE e DELETE Segundo John Watson e Roopesh Ramklass 2009 a maioria dos profissio nais não incluem o select como sendo um comando DML pois ele é considerado uma linguagem separada em seu próprio direito INSERT O comando INSERT é simples digamos que ele é um pedido de inclusão de uma linha em uma tabela Vale lembrar que os valores a serem inseridos no Banco de Dados devem pertencer ao domínio do campo Contudo as ordens dos cam pos devem seguir a ordem de criação dos campos na tabela As linhas podem ser preenchidas de diversas maneiras mas a maneira mais utilizada e mais sim ples é o INSERT As versões mais básicas da instrução inserem apenas uma linha em uma tabela mas as variações mais complexas podem chegar a inserir várias linhas em várias tabelas A LINGUAGEM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 II U N I D A D E 50 Por meio do comando INSERT podemos inserir uma ou mais linhas no Banco de Dados Segue um exemplo da instrução INSERT em sua variação mais simples INSERT INTO nomedatabela coluna1 coluna2 coluna3 colunaN VALUES valor1 valor2 valor3 valorN UPDATE O comando UPDATE é utilizado para efetuar alterações nas linhas já existentes no Banco de Dados que possivelmente foram gravadas por meio do comando INSERT Da mesma maneira que o INSERT o UPDATE pode afetar uma linha somente ou um conjunto de linhas existentes na tabela Para que se possa deli mitar a linha ou quais linhas receberão a alteração é utilizado a cláusula WHERE que recebe os mesmo tratamentos do SELECT Um grande diferencial do UPDATE é que não é possível atualizar campos de mais de uma tabela de uma única vez Ao atualizar uma linha ou várias linhas o UPDATE determina quais colunas atualizar e assim não é necessário atualizar cada coluna da linha Poderá ser alterado apenas uma coluna da linha Caso a coluna já estiver preenchida com algum valor o valor antigo será substituído pelo novo valor passado pelo comando UPDATE Caso a coluna estiver vazia ela será preenchida depois do UPDATE com o novo valor A sintaxe básica é a seguinte UPDATE nomedatabela SET coluna novovalor DELETE As linhas inseridas e alteradas com os exemplos anteriores agora poderão ser removidas por meio do comando DELETE Esse comando pode remover uma linha ou um conjunto de linhas da tabela a quantidade de linhas a serem remo vidas irá depender da cláusula WHERE Caso a cláusula não for utilizada o comando irá remover todas as linhas pertencentes à tabela o que pode ser um Ddl Linguagem de Definição de Dados Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 51 problema se você esquecer de colocar a cláusula acidentalmente Uma exclusão é tudo ou nada pois não se pode definir colunas para a remoção e sim a linha inteira A sintaxe mais simples para a remoção de uma linha é a seguinte DELETE nomedatabela Por meio desse comando iremos remover todos os dados da tabela informada O efeito de uma instrução DML não é permanente até que você confirmar a tran sação que o incluir A transação é uma sequência de instruções SQL podendo ser uma única instrução DML Até que uma transação seja confirmada ela pode ser revertida desfeita Para mais informações sobre transações consulte Sobre instruções de controle de transação DDL LINGUAGEM DE DEFINIÇÃO DE DADOS Como já tratamos no iní cio desta unidade DDL ou em inglês Data Definition Language é um conjunto dos comandos da lingua gem SQL utilizado para a definição das estruturas dos dados fornecendo as instruções para criação modificação e remoção das tabelas do Banco de Dados A LINGUAGEM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 II U N I D A D E 52 VALORES NOT NULL Na linguagem SQL podemos definir que uma tabela poderá conter campos com valores nulos O único campo que não poderá ficar nulo é a chave primária caso seja obrigatório o preenchimento devese criar esse campo com o atributo NOT NULL pois assim reforçamos a necessidade de se informar um valor para um determinado campo na tabela Dessa maneira o campo especificado não poderá ser deixado de informar pois não pode ser deixado em branco ou seja não poderá conter NULL em seu valor CHAVES E INTEGRIDADE Chaves primárias Para que seja possível especificar uma chave primária utilizase a cláusula PRIMARY KEYS em inglês Primary keys ou PK Uma chave primária tem a função de tornarse um registro em uma tabela única sendo assim nunca haverá a repetição de um mesmo valor para este campo A chave primária pode ser atribuída a um ou mais campos considerando dessa maneira que nunca haverá a repetição da combinação desses dois valores na mesma tabela no Banco de Dados Segue a sintaxe para a utilização da PRIMARY KEYS lembrando que esses comandos vão ao final da sintaxe de criação das tabelas CREATE TABLE nomedatabela nomedocampo INT NOT NULL PRIMARY KEY nomedocampo Ddl Linguagem de Definição de Dados Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 53 A chave estrangeira acontece quando um campo de uma tabela for chave primá ria em outra tabela Podemos pensar da seguinte maneira sempre que houver o relacionamento 1N entre duas tabelas a tabela 1 receberá a chave primária e a tabela N receberá a chave estrangeira A seguir a sintaxe para a criação CREATE TABLE nomedatabela idtabela INT NOT NULL campo1 VARCHAR45 NOT NULL campo2 INT NOT NULL PRIMARY KEY idtabela CONSTRAINT campo2 FOREIGN KEY campo2 REFERENCES tabelareferenciada camporeferenciado ON DELETE NO ACTION ONUPDATE NO ACTION CRIANDO UMA TABELA SIMPLES Existem várias maneiras de se armazenar uma tabela no Banco de Dados porém a mais simples e mais utilizada é a tabela empilhada Essa pilha são as linhas O seu comprimento pode ser variado de forma aleatória podendo haver uma correlação entre as linhas inseridas com a ordenação de armazenamento Para a criação das tabelas é utilizado o comando CREATE TABLE em que o primeiro parâmetro deste comando é o nome da tabela seguido dos campos seus respec tivos tipos e suas devidas restrições Por exemplo CREATE TABLE schemanomedatabela nomedacoluna tipoda coluna A LINGUAGEM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 II U N I D A D E 54 Obrigatoriamente se deve especificar o nome da tabela a ser criada Essa tabela deverá conter no mínimo um campo para que seja possível sua criação Segue o exemplo para a criação da tabela pessoa e telefone da Figura 1 Figura 1 Exemplo para a criação da tabela Fonte os autores Os comandos a seguir são responsáveis por criar as tabelas definidas na figura CREATE TABLE pessoa idpessoa INT NOT NULL nomepessoa VARCHAR100 NOT NULL peso DECIMAL102 NULL PRIMARY KEY idpessoa CREATE TABLE t elefone idtelefone INT NOT NULL numero VARCHAR45 NOT NULL idpessoa INT NOT NULL PRIMARY KEY idtelefone CONSTRAINT idpessoa FOREIGN KEY idpessoa REFERENCES pessoa idpessoa ON DELETE NO ACTION ON UPDATE NO ACTION Ddl Linguagem de Definição de Dados Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 55 ALTERANDO AS DEFINIÇÕES DE UMA TABELA JÁ EXISTENTE Existem muitas alterações que podem ser efetuadas em uma tabela após a sua criação existem muitas alterações em seu meio físico Essas são de responsabi lidade do administrador de Banco de Dados mas muitas outras são puramente lógicas e poderão ser feitas por meio dos desenvolvedores SQL seguem exem plos das alterações possíveis Adicionar uma coluna alter table nomedatabela add nomedocampo tipodocampo Modificando uma coluna alter table nomedatabela modify nomedocampo novotipo campo Deletando uma coluna alter table nomedatabela drop column nomedocampo Adicionando uma restrição a coluna ALTER TABLE nomedatabela ADD FOREIGN KEY tabela1fk tabela2 REFERENCES tabelareferenciada Para adicionar a restrição de não nulo ALTER TABLE nomedatabela ALTER COLUMN nomedocampo SET NOT NULL A LINGUAGEM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 II U N I D A D E 56 Removendo restrição ALTER TABLE produtos DROP CONSTRAINT nomedarestrição Removendo restrição not null ALTER TABLE produtos ALTER COLUMN codprod DROP NOT NULL DELETANDO UMA TABELA O comando DROP TABLE permite a remoção de uma tabela do Banco de Dados Essa operação remove linhas estrutura e índices de acesso associados à tabela Esse comando é bem simples em relação aos demais segue a sintaxe drop table nomedatabela DCL LINGUAGEM DE TRANSAÇÃO DE DADOS A DCL conhecida também como Linguagem de Controle de Dados permite o controle de acesso e manipulação a dados dentro do Banco de Dados Normalmente esses comandos são utilizados para controlar a distribuição de privilégios entre um usuário e outro Dcl Linguagem de Transação de Dados Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 57 CONCEDENDO PERMISSÕES O comando GRANT é o meio pelo qual é possível conceder privilégios para acessar objetos dentro do Banco de Dados papéis e públicos As concessões também permitem conceder privilégios a nível de sistema do Banco de Dados para usuário e papéis As permissões em tabelas podem conceder diferentes tipos de níveis de acesso dentro do Banco de Dados Esse acesso poderá ser muito específico como por exemplo você pode conceder acesso às colunas específicas de uma tabela As permissões a nível de sistema permite conceder diferentes funciona lidades a determinados usuários dentro do Banco de Dados como a capacidade de criar uma tabela ou alterar as configurações de parâmetros de uma sessão de um usuário Uma vez que um privilégio é atribuído a um usuário ele entrará em vigor imediatamente O que é computação nas nuvens cloud computing O termo computação nas nuvens vem do inglês cloud computing que é a atual tendência para softwares Alguns anos atrás para você poder acessar um programa como o word por exemplo era necessário você instalálo no seu computador pagando uma taxa em geral alta Quando falamos em computação nas nuvens partimos do princípio que você não precisa insta lar o word no seu computador e poderá acessálo pela internet pagando pelo uso ou um valor fixo mensal bem abaixo do que quando precisava comprálo Mas isso é possível Sim o word pode ser acessado apenas pela internet É fácil perceber que os dados desse aplicativo não estão no seu computa dor mas em uma rede com acesso à internet que uma vez que conectado você poderá desfrutar de todas as suas ferramentas online e acessar seu tra balho de qualquer lugar Fonte RSWA 2015 online2 A LINGUAGEM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 II U N I D A D E 58 Segue a sintaxe do GRANT GRANT ALL ON TO usuarioseuhost O comando REVOKE revoga direitos de acesso do usuário ou privilégios para os objetos de Banco de Dados Podemos dizer que esse comando remove os pri vilégios concedidos pelo comando GRANT Um determinado usuário pode remover somente os privilégios que foram concedidos diretamente por esse usuário Se por exemplo o usuário José con cedeu um privilégio com opção de concessão para o usuário Maria e o usuário Maria por sua vez concedeu o privilégio para o usuário João então o usuário José não poderá revogar diretamente o privilégio de João Em vez disso o usuá rio José poderá revogar a opção de concessão do usuário Maria usando a opção CASCADE para que o privilégio seja por sua vez revogado do usuário João Outro exemplo é o caso em que tanto José quanto Maria concederam o mesmo privilégio a João nesse caso José poderá revogar sua própria concessão mas não poderá revogar a concessão feita por Maria e portanto João continuará com o privilégio mesmo que José revogue o privilégio Segue a sintaxe do REVOKE REVOKE ALL ON TO usuarioseuhost Dessa maneira aprendemos como conceder e remover os privilégios de um usuário a nível de um objeto ou mesmo a nível de Banco de Dados Esse tipo de controle é muito utilizado a fim de garantir a segurança e a integridade das informações no Banco de Dados Dtl Linguagem de Transação de Dados Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 59 DTL LINGUAGEM DE TRANSAÇÃO DE DADOS O conceito por trás de uma transação faz parte de um paradigma do Banco de Dados relacional Uma transação completa consiste em um ou mais comandos DML seguidos por um comando COMMIT ou ROLLBACK Uma sessão começa uma transação a partir do momento que qualquer instrução INSERT UPDATE ou DELETE é emitida ao Banco de Dados onde essa transação irá continuar por inúmeros comandos DML até que a sessão emita uma instrução COMMIT ou ROLLBACK somente após esses comandos de fato a alteração será persistida no Banco de Dados e se tornarão visíveis aos demais usuários do Banco de Dados COMMIT Esse tem a função de confirmar os comandos DML aplica dos ao Banco de Dados ROLLBACK Faz com que as mudanças nos dados feitos pelos coman dos DML existentes desde o último COMMIT ou ROLLBACK sejam descartadas A LINGUAGEM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 II U N I D A D E 60 Digamos que você pediu para excluir dados dentro do Banco de Dados DELETE FROM pessoa Quando uma exclusão é efetuada podemos confirmar essa exclusão com a utili zação do comando commit Tome muito cuidado com essa utilização pois uma confirmação efetuada commit se torna irreversível Esse comando é muito útil e muito utilizado quando temos cenários com vários usuários conectados à mesma base de dados pois ele trabalha especificamente em cada seção dos usuários Após o seu uso as modificações se tornam visíveis a todos os demais usuários BMED 2016 online3 Mas caso não queira que os dados sejam gravados de fato no Banco de Dados podemos utilizar o ROLLBACK dessa maneira os dados anteriores são copiados novamente para o Banco de Dados e retornando assim a sua versão original novamente Tenha em mente que somente dados corretos proporcionarão informações confiáveis para criar estratégias e soluções válidas para a empresa Afinal não adianta investir em ferramentas tecnologias e aplicativos variados se os dados que estão armazenados não têm qualidade e credibilidade Considerações Finais Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 61 CONSIDERAÇÕES FINAIS Caroa alunoa com a finalização do estudo sobre esta unidade podemos con cluir que a prática da SQL é de extrema importância para o amplo entendimento e conhecimento especializado Durante esta unidade estudamos os principais comandos de um Banco de Dados para a criação alteração e remoção das tabe las comandos para a inserção alteração e remoção de dados em uma tabela e também estudamos as permissões dos usuários com a concessão de privilégios e revogação a fim de garantir a segurança e a integridade do nosso Banco de Dados Vimos que a cada comando DML executado no Banco de Dados é controlado por uma transação ou seja por meio dos comandos DTL Todos esses dados foram trabalhados de uma forma genérica para todos os Banco de Dados que usam como linguagem a SQL 62 1 Quais são os comandos DML a Insert update select delete b Drop delete create c Truncate delete d Insert update delete e Rollback commit 2 O comando é responsável por relacionar os atributos desejados no resultado de uma consulta ou seja ele permite recuperar os dados de uma tabela do Banco de Dados esse comando corresponde à operação da álgebra relacional Estamos falando de a Select b From c Where d and e in 3 O é utilizado para delimitar os dados a serem retornados pela nossa consulta os filtros a serem aplicados se restringem a linhas utilizando operado res de comparação em um conjunto de campos e valores literários a Select b From c Where d width e view 63 4 Conjunto de comandos dentro da SQL que permite ao desenvolvedor utilizálo para a definição das estruturas de dados contendo instruções as quais permi tem a criação modificação e remoção de tabelas bem como criação alteração e remoção de elementos associados às tabelas SILBERSCHATZ KORTH SUDAR SHAN 1999 Quais são os comandos DDL a create alter drop b create alter drop update c create drop d Select insert delete e Select insert update delete 5 DML Data Manipulation Language Linguagem de Manipulação de Dados Es tes comandos são utilizados para realizar inclusões exclusões e alterações de dados os quais são utilizados a partir dos comandos INSERT UPDATE e DELETE WATSON RAMKLASS et al 2012 comente sobre cada um desses comandos MATERIAL COMPLEMENTAR Fundamentos I SQL 2008 John Watson e Roopesh Ramklass Editora Alta Books Sinopse este livro é um guia de treinamento ofi cial da Oracle para o exame 1Z0052 Com mais exercícios dicas e perguntas de teste O leitor poderá aprender a criar um Banco de Dados Oracle confi gurar uma rede Oracle administrar a segurança do usuário fazer backup e recuperação e outros Este livro inclui um CD com um teste em inglês que simula a experiência do exame REFERÊNCIAS SILBERSCHATZ A KORTH H F SUDARSHAN S Sistema de banco de dados 3 ed São Paulo Makron Books do Brasil 1999 WATSON J RAMKLASS R Fundamentos I SQL OCA Oracle Database 11g Rio de Janeiro Alta Books 2012 Referências online 1 Em httpdocplayercombr16602595Linguagemsqldmllinguagemde manipulacaodedadoshtml Acesso em 21 nov 2016 2 Em httpsrswacombr20150729oqueecomputacaonasnuvens Acesso em 21 nov 2016 3 Em httpwwwfabiobmedcombrcomandosmaiscomunsemumbanco dedadossql Acesso em 21 nov 2016 65 GABARITO 1 D 2 A 3 C 4 A 5 O comando INSERT é simples ele é um pedido de inclusão de uma linha em uma tabela O comando UPDATE é utilizado para efetuar alterações nas linhas já existentes no Banco de Dados que possivelmente foram gravadas por meio do comando INSERT DELETE é o comando que pode remover uma linha ou um conjunto de linhas da tabela A quantidade de linhas a serem removidas irá de pender da cláusula WHERE UNIDADE III Professor Esp Jeferson Kaiser MANIPULAÇÃO DE DADOS Objetivos de Aprendizagem Identificar na prática a utilidade das teorias aplicadas em relação à linguagem SQL Compreender as atividades básicas do dia a dia de um programador de Banco de Dados Plano de Estudo A seguir apresentamse os tópicos que você estudará nesta unidade Extrair dados de uma tabela Agrupando a exibição dos dados Ordenação na exibição dos dados Criando consultas com filtros específicos Valores nulos Rename Consulta utilizando mais de uma tabela Consultas com subqueries Teste de relações vazias Alterando os dados com update Removendo os dados com delete Rollback e commit Truncate e drop INTRODUÇÃO Caroa alunoa Nesta unidade vamos nos aprofundar mais sobre os comandos da Structured Query Language SQL scripts que podemos utilizar por meio de um Sistema de Gerenciamento de Banco de Dados SGBD Dessa forma iremos trabalhar com retorno de dados consultas e comandos avançados sendo utiliza dos para um melhor aproveitamento de nosso Banco de Dados Conhecemos nas unidades anteriores o conteúdo sobre a linguagem SQL e por meio dela conhece mos as suas classificações sendo a Data Query Language DQL em português Linguagem de Consulta de Dados a Data Manipulation Language DML em português Linguagem de Manipulação de Dados a Data Definition Language DDL em português Linguagem de Definição de Dados a Data Control Language DCL em português Linguagem de Controle de Dados e a Data Transaction Language DTL em português Linguagem de Transação de Dados Em Banco de Dados MySQL e para qualquer distribuição de Banco de Dados é extremamente necessário o conhecimento dos conceitos abordados pois para a aplicabilidade é necessário concretizar as teorias vistas Nosso objetivo nesta unidade é demonstrar na prática os conceitos abor dados sobre DQL parte dos comandos DML pois o comando INSERT já foi abordado na unidade anterior além dos comandos DTL Esperamos que o conteúdo e os assuntos abordados neste livro seja de muito proveito em sua vida profissional pois o mundo gira em torno da teoria e da prática ambos necessariamente tem que trabalhar lado a lado a fim de se obter uma plena excelência sobre o produto desenvolvido Os melhores sempre são aqueles que têm pleno conhecimento naquilo que fazem Bom estudo Introdução Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 69 MANIPULAÇÃO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 III U N I D A D E 70 EXTRAIR DADOS DE UMA TABELA Caroa alunoa compreendemos que as informações ficam armazenadas junto ao Banco de Dados Devemos saber como efetuar a consultas destas informações para assim manipulálas Para extrair os dados de uma tabela ela deve ser con sultada pelo SGBD e deverá conter dados Dessa forma iremos trabalhar nesta unidade com as informações utilizadas nas unidades anteriores Primeiramente vamos relembrar a sintaxe do comando Select lista de colunas From tabela Nossa primeira consulta será na tabela de pessoa da qual iremos trazer todas as colunas Na linguagem SQL existe um comando específico para retornar todos os dados da coluna Esse comando é dado por meio do que é uma forma abre viada de dizer todas as colunas Select From pessoa Extrair Dados de uma Tabela Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 71 Na Figura 1 podemos observar como a saída de nossa consulta deverá ser Figura 1 Saída da consulta Fonte os autores Para que seja possível trabalharmos com os próximos comandos da linguagem SQL será necessário mais de um registro presente na tabela de pessoa Dessa forma sugiro que utilize o comando insert para efetuar a inserção de mais dados nessa tabela Dando continuidade em nosso conteúdo sugiro que faça a criação das tabelas que faltam em nossa base de dados seguindo o diagrama Figura 2 Tabelas a serem criadas Fonte os autores Com as tabelas já criadas sugiro que insiram 5 produtos diferentes na tabela de pedidoitem Lembrando que os 5 registros deverão sempre conter o mesmo idpessoa Agora vamos trabalhar com funções no select MANIPULAÇÃO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 III U N I D A D E 72 Segundo Silberschatz Korth e Sudarshan 1999 Funções agregadas são funções que tomam uma coleção um conjunto ou subconjuntos de valores de entrada retornando apenas um valor simples A linguagem SQL oferece 5 fun ções agregadas Média average avg Mínimo minimum min Máximo maximum max Total sum sum Contagem count count Para a entrada das funções sum e avg os dados necessariamente precisam ser numéricos Entretanto as outras funções podem ser operadas com dados do tipo não numéricos como as strings e seus semelhantes Dessa maneira vamos exemplifi car a função sum com o campo total da tabela pedidoitem select sumtotal from unicesumarpedidoitem Nosso resultado deverá apresentar apenas um registro com a soma dos 5 regis tros desse campo na tabela Figura 3 Soma dos 5 registros Fonte os autores Extrair Dados de uma Tabela Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 73 Funciona da mesma maneira para a função avg select avgtotal from unicesumarpedidoitem Nesse caso nosso resultado será a média entre os 5 registros da tabela Quando desejamos encontrar o número de registros que um select irá nos retornar utilizamos a função count A notação para esta função sem SQL é count como no exemplo a seguir select count from unicesumarpedidoitem O resultado a ser apresentado deverá ser apenas um registro contendo a quan tidade dos registros Figura 4 Quantidade de registros Fonte os autores Em nosso exemplo o resultado foi 5 pois só temos 5 registros inseridos na tabela A função min retorna o menor valor de uma coluna em um grupo de linhas Podemos utilizála para colunas do tipo data ou alfanuméricas Para saber o preço de venda mais alto do pedido execute o comando a seguir select avgtotal from unicesumarpedidoitem MANIPULAÇÃO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 III U N I D A D E 74 Já a função max retorna o maior valor de uma coluna em um grupo de linhas Igualmente ao min podese utilizála para colunas do tipo data ou alfanuméricas Para saber qual é o produto mais caro do pedido execute o seguinte comando select maxtotal from unicesumarpedidoitem Assim encerramos a principais funções agregadas da linguagem SQL Essas fun ções são de uso diário na vida de um programador pois além de simples elas são muito úteis Todo Banco de Dados possui alguma linguagem na qual os comandos de vem ser enviados por meio de alguma ferramenta console Geralmente essa linguagem conta com elementos padrão do SQL e também com outros elementos adicionais que são específicos do Banco de Dados De qualquer forma sempre que um comando não consegue ser executado ou algum ou tro tipo de problema ocorre é de responsabilidade do SGBD retornar uma mensagem de erro junto com o código Geralmente essa mensagem de erro está em inglês apesar de existirem al guns trabalhos de tradução para o português Além disso todas as mensa gens de erro devem estar devidamente descritas e apresentadas na docu mentação oficial do Banco de Dados Para ler o artigo na íntegra acesse httpimasterscombrbancodedados quaissaooserrosmaiscomunsembancosdedadostrace 1519021197sourcesingle Fonte Pichiliani 2013 online1 Agrupando a Exibição dos Dados Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 75 AGRUPANDO A EXIBIÇÃO DOS DADOS Com a utilização do group by é possível efetuar o agrupamento de diversos regis tros baseados em uma ou mais colunas de uma tabela Por exemplo os produtos da tabela pedidoitem podem ser agrupados pelo valor maior valor menor valor pelo qtde etc Importante frisar que a cláusula group by é comumente em conjunto com as funções agregadas O group by é responsável por determinar em quais grupos devem ser coloca das as linhas de saída Caso nosso select tenha funções agregadas a cláusula group by realiza um cálculo a fim de chegar ao valor sumário para cada um dos grupos Para colocarmos essa cláusula devemos estar diante de uma das seguintes situações ou a expressão group by deve ser correspondente à expressão da lista de seleção ou cada uma das colunas presentes em uma expressão não agregada na lista de seleção deve ser adicionada à lista de group by Observe o exemplo a seguir select idpedido sumvalor from unicesumarpedidoitem group by idpedido MANIPULAÇÃO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 III U N I D A D E 76 O select nos traz a soma da coluna valor agrupador pelo idpedido conforme explicação anterior Para exemplifi car esta consulta foi inserido mais um pedido na tabela de pedido e mais alguns itens relacionados com o segundo pedido Figura 5 Novo pedido Fonte os autores ORDENAÇÃO NA EXIBIÇÃO DOS DADOS Segundo Silberschatz Korth e Sudarshan 1999 a linguagem SQL oferece ao usuário um con trole sobre a ordem que os dados são retornados para o usuário A Cláusula order by faz com que os registros do resultado de uma consulta apareçam na ordem clas sifi cada Para listar por ordem alfabética todos os produtos da tabela de produtoitem devemos escrever da seguinte maneira Select descproduto idpedido From pedidoitem Order by descproduto Criando Consultas com Filtros Específicos Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 77 Por padrão a cláusula order by lista os resultados em forma crescente Para que seja possível especificar a ordem de retorno podemos especificar desc para decrescente ou asc para a ordem crescente dos dados Além disso a ordenação pode ser feita por mais de 1 campo da consulta Supondo que precisamos orde nar uma consulta por mais de um campo Select From pedidoitem Order by descpedido valor Dessa forma primeiro nossa consulta irá ser ordenada pela descrição do produto e posteriormente irá ordenar o segundo campo sempre respeitando a ordenação anterior Vale lembrar que como a classificação de um grande número de regis tros pode ser demorada esse tipo de classificação deverá ser feita somente quando necessário CRIANDO CONSULTAS COM FILTROS ESPECÍFICOS Vamos ilustrar o uso da cláusula where na SQL Consideremos então a seguinte consulta encon tre todos os registros no pedido os quais o valor é maior que 20 MANIPULAÇÃO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 III U N I D A D E 78 Seletc From pedidoitem Where valor 20 Na linguagem SQL utilizamos and or e not ao invés de utilizarmos notações mate máticas e na cláusula where Os operandos dos conectivos lógicos podem ser expressões envolvendo os seguintes operadores de comparação e Essa linguagem permite operadores que comparam strings expressões aritméticas além de comparativos entre datas A linguagem SQL possui um operador de comparação between que tem o intuito de simplificar a cláusula where Seu intuito é encontrar um valor que seja menor ou igual a algum valor e maior ou igual a um outro valor No caso se quisermos encontrar os produtos que estão entre 10 e 20 devemos escrever a consulta da forma apresentada a seguir Select From pedidoitem Where valor between 10 and 20 Ao invés de Select From pedidoitem Where valor 10 and valor 20 Da mesma forma também é possível utilizar o comando de comparação not between Valores Nulos Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 79 VALORES NULOS Podemos realizar o uso de valores nulos para denotar a ausência de informações nos registros Na SQL utilizase a palavra null para testar a presença de um valor nulo por exem plo para buscar os registros que não possuem valor devemos escrever a seguinte consulta Select From pedidoitem Where valor is null Também podemos utilizar o predicado is not null que testa a ausência de um valor nulo no campo RENAME Um grande mecanismo da lingua gem SQL muito utilizado por todos é a função de renomear um campo ou tabela da consulta empregando a utilização da cláusula AS da seguinte forma MANIPULAÇÃO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 III U N I D A D E 80 Nomedocampooutabela as nomenovo A utilização dessa cláusula se dá tanto no select quanto no from das consultas Consideremos a seguinte consulta Select pedidoitemdescproduto Pedidoitemvalor pedidoitemqtde From pedidoitem Agora utilizando a função de rename Select pddescproduto pdvalor pdqtde From pedidoitem as pd Dessa maneira rebatizam o nome da tabela para que a consulta fique mais simples A utilização na cláusula from é logo depois do nome da tabela a qual se deseja a associação com a palavra as entre eles como no exemplo anterior Vale lembrar que a utilização do as é opcional pois o próprio sgbd subentende essa utilização Assim como em muitos livros aqui iremos tratar esse rename como a criação de um alias para as tabelas ou colunas Consulta Utilizando Mais de uma Tabela Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 81 CONSULTA UTILIZANDO MAIS DE UMA TABELA Para que seja possível efetuar uma con sulta utilizando mais de uma tabela é necessário trabalharmos com a cláu sula from de nosso select Veja no exemplo a seguir que estamos acres centando uma tabela no from a tabela Tabela2 lembrando que para essa prá tica é necessário ligarmos as tabelas com a cláusula where em suas respec tivas chaves primárias e estrangeiras ANTUNES online 20161 SELECT Tabela1coluna1 Tabela1coluna2 Tabela2coluna1 Tabela2coluna2 FROM Tabela1 Tabela2 WHERE Tabela1chaveprimaria Tabela2chaveestrangeira Vale lembrar que a utilização de alias é opcional antes do nome das colunas Contudo essa é uma prática muito utilizada facilitando assim o entendimento e uma possível manutenção no código A utilização do alias antes do nome do campo se torna obrigatório quando temos o mesmo campo em mais de uma tabela de nossa consulta Geralmente isso acontece muito com as chaves primárias e estrangeiras sendo assim neste caso é obrigatório que se indique de qual tabela deverá retornar o dado Considere que informar em qual tabela está a coluna facilita o trabalho do Banco de Dados Essa prática leva a maior agilidade na recuperação da informação A união regular se dá pela união na cláusula where da chave primária com a chave estrangeira das tabelas MANIPULAÇÃO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 III U N I D A D E 82 CONSULTAS COM SUBQUERIES Segundo Silberschatz Korth e Sudarshan 1999 a SQL fornece um mecanismo para aninhar subconsultas Uma subconsulta é uma expres são select from where que é utilizada dentro de outra con sulta Esse uso é muito comum para realizar teste de partici pação de conjuntos fazer comparações e determinar a cardinalidade dos conjuntos O conectivo IN testa um conjunto de valores produzidos pelo select na cláusula where ou ainda podemos trabalhar com o NOT IN que testa a ausência de um conjunto de valores Para exemplificar essa utilização vamos pensar em um select que retorna todos os pedido dos clientes cadastrados em nossa base Primeiro vamos encon trar os identificadores da tabela de pessoa Select idpessoa From pessoa Depois de encontrar os dados da pessoa vamos aninhar essa consulta com a consulta dos pedidos Vale lembrar que a subconsulta sempre deve estar entre parênteses Select From pedido Where idpessoa in Select idpessoa From pessoa Teste de Relações Vazias Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 83 Selects que incluem uma subconsulta normalmente têm um destes formatos WHERE expressão NOT IN subconsulta WHERE expressão operador de comparação ANY ALL subconsulta WHERE NOT EXISTS subconsulta TESTE DE RELAÇÕES VAZIAS A função Exists e Not Exists da SQL é usada para verificar se o resultado de uma consulta aninhada correlacionada é vazio não contém nenhum registro ou não A construção do exists retorna um valor true se a subconsulta não é vazia Pensando dessa maneira vamos escre ver um select que nos traga somente os dados das pessoas que possuem pedido Select pes From pessoa pes Where exists select ped From pedido ped Where pedidpessoa pesidpessoa Da mesma maneira podemos testar a existência dos registros utilizando o not exists De acordo com o exemplo se utilizamos o not exists iremos trazer os dados de todas as pessoas que não possuem nenhum pedido MANIPULAÇÃO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 III U N I D A D E 84 Select pes From pessoa pes Where not exists select ped From pedido ped Where pedidpessoa pesidpessoa ALTERANDO OS DADOS COM UPDATE Em muitas situações temos que alterar um dado de uma tabela sem alterar todos os seus valores A instrução UPDATE deve ser utilizada nesse caso Bem parecida com a instrução de insert e delete podem ser seleciona dos os campos que devem ser atualizados Supondo que desejamos alterar o valor dos produtos em 10 deveremos escrever o seguinte comando Update pedidoitem Set valor valor 11 O comando anterior é aplicado uma vez em cada registro da tabela de pedido item Caso desejarmos por exemplo aumentar em 10 o valor dos produtos que custam mais de R10 deveríamos escrever o seguinte comando Alterando os Dados com Update Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 85 Update pedidoitem Set valor valor 11 Where valor 10 A cláusula where da instrução update é muito semelhante a instrução where do select incluindo select aninhado Como no insert e delete um select pode referen ciar uma relação a ser atualizada do update Pensando dessa forma vamos escrever um update que atualiza o valor dos produtos que estão acima da média em 10 Update pedidoitem Set valor valor 11 Where valor select avgvalor From pedidoitem O update fornece uma instrução chamada case que nos dá a opção de fazer duas atualizações com uma única instrução update Em uma forma geral a instrução case é a seguinte Case When predicado1 then Resultado1 When predicado12 then Resultado2 Else resultado0 End MANIPULAÇÃO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 III U N I D A D E 86 Para atualizarmos o valor dos produtos menores que R1000 em 10 e os produtos maiores que R1000 em 5 devemos escrever o seguinte comando Update pedidoitem Set valor case When valor 10 then Valor valor 11 Else Valor valor 105 End REMOVENDO OS DADOS COM DELETE Uma remoção de dados de uma tabela às vezes é expressa do mesmo modo que uma consulta podendo serem removidos apenas registros completos não podendo assim remover o conteúdo de um campo Podemos expres sar uma remoção da seguinte maneira Delete from nome da tabela Where condição Vale lembrar que o comando delete trabalha apenas com uma relação caso seja necessário remover os dados de diversas tabelas é necessário utilizar um comando de delete para cada tabela A cláusula where pode ser tão complexa quanto a cláu sula where de um select porém pensando em simplicidade podemos também Rollback e Commit Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 87 executar o comando delete sem o uso da cláusula where e assim todos os regis tros da tabela em questão serão apagados Exemplo Delete from pedidoitem No exemplo anterior estamos apagando todos os itens registrados na tabela de pedidoitem Apresentamos aqui uma série de exemplos de remoção utilizando a SQL Deleta os registros desde que o valor seja maior que 10 Delete from pedidoitem where valor 10 Apaga os registros que tenham o valor de produto acima da média Delete from pedidoitem where valor select avgvalor from pedidoitem ROLLBACK E COMMIT Segundo Silberschatz Korth e Sudarshan 1999 uma transação consiste em uma sequência de instruções de consulta ou de atualização O padrão SQL espe cifica que uma transação inicia implicitamente quando uma instrução SQL é executada em que uma das seguintes instruções precisam finalizar a transação Commit confirma a transação atual Ou seja torna as atualizações realiza das pela transação permanentes no Banco de Dados Após a confirmação da transação uma nova transação é iniciada Rollback faz com que a transação atual seja revertida ou seja ele desfaz todas as atualizações realizadas pela instrução SQL na transação Portanto o estado do Banco de Dados é restaurado para como era antes da primeira instrução da transação ser executada MANIPULAÇÃO DE DADOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 III U N I D A D E 88 TRUNCATE E DROP Para que seja possível remover uma tabela de um Banco de Dados utilizamos o comando drop table O comando drop table exclui do Banco de Dados a tabela e todas as informações nela contida Esse comando é muito simples segue a sintaxe Drop table nomedatabela O comando TRUNCATE é responsável por limpar os registros de uma tabela e fará isso de uma forma mais rápida que o comando DELETE Esse comando é mais rápido que o comando delete porque ele não faz uma cópia dos dados Assim não se tem o comando de rollback O comando TRUNCATE é um comando DDL enquanto o DELETE é um comando DML Observe as principais dife renças entre o DELETE e o TRUNCATE 1 TRUNCATE é um comando de Linguagem de Definição de Dados enquanto DELETE é de Manipulação de Dados 2 O Comando TRUNCATE não conta com a função RollBack ao contrá rio do DELETE 3 Uma TRIGGER não é disparada quando utilizamos o TRUNCATE com o comando DELETE se existir ela será disparada 4 O comando TRUNCATE apaga todos os dados da tabela já o DELETE podemos associálo a condições cláusula WHERE LEITÃO 2015 online3 Pensando dessa forma utilizamos o comando drop para apagar as tabelas do Banco de Dados juntamente com os seus respectivos conteúdos Já o comando truncate por sua vez para limpar os dados de uma tabela Considerações Finais Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 89 CONSIDERAÇÕES FINAIS Prezadoa alunoa Após conhecermos os comandos básicos da linguagem SQL sendo os de Linguagem de Manipulação de Dados DML Linguagem de Definição de Dados DDL e Linguagem de Controle de Dados DCL entre outros apresentados na unidade anterior apresentamos por meio desta unidade alguns conceitos e comandos mais avançados sobre o SQL Foram apresentados comandos e formas mais avançadas de seleção de dados em uma tabela Alguns que facilitam a manipulação da informação e ajudam a refi nar nossas buscas tornando a consulta mais eficaz Por meio dessas consultas a manipulação de dados fica mais clara podemos citar como exemplo os comando de contagem soma ordenação de apresentação dos dados crescente ou decrescente e a cláusula Where está sendo fundamental em várias consultas ao Banco de Dados Ao falarmos de seleção de dados nos deparamos em casos que precisamos manipular mais do que uma tabela Trabalhamos também nesta unidade essa questão que pode nos ajudar em nosso processo de desenvolvimento de software Nesse sentido aprendemos duas formas de efetuar por meio de uma consulta em várias tabelas ou subqueries Entendemos que em um Banco de Dados as informações podem ser modi ficadas a qualquer momento Pensando nisto conhecemos algumas formas de efetuar a alteração dos dados por meio dos comando UPDATE além de enten dermos a função dos comandos ROLLBACK e COMMIT esses sendo úteis em casos de situação efetuadas de modo errado no Banco de Dados Por fim conhecemos os comandos TRUNCATE DELETE e DROP Todos têm o mesmo princípio limpar os dados da tabela cada um com sua particularidade comandos que podem ser perigosos se não forem manipulados de modo adequado Chegamos ao fim de mais uma unidade espero que tenha contribuído de forma significativa em sua aprendizagem prezado alunoa um forte abraço e até a próxima A manipulação das informações em um Banco de Dados ocorre somente com a linguagem SQL ou podemos utilizar programas que nos auxiliem nes se processo 90 1 Com a utilização do group by é possível efetuar o agrupamento de diversos re gistros baseados em uma ou mais colunas de uma tabela Dessa maneira dê o exemplo de um select utilizando o group by e o porquê de sua utilização 2 Sabemos que o truncate é responsável por limpar os registros de uma tabela e que ele é mais rápido que o comando delete Esse comando é mais rápido por não se tratar de um comando DML Dessa maneira cite as principais diferenças entre os comandos DELETE e TRUNCATE 3 Uma subconsulta é uma expressão select from where que é utilizada dentro de outra consulta Esse uso é muito comum para se realizar teste de participação de conjuntos fazer comparações e determinar a cardinalidade dos conjuntos O conectivo IN testa um conjunto de valores produzidos pelo select na cláusula where ou ainda podemos trabalhar com o NOT IN que testa a ausência de um conjunto de valores Exemplifique a utilização de subqueries de acordo com al guma tabela de nosso livro 4 A linguagem SQL possui um operador de comparação between que tem o intuito de simplificar a cláusula where O intuito é encontrar um valor que seja menor ou igual a algum valor e maior ou igual a um outro valor Pensando dessa forma escreva um select que encontre os produtos que estão com valor entre 3 e 8 5 Segundo Silberschatz Korth e Sudarshan 1999 Funções agregadas são funções que tomam uma coleção um conjunto ou subconjuntos de valores de entrada retornando apenas um valor simples Descreva e relacione essas funções Material Complementar MATERIAL COMPLEMENTAR Sistemas de Banco de Dados 2012 Virgínia M Cardoso e Giselle Cristina Cardoso Editora Saraiva Sinopse em texto introdutório as autoras apresentam tópicos que levam o aluno a conhecer o universo do Banco de Dados apresentando como projetar construir e popular um Banco de Dados por meio de exemplos didáticos e práticos De forma a facilitar o aprendizado este texto proporciona ferramentas para que o aluno seja capaz de desenvolver de forma independente um Banco de Dados com armazenamento seguro e que ofereça uma pesquisa rápida e efi caz REFERÊNCIAS SILBERSCHATZ A KORTH H F SUDARSHAN S Sistema de Banco de Dados 3 ed São Paulo Makron Books do Brasil 1999 Referências online 1 Em httpimasterscombrbancodedadosquaissaooserrosmais co munsembancosdedadostrace1519021197sourcesingle Acesso em 22 nov 2016 2 Em httpwwwdevmediacombrintroducaoaosqlpesquisaemmultiplas tabelas17006 Acesso em 22 nov 2016 3 Em httpwwwprofissionaloraclecombrgposervicoseasyblogentry 20150811diferencaentreoscomandostruncatedeleteedroptmplcompo nenttyperaw Acesso em 22 nov 2016 93 GABARITO 1 Utilizado para efetuar o agrupamento pelo idproduto da tabela de pedido select idpedido sumvalor from unicesumarpedidoitem 2 As principais diferenças são TRUNCATE é um comando DDL enquanto DELETE é um comando DML TRUNCATE é muito mais rápido do que o DELETE Não existe Rollback para o comando TRUNCATE mas para o DELETE sim O co mando TRUNCATE re move o registro permanente Em caso de TRUNCATE a TRIGGER não é disparada mas no caso do comando DELETE existindo TRIGGER para deleção ela é disparada Você não pode usar condições cláusula WHERE com o comando TRUNCATE Mas com o comando DELETE você pode escrever usando condições cláusula WHERE 3 Select From pessoa Where idpessoa in Select idpessoa From pedido Nesse select estamos retornando somente as pessoas que possuem pedido no Banco de Dados GABARITO 95 4 Select From pedidoitem Where valor between 3 and 8 5 Média average avg Min Traz o menor valor da coluna Max Traz o maior valor da coluna Sum Soma os valores da coluna Count Retorna a quantidade de registros da consulta em questão Avg Faz a média dos registros para esta coluna retornados em um select UNIDADE IV Professor Me William Roberto Pelissari PROGRAMAÇÃO EM SQL Objetivos de Aprendizagem Conhecer algumas diferenças da ferramenta utilizada Entender a programação de Store Procedures Plano de Estudo A seguir apresentamse os tópicos que você estudará nesta unidade Visão Geral sobre PLSQL Procedures Functions Packages INTRODUÇÃO Olá caroa alunoa Compreender um produto tão grandioso quanto um Banco de Dados é conseguir obter uma noção de como efetivamente ele deve funcio nar ter o conhecimento dos detalhes intrínsecos da formação do sistema e o seu gerenciamento O objetivo é ter uma base abrangente sobre os conceitos e tecnologias que formam os fundamentos de um Servidor de Banco de Dados utilizando os seus melhores recursos A ferramenta utilizada será o Oracle A faixa de utilizadores passa por usuá rios e desenvolvedores e até Administradores de Banco de Dados DBA sendo uma das opções mais populares e eficazes do mercado A compreensão básica sobre o produto deve proporcionar a ligação dos pontos para a utilização do volumoso conjunto de características e documen tação do Oracle bem como os muitos livros e publicações que descrevem essa base de dados A empresa antes pouco conhecida sendo apenas uma a mais entre as con correntes levou anos em desenvolvimento de suas soluções para o mercado de gerenciadores de bancos de dados até se tornar a líder mundial no segmento oferecendo um produto com níveis cada vez melhores em escalabilidade fun cionalidade e gerenciamento dos dados Vamos abordar algumas diferenças entre o Oracle e o MySQL para que pos samos equalizar as funcionalidades entre eles e compreender as funcionalidades dos comandos de programação e estruturação de códigos e suas particularidades Ótimo estudo Introdução Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 99 Figura 1 O poder da linguagem PLSQL PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 100 Visão Geral Sobre PlSql O PLSQL Procedural LanguageSQL é uma extensão para o Structured Query Language SQL incorporando várias facilidades das linguagens de programa ção existentes O PLSQL permite utilizar comandos para manipulação de dados e consultas em blocos de programação estruturados fazendo do PLSQL uma poderosa linguagem de processamento de transações O objetivo desta visão geral sobre PLSQL é distinguir entre um bloco PL SQL anônimo e nomeado descrever subprogramas listar os benefícios na uti lização de subprogramas e descrever onde um subprograma pode ser chamado Para iniciar esta abordagem é fundamental conhecer um subprograma Ele é um bloco padrão de PLSQL nomeado que aceita parâmetros e pode ser cha mado de um ambiente São dois os tipos de subprogramas functions e procedures Um subprograma é modular pode ser reutilizável extensível e gerenciável fornece a maior segurança aos dados melhorando a performance do sistema a corretude do código e a integridade dos dados oferecendo mais clareza ao código São blocos nomeados Estes podem ser declarados como uma procedure ou fun ção Em alguns casos pode retornar valores quando executados Visão Geral Sobre PlSql Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 101 ESTRUTURA DE BLOCO PLSQL é uma linguagem estruturada em blocos Variáveis podem ser definidas em blocos que representam seu escopo de utilização e erros podem ser trata dos dentro do bloco Controles de fluxo por meio de comandos IF THEN ELSE e ELSIF contro lam os desvios do fluxo de execução em tempo real A portabilidade e integração dos códigos PLSQL é garantida em qualquer ambiente executando uma base de dados Oracle ou que o suportem Em termos de desempenho codificação PLSQL pode proporcionar bons níveis de performance em certos ambientes favorecendo seu uso BAC 2016 online1 ESTRUTURA DO PLSQL As unidades feitas em PLSQL podem ser definidas em um ou mais blocos Esses blocos são como componentes que podem estar inclusive embutidos em outro bloco como se fossem subrotinas Um exemplo de estrutura para esses blocos inicia com um trecho de declaração seguido pelo trecho principal de comandos do bloco e finalmente a parte de coman dos para tratamento de exceções como indicado a seguir BAC 2016 online1 DECLAREOpcional Variáveis cursores exceções BEGINObrigatório Declarações SQL Declarações PLSQL EXCEPTIONOpcional Manusear ações a serem tomadas quando ocorrerem erros prédefinidos END Obrigatório Figura 2 Sintaxe para Banco de Dados PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 102 IMPORTANTE os comandos DECLARE BEGIN e EXCEPTION não são precedidas de ponto e vírgula Todos os outros comandos PLSQL incluindo o END exigem ponto e vírgula No geral um bloco pode ser anônimo declarado em qualquer ponto do código para ser executado no PLSQL Engine ou nomeado subprograma repre sentando subprogramas e podendo representar Procedures ou Functions com retorno de valores BAC 2016 online1 SINTAXE BÁSICA DO PLSQL Sabendo que o PLSQL é uma extensão do SQL geralmente as regras de sin taxe que são aplicadas no SQL são aplicadas no PLSQL Ao trabalharmos com a Linguagem SQL temos que nos atentar sobre alguns comandos reservados exemplos DATABASE SHOW USE VARCHAR entre outros Eles podem ser utilizados em alguns casos especiais utilizando entre aspas duplas USE comen tários podem estar entre e e atribuições são feitas com dois pontos e sinal de igual BAC 2016 online1 O controle do fluxo dos comandos pode utilizar as instruções básicas de lin guagens de programação IF Efetua um controle de ações baseado em condições GO TO Efetua um desvio incondicional para um ponto determinado no programa LOOP END LOOP Para repetições de ações sem uma condição imposta FOR Controla as repetições de ações utilizando um contador WHILE Controla as repetições de ações baseado em condições EXIT Termina uma repetição Visão Geral Sobre PlSql Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 103 ATRIBUTOS UTILIZADOS Os atributos utilizados para declarar um registro com base em uma coluna ou em uma coleção de colunas são muito úteis para a leitura e recuperação de infor mações nas estruturas PLSQL São identificados na seção DECLARE São eles TYPE é utilizado para declarar um registro baseado numa coluna de uma tabela ou visão Exemplo DECLARE Xnome YnomeTYPE BEGIN SELECT nome INTO Xnome FROM Y WHERE nome 1234 END ROWTYPE é utilizado para declarar um registro baseado numa cole ção de colunas de uma tabela ou visão Os campos dentro dos registros receberão seus tipos de dados das colunas referenciadas DECLARE idfuncionario YnomeROWTYPE BEGIN SELECT INTO idfuncionario FROM Y WHERE regfuncio nario 100000 END Desde a versão 7 o Oracle possui dois otimizadores por Regra Ruled Based Optimizer RBO e por Custo cost Based Optimizer CBO Você conhece os otimizadores Conhece os benefícios deles Figura 3 Apresentação de procedure PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 104 PROCEDURES Procedure é um subprograma que exe cuta uma ação Pode ser armazenada no Banco de Dados com um objeto per tencente ao esquema para execuções futuras A segurança de utilização da procedure é gerenciada pelo próprio Banco de Dados Para criar uma nova procedure utili zar o comando CREATE PROCEDURE Com uma lista de argumentos e defi nindo as ações que serão executadas pelo bloco PLSQL a sintaxe é CREATE OR REPLACE PROCEDURE nomeprocedimento parametro1 modo tipo1 Parametro2 modo2 tipo2 IS AS PLSQL Block Procedures Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 105 PARÂMETROS Existem dois tipos de parâmetros para a execução das procedures são Formais e Reais Os parâmetros formais são variá veis declaradas na lista de parâmetros da especificação de um subprograma Por exemplo CREATE PROCEDURE procedimento id number END procedimento Os parâmetros Reais são variáveis ou expressões utilizadas na chamada de um subprograma por exemplo procedimento 7788 A apresentação dos Parâmetros pode ser do tipo entrada saída ou entrada e saída simultâneos Para criar procedures com parâmetros os detalhes podem ser observados na Tabela 1 Tabela 1 Criando procedures com parâmetros IN OUT IN OUT Modo padrão Deve ser especificado Deve ser especificado Valores de entrada para subprogramas Valores retornados para o ambiente chamador dos subprogramas Valores de entrada para o subprograma e retornados para o ambiente chamador Parâmetros formais constantes Variável não inicializada Variável inicializada Pode ser atribuído mas não retorna um valor Não pode ser atribuído mas retorna um valor Pode atribuir e retornar um valor Figura 4 Os parâmetros são o caminho PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 106 Parâmetros reais podem ser constantes expressões literais e variáveis Deve ser uma variável Deve ser uma variável Fonte os autores Observe a seguir um exemplo de um parâmetro IN criando uma procedure com o argumento IN armazenando todas as informações de um novo empregado CREATE TABLE LOGTABLE USUARIO CHAR20 NOT NULL DATA DATE DEFAULT SYSDATE CREATE OR REPLACE PROCEDURE logexecution IS BEGIN INSERT INTO LOGTABLE USUARIO DATA VALUES USER SYSDATE END Agora caro a aluno a observe um exemplo de um parâmetro OUT retor nando um valor da procedure para o ambiente chamador utilizando o argumento OUT devolvendo informações sobre um funcionário CREATE OR REPLACE PROCEDURE PESQUISA ID IN REGISTROTYPE NOME OUT EMPENAMETYPE SAL OUT EMPSALTYPE COM OUT EMPCOMMTYPE IS BEGIN SELECT ENAME SAL COMM INTO NOME SAL COM FROM EMP WHERE EMPNO EMPID END PESQUISAEMP Procedures Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 107 Agora caro a aluno a execute da seguinte forma SQL VARIABLE ENOME VARCHAR230 SQL VARIABLE ESAL NUMBER SQL VARIABLE ECOM NUMBER SQL EXECUTE QUERYEMP7788 ENOME ESAL ECOM A partir disso retorna a mensagem PLSQL procedure successfully completed O passo seguinte é a apresentação do resultado da Procedure com parâmetro OUT conforme o código apresentado PRINT ESAL O resultado é apresentado da seguinte forma ESAL 3000 Por último veremos o exemplo de um parâmetro IN OUT passando um valor do ambiente chamador para a procedure e a procedure retornando um valor para o ambiente chamador Na sequência é apresentado o código da criação desta procedure PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 108 CREATE OR REPLACE PROCEDURE FORMATACNPJ PCNPJ IN OU VARCHAR2 IS BEGIN PCNPJ SUBSTRPCNPJ 12 PCNPJ SUBSTRPCNPJ 33 PCNPJ SUBSTRPCNPJ 63 PCNPJ SUBSTRPCNPJ 94 PCNPJ SUBSTRPCNPJ 132 END FORMATACNPJ A mensagem Procedure created é apresentada e a variável deve ser criada e carregada com o valor para a formatação observe a seguir VARIABLE CNPJ VARCAHAR220 BEGIN CNPJ 12345678901234 END EXECUTE FORMATACNPJ CNPJ A mensagem PLSQL procedure successfully completed é apresentada e para visualizar o CNPJ formatado veja a seguir Procedures Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 109 PRINT CNPJ O CNPJ será apresentado formatado da seguinte forma CNPJ 12345678901234 O método de passagem dos parâmetros também exige atenção e pode ocor rer de três formas Posicional lista dos parâmetros reais na mesma ordem dos parâmetros formais Nomeado lista dos parâmetros em ordem arbitrária porém relaciona dos pelos nomes Combinado alguns parâmetros posicionais e outros nomeados Os valores padrões podem ser informados diretamente no código e tam bém pode ser observado um modelo de passagem de parâmetros conforme o código Observe a seguir PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 110 CREATE OR REPLACE PROCEDURE adddept pname IN departmentsdepartmentnameTYPE DEFAULT unknown ploc IN departmentslocationidTYPE DEPAULT 1700 IS BEGIN INSERT INTO departmentsdepartmentid Departmentname locationid VALUES departmentsseqNEXTVAL pname ploc END adddept BEGIN adddept adddept TRAINING 2500 adddept ploc 2400 pname EDUCA TION adddept ploc 1200 END SUBPROGRAMAS Conforme a necessidade de trabalhar com eventos sincronizados ou de outra ordem as procedures podem trabalhar com subprogra mas automatizando e realizando esses eventos Um subprograma é de fácil manutenção melhora a segurança e integridade dos dados melhora a performance e também o código A gerência dos subprogramas que são procedimentos e funções armazenadas no Procedures Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 111 Banco de Dados acontece por meio dos comandos de criação e exclusão e são respectivamente CREATE OR REPLACE PROCEDUREFUNCTION DROP PROCEDUREFUNCTION Um exemplo interessante de um subprograma é da exclusão de um registro e instantaneamente a inclusão de um log de exclusão conforme apresentado no código que segue com o subprograma em destaque CREATE OR REPLACE PROCEDURE leaveemp2 pid IN employeesemployeeidTYPE IS PROCEDURE logexec IS BEGIN INSERT INTO logtable userid logdate VALUES USER SYSDATE END logexec BEGIN DELETE FROM employees WHERE employeeid pid Logexec END leaveemp2 As chamadas de execução podem acontecer tanto internamente em procedures quanto em blocos anônimos Veja as diferenças apresentadas no exemplo para aumentar o salário de empregados Seguem os exemplos nos destaques PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 112 1 No bloco anônimo DECLARE Vid NUMBER 163 BEGIN raisesalaryvid COMMIT END 2 Dentro de uma procedure CREATE OR REPLACE PROCEDURE processemp IS CURSOR empcursor IS SELECT employeeid FROM employees BEGIN FOR emprec IN empcursor LOOP Raisesalary emprecemployeeid END LOOP COMMIT END processemp Figura 6 Exceções devem ser programadas Procedures Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 113 EXCEÇÕES O gerenciamento das exceções em tempo de execução permite gerenciar qual quer tipo de exceção em tempo de execução e dessa forma permite propagar para o ambiente chamador ou tomar ações quando ocorrem RAISEAPPLICATIONERRORnumeroerro textoerro Numeroerro É o número do erro definido pelo usuário Deve estar entre 20000 e 20999 Textoerro É a mensagem definida pelo usuário As exceções também podem ser tratadas para problemas não contemplados pelo ORACLE Para isso é necessário efetuar a declaração da exceção com um nome e cláusula EXCEPTION e ainda é necessário associála com um número de erro utilizando o comando PRAGMA EXCEPTIONINIT PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 114 Executando uma operação no Banco de Dados como ROLLBACK ou cus tomizando uma mensagem de erro para uma exceção ORACLE pode tratar estas exceções no bloco EXCEPTION No exemplo que segue o retorno da EXCEPTION ocorre ao tentar excluir um funcionário não cadastrado DELETE FROM EMP WHERE empno vempno IF SQLNOTFOUND THEN RAISEAPPLICATIONERROR20200 Func nao existe END IF COMMIT WORK END excluifuncionario Com o bloco EXCEPTION incluído no código o mesmo exemplo apresentado ficaria da seguinte forma CREATE OR REPLACE PROCEDURE excluifuncionario vempno IN empempnoTYPE IS BEGIN DELETE FROM EMP WHERE empno vempno COMMIT WORK EXCEPTION WHEN NODATAFOUND THEN RAISEAPPLICATIONERROR20200 Func nao existe END excluifuncionario Procedures Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 115 EXECUTAR UMA PROCEDURE De qualquer ambiente PLSQL basta simplesmente chamar a procedure com uma chamada direta em um bloco anônimo como no exemplo DECLARE Vempno NUMBER 7654 BEGIN Excluifuncionario vempno END A execução de outra procedure ocorre no seguinte exemplo CREATE PROCEDURE processafuncionario vempno IN empempnoTYPE IS BEGIN excluifuncionario vempno END Observação Importante ao executar o PLSQL dos ambientes SQL Plus ou do SQL DBA é necessário utilizar o comando EXECUTE E para entrar com valores via SQL Plus é necessário utilizar o comando ACCEPT e substituir o parâmetro de entrada da procedure pela variável do ACCEPT iniciado pela string conforme o exemplo ACCEPT pempno PROMPT Entre com o numero do funcio nario EXECUTE excluifuncionario pempno PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 116 De acordo com o ambiente chamador é possível executar procedures de um outro usuário schema ou de um outro Banco de Dados No exemplo a execução de uma procedure de outro usuário EXECUTE williamexcluifuncionario 7654 No exemplo a execução de uma procedure de outro Banco de Dados EXECUTE williamexcluifuncionario 7654pr No caso em que uma procedure contenha vários argumentos existem três méto dos para especificar seus valores 1 Posicional Lista valores na ordem em que foram declarados exemplo EXECUTE novosfuncionariosWILLIAM ANALISTA 7566 3000 2 Nomeado Lista valores associando cada valor com o nome do argu mento utilizado na sintaxe especial exemplo EXECUTE novosfuncionarios vempsal 3000 vmgr no 7566 vmpname LUIZ vempjob ANALISTA 3 Combinado Lista os primeiros valores de acordo com a posição e o res tante nomeando exemplo Procedures Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 117 EXECUTE novosfuncionarios LUIZ ANALISTA v empsal 3000 vmgrno 7566 As maneiras de executar uma função são as mesmas utilizadas para executar uma procedure Mas no caso de uma função o retorno é de um único valor Executando uma função de um bloco PLSQL segue exemplo DECLARE vempno NUMBER 7654 vsal NUMBER BEGIN Vsal pesquisasalario vempno END Ao executar uma função de uma procedure temos o seguinte código de exemplo CREATE PROCEDURE processaemp vempno IN empemp noTYPE IS vsal NUMBER BEGIN Vsal pesquisasalario vempno END PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 118 Uma observação importante sobre o SQL PLUS é que o valor retornado de uma função é armazenado em uma variável global e a execução nessa interface segue o seguinte exemplo ACCEPT pempno PROMPT Entre com o numero do funcio nario VARIABLE gsal NUMBER EXECUTE gsal pesquisasalariopempno PRINT gsal Para verificar o status de uma procedure SELECT OBJECTNAME OBJECTTYPE STATUS FROM USEROBJECTS WHERE OBJECTNAME nomedaprocedure Para visualizar o códigofonte de uma procedure SELECT TEXT FROM USERSOURCE WHERE NAME nomedaprocedure ORDER BY LINE Para eliminar uma procedure SHOW ERRORS PROCEDURE nomedaprocedure Para eliminar uma procedure DROP PROCEDURE nome da procedure Figura 7 Funções Functions Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 119 FUNCTIONS Funções são blocos PLSQL nomeados que retornam um valor podem ser armaze nadas no Banco de Dados Uma função é chamada como parte de uma expressão As vantagens das funções em expressões SQL são 1 Estender o SQL para atividades mais complexas 2 Aumentar a eficiência para filtrar dados na cláusula WHERE 3 Manipular dados do tipo string As funções são amplamente utilizadas e a ligação com elas são os comandos e cláusulas que auxiliam manipulações e nos resultados Com isso as funções podem ser utilizadas 1 Juntamente ao comando SELECT 2 Para valores no comando INSERT 3 Para valores no comando UPDATE 4 Nas cláusulas de filtro WHERE e HAVING 5 Nas cláusulas de resultados CONNECT BY START WITH ORDER BY e GROUP BY PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 120 Algumas restrições devem ser observadas para usar as funções definidas pelo usuário pois elas devem ser armazenadas no Banco de Dados aceitar somente parâmetros do tipo IN aceitar somente tipos de dados válidos no SQL não uti lizando tipos de dados PLSQL e devem retornar tipos de dados válidos no SQL As restrições na chamada das funções tratam de alguns detalhes importan tes com isso as funções chamadas 1 Com SQL não podem conter comandos DDL 2 Por um UPDATE ou DELETE em uma tabela T não podem conter coman dos DML na mesma tabela 3 Por qualquer comando DML não podem fazer uma pesquisa na mesma tabela 4 A partir de comandos SQL não podem conter comandos que finalizam uma transação A Sintaxe da Function é muito parecida com a sintaxe e as restrições de uma Procedure pois possuem a mesma construção Apresentamos a seguir o modelo CREATE OR REPLACE FUNCTION functionname parameter1 mode1 datatype1 Parameter2 mode2 datatype2 RETURN datatype IS AS PLSQL Block Functions Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 121 Segue também um exemplo do código da criação e da execução da function CREATE OR REPLACE FUNCITION GETSAL EMPID EMPEMPNOTYPE RETURN NUMBER IS VSAL EMPSALTYPE BEGIN SELECT SAL INTO VSAL FROM EMP WHERE EMPNO EMPID RETURN VSAL END Após a mensagem emitida Function created a função pode ser executada por meio do seguinte código SELECT GETSAL7788 FROM DUAL O resultado é apresentado da seguinte forma GETSAL7788 3000 PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 122 Na interface SQLPLUS a execução da function é diferenciada conforme apresentada a seguir VARIABLE SALARIO NUMBER EXECUTE SALARIO GETSAL7788 Após a mensagem emitida PLSQL function successfully completed a fun ção pode ser executada por meio do código PRINT SALARIO O resultado é apresentado da seguinte forma SALARIO 3000 A exclusão da function é simples Seu comando possui a seguinte estrutura DROP FUNCTION nome da function Comparando as Functions e Procedures podemos observar em poucos detalhes como a diferença fundamental entre ambas é o fato de funções serem caracteri zadas pelo retorno de valores como parâmetros de saída As functions e procedures agregam os benefícios da fácil manutenção a melhora na segurança e integridade dos dados a melhora na performance e também no código A comparação entre Procedure e Function aborda detalhes de como é a apli cação de cada uma e também a sua forma de retornar informação Esses detalhes podem ser observados no quadro a seguir Functions Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 123 Quadro 2 Comparação de aplicabilidade entre Procedure e Function PROCEDURE FUNCTION Executa como um comando PLSQL É chamada como parte de uma expressão Não existe a cláusula RETURN no cabeçalho Precisa conter o cláusula RETURN Pode retornar nenhum um ou mais valores Retorna apenas um valor É necessário que o último comando seja o RETURN Fonte os autores Para verificar o status de uma função SELECT OBJECTNAME OBJECTTYPE STATUS FROM USEROBJECTS WHERE OBJECTNAME nomedafuncao Para visualizar o códigofonte de uma função SELECT TEXT FROM USERSOURCE WHERE NAME nomedafuncao ORDER BY LINE Para eliminar uma função SHOW ERRORS FUNCTION nomedafuncao Outra forma de eliminar uma função DROP FUNCTION nomedafuncao Figura 8 Ilustração de pacote de tipos itens e subprogramas PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 124 PACKAGES Packages são grupos lógicos de tipos de PLSQL itens e subprogramas Possuem duas partes Especificação specification e Corpo body e não podem ser cha mados diretamente ou parametrizados Entretanto é importante destacar que permitem ao servidor colocar ou ler múltiplos objetos na memória de uma única vez A especificação do package possui variáveis públicas procedures públicas e a declaração da procedure Os componentes do corpo do package são compostos pela definição de procedu res por procedures públicas ou privadas e também por variáveis locais ou privadas Os passos básicos para desenvolver um package são similares aos de desen volver uma procedure Packages Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 125 É recomendado salvar os textos de especificação e corpo em arquivos dife rentes para facilitar alterações posteriores O desenvolvimento do package utiliza comandos como CREATE PACKAGE CREATE PACKAGE BODY além de poder chamar construções públicas dentro de pacotes em ambiente Oracle A sintaxe utilizada na criação da especificação e do corpo do package é apre sentada nos códigos seguintes e o REPLACE é uma opção para incluir quando o pacote já existir Segue a sintaxe CREATE OR REPLACE PACKAGE PACKAGENAME IS AS DECLARAÇÃO DE ITENS E TIPOS PÚBLICOS ESPECIFICAÇÕES DE SUBPROGRAMAS END PACKAGENAME CREATE OR REPLACE PACKAGE BODY PACKAGENAME IS AS PRIVATE TYPE AND ITEM DECLARATIONS SUBPROGRAM BODIES END PACKAGENAME Da mesma forma para exemplificar a criação do package serão apresentados os códigos de um exemplo que tratam de comissão Estão apresentados os códi gos da especificação e do corpo Também é importante observar que é possível chamar uma função ou procedure no mesmo package assim como na proce dure criada RESETCOMM que chama a function VALIDATECOMM que foi criada no mesmo PACKAGE segue o exemplo PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 126 CREATE OR REPLACE PACKAGE commpackage IS GCOMM NUMBER 010 inicializando com 010 PROCEDURE RESETCOMM PCOMM IN NUMBER END commpackage Corpo do Package CREATE OR REPLACE PACKAGE BODY commpackage IS FUNCTION VALIDATECOMMPCOMM IN NUMBER RETURN BOOLEAN IS VCOMM NUMBER BEGIN SELECT MAXCOMM INTO VCOMM FROM EMP IF PCOMM VCOMM THEN RETURNFALSE ELSE RETURNTRUE END IF END VALIDATECOMM PROCEDURE RESETCOMM PCOMM IN NUMBER IS BEGIN IF VALIDATECOMMPCOMM THEN GCOMM PCOMM ELSE RAISEAPPLICATIONERROR 20210 Comissão inválida END IF END RESETCOMM END commpackage Packages Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 127 Figura 9 Imagem da marca Oracle Quadro 3 Alguns packages oferecidos pela Oracle PACKAGE DESCRIÇÃO DBMSOUTPUT Saída de informação de procedure ou funções armazenadas DBMSDDL Compila procedure funções e packages Obtém estatísticas de performance com o comando analyse DBMSSESSION Altera a sessão do usuário Define regras para o usuário e reinicializa o estado de um package DBMSPIPE Envia mensagem do Banco de Dados para a aplicação DBMSJOB Gerencia o agendamento de tarefas no Banco de Dados UTLFILE Trabalha com arquivos textos UTLTCP Habilita o Oracle a se comunicar com outros servidores Fonte os autores PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 128 Um package também pode não ter o BODY e pode ser executado normalmente veja no exemplo a seguir CREATE OR REPLACE PACKAGE globalconsts IS mile2kilo CONSTANT NUMBER 16093 kilo2mile CONSTANT NUMBER 06214 yard2meter CONSTANT NUMBER 09144 meter2yard CONSTANT NUMBER 10936 END globalconsts EXECUTE DBMSOUTPUTPUTLINE 20 miles 20 Globalconstsmile2kilo kmꞌ Em um package também pode haver sobrecarga overloading que habilita a utilização de um mesmo nome para várias procedures ou functions dentro do mesmo package Nesse caso é necessário ter parâmetros formais que diferen ciem o método mas somente é possível a sobrecarga de métodos dentro do package por exemplo CREATE OR REPLACE PACKAGE overpack IS PROCEDURE adddept pdeptno IN departmentsdepartmentidTYPE pname IN departmentsdepartmentnameTYPE EFAULT unknown ploc IN departmentslocationidTYPE DEFAULT 0 PROCEDURE adddept pname IN departmentsdepartmentnameTYPE DEFAULT ꞌunknown ploc IN departmentslocationidTYPE DEFAULT 0 END overpack Packages Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 129 CREATE OR REPLACE PACKAGE BODY overpack IS PROCEDURE adddept pdeptno IN departmentsdepartmentidTYPE pname IN departmentsdepartmentnameTYPE DEFAULT unknown ploc IN departmentslocationidTYPE DEFAULT 0 IS BEGIN INSERT INTO departments departmentid departmentname locationid VALUES pdeptno pname ploc END adddept PROCEDURE adddept pname IN departmentsdepartmentnameTYPE DEFAULT unknown ploc IN departmentslocationidTYPE DEFAULT 0 IS BEGIN INSERT INTO DEPARTMENTSdepartmentid departmentname locationid VALUES departmentsseqNESTVAL pname ploc END adddept END overpack Para excluir um package é necessário excluir a especificação e o corpo Para isso é utilizado o comando DROP PACKAGE nome para a especificação e DROP PACKAGE BODY nome para o corpo do package PROGRAMAÇÃO EM SQL Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 IV U N I D A D E 130 CONSIDERAÇÕES FINAIS Prezadoa alunoa Nesta unidade foi possível reunir conceitos relacionados a Banco de Dados de nível mais avançado com a apresentação de códigos e exem plos necessários para a exploração do assunto O nosso estudo promoveu a compreensão de que subprogramas são blocos PLSQL com nomes e declarados como procedure ou functions podendo ser blocos anônimos e o subprograma pode ser chamado de diferentes ambientes Dentre os benefícios dos subprogramas constam o fácil gerenciamento a melhora na segurança e na integridade dos dados a melhora na performance do sistema e clareza no código Também as procedures que são subprogramas e executam ações programadas podem ser criadas compiladas e armazenadas no Banco de Dados Parâmetros são utilizados para enviar dados do ambiente chamador para a procedure de três formas IN OUT e IN OUT Um subprograma pode ser declarado dentro de outro contudo procedu res somente podem ser chamadas pelos ambientes que possuem suporte e uma procedure pode ser facilmente excluída com o comando DROP PROCEDURE Uma função igualmente à procedure é um bloco PLSQL que retorna um valor É criada por meio do comando CREATE FUNCTION e pode ser chamada por um comando SQL como parte de uma expressão Para a função ser usada como comandos SQL deverá estar armazenada no BD e pode ser removida facilmente por meio do comando DROP FUNCTION É comum a utilização de procedures para executar uma ação e de functions para cálculos de valores Já uma package melhora a segurança organização gerenciamento e perfor mance Nela podem estar juntos grupo de procedures e funções e assim garantindo a segurança de acesso ao package Considerações Finais Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 131 Encerrando temos as triggers Como efetuar a sua criação os eventos que podem ser associados ao trigger seu gerenciamento e a possibilidade de utili zação em auditorias Mais adiante serão detalhadas as questões de controle de acesso ao Banco de Dados e aos códigos de programação SQL de forma a oferecer conhecimen tos úteis para o dia a dia na administração desses processos de Bancos de Dados 132 1 Dada a tabela valorproduto e a trigger verificavalor abaixo CREATE TABLE valorproduto codigo NUMBER4 valoranterior NUMBER72 valornovo NUMBER72 CREATE OR REPLACE TRIGGER verificavalor BEFORE UPDATE OF valor ON produto FOR EACH ROW BEGIN INSERT INTO valorproduto VALUES OLDcodigo OLDvalor NEWvalor END CODIGO VALORANTERIOR VALORNOVO 3 58 54 Incluir na tabela valorproduto os campos codigousuario VARCHAR2 30 datalog DATE Alterar a trigger verificavalor para que também sejam incluídos na tabela va lorproduto a data do sistema no momento da atualização e o nome do usuário que realizou a alteração no campo valor 133 2 Criar uma função para apresentar o fatorial de um número a ser informado no comando SELECT Lembrete x x x1 No exemplo SELECT fatorial 3 FROM dual O retorno apresentado deverá ser FATORIAL3 6 3 Crie uma tabela chamada CIRCULO com as seguintes colunas RAIO NUMBER2 AREA NUMBER82 CREATE TABLE CIRCULO RAIO NUMBER2 AREA NUMBER82 Desenvolva um programa em PLSQL para inserir os raios com valores 1 a 10 e as respectivas áreas na tabela criada utilizando WHILE ou FOR 4 Conforme tabela de erros abaixo segue ERRO NOME DESCRIÇÃO ORA 00001 DUPVALONIN DEX Tentativa de armazenar valor duplicado em uma coluna que possui chave primária ou única ORA 01403 NOTLOGGEDON Tentativa acessar o banco de dados sem estar conec tado a ele ORA 01403 NODATAFOUND Ocorre quando um comando SELECT INTO não retorna nenhuma linha ORA 01422 TOOMANYROWS Ocorre quando um comando SELECT INTO retorna mais de uma linha ORA 01476 ZERODIVIDE Tentativa de dividir qualquer número por zero Fonte os autores 134 Elabore um programa em PLSQL que faça o tratamento de exceção na tabela ALUNO cujo o código de criação é CREATE TABLE ALUNO RA NUMBER9 NOME VARCHAR230 INSERT INTO ALUNO VALUES 1MARIA No programa em PLSQL Informe a tentativa de inserir valor duplicado numa coluna que é chave primária 5 Utilizando as tabelas de exemplo citadas nesta unidade faça uma procedure para cadastrar um departamento O departamento deve ser incrementado por meio de um select no maior dept 10 135 O DataBase Administrator DBA é o Administrador de Banco de Dados e o gerente res ponsável pelos sistemas de Banco de Dados É o responsável pelos privilégios de acesso e de classificação de usuários do sistema conforme as determinações das políticas de segurança O DBA possui uma conta direitos de acesso próprio e acessa a toda e qualquer instância do Banco de Dados Sua função é manter o Banco íntegro tanto em relação às contas de acesso quanto à consistência dos dados e ao andamento da performance do banco Outra função importante do DBA é a de se encarregar do tunning do Banco de Dados referente à performance de operação do banco Responsável também pela cópia de se gurança dos dados e pela criação de índices triggers stored procedures e demais estru turas necessárias Toda esta estrutura e cessão de direitos demonstra a grandeza da segurança do sistema de gerenciamento de Banco de Dados em especial o Oracle Os tipos de segurança devem levar em consideração os perfis de segurança de acesso aos dados que devem ser traçados Para proteger o BD e atingir as medidas de seguran ça necessárias ações devem ser tomadas em diversos níveis Físico torna o sistema fisicamente seguro contra entradas de intrusos Humano os controles dos acessos dos usuários são cuidadosamente estudados Sistema operacional a fragilidade na segurança do SO pode ser uma porta de acesso nãoautorizado ao Banco de Dados Sistema de BD os usuários de sistemas de BD devem ter autorização de acesso somente nas porções limitadas e necessárias Outros usuários deverão ser habili tados a emitir consultas com a proibição de modificar dados A metodologia e tuning da Oracle é focada no design da aplicação e no tuning de con sultas SQL mesmo antes de analisar qualquer tipo de problema relacionado à configura ção do Banco de Dados ou à ação do DBA A otimização de uma consulta determina a melhor estratégia ou forma de execução por parte do Banco de Dados O otimizador do Oracle é eficiente na escolha e faz por exemplo uma análise se usará um índice ou não para uma determinada consulta e quais as técnicas de JOIN usar na junção de tabelas Estas decisões impactam diretamente na performance de um SQL É muito importante que os conceitos do otimizador do Oracle e de tuning sejam co nhecidos pelos desenvolvedores Tal conhecimento ajudará a escrever consultas mais eficientes rápidas e que não impactarão nas atividades do DBA Fonte os autores MATERIAL COMPLEMENTAR A Oracle disponibiliza um artigo em inglês com as principais diferenças entre o Oracle e o MySQL intitulado Database SQL Developer Supplementary Information for MySQL Migrations Nele estão as descrições dos objetos migrações e a comparação de comandos Acesse o link a seguir httpdocsoraclecomcdE1215101doc150e12155oraclemysqlcompared htmCHDIIBJH REFERÊNCIAS Referências online 1 Em httpdocslidecombrdocumentsapostilaintroducaoaooracle proce duraloptionparaamigoshtml Acesso em 25 nov 2016 137 GABARITO 1 ALTER TABLE valorproduto ADD codigousuario VARCHAR230 data log DATE CREATE OR REPLACE TRIGGER verificavalor BEFORE UPDATE OF valor ON produto FOR EACH ROW BEGIN INSERT INTO valorproduto VALUES OLDcodigo OLDvalor NEWvalor user sysdate END 2 CREATE OR REPLACE FUNCTION fatorial pn IN NUMBER RETURN number IS BEGIN IF pn 1 THEN RETURN 1 ELSE RETURN pn fatorial pn1 END IF END fatorial 3 GABARITO 139 SOLUÇÃO 1 WHILE DECLARE PI CONSTANT NUMBER97 31415927 RAIO NUMBER2 AREA NUMBER82 BEGIN RAIO 1 WHILE RAIO 10 LOOP AREA PIPOWERRAIO2 INSERT INTO CIRCULO VALUES RAIOAREA RAIO RAIO1 END LOOP END OU SOLUÇÃO 2 FOR DECLARE PI CONSTANT NUMBER97 31415927 RAIO NUMBER2 1 AREA NUMBER82 BEGIN FOR CONTADOR IN 110 LOOP AREA PIPOWERRAIO2 INSERT INTO CIRCULO VALUES RAIOAREA RAIO RAIO 1 END LOOP END GABARITO 4 DECLARE BEGIN INSERT INTO ALUNO VALUES 1ANTONIO EXCEPTION WHEN DUPVALONINDEX THEN DBMSOUTPUTPUTLINE Já existe um aluno com este RA END 5 CREATE OR REPLACE PROCEDURE INSDEPT PDNAME DEPTDNAMETYPE PLOC DEPTLOCTYPE IS VDEPTEMP EMPDEPTNOTYPE BEGIN SELECT MAXDEPTNO INTO VDEPTEMP FROM DEPT EXCEPTION WHEN NODATAFOUND THEN VDEPTEMP 0 END VDEPTEMP VDEPTEMP 10 INSERT INTO DEPT VALUES VDEPTEMP PDNAME PLOC END UNIDADE V Professor Me William Roberto Pelissari CONTROLANDO ACESSOS Objetivos de Aprendizagem Gerenciar usuários Controlar os papéis para gerenciar acesso aos dados Usar comando DCL para controlar acesso a privilégios de sistema e de objetos Utilizar instruções para gerenciar direitos de acesso a Banco de Dados Plano de Estudo A seguir apresentamse os tópicos que você estudará nesta unidade Triggers gatilhos Segurança do Banco Controle de acesso ao usuário Criar e acessar vínculos de Banco de Dados Gerenciando Senhas e Recursos Gerenciando Usuários Auditoria INTRODUÇÃO Olá caroa alunoa Com o valor agregado aos dados pelas empresas é possí vel mensurar o grau de importância que os bancos de dados recebem para que a proteção devida seja atribuída a eles Os Bancos de Dados precisam ser especial mente protegidos já que guardam tantas possibilidades e detalhes da empresa Em relação ao armazenamento tratar e conservar informações é um dos assuntos essenciais em Banco de Dados Por isso o tema abordado toma grandes proporções pois é possível imaginar que essa seja uma questão difícil de assimilar Entretanto o que deve ser levado em consideração é que os dados ou infor mações de uma empresa podem ser tão importantes para o andamento dos processos cotidianos que impactam diretamente no sucesso ou no seu fracasso Dessa forma a segurança se torna uma questão tão importante quanto os pró prios dados do sistema Ou seja desde a criação da estrutura para receber os dados até os perfis e os níveis de acesso tudo deve ser devidamente pensado e estruturado para que a garantia de uso e segurança de acesso sejam considerados O Administrador de Banco de Dados DBA é o profissional responsável pelo gerenciamento de um sistema de Banco de Dados Responsável também pela concessão de privi légios é ele quem libera os acessos e classifica os usuários do sistema conforme as determinações das políticas de segurança O Banco de Dados Oracle segue em grande evolução e cada vez mais se torna autogerenciável por meio de novos recursos adicionados que o tornam capaz de realizar certas tarefas sem a necessidade da interação humana Tudo deve ser planejado dentro da política de segurança da empresa e programado e concor dância com o DBA Gerência e até a Governança de TI conforme o tamanho da empresa e sua divisão setorial Nesta unidade serão abordados os diversos pontos que tratam da segurança no Banco de Dados Esses pontos tratam diretamente da administração e da pro teção dos dados que em primeira instância contam com o gerenciamento de um DBA para a garantia de segurança e disponibilidade Bom estudo Introdução Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 143 CONTROLANDO ACESSOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 V U N I D A D E 144 TRIGGERS GATILHOS Um trigger ou gatilho é um bloco PLSQL Procedural LanguageStructured Query Language associado a uma tabela Executa implicitamente de acordo com um evento e podem ser de aplicação ou de Banco de Dados Desenvolver um gatilho de Banco de Dados é solicitar a execução de um bloco PLSQL somente quando um comando de manipulação específico é executado em uma certa tabela Algumas diferenças elementares entre gatilho e procedure procedimento são expressadas na tabela 1 Tabela 1 Diferenças entre Triggers e Procedures TRIGGERS PROCEDURE Definido com CREATE TRIGGER Definido com CREATE PROCEDURE Código na view USERTRIGGER Código na view USERSOURCE Chamada automática conforme o evento Necessário fazer a chamada COMMIT SAVEPOINT e ROLLBACK não são permitidos COMMIT SAVEPOINT e ROLLBACK são permitidos Fonte os autores O uso de trigger é recomendado para desenvolver regras complexas de validação de dados que não podem ser criadas com as constraints Também é possível fazer replicação de dados síncrona e ainda é utilizada para fazer auditorias A decisão de qual o momento em que o evento do trigger deve ser execu tado deve ser definido para que ocorra antes ou depois do disparo do evento As opções estão apresentados na tabela 2 Triggers Gatilhos Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 145 Tabela 2 Tabela de partes e valores de um Trigger PARTE DESCRIÇÃO VALORES POSSÍVEIS Momento de Execução Quando o trigger é disparada em relação ao evento Tabelas BEFORE ou AFTER Views INSTEAD OF Evento Qual operação de manipulação dos dados na tabela causará o disparo do trigger INSERT UPDATE DELETE Tipo Quantas vezes o corpo do trigger será executado Para cada registro ou para o comando Cláusula Causa uma restrição para a exe cução do trigger WHEN Corpo Quais ações que o trigger exe cutará Bloco de código PLSQL Fonte os autores A sintaxe do trigger é mostrada a seguir para ilustrar como são utilizados na codificação de um sistema de Banco de Dados CREATE OR REPLACE TRIGGER triggergatilho timing evento1 OR evento2 OR evento3 ON tabela gatilhocorpo Um exemplo interessante é a proibição de incluir um funcionário após o horá rio comercial mostrado a seguir em que é verificado o horário de tentativa de acesso de um funcionário de acordo com os horários permitidos CONTROLANDO ACESSOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 V U N I D A D E 146 CREATE OR REPLACE TRIGGER SECUREEMP BEFORE INSERT ON EMP BEGIN IF TOCHARSYSDATE DY IN SATSUN OR TONUMBERTOCHARSYSDATE HH24 NOT BETWEEN 8 AND 18 THEN RAISEAPPLICATIONERROR20500 Não é possível incluir um funcionário fora do horário comercial END IF END Outro exemplo da combinação de eventos dentro de um trigger é mostrado a seguir em que são acrescentadas ações de acordo com a sequência de eventos gerados pelos gatilhos CREATE OR REPLACE TRIGGER secureemp BEFORE INSERT OR UPDATE OR DELETE ON emp BEGIN IF TOCHAR SYSDATE DY IN SAT SUN OR TOCHAR SYSDATEHH24 NOT BETWEEN 08 AND 18 THEN IF DELETING THEN RAISEAPPLICATIONERROR 20502 Deletar no horário comercial ELSE IF INSERTING THEN RAISEAPPLICATIONERROR 20500 Inserir no horário comercial Triggers Gatilhos Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 147 ELSE IF UPDATING SALARY THEN RAISEAPPLICATIONERROR 20503Atualizar no horário cial ELSE RAISEAPPLICATIONERROR 20504Atualizar no horário cial END IF END IF END Criando um trigger para cada registro a ser inserido ou que tenha seus dados atualizados conforme o código CREATE OR REPLACE TRIGGER restrictsalary BEFORE INSERT OR UPDATE OF salary ON emp FOR EACH ROW BEGIN IF NOT NEWjob IN ADPRES ADVP AND NEWsal15000 THEN RAISEAPPLICATIONERROR 20202 Valor não autorizado END IF END Usando os qualificadores OLD e NEW o código seguinte registra uma auditoria na manutenção de registros gravando os velhos e os novos valores conforme indicado no exemplo CONTROLANDO ACESSOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 V U N I D A D E 148 CREATE OR REPLACE TRIGGER auditemp AFTER DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW BEGIN INSERT INTO AUDITEMP USERNAME DATA EMPNO OLDENAMENEWENAME OLDJOB NEWJOB OLDSAL NEWSAL VALUES USER SYSDATE OLDEMPNO OLDENAME NEWENAME OLDJOB NEWJOB OLDSAL NEWSAL END Um exemplo de código para restringir a ação do trigger é mostrado da seguinte maneira CREATE OR REPLACE TRIGGER derivecommpct BEFORE INSERT OR UPDATE OF SAL ON emp FOR EACH ROW WHEN NEW JOB SALESMAN BEGIN IF INSERTING THEN NEWCOMM 0 ELSEIF OLDCOMM IS NULL THEN NEWCOMM 0 ELSE NEWCOMM OLDCOMM 005 END IF END Um gatilho pode ter o seu funcionamento habilitado ou desabilitado Isso é muito útil quando for necessário executar algumas instruções que não necessitem que os triggers sejam executados Dessa forma os comandos são Segurança do Banco Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 149 ALTER TRIGGER nome DISABLEENABLE ALTER TABLE nome DISABLEENABLE ALL TRIGGERS ALTER TRIGGER nome COMPILE Para remover um trigger é só utilizar o seguinte comando mostrado DROP TRIGGER nome Contudo é necessário destacar que ao remover uma tabela seus gatilhos serão automaticamente excluídos SEGURANÇA DO BANCO O responsável pelo gerenciamento de um sistema de Banco de Dados é o Administrador de Banco de Dados DBA que é a pessoa capaz de conceder privilégios de acesso e classificação de usuários do sistema conforme as deter minações das políticas estabelecidas para segurança O DBA possui direitos de acesso a toda e qualquer instância do Banco de Dados com sua conta própria O seu papel é garantir a integridade do Banco seja em relação às contas de acesso e também à consistência dos dados além da performance do Banco encarregandose do tunning do Banco Também admi nistra as cópias de segurança dos dados a criação de índices triggers stored procedures e outros CONTROLANDO ACESSOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 V U N I D A D E 150 Quando é necessário o acesso de um novo usuário ou a modificação de perfis de acesso de existentes o DBA cria a nova conta atribui as permissões necessá rias ou modifica as configurações de contas existentes mantendo assim o acesso e controle das ações relativas a segurança de acesso concentradas em uma única pessoa com perfil adequado CONTROLE DE ACESSO AO USUÁRIO O DBA determina o controle do acesso do usuário seus privilégios como usuá rio em relação ao sistema e áreas com permissão de acesso e trabalho mantendo sob controle as ações dos colaboradores Esses privilégios tratam da segurança do Banco de Dados seja em sistema ou em dados bem como o acesso ao Banco de Dados manipulação de conteú dos no Banco de Dados e ainda manipulação de schemas que tratam das coleções de objetos como tabelas visões e sequências Com cerca de 100 tipos de configurações de acesso e manipulação do Banco de Dados o DBA tem o mais alto nível de acesso ao sistema sendo capaz de ações como criar novos usuários remover usuários remover qualquer tabela e fazer backup das tabelas ORACLE 2004 online1 Garantir a segurança da informação é fazer com que as informações perma neçam confidenciais íntegras e disponíveis Os Sistemas de Gerenciamento de Banco de Dados podem garantir os princípios da segurança por meio das nuvens Controle de Acesso ao Usuário Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 151 CRIANDO USUÁRIOS O DBA cria usuários utilizando a instrução CREATE USER que permite a identi ficação de um usuário e sua senha de acesso ao sistema como mostramos a seguir Sintaxe CREATE USER usuário IDENTIFIED BY senha Exemplo CREATE USER funcionarioscott IDENTIFIED BY senhapadraotiger User created CONCEDENDO PRIVILÉGIOS Quando o usuário é criado o DBA pode conceder a ele privilégios de sistemas específicos por exemplo um desenvolvedor de aplicações pode ter privilégios de sistema para criar sessões tabelas gatilhos procedimentos usando comandos como CREATE SESSION CREATE TABLE CREATE SEQUENCE CREATE VIEW CREATE PROCEDURE e CREATE TRIGGER Ao conhecer todos os privilégios que um sistema possui o DBA pode con ceder a um usuário os privilégios individuais de sistemas específicos usando comandos como indicado a seguir GRANT create session create table create sequence create view TO scott CONTROLANDO ACESSOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 V U N I D A D E 152 ROLE FUNÇÃO A alocação de privilégios pode ocorrer de duas formas sendo a primeira sem atribuição direta para cada usuário e a segunda com privilégios em uma atribui ção podendo agrupar usuários assim como a alocação de privilégios para todos os gerentes Role é um papel criado pelo DBA para facilitar o gerenciamento de direitos Torna mais fácil garantir e revogar direitos para um grupo de usuários que pos suam as mesmas regras Role O DBA cria uma role com o comando CREATE ROLE Para criar uma role e dar direitos devem ser executados os seguintes passos É possível criar uma atribuição usando o comando CREATE ROLE MANAGER e conceder privilégios a uma atribuição com o comando GRANT CONNECT RESOURCE TO MANAGER além de poder conceder uma atri buição aos usuários por meio do comando GRANT MANAGER TO CURSO Um usuário pode ter várias roles associadas O padrão é que todas as roles associadas sejam habilitadas já no logon sem a necessidade de senha Aos usuários podem ser aplicadas uma ou mais roles padrões com o comando ALTER USER As roles padrões somente podem ser especificadas após a operação de grant As demais roles deverão ser habilitadas na sessão do usuário Uma role pode ser habilitada ou desabilitada por meio da procedure DBMS SESSIONSETROLE As roles padrões já são habilitadas no login do usuário e uma senha poderá ser necessária para habilitar uma role para o usuário Para aplicar uma role pode mos ter instruções como SET ROLE GERENTE IDENTIFIED BY OOX SET ROLE ALL EXCEPT GERENTE e SET ROLE VENDEDOR Ao remover uma role deve ser observada se a opção é do usuário com o comando REVOKE ou do sistema com o comando DROP Nesse caso podemos utilizar ins truções como REVOKE GERENTE FROM BOB e DROP ROLE GERENTE As chamadas visões do data dictionar podem ser DBAROLES DBAROLEPRIVS ROLEROLEPRIVS Controle de Acesso ao Usuário Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 153 DBASYSPRIVS ROLESYSPRIVS ROLETABPRIVS SESSIONROLES ALTERAÇÃO DE SENHA Para alterar a senha do usuário o DBA cria uma conta e a inicia com uma senha padrão A senha pode ser alterada com o comando ALTER USER Por exem plo podemos utilizar comando ALTER USER pessoa IDENTIFIED BY idade PRIVILÉGIOS DE OBJETO Os privilégios podem ser atribuídos conforme a necessidade e suas possibilida des de acesso podendo variar de um objeto para outro O proprietário de um objeto detém todos os privilégios sobre ele e pode repassar privilégios específi cos de seus objetos para outros usuários Na tabela 3 está a relação dos objetos e seus respectivos privilégios possíveis Tabela 3 Tabela privilégios de objetos Privilégio Table View Sequence Procedure ALTER DELETE EXECUTE INDEX INSERT REFERENCES SELECT UPDATE Fonte os autores CONTROLANDO ACESSOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 V U N I D A D E 154 O usuário que recebe o direito pode repassar para os demais mas o direito garantido é revogado caso o concessor do direito com WITH GRANT OPTION retire a concessão Não pode ser usado para um role a opção WITH GRANT OPTION mas para simbolizar o acesso público pode ser usado PUBLIC A sintaxe de privilé gios de objetos pode ser observada a seguir GRANT privobjeto columns ON object TO user role PUBLIC WITH GRANT OPTION O exemplo de concessão de privilégios de consulta na tabela EMPLOYEES pode ser observado no código mostrado a seguir GRANT select ON uncionarios TO pessoa gerente A concessão aos usuários e atribuições de privilégios para atualização de colu nas específicas pode ser observado no código a seguir GRANT update department id ON departamento TO pessoa gerente Uma forma de garantir privilégios para outros usuários é apresentado no código que segue Controle de Acesso ao Usuário Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 155 GRANT SELECT ON emp TO CURSO Apresentada a conexão do usuário e demonstração da tabela liberada do usuá rio PESSOA Também é apresentado na sequência a forma de criar um synonym e reduzir o nome da tabela agregada ao nome do usuário CONN CURSO ALUNO SELECT FROM SCOTTEMP CREATE SYNONYM EMP FOR SCOTTEMP SELECT FROM EMP A confirmação de privilégios concedidos pode ser consultada por meio de views do dicionário de dados Essas views podem ser conhecidas como ROLESYSPRIVS Privilégios de sistema concedidos a atribuições ROLETABPRIVS Privilégios de tabela concedidos a atribuições USERROLEPRIVS Atribuições acessíveis ao usuário USERTABPRIVSMADE Privilégios de objeto concedidos sobre os objetos do usuário USERTABPRIVSRECD Privilégios de objeto concedidos ao usuário USERCOLPRIVSMADE Privilégios de objeto concedidos sobre as colunas dos objetos do usuário USERCOLPRIVSRECD Privilégios de objeto concedidos ao usuá rio sobre colunas específicas USERSYSPRIVS Lista os privilégios de sistema concedidos ao usuário CONTROLANDO ACESSOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 V U N I D A D E 156 RETIRAR DIREITOS O direito de um privilégio dado à um usuário pode ser retirado Utilizar a ins trução REVOKE para revogar os privilégios concedidos a outros usuários Os privilégios concedidos a outros usuários por meio da cláusula WITH GRANT OPTION também serão revogados A sintaxe dessa instrução é Um exemplo de uso da instrução é mostrado a seguir REVOKE privilege privilegeALL ON object FROM user userrolePUBLIC CASCADE CONSTRAINTS Visões do data dictionary pode ser representadas por DBASYSPRIVS SESSIONPRIVS DBATABPRIVS e DBACOLPRIVS CRIAR E ACESSAR VÍNCULOS DE BANCO DE DADOS Uma conexão de vínculo de Banco de Dados permite que os usuários locais acessem dados em um Banco de Dados remoto Um exemplo para acesso a um banco de dados remoto por meio de vínculo por um Banco de Dados local pode ser observado na Figura 1 Figura 1 Representação de Banco de dados remoto Fonte os autores Criar e Acessar Vínculos de Banco de Dados Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 157 Para criar um vínculo de Banco de Dados podese utilizar como exem plo o código CREATE PUBLIC DATABASE LINK empresaexemplocom USING vendas Para visualizar os dados por meio das instruções SQL que utilizem o vín culo de Banco de Dados podemos utilizar o código SELECT FROM emp empresaexemplocom Para simplificar nomes pode ser utilizado o SYNONYM Além de simplificar o acesso referencia objetos de um outro usuário e deixa os nomes mais curtos A sintaxe da instrução é representada por CREATE PUBLIC SYNONYM synonym FOR object Softwares diversos como firewalls que controlam o acesso a rede local ou pro gramas antivírus que monitoram aplicações em execução no sistema opera cional também têm papel fundamental nesse processo Mas o software por si só não é cem por cento confiável e pode falhar ter defeitos em sua implemen tação ser atacado por invasões de crackers ou ataques de hackers Para contribuir para melhores níveis de segurança podemos adicionar se gurança física que envolva pessoas e infraestrutura para aumentar o nível de proteção dos sistemas do negócio em relação a outros males também prejudiciais à segurança desses sistemas Algumas medidas de prevenção que podemos elencar são portas de segu rança com acesso restrito senhas para acesso a locais seguros como data centers centros de concentração de servidores e equipamentos de infraes trutura treinamento preventivo e de combate a eventos e acidentes que possam prejudicar toda a infraestrutura e sistemas do negócio Fonte os autores CONTROLANDO ACESSOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 V U N I D A D E 158 GERENCIANDO SENHAS E RECURSOS Caro a Alunoa também existe a possibilidade de gerenciar senhas utilizando profiles A administração desses profiles é simples controla a utilização de recursos por meio dos profiles e é possível ter informações sobre profiles senhas e recursos Profile é um nome dado a um conjunto de limites de recursos e senhas São anexados aos usuários por meio do comando CREATE USER ou ALTER USER e podem ser habilitados ou desabilitados O gerenciamento de senhas e perfis por meio do profile vai desde a cria ção do perfil englobando os usuários passando pelo controle de verificação de senha expiração e validade de senhas bloqueio de contas e histórico de senhas Para habilitar o gerenciamento de senhas ajuste o gerenciamento de senha utilizando profiles e anexandoo aos usuários É possível bloquear desbloquear e invalidar as contas de usuários utilizando o comando CREATE USER ou ALTER USER Ao bloquear usuários é possível utilizar alguns parâmetros prédefinidos que são FAILEDLOGINATTEMPTS Número de tentativas de login falhas antes de travar a conta do usuário PASSWORDLOCKTIME Número de dias que a conta ficará bloqueada PASSWORDLIFETIME Tempo limite em dias para expirar a senha PASSWORDGRACETIME Período de dias para a nova troca de senha após o primeiro login com sucesso PASSWORDREUSETIME Número de dias antes de reutilizar uma senha novamente PASSWORDREUSEMAX Número máximo de vezes que a mesma senha poderá ser utilizada PASSWORDVERIFYFUNCTION Função PLSQL para verificação da senha Gerenciando Senhas e Recursos Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 159 A função de verificação de senha deve validar o seguinte O SYS deve ser o proprietário da função Deve retornar TRUE ou FALSE Deve seguir a assinatura padrão Um erro na função causará erro no comando CREATE ou ALTER CREATE OR REPLACE FUNCTION VALIDASENHA USERID IN VARCHAR230 PASSWORDPARAMETER IN VARCHAR230 OLDPASSWORDPARAMETER IN VARCHAR230 RETURN BOOLEAN A criação de um profile ou perfil pode ser vista no exemplo a seguir CREATE PROFILE TEMPOPARATROCA LIMIT FAILEDLOGINATTEMPTS 3 PASSWORDLOCKTIME UNLIMITED PASSWORDLIFETIME 30 PASSWORDREUSETIME 30 PASSWORDVERIFYFUNCTION validasenha PASSWORDGRACETIME 5 Por sua vez a alteração de um perfil pode ser vista no exemplo a seguir ALTER PROFILE default FAILEDLOGINATTEMPTS 3 PASSWORDLIFETIME 60 PASSWORDGRCETIME 10 CONTROLANDO ACESSOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 V U N I D A D E 160 Observação Menos de 1 dia 124 1 hora 101400 10 minutos A exclusão de um perfille pode ser feita com instruções como DROP PROFILE TEMPO ou DROP PROFILE TEMPO CASCADE por exemplo No gerenciamento de recursos podem ser impostos limites de recursos para a sessão do usuário por chamada ou ambos Os limites podem ser defini dos por meio de um profile e para habilitar a utilização de limites é só utilizar códigos como RESOURCELIMIT initSIDora ou ALTER SYSTEM SET RESOURCELIMITTRUE Os limites que podem ser observados no gerenciamento de recursos são CPUPERSESSION Total de tempo de CPU utilizada em centésimos de segundo SESSIONSPERUSER Número de sessões concorrentes para o mesmo usuário CONNECTTIME Tempo decorrido em minutos IDLETIME Período de inatividade LOGICALREADSPERSESSION Número de data blocks leituras lógicas e físicas PRIVATESGA Espaço privado na SGA em bytes Shared Server Only CPUPERCALL Tempo de CPU em centésimos de segundo em cada chamada LOGICALREADSPERCALL Número de data blocks leituras lógi cas e físicas Gerenciando Usuários Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 161 O exemplo da criação de um profile com a utilização de limites pode ser obser vado no código a seguir CREATE PROFILE developerprof LIMIT SESSIONSPERUSER 2 CPUPERSESSION 10000 IDLETIME 60 CONNECTTIME 480 As informações sobre senhas e limites de recursos podem ser obtidas no data dictionary DD DBAUSERS e DBAPROFILES GERENCIANDO USUÁRIOS Ao gerenciar usuários é possível criar um novo usuário para a instância bem como excluir e alterar as opções e monitorar suas informações Os critérios de usuários e segurança norteiam alguns pontos importantes como por exemplo valores padrão de inicialização privilégios e mecanismos de autenticação Schema é o nome dado para uma coleção de objetos Ao criar um usuário um schema correspondente ao usuário também é criado O usuário pode ser associado a somente um schema podendo conter objetos como gatilhos sequ ências visões índices tabelas ou sinônimos por exemplo Para a criação de usuários identifique a tablespace que o usuário usará para armazenar seus dados Decida sobre as quotas na tablespace default e na tem porária Na sequência já é possível criar o usuário e garantir os seus privilégios necessários Observe a seguir como se cria um usuário CONTROLANDO ACESSOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 V U N I D A D E 162 CREATE USER TESTE IDENTIFIED BY TESTE DEFAULT TABLESPACE USER TEMPORARY TABLESPACE TEMP QUOTA 15M ON USERS PASSWORD EXPIRE Na gerência do usuário é possível alterar um usuário usando comandos como ALTER USER TESTE QUOTA 0 ON USERS ou ALTER USER TESTE IDENTIFIED BY XXI Podemos também excluir um usuário usando o comando DROP USER TESTE Ter informações sobre o usuário por meio de comandos como DBAUSERS e DBATSUSERS AUDITORIA A auditoria consiste no monitoramento de um usuário e é usada para investi gar atividades suspeitas no Banco de Dados relatando informações sobre todas as atividades dos usuários Para uma boa auditoria é necessário definir o que auditar por exemplo usuários comandos ou objetos Devem ser analisados os comandos executa dos com sucesso sem sucesso ou ambos A própria auditoria deve ser protegida de acesso não autorizado e ainda deve ser monitorado o tamanho e volume da auditoria para que não prejudique o andamento do Banco de Dados A audito ria é dividida em três categorias Padrão Startup e shutdown da instância e privilégios de administrador Auditoria do Banco de Dados Deve ser habilitada pelo DBA e não pode registrar valores para as colunas Auditoria Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 163 Aplicação Implementada por meio do código pode armazenar valores para as colunas e usada para auditar trocas nas tabelas Uma auditoria da instância deve indicar no initSIDora com a seguinte instru ção AUDITTRAILDB A gravação da auditoria ocorre na visão SYSAUD e seus comandos são AUDIT TABLE AUDIT CREATE ANY TRIGGER AUDIT SELECT ON scottemp AUDIT DELETE ON scottemp WHENEVER SUCCESSFUL AUDIT INSERT on scottemp WHENEVER NOT SUCCESSFUL BY ACCESS AUDIT SELECT ON scottemp BY SESSION A package DBMSFGA pode ser utilizada para refinar as opções de auditoria E para remover a auditoria deve ser utilizado o comando NOAUDIT As visões do data dictionary são ALLDEFAUDITOPTS DBASTMTAUDITOPTS DBAPRIVAUDITOPTS DBAOBJAUDITOPTS DBAAUDITTRAIL DBAAUDITEXISTS DBAAUDITOBJECT DBAAUDITSESSION DBAAUDITSTATEMENT CONTROLANDO ACESSOS Reprodução proibida Art 184 do Código Penal e Lei 9610 de 19 de fevereiro de 1998 V U N I D A D E 164 CONSIDERAÇÕES FINAIS Caroa alunoa Com foco principal na Tecnologia da Informação a segurança do Banco de Dados pode ser classificada em duas categorias distintas segurança de sistema e segurança de dados A segurança de sistema contém mecanismos de controle de acesso e o uso do Banco de Dados em um nível específico do sistema Os processos de segurança do sistema verificam a autorização para conexão ao Banco de Dados auditoria do Banco de Dados e o que poderá ser executado por um usuário Além disto temos o controle de usuários e senhas espaço dis ponível e limites de recursos dos usuários A segurança de dados inclui mecanismos de controle de acesso e o uso do Banco de Dados no nível de objeto de esquema incluindo os usuários com acesso ao objeto e os tipos específicos de ações que cada um pode executar Algumas ferramentas do Oracle Server adicionam um incremento à segu rança o que possibilita um ambiente multiplataforma de maior escalabilidade É possível citar o Oracle Enterprise Manager OEM e Oracle Security Server Manager OSS O OEM é um conjunto de utilitários disponibilizados de forma gráfica em modo usuário GUI que permitem meios de gerenciar uma ou mais bases de dados O OEM é composto por Conjunto de ferramentas administrati vas Monitor de eventos Agendador de tarefas Interface gráfica para o Recovery Manager Tools Por sua vez o OSS pode ser utilizado para implementar uma estrutura mais complexa de segurança para dados mais sensíveis com aspectos como Autenticação de usuário por meio de credenciais eletrônicas Assinatura Digital e Single Sign On SSO Por ser multiplataforma a segurança não pode ser resguardada com isso a instalação do Oracle tem uma política que depende o mínimo do sistema operacional A primeira ação é a alteração das senhas dos usuários padrão do banco Usuários como System manager Sys changeoninstall e DBSNMP dbsnmp que são instalados dessa forma e têm grande acesso ao banco o que pode com prometer por completo a sua segurança 165 1 O DBA é o profissional responsável pelo gerenciamento de um sistema de Ban co de Dados pela concessão de privilégios pela liberação dos acessos e clas sificação dos usuários do sistema conforme as determinações das políticas de segurança É ele quem cria usuários utilizando a instrução CREATE USER Crie um usuário simplesmente atribuindo um nome de usuário SCOTT com a senha ou identificação TIGER 2 Quando o usuário é criado o DBA pode conceder a ele privilégios específicos de sistema Dê a concessão de privilégios de consulta na tabela EMP para o usuário criado no exercício 1 3 Os privilégios podem ser atribuídos conforme o objeto e suas possibilidades de acesso Eles também podem variar de um objeto para outro Leia atentamente as afirmações seguintes I O proprietário de um objeto detém todos os privilégios sobre este objeto II O proprietário de um objeto não tem permissão para conceder privilégios es pecíficos sobre seus objetos para outros usuários III O direito de um privilégio dado a um usuário pode ser retirado IV É utilização da instrução REMOVE para revogar os privilégios concedidos a ou tros usuários V Os privilégios concedidos a outros usuários por meio da cláusula WITH GRANT OPTION também serão revogados Assinale a alternativa correta a Apenas I e II estão corretas b Apenas II e III estão corretas c Apenas I está correta d Apenas II III e IV estão corretas e Apenas I III e V estão corretas 166 4 Existe a possibilidade de gerenciar senhas utilizando perfis A administração des ses perfis é simples controla a utilização de recursos por meio deles e é possível ter informações sobre eles e suas respectivas senhas e recursos Com base na ideia dos perfis analise as afirmativas I Profile é um nome dado aos arquivos que contém o conjunto de usuários e senhas II Um Profile é anexado aos usuários por meio do comando CREATE USER ou AL TER USER III Profile pode ser habilitado ou desabilitado IV O gerenciamento de senhas e perfis por meio do profile atende somente a criação do perfil V As informações sobre profile podem ser obtidas por meio do data dictionary DBAPROFILES Assinale a alternativa correta a Apenas I II e III estão corretas b Apenas II III e IV estão corretas c Apenas I IV e V estão corretas d Apenas II III e V estão corretas e Apenas I III e IV estão corretas 5 A auditoria consiste no monitoramento de um usuário e pode ser usada para investigar atividades suspeitas no Banco de Dados relatando informações so bre todas as atividades dos usuários Sobre auditoria assinale Verdadeiro V ou Falso F a Para uma boa auditoria é necessário definir o que auditar por exemplo usuários comandos ou objetos b Na auditoria devem ser analisados somente os comandos executados sem sucesso c A própria auditoria deve ser protegida de acesso não autorizado d A própria auditoria deve ser monitorada o tamanho e volume da mesma para que não prejudique o andamento do Banco de Dados e A Auditoria do Banco de Dados deve ser habilitada pelo DBA e não pode registrar valores para as colunas 167 Real Application Cluster RAC Com a capacidade de oferecer altos índices de desempenho tolerância a falhas e pos sibilidade de ajuste de acordo com a demanda escalabilidade esse recurso foi imple mentado em sua versão 10g agora vem incluso com a aquisição de uma licença padrão standard do Oracle Nessa versão Standard existem limitações comuns em versões menos custosas como limite de computadores e recursos destes computadores mas em termos de funcionali dade seu ambiente mesmo nesta edição é muito bom Com o RAC um Banco de Dados pode ser ajustado para funcionar o tempo todo inin terruptamente sendo que em casos de manutenção preventiva planejadas ou corretiva devido a incidentes não previstos Mesmo em casos de pane não prevista instâncias fora da área afetada da infraestrutura podem continuar oferecendo os serviços de aces so ao Banco de Dados mantendo um nível aceitável de eficiência disponibilidade Assim processos que ocorriam na parte afetada da infraestrutura podem migrar para instâncias funcionais de forma transparente dando a impressão ao usuário de que nada esteja ocorrendo de grave na infraestrutura Essa escalabilidade transparente garantida por esse procedimento de migração de pro cessos é muito importante para garantir a principal funcionalidade de um Banco de Da dos ainda mais com o recurso RAC Em grande parte dos casos a solução utilizando RAC é mais eficiente exceto em casos em que esse processamento em paralelo por meio de diversos servidores pode ser in ferior Como exemplo temos o caso de um Banco de Dados sendo executado em uma única instância tendo que distribuir seu processamento entre vários servidores pode gerar atrasos nos processos devido a perda de desempenho no controle desse processamen to distribuído O afunilamento natural que pode ocorrer em situações de pico de acesso a um Ban co de Dados é minimizado devido ao recurso RAC distribuir as ações da mesma forma que pode ocorrer no processamento de quantidades maiores de processos em sistemas OLTP Online Transaction Processing Assim é visível a melhora encontrada no uso desse recurso muito poderoso que acom panha o pacote Oracle Fonte os autores MATERIAL COMPLEMENTAR Sistemas de Banco de Dados 2011 Rames Elmasri e ShamKant Navathe Editora Editora Pearson Sinopse a obra aborda conceitos fundamentais para proteger e usar os sistemas de Banco de Dados Fundamentos de modelagem e de projeto de Banco de Dados Linguagens e modelos fornecidos pelos sistemas de gerenciamento de Banco de Dados Técnicas de implementação do sistema de Banco de Dados com exemplos práticos REFERÊNCIAS 169 WATSON J OCA Oracle Database 11g administração I guia do exame 1Z0052 Porto Alegre Bookman 2010 Referências online 1 Em httpdocplayercombr3158030Bancodedadosoracle10gfundam en tos desqliihtml Acesse em 28 nov 2016 REFERÊNCIAS GABARITO 1 CREATE USER scott IDENTIFIED BY tiger 2 GRANT select ON emp TO scott 3 E Apenas I III e V estão corretas 4 D Apenas II III e V estão corretas 5 V F V V V CONCLUSÃO 171 Prezadoa alunoa É com muita satisfação que apresentamos neste material alguns temas sobre Bancos de Dados O conhecimento sobre bancos de dados e alguns de seus pontos são fundamentais para o desenvolvimento de softwares e tecnologias em inovação Esse conhecimento faz toda a diferença na criação e administração dos dados e das infraestruturas necessárias Com esta abordagem é importante relembrar os pontos principais do material Na unidade I abordamos inicialmente o conceito sobre a linguagem Structured Query Language SQL sendo que ela é a linguagem padrão utilizada nos banco de dados que seguem o modelo de dados relacional para efetuar a manipulação dos dados em um banco de dados utilizamos os conceitos de Doctrine Query Langua ge DQL Data Manipulation Language DML Data Definition Language DDL Data Control Language DCL e Data Transaction Language DTL Por sua vez na unidade II conhecemos os formas de criação de um banco de da dos no MySQL além da criação alteração e remoção das tabelas Estudamos os co mando de popular as tabelas Nossa intenção foi fazer com que vocês acadêmicos vivenciarem na prática a manipulação de um Sistema de Gerenciamento de Banco de Dados SGBD Na unidade III foi trabalhado conceitos mais avançados da linguagem SQL sendo aplicada em um Banco de Dados Falamos sobre Rollback e Commit Também foram apresentados alguns comando especiais a serem utilizados em conjunto com a lin guagem SQL Na unidade IV vimos o conteúdo sobre Programação SQL Foi utilizado o Banco de Dados Oracle apresentado uma visão geral sobre Procedural LanguageStructured Query Language PLSQL além de tratar de Procedures Functions Packages Por fim a unidade V abordou informações relevantes sobre Triggers e segurança do banco e dos dados São tratados assuntos como o controle de acesso ao usuário a concessão de privilégios role ou regras alteração de senha privilégios de objeto além de gerenciar senhas e recursos com a gerência dos próprios usuários e a audi toria realizada no banco de dados Esperamos ter alcançado nosso objetivo em passar nosso conhecimento a você caro a alunoa Desejamos que você seja muito feliz ao percorrer o mundo profis sional Muito sucesso e paz CONCLUSÃO