Baixe o app para aproveitar ainda mais
Prévia do material em texto
1 AULA 06: SQL PARTE 4 – JUNÇÃO Modelo de Dados Continuaremos utilizando em nossas aulas o modelo de dados de uma empresa composto pelas tabelas Empregado, Cliente e Departamento Antes de continuarmos devemos alterar a configuração do ambiente do ORACLE para que exiba mais linhas no retorno do comando para tal selecione 100 na caixa de exibição na tela de comando SQL. (Veja figura) Junções de tabelas Em nosso banco de dados tabela EMPREGADO contém dados dos funcionários da empresa. Na tabela ‘Departamento’ estão os dados dos departamentos. Para identificar o departamento do Empregado, a tabela 'EMPREGADO possui a coluna 'Id_DEPTO' chave estrangeira para a tabela departamento que nos diz em qual departamento o empregado esta alocado. Até agora dos comandos que havíamos dados recuperaram dados de apenas uma tabela, como faríamos para listar os dados do empregado e do departamento em que ele esta alocado? Este tipo de situação nos leva ao comando de junção que é um comando SELECT que traz dados de duas ou mais tabelas. Esse é um processo é chamado de junção [join]. E as tabelas têm uma coluna em comum que é usado para determinar as linhas que retornam. Sintaxe da junção Existem duas sintaxes diferentes para junção de tabelas. Uma delas, é a tradicional, sendo a única aceita até a versão 8i do Oracle. A segunda é a Sintaxe ANSI que passou a ser aceita a partir da versão 9i do Oracle. Na sintaxe tradicional, na lista do FROM as duas (ou mais) tabelas são especificadas, separadas por vírgulas. Na cláusula WHERE deve haver uma condição ligando as duas, a condição de junção [join condition]. Na lista de colunas do SELECT podem ser incluídos colunas de qualquer uma das tabelas. A outra forma de sintaxe que pode ser usada é a sintaxe do padrão ANSI SQL onde o tipo de junção entre as tabelas deve ser especificado entre elas (veremos os diferentes tipos a seguir) e a condição de junção é especificada com a palavra ON. Ao longo desta aula exemplificaremos as duas sintaxes. 2 Tipos de junção Junção Interior: Esse tipo de junção conecta as duas tabelas e retorna apenas as linhas que estão relacionadas. Junção cruzada: Também chamada JUNÇÃO IRRESTRITA realiza o produto cartesiano das duas tabelas. Junção exterior: Similar a JUNÇÃO INTERIOR traz as linhas que estão relacionadas acrescidas as linhas não relacionadas. Autojunção: É a junção de uma tabela com ela mesma. Junção cruzada ou irrestrita - Cross join Uma junção cruzada de tabelas, também chamada junção irrestrita de duas tabelas gera um resultado formado por todas as combinações possíveis de uma linha da primeira tabela com uma linha da segunda. Não existe uma condição de junção. Esse resultado é chamado produto cartesiano das duas tabelas. Por exemplo desejamos realizar a Junção Cruzada das tabelas Empregado e Departamento cujo conteúdo é mostrado na figura. Nesse caso, como a tabela Empregado possui 6 linhas e a tabela Departamento 3 linhas , o resultado final será 18 linhas que é 6 * 3 = 18 linhas, formadas por todas as combinações possíveis de empregado e departamento. Teríamos como resultado então esta tabela. Empregado Departamento Tabela Na sintaxe ANSI, junções cruzadas são indicadas com CROSS JOIN, no caso de nosso exemplo o comando seria: Select * From Empregado cross join Departamento; 3 Na sintaxe Tradicional apenas listamos as tabelas envolvidas na cláusula from, separadas por virgulas: Select * From Empregado , Departamento; O principal problema com a sintaxe tradicional é que se você incluir mais de uma tabela mas não colocar uma condição de junção(é fácil esquecer uma condição quando existem várias outras envolvidas), o banco de dados vai simplesmente fazer um produto cartesiano sem nenhum aviso. Junções cruzadas raramente são usadas, mas é importante saber como evitar usá-las. Para isso, sempre crie uma junção interior ou exterior (veremos nos próximos tópicos), em casos de ter várias tabelas envolvidas. Junção interior - Inner join A junção interior de tabelas conecta as duas tabelas e retorna apenas as linhas que satisfazem a condição de junção. Se desejássemos retornar os dados do empregado e o departamento onde ele esta alocado, ao utilizarmos a JUNÇÃO INTERIOR se existirem funcionários para os quais não há departamento relacionado eles não serão incluídos no resultado. Igualmente, se existirem departamentos que não têm empregados eles não aparecem no resultado. Uma junção interior é chamada de equijoin quando as colunas são comparadas usando o =, e as duas colunas aparecem no resultado, mostrando dados redundantes, já que elas têm o mesmo valor. 4 Junção interior - sintaxe tradicional Vejamos um exemplo para melhor ilustrar. Queremos retornar os dados do empregado e os dados do departamento onde ele esta alocado. Observe as figuras com a estrutura das duas tabelas podemos notar que a coluna ID_DEPTO é uma chave estrangeira para o coluna ID da tabela DEPARTAMENTO. Na sintaxe Tradicional apenas listamos as tabelas envolvidas na cláusula from, separadas por virgulas: Select * From Empregado , Departamento; O principal problema com a sintaxe tradicional é que se você incluir mais de uma tabela mas não colocar uma condição de junção(é fácil esquecer uma condição quando existem várias outras envolvidas), o banco de dados vai simplesmente fazer um produto cartesiano sem nenhum aviso. Junções cruzadas raramente são usadas, mas é importante saber como evitar usá-las. Para isso, sempre crie uma junção interior ou exterior (veremos nos próximos tópicos), em casos de ter várias tabelas envolvidas. 5 O comando que executaria a junção então seria: Select * From Empregado, Departamento Where ID_DEPTO = DEPARTAMENTO.ID Observe o seguinte no comando: Como pedimos todas as colunas a estrutura da tabela de retorno é a justaposição dos esquemas das duas tabelas envolvidas. A quantidade de linhas no retorno da consulta é igual, no máximo, a quantidade de linhas da tabela que possui mais linhas ( como empregado possui 6 linhas e departamento 3 no retorno poderemos ter no máximo 6 linhas). Repare a qualificação da coluna ID. Temos que qualificar uma coluna quando o seu identificar existe em mais de uma tabela. Vejamos o caso de nosso exemplo se simplesmente escrevêssemos na condição de junção: WHERE ID_DEPTO = ID O SGBD retornaria um erro pois como temos uma coluna ID na tabela Empregado e outra coluna ID na tabela de Departamento haveria uma ambiguidade e o banco não saberia qual id utilizar. Para resolver isso qualificamos as colunas repetidas, ou seja escrevemos o nome da tabela antes do nome da coluna e os separamos por um ponto. Desta forma a condição de junção ficaria WHERE ID_DEPTO = DEPARTAMENTO.ID Outra forma de escrevermos o mesmo comando seria utilizando alias de tabelas. Para simplificar a qualificação de colunas, pode-se usar um apelido [alias] de tabela, um nome colocado imediatamente após o nome da tabela, na lista do FROM. Esse nome representa a tabela nas qualificações. Por exemplo, a consulta do exemplo pode ser reescrita da forma: SELECT * FROM EMPREGADO E, DEPARTAMENTO D WHERE E.ID_DEPTO = D.ID 6 No comando de junção podemos ainda definir quais colunas desejamos que retornem, para isso basta elencarmos as colunas na clausula SELECT. Continuando em nosso exemplo se desejássemos retornar apenas as colunas ID, Ult_nome, Cargo e Salario da tabela empregado e a coluna Nome da tabela Departamento o comando seria: Junção interior - sintaxe tradicionalNa sintaxe ANSI, junções interiores são indicadas com INNER JOIN: select <colunas> from tabela1 INNER JOIN tabela2 ON <condição de junção>; No caso de nosso exemplo o comando seria: SELECT E.ID, E.ULT_NOME, E.CARGO, E.SALARIO, D.NOME FROM EMPREGADO E INNER JOIN DEPARTAMENTO D ON E.ID_DEPTO = D.ID Junção interior com mais de duas tabelas É possível juntar três ou mais tabelas com informações relacionadas. No banco de dados na tabela Cliente temos a coluna Vendedor (FK para empregado) que relaciona ao cliente ao vendedor que o atende ( identificado pelo ID) e na tabela empregado a coluna ID_DEPTO que como já vimos nos diz em qual departamento o empregado esta alocado. O comando de junção das 3 tabelas na sintaxe ANSI seria então: SELECT C.NOME E.ID, E.ULT_NOME,E.CARGO, D.NOME FROM CLIENTE C INNER JOIN EMPREGADO E ON C.VENDEDOR = E.ID INNER JOIN DEPARTAMENTO D ON E.ID_DEPTO = D.ID 7 Observe que retornam 3 linhas sendo obedecida aquela regra que diz que a quantidade de linhas que retornam é igual ou menor que a tabela que possui mais linhas ( a tabela empregado com 6 linhas é a que possui mais linhas). Como ficaria o comando na sintaxe tradicional? SELECT E.ID, E.ULT_NOME,E.CARGO, E.SALARIO, D.NOME FROM EMPREGADO E INNER JOIN DEPARTAMENTO D ON E.ID_DEPTO = D.ID WHERE SALARIO < 10000 Observe a clausula WHERE realizando a filtragem das linhas. Junção interior - filtrando o resultado Vamos supor agora que desejamos o ID, Ult_nome, Cargo e Salario da tabela empregado e a coluna Nome da tabela Departamento, mas apenas para os empregados com salario maior que 1000. Como serio o comando? 8 Comando na Sintaxe Ansi SELECT E.ID, E.ULT_NOME,E.CARGO, E.SALARIO, D.NOME FROM EMPREGADO E INNER JOIN DEPARTAMENTO D ON E.ID_DEPTO = D.ID WHERE SALARIO < 10000 Observe a clausula WHERE realizando a filtragem das linhas. Comando na sintaxe tradicional SELECT E.ID, E.ULT_NOME,E.CARGO, E.SALARIO, D.NOME FROM EMPREGADO E , DEPARTAMENTO D WHERE (E.ID_DEPTO = D.ID) AND (SALARIO < 10000 OR ID < 3) Note o uso de parêntesis para agrupar as condições de junções e as condições de filtragem e o uso do AND, interligando as duas. Esta formatação é necessária para evitar um produto cartesiano parcial. Se escrevemos o comando sem parêntesis, como abaixo , o SGBD ao avaliar a expressão do WHERE não faz distinção entre as condições de junção e as de filtragem, a expressão lógica é avaliada com um todo e devido ao fato de termos usado OR qualquer linha que tenho ID < 3 ira voltar mesmo que o ID_DEPTO seja diferente do ID do departamento, como podemos notar na figura SELECT E.ID, E.ULT_NOME,E.CARGO, E.SALARIO, D.NOME FROM EMPREGADO E , DEPARTAMENTO D WHERE (E.ID_DEPTO = D.ID AND SALARIO < 10000 OR ID < 3 Teste o comando no ORACLE e veja o resultado. FAÇA Exercicio_aula06_doc1,doc2 e doc3. 9 Junção exterior - sintaxe tradicional Uma junção exterior [outer join] mostra todas as linhas de uma tabela, mesmo quando elas não satisfazem a condição de junção. Vejamos um exemplo. Queremos retornar todos os empregados e os clientes que eles atendem. Este comando seria um comando de Junção Interior normal onde a coluna Vendedor deve ser igualada a coluna ID de empregado. Observe que somente os empregados de ID 5 e 6 atendem algum cliente, portanto apenas eles podem retornar na consulta. Junção exterior O comando na Sintaxe Ansi para realizar a junção interior seria: SELECT C.ID, C.NOME, E.ID, E.ULT_NOME,E.CARGO FROM EMPREGADO E INNER JOIN CLIENTE C ON C.VENDEDOR = E.ID Resultando na tabela da figura Mas e se desejássemos retornar todos os empregados e para os vendedores os dados dos clientes? 10 Comandaríamos então uma junção exterior. Neste caso substituiríamos a expressão INNER e pela expressão LEFT OU RIGHT caso a tabela que desejamos retornar esteja do lado esquerdo ou direito do comando. No caso como empregado esta a esquerda o comando seria: SELECT C.ID, C.NOME, E.ID, E.ULT_NOME,E.CARGO FROM EMPREGADO E LEFT JOIN CLIENTE C ON C.VENDEDOR = E.ID Resultando na tabela da figura Note que as colunas de clientes ficam nulas nas linhas que correspondem ao empregados que não se relacionam com clientes. O comando na Sintaxe Ansi para realizar a junção interior seria: SELECT C.ID, C.NOME, E.ID, E.ULT_NOME,E.CARGO FROM EMPREGADO E INNER JOIN CLIENTE C ON C.VENDEDOR = E.ID Resultando na tabela da figura Mas e se desejássemos retornar todos os empregados e para os vendedores os dados dos clientes? Comandaríamos então uma junção exterior. Neste caso substituiríamos a expressão INNER e pela expressão LEFT OU RIGHT caso a tabela que desejamos retornar esteja do lado esquerdo ou direito do comando. No caso como empregado esta a esquerda o comando seria: SELECT C.ID, C.NOME, E.ID, E.ULT_NOME,E.CARGO FROM EMPREGADO E LEFT JOIN CLIENTE C ON C.VENDEDOR = E.ID Resultando na tabela da figura 11 Note que as colunas de clientes ficam nulas nas linhas que correspondem ao empregados que não se relacionam com clientes. Se a tabela empregado estivesse do outro lado utilizaríamos RIGHT JOIN, neste caso o comando seria: SELECT C.ID, C.NOME, E.ID, E.ULT_NOME,E.CARGO FROM CLIENTE C RIGHT OUTER JOIN EMPREGADO E ON C.VENDEDOR = E.ID Resultando na tabela da figura Note que expressão OUTER no comando. Ela é opcional pode ser omitida como fizemos no comando anterior. Como ficaria este comando na sintaxe Tradicional? No caso do ultimo comando se tivemos comando LEFT JOIN, retornaríamos todos os clientes que não se relacionam com Vendedores veja o comando: SELECT C.ID, C.NOME, E.ID, E.ULT_NOME,E.CARGO FROM CLIENTE C LEFT JOIN EMPREGADO E ON C.VENDEDOR = E.ID Resultando na tabela da figura Note que CASA DESCONTO apareceu no retorno e que as colunas da tabela empregado estão nulas. Como ficaria este comando na sintaxe Tradicional? Ao contrario da sintaxe de INNER JOIN, a sintaxe de OUTER JOIN varia de SGBD para SGBD. No caso do ORACLE a sintaxe tradicional de outer join: SELECT nome da tabela1.nome da coluna, nome da tabela2 .nome da coluna .... FROM nome da tabela1, nome da tabela2 WHERE nome da tabela1.nome da coluna ( + ) = nome da tabela2.nome da coluna Onde ( + ) é o símbolo do outer join, que pode ser colocado em quaisquer dos lados da cláusula where mas não em ambos os lados. Este símbolo deve ser colocado seguindo o nome da coluna que pode não ter correspondente. No caso do nosso exemplo o comando na sintaxe tradicional seria: SELECT C.ID, C.NOME, E.ID, E.ULT_NOME,E.CARGO FROM CLIENTE C , EMPREGADO E WHERE C.VENDEDOR (+) = E.ID 12 O operador de OUTER JOIN(+) não tem ligação com o lado da tabela na clausula from. Faça um teste escreva o comando invertendo a ordem das tabelas na from e veja que o resultado é o mesmo. Existe um ultimo tipo de junção exterior na Sintaxe ANSI o FULL JOIN onde todas as linhas de ambas as tabelas são incluídas, mesmo as que não estão relacionadas com a outra tabela. Um exemplo de comando seria SELECT C.ID, C.NOME, E.ID, E.ULT_NOME,E.CARGO FROM CLIENTE C FULL JOIN EMPREGADO E ON C.VENDEDOR = E.IDEste comando retornaria as linhas relacionadas e as não relacionadas das duas tabelas. De o comando a junção e veja o resultado: Repare que as colunas de empregado estão nulas para o cliente CASA DESCONTO bem como as colunas de cliente para os empregados que não são vendedores. Este comando equivale a fazer LEFT JOIN e RIGHT JOIN ao mesmo tempo. DICA: Na SINTAXE TRADICIONAL do ORACLE NÃO existe comando de FULL JOIN, colocar o (+) dos dois lados gera uma mensagem de erro. Uma das maiores utilidade do uso do outer join é descobrimos qual linha de uma tabela não se relaciona com outra. Para tal fazer um Outer Join e depois selecionamos as linhas que retornam de outra tabela onde um campo de preenchimento obrigatório ( por exemplo a chave primária) tenha o valor nulo. Se por exemplo desejássemos saber as os clientes que não são atendidos por vendedores dar o seguinte comando: 13 SELECT C.* FROM CLIENTE C LEFT JOIN EMPREGADO E ON C.VENDEDOR = E.ID WHERE E.ID IS NULL Note duas coisas: A clausula where elimina todas as linhas que possuem empregados relacionados a cliente, tente dar o comando sem a clausula e veja o retorno. C.* é uma forma simples de projetar todas as linhas da tabela C ou seja tabela Cliente. Auto junção Uma junção exterior [outer join] mostra todas as linhas de uma tabela, mesmo quando elas não satisfazem a condição de junção. Uma auto-junção [self join] é uma junção da tabela com ela mesma. Na tabela Empregado, por exemplo, cada empregado está subordinado a outro. A coluna 'id_gerente' indica o código do gerente do empregado. Qual seria o comando para mostrarmos uma lista de todos os gerentes , cada um com seus subordinados? Auto junção - sintaxe tradicional Para mostrarmos uma lista de todos os gerentes , cada um com seus subordinados podemos usar: SELECT G.ID, G._ULT_NOME, G.CARGO, S.ID, S.ULT_NOME,S.CARGO FROM EMPREGADO G , EMPREGADO S WHERE S.ID_GERENTE = G.ID Nesse caso, é obrigatório usar um apelido de tabela para distinguir as duas "cópias" da tabela que estão sendo relacionadas: 'G' no exemplo representa uma linha da tabela Empregado enquanto Gerente e 'S' representa outra linha, de um subordinado, que estão sendo comparadas entre si. O resultado dessa consulta pode ser visto na figura. 14 Auto junção - sintaxe ANSI Na sintaxe ANSI o mesmo comando seria: SELECT G.ID, G._ULT_NOME, G.CARGO, S.ID, S.ULT_NOME,S.CARGO FROM EMPREGADO G INNER JOIN EMPREGADO S ON S.ID_GERENTE = G.ID Onde obteríamos o mesmo resultado da consulta anterior. Observe a figura. Faça os exercícios de fixação, em seguida confira com o gabarito. AULA 7: SQL PARTE 5 – SUBCONSULTA E OPERADORES DE CONJUNTO Modelo de Dados Continuaremos utilizando em nossas aulas o modelo de dados de uma empresa composto pelas tabelas Empregado, Cliente e Departamento SubConsulta Subconsulta é uma consulta dentro de um outro comando SQL que pode ser: CREATE TABLE CREATE VIEW SELECT INSERT DELETE UPDATE Regras para Construção de Subconsultas A consulta interna tem que estar entre parênteses e sempre a mais interna é executada primeiro. Admite o aninhamento de n consultas internas ou em conjunto com operadores AND e OR. Retorna uma ou várias linhas ou colunas. Usualmente é usada na cláusula WHERE dos comandos SELECT, DELETE e UPDATE. Pode ser usada na cláusula FROM do comando SELECT. Pode referenciar colunas da consulta mais externa. Não pode conter a cláusula ORDER BY. Operadores de Comparação em Subconsultas Simples Os operadores abaixo podem ser usados em condições de comparação com subconsultas simples, pois manipulam o retorno de apenas uma linha de comparação com a consulta principal. 15 = <> > < >= <= Igual a Diferente Maior que Menor que Maior ou igual Menor ou igual Subconsulta simples em SELECT Vamos supor que desejamos recuperar todos os dados dos empregados que trabalham no departamento de vendas. Para isso necessitaríamos acessar os dados das tabelas Empregado e Departamento. Neste caso daríamos o comando: SELECT * FROM EMPREGADO WHERE ID_DEPTO = ( SELECT ID FROM DEPARTAMENTO WHERE UPPER(NOME) = ‘VENDAS’) Observe que existe um comando SELECT (consulta inicial) e um outro comando SELECT dentro deste primeiro (interno). Sua execução gera o resultado da figura. Modo de Execução Conforme visto no exemplo anterior, o segundo SELECT, é executado primeiramente, gerando a informação que será utilizada pelo SELECT principal. Acompanhe: SELECT ID FROM DEPARTAMENTO WHERE UPPER(NOME) = ‘VENDAS’ 16 Outro Exemplo Este tipo de subconsulta pode ser utilizado para retornar linhas que atendam a condições que não existem diretamente nos dados de uma tabela. Por exemplo, desejamos retornar todos os dados dos empregados que ganham mais que o salario médio da empresa. Não existe em nenhuma tabela o valor do salario médio, mas ele pode ser calculado em uma consulta utilizando-se a função AVG: DICA: Observe que o salario médio é de 13000 e que os empregados de ID 1,2 e 3 possuem salario superior a media salarial da empresa. Poderiamos então escreve um comando de Select onde na subconsulta colocaríamos o calculo do Salario Médio. O nosso comando ficaria então assim: SELECT * FROM EMPREGADO WHERE SALARIO > ( SELECT AVG (SALARIO) FROM EMPREGADO) Observe que retornaram apenas os empregados de ID 1,2 e 3. Exercicios de fixação Para a realização dos exercícios desta aula utilizaremos o Banco de Dados da SuperSport que você deve ter criado na aula anterior. Se você não criou o banco faça os seguintes passos: 1.Criar um novo usuário denominado SuperSport como os privilégios de connect e resource. Se tiver duvidas de como criar um usuário veja como realizar esta atividade na aula3. 2.Conectar no Oracle utilizando o aplicativo de linha de comando com o usuário SuperSport e executar o script SuperSport disponível na biblioteca da disciplina. Se tiver duvidas veja na aula 4 como fazer login e executar um script. 3.Conectar na interface Web do Oracle com o usuário SuperSport e executar os comandos SQL solicitados. Subconsultas Multi-Linhas Uma subconsulta multi-linhas é aquela que pode retornar à consulta principal mais que uma linha de dados para efeito de substituição. Isto, por conseguinte, implica numa construção ligeiramente diferente a nível de operadores, pois estes têm que ser capazes de manipular um conjunto (lista) com mais de um elemento. 17 Vamos supor que desejamos listar os empregados que ganham salários diferentes dos vendedores. Para isso iriamos selecionar inicialmente os salários dos vendedores com o comando SELECT SALARIO FROM EMPREGADO WHERE UPPER(CARGO) = ‘VENDEDOR’ Observe que como temos dois vendedores, linhas em destaque, a consulta retorna dois valores 4000 e 3500. Se tentássemos escrever a subconsulta utilizando um operador de comparação como > obteríamos um erro. Observe que o operador <> tenta fazer uma comparação direta com o primeiro elemento (linha) do conjunto, porém existem mais, o que faz com que o erro aconteça. Os operadores de comparação ( >, < , = ou <> ) somente pode ter um valor de cada lado. Operadores de comparação em subconsultas multi-linhas Existe um conjunto de operadores que podem ser utilizados em subconsultas que retornam maisde uma linhas, este operadores são: Além disso, podem ser usadas combinações com >= e <=, de forma análoga. Note que não é permitido o uso de = ALL. Utilizaremos então em nosso exemplo o NOT IN. Nosso comando seria então: 18 SELECT * FROM EMPREGADO WHERE SALARIO NOT IN (SELECT SALARIO FROM EMPREGADO WHERE UPPER(CARGO) = ‘VENDEDOR’) Exercicios de fixação Para a realização dos exercícios desta aula utilizaremos o Banco de Dados da SuperSport A descrição do banco de dados você pode encontrar aqui. Antes de continuar faça os exercícios de fixação e após leia o gabarito. Subconsultas correlatas As sub-consultas que foram vistas até agora nos exemplos podem ser avaliadas uma vez só e depois substituídas no corpo da consulta principal. Já uma sub-consulta correlata [correlated subquery] depende dos valores da consulta principal onde ela está alinhada, por isso deve ser avaliada uma vez para cada linha do resultado externo. Por exemplo, desejamos saber quais empregados ganham mais que a média salarial de seu departamento. A consulta abaixo nos dá este resultado: SELECT * FROM EMPREGADO E WHERE SALARIO > (SELECT AVG(SALARIO) FROM EMPREGADO I WHERE ID_DEPTO = E.ID_DEPTO) Essa é uma sub-consulta correlata porque ela faz referência a uma tabela da consulta mais externa. A sub- consulta é avaliada repetidas vezes, uma para cada linha da retornada pela consulta externa. Testes de existência Um teste de existência é uma condição que envolve a palavra EXISTS e uma sub-consulta. A condição é verdadeira se a sub-consulta retorna alguma linha e é falsa se ela retorna zero linhas. Por exemplo, para saber quais os departamentos que possuem funcionários cujo cargo é igual a ‘Vendedor’ execute o seguinte comando: 19 SELECT * FROM DEPARTAMENTO E WHERE EXISTS (SELECT * FROM EMPREGADO I WHERE E.ID = I.ID_DEPTO AND UPPER(I.CARGO) = 'VENDEDOR') O resultado da sub-consulta não importa, pois está apenas sendo testada a existência de um resultado. Nesse caso, a lista de colunas é sempre um asterisco (*). Trabalhando com conjuntos Muitas vezes necessitamos que nossas consultas incluam, em uma mesma coluna, dados de mais de uma tabela. Para isto utilizamos os operadores de conjuntos UNION, UNION ALL, INTERSECT e MINUS. Regras para Utilização dos Operadores de Conjuntos Os comandos SELECT participantes têm que ter o mesmo número de colunas, ou seja tem que ser compatíveis quanto a união. As colunas correspondentes têm que ser do mesmo tipo de dado, Linhas duplicadas são automaticamente descartadas não gerando redundância de tuplas, a exceção é o operador UNION ALL, Os nomes das colunas resultantes são os da primeira consulta, ALIAS de colunas só tem efeito se utilizados na primeira consulta, A cláusula ORDER BY só pode ser utilizada ao final do comando, Os operadores de conjuntos podem ser utilizados em subconsultas. Compatíveis quanto a união Denominamos Compatibilidade de União quando duas relações possuem o mesmo esquema,ou seja, a mesma quantidade de colunas com o mesmo tipo e na mesma ordem. Observemos a figura abaixo. Podemos notar que as tabelas Empregados e Produtos são compatíveis quanto União já que as duas possuem três colunas a primeira e a segunda numéricas e a terceira alfanumérica, enquanto a tabela Animais não é compatível com as outras duas já que apesar de também possuir três colunas, sendo duas numéricas e um alfanumérica eles não se encontram na mesma ordem pois a alfanumérica é a primeira e as numéricas a duas últimas. A análise da Compatibilidade visa verificar apenas se é possível fazer a operação, não tendo ligação com a Semântica do conteúdo das relações, no nosso exemplo não faria muito sentido realizar uma União entre Empregados e Produtos embora elas sejam compatíveis para a operação. Uma observação importante é que apesar de se denominada “Compatibilidade de União” esta característica se aplica a TODAS as operações de conjunto, ou seja, União, Interseção e Diferença. Redundância de tuplas Uma tupla é considerada Redundante quando todos os valores de atributos forem iguais em seus respectivos atributos. Analisemos o exemplo abaixo: 1 – Ao compararmos a primeira linha da tabela empregado com cada uma das linhas da tabela Acionista podemos observar que não existe nenhuma linha igual na tabela de Acionista, pois apesar de existir um acionista com o mesmo Id ( primeira linha, id 101) o nome é diferente. tabela possui o mesmo valor de ID mas o nome é diferente, portanto estas tuplas não são redundantes. O mesmo ocorre em relação a terceira linha de Empregado pois a segunda linha de acionista possui o mesmo valor para nome ( Marco Aurélio) porém com valor de Id diferente. 20 2 – Ao analisarmos a segunda linha da tabela Empregado já ocorre o contrário ,notamos que esta linha é EXATAMENTE IGUAL a última ultima linha de acionista já que possuem o mesmo nome e o mesmo ID e caso a duas fizessem parte do conjunto resposta de uma expressão relacional apenas uma delas apareceria no resultado O Operador UNION Resulta na combinação de todas as linhas de duas ou mais tabelas participantes do UNION, eliminando as linhas duplicadas resultantes. Se quisermos, por exemplo, id dos empregados que trabalham nos departamentos 10 ou 20 ou que possuem o cargo de vendedor como ficaria o comando utilizando operadores de conjunto? Observe que os empregados de ID 2 a 6 são dos departamento 10 ou 20 ( destacados em vermelho) e os empregados de ID 5 e 6 são vendedores ( destacados em azul). Queremos os que estão em um conjunto ou no outro ,portanto os empregados de ID 2, 3,4,5 e 6. Note ainda que os empregados de ID 5 e 6 aparecem nos dois conjuntos. O comando que realiza a consulta é: SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,30) UNION SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR' Observe que: - Os IDs 5 e 6 retornaram apenas uma vez, na operação UNION não ocorre redundância de tuplas. - A operação é comutativa se você comandar: SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR' UNION SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,30) Obterá o mesmo resultado. Teste e comprove. O Operador UNION ALL Resulta na combinação de todas as linhas de duas ou mais tabelas participantes do UNION, mantendo todas as linhas duplicadas. A mesma situação do exemplo anterior com Union ALL ficaria: SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,20) UNION ALL SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR' E produziria o seguinte resultado: Observe que: - Os IDs 5 e retornaram duas vezes pois o UNION ALL retorna as tuplas redundantes.. - A operação é comutativa se você comandar: 21 SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR' UNION ALL SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,30) Obterá o mesmo resultado. Teste e comprove. Observe que os ID 5 e 6 retornaram duas vezes pois o UNION ALL retorna as tuplas redundantes. O Operador INTERSECT Resulta na interseção entre todas as linhas de duas ou mais tabelas participantes do INTERSECT, ou seja, apenas as linhas comuns entre ela: Se quisermos, por exemplo, id dos empregados que trabalham nos departamentos 10 ou 20 E que possuem o cargo de vendedor como ficaria o comando utilizando operadores de conjunto? Observe que os empregados de ID 2 a 6 são dos departamento 10 ou 20 ( destacados em vermelho) e os empregados de ID 5 e 6 são vendedores (destacadosem azul). Queremos portanto os empregados que estão nos dois conjuntos ou seja os ID 5 e 6. O comando que realiza a consulta é: SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,30) INTERSECT SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR' Observe que: - Os IDs 5 e 6 retornaram apenas uma vez, na operação INTERSECT não ocorre redundância de tuplas. - Não existe INTERSECT ALL - A operação é comutativa se você comandar: SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR' INTERSECT SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,30) Obterá o mesmo resulta. Teste e comprove. O Operador MINUS Resulta nas linhas existentes na primeira tabela, mas que não existem na segunda. As linhas comuns também não são resultantes. Observe que os empregados de ID 2 a 6 são dos departamento 10 ou 20 ( destacados em vermelho) e os empregados de ID 5 e 6 são vendedores ( destacados em azul). 22 Se desejássemos o ID dos empregados que trabalham nos departamentos 10 e 30 e que NÃO são vendedores, queremos os empregados que estão no primeiro conjunto e que não aparecem no segundoou seja os ID 2,3 e 4. O comando que realiza a consulta é: SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,30) MINUS SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR' Observe que: - Na operação MINUS não ocorre redundância de tuplas. - Não existe MINUS ALL - A operação é não comutativa se você comandar: SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR' MINUS SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,30) Teste e veja a diferença no resultado. Utilizando a Cláusula ORDER BY Para ordenar o resultado de uma consulta com operadores de conjuntos, deve-se colocar a cláusula ORDER BY ao final do comando. Por exemplo, para ordenar o resultado o UINON ALL pelo ID dos empregados em ordem Descendentes devemos dar os seguinte comando: SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,30) UNION ALL SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR' ORDER BY ID DESC Saiba mais Você pode visitar sites como: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt onde encontrará a especificação da SQL2 http://www.oracle.com/pls/db10g/portal.portal_demo3?selected=1 onde encontrará a documentação completa do SGBD Oracle 10g. Além disso você pode pesquisar na internet sites, vídeos e artigos relacionados ao conteúdo visto. Se ainda tiver alguma dúvida, fale com seu professor online utilizando os recursos disponíveis no ambiente de aprendizagem. 23 AULA 08: SQL PARTE 6 – OUTROS OBJETOS DE BANCO DE DADOS Continuaremos utilizando em nossas aulas o modelo de dados de uma empresa composto pelas tabelas Empregado, Cliente e Departamento Visões (Views) A utilização de visões nos permite materializar o Esquema Externo de um banco de dados. Uma Visão (View) é uma consulta previamente definida e que fica armazenada no dicionário de dados, podendo ser acessada de forma similar a uma tabela. Quando a visão é referenciada em um comando de SELECT suas linhas e colunas são determinadas dinamicamente, ou seja a consulta é executada e o resultado apresentado para o usuário, atuando como um verdadeira tabela virtual. Uma visão pode permitir, com restrições que os dados da tabela sejam manipulados em comando de INSERT, UPDATE e DELETE, porém, não armazena estes dados. Vejamos a sintaxe do comando de criação de Visões: CREATE [OR REPLACE] [FORCE I NOFORCE] VIEW nome_view [(alias1 [, alias2] [, ....] ) ] AS subquery [WITH READ ONLY] Recuperando Dados Através da View Tipos de Views Existem basicamente dois tipos de Views: O tipo simples é composto por apenas um SELECT, utiliza apenas uma tabela, suas colunas são formadas por colunas da tabela original, sem cálculos ou funções. A View complexa é aquele onde há um join entre tabelas na subquery, conforme visto no exemplo. DICA: • Com uma VIEW simples será possível executarmos comandos INSERT, UPDATE e DELETE (além do SELECT). • A manipulação dos dados através de uma VIEW não desabilita as constraints das tabelas as quais os mesmos se referem. • Cada coluna definida para VIEWs deve ter um nome de coluna válido. • Caso seja uma fórmula, deve possuir um alias. Recuperando Dados Através da View Existem basicamente dois tipos de Views: Simples e Complexas. é composto por apenas um SELECT, utiliza apenas uma tabela, suas colunas são formadas por colunas da tabela original, sem cálculos ou funções. é aquele onde há um join entre tabelas na subquery, conforme visto no exemplo. • Com uma VIEW simples será possível executarmos comandos INSERT, UPDATE e DELETE (além do SELECT). • A manipulação dos dados através de uma VIEW não desabilita as constraints das tabelas as quais os mesmos VIEWs deve ter um nome de coluna válido. • Caso seja uma fórmula, deve possuir um alias. 24 é composto por apenas um SELECT, utiliza apenas uma tabela, suas colunas são formadas por é aquele onde há um join entre tabelas na subquery, conforme visto no exemplo. • Com uma VIEW simples será possível executarmos comandos INSERT, UPDATE e DELETE (além do SELECT). • A manipulação dos dados através de uma VIEW não desabilita as constraints das tabelas as quais os mesmos 25 26 A Cláusula WITH READ ONLY 27 Eliminando uma View Um gerador de sequências (sequences) O Oracle possui internamente uma máquina geradora de números seqüenciais que pode perfeitamente ser usado para produzir números únicos, consecutivos e incrementados conforme determinado. A esses números chamamos de Sequences e são muito utilizados para fazer o papel de Chaves Primárias em tabelas onde não existe uma coluna mais apropriada, ou qualquer outra aplicação que haja necessidade de números únicos. A sintaxe do comando de criação de uma Sequence é: CREATE SEQUENCE sequence_name [ INCREMENT BY n ] [START WITH n] [MAXVALUE n I NOMAXVALUE] [MINVALUE n I NOMINVALUE] [CYCLE I NOCYCLE] [CACHE l NOCACHE] [ORDER I NOORDER ] 28 Gerando sequences com NEXTVAL 29 Verificando a sequence com CURRVAL Inserindo valores únicos numa tabela A figura mostra o conteúdo da tabela Cliente após o comando de inserção. Observe particularmente a linha em destaque que corresponde a inserção utilizando Nextval. Particularidades de sequences Regras para utilização de NEXTVAL e CURRVAL Alterando e eliminando uma sequence Sequences podem ser submetidas a alteração e eliminação tal qual uma tabela, através dos comandos ALTER SEQUENCE e DROP SEQUENCE. Veja a sintaxe do comando: ALTER SEQUENCE [schema.]sequence_name [INCREMENT BY n ] [MAXVALUE n I NOMAXVALUE] [MINVALUE n I NOMINVALUE] ........... Controles sobre Sequences Alteradas Serão afetados apenas os números a serem gerados. Algumas validações serão feitas como um novo valor MAXVALUE, não poderá ser definido se menor que o último número gerado. A cláusula START WITH, não poderá ser alterada, neste caso, a sequence deverá ser eliminada e recriada. Particularidades de sequences Regras para utilização de NEXTVAL e CURRVAL Alterando e eliminando uma sequence Sequences podem ser submetidas a alteração e eliminação tal qual uma tabela, através dos comandos ALTER ALTER SEQUENCE [schema.]sequence_name Controles sobre Sequences Alteradas Serão afetados apenas os números a serem gerados. Algumas validações serão feitas como um novo valor MAXVALUE,não poderá ser definido se menor que o não poderá ser alterada, neste caso, a sequence deverá ser eliminada e recriada. 30 Sequences podem ser submetidas a alteração e eliminação tal qual uma tabela, através dos comandos ALTER Algumas validações serão feitas como um novo valor MAXVALUE, não poderá ser definido se menor que o não poderá ser alterada, neste caso, a sequence deverá ser eliminada e recriada. 31 Indexando tabelas Tipos de índices 32 Utilizando índices • A tabela for pequena (pode ser armazenada em poucos blocos Oracle ). • As colunas não são freqüentemente usadas em condições. • A maior parte das queries recupera mais que 4% das linhas da tabela. • A tabela sofre alta taxa de atualização. Cuidados com índices É importante observar que Índices são objetos atualizados pelo Oracle, em todas as operações de INSERT, UPDATE e DELETE na tabela indexada, portanto, se por um lado aceleram a pesquisa aos dados através do comando SELECT, por outro lado, quanto mais Índice tiver a tabela, maior é o tempo de atualização da mesma. Criando um índice Vejamos a sintaxe do comando abaixo: CREATE INDEX [schema.]nome_indice ON tabela (coluna1 [, coluna2 [,...] ] ) Onde: Vejamos um exemplo. Desejamos criar um índice na coluna vendedor da tabela Cliente, o comando seria: CREATE INDEX IND_VEND ON CLIENTE(VENDEDOR) O índice criado para a coluna VENDEDOR da tabela CLIENTE, poderá ser utilizado como Chave Alternada de acesso àquela tabela, sendo referenciado numa cláusula WHERE dentro de um comando SELECT com por exemplo no comando:. SELECT * FROM CLIENTE WHERE VENDEDOR = 6 33 Eliminando um índice Índices podem ser eliminados mas nunca alterados. Para alterá-los devemos efetuar a remoção e depois recriá-los. O comando de eliminação de um índice é simples e exige apenas o privilégio DROP INDEX. Vejamos a sintaxe do comando abaixo: DROP INDEX [schema.]nome_índice Onde: Por exemplo para eliminarmos o índice criado na coluna Vendedor devemos dar o seguinte comando: DROP INDEX IND_VEND 34 Aula 09: Transações Propriedades das Transações Atomicidade - indivisibilidade, é a propriedade que garante que todas as operações de uma transação são refletidas corretamente no banco de dados ou nenhuma será. Uma transação é indivisível. Consistência - a execução de uma transação isolada (ou seja, sem a execução de outra transação qualquer concorrentemente) preserva a consistência do banco de dados. Isolamento - embora diversas transações possam ser executadas concorrentemente, o SGBD deve garantir que, para todo par de transações Ti, Tj, Ti tem a sensação de que Tj terminou suas operações, antes de Ti começar com as suas. Assim, cada transação não toma conhecimento de outras transações concorrentes no sistema. O sistema pode executar um pedaço de Ti parar e executar um pedaço de Tj, mas para as transações elas são únicas no sistema. Durabilidade - depois de a transação completar-se com sucesso, as mudanças que ela faz no banco dedados persistem, até mesmo se houver falha no sistema. Estados da Transação 35 ATIVA - ou estado inicial, a transação permanece neste estado enquanto está executando suas operações; EM EFETIVAÇÃO PARCIAL - após a execução da última operação; EFETIVADA - após a descoberta de que a execução normal da transação já não pode ser realizar; EM FALHA - depois que a transação foi desfeita e o banco de dados foi restabelecido ao estado anterior do início da execução da transação ABORTADA - após a conclusão da transação com sucesso. Transações concorrentes Os sistemas de processamento de transação de banco de dados, normalmente permitem que diversas transações sejam executadas de modo concorrente. Permitir que essas transações concorram na atualização dos dados traz diversas complicações em relação à consistência dos bancos de dados. Principal desvantagem das execuções concorrentes: Assegurar a consistência de transações, exige trabalho adicional, é mais fácil assegurar as propriedades ACID em transações com execução seqüencial do que em execuções concorrente. Principais vantagens de execuções concorrentes: • Uma transação consiste de diversos passos. Alguns envolvem atividades de E/S, outros, atividade de UCP; logo, atividades de E/S podem ser feitas em paralelo com o processamento de UCP; assim, o paralelismo entre atividades de E/S e atividades de UCP podem ser explorando para executar diversas transações em paralelo aumentando o “throughput” do sistema (um maior número de transações podem ser executadas num determinado tempo); • reduz o tempo médio de resposta para uma transação se completar após ser submetida, pois as transações curtas não precisam esperar que as transações longas terminem para serem iniciadas. Fonte: Adaptado de: Sistemas de Banco de Dados – Siberschatz, Korth e Sudarshan 5 Edição. Exemplo: Considere um sistema bancário simplificado com várias contas e um conjunto de transações que atualiza estas contas, e as operações: Read(X): transfere o item de dados X do banco de dados para o buffer alocado à transação; Write(X): transfere o item de dados X do buffer da transação para o banco de dados. Sejam T1 e T2 duas transações que transferem fundos de uma conta para outra. O saldo inicial de A é 100 e de B 200. A transação T1 transfere 50 reais da conta A para a conta B. A transação T2 transfere 10 por cento do saldo da conta A para a conta B. T1: read(A); A := A – 50; write(A); read(B); B := B + 50; write(B); T2: read(A); temp := A * 0,1; A := A – temp; write(A); read(B); B := B + temp; write(B); Fonte: Adaptado de: Sistemas de Banco de Dados – Siberschatz, Korth e Sudarshan 5 Edição. 36 Exemplo: Se executarmos as transações em sequencia primeiro T1 e depois T2 ou primeiro T2 e depois T1 os dados estarão consistentes pois, apesar dos saldos das contas serem diferentes no final da execução, o valor total das duas continua sendo de 300. Note: 1. O fato da execução das transações gerarem dados consistentes não significa que produzem o mesmo resultado. A mudança da ordem das transações pode provocar resultados diferentes. 2. A execução sequencial de duas transações sempre produz um resultado consistente. Fonte: Adaptado de: Sistemas de Banco de Dados – Siberschatz, Korth e Sudarshan 5 Edição Exemplo: Se executarmos as transações de forma concorrente, ou seja, intercalando operações das duas transações poderemos obter resultados inconsistentes. Repare que na execução dois o saldo total ficou em 310 devido ao fato de as duas transferências terem sido efetivadas a partir do saldo de 100 em A. Note: 1.Uma execução concorrente pode produzir ou não um resultado consistente. 2. Para mais informações a respeito do execução concorrente de transações vejo o material didático distribuído. É tarefa do sistema de Banco de Dados garantir que qualquer escala executada deixe o Banco de Dados em estado consistente. O componente do Sistema de Banco de dados que executa esta tarefa é chamado de componente de controle de concorrência, onde as únicas operações executadas por uma transação (significativas), do ponto de vista da escala de execução, são as instruções de leitura e de gravação. A melhor forma de assegurar a consistência do banco de dados, sob execução concorrente, garantindo que qualquer escala executadatenha o mesmo efeito de outra que estivesse sendo executada sem qualquer concorrência. Para saber mais a respeito de controle de concorrência AULA9CONTCONC.PDF O Oracle assegura a consistência dos dados baseado nas transações. As transações dão mais flexibilidade e controle quando da mudança do conteúdo das tabelas e asseguram a consistência dos dados em caso de falhas nos processos do usuário ou falhas no sistema. Uma transação consiste de comandos DML [Insert, update, delete, commit, rollback] que fazem uma mudança consistente nos dados. Por exemplo, uma transferência de valores entre duas contas bancárias implica no débito em uma conta e no crédito em outra no mesmo montante. Ambas as ações ou são realizadas ou são anuladas. O crédito não pode ser concretizado sem o correspondente débito. 37 Uma transação começa quando o primeiro comando SQL executável é encontrado e termina quando: Um comando COMMIT ou ROLLBACK aparece. Um comando DDL ou um comando DCL aparece. Certos erros são identificados, como um deadlock. O computador é desligado. Após uma transação terminar, o próximo comando SQL executável automaticamente inicia uma nova transação. Todas as modificações feitas durante a transação são temporárias até que a transação seja “commited” (concretizada). Situação do dado antes do COMMIT ou do ROLLBACK Situação do dado depois do COMMIT As operações de manipulação de dados primeiramente afetam o buffer do banco de dados. O usuário corrente pode rever os resultados das operações de manipulação de dados usando o comando SELECT. Outros usuários NÃO podem ver os resultados das operações de manipulação de dados do usuário corrente. As linhas afetadas ficam bloqueadas; outros usuários não podem modificar os dados existentes nas linhas afetadas. As modificações são concretizadas no banco de dados. O conteúdo anterior do dado é definitivamente perdido. Todos os usuários podem ver os resultados da transação. Os bloqueios nas linhas afetadas são desfeitos; as linhas ficam disponíveis para que outros usuários possam executar novas alterações. Situação do dado depois do ROLLBACK Situações nas quais o COMMIT e o ROLLBACK são implícitos As mudanças são desfeitas. O conteúdo anterior do dado é restabelecido. Os bloqueios nas linhas afetadas são desfeitos; as linhas ficam disponíveis para que outros usuários possam executar novas alterações. Execução de um comando DDL, como um CREATE TABLE COMMIT automático Saída normal do SQL*Plus, sem que tenha sido explicitado COMMIT ou ROLLBACK COMMIT automático Término anormal do SQL*Plus ou queda do sistema ROLLBACK automático Recomendação: Sempre explicitar o COMMIT e o ROLLBACK Preparação do ambiente Para fazermos o estudo dos comandos de controle de transação do Oracle inicialmente vocês devem fazer a conexão no Oracle utilizando a interface web e o usuário aulabd, senha estacio. A seguir abra uma janela de outro browser e digite na URL http://localhost:8080/apex, a seguir faça a conexão utilizando o mesmo usuário da janela anterior. Importante se você abrir a nova conexão utilizando o atalho do menu iniciar o exercício não irá funcionar. Agindo assim você deverá ter duas janelas de conexão para o Oracle na área de trabalho. A seguir desmarque o commit automático em ambas as janelas. 38 Note: Cada janela no Oracle corresponde a uma conexão diferente, mesmo que estejamos utilizando o mesmo usuário. O controle de transação e de concorrência é feito pela sessão e não pelo usuário. Desta forma ao abrirmos duas janelas poderemos trabalhar como se fossem duas pessoas diferentes acessando o banco e vermos o controle de concorrência funcionar. Vamos dar agora o seguinte comando em uma das janelas: CREATE TABLE COPIA_CLIENTE AS SELECT * FROM CLIENTE Note: 1.- Este comando cria uma tabela chamada COPIA_CLIENTE que possui a mesma estrutura da tabela Cliente. 2 – O comando copia para a tabela criada as linhas geradas pela subconsulta ( Select * from cliente), no caso todas as linhas da tabela cliente. 3 – As constraints não são copiadas de uma tabela para a outra. PREPARAÇÃO DO AMBIENTE Podemos em ambas as janelas acessar o conteúdo da tabela pelo comando: SELECT * FROM COPIA_CLIENTE Para visualizarmos o controle de transação vamos agora na janela da esquerda dar o seguinte comando: INSERT INTO COPIA_CLIENTE VALUES ( 1, ‘TESTE’,6) Note: 1 – Este comando insere uma nova linha na tabela COPIA_CLIENTE 2 –o COMMIT AUTOMATICO DEVE ESTAR DESMARCADO para que o exercício funcione corretamente. 39 Comandos de controle de transação - commit Vamos acessar o conteúdo da tabela em ambas as janelas: SELECT * FROM COPIA_CLIENTE Note: O cliente inserido pode ser visualizados pelo usuário que os executou ( janela da esquerda) em sua própria sessão, através de comandos SELECT. O mesmo usuário em outra sessão (a interface web da direita) ou outros usuários que tenham acesso à mesma tabela, ainda não terão acesso a estes novos dados. Podemos neste momento executar dois tipos de comando: Confirmar (COMMIT) a inclusão dos novos cursos, liberando a informação para as demais sessões e usuários, ou Reverter (ROLLBACK) o comando, cancelando as inserções. Se executado um comando COMMIT explícito, ou outro que o contenha implicitamente, os comandos de manipulação de dados que estejam pendentes serão confirmados e liberados para outras sessões / usuários. Se executado um comando ROLLBACK, todas as operações pendentes serão desfeitas Vamos comandar então commit: O comando de COMMIT confirma a transação e torna as alterações visíveis para os outros usuários. Vamos novamente acessar o conteúdo da tabela em ambas as janelas: SELECT * FROM COPIA_CLIENTE Repare que agora a linha inserida se tornou visível para o usuário da janela da direita. 40 Comandos de controle de transação - rollback Vamos agora na janela da esquerda dar o seguinte comando: DELETE COPIA_CLIENTE O Oracle informa que as 6 linhas foram eliminadas. Vamos novamente acessar o conteúdo da tabela em ambas as janelas: SELECT * FROM COPIA_CLIENTE Nota: Repare que na janela da esquerda não aparece nenhuma linha, pois ali foi executado o comando de delete. Na janela da direita, com não foi comandado COMMIT ainda aparecem os dados na tabela. Podemos novamente neste momento comandar COMMIT ou ROLLBACK.: Vamos comandar então ROLLBACK na janela da esquerda: ROLLBACK Nota: O comando de ROLLBACK desfaz TODAS as operações da transação e retorna os dados à condição inicial 41 Vamos novamente acessar o conteúdo da tabela em ambas as janelas: SELECT * FROM COPIA_CLIENTE Nota: 1 - Repare que agora as linhas voltaram a aparecer para o usuário da janela da esquerda. 2 – O usuário da direita sequer tomou conhecimento que as linhas chegaram a ser eliminadas. Utilizando subconsulta em comando de DML Inicialmente vamos agora ficar apenas com uma janela aberta, feche portanto e janela da direita. A seguir marque a opção de COMMIT AUTOMATICO e digite o comando: DELETE COPIA_CLIENTE Nota: O COMMIT AUTOMATICO deve estar marcado. Repare que a tabela esta vazia. Eventualmente, podemos inserir várias linhas numa tabela a partir de um único comando INSERT, visto que este pode receber o resultado de uma subquery. Para inserirmos agora na tabela COPIA_CLIENTE todo o conteúdo da tabela CLIENTE daríamos o seguinte comando: INSERT INTO COPIA_CLIENTE (SELECT * FROMCLIENTE) Nota: O INSERT com subconsulta inseriu 5 linhas na tabela COPIA_CLIENTE, conforme pode ser comprovado se acessarmos o conteúdo da tabela. 42 Utilizando subconsulta em comando de DML - Update Vamos supor que a empresa resolveu colocar todos os cliente sendo atendidos pelo empregado de Ult_nome Ugarte. Como na tabela de cliente não temos o nome do vendedor a forma de executar o comando é através de uma subquery, que retorna o ID do empregado a partir de seu sobrenome para o comando UPDATE. O comando seria. UPDATE COPIA_CLIENTE SET VENDEDOR = ( SELECT ID FROM EMPREGADO WHERE UPPER(ULT_NOME) = 'UGARTE') Vamos supor que a empresa resolveu apagar os dados de todos os cliente atendidos pelo empregado de Ult_nome Ugarte. Como na tabela de cliente não temos o nome do vendedor a forma de executar o comando é através de uma subquery, que retorna o ID do empregado a partir de seu sobrenome para o comando DELETE. O comando seria. DELETE FROM COPIA_CLIENTE WHERE VENDEDOR IN( SELECT ID FROM EMPREGADO WH ERE UPPER(ULT_NOME) = 'UGARTE') Definindo o dicionário de dados Oracle O dicionário de dados é o ponto central do banco de dados Oracle. É nele que ficam armazenadas todas as informações manipuladas pelo banco Oracle. Além disso, o dicionário de dados é uma ferramenta disponibilizada de extrema importância para os diversos níveis de usuários do BD Oracle, como: Usuários finais Desenvolvedores Administradores de banco de dados Como regra básica do RDBMS Oracle, a fim de garantir a integridade dos dados, nenhum comando DML (Insert, Update, Delete) pode ser efetuado sobre o dicionário de dados. O armazenamento no dicionário de dados O dicionário de dados nada mais é que uma composição de tabelas e visões diversas, que armazenam todas as informações a serem manipuladas e fornecidas sobre o banco de dados associado ao mesmo. Todas estas são definidas como read-only. O dicionário de dados é criado juntamente com o banco de dados. Sempre que o banco de dados Oracle está operante, o RDBMS se encarrega de manter o dicionário de dados atualizado e íntegro. Os nomes dos usuários do banco de dados; Informações sobre restrições de integridade; 43 Informações de auditoria, por exemplo, quem acessou ou alterou determinados objetos; Informações sobre restrições de integridade; Nome dos objetos de um schema, como tabelas, visões, Índices, sequences, etc; Informações sobre os objetos acima referenciados, como data de criação, etc; Informações sobre privilégios e roles concedidos e recebidos; Qualquer outra informação sobre o banco de dados; Extraindo Informações Como o dicionário de dados está estruturado em tabelas e views, assim como quaisquer outros objetos do banco de dados, a extração de dados é feita através do comando SELECT. A Estrutura do Dicionário de Dados O dicionário de dados está dividido em: Nota: É importante ressaltar que as tabelas criadas pelos usuários (tabela CLIENTE por exemplo) não são as tabelas básicas do dicionário de dados. Apenas as informações referentes a estas tabelas de usuários é que ficam armazenadas no dicionário, como nome, usuário dono, data de criação, etc. Acessando visões do dicionário de dados As visões do dicionário de dados são divididas em categorias, a fim de que se possa restringir o acesso das mesmas para diferentes usuários. Os nomes atribuídos às visões do dicionário de dados são definidos a fim de identificar a finalidade de cada uma. Além disto, as visões são divididas em três categorias, que identificam o tipo de acesso a cada uma, diferenciadas por um prefixo: 44 Outras Visões/ Tabelas Identificação da estrutura da tabela table_privileges Por exemplo vamos identificar a estrutura da tabela table_privileges. Para isso comande: DESC TABLE_PRIVILEGES Visualizando o dicionário de dados Eventualmente podemos inspecionar o dicionário de dados do Oracle para confirmar os privilégios que temos direitos evitando operações não autorizadas, nomes de objetos que criamos como tabelas, Índices, constraints, sinônimos etc. Abaixo temos uma série de exemplos de como devemos proceder na consulta ao Dicionário de Dados do Oracle. 45 Para visualizar direitos e privilégios, devemos verificar as seguintes views: Queremos identificar todas as tabelas do dicionário de dados: SELECT * FROM DICTIONARY Na figura esta sendo exibida apenas uma pequena parte do resultado da consulta. Queremos identificar o nome, o tipo e data de criação de todos os objetos criados no esquema do usuário que estamos logados, ordenados por nome do objeto. O comando seria: SELECT OBJECT_NAME, OBJECT_TYPE, CREATED FROM USER_OBJECTS ORDER BY OBJECT_NAME; Na figura esta sendo exibida apenas uma pequena parte do resultado da consulta. 46 Queremos identificar todas as constraints definidas para a tabela EMPREGADO SELECT CONSTRAINT_NAME, SEARCH_CONDITION, STATUS FROMUSER_CONSTRAINTS WHERETABLE_NAME = 'EMPREGADO' Queremos identificar identificar para quais colunas da tabela EMPREGADO foram definidas CONSTRAINTS SELECTCONSTRAINT_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = 'EMPREGADO Para saber mais a respeito do dicionario de dados acesse a documento do Oracle disponível aqui. 47 Aula 10: Execução de comandos e otimização Otimização e execução de comandos O processo de submissão de uma consulta segue os seguintes passos: Otimização heurística de consultas 48 Dada a consulta: SELECT C.NOME, C.NOME, E.ULT_NOME, D.NOME FROM CLIENTE C INNER JOIN EMPREGADO E ON C.VENDEDOR = E.ID INNER JOIN DEPARTAMENTO D ON E.ID_DEPTO = D.ID WHERE C.ID < 130 Esta então traduzida para uma expressão algébrica equivalente (representada normalmente com uma arvore): A partir da arvore é realizada a otimização segundo as seguintes regras: • Executar seleções e projeções o mais cedo possível - Essas operações geram resultados menores (menos linhas, menos colunas) que seus operandos • Executar operações binárias o mais tarde possível - Operações binárias aumentam o volume de dados (efeito multiplicativo) e são onerosas em termos de tempos de execução • Transformar as operações de produto cartesiano sempre que possível em operações de junção Aplicando estas regras a arvore de execução seria transformada para arvore da figura. A partir desta arvore o otimizador poderia definir os métodos de acesso físico aos dados. Para saber mais a respeito da otimização heurística veja o 49 Cap15 Algoritmos para Processamento e Otimização de Consultas do livro ELMASRI, R.; NAVATHE, S., Sistemas de Banco de Dados. Pearson Education do Brasil, 4ª.Ed 2005 no material didático distribuído para a disciplina. Estratégias para otimização de consultas Estatísticas Para gerar estatísticas em uma tabela cada SGBD disponibiliza comandos: – Oracle e PostgreSQL: analyze; – DB2: runstats; – SQLServer: create/update statistics; • Tipos de estatísticas – de tabelas: número de linhas, blocos, tamanho de registro médio – de colunas: número de valores distintos, número de NULLs, histograma devalores – de índices: número de folhas, níveis, clustering. – de sistema: utilização típica de I/O e de CPU. • A partir destas informações, o SGBD pode estimar o custo envolvido em acessar a tabela de forma seqüêncial ou através de índices. • Com estes custos iniciais, o SGBD realiza cálculos para determinar os custos das operações subseqüentes aplicadas no plano de acesso. Isto leva à determinação do custo total do plano de acesso. Planos de Execução 50 Tipos de Operação Varreduras seqüenciais Varredura indexada Ordenação Eliminando Duplicatas e agregações Operação de Conjunto Junções 51 52 As fases do processamento de comandos SQL no Oracle Otimizador Oracle 53 54 ATENÇÃO: Sempre que possível, escreva condições que comparem colunas com constantes, ao invés de condições com expressões envolvendo colunas. 55 ATENÇÃO: Utilizando a abordagem por REGRA, o otimizador faz esta transformação porque cada componente da query composta pode ser executado utilizando um índice. Utilizando a abordagem por CUSTO, o otimizador compara o custo da execução original usando full table scan com o resultado da query composta, determinando ao final se faz ou não a transformação. 56 57 58 O número de blocos visitados para executar um full table scan é 80/8 = 10; Este é um índice não- único, logo a seletividade é calculada como 0,17 (6 valores distintos para codigo_depart). O custo total é 0,17 * 80 = 14; Este é um índice único porém em um intervalo limitado de valores. A seletividade é calculada como 0,19(intervalo da condição 250 – 100 dividido pelo intervalo do índice 800 – 1). O custo total é 0,19 * 80 = 16; Este caminho está disponível para todos os comandos SQL. Seu rank é 15; Este caminho está disponível por causa da condição ‘codigo_depart = 3’. Seu rank é 9 (single- column index); Este caminho está disponível por causa da condição ‘matricula BETWEEN 100 AND 250’. Seu rank é 10 (bounded range search); Atenção: O índice em nome não é considerado, porque ele não é referenciado na cláusula WHERE. O otimizador escolhe usar o índice I_COD_DEPART porque ele tem o menor número no ranking de caminhos de acessos disponíveis. O full table scan tem o maior número dentre todos os caminhos. Logo o otimizador sempre escolhe qualquer outro caminho disponível. Você calcula estatísticas exatas com esta opção. Ela bloqueia a tabela e executa um full table scan. Pode levar muito tempo se a tabela é muito grande. Você jamais deverá usar esta opção e logados e acessando a tabela. Você somente estima estatísticas com esta opção. Contudo, se você utilizá aceitável, ela pode ser considerada tão realista quanto a opção COMPUTE STATISTICS. Limpa todas as estatísticas com esta opção. Não é necessário usá Você calcula estatísticas exatas com esta opção. Ela bloqueia a tabela e executa um full table scan. Pode levar muito tempo se a tabela é muito grande. Você jamais deverá usar esta opção enquanto usuários estão Você somente estima estatísticas com esta opção. Contudo, se você utilizá-la com uma proporção de dados aceitável, ela pode ser considerada tão realista quanto a opção COMPUTE STATISTICS. s estatísticas com esta opção. Não é necessário usá-la antes de reanalisar um objeto. 59 Você calcula estatísticas exatas com esta opção. Ela bloqueia a tabela e executa um full table scan. Pode nquanto usuários estão la com uma proporção de dados la antes de reanalisar um objeto.
Compartilhar