Apostila Oracle - DCC
Transcrição
Apostila Oracle - DCC
Apostila Oracle Ricardo Terra rterrabh [at] gmail.com Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 1 CV Nome: Ricardo Terra Email: rterrabh [at] gmail.com www: ricardoterra.com.br Twitter: rterrabh Lattes: lattes.cnpq.br/ 0162081093970868 Ph.D. (UFMG/UWaterloo), Post-Ph.D. (INRIA/Université Lille 1) Background Acadêmico: UFLA (desde 2014), UFSJ (1 ano), FUMEC (3 anos), UNIPAC (1 ano), FAMINAS (3 anos) Profissional: DBA Eng. (1 ano), Synos (2 anos), Stefanini (1 ano) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 2 Aula 01 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 3 Instalando o servidor Oracle XE n Baixando o Oracle XE q q n Registrar-se na Oracle Ir ao endereco http://www.oracle.com/technology/software/products/database/xe/index.html Instalando o Oracle XE q Depois que já baixar o instalador do Oracle, basta executá-lo e seguir os passos n q Você deverá registrar o cadastro de uma senha para o usuário system Dica: n n Geralmente o próprio instalador já o configura como serviço do Windows, porém, como o mesmo é muito pesado, vá em Serviços e configure o serviço OracleXETNSListener e OracleServiceXE para iniciar manualmente Assim, quando você precisar utilizar o banco de dados, basta ir na opção Start Database disponível no menu iniciar Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 4 Instalando o cliente Oracle XE n Baixando o Oracle SQL Developer q q q n Registrar-se na Oracle Ir ao endereco http://www.oracle.com/technology/software/products/sql/index.html Este é um cliente gráfico gratuito e muito utilizado Baixando o SQL*Plus q q q O SQL*Plus é largamente utilizado por DBAs e desenvolvedores para a interação com a base de dados. Usando o SQL*Plus, você pode executar todas as instruções SQL e programas PL/SQL, formatar resultados de consultas e administrar a base de dados Ir ao endereço http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/ 10201winsoft.html Baixar Oracle Database 10g Client Release 2 (10.2.0.1.0) n Instalar pela opção Runtime. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 5 Configurando o SQL*Plus n Configurando o SQL*Plus q Depois de instalado, basta entrar no Net Configuration Assistant. n n n n n n q Selecione Configuração do Nome do Serviço de Rede Local Selecione Adicionar Em nome do serviço: xe Selecione TCP Em nome do host deve ser inserido o IP ou hostname onde encontra-se instalado o Oracle Realize o teste q Talvez seja necessário alterar o login para o teste Com isto, o SQL*Plus já estará funcionando perfeitamente. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 6 Arquitetura do Oracle n Algumas arquiteturas de hardware permitem múltiplos computadores compartilharem os mesmos dados, softwares ou periféricos. O Oracle permite tirar proveito dessa característica através da execução de múltiplas instancias que compartilham um único banco de dados. Assim os usuários de múltiplas maquinas podem acessar o mesmo banco de dados com uma melhoria da performance. n Quando o Oracle é iniciado um grupo de buffers de memória denominado System Global Área (SGA) é alocado e alguns processos que permanecem em background são inicializados. A combinação dos buffers com os processos em background formam uma instância. n SGA é um grupo de buffers de memória compartilhados que são alocados pelo Oracle para uma instância. n Processos em background executam tarefas distintas assincronamente em benefício de todos os usuários de um banco de dados. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 7 Arquitetura do Oracle n Tablespace é uma sub-divisão lógica de um banco de dados utilizado para agrupar estruturas lógicas relacionadas. n As tablespaces apenas especificam a localização de armazenamento do banco de dados e são armazenadas fisicamente em datafiles, que alocam imediatamente o espaço especificado na sua criação. n A primeira tablespace criada pelo Oracle é a SYSTEM. n Existe um relacionamento "um para muitos" entre os bancos e as tablespaces e um relacionamento "um para muitos" entre as tablespaces e os datafiles. A qualquer momento um datafile pode ser incluído em uma tablespace. n Um banco de dados pode ter vários usuários, cada qual com seu esquema, que nada mais é do que uma coleção lógica de objetos de banco de dados, como tabelas e índices. Por sua vez, esses objetos referem-se às estruturas físicas dos dados, que são armazenados nos datafiles das tablespaces. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 8 Usuários do Oracle n Usuários q SYS n n q SYSTEM n n n n q Usuário que é conhecido como o proprietário do dicionário. Ele possui todas as tabelas bases e visões de acesso ao usuário de um dicionário de dados. Nenhum usuário deve alterar (UPDATE, DELETE ou INSERT) qualquer linha ou objetos de esquema contidos no esquema SYS, pois tal ação pode comprometer a integridade dos dados. Usuário do DBA. Pode-se dizer que o SYSTEM é um pouco mais “ fraco” que o SYS. Responsável pela criação de tabelas e visões adicionais que exibem informações administrativas e/ou são utilizadas pelas ferramentas da Oracle. Nunca deve-se criar tabelas no esquema SYSTEM de interesse de usuários particulares. HR n Esquema de exemplo. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 9 Criando seu próprio usuário n Para criar seu próprio usuário, basta: create user <nome_do_usuario> identified by <senha_do_usuario>; grant connect, resource, create view, create sequence, create synonym, create trigger, create procedure to <nome_do_usuario>; n Veremos maiores detalhes sobre as instruções acima nas aulas posteriores. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 10 Tabela dual n n A tabela dual é uma tabela que é criada pelo Oracle junto com o dicionário de dados. Ela consiste exatamente de uma única coluna cujo nome é DUMMY e um registro cujo valor é “X”. SQL> desc dual Name Null? Type ----------------------- -------- ---------------DUMMY VARCHAR2(1) SQL> select * from dual; D X n O proprietário da tabela dual é o SYS, mas ela pode ser acessada por qualquer usuário. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 11 Tabela dual n A tabela dual é a tabela predileta para selecionar uma pseudo-coluna (tal como sysdate) SQL> select sysdate from dual SYSDATE -------------15-FEB-08 n Mesmo sendo possível excluir o único registro ou inserir registros adicionais, realmente isto não deve ser feito. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 12 Comandos importantes n SPOOL <caminho_do_arquivo> q Grava toda a saída a partir deste comando no arquivo indicado até que seja inserido o seguinte comando: n SPOOL off Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 13 Comandos importantes n SHOW <opcao> q ALL n q SGA n q Exibe o tamanho atual da SGA (System Global Area) RELEASE n q Exibe todas as informações do ambiente Exibe o número da versão atual do Oracle USER n Exibe o usuário corrente Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 14 Comandos importantes n PASSWORD <usuario> q n CONNECT <usuario>/<senha>@<nome_do_servico> q n Conecta em dado usuário. DISCONNECT q n Altera a senha de um dado usuário. Comita alterações pendentes na base de dados e desconecta o usuário do Oracle, porém não encerra o SQL*Plus. EXIT ou QUIT q Desconecta o usuário do Oracle e encerra o SQL*Plus. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 15 Comandos importantes n / (barra) q n EDIT ou EDIT <nome_do_arquivo> q n Executa o comando SQL que está no buffer. Edita o buffer ou um arquivo @<caminho_do_arquivo> q q Executa todo o arquivo de script Dica: n Caso queira inserir um comentário em um script basta inseri-lo entre /* e */. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 16 Comandos importantes n SET TIME ON q n SET LINESIZE <tamanho> q q n q Redimensiona o tamanho da página a ser exibida. Geralmente 40. PAUSE <texto> q n Redimensiona o tamanho da linha a ser exibida. Geralmente 100. SET PAGESIZE <tamanho> q n Exibe a hora no prompt. Exibe a mensagem e espera até que o usuário digite ENTER. PROMPT <texto> q Exibe a mensagem. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 17 Comandos importantes n select sysdate from dual; q n select systimestamp from dual; q n Exibe a data e a hora. select TABLE_NAME from USER_TABLES; q n Exibe a data. Exibe a listagem de todas as tabelas do usuário ativo. DESC <nome_da_tabela> ou DESCRIBE <nome_da_tabela> q Descreve a estrutura da tabela. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 18 Comandos importantes n CLEAR <opcao> q screen n q buffer n n Limpa a tela. Limpa o buffer. Dica interessante: q A seta para cima não volta aos comandos, para isto, no SQL*Plus, basta selecionar o texto que você deseja e, antes de soltar o botão esquerdo, aperte o botão direito. Assim, ele copiará o texto que você selecionou. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 19 Utilizando parametrização n Às vezes é necessária a parametrização de algum comando e isto pode ser facilmente realizado. SQL> select &campo from dual Enter value for campo: sysdate old 1: select &campo from dual new 1: select sysdate from dual SYSDATE -------------15-FEB-08 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 20 Aula 02 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 21 Categorias de Instruções SQL n DML (Data Manipulation Language) q Utilizada para acesso, criação, alteração e exclusão de dados em existentes estruturas do banco de dados. n q n SELECT, INSERT, UPDATE, DELETE, EXPLAIN PLAN e LOCK TABLE são as instruções de DML. Também conhecida como LMD (tradução desnecessária) DDL (Data Definition Language) q Utilizada para criar, alterar ou destruir objetos do banco de dados e seus privilégios. n q CREATE, ALTER, DROP, RENAME, TRUNCATE, GRANT, REVOKE, AUDIT, NOAUDIT e COMMENT são as instruções de DDL. Também conhecida como LDD (tradução desnecessária) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 22 Categorias de Instruções SQL n Controle de Transação (Transaction Control) q Utilizada para agrupar um conjunto de instruções como uma única transação. n n Controle de Sessão (Session Control) q Utilizada para controlar as propriedades de uma sessão de um usuário. n n COMMIT, ROLLBACK, SAVE POINT, SET TRANSACTION são as instruções de controle de transação. ALTER SESSION, SET ROLE são as instruções de controle de sessão. Controle de Sistema (System Control) q Utilizada para controlar as propriedades do banco de dados. n ALTER SYSTEM é a única instrução de controle de sistema. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 23 Literais n Literais são valores que representam uma constante. Existem quatro tipos de literais: q Texto (ou caractere) n Entre aspas simples. q q Inteiro n Número sem casas decimais. q q Ex.: -2 43 0 Número n Números inteiros e números decimais. q q Ex.: 'Firefox', 'Maria José' Ex.: 32 -2,55 3,14159 23e-10 Intervalo n Especifica um período de tempo em termos de anos e meses ou de dias e segundos. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 24 Como trabalhar com outro esquema n Como já foi visto, o usuário no Oracle tem o seu próprio esquema. n Na maioria das aulas irei criar um esquema que será utilizado em nosso aprendizado. O esquema de hoje será um do próprio Oracle denominado HR. n O usuário HR vem bloqueado. Para desbloqueá-lo deve realizar o seguinte comando utilizando o usuário SYSTEM: q n alter user HR identified by HRPASSWORD ACCOUNT UNLOCK; Para permitir um usuário a acessar tabelas de um outro usuário devese realizar o seguinte comando utilizando o usuário SYSTEM: q grant select on <usuário_origem>.<tabela> to <usuário_destino>; n Ex.: q grant select on HR.DEPARTMENTS to TERRA; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 25 Como trabalhar com outro esquema n As tabelas não pertencem ao seu usuário, mas sim, ao usuário HR, portanto para verificar as tabelas, que possui acesso, de um outro esquema deve ser realizado o seguinte comando: q n select TABLE_NAME from ALL_TABLES where OWNER = 'HR'; Portanto, para acessar, descrever, enfim, qualquer ação nas tabelas deste esquema deve-se colocar o nome do esquema antes da tabela: q q select * from HR.DEPARTMENTS; desc HR.DEPARTMENTS; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 26 Esquema HR Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 27 Instruções Básicas de SQL n O SQL é a linguagem padrão ANSI para acesso a banco de dados relacionais e engloba tanto uma DDL quanto uma DML. n É uma linguagem baseada em conjunto, isto é, com um simples comando recuperamos um conjunto de registros, sem precisarmos efetuar leituras registro a registro. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 28 Select n Comando para seleção (recuperação) de dados em uma tabela. q Uma sintaxe básica (veremos order by ainda hoje e where futuramente): n q Na lista de colunas, deverá ser citada cada uma das colunas separadas por vírgula (,). n q select <lista_de_colunas> from <TABELA> where <condições> order by <lista_de_colunas> <asc|desc> select JOB_TITLE, MIN_SALARY, MAX_SALARY from HR.JOBS; A lista de colunas pode ser substituida por asterisco (*), que indica a exibição de todas as colunas. n select * from HR.JOBS; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 29 Select n Em algumas situações o nome do cabeçalho de uma coluna pode apresentar pouco significado. Nestas situações pode ser utilizado um ‘apelido’ para a coluna. n O ‘apelido’ é definido logo em seguida ao nome da coluna com um espaço ou utilizando a palavra AS. É recomendável inserir o nome do ‘apelido’ entre aspas duplas ("). q n Caso o apelido tenha espaço, é obrigatório o uso de aspas duplas ("). Exemplo: q select JOB_TITLE AS “CARGO", MIN_SALARY AS "SALÁRIO BASE" from HR.JOBS; equivalente a q select JOB_TITLE “CARGO", MIN_SALARY "SALÁRIO BASE" from HR.JOBS; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 30 Select distinct n Em algumas situações o resultado pode apresentar repetição. n Buscar os códigos de departamento dos empregados, por exemplo, retornará código de departamento repetido caso exista mais de um empregado que trabalhe em um mesmo departamento. q Observe: n select DEPARTMENT_ID from HR.EMPLOYEES order by 1; q n Veremos em um futuro próximo o que quer dizer “order by 1” Para evitar registro repetidos basta inserir a palavra DISTINCT. q select distinct DEPARTMENT_ID from HR.EMPLOYEES order by 1; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 31 Select distinct n Esta unicidade não é aplicada somente à primeira coluna, mas sim, a toda linha. Observe fazendo a seguinte consulta: q n select distinct DEPARTMENT_ID, JOB_ID from HR.EMPLOYEES order by 1; Observe que não houve um único registro com o mesmo código de departamento e código do cargo. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 32 Ordenando n Ordenação (ORDER BY) q q q A expressão ORDER BY é utilizada quando se pretende exibir os registros em uma determinada ordem, seja esta crescente (ASC default) ou decrescente (DESC). Caso a expressão não esteja presente, os registros serão exibidos na ordem em que foram inseridos na tabela. Os campos que constam na expressão ORDER BY não devem obrigatoriamente estarem presentes na expressão SELECT. Caso estejam presentes, pode-se utilizar números indicando que a ordenação será feira por determinado campo de acordo com a ordem do select. Não é necessário possuir um índice fisicamente criado e composto pelos campos da ordenação para usar o ORDER BY. No entanto, na existência do índice, o comando será executado com melhor desempenho. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 33 Ordenando n Deve ser a última instrução do select e tem a seguinte sintaxe: q select ... order by <lista_de_colunas> <asc|desc> <nulls first|nulls last> n n A lista de colunas pode referenciar à posição da coluna na lista de colunas do select, caso a coluna esteja sendo exibida. Exemplos: q select FIRST_NAME, SALARY from HR.EMPLOYEES order by SALARY desc equivale a q select FIRST_NAME, SALARY from HR.EMPLOYEES order by 2 desc n Observe que caso a ordenação seja feita por uma coluna que não esteja na lista de colunas do select, esta sintaxe não é possível. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 34 Ordenando n Mais exemplos: q Listagem do código do departamento e do nome do empregado ordenados ascendentemente pelo código do departamento: n q Agora ordenados descendentemente: n q select DEPARTMENT_ID, FIRST_NAME from HR.EMPLOYEES order by DEPARTMENT_ID asc select DEPARTMENT_ID, FIRST_NAME from HR.EMPLOYEES order by DEPARTMENT_ID desc Agora ordenados descendentemente pelo código do departamento e ascedentemente pelo nome do empregado: n select DEPARTMENT_ID, FIRST_NAME from HR.EMPLOYEES order by DEPARTMENT_ID desc, FIRST_NAME asc Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 35 Ordenando n Ainda mais exemplos: q Listagem do código do departamento e do nome do empregado ordenados ascendentemente pelo código do departamento, porém com a exibição de departamentos nulos no início: n q select DEPARTMENT_ID, FIRST_NAME from HR.EMPLOYEES order by DEPARTMENT_ID asc nulls first; Agora com os departamentos nulos no final: n select DEPARTMENT_ID, FIRST_NAME from HR.EMPLOYEES order by DEPARTMENT_ID asc nulls last; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 36 Limitando o número de resultados n As vezes você não está interessado em ver o resultado como um todo, mas sim, alguma parte do resultado. Para isto, o Oracle utiliza de uma pseudo-coluna conhecida como ROWNUM a partir do resultado. n Por exemplo, a seguinte consulta retorna o nome e o salário de todos os empregados ordenados do maior salário para o menor. q n Mas o interesse é obter somente a lista dos TOP 3. Portanto o uso de ROWNUM é indispensável: q n select FIRST_NAME, SALARY from HR.EMPLOYEES order by SALARY desc; select * from ( select FIRST_NAME, SALARY from HR.EMPLOYEES order by SALARY desc ) where ROWNUM <= 3; A princípio parece ser difícil já que outros SGBDs fazem de um modo muito mais fácil, porém estamos trabalhando com um SGBD de alta robustez. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 37 Aula 03 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 38 Esquema RH (tradução do HR) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 39 Restrigindo n Existem vários operadores que pode ser usados para a restrição do número de resultados. q Operadores de comparação n n n q Equalidade: = Diferença: <> != Demais: q > < >= <= q ANY q SOME q ALL q Outros operadores: n ^= IN NOT IN BETWEEN EXISTS IS NULL q IS NOT NULL LIKE q n n n n Lógicos: n AND OR NOT q A utilização dos operadores lógicos utiliza a precedência de parênteses como em linguagens de programação. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 40 Restrigindo n Operadores de comparação q q São operadores que retornam um booleano como resultado. Permite que realizemos consultas restringindo pela equalidade, diferença, relações de maior, maior ou igual, menor e menor ou igual. n O oracle acrescenta os operadores SOME, ANY e ALL. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 41 Restrigindo n Um exemplo de uso da equalidade é a busca dos empregados de um determinado departamento, por exemplo: q n select * from RH.EMPREGADO where ID_DEPARTAMENTO = 90 Para retornar os que não estão em um departamento basta: q select * from RH.EMPREGADO where ID_DEPARTAMENTO <> 90 equivalente a q select * from RH.EMPREGADO where ID_DEPARTAMENTO != 90 equivalente a q select * from RH.EMPREGADO where ID_DEPARTAMENTO ^= 90 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 42 Restrigindo n Um exemplo de uso da relação de maior, maior ou igual, menor ou menor ou igual é o busca de um empregado que possui um salário maior que um certo valor e menor do que outro. q select * from RH.EMPREGADO where SALARIO > 13000 n q select * from RH.EMPREGADO where SALARIO >= 13000 n q Busca os que possuem salário maior ou igual a 13.000 select * from RH.EMPREGADO where SALARIO < 13000 n q Busca os que possuem salário maior que 13.000 Busca os que possuem salário menor que 13.000 select * from RH.EMPREGADO where SALARIO <= 13000 n Busca os que possuem salário menor ou igual a 13.000 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 43 Restrigindo n ANY ou SOME q q São utilizados para comparar um valor para cada valor em uma lista ou subquery. Devem ser precedidos por um dos operadores de comparação. n q =, <>, !=, ^=, <, <=, >, ou >= Para retornar os usuário que estão em uma determinada lista de departamentos utilizamos: n select * from RH.EMPREGADO where ID_DEPARTAMENTO = ANY (90,100) equivalente a n select * from RH.EMPREGADO where ID_DEPARTAMENTO = SOME (90,100) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 44 Restrigindo n ALL q q q São utilizados para comparar um valor para todos os valores em uma lista ou subquery. Assim como SOME e ANY, devem ser precedidos por um dos operadores de comparação. Para retornar os usuário que não estão nos departamentos de uma determinada lista utilizamos: n select * from RH.EMPREGADO where ID_DEPARTAMENTO != ALL (10,30,50) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 45 Restrigindo n Operadores lógicos q q Os operadores lógicos são utilizados para combinar os resultados de duas condições de comparação para produzir um único resultado (AND e OR) ou para inverter o resultado de uma simples comparação (NOT). NOT n Às vezes, no português falado dizemos, por exemplo, que queremos todos os funcionários que não estão no departamento 30. n A consulta abaixo faz exatamente isto: q n select * from RH.EMPREGADO where not ID_DEPARTAMENTO = 30 E poderia ser feito, de forma equivalente, como abaixo: q select * from RH.EMPREGADO where ID_DEPARTAMENTO <> 30 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 46 Restrigindo n Operadores lógicos q AND n Às vezes, no português falado dizemos, por exemplo, que queremos todos os funcionários que estão no departamento 30 e que ganham mais de 3.000. n A consulta abaixo faz exatamente isto: q q select * from RH.EMPREGADO where ID_DEPARTAMENTO = 30 AND SALARIO > 3000 OR n Existem outros casos que queremos todos os funcionários que estão no departamento 30 ou que ganham mais de 17.000. n A consulta abaixo faz exatamente isto: q select * from RH.EMPREGADO where ID_DEPARTAMENTO = 30 OR SALARIO > 17000 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 47 Restrigindo n IN e NOT IN q q q q O operador IN e NOT IN são utilizados para testar uma condição de um grupo de valores. IN é equivalente ao operador =ANY, pois ambos retornam verdadeiro se um valor existe em uma lista ou em uma subquery. NOT IN é equivalente ao operador !=ALL, pois ambos retornam verdadeiro se o valor não existe em uma lista ou em uma subquery. Vamos ver um exemplo no próximo slide e um comparativo com o ANY e SOME. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 48 Restrigindo n IN q Para retornar os usuário que estão em uma determinada lista de departamentos utilizamos: n select * from RH.EMPREGADO where ID_DEPARTAMENTO IN (90,100) equivalente a n n select * from RH.EMPREGADO where ID_DEPARTAMENTO = ANY (90,100) NOT IN q Para retornar os usuário que não estão nos departamentos de uma determinada lista utilizamos: n select * from RH.EMPREGADO where ID_DEPARTAMENTO NOT IN (10,30,50) equivalente a n select * from RH.EMPREGADO where ID_DEPARTAMENTO != ALL (10,30,50) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 49 Restrigindo n BETWEEN q q O operador BETWEEN permite testar se um determinado campo assume o valor dentro de um intervalo especificado. É utilizado por ser mais prático que o teste de >= e <=. Para retornar os usuário que recebem entre 5.000 e 10.000 utilizamos: n q select * from RH.EMPREGADO where SALARIO between 5000 and 10000 Caso queíramos os que não se enquadram neste intervalo, basta acrescentar o operador lógico NOT: n select * from RH.EMPREGADO where not SALARIO between 5000 and 10000 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 50 Restrigindo n EXISTS q q O operador EXISTS é sempre seguido por uma subquery em parênteses. Este operador retorna verdadeiro se o resultado da subquery retorna pelo menos um registro. O exemplo abaixo retorna os empregados que trabalham no departamento de marketing. Pode não ficar claro o entendimento desde operador, porém o abordaremos mais detalhadamente ao falarmos de subqueries. select * from RH.EMPREGADO e where EXISTS ( select 1 from RH.DEPARTAMENTO d where d.ID_DEPARTAMENTO = e.ID_DEPARTAMENTO AND d.NOME_DEPARTAMENTO = 'Marketing' ) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 51 Restrigindo n IS NULL e IS NOT NULL q q Para encontrar valores nulos, você deve utilizar o operador IS NULL. Os operadores ‘=‘ ou ‘!=‘ não funcionam com valores nulos. A literal NULL é a representação para campos sem valores (é bem diferente um campo ser nulo e ser zero). O nulo significa que nenhum valor se aplica. n q Para retornar os usuários que não estão lotados em nenhum departamento utilizamos: n q No momento da criação da tabela é que se define se o campo pode ou não conter valores nulos. select * from RH.EMPREGADO e where ID_DEPARTAMENTO is NULL Caso queíramos os que estejam lotados em um departamento: n select * from RH.EMPREGADO e where ID_DEPARTAMENTO is not NULL Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 52 Restrigindo n LIKE q q O operador LIKE é usado quando se deseja obter colunas de um registro que sigam um determinado padrão pré-especificado. Quando se quer saber o nome de todos os funcionários cujo nome começa com ‘João’ ou termina com ‘Silva’, utiliza-se o operador LIKE. Comparando com MS-DOS: n n q % funciona como o * (indica quaisquer caracteres) _ funciona como o ? (indica que naquela posição pode qualquer caractere) Exemplo: n select * from RH.EMPREGADO where PRIMEIRO_NOME like 'Alex%' q n Seleciona os empregados cujo nome inicia-se com ‘Alex’. select * from RH.EMPREGADO where PRIMEIRO_NOME like '_o%' q Seleciona os empregados cujo nome possua a letra ‘o’ na 2º posição. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 53 Restrigindo n Restringindo pelo valor de uma data q q q No Oracle, ‘07-06-1994’ não é uma data, mas sim, uma string. Portanto, como representar uma data para utilizar os operadores aprendidos? Basta utilizar a função to_date que veremos mais à frente, porém segue um exemplo para já ir experimentando. Selecionar os empregados que foram pagos em 07 de junho de 1994. n q select * from RH.EMPREGADO where DATA_PAGAMENTO = to_date('07/06/1994', 'dd/mm/yyyy') Selecionar os empregados que já foram pagos. n select * from RH.EMPREGADO where DATA_PAGAMENTO < sysdate Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 54 Aula 04 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 55 Esquema RH (tradução do HR) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 56 Tipos de dados no Oracle n No oracle, existem vários tipos de dados. Observe: Categoria Tipos de dados Caractere CHAR, NCHAR, VARCHAR2, NVARCHAR2 Número NUMBER D a d o s b r u t o s LONG, LONG RAW, RAW alfanuméricos e binários Data e hora DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND Objetos largos CLOB, NCLOB, BCLOB, BFILE Identificadores de ROWID, UROWID linha q Nos slides seguintes, veremos, em detalhes, os mais utilizados. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 57 Tipos de dados n CHAR(<size>) q q q É uma string alfanumérica de tamanho fixo, que possui um tamanho máximo em bytes. Seu tamanho deve ser de 1 à 2000 bytes. O valor padrão é 1. Dados armazenados em um tipo char são completados com espaços até completar o tamanho máximo. n q Ex.: Gravar "Olá" em um char(10), ficaria mais ou menos assim: "Olá " O Oracle garante que todo dado dentro deste tipo terá sempre o mesmo tamanho. n n Se for menor, é completado com espaços. Se for maior, um erro é levantado. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 58 Tipos de dados n VARCHAR2(<size>) q q q É uma string alfanumérica de tamanho variável, que possui um tamanho máximo em bytes. Seu tamanho deve ser de 1 à 4000 bytes e não há valor padrão de tamanho. Dados armazenados em um tipo varchar2 somente requerem o espaço necessário para armazenar o dado. n Isto é, um coluna varchar2(4000) vazia gasta o mesmo espaço que uma coluna varchar(1) vazia. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 59 Tipos de dados n NUMBER(<p>,<s>) q q q O tipo NUMBER armazena números com uma precisão de p dígitos tendo uma escala de s dígitos reservados para as casas decimais. A precisão e a escala de dígitos são opcionais, porém se não informadas o Oracle assume o valor máximo possível. Exemplos: n n q NUMBER(6,2) à -9999.99 a 9999.99 NUMBER(2,0) à -99 a 99 Se o número a ser inserido é superior à capacidade, um erro é levantando. Porém, caso a parte decimal informada é superior a suportada pelo tipo, o Oracle realiza o arrendondamento. n Ex.: q q q 9.555 em um NUMBER(3,2) ficará 9.56 99.0123 em um NUMBER(4,2) ficará 99.01 100 em um NUMBER(4,2) gerará erro. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 60 Tipos de dados n DATE q q q É o tipo utilizado para armazenar informação de data e hora. Este tipo pode ser convertido para outras formas de visualização, mas existe várias funções e propriedades especiais que permite sua manipulação e cálculos simples. Um tipo data ocupa 7 bytes. As seguintes informações são armazenadas em cada tipo DATE: n n n n n n n Século Ano Mês Dia Hora Minuto Segundo Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 61 Concatenação n Concatenação q q q Antes de iniciar com funções, vamos falar um pouco sobre o operador de concatenação. Ele é representado por duas barras verticais (||) e é utilizado para concatenar ou juntar duas strings. Exemplos: n select 'Eu ' || 'amo ' || 'Oracle' as "FRASE" from DUAL; n select PRIMEIRO_NOME || ' ' || ULTIMO_NOME as "NOME COMPLETO" from RH.EMPREGADO Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 62 Expressões n Expressões q q q Antes de iniciar com funções, vamos falar um pouco sobre expressões. Uma expressão é uma combinação de um ou mais valores, operadores e funções SQL que resultam em um único valor. Exemplos: n n n n 5+6 à 11 mod(4*2, 3) à 2 upper(‘José ’ || ‘oliveira’) à ‘JOSÉ OLIVEIRA’ sqrt( mod( (4*4)+3, 5) ) à 2 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 63 Expressão CASE n Expressão CASE q q A expressão CASE pode ser usada como uma derivação da lógica if.. then.. else no SQL. Sintaxe: n CASE <expressao> WHEN <valor de comparação> THEN <valor de retorno> ELSE <valor de retorno> END q A cláusula WHEN pode se repetir. A cláusula ELSE é opcional. q No slide seguinte, veremos um exemplo. q Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 64 Expressão CASE n Expressão CASE q Retorna o nome do país, o código da região e o continente. Este último é realizado a partir de uma expressão CASE pelo código da região. select NOME_PAIS , ID_REGIAO, CASE ID_REGIAO WHEN 1 THEN 'Europa' WHEN 2 THEN 'America' WHEN 3 THEN 'Asia' ELSE 'Outro' END as "Continente" from RH.PAIS where upper(NOME_PAIS) like 'B%' Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 65 Funções n O Oracle criou várias funções que podem ser chamadas a partir de instruções SQL. n O Oracle possui cinco classes principais de funções: q q q q q Funções de única linha (serão abordadas nesta aula) Funções de agregação (serão abordadas nas próximas aulas) Funções analíticas Funções de referência à objetos Funções definidas pelo programador Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 66 Funções de única linha (single-row funcions) n Funções de uma única linha q q Trabalha em expressões derivadas de colunas ou literais. São executadas uma vez para cada linha recuperada. n Existem vários tipos: funções textuais, funções numéricas, funções de data, funções de conversão e funções diversas. n Todas as funções de uma única linha pode ser incorporadas no SQL (e, certamente, em PL/SQL). q Elas são utilizadas no SELECT, WHERE e ORDER BY de instruções SELECT. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 67 Funções de única linha de valores nulos n Existem as funções NVL e NVL2 n NVL (x1, x2) q q Possui dois argumentos nos quais ambos são expressões. A função NVL retorna x2 se x1 é nulo. n q Se x1 não é nulo, então x1 é retornado. Ex.: n select PRIMEIRO_NOME || ' ' || ULTIMO_NOME as "NOME COMPLETO", NVL(PERCENTUAL_COMISSAO,0) AS "COMISSAO (%)" from RH.EMPREGADO order by 2 desc Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 68 Funções de única linha (single-row funcions) n Veremos agora várias tabelas com a listagem de todas as funções de única linha utilizadas pelo Oracle. n A idéia é passar uma visão sobre cada uma delas, porém a sintaxe destas funções deverá ser estudada nas bibliografias da disciplina. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 69 Funções textuais de única linha (1/2) Função Descrição ASCII Retorna o código na tabela ASCII equivalente ao caractere informado. CHR Retorna o caractere dado o código da tabela ASCII. CONCAT Concatena duas strings, assim como o operador ||. INITCAP Retorna uma string com a primeira letra de cada palavra em maíuscula. INSTR Encontra a posição numérica inicial de uma string dentro de uma outra string. INSTRB Assim com INSTR, porém conta bytes ao invés de caracteres. LENGTH Retorna o tamanho de uma string em caracteres. LENGTHB Retorna o tamanho de uma string em bytes. LOWER Converte toda a string para minúscula. LPAD Complementa à esquerda uma string até um tamanho especificado usando um caractere específico. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 70 Funções textuais de única linha (2/2) Função Descrição LTRIM Retira espaços à esquerda de uma string. RPAD Complementa à direita uma string até um tamanho especificado usando um caractere específico. RTRIM Retira espaços à direita de uma string. REPLACE Realiza pesquisa e substituição de substring. SUBSTR Retorna uma parte de uma string especificada pelo posição numérica dos caracteres. SUBSTRB Retorna uma parte de uma string especificada pelo posição numérica dos bytes. SOUNDEX Retorna a representação fonética de uma string. TRANSLATE Realiza pesquisa e substituição de caracteres. TRIM Retira espaços à direita e à esquerda de uma string. UPPER Converte toda a string para maiúscula. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 71 Funções numéricas de única linha (1/3) Função Descrição ABS Retorna o valor absoluto. ACOS Retorna o arco-cosseno. ASIN Retorna o arco-seno. ATAN Retorna o arco-tangente. ATAN2 Retorna o arco-tangente. Recebe dois parâmetros. BITAND Retorna o resultado de uma operação AND nos bits de dois parâmetros. CEIL Retorna o próximo inteiro superior. COS Retorna o cosseno. COSH Retorna o cosseno hiperbólico. EXP Retorna a base de um logaritmo natural elevada a uma potência. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 72 Funções numéricas de única linha (2/3) Função Descrição FLOOR Retorna o próximo inteiro inferior. LN Retorna o logaritmo natural. LOG Retorna o logaritmo. MOD Retorna o módulo (resto) de uma operação de divisão. POWER Retorna um número elevado a uma potência. ROUND Arredonda o número. SIGN Retorna o signal: negativo, positivo ou zero. SIN Retorna o seno. SINH Retorna o seno hiperbólico. SQRT Retorna a raiz quadrada de um número Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 73 Funções numéricas de única linha (3/3) Função Descrição TAN Retorna a tangente. TANH Retorna a tangente hiperbólica. TRUNC Retorna o número truncado. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 74 Funções de data de única linha (1/2) Função Descrição ADD_MONTHS Adiciona um número de meses a uma data. CURRENT_DATE Retorna a data corrente. CURRENT_TIMESTAMP Retorna a data e hora corrente. DBTIMEZONE Retorna o fuso horário do banco de dados. EXTRACT Retorna um componente de uma data. FROM_TZ Retorna uma data e hora com o fuso horário dado. LAST_DAY Retorna o último dia do mês. LOCALTIMESTAMP Retorna a data e hora corrente do fuso horário da sessão. MONTHS_BETWEEN Retorna o número de meses entre duas datas. NEW_TIME Retorna a data e hora em um diferente fuso horário. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 75 Funções de data de única linha (2/2) Função Descrição NEXT_DAY Retorna o próximo dia da semana de um dada data. ROUND Arredonda a data/hora. SESSIONTIMEZONE Retorna o fuso horário do sessão corrente. SYS_EXTRACT_UTC Retorna o UTC (GMT) de uma data/hora. SYSDATE Retorna a data corrente. SYSTIMESTAMP Retorna a data e hora corrente. TRUNC Trunca uma data para uma dada granularidade. TZ_OFFSET Retorna a diferença da UTC de uma determinada localização. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 76 Funções de conversão de única linha (1/2) Função Descrição ASCIISTR Converte caracteres para ASCII. BIN_TO_NUM Converte uma string de bits para um número. CAST Converte tipos. CHARTOROWID Converte um caractere para um tipo ROWID. COMPOSE Converte para UNICODE. CONVERT Converte de um conjunto de caracteres para outro. DECOMPOSE Decompõe um string UNICODE. HEXTORAW Converte um hexadecimal para um raw. NUMTODSINTERVAL Converte um número para um intervalo de dias por segundo. NUMTOYMINTERVAL Converte um número para um intervalo de anos por mês. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 77 Funções de conversão de única linha (2/2) Função Descrição RAWTOHEX Converte um raw para um hexadecimal. ROWIDTOCHAR Converte um ROWID para um caractere. TO_CHAR Converte e formata uma data em uma string. TO_DATE Converte uma string para uma data, especificando o formato. TO_DSINTERVAL Converte uma string para um intervalo dias por segundo. TO_MULTIBYTE Converte um caractere de um único byte para seu correspondente multibyte. TO_NUMBER Converte uma string numérica para um número, especificado o formato. TO_SINGLE_BYTE Converte um caractere multibyte para seu correspondente único byte. TO_YMINTERVAL Converte uma string para um intervalo anos por mês. UNISTR Converte UCS2 Unicode. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 78 Funções diversas de única linha (1/2) Função Descrição BFILENAME Retorna o localizador BFILE para o especificado diretório e arquivo. COALESCE Retorna o primeiro não-nulo em uma lista. DECODE Instrução case em uma única linha (uma função if.. então.. else) DUMP Retorna uma substring raw na codificação especificada. EMPTY_BLOB Retorna um localizador BLOB vazio. EMPTY_CLOB Retorna um localizador CLOB vazio. GREATEST Organiza os argumentos e retorna o maior. LEAST Organiza os argumentos e retorna o menor. NULLIF Retorna NULL se duas expressões são iguais. SYS_CONNECT_BY_PATH Retorna valores da raiz até os nodos de uma consulta usando CONNECT BY. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 79 Funções diversas de única linha (2/2) Função Descrição SYS_CONTEXT Retorna vários atributos de sessão, tais como endereço IP, terminal e usuário corrente. UID Retorna o ID numérico do usuário da sessão corrente. USER Retorna o nome do usuário da sessão corrente. USERENV Não mais utilizado. Substituído por SYS_CONTEXT. VSIZE Retorna o tamanho interno em bytes de uma expressão. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 80 Aula 05 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 81 Esquema RH (tradução do HR) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 82 Fundamentos de funções de agregação n Funções de agregação (group functions) q q q Também conhecidas como funções de agrupamento e retornam um valor baseado no número de entradas. O número exato de entradas não está determinado até que a consulta tenha sido executada e todas as linhas tenham sido trazidas. Isto difere das funções de uma única linha, em que o número de entradas já é conhecido antes que a consulta seja executada. Devido a diferença acima, funções de agregação possuem requisitos e comportamentos ligeiramente diferentes que funções de uma única linha. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 83 Fundamentos de funções de agregação n Valores Nulos q Funções de agregação não processam valores nulos e nunca retornam um valor nulo, mesmo quando somente valores nulos são avaliados. n Ex.: q n Uma contagem (COUNT) ou uma soma (SUM) de valores nulos irá retornar 0 (zero). Aplicabilidade q q A maioria das funções de agregação podem ser aplicadas em todos os valores (ALL) ou somente em valores distintos (DISTINCT). Ex.: n Soma de todos os salários q select sum(SALARIO) from RH.EMPREGADO ou q select sum(all SALARIO) from RH.EMPREGADO n Soma de todos os salários diferentes q select sum(distinct SALARIO) from RH.EMPREGADO Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 84 Fundamentos de funções de agregação n Observe a consulta simples abaixo e o seu resultado: q select PRIMEIRO_NOME || ' ' || ULTIMO_NOME as "NOME COMPLETO", SALARIO from RH.EMPREGADO where ID_DEPARTAMENTO = 90 order by 1 NOME COMPLETO SALARIO ---------------------------------------------- ---------Lex De Haan 17000 Neena Kochhar 17000 Steven King 24000 n Observe a consulta utilizando função de agregação abaixo e o seu resultado: q select avg(SALARIO) "MEDIA", avg(ALL SALARIO) "MEDIA", avg(DISTINCT SALARIO) "MEDIA DISTINTA", count(SALARIO) "CONTAGEM", count(DISTINCT SALARIO) "CONTAGEM DISTINTA", sum(SALARIO) "SOMA", sum(DISTINCT SALARIO) "SOMA DISTINTA" from TABELA_ACIMA MEDIA MEDIA MEDIA DISTINTA CONTAGEM CONTAGEM DISTINTA SOMA SOMA DISTINTA ---------- ---------- -------------- ---------- ----------------- ---------- ------------19333.3333 19333.3333 20500 3 2 58000 41000 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 85 Funções de agregação n Usando funções de agregação q q q q Assim como funções de uma única linha, o Oracle oferece uma grande variedade de funções de múltiplas linhas. Estas funções aparecem no SELECT ou nas cláusulas HAVING de instruções SELECT. Quando utilizada em um SELECT, geralmente requer uma cláusula GROUP BY, porém quando a mesma não é especificada, o agrupamente padrão é por todo o resultado. Funções de agregação NÃO PODEM aparecer na cláusula WHERE de uma instrução SELECT. Nos próximos slides veremos as seguintes funções de agregação: n n n n n AVG COUNT MAX MIN SUM Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 86 Funções de agregação n AVG (média aritmética): q Retorna a média dos valores de uma determinada coluna. Sintaxe: n q Ex.: n n avg( [ALL | DISTINCT] <nome-da-coluna>) select avg(SALARIO) from EMPREGADO where ID_DEPARTAMENTO=30 q Seleciona a média salarial dos empregados do departamento 30 SUM (soma de valores): q Retorna a soma dos valores de uma determinada coluna. Sintaxe: n q sum( [ALL | DISTINCT] <nome-da-coluna>) Ex.: n select sum(SALARIO) from EMPREGADO q Seleciona a soma dos salários de todos os empregados Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 87 Funções de agregação n MAX (maior valor): q Retorna o maior valor de uma determinada coluna. Sintaxe: n q Exemplo: n n max( [ALL | DISTINCT] <nome-da-coluna>) select max(SALARIO) from EMPREGADO q Seleciona o maior salário de todos os empregados MIN (menor valor): q Retorna o menor valor de uma determinada coluna. Sintaxe: n q min([ALL | DISTINCT] <nome-da-coluna>) Exemplo: n select min(SALARIO) from EMPREGADO q Seleciona o menor salário de todos os empregados Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 88 Funções de agregação n COUNT (contagem) q Retorna o número de linhas ou de colunas do resultado. Pode vir a ser distinta com a utilização do operador DISTINCT. Sintaxe: n n n q count(*) q Retorna o número de linhas do resultado. count(<nome-da-coluna>) ou count(ALL <nome-da-coluna>) q Retorna o número de colunas do resultado. count(DISTINCT <nome-da-coluna>) q Retorna o número de colunas distintas do resultado. Exemplo: n select count(*) from EMPREGADO q Seleciona o número de empregados. n select count(PERCENTUAL_COMISSAO) from EMPREGADO q Seleciona o número de empregados que possuem percentual de comissão, pois valores nulos não são avaliados. n select count(DISTINCT ULTIMO_NOME) from EMPREGADO q Seleciona o número dos sobrenomes distintos de empregados. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 89 Agrupando dados com GROUP BY n Agrupando os dados utilizando GROUP BY q q q As funções de agregação são úteis, porém seu uso comum é vinculá-las a uma cláusula GROUP BY. A cláusula GROUP BY como o próprio nome diz trabalha nos dados que estão agrupados. Como já visto, quando a mesma não é especificada, o agrupamente padrão é por todo o resultado. Por exemplo, seria interessante buscar o menor e o maior salário por cada departamento, a média salarial por função, o número de países por cada região, entre outros. Para isto, devemos aplicar um função de agregação e agrupá-la por algum campo. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 90 Agrupando dados com GROUP BY n Buscar o maior e o menor salário por cada departamento q select ID_DEPARTAMENTO, MIN(SALARIO), MAX(SALARIO) from RH.EMPREGADO group by ID_DEPARTAMENTO order by 1 nulls first ID_DEPARTAMENTO MIN(SALARIO) MAX(SALARIO) --------------- ------------ -----------7000 7000 10 4400 4400 20 6000 13000 30 2500 11000 40 6500 6500 50 2100 8200 60 4200 9000 70 10000 10000 80 6100 14000 90 17000 24000 100 6900 12000 110 8300 12000 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 91 Agrupando dados com GROUP BY n Buscar a média salarial por função. q select ID_FUNCAO, AVG(SALARIO) from RH.EMPREGADO group by ID_FUNCAO order by 1 nulls first ID_FUNCAO AVG(SALARIO) ---------- -----------AC_ACCOUNT 8300 AC_MGR 12000 AD_ASST 4400 AD_PRES 24000 ... SA_MAN 12200 SA_REP 8350 SH_CLERK 3215 ST_CLERK 2785 ST_MAN 7280 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 92 Agrupando dados com GROUP BY n Buscar o número de países por região q select ID_REGIAO, count(ID_PAIS) from RH.PAIS group by ID_REGIAO order by 1 ID_REGIAO COUNT(ID_PAIS) ---------- -------------1 8 2 5 3 6 4 6 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 93 Limitando com HAVING n Limitando dados agrupados com HAVING q q q Funções de agregação não podem ser usadas na cláusula WHERE, porém em alguns casos você deseja agrupar os dados e aplicar um certo filtro no agrupamento dos dados. Este filtro não pode ser feito na cláusula WHERE, pois ele é um filtro sobre as linhas agrupadas. Por exemplo, exibir a média salarial dos empregados de cada departamento cuja média salarial seja superior a 8.000,00. O filtro não é sobre a tabela de DEPARTAMENTO, mas sim, sobre o agrupamento feito sobre esta tabela. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 94 Limitando com HAVING n Buscar a média salarial dos empregados que não possuem percentual de comissão de cada departamento cuja média salarial seja superior ou igual a 10.000,00. q select ID_DEPARTAMENTO, avg(SALARIO) from RH.EMPREGADO where PERCENTUAL_COMISSAO is null group by ID_DEPARTAMENTO having avg(SALARIO) >= 10000.00 order by avg(SALARIO) ID_DEPARTAMENTO AVG(SALARIO) --------------- -----------70 10000 110 10150 90 19333.3333 q Observe como a ordenação pode ser feita pelo resultado da função de agregação. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 95 Aula 06 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 96 Esquema RH (tradução do HR) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 97 Consultas em múltiplas tabelas n Um banco de dados possui várias tabelas que armazenam dados. Nós já aprendemos a escrever consultas simples que selecionam informações de uma única tabela. n A capacidade de junção de duas ou mais tabelas e o acesso às informações é a principal força dos banco de dados relacionais. n Usando a instrução SELECT podemos escrever consultas avançadas que satisfazem os requisitos dos usuários. n O foco deste material é vermos como escrever consultas avançadas que satisfazem os requisitos dos usuários. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 98 Consultas em múltiplas tabelas n A instrução SELECT possui as seguintes cláusulas obrigatórias: q SELECT n q possui uma lista de colunas, expressões, funções e afins. FROM n diz em qual(is) tabela(s) irá buscar as informações desejadas. q Até então, aprendemos a buscar informações de uma única tabela, porém isto agora não mais será uma limitação. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 99 Consultas em múltiplas tabelas n Para consultar dados de mais de uma tabela, você precisa identificar colunas comuns entre as duas tabelas. q n Na cláusula WHERE, você define a relação entre as tabelas listadas na cláusula FROM usando operadores de comparação. q n Um exemplo seria a coluna ID_DEPARTAMENTO na tabela de EMPREGADO que corresponde a coluna de mesmo nome (ID_DEPARTAMENTO) na tabela de DEPARTAMENTO. Normalmente o operador de igualdade (=) é o utilizado. Veremos que ao invés de utilizarmos o WHERE, podemos indicar a correspondência entre duas tabelas utilizando a cláusula JOIN. q A consulta entre múltiplas tabelas sem correspondência ou colunas em comum é conhecida como produto cartesiano e será abordada à frente. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 100 Junções Simples n Uma junção simples é conhecida como inner join, pois ela retorna somente as linhas que satisfazem as condições de junção. q Um exemplo seria a busca do código e nome dos departamentos e suas respectivas cidades. Esta informação não consta em uma única tabela, bastando uma junção simples. n q select RH.DEPARTAMENTO.ID_DEPARTAMENTO, RH.DEPARTAMENTO.NOME_DEPARTAMENTO, RH.LOCALIZACAO.CIDADE from RH.DEPARTAMENTO, RH.LOCALIZACAO where RH.DEPARTAMENTO.ID_LOCALIZACAO = RH.LOCALIZACAO.ID_LOCALIZACAO O resultado é algo como abaixo: ID_DEPARTAMENTO --------------10 20 ... 270 NOME_DEPARTAMENTO -----------------------------Administration Marketing ... Payroll Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle CIDADE -----------------------------Seattle Toronto ... Seattle Outubro, 2008 101 Junções Complexa n Uma junção complexa é uma junção simples que contém na cláusula WHERE, além da condição de junção, uma outra condição para filtrar as linhas retornadas. q Um exemplo seria a busca do código e nome dos departamentos e suas respectivas cidades fora dos Estados Unidos. n q select RH.DEPARTAMENTO.ID_DEPARTAMENTO, RH.DEPARTAMENTO.NOME_DEPARTAMENTO, RH.LOCALIZACAO.CIDADE from RH.DEPARTAMENTO, RH.LOCALIZACAO where RH.DEPARTAMENTO.ID_LOCALIZACAO = RH.LOCALIZACAO.ID_LOCALIZACAO and RH.LOCALIZACAO.ID_PAIS != 'US' O resultado é algo como abaixo: ID_DEPARTAMENTO --------------20 ... 80 NOME_DEPARTAMENTO -----------------------------Marketing ... Sales Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle CIDADE -----------------------------Toronto ... Oxford Outubro, 2008 102 Criando aliases para tabelas n Antes de iniciar nosso estudo sobre consultas em múltiplas tabelas, é indispensável o conhecimento sobre “apelidar” tabelas. n Assim como em colunas, tabelas também pode ter “apelidos”. n Observe a consulta do slide anterior reescrita usando “apelidos”, como o seguinte: n n select d.ID_DEPARTAMENTO, d.NOME_DEPARTAMENTO, l.CIDADE from RH.DEPARTAMENTO d, RH.LOCALIZACAO l where d.ID_LOCALIZACAO = l.ID_LOCALIZACAO and l.ID_PAIS != 'US' Apelidos de tabela aumentam a legibilidade da consulta. Eles são muito utilizados para reduzir grandes nomes de tabelas em curtos apelidos. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 103 Sintaxe ANSI n A diferença entre a sintaxe de junção tradicional do Oracle e a sintaxe ANSI/ISO SQL1999 é que na ANSI, o tipo de junção é especificado explicitamente na cláusula FROM. n Usar a sintaxe ANSI é mais clara e é mais recomendada do que a sintaxe de junção tradicional do Oracle. As junções simples podem ter as seguintes formas: q NATURAL JOIN n q JOIN ... USING n q <nome-da-tabela> NATURAL [INNER] JOIN <nome-da-tabela> <nome-da-tabela> [INNER] JOIN <nome-da-tabela> USING (<colunas>) JOIN ... ON n <nome-da-tabela> [INNER] JOIN <nome-da-tabela> ON <condição> Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 104 NATURAL JOIN n No natural join, a junção é realizada por todas as colunas que possuem o mesmo nome em ambas as tabelas. n Neste tipo de junção não se deve qualificar o nome das colunas com o nome da tabela ou com o “apelido” da tabela. n Sintaxe: q n <nome-da-tabela> NATURAL [INNER] JOIN <nome-da-tabela> Vamos refazer o exemplo da busca do código e nome dos departamentos e suas respectivas cidades. q select ID_DEPARTAMENTO, NOME_DEPARTAMENTO, CIDADE from RH.DEPARTAMENTO natural join RH.LOCALIZACAO Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 105 JOIN ... USING n Neste tipo de junção, a junção é realizada pelas colunas indicadas e que possuem o mesmo nome em ambas as tabelas. n A cláusula USING especifica os nomes das colunas que devem ser utilizadas para realizar a junção das tabelas. n Também neste tipo de junção não se deve qualificar o nome das colunas com o nome da tabela ou com o “apelido” da tabela. n Sintaxe: q <nome-da-tabela> [INNER] JOIN <nome-da-tabela> USING (<colunas>) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 106 JOIN ... USING n Vamos refazer o exemplo da busca do código e nome dos departamentos e suas respectivas cidades. q select ID_DEPARTAMENTO, NOME_DEPARTAMENTO, CIDADE from RH.DEPARTAMENTO join RH.LOCALIZACAO using (ID_LOCALIZACAO) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 107 JOIN ... ON n Quando não se tem nomes de colunas comuns entre tabelas para fazer uma junção ou deseja-se especificar condições de junções arbitrárias, este é o tipo de junção a ser utilizado. n Neste tipo de junção pode-se e deve-se qualificar o nome das colunas com o nome da tabela ou com o “apelido” da tabela. n Sintaxe: q <nome-da-tabela> [INNER] JOIN <nome-da-tabela> ON <condição> Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 108 JOIN ... ON n Vamos refazer o exemplo da busca do código e nome dos departamentos e suas respectivas cidades. q select d.ID_DEPARTAMENTO, d.NOME_DEPARTAMENTO, l.CIDADE from RH.DEPARTAMENTO d join RH.LOCALIZACAO l on (d.ID_LOCALIZACAO = l.ID_LOCALIZACAO) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 109 Junções em múltiplas tabelas n Uma junção em múltiplas tabelas é uma junção em mais de duas tabelas. n Na sintaxe ANSI, as junções são realizadas da esquerda para a direita. A primeira condição de junção pode referenciar colunas somente da primeira e segunda tabelas; a segunda condição de junção da primeira, segunda e terceira e assim por diante. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 110 Junções em múltiplas tabelas n Vamos fazer o exemplo da busca do primeiro nome do empregado, nome do seu departamento, nome da cidade do seu departamento. q n select e.PRIMEIRO_NOME, d.NOME_DEPARTAMENTO, l.CIDADE from RH.EMPREGADO e JOIN RH.DEPARTAMENTO d on (e.ID_DEPARTAMENTO = d.ID_DEPARTAMENTO) JOIN RH.LOCALIZACAO l on (d.ID_LOCALIZACAO = l.ID_LOCALIZACAO) Perguntas: q q Será que poderia utilizar NATURAL JOIN ou JOIN ... USING ao invés de JOIN ... ON? Outra pergunta: Será que poderia misturar NATURAL JOIN, JOIN ... USING, JOIN ... ON e, até mesmo, junção tradicional em uma mesma consulta? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 111 Junções cartesianas n Uma junção cartesiana ocorre quando os dados são selecionados de duas ou mais tabelas e não há uma condição de junção especificada. n Assim, é feita a junção de cada linha da primeira tabela com todas as linhas da segunda tabela. q n Se a primeira tabela tem 5 linhas e a segunda tabela tem 10 linhas, será gerado um resultado de 50 linhas. Se for adicionada uma nova tabela de 10 linhas sem ainda especificar uma condição de junção, o resultado terá 500 linhas. Para se evitar um produto cartesiano, deve existir pelo menos n-1 condições de junções para a junção de n tabelas. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 112 Junções cartesianas n Um exemplo seria buscar o nome da região e o nome do país daqueles países que começam com ‘I’. q q select r.NOME_REGIAO, p.NOME_PAIS from RH.REGIAO r, RH.PAIS p where p.NOME_PAIS like 'I%' ou select r.NOME_REGIAO, p.NOME_PAIS from RH.REGIAO r cross join RH.PAIS p where p.NOME_PAIS like 'I%' Observe o resultado no próximo slide. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 113 Junções cartesianas NOME_REGIAO ------------------------Europe Americas Asia Middle East and Africa Europe Americas Asia Middle East and Africa Europe Americas Asia Middle East and Africa q Observe que não foi citada nenhuma condição de junção e, portanto, cada uma das regiões será relacionada a todos os países. n q NOME_PAIS ---------------------------------------Israel Israel Israel Israel India India India India Italy Italy Italy Italy Isto faz sentido? Por que? Existe um exemplo em que o produto cartesiano deva ser usado? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 114 OUTER JOINS n Até agora, nos vimos inner joins, que retornam somente as linhas correspondentes entre ambas as tabelas, e produtos cartesianos, que retornam uma combinação de todas as linhas de ambas as tabelas. n Em alguns casos, deseja-se ver os dados de uma tabela, mesmo que não haja uma linha correspondente na tabela de junção. n Para o problema acima, existe a junção do tipo outer join. Ele retorna valores baseados nas condições de inner join, como trás também linhas não relacionadas de um ou dos dois lados das tabelas. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 115 OUTER JOINS n Na sintaxe tradicional do Oracle, o símbolo + envolvido por parênteses, denota um outer join na consulta. n Digite (+) próximo a coluna da tabela onde pode não haver linhas correspondentes. n Um exemplo é buscar a lista de nomes de todos os países e cidades em que possuem departamentos localizados. Caso o país não tenha nenhum departamento localizado, deseja-se exibí-lo mesmo assim. q select p.NOME_PAIS, l.CIDADE from RH.PAIS p, RH.LOCALIZACAO l where p.ID_PAIS = l.ID_PAIS (+) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 116 OUTER JOINS n O exemplo da página anterior pode ser reescrito das seguintes formas: q q q select p.NOME_PAIS, l.CIDADE from RH.PAIS p left outer join RH.LOCALIZACAO l on p.ID_PAIS = l.ID_PAIS select p.NOME_PAIS, l.CIDADE from RH.PAIS p left outer join RH.LOCALIZACAO l using (ID_PAIS) select p.NOME_PAIS, l.CIDADE from RH.PAIS p natural left outer join RH.LOCALIZACAO l Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 117 OUTER JOINS n Um exemplo inverso é buscar a lista de todas as cidades que possuem departamentos e os seus países. Caso a cidade não esteja vinculada a um país, deseja-se exibí-la mesmo assim. q select p.NOME_PAIS, l.CIDADE from RH.PAIS p, RH.LOCALIZACAO l where p.ID_PAIS (+) = l.ID_PAIS Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 118 OUTER JOINS n O exemplo da página anterior pode ser reescrito das seguintes formas: q q q select p.NOME_PAIS, l.CIDADE from RH.PAIS p right outer join RH.LOCALIZACAO l on p.ID_PAIS = l.ID_PAIS select p.NOME_PAIS, l.CIDADE from RH.PAIS p right outer join RH.LOCALIZACAO l using (ID_PAIS) select p.NOME_PAIS, l.CIDADE from RH.PAIS p natural right outer join RH.LOCALIZACAO l Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 119 OUTER JOINS n Um exemplo que envolva os dois lados, isto é, inverso é buscar a lista de todas as cidades que possuem departamentos e os todos os países. Caso a cidade não esteja vinculada a um país ou caso o país não tenha nenhum departamento localizado, deseja-se exibí-lo mesmo assim. q select p.NOME_PAIS, l.CIDADE from RH.PAIS p, RH.LOCALIZACAO l where p.ID_PAIS (+) = l.ID_PAIS (+) n q ERRO. Não funciona. Deve-se utilizar o full outer join. select p.NOME_PAIS, l.CIDADE from RH.PAIS p full outer join RH.LOCALIZACAO l using (ID_PAIS) n Se preferir, pode-se utilizar o NATURAL JOIN ou JOIN ... ON sem problemas. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 120 SELF JOINS n Um self-join é uma junção na própria tabela. Certamente um resultado de um auto-relacionamento. n Um exemplo claro é o ID_GERENTE na tabela EMPREGADO que referencia o ID_EMPREGADO na própria tabela. Portanto, como fazer a junção, isto é, buscar o nome completo dos gerentes e de seus respectivos subordinados. q n select e.PRIMEIRO_NOME || ' ' || e.ULTIMO_NOME as "NOME EMPREGADO", g.PRIMEIRO_NOME || ' ' || g.ULTIMO_NOME as "NOME GERENTE" from RH.EMPREGADO e INNER JOIN RH.EMPREGADO g ON e.ID_GERENTE = g.ID_EMPREGADO order by 1, 2 Em um self-join pode ser utilizado NATURAL JOIN? E JOIN ... USING? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 121 Aula 06 - Complementar Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 122 Esquema HOMEM x MULHER Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 123 Esquema HOMEM x MULHER n Tabela HOMEM ID_HOMEM ---------10 20 30 n NOME_HOMEM ID_MULHER ------------------------------ ---------Anderson Jander 1 Rogério 2 Tabela MULHER ID_MULHER ---------1 2 3 NOME_MULHER -----------------------------Edna Stefanny Cássia Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 124 INNER JOINS n Selecionar os casamentos: q n select h.NOME_HOMEM, m.NOME_MULHER from HOMEM h, MULHER m where h.ID_MULHER = m.ID_MULHER Resultado: NOME_HOMEM -----------------------------Jander Rogério n NOME_MULHER -----------------------------Edna Stefanny Também pode ser feito com NATURAL JOIN, JOIN ... USING e JOIN ... ON conforme poderá ser visto no próximo slide. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 125 INNER JOINS n NATURAL JOIN q n JOIN ... USING q n select h.NOME_HOMEM, m.NOME_MULHER from HOMEM h natural join MULHER m select h.NOME_HOMEM, m.NOME_MULHER from HOMEM h join MULHER m USING (ID_MULHER) JOIN ... ON q select h.NOME_HOMEM, m.NOME_MULHER from HOMEM h join MULHER m ON (h.ID_MULHER = m.ID_MULHER) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 126 Produto Cartesiano n Simular todos os casamentos possíveis: q q select h.NOME_HOMEM, m.NOME_MULHER from HOMEM h, MULHER m ou select h.NOME_HOMEM, m.NOME_MULHER from HOMEM h cross join MULHER m Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 127 Produto Cartesiano n Resultado: NOME_HOMEM -----------------------------Anderson Anderson Anderson Jander Jander Jander Rogério Rogério Rogério Ricardo Terra (rterrabh [at] gmail.com) NOME_MULHER -----------------------------Edna Stefanny Cássia Edna Stefanny Cássia Edna Stefanny Cássia Apostila Oracle Outubro, 2008 128 OUTER JOINS n Selecionar os casamentos, caso não haja homens casados também é desejável exibí-los: q n select h.NOME_HOMEM, m.NOME_MULHER from HOMEM h, MULHER m where h.ID_MULHER = m.ID_MULHER (+) Resultado: NOME_HOMEM -----------------------------Anderson Jander Rogério Ricardo Terra (rterrabh [at] gmail.com) NOME_MULHER -----------------------------Edna Stefanny Apostila Oracle Outubro, 2008 129 OUTER JOINS n Selecionar os casamentos, caso não haja mulheres casadas também é desejável exibí-las: q n select h.NOME_HOMEM, m.NOME_MULHER from HOMEM h, MULHER m where h.ID_MULHER (+) = m.ID_MULHER Resultado: NOME_HOMEM -----------------------------Jander Rogério Ricardo Terra (rterrabh [at] gmail.com) NOME_MULHER -----------------------------Edna Stefanny Cássia Apostila Oracle Outubro, 2008 130 OUTER JOINS n Além da símbolo (+), um outer join pode ser feito por NATURAL OUTER JOIN, OUTER JOIN ... USING e OUTER JOIN ... ON. n Casamentos e todos os homens mesmo que não estejam casados. q n Casamentos e todas as mulheres mesmo que não estejam casadas. q n select h.NOME_HOMEM, m.NOME_MULHER from HOMEM h natural left outer join MULHER m select h.NOME_HOMEM, m.NOME_MULHER from HOMEM h natural right outer join MULHER m Como exercício refaça as consultas acima usando OUTER JOIN ... USING e OUTER JOIN ... ON. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 131 OUTER JOINS n E se deseja-se selecionar todos casamentos e caso não hajam homens ou mulheres casados também é desejável exibí-los: q n select h.NOME_HOMEM, m.NOME_MULHER from HOMEM h natural full outer join MULHER m Resultado: NOME_HOMEM -----------------------------Anderson Jander Rogério n NOME_MULHER -----------------------------Edna Stefanny Cássia Como exercício refaça a consulta acima usando OUTER JOIN ... USING e OUTER JOIN ... ON. Observe que não é possível com (+). Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 132 Aula 07 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 133 Esquema RH (tradução do HR) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 134 Operadores de Conjunto n Operadores de conjuntos (set operators) podem ser usados para selecionar dados de tabelas múltiplas. n Eles simplesmente combinam resultados de duas consultas em uma única consulta. Estas consultas são conhecidas como consultas compostas (compound queries). Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 135 Operadores de Conjunto n São os operadores de conjunto: Operador Descrição UNION Retorna todas as linhas não repetidas selecionadas por cada consulta. UNION ALL Retorna todas as linhas, inclusive duplicadas, selecionadas por cada consulta. INTERSECT Retorna linhas selecionadas por ambas as consultas. MINUS Retorna linha únicas selecionadas pela primeira consulta, porém que não foram selecionadas na segunda consulta. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 136 Operadores de Conjunto n Para entender melhor vamos tomar como base as seguintes consultas: q Selecionar o último nome dos empregados que trabalham no departamento 90. n q q n select e.ULTIMO_NOME from RH.EMPREGADO e where e.ID_DEPARTAMENTO = 90 Resultado: q ULTIMO_NOME ------------------------King Kochhar De Haan Ricardo Terra (rterrabh [at] gmail.com) Selecionar o último nome dos empregados que começam com ‘K’. select e.ULTIMO_NOME from RH.EMPREGADO e where e.ULTIMO_NOME like 'K%' Resultado: ULTIMO_NOME ------------------------King Kochhar Khoo Kaufling King Kumar Apostila Oracle Outubro, 2008 137 UNION n Une todas as linhas não repetidas de cada consulta. q select e.ULTIMO_NOME from RH.EMPREGADO e where e.ID_DEPARTAMENTO = 90 UNION select e.ULTIMO_NOME from RH.EMPREGADO e where e.ULTIMO_NOME like 'K%‘ q Resultado: ULTIMO_NOME ------------------------De Haan Kaufling Khoo King Kochhar Kumar Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 138 UNION ALL n Une todas as linhas, inclusive duplicadas, de cada consulta. q select e.ULTIMO_NOME from RH.EMPREGADO e where e.ID_DEPARTAMENTO = 90 UNION ALL select e.ULTIMO_NOME from RH.EMPREGADO e where e.ULTIMO_NOME like 'K%‘ q Resultado: ULTIMO_NOME ------------------------King Kochhar De Haan King Kochhar Khoo Kaufling King Kumar Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 139 INTERSECT n Faz a interseção entre as linhas de cada consulta, isto é, retorna linhas comuns de ambas as consultas. q select e.ULTIMO_NOME from RH.EMPREGADO e where e.ID_DEPARTAMENTO = 90 INTERSECT select e.ULTIMO_NOME from RH.EMPREGADO e where e.ULTIMO_NOME like 'K%' q Resultado: ULTIMO_NOME ------------------------King Kochhar Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 140 MINUS n Realiza a subtração entre as linhas da primeira consulta com as linhas da segunda consulta, isto é, retorna as linhas da primeira consulta que não aparecem na segunda consulta. q select e.ULTIMO_NOME from RH.EMPREGADO e where e.ID_DEPARTAMENTO = 90 MINUS select e.ULTIMO_NOME from RH.EMPREGADO e where e.ULTIMO_NOME like 'K%' q Resultado: ULTIMO_NOME ------------------------De Haan Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 141 SUBQUERIES n Uma subquery é um consulta dentro de uma consulta. n Quando você tem várias subqueries, a subquery mais interna é avaliada primeiramente. n Podem ser usadas com todas as instruções DML. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 142 SUBQUERIES n Existem vários tipos de subqueries tais como: q nested subquery n n q correlated subquery n n q Tradução: subquery correlacionada São subqueries que utilizam colunas da consulta "pai". Para cada linha processada da consulta "pai", a correlated subquery é avaliada novamente. scalar subquery n n n Tradução: subquery aninhada São subqueries utilizadas na cláusula WHERE. Tradução: subquery escalar São subqueries que podem ser usadas em qualquer lugar onde um nome de uma coluna ou um expressão podem ser utilizadas. Vamos ver alguns exemplos de subqueries. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 143 SUBQUERIES n Buscar o primeiro e o último nome e o salário do empregado com o maior salário. q q select e.PRIMEIRO_NOME, e.ULTIMO_NOME, e.SALARIO from RH.EMPREGADO e where e.SALARIO = (select max(SALARIO) from RH.EMPREGADO) Resultado: PRIMEIRO_NOME ULTIMO_NOME SALARIO -------------------- ------------------------- ---------Steven King 24000 q Como fazer isto sem utilizar subquery? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 144 SUBQUERIES n Buscar o primeiro e o último nome dos empregados e o código do departamento cujo nome do departamento comece com a letra 'A'. q q select e.PRIMEIRO_NOME, e.ULTIMO_NOME, e.ID_DEPARTAMENTO from RH.EMPREGADO e where e.ID_DEPARTAMENTO IN (select ID_DEPARTAMENTO from RH.DEPARTAMENTO where NOME_DEPARTAMENTO like 'A%') Resultado: PRIMEIRO_NOME -------------------Jennifer Shelley William q ULTIMO_NOME ID_DEP ------------------------- ---------Whalen 10 Higgins 110 Gietz 110 Como fazer isto sem utilizar subquery? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 145 SUBQUERIES n Buscar o código do departamento, o primeiro e o último nome e o salário do empregado com o maior salário do seu departamento. q q select e.ID_DEPARTAMENTO, e.PRIMEIRO_NOME, e.ULTIMO_NOME, e.SALARIO from RH.EMPREGADO e where e.SALARIO = (select max(ei.SALARIO) from RH.EMPREGADO ei where ei.ID_DEPARTAMENTO = e.ID_DEPARTAMENTO) Resultado no próximo slide. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 146 SUBQUERIES q Resultado: ID_DEP ---------90 60 100 30 50 80 10 20 40 70 110 q PRIMEIRO_NOME -------------------Steven Alexander Nancy Den Adam John Jennifer Michael Susan Hermann Shelley ULTIMO_NOME SALARIO ------------------------- ---------King 24000 Hunold 9000 Greenberg 12000 Raphaely 11000 Fripp 8200 Russell 14000 Whalen 4400 Hartstein 13000 Mavris 6500 Baer 10000 Higgins 12000 Como fazer isto sem utilizar subquery? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 147 Aula 08 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 148 Esquema Acadêmico Simples Modelo ER Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 149 Esquema Acadêmico Simples Modelo Relacional (abstraindo domínios) PROFESSOR (cpf, nome, salario, titulacao) DISCIPLINA (codigo, nome, carga_horaria, cpf_professor) DISCIPLINA [cpf_professor] ß PROFESSOR [cpf] ALUNO (matricula, nome, data_nascimento) ALUNO_DISCIPLINA (matricula_aluno, codigo_disciplina, pontuacao) ALUNO_DISCIPLINA [matricula_aluno] ß ALUNO [matricula] ALUNO_DISCIPLINA [codigo_disciplina] ßDISCIPLINA [codigo] Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 150 Instruções DML n DML (Data Manipulation Language) é um subconjunto do SQL que é empregado para manipular dados. q Instrução Propósito INSERT Adicionar linhas à uma tabela. UPDATE Modificar os valores armazenados em uma tabela. MERGE Atualizar ou inserir linhas de uma tabela em outra. DELETE Remover linhas de uma tabela. SELECT FOR UPDATE Bloqueia outras sessões de realizar DML nas linhas selecionadas. LOCK TABLE Bloqueia outras sessões de realizar DML em uma tabela. Nos slides seguintes, abordaremos cada instrução acima. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 151 Insert n A instrução INSERT é usada para adicionar linha em uma ou mais tabelas. q n As linhas podem ser adicionada com valores especificados ou podem ser criadas por dados existentes utilizando uma subquery. Sintaxes: q insert into <TABELA> (<lista-de-colunas>) values (<lista-de-valores>) q insert into <TABELA> values (<lista-de-valores>) n q A lista de colunas é opcional, porém só funciona se na lista-de-valores forem inseridas todas as colunas da tabela na ordem de sua criação. insert into <TABELA> [<lista-de-colunas>] SELECT... n n Também pode ser inserido em uma tabela o resultado de uma consulta. Muito utilizada em migração de dados. Lista de colunas é opcional. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 152 Insert n Exemplos: q q q insert into PROFESSOR (CPF,NOME,SALARIO,TITULACAO) values (11111111111, 'RICARDO TERRA', 1.99, 'ESPECIALISTA'); insert into PROFESSOR values (11111111111, 'RICARDO TERRA', 1.99, 'ESPECIALISTA'); insert into PROFESSOR (CPF,NOME,SALARIO,TITULACAO) select cpf_cliente, nome_cliente, 1000.00, 'GRADUADO' from CLIENTE q insert into PROFESSOR select cpf_cliente, nome_cliente, 1000.00, 'GRADUADO' from CLIENTE Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 153 Update n A instrução UPDATE é usada para modificar linhas existentes em uma tabela. n Sintaxe: q n update <TABELA> set <coluna1> = <valor1>, <coluna2> = <valor2>, ... [where <condições>] Exemplos: q q update PROFESSOR set SALARIO = 1.1*SALARIO update PROFESSOR set SALARIO = 1.1*SALARIO where upper(NOME) like ' %TERRA%' Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 154 Delete n A instrução DELETE é usada para remover linhas de uma tabela. n Sintaxe: q n delete [from] <TABELA> [where <condições>] Exemplos: q delete from PROFESSOR n q exclui todos os professores delete from PROFESSOR where SALARIO > 1.99 n exclui os professores cujo salário seja superior a 1.99 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 155 Truncate n Quando se deseja apagar todas as linhas de uma tabela, deve ser considerado o uso da instrução TRUNCATE. n Assim como na instrução DELETE sem a cláusula WHERE, TRUNCATE irá remover todas as linhas e uma tabela. n Entretanto, TRUNCATE não é uma DML, mas sim, uma DDL e, por isto, possui características diferentes de uma instrução DELETE que é uma instrução DML. n Um usuário só poderá realizar o TRUNCATE de uma tabela de um outro esquema, se tiver permissão de DROP TABLE naquela tabela. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 156 Truncate n Para sua execução não poderá haver FKs ativas. n Sintaxe: q n truncate table ALUNO; Exemplo: q alter table ALUNO_DISCIPLINA disable constraint fk_aluno; truncate table ALUNO; n Depois não esqueça de reativar as FKs: q alter table ALUNO_DISCIPLINA enable constraint fk_aluno; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 157 Truncate n Algumas diferenças do TRUNCATE para o DELETE: q q q O TRUNCATE é mais rápido tanto em largas ou pequenas tabelas. O DELETE gerará informação de recuperação (como um desfazer), ao contrário do TRUNCATE que não gera este tipo de informação. Não ativa triggers. Com o TRUNCATE, o armazenamento da tabela e todos os índices são zerados. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 158 Truncate n Mesmo sendo uma instrução DDL, realizar o TRUNCATE de uma tabela é diferente de removê-la (DROP TABLE) e criá-la (CREATE TABLE) novamente. n O TRUNCATE não faz: q q q Invalidar objetos dependentes. Exclui índices, triggers ou restrições de integridade referencial. Requer que privilégios sejam garantidos novamente. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 159 Transação n Uma transação representa uma unidade atômica de trabalho. q n Todas as operações são aplicadas ou nenhuma delas. Uma transferência monetária é um exemplo tradicional de transação. Uma conta deve ser debitada e a outra creditada. Caso uma das operações apresente problema, nada é feito, senão todas as operações são efetuadas. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 160 Transação n Por padrão, uma transação é aberta logo que a primeira instrução seja efetuada. Ao criar um script é altamente recomendável inserir todas as operações em um bloco BEGIN e END. q O SQL*Plus possui a configuração AUTOCOMMIT ativada, para desativá-la, basta: n n SET AUTOCOMMIT OFF Um bloco inicia com BEGIN e o termina com END. q q A instrução COMMIT confirma todas as alterações. A instrução ROLLBACK desfaz todas as alterações. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 161 Transação n Exemplo: BEGIN update CONTA set SALDO - 50 where NUMERO = 100; update CONTA set SALDO + 50 where NUMERO = 200; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; q Caso as atualizações não gerem problemas, todas as operações serão confirmadas pelo COMMIT. Porém, caso haja qualquer problema, nenhuma alteração será realizada. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 162 Selecionando linhas FOR UPDATE n A instrução SELECT FOR UPDATE é utilizada para bloquear linhas específicas, impedindo que outras sessões alterem ou excluam estas linhas. n Quando uma linha está bloqueada, outras sessões somente podem selecionar estas linhas, mas não podem alterar nem tampouco bloquear estas linhas. n A sintaxe é similar a de um SELECT, porém com a inserção da expressão FOR UPDATE no final. n O bloqueio permanece, independente de qualquer coisa, até que a transação encerre com um COMMIT ou com um ROLLBACK. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 163 Selecionando linhas FOR UPDATE n Exemplo: declare l_professor PROFESSOR%rowtype; BEGIN select * into linha_professor from PROFESSOR where TITULACAO = 'ESPECIALISTA' FOR UPDATE; --Várias outras operações COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; q Nenhuma modificação ou exclusão nos professores especialistas pode ser feita por outra sessão até que haja um COMMIT ou ROLLBACK. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 164 Bloqueando uma tabela n A instrução LOCK é usada para bloquear toda uma tabela, impedindo outras sessões de executar a maioria ou todas as DML na tabela bloqueada. n Sintaxe: q LOCK TABLE professor IN EXCLUSIVE MODE n n n O EXCLUSIVE MODE impede qualquer outra sessão de adquirir qualquer compartilhamento ou bloqueio nesta tabela. Além do EXCLUSIVE MODE, existem diversos modos de bloquear uma tabela. Deadlocks q Ocorre quando duas transações aguardam um desbloqueio e cada uma delas está esperando o desbloqueio da outra. Oracle detecta? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 165 Aula 09 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 166 Esquema Acadêmico Simples Modelo ER Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 167 Esquema Acadêmico Simples Modelo Relacional PROFESSOR (cpf, nome, salario, titulacao) dom(cpf) = numérico(11) NN dom(nome) = alfabético(60) NN dom(salario) = numérico(9,2) NN dom(titulacao) = alfabético(40) NN ALUNO (matricula, nome, data_nascimento) dom(matricula) = numérico(6) NN dom(nome) = alfabético(60) NN dom(data_nascimento) = data NN Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 168 Esquema Acadêmico Simples Modelo Relacional DISCIPLINA (codigo, nome, carga_horaria, cpf_professor) dom(codigo) = numérico(3) NN dom(nome) = alfanumérico(40) NN dom(carga_horaria) = numérico(3) NN dom(cpf_professor) = numérico(11) NN DISCIPLINA [cpf_professor] ß PROFESSOR [cpf] ALUNO_DISCIPLINA (matricula_aluno, codigo_disciplina, pontuacao) dom(matricula_aluno) = numérico(6) NN dom(codigo_disciplina) = numérico(3) NN dom(pontuacao) = numérico(3) ALUNO_DISCIPLINA [matricula_aluno] ß ALUNO [matricula] ALUNO_DISCIPLINA [codigo_disciplina] ßDISCIPLINA [codigo] Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 169 Tipos de dados n Os tipos de dados existentes na linguagem SQL variam de acordo com a versão e o fabricante. O conjunto de tipos de dados evoluem junto com a evolução da informática. A primeira versão, surgida por volta de 1970, não possuia tipos de dados para armazenamento de informações multimídia como som, imagem, vídeo; tão comuns nos dias de hoje. A maioria dos SGBDs incorporaram esses novos tipos de dados às suas versões da linguagem SQL. n Os principais tipos de dados do Oracle que iremos utilizar estão citados no próximo slide. q A explicação detalhada destes tipos pode ser encontrada na Aula 04. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 170 Tipos de dados n CHAR(<size>) q n VARCHAR2(<size>) q n É uma string alfanumérica de tamanho variável, que possui um tamanho máximo em bytes (4000). NUMBER(<p>,<s>) q n É uma string alfanumérica de tamanho fixo, que possui um tamanho máximo em bytes (2000). O tipo NUMBER armazena números com uma precisão de p dígitos tendo uma escala de s dígitos reservados para as casas decimais. DATE q É o tipo utilizado para armazenar informação de data e hora. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 171 Criando tabelas n Você pode pensar em uma tabela como uma planilha com linhas e colunas. Esta é a estrutura que armazena dados em uma base de dados relacional. n A tabela é criada com um nome para identificá-la e com colunas bem definidas com nomes e propriedades válidas, tais como tipo de dado e tamanho. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 172 Criando tabelas n A instrução CREATE TABLE é uma instrução com várias opções. q Exemplo da sintaxe mais simples de criação de tabela: create table ALUNO ( MATRICULA NOME DATA_NASCIMENTO ) number(6), varchar2(60), date n Você deve especificar o nome da tabela após o create table. Como não informou o usuário da tabela, o Oracle entende como sendo o seu. n As definições de colunas estão delimitadas por um parênteses. A tabela criada acima possui três colunas, cada qual com seu nome, tipo de dado e tamanho. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 173 Criando tabelas n Algumas recomendações e regras para nomenclatura de nomes de tabelas e colunas: q Tente fazer nomes de tabelas e colunas o mais descritivo possível. q Nomes de tabelas e colunas são identificadores e podem: n n n q ter no máximo 30 caracteres; deve começar com letra e pode conter números; também aceitam os caracteres $ (cifrão), _ (traço inferior) e # (sustenido). Os nomes não são sensíveis a caixa, isto é, a tabela ALUNO pode ser referenciada como Aluno ou aluno ou AlUnO. n n O Oracle converte tudo para maiúscula e grava no dicionário de dados. Porém, se você cria a tabela ou coluna com o nome em aspas duplas ("), o Oracle a trata como sensível a caixa. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 174 Criando tabelas n Exemplo: q create table Abc ( COL1 number ); select * from ABC; q create table "Abc" ( COL1 number ); select * from ABC; --Não funciona select * from Abc; --Não funciona select * from "Abc"; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 175 Criando tabelas: valor padrão n Na criação ou alteração de uma tabela, pode-se especificar valores padrões para colunas. n O valor padrão especificado será usado quando você não especificar o valor para aquela coluna no momento de inserção de um registro. n O valor padrão especificado deve seguir o tipo de dado e o tamanho da coluna em questão. n Se o valor padrão não é explicitado, o valor padrão é implicitamente NULL. n Geralmente constantes ou: SYSDATE, USER, USERENV e UID. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 176 Criando tabelas: valor padrão n Exemplo: create table ALUNO ( MATRICULA NOME DATA_NASCIMENTO number(6), varchar2(60) date DEFAULT 'ANÔNIMO', DEFAULT SYSDATE ); insert into ALUNO (MATRICULA) values (1); select * from ALUNO; MATRICULA NOME DATA_NASC ---------- ------------------------------ --------1 ANÔNIMO 25-APR-08 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 177 Criando tabelas: anulabilidade n Uma coluna pode ser obrigatória ou opcional. n Se a coluna é obrigatória deve ser declarada como NOT NULL e, caso seja opcional, deve ser declarada como NULL. Se não for declarado, NULL será admitido. n Exemplo: create table ALUNO ( MATRICULA NOME DATA_NASCIMENTO ); n number(6) varchar2(60) date NOT NULL, NOT NULL, NULL Neste caso a matrícula e o nome são obrigatórios, porém a data de nascimento é opcional. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 178 Criando tabelas: chave primária n Uma tabela sempre possui uma chave primária. n Uma tabela só possui uma chave primária (pode ser mais de uma coluna) que é única e nunca nula. n Existem várias sintaxes para criação de uma chave primária, porém vamos aprender a criá-la: q q junto com a instrução create table; utilizando a instrução alter table. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 179 Criando tabelas: chave primária n Exemplo da criação de uma chave primária junto ao create table: create table ALUNO ( MATRICULA number(6) NOT NULL, NOME varchar2(60) NOT NULL, DATA_NASCIMENTO date NULL, CONSTRAINT pk_aluno PRIMARY KEY (MATRICULA) ); n Caso seja mais de uma coluna, basta separá-las por vírgula: q CONSTRAINT pk_tabela PRIMARY KEY (COLA, COLB, COLC) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 180 Criando tabelas: chave primária n Exemplo da criação de uma chave primária utilizando alter table: create table ALUNO ( MATRICULA NOME DATA_NASCIMENTO ); number(6) varchar2(60) date NOT NULL, NOT NULL, NULL alter table ALUNO add CONSTRAINT pk_aluno PRIMARY KEY (MATRICULA); q Isto é, você cria a tabela sem chave primária e depois a altera inserido a restrição de chave primária. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 181 Criando tabelas: comentários n O propósito da tabela ou da coluna pode ser documentado na base de dados usando a instrução COMMENT. n Exemplos: COMMENT ON TABLE ALUNO IS 'Tabela de Armazenamento de Alunos'; COMMENT ON COLUMN ALUNO.MATRICULA IS 'Matrícula Única do Aluno'; COMMENT ON COLUMN ALUNO.NOME IS 'Nome Completo do Aluno'; COMMENT ON COLUMN ALUNO.DATA_NASCIMENTO IS 'Data de Nascimento do Aluno'; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 182 Criando tabelas: comentários n Para ver os comentários de uma tabela: q select TABLE_NAME, COMMENTS from USER_TAB_COMMENTS where TABLE_NAME = 'ALUNO'; TABLE_NAME COMMENTS ------------ --------------------------------ALUNO Tabela de Armazenamento de Alunos Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 183 Criando tabelas: comentários n Para ver os comentários das colunas de uma tabela: q select TABLE_NAME, COLUMN_NAME, COMMENTS from USER_COL_COMMENTS where TABLE_NAME = 'ALUNO'; TABLE_NAME ---------------ALUNO ALUNO ALUNO COLUMN_NAME ------------------MATRICULA NOME DATA_NASCIMENTO Ricardo Terra (rterrabh [at] gmail.com) COMMENTS --------------------------Matrícula Única do Aluno Nome Completo do Aluno Data de Nascimento do Aluno Apostila Oracle Outubro, 2008 184 Criando tabelas: a partir de outra tabela n Você pode criar uma tabela utilizando uma consulta em uma ou mais tabelas ou visões existentes. n O tipo de dado e o tamanho de cada coluna será determinado pelo resultado da consulta. n Além do mais, os dados já virão populados na nova tabela. n Sintaxe: q CREATE TABLE <nome-da-tabela> AS SELECT <consulta> Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 185 Criando tabelas: a partir de outra tabela n Exemplo: q n CREATE TABLE RH.REGIAO AS select REGION_ID as "ID_REGIAO", REGION_NAME as "NOME_REGIAO" from HR.REGIONS Quando você faz este tipo de criação, somente as restrições de anulabilidade (not null) são copiadas para a nova tabela, isto é, outras restrições e definições de valores padrões não são copiados. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 186 Destruindo tabelas n Destruir uma tabela é simples, bastando utilizar a instrução DROP TABLE. n Sintaxe: q DROP TABLE <tabela> [CASCADE CONSTRAINTS] n Quando você destroi uma tabela, os dados e as definições dela são removidas. Os índices, restrições, gatilhos (triggers) e privilégios também são destruídos. n Uma vez destruída, a ação não pode ser desfeita. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 187 Destruindo tabelas n Caso existam restrições de integridade referencial de outras tabelas que referenciam a uma chave primária ou única desta tabela, você deverá especificar o CASCADE CONSTRAINTS. n Em outras palavras, a tabela PROFESSOR não pode ser removida, pois a tabela de DISCIPLINA referencia à chave primária (CPF_PROFESSOR) desta tabela. SQL> drop table PROFESSOR; drop table PROFESSOR * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SQL> drop table PROFESSOR cascade constraints; Table dropped. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 188 Aula 10 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 189 Gerenciando restrições n Restrições são criadas em um banco de dados para garantir uma regra de negócio no banco de dados e para especificar relacionamentos entre várias tabelas. n Regras de negócio também podem ser garantidas pela utilização de gatilhos (triggers) e pelo código da aplicação. n Restrições de integridade previnem que dados inconsistentes entrem no banco de dados. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 190 Gerenciando restrições n Oracle suporta cinco tipos de restrições de integridade: NOT NULL Previne valores nulos em colunas. Por padrão, o Oracle permite valores NULL em qualquer coluna. PRIMARY KEY Identifica unicamente cada linha de uma tabela e previne valores nulos. Uma tabela pode somente possuir uma única chave primária. FOREIGN KEY Estabelece um relacionamento pai-filho entre tabelas por utilização de colunas comuns. Uma chave estrangeira em uma tabela referencia a uma chave primária ou única de uma outra tabela. UNIQUE Garante a unicidade de valores para as colunas especificadas. CHECK Verifica se a condição especificada é satisfeita. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 191 Criando restrições n Existem várias sintaxes para criação de uma chave estrangeira, porém vamos aprender a criá-la: q q n junto com a instrução create table; utilizando a instrução alter table. Se você não fornece um nome para as restrições, Oracle atribui um nome único gerado automaticamente que começa com "SYS_". q O nome é fornecido logo em seguida a palavra-chave CONSTRAINT conforme será visto na criação de restrições a frente. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 192 Criando restrições: chave estrangeira n Uma restrição de chave estrangeira (foreign key) garante que uma ou mais colunas em uma tabela tenham valores não-nulos correspondentes em alguma chave primária ou única no banco de dados. n Isto é, uma chave estrangeira é uma ou mais colunas em uma tabela (tabela filha) que referenciam a uma chave primária ou uma chave única em uma outra tabela (tabela pai). n Os tipos da coluna na tabela pai e na tabela filho devem coincidir, isto é, serem idênticos. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 193 Criando restrições: chave estrangeira n Exemplo da criação de uma chave estrangeira junto ao create table: create table DISCIPLINA( CODIGO number(3) not null PRIMARY KEY, NOME varchar2(40) not null, CARGA_HORARIA number(3) not null, CPF_PROFESSOR number(11) not null, CONSTRAINT fk_professor FOREIGN KEY (CPF_PROFESSOR) REFERENCES PROFESSOR(CPF) ); n Caso seja mais de uma coluna, basta separá-las por vírgula: q … FOREIGN KEY (COLA,COLB) REFERENCES TABLE(COLA,COLB) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 194 Criando restrições: chave estrangeira n Exemplo da criação de uma chave estrangeira utilizando alter table: create table DISCIPLINA( CODIGO number(3) not null PRIMARY KEY, NOME varchar2(40) not null, CARGA_HORARIA number(3) not null, CPF_PROFESSOR number(11) not null ); ALTER TABLE DISCIPLINA ADD CONSTRAINT fk_professor FOREIGN KEY (CPF_PROFESSOR) REFERENCES PROFESSOR(CPF); q Isto é, você cria a tabela sem chave estrangeira e depois a altera inserido a restrição de chave estrangeira. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 195 Criando restrições: chave estrangeira n Independente da sintaxe de criação, existe uma cláusula opcional a ser inserida na sintaxe da restrição: q n [ON DELETE {CASCADE | SET NULL} ] Esta cláusula especifica a ação que deve ser tormada quando um registro na tabela pai é excluído e ainda existem registros filhos que o referenciam. q q Você pode deletar todos os registros filhos (CASCADE) ou alterar a coluna de chave estrangeira para NULL (SET NULL). Se a cláusula é omitida, o Oracle não permite a exclusão de um registro se existirem registros filhos. Você deve excluir os registros filhos primeiramente antes de excluir o registro pai. É o mais utilizado. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 196 Criando restrições: chave estrangeira n Exemplo 1: alter table CIDADE add constraint FK_ESTADO foreign key (ID_ESTADO) references ESTADO (ID) ON DELETE CASCADE; n Exemplo 2: alter table CIDADE add constraint FK_ESTADO foreign key (ID_ESTADO) references ESTADO (ID) ON DELETE SET NULL; n O que acontecerá no primeiro exemplo? E no segundo? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 197 Criando restrições: chave única n Uma restrição de chave única protege uma ou mais colunas em uma tabela, garantindo que registros tenham valores repetidos. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 198 Criando restrições: chave única n Exemplo da criação de uma chave única junto ao create table: create table DISCIPLINA( CODIGO number(3) not null PRIMARY KEY, NOME varchar2(40) not null, CARGA_HORARIA number(3) not null, CPF_PROFESSOR number(11) not null, CONSTRAINT fk_professor FOREIGN KEY (CPF_PROFESSOR) REFERENCES PROFESSOR(CPF), CONSTRAINT un_nome_disciplina UNIQUE (nome) ); n Caso seja mais de uma coluna, basta separá-las por vírgula. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 199 Criando restrições: chave única n Exemplo da criação de uma chave estrangeira utilizando alter table: create table DISCIPLINA( CODIGO number(3) not null PRIMARY KEY, NOME varchar2(40) not null, CARGA_HORARIA number(3) not null, CPF_PROFESSOR number(11) not null ); ALTER TABLE DISCIPLINA ADD CONSTRAINT un_nome_disciplina UNIQUE (NOME); q Isto é, você cria a tabela sem chave única e depois a altera inserido a restrição de chave única. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 200 Criando restrições: verificação n Uma restrição de verificação (check) especifica uma condição booleana que deve ser sempre satisfeita. n Pode referir aos valores das colunas, porém não pode realizar consultas, nem utilizar funções do ambiente (tal como SYSDATE, USER, etc) e pseudo-colunas (tal como ROWNUM, etc). Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 201 Criando restrições: verificação n Exemplo da criação de uma restrição de verificação junto ao create table: create table DISCIPLINA( CODIGO number(3) not null PRIMARY KEY, NOME varchar2(40) not null, CARGA_HORARIA number(3) not null, CPF_PROFESSOR number(11) not null, CONSTRAINT fk_professor FOREIGN KEY (CPF_PROFESSOR) REFERENCES PROFESSOR(CPF), CONSTRAINT un_nome_disciplina UNIQUE (nome), CONSTRAINT ck_carga_horaria CHECK (carga_horaria between 80 and 160) ); Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 202 Criando restrições: verificação n Exemplo da criação de uma restrição de verificação utilizando alter table: create table DISCIPLINA( CODIGO number(3) not null PRIMARY KEY, NOME varchar2(40) not null, CARGA_HORARIA number(3) not null, CPF_PROFESSOR number(11) not null ); ALTER TABLE DISCIPLINA ADD CONSTRAINT ck_carga_horaria CHECK (carga_horaria between 80 and 160) q Isto é, você cria a tabela sem restrição de verificação e depois a altera inserido a restrição de verificação. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 203 Adiando verificação de restrições n Por padrão, o Oracle verifica se um dado conforma com a restrição logo que a instrução é executada. n Oracle permite alterar este comportamento se a restrição é criada com a cláusula DEFERRABLE que modifica o comportamento da verificação de restrições. q q n INITIALLY IMMEDIATE indica que um dado é verificado logo após a instrução ser executada. É o padrão. INITIALLY DEFERRED indica que a restrição deve ser verificada no final da transação, ie, no momento do commit. Aplica-se às restrições de chave primária ou de chave estrangeira. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 204 Adiando verificação de restrições n Observe o seguinte DER: q q n Um departamento só existe se possuir um empregado que o gerencie. Um empregado só existe se possuir um departamento que o lote. Enfim, para incluir um empregado deve-se vinculá-lo a um departamento e para incluir um departamento deve-se indicar quem o gerencia. E se o banco de dados estiver vazio, como faremos para populá-lo? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 205 Adiando verificação de restrições n Observe a seguinte DML: create table DEPARTAMENTO( CODIGO number(3) not null PRIMARY KEY, NOME varchar2(60) not null, CPF_GERENTE number(11) not null ); create table EMPREGADO( CPF number(11) not null PRIMARY KEY, NOME varchar2(60) not null, CODIGO_DEPTO number(3) not null ); Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 206 Adiando verificação de restrições n Observe a seguinte DML: alter table DEPARTAMENTO add constraint fk_depto_gerente FOREIGN KEY (CPF_GERENTE) references EMPREGADO(CPF) INITIALLY DEFERRED DEFERRABLE; alter table EMPREGADO add constraint fk_emp_depto FOREIGN KEY (CODIGO_DEPTO) references DEPARTAMENTO(CODIGO) INITIALLY DEFERRED DEFERRABLE; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 207 Adiando verificação de restrições n Observe o bloco transacional abaixo: BEGIN insert into DEPARTAMENTO (CODIGO, NOME, CPF_GERENTE) values (1,'Departamento Qualquer',12345678901); insert into EMPREGADO (CPF, NOME, CODIGO_DEPTO) values (12345678901,'Gerente do Departamento',1); COMMIT; END; n Funcionará? Funcionaria sem a utilização da cláusula DEFERRABLE? Por quê? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 208 Ativando e desativando restrições n Quando se cria uma restrição, ela está automaticamente ativa (a não ser que você a crie utilizando a cláusula DISABLE). n Você pode desativar uma restrição utilizando a cláusula DISABLE na instrução ALTER TABLE. n Você pode desativar qualquer restrição especificando a cláusula DISABLE seguida do nome da restrição. q n Pode-se também especificar a palavra-chave UNIQUE e o nome da(s) coluna(s) ou também somente especificando a palavra-chave PRIMARY KEY. Para desativar todas as chaves estrangeiras vinculadas a uma chave primária ou única, basta especificar CASCADE. q Mas CASCADE não funciona para a ativação. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 209 Ativando e desativando restrições n Exemplos de desativação: q ALTER TABLE professor DISABLE CONSTRAINT ck_salario; n q ALTER TABLE disciplina DISABLE CONSTRAINT un_nome_disciplina; n q Desabilita a restrição de unicidade do nome da disciplina. ALTER TABLE disciplina DISABLE UNIQUE (NOME); n q Desabilita a verificação de salário. Idem anterior. ALTER TABLE disciplina DISABLE PRIMARY KEY CASCADE; n Desabilita a verificação da chave primária da tabela DISCIPLINA e de todas as chaves estrangeiras vinculadas. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 210 Ativando e desativando restrições n Exemplos de ativação: q ALTER TABLE professor ENABLE CONSTRAINT ck_salario; n q ALTER TABLE disciplina ENABLE CONSTRAINT un_nome_disciplina; n q Ativa a restrição de unicidade do nome da disciplina. ALTER TABLE disciplina ENABLE UNIQUE (NOME); n q Ativa a verificação de salário. Idem anterior. ALTER TABLE disciplina ENABLE PRIMARY KEY; n Ativa a verificação da chave primária da tabela DISCIPLINA, mas não das chaves estrangeiras vinculadas. O CASCADE não funciona na ativação. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 211 Destruindo restrições n Restrições são destruídas usando a instrução ALTER TABLE. Qualquer restrição pode ser destruída pela espeficação do seu nome. q n Restrições de chave única podem ser destruídas pela especificação do nome da coluna única e pode também destruir todas as chaves estrangeiras vinculadas pela utilização da cláusula CASCADE. q n ALTER TABLE disciplina DROP CONSTRAINT ck_carga_horaria; ALTER TABLE disciplina DROP UNIQUE (NOME) CASCADE; O mesmo pode ser feito com a chave primária: q ALTER TABLE disciplina DROP PRIMARY KEY CASCADE; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 212 Aula 11 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 213 Esquema Venda Modelo ER Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 214 Esquema Venda Modelo Relacional CLIENTE ( cpf-ou-cnpj , titulo-eleitor , nome , cidade , estado , pais) dom(cpf-ou-cnpj) = numérico(14) NN dom(titulo-eleitor) = numérico(8) NN, com título de eleitor único dom(nome) = alfabético(60) NN dom(cidade) = alfabético(60) NN dom(estado) = alfabético(2) NN, deve ser “MG”, “RJ” ou “SP” dom(pais) = albabético(60) PRODUTO ( id-produto , descricao-produto , valor ) dom(id-produto) = numérico(2) NN dom(descricao-produto) = alfabético(30) NN, com descrição única dom(valor) = numérico(4,2) NN Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 215 Esquema Venda Modelo Relacional COMPRA ( cpf-ou-cnpj , id-produto , data , qtde ) dom(cpf-ou-cnpj) = numérico(14) NN dom(id-produto) = numérico(2) NN dom(data) = data NN dom(qtde) = numérico(1) NN, com o valor “1” como padrão COMPRA [cpf-ou-cnpj] ßr CLIENTE [cpf-ou-cnpj] COMPRA [id-produto] ßr PRODUTO [id-produto] Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 216 Renomeando tabelas n A instrução RENAME é usada para renomear uma tabela e outros objetos, como visões, sinônimos ou sequências. n Sintaxe: q RENAME nome-antigo TO novo-nome; n Quando você renomeia uma tabela, o Oracle automaticamente transfere as restrições de integridade, índices e permissões da tabela antiga para a nova tabela. n Exemplo: q RENAME funcionario TO empregado; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 217 Modificando Tabelas n Após a criação de uma tabela, existem vários motivos pelos quais você tenha que modificá-la. n Você pode modificar uma tabela para alterar a definição de alguma coluna ou seu valor padrão, adicionar uma nova coluna, remover uma coluna existente ou, até mesmo, renomear colunas. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 218 Renomeando colunas n A instrução ALTER TABLE é utilizada para renomear colunas. n Sintaxe: q n ALTER TABLE nome-tabela RENAME COLUMN nome-antigo TO novo-nome; Exemplo: q ALTER TABLE empregado RENAME COLUMN pnome TO primeiro_nome; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 219 Adicionando colunas n Às vezes pode ser necessária adicionar colunas a uma tabela já existente. n Sintaxe: q ALTER TABLE nome-tabela ADD definição-da-coluna; n Quando uma nova coluna é adicionada, ela sempre é inserida como a última coluna da tabela. n Exemplo: q ALTER TABLE empregado ADD salario number(8,2) null; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 220 Adicionando colunas n Pode-se adicionar várias colunas ao mesmo tempo, bastando apenas demarcar as colunas por parênteses e separar cada uma das colunas por vírgula. n Exemplo: q ALTER TABLE empregado ADD ( salario number(8,2) null , bonus number(6,2) null ) n Para as linhas existentes, o valor padrão da coluna será NULL. q Isto pode resultar em algum problema? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 221 Adicionando colunas n Se a coluna for NOT NULL, como pode-se preencher as linhas existentes com valor NULL? q Uma solução é adicionar a coluna com um valor padrão (o salário mínimo, por exemplo) que será preenchido para todas as linhas existentes, atualizar o salário dos empregados que não ganham o salário mínimo e. n Ex.: q ALTER TABLE empregado ADD salario number(8,2) DEFAULT 415.00 not null; ... (série de atualizações) ... ALTER TABLE empregado MODIFY salario DEFAULT NULL; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 222 Adicionando colunas q Uma outra solução seria adicioná-la como NULL, atualizar o salário de todos os empregados (colocando valores não-nulos) e então modificar a coluna para NOT NULL. n Ex.: q ALTER TABLE empregado ADD salario number(8,2) null; ... (série de atualizações) ... ALTER TABLE empregado MODIFY salario not null; q Quais das soluções apresentadas você considera mais "adequada"? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 223 Modificando colunas n Às vezes pode ser necessária modificar colunas de uma tabela já existente. n Sintaxe: q ALTER TABLE nome-tabela MODIFY nome-da-coluna novos-atributos; n Se você omitir qualquer parte da definição da coluna (tipo de dado, valor padrão ou anulabilidade) estas partes não serão modificadas. n Assim como na adição de colunas, você pode modificar várias colunas na mesma instrução usando a delimitação por parênteses e separação por vírgula. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 224 Modificando colunas n Exemplo: q Alterando o tipo de dado: n q Alterando a anulabilidade: n q ALTER TABLE empregado MODIFY salario null; Alterando o valor padrão: n q ALTER TABLE empregado MODIFY salario number(10,2); ALTER TABLE empregado MODIFY salario default 430.00; Alterando o tipo de dado, a anulabilidade e o valor padrão: n ALTER TABLE empregado MODIFY salario number(10,2) default 430.00 null; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 225 Modificando colunas n Existem algumas regras a serem seguidas para a alteração das definições de colunas: q q q Você pode aumentar o tamanho de uma coluna texto e a precisão de uma coluna numérica. Se a coluna for CHAR e a tabela possuir diversas linhas irá demandar um tempo superior, pois todos as linhas terão que serem preenchidas com espaços em branco. Você pode reduzir o tamanho de uma coluna texto ou numérica se todos os dados couberem neste novo tamanho. Para a modificação do tipo de dado, todos os valores da coluna devem estar NULL, exceto se a alteração for de CHAR para VARCHAR2 ou vice-versa. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 226 Destruindo colunas n Às vezes uma coluna não faz mais sentido e deseja-se retirá-la da tabela. n Sintaxe: q ALTER TABLE nome-tabela DROP nome-da-coluna [CASCADE CONSTRAINTS]; n A cláusula CASCADE CONSTRAINTS destrói também todas as restrições vinculadas a esta coluna. n Assim como na adição e modificação de colunas, você pode destruir várias colunas na mesma instrução usando a delimitação por parênteses e separação por vírgula. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 227 Destruindo colunas n Destruir uma coluna de uma tabela com muitos registros pode levar um tempo considerável. n O Oracle oferece uma sintaxe de desativação temporária de uma coluna, para que em uma melhor momento a coluna seja realmente destruida. n Sintaxe: q ALTER TABLE nome-tabela SET UNUSED COLUMN nome-da-coluna [CASCADE CONSTRAINTS]; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 228 Destruindo colunas n Exemplo: q n ALTER TABLE empregado SET UNUSED COLUMN titulo_eleitor; Para realmente efetivar a destruição da coluna, deve-se fazer a seguinte instrução: q ALTER TABLE empregado DROP UNUSED COLUMNS; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 229 Aula 12 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 230 Esquema RH (tradução do HR) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 231 Criando visões n Uma visão é uma representação lógica de dados de uma ou mais tabelas ou visões. Pode-se pensar em uma visão como uma consulta (query) armazenada no banco de dados. n As tabelas que a visão referencia são conhecidas como tabelas base. n Uma visão pode ser considerada uma consulta armazenada ou mesmo uma tabela virtual (não existe fisicamente). q q Somente a consulta é armazenada no dicionário de dados, os dados atuais não são copiados de forma alguma (com exceções da visão materializada que será abordada à frente). Isto indica que visão convencional não ocupa qualquer espaço de armazenamento, somente um espaço no dicionário de dados. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 232 Criando visões n Exemplo: q O departamento 90 é o departamento Executivo, portanto segue uma visão simplificada sobre os seus funcionários: n q create view EXECUTIVO as select PRIMEIRO_NOME || ' ' || ULTIMO_NOME as "NOME", EMAIL from RH.EMPREGADO where ID_DEPARTAMENTO = 90; Basta a seguinte consulta para retornar todos os executivos: n select * from EXECUTIVO; NOME ---------------------------------Steven King Neena Kochhar Lex De Haan Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle EMAIL ------------------------SKING NKOCHHAR LDEHAAN Outubro, 2008 233 Criando visões n Visões podem ser baseadas em consultas mais sofisticadas, envolvendo junções, funções, agrupamentos, subqueries, etc. q Observe a seguinte visão. Ela retorna a quantidade de funcionários por função de cada departamento daquelas funções que possuem mais de dois funcionários ordenadas pelo departamento, depois pela função e depois pela quantidade é uma consulta muito utilizada. n create view REL_DEPTO_FUNCAO as select d.NOME_DEPARTAMENTO as "DEPARTAMENTO", f.DESCRICAO_FUNCAO as "FUNÇÃO", to_char(count(e.ID_EMPREGADO),'000') as "QUANTIDADE" from RH.DEPARTAMENTO d inner join RH.EMPREGADO e using (ID_DEPARTAMENTO) inner join RH.FUNCAO f using (ID_FUNCAO) group by d.NOME_DEPARTAMENTO, f.DESCRICAO_FUNCAO having count(e.ID_EMPREGADO) > 2 order by 1, 2, 3 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 234 Criando visões q Como é uma consulta largamente utilizada, agora basta realizar a seguinte consulta que retornará o resultado esperado: n select * from REL_DEPTO_FUNCAO; DEPARTAMENTO -------------------Finance IT Purchasing Sales Sales Shipping Shipping Shipping Ricardo Terra (rterrabh [at] gmail.com) FUNÇÃO -------------------Accountant Programmer Purchasing Clerk Sales Manager Sales Representative Shipping Clerk Stock Clerk Stock Manager Apostila Oracle QUAN ---005 005 005 005 029 020 020 005 Outubro, 2008 235 Criando visões somente leitura n Ao criar uma visão, instruções DML podem ser executadas nela. Por exemplo, pode-se inserir, atualizar, excluir dados de uma visão, o que irá modificar as tabelas base envolvidas. q n Existem uma série de regras para que possa realizar instruções DML em uma visão. Porém, para evitar qualquer problema, pode-se criar uma visão somente leitura, bastando apenas utilizar a opção WITH READ ONLY. q As visões criadas com esta opção somente podem ser usadas em consultas, nenhuma outra instrução DML poderá ser utilizada. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 236 Criando visões somente leitura q Exemplo: n q create view ASSALARIADO as select PRIMEIRO_NOME || ' ' || ULTIMO_NOME as "NOME", SALARIO as "SALARIO" from RH.EMPREGADO WITH READ ONLY A seguinte instrução não funcionará: n update ASSALARIADO set SALARIO = 4000 where NOME = 'Sarah Bell' Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 237 Criando visões materializadas n Uma visão materializada é uma visão que armazena os resultados de uma consulta. n Tem várias utilidades, mas, geralmente, são cópias locais de dados localizados remotamente ou são usadas para criar tabelas sumarizadas baseadas nos dados de uma certa tabela. n Elas são cópias e devem possuir um mecanismo de atualização destes dados. q Geralmente a atualização ocorre de tempos em tempos com base na chave primária. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 238 Criando visões materializadas q Exemplo: n q create materialized view FUNCIONARIO REFRESH FORCE START WITH SYSDATE NEXT SYSDATE + 1 WITH PRIMARY KEY as select * from RH.EMPREGADO@banco_dados_remoto "@banco_dados_remoto" é um dblink para um outro SGBD Oracle. n Portanto, não funcionará. Foi apenas para dar mais realidade ao uso. q Para funcionar basta retirar o "@banco_dados_remoto" Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 239 Modificando visões n Para alterar uma definição de uma visão, deve-se utilizar a instrução CREATE VIEW com a opção OR REPLACE. n Quanto se usa a opção OR REPLACE, se a visão existir, ela será substituída pela nova definição. Caso contrário, uma nova visão será criada. n Como exemplo, a visão assalariado tem formatação do salário: q create OR REPLACE view ASSALARIADO as select PRIMEIRO_NOME || ' ' || ULTIMO_NOME as "NOME", to_char(SALARIO,'990,000.00') as "SALARIO" from RH.EMPREGADO Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 240 Destruindo visões n Para destruir uma uma visão, deve-se utilizar a instrução DROP VIEW. n Ao destruir uma visão, a definição desta visão é removida do dicionário de dados e os privilégios e permissões da visão também serão removidos. q n Outras visões que se referem a ela, tornarão-se inválidas. Exemplo: q drop view ASSALARIADO Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 241 Outras operações em visões n Existem várias outras coisas que podem ser feitas em uma visão como: q Criar visões com erros (opção FORCE) q Criar restrições em visões q Utilizar a instrução ALTER VIEW para: n n n compilar ou recompilar validar e invalidar adicionar ou destruir restrições Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 242 Utilizando visões n Uma visão pode ser utilizada na maioria dos locais onde uma tabela é utilizada, tal como em consultas e instruções DML. q Para verificar as visões do seu esquema basta: n q select VIEW_NAME from USER_VIEWS Para verificar as visões, que possui acesso, de um outro esquema deve ser realizado o seguinte comando: n select VIEW_NAME from ALL_VIEWS where OWNER = '<nome-usuário>' Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 243 Utilizando visões n Se certas condições são respeitadas, maioria das visões baseadas em uma única tabela e muitas das visões baseadas em junções podem ser utilizadas para inserir, atualizar e deletar dados da(s) tabela(s) base. q Para verificar quais instruções DML podem ser realizadas em uma dada visão, basta: n n select COLUMN_NAME, UPDATABLE, INSERTABLE, DELETABLE from USER_UPDATABLE_COLUMNS where OWNER = '<nome-usuário>' and TABLE_NAME='<nome-tabela>' Todas as operações em visões afetam as tabelas base, contudo elas devem satisfazer toda restrição de integridade definida nas tabelas base. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 244 Utilizando visões n São utilizações comuns de visões: q Representar um subconjunto de dados q Representar um superconjunto de dados q Ocultar junções complexas q Prover nomes mais entendíveis para colunas q Criação de uma camada entre a aplicação e a fonte de dados Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 245 Aula 13 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 246 Esquema Acadêmico Simples Modelo ER Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 247 Esquema Acadêmico Simples Modelo Relacional PROFESSOR (cpf, nome, salario, titulacao) dom(cpf) = numérico(11) NN dom(nome) = alfabético(60) NN dom(salario) = numérico(9,2) NN dom(titulacao) = alfabético(40) NN ALUNO (matricula, nome, data_nascimento) dom(matricula) = numérico(6) NN dom(nome) = alfabético(60) NN dom(data_nascimento) = data NN Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 248 Esquema Acadêmico Simples Modelo Relacional DISCIPLINA (codigo, nome, carga_horaria, cpf_professor) dom(codigo) = numérico(3) NN dom(nome) = alfanumérico(40) NN dom(carga_horaria) = numérico(3) NN dom(cpf_professor) = numérico(11) NN DISCIPLINA [cpf_professor] ß PROFESSOR [cpf] ALUNO_DISCIPLINA (matricula_aluno, codigo_disciplina, pontuacao) dom(matricula_aluno) = numérico(6) NN dom(codigo_disciplina) = numérico(3) NN dom(pontuacao) = numérico(3) ALUNO_DISCIPLINA [matricula_aluno] ß ALUNO [matricula] ALUNO_DISCIPLINA [codigo_disciplina] ßDISCIPLINA [codigo] Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 249 Outros objetos do banco de dados n Uma base de dados do Oracle contem muito mais do que simples tabelas e visões: q q q Sequências podem ser utilizadas para gerar chaves artificiais; Sinônimos provêm apelidos (alias) para objetos; Vários tipos de índices podem ser inseridos para melhorar o desempenho de consultas. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 250 Sequências n Uma sequência do Oracle é um gerador nominado de números sequênciais. n Geralmente são utilizadas para chaves artificiais ou para ordenar linhas que de outra forma não teriam ordem. n Assim como as restrições, as sequências existem somente no dicionário de dados. n Elas podem ser configuradas para aumentar ou diminuir sem limites ou para repetir após alcançar um valor limite. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 251 Criando sequências n Sintaxe: q CREATE SEQUENCE [esquema].<nome-da-sequência> [START WITH integer] [INCREMENT BY integer] [MINVALUE integer | NOMINVALUE] [MAXVALUE integer | NOMAXVALUE] [CYCLE | NOCYCLE] [CACHE integer | NOCACHE] [ORDER | NOORDER] Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 252 Criando sequências n START WITH q n INCREMENT BY q n Define a quantidade que aumenta ou diminui entre números. O padrão é 1. Para sequências descendentes, basta inserir um valor negativo. MINVALUE q n Define o valor inicial da sequência. Por padrão é MAXVALUE para sequências descendentes (-1) e MINVALUE para ascendentes (1). Define o menor valor que a sequência irá gerar. O padrão é NOMINVALUE. (1 para sequências descendentes e -1026 para as ascendentes. MAXVALUE q Define o maior valor que a sequência irá gerar. O padrão é NOMAXVALUE. (-1 para sequências descendentes e 1027 para as ascendentes. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 253 Criando sequências n CYCLE q n NOCYCLE q n Configura a sequência para não repetir números após alcançar o limite. Este é o padrão. Quando tentar gerar MAXVALUE+1, uma exceção será lançada. CACHE q n Configura a sequência para repetir números após alcançar o limite. Define o tamanho do bloco de números da sequência armazenados na memória. O padrão é 20. NOCACHE q Força o dicionário de dados a atualizar a sequência para cada número gerado, garantindo que nenhum espaço existirá entre os números gerados, porém o desempenho inferior. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 254 Criando sequências n ORDER q n Garante que os números da sequência sejam gerados pela ordem de requisição. NOORDER q Não garante que os números da sequência sejam gerados pela ordem de requisição. Esta é a opção padrão. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 255 Criando sequências n Exemplo de uma sequência ascendente: q create sequence SQ_MATRICULA_ALUNO; corresponde a q n create sequence SQ_MATRICULA_ALUNO START WITH 1 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE CACHE 20 NOORDER Exemplo de uma sequência descendente: q create sequence SQ_MATRICULA_ALUNO INCREMENT BY -1 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 256 Destruindo sequências n Sintaxe: q n drop sequence <nome-da-sequência> Exemplo: q drop sequence SQ_MATRICULA_ALUNO Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 257 Usando sequências n Para acessar o próximo número na sequência, você simplesmente seleciona, usando a pseudo-coluna NEXTVAL. q Ex.: n n select SQ_MATRICULA_ALUNO.NEXTVAL from dual; Para buscar o último número da sequência gerado na sua sessão, você seleciona a pseudo-coluna CURRVAL. q Ex.: n q select SQ_MATRICULA_ALUNO.CURRVAL from dual; Se você ainda não tiver gerado nenhum número da sequência, CURRVAL não funcionará. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 258 Usando sequências n Exemplo de usos: q Inserindo um novo aluno: n q insert into ALUNO (matricula, nome, data_nascimento) values (sq_matricula_aluno.nextval, 'Aluno', sysdate) Atualização de todas as matrículas: n update ALUNO set matricula = sq_matricula_aluno.nextval Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 259 Alterando sequências n Você não pode simplesmente alterar a sequência e ajustar o seu NEXTVAL. Para isto existem as seguintes soluções: q Destruí-la e recriá-la. n q q Invalidará todos os objetos dependentes e perderá as permissões Selecionar NEXTVAL até que a sequência alcançe o valor desejado. Alterar a sequência ajustando o valor do INCREMENT BY para um valor muito grande, selecionar NEXTVAL e depois alterar a sequência para voltar ao INCREMENT BY ao valor original. n alter sequence SQ_MATRICULA_ALUNO increment by 1000; select SQ_MATRICULA_ALUNO.NEXTVAL from dual; alter sequence SQ_MATRICULA_ALUNO increment by 1; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 260 Sequências n Para ver as sequências existentes e suas propriedades, basta: q n select * from USER_SEQUENCES; Para ver de um outro usuário: q select * from ALL_SEQUENCES where SEQUENCE_OWNER = '<usuario>'; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 261 Sinônimos n Sinônimo é um apelido para um outro objeto do banco de dados. n Sintaxe de criação: q q create synonym <nome-sinônimo> for <[esquema].<nome-objeto>> Ex.: n n create synonym EMPREGADO for RH.EMPREGADO Sintaxe de destruição: q Ex.: n drop synonym EMPREGADO Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 262 Índices n Os índices são estruturas de dados que podem oferecer um melhor desempenho na obtenção de linhas específicas do que o full-table scan (escaneamento completo da tabela) que é o padrão. n Índices podem aumentar o desempenho de instruções SELECT, porém podem ser gargalos para operações de alterações de dados, pois devem ser atualizados de acordo com os dados. q n Por isto devem ser utilizados com racionalidade e cautela. Pensamento: q Um bilhão de empregados. Crio os índices e depois insiro todos os empregados ou insiro todos os empregados e depois crio os índices? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 263 Índices n Os índices podem ser únicos (UNIQUE) ou não únicos. n Os índices podem vincular mais de uma coluna. São conhecidos como índices concatenados. n Pensamentos: q Qual possui melhor desempenho? q Se algum é melhor, posso sempre utilizá-lo? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 264 Criando índices n Sintaxe: q n CREATE [UNIQUE] INDEX <nome-índice> ON <nome-tabela> ( <nome-coluna> [, <nome-coluna>... ] ) Exemplo: q q create index IX_DATA_NASCIMENTO on ALUNO (DATA_NASCIMENTO) create unique index IX_NOME_UNICO on ALUNO (NOME) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 265 Destruindo índices n Sintaxe: q n DROP INDEX <nome-índice> Exemplo: q drop index IX_DATA_NASCIMENTO q drop index IX_NOME_UNICO Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 266 Aula 14 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 267 Acesso de usuário e segurança n O Oracle provê vários métodos para controle de acesso de usuários. Quando se cria um usuário, pode-se especificar como ele é autenticado e também ajustar vários de seus atributos. n O modo inicial para controle de acesso de usuários é através de privilégios. Por garantir (grant) ou revogar (revoke) privilégios, você pode especificar o que os usuários podem fazer como certos objetos do banco de dados. n Como os usuários usarão os recursos do sistema e diretivas de senha se fazem por um outro modo de controle conhecido como profile (perfil). Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 268 Criando e alterando usuários n A instrução CREATE USER é empregada para a criação de usuários (também conhecidos como conta ou esquema) e pode, opcionalmente, designar certos atributos adicionais ao usuário. n A instrução ALTER USER é utilizada para a realização de alterações de quaisquer atributos do usuário, porém o mesmo já deve estar criado. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 269 Criando usuários n A sintaxe para criação de usuários é: q n CREATE USER <nome-do-usuário> IDENTIFIED BY <senha-do-usuário> O usuário acima é criado para ser autenticado no próprio Oracle. Porém existem outros modos de criação de usuário: q q Autenticação externa: Validação de senha pelo sistema operacional ou pela rede. Autenticação Global: Validação de senha pelo Serviço de Segurança do Oracle (Oracle Security Service), um serviço configurado e licenciado separadamente. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 270 Designando atributos à usuários n Atributos da conta são atribuidos com a própria instrução CREATE como com a instrução ALTER USER. q n A instrução CREATE USER deve conter minimamente o usuário e a cláusula da senha. As instruções CREATE e ALTER USER podem: q q q q q q q q Designar validação (senha) Designar a default tablespace (espaço de tabelas padrão) Designar a temporary tablespace (espaço de tabelas padrão) Designar a quota de espaço disponível Vincular a um profile (perfil) Habilitar e desabilitar uma role (papel) Ajustar expiração de senha Travar e destravar usuários Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 271 Designando atributos à usuários n Segue exemplos dos mais utilizados: q Alterando senha: n q Vinculando a um profile: n q ALTER USER <usuário> IDENTIFIED BY <senha> ALTER USER <usuário> PROFILE <nome-do-perfil> Vinculando a um role: n ALTER USER <usuário> DEFAULT ROLE <nome-do-papel> q Somente funciona com usuários administradores e só com a instrução ALTER USER. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 272 Designando atributos à usuários q Expirando senha: n q ALTER USER <usuário> PASSWORD EXPIRE Travando um usuário: n ALTER USER <usuário> ACCOUNT LOCK q Para destravar, basta substituir LOCK por UNLOCK Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 273 Usando controle de licença de usuários n O Oracle fornece vários tipos de licença de funcionamento. Para que se respeite a licença de uso adquirida, o Oracle inclui alguns mecanismos de controle. n Você pode limitar o número de sessões permitidas a conectar simultaneamente e, também, o número de usuários que podem estar criados no SGBD. q Estes parâmetros que estão no init.ora podem ajudar nas restrições: n n n n licence_max_users licence_sessions_warning licence_max_sessions Como administrador, para consultar os usuário do Oracle: q select USERNAME, PASSWORD, CREATED, PROFILE, ACCOUNT_STATUS from DBA_USERS; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 274 Criando e usando roles n Um role é um instrumento para administrar privilégios. Os privilégios podem ser garantidos a um role e este role ser garantido a um usuário ou, até mesmo, a um outro role. q n Não servem para nenhum outro propósito a não ser um agrupamento de privilégios para facilitar a administração de privilégios. Sintaxe: q CREATE ROLE <nome-do-papel>; n Pode-se inserir uma senha para o role, se isto for feito ela deve ser ativada para seu funcionamento. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 275 Criando e usando roles n Exemplo: q CREATE ROLE permissao_aluno; GRANT select on rh.empregado TO permissao_aluno; GRANT select on rh.departamento TO permissao_aluno; GRANT select on rh.funcao TO permissao_aluno; GRANT select on rh.localizacao TO permissao_aluno; GRANT select on rh.pais TO permissao_aluno; GRANT select on rh.regiao TO permissao_aluno; GRANT select on rh.historico_funcao TO permissao_aluno; GRANT permissao_aluno TO aluno1, aluno2, aluno3, aluno4; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 276 Criando e usando profiles n Profiles permitem gerenciar grupos de usuários por limitar recursos de processamento ou políticas de senha. n Todos os usuário possuem um profile. O profile padrão é admitido quando não é explicitado na criação de um usuário. n As instruções CREATE PROFILE e ALTER PROFILE são responsáveis, respectivamente, por criar e alterar profile. Possuem praticamente a mesma sintaxe. q A única diferença é que a instrução ALTER PROFILE necessita que o profile já exista. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 277 Criando e usando profiles n Sintaxe: q n [CREATE | ALTER] PROFILE <nome-do-perfil> LIMIT <restrição> Existem várias restrições, são alguns exemplos: q q CONNECT_TIME CPU_PER_CALL SESSIONS_PER_USER PASSWORD_LIFE_TIME PASSWORD_LOCK_TIME PASSWORD_VERIFY_FUNCTION IDLE_TIME CPU_PER_SESSION FAILED_LOGON_ATTEMPTS PASSWORD_GRACE_TIME PASSWORD_REUSE_TIME PRIVATE_SGA Mais detalhes podem ser encontrados nas páginas 482-490 da referência DAWES e THOMAS (2002). Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 278 Criando e usando profiles n Exemplo: q CREATE PROFILE aluno LIMIT SESSIONS_PER_USER 1; ALTER PROFILE aluno LIMIT CONNECT_TIME 3; ALTER PROFILE aluno LIMIT IDLE_TIME 1; ALTER PROFILE aluno LIMIT FAILED_LOGIN_ATTEMPTS 3; ALTER PROFILE aluno LIMIT PASSWORD_LIFE_TIME 30; ALTER PROFILE aluno LIMIT PASSWORD_REUSE_TIME 90; CREATE USER alunox IDENTIFIED BY alunox PROFILE aluno; GRANT papel_usuario TO alunox; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 279 Garantindo e revogando privilégios n Privilégios permitem um usuário acessar objetos ou executar programas que são de um outro usuário ou executar operações de nível de sistema, como criar e destruir objetos. n Privilégios podem ser garantidos (grant) para um usuário, para o usuário especial PUBLIC ou para um role. Uma vez garantidos, tais privilégios podem ser revogados (revoke). Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 280 Garantindo e revogando privilégios n O Oracle tem três tipos de privilégios: q Privilégios de objetos (Object privileges) n q Privilégios de sistema (System privileges) n q Habilita ao usuário a desenvolver operações de nível de sistema, tais como conexão, alterar sessão, criar tabelas ou criar usuários. Privilégios de role (Role privileges) n n São permissões em objetos de esquemas, como tabelas, visões, funções e bibliotecas. São aqueles privilégios que um usuário possui por intermédio de um role. Nos próximos slides iremos ver quais são estas permissões, contudo mais detalhes podem ser encontrados nas páginas 464-473 da referência DAWES e THOMAS (2002). Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 281 Privilégios de objetos n Privilégios de objetos: q ALTER DELETE EXECUTE INDEX INSERT SELECT UPDATE ALL q Outros privilégios: q q q q q q q n READ Ricardo Terra (rterrabh [at] gmail.com) REFERENCE Apostila Oracle Outubro, 2008 282 Privilégios de sistema n Privilégios de sistema: q CLUSTER DATABASE INDEX PROCEDURE PROFILE ROLE ROLLBACK SEGMENT q Existem alguns privilégios especiais: q q q q q q n q SYSDBA SEQUENCE SESSION SYNONYM TABLE TABLESPACE USER VIEW SYSOPER Outros privilégios: n n ANALYSE PRIVILEGE Ricardo Terra (rterrabh [at] gmail.com) AUDIT ROLE Apostila Oracle COMMENT Outubro, 2008 283 Atribuindo privilégios n Quando se deseja atribuir um ou mais privilégios para um usuário ou um role, deve ser utilizada a instrução GRANT. n SINTAXES: q q GRANT <privilegio-de-objeto> TO <usuário|role|PUBLIC> [WITH GRANT OPTION] GRANT <privilegio-de-sistema> TO <usuário|role|PUBLIC> [WITH ADMIN OPTION] n Garantir um privilégio para o usuário especial PUBLIC implica em garantir o privilégio para qualquer usuário que conectar o banco de dados. n Quando um privilégio é garantido, o seu efeito é imediato, não necessitando que um usuário desconecte e conecte novamente. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 284 Atribuindo privilégios de objetos n Exemplos de atribuição de privilégios de objetos: q GRANT SELECT ON rh.empregado TO joazinho GRANT UPDATE (ultimo_nome, salario ) ON rh.empregado TO joazinho GRANT INSERT, UPDATE, DELETE ON rh.departamento TO joaozinho Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 285 Atribuindo privilégios de objetos n Privilégios de objetos podem ser garantidos com WITH GRANT OPTION, que permite ao garantido garantir estes privilégios para qualquer outro usuário ou role. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 286 Atribuindo privilégios de objetos n Por exemplo, se o usuário RH conceder consulta a tabela FUNCAO ao Joãozinho usando WITH GRANT OPTION, Joãozinho pode conceder consulta a essa mesma tabela a Mariazinha: q q conn rh/rh@xe; GRANT SELECT ON rh.funcao TO joaozinho WITH GRANT OPTION; conn joaozinho/joaozinho@xe; GRANT SELECT ON rh.funcao TO mariazinha; conn mariazinha/mariazinha@xe; select * from rh.funcao; Porém, se Joãozinho deixar de ser um usuário, Mariazinha pode perder a sua garantia de consulta. n A não ser que outro usuário também tenha concedido este privilégio a ela. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 287 Atribuindo privilégios de objetos n Entendimento 1: Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 288 Atribuindo privilégios de objetos n Entendimento 2: Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 289 Atribuindo privilégios de sistema n Exemplos de atribuição de privilégios de sistema: q GRANT CREATE SESSION TO joaozinho GRANT CREATE SEQUENCE TO joaozinho GRANT CREATE SYNONYM TO joaozinho GRANT CREATE TABLE TO joaozinho GRANT CREATE ANY TABLE TO joaozinho GRANT DROP TABLE TO joaozinho GRANT DROP ANY TABLE TO joaozinho GRANT CREATE PROCEDURE TO joaozinho GRANT EXECUTE ANY PROCEDURE TO joaozinho GRANT CREATE USER TO joaozinho GRANT DROP USER TO joaozinho GRANT CREATE VIEW TO joaozinho Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 290 Atribuindo privilégios de sistema n Existe três roles que abrangem vários privilégios de sistema e facilitam em muito a concessão de várias permissões: q CONNECT n q Contém somente o privilégio CREATE SESSION. RESOURCE n Contém os seguintes privilégios: q q CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE. DBA n Contém os privilégios comuns de um administrador do banco de dados. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 291 Atribuindo privilégios de sistema n Privilégios de sistema podem ser garantidos com WITH ADMIN OPTION, que permite ao garantido garantir estes privilégios de sistema para qualquer outro usuário ou role. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 292 Atribuindo privilégios de sistema n Por exemplo, se o usuário RH conceder criação de visão ao Joãozinho usando WITH ADMIN OPTION, Joãozinho pode conceder criação de visão a Mariazinha: q q conn rh/rh@xe; GRANT CREATE VIEW TO joaozinho WITH ADMIN OPTION; conn joaozinho/joaozinho@xe; GRANT CREATE VIEW TO mariazinha; conn mariazinha/mariazinha@xe; create view DUPLO as select * from dual; Ao contrário do privilégio de objetos, que utiliza WITH GRANT OPTION, se Joãozinho deixar de ser um usuário, Mariazinha não perde a garantia de criação de visão. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 293 Atribuindo privilégios de sistema n Entendimento: Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 294 Revogando privilégios n Quando se deseja revogar um ou mais privilégios para um usuário ou um role, deve ser utilizada a instrução REVOKE. n SINTAXE: q REVOKE <privilegio-de-objeto-ou-sistema> FROM <usuário|role|PUBLIC> Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 295 Revogando privilégios n Exemplos de revogação de privilégios de sistema: q q q q q q q q q q q q REVOKE CREATE SESSION FROM joaozinho REVOKE CREATE SEQUENCE FROM joaozinho REVOKE CREATE SYNONYM FROM joaozinho REVOKE CREATE TABLE FROM joaozinho REVOKE CREATE ANY TABLE FROM joaozinho REVOKE DROP TABLE FROM joaozinho REVOKE DROP ANY TABLE FROM joaozinho REVOKE CREATE PROCEDURE FROM joaozinho REVOKE EXECUTE ANY PROCEDURE FROM joaozinho REVOKE CREATE USER FROM joaozinho REVOKE DROP USER FROM joaozinho REVOKE CREATE VIEW FROM joaozinho Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 296 Aula 15 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 297 Sistema da Locadora: MR s/atributos ATOR ( id_ator , nome_real , nome_artistico , dt_nascimento ) FILME ( id_filme , titulo , id_categoria ) FILME [id_categoria] ß CATEGORIA [id_categoria] CATEGORIA ( id_categoria , descricao ) ATOR_FILME ( id_ator , id_filme) ATOR_FILME [id_ator] ß ATOR [id_ator] ATOR_FILME [id_filme] ß FILME [id_filme] DVD ( id_filme , numero ) DVD [id_filme] ßFILME [id_filme] Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 298 Sistema da Locadora: MR s/atributos LOCACAO ( id_filme , numero , id_cliente , dt_locacao , dt_devolucao_prevista, dt_devolucao_efetiva) LOCACAO [id_filme , numero] ß DVD [id_filme , numero] LOCACAO [id_cliente] ß CLIENTE [id_cliente] CLIENTE ( id_cliente , nome , telefone , id_endereco) CLIENTE [id_endereco] ß ENDERECO [id_endereco] TITULAR ( id_titular , cpf ) TITULAR [id_titular] ß CLIENTE [id_cliente] DEPENDENTE ( id_dependente , id_titular ) DEPENDENTE [id_dependente] ß CLIENTE [id_cliente] DEPENDENTE [id_titular] ß TITULAR [id_titular] ENDERECO ( id_endereco , logradouro , numero , complemento , cep , bairro , cidade , estado) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 299 Visões, permissões e otimizações n Hoje vamos trabalhar com visões, permissões e otimizações n Com o uso de visões, conseguimos filtrar informações de um banco de dados. São utilizações comuns de visões: q q q q q Representar um subconjunto de dados Representar um superconjunto de dados Ocultar junções complexas Prover nomes mais entendíveis para colunas Criação de uma camada entre a aplicação e a fonte de dados n Com o uso de permissões, podemos restringir o que cada usuário pode fazer com certos objetos do banco de dados n Com o uso de índices, podemos melhorar o desempenho em várias consultas Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 300 Cronograma da aula Criar três tipos de usuários: 1. n Administrador, cliente e atendente Criar todo o modelo do Sistema Locadora naquele que será o administrador, no caso, proprietário 2. n Explicar o funcionamento usual em sistemas web 3. Criar visões aos usuários do tipo cliente e atendente 4. Garantir as devidas permissões ao cliente e ao atendente 5. Criar sinônimos 6. Pensar em otimização utilizando índices Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 301 Para o cliente n Criação da visão somente leitura VW_DADOS_CLIENTE q q n Exibirá o ID_CLIENTE, CPF, NOME, TIPO, TELEFONE, ENDERECO e o NOME_TITULAR (este último caso seja dependente) A própria aplicação fará o filtro pelo ID_CLIENTE Criação da visão somente leitura VW_VINCULO_DEPENDENTE q Exibirá o ID_CLIENTE, ID_DEPENDENTE e NOME_DEPENDENTE q A própria aplicação fará o filtro pelo ID_CLIENTE Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 302 Para o cliente n Criação da visão somente leitura VW_RELATORIO_FILMES q n Exibirá o NOME_FILME, CATEGORIA, NOME_ARTISTICO (caso não exista exibir o NOME_REAL), QTDE e QTDE_DISP Criação da visão somente leitura VW_HISTORICO_LOCACOES q Exibirá o NOME_USUARIO, TIPO_USUARIO, NOME_FILME, DT_LOCACAO, DT_DEV_PREVISTA, DT_DEV_EFETIVA e STATUS n q STATUS pode ser ALUGADO, DEVOLUÇÃO PENDENTE, DEVOLVIDO e DEVOLVIDO COM ATRASO A própria aplicação fará o filtro pelo ID_CLIENTE Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 303 Para o cliente n As seguintes permissões devem ser concedidas: q Em todas as visões criadas para o cliente n R (Retrieve) q n Pois somente poderá consultar Sinônimos para todas as visões criadas para o cliente foram criados: q q q q create create create create SYNONYM SYNONYM SYNONYM SYNONYM VW_RELATORIO_FILMES FOR LOC_ADM.VW_RELATORIO_FILMES; VW_DADOS_CLIENTE FOR LOC_ADM.VW_DADOS_CLIENTE; VW_HISTORICO_LOCACOES FOR LOC_ADM.VW_HISTORICO_LOCACOES; VW_VINCULO_DEPENDENTE FOR LOC_ADM.VW_VINCULO_DEPENDENTE; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 304 Para o atendente n Criação da visão somente leitura VW_RELATORIO_FILMES_COMPLETO q n Exibirá o NOME_FILME, CATEGORIA, NOME_REAL, NOME_ARTISTICO QTDE e QTDE_DISP Criação da visão somente leitura VW_SITUACAO_DVDS q Exibirá o ID_FILME, NOME_FILME, NUMERO_DVD e STATUS n n STATUS pode ser ALUGADO, DEVOLUÇÃO PENDENTE e DISPONÍVEL Criação de uma tabela FUNCIONARIO q q q Para informar os dados do atendente e para o mesmo se logar Terá ID_FUNCIONARIO, CPF, NOME e SENHA Pertencerá ao administrador Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 305 Para o atendente n As seguintes permissões devem ser concedidas: q Em LOCACAO n CRUD (Create, Retrieve, Update e Delete) q q Em FUNCIONARIO n R q q Pois o atendente somente poderá consultar essa tabela para se logar Em CLIENTE, TITULAR, DEPENDENTE e ENDERECO n CRUD q q Pois o atendente deve gerenciar as locações Pois o atendente deve gerenciar o cadastro de clientes Em SQ_CLIENTE e SQ_ENDERECO n Acesso q Pois o atendente deve utilizar essas sequências para as atividades acima mencionadas Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 306 Para o atendente n Sinônimos para todas as visões criadas para o atendentes foram criados: q q create SYNONYM VW_RELATORIO_FILMES_COMPLETO FOR LOC_ADM.VW_RELATORIO_FILMES_COMPLETO; create SYNONYM VW_SITUACAO_DVDS FOR LOC_ADM.VW_SITUACAO_DVDS; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 307 Como ficou? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 308 Otimização n Observando as visões criadas e as responsabilidades tanto do cliente quanto do atendente, quais índices seriam apropriados para criação e porque? q CPF e SENHA do FUNCIONÁRIO n q ID_CLIENTE em LOCACAO n q create index ix_locacao_dvd on LOCACAO(ID_FILME, NUMERO); ID_TITULAR em DEPENDENTE n q create index ix_locacao_cliente on LOCACAO(ID_CLIENTE); ID_FILME e NUMERO em LOCACAO n q create index ix_login_funcionario on FUNCIONARIO(CPF,SENHA); create index ix_dependente_titular on DEPENDENTE(ID_TITULAR); ... Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 309 Otimização q ID_CATEGORIA em FILME n q create index ix_filme_categoria on FILME(ID_CATEGORIA); NOME em CLIENTE n create index ix_nome_cliente on CLIENTE(NOME); Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 310 Aula 16 Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 311 Triggers n Triggers são procedimentos que podem ser gravados em Java, PL/ SQL ou C. São executados (ou disparados) implicitamente quando uma tabela é modificada, um objeto é criado ou ocorrem algumas ações de usuário ou de sistema de banco de dados n As triggers são similares as stored procedures diferenciando, apenas, na maneira como são chamadas. A trigger é executada implicitamente quando ocorre algum evento enquanto a stored procedure deve ser executado explicitamente Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 312 Triggers n Uma trigger é composta por quatro partes: q q q q Momento Evento Tipo Corpo Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 313 Triggers - momento n Momento define quando uma trigger irá ser acionada. Pode ser: q q q BEFORE (tabela) AFTER (tabela) INSTEAD OF (view) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 314 Triggers - momento n BEFORE indica que os comandos PL/SQL do corpo da trigger serão executados ANTES dos dados da tabela serem alterados. Normalmente usamos BEFORE nos casos em que precisamos incializar variáveis globais, validar regras de negócios, alterar o valor de flags ou para salvar o valor de uma coluna antes de alterarmos o valor delas. Exemplo: CREATE OR REPLACE TRIGGER novo_func BEFORE... . . END; / Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 315 Triggers - momento n AFTER indica que os comando PL/SQL do corpo da trigger será executado APÓS os dados da tabela serem alterados. Normalmente usamos AFTER para completar os dados de outras tabelas e para completar a atividade de outra trigger de momento BEFORE. Exemplo: CREATE OR REPLACE TRIGGER novo_func AFTER... . . END; / Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 316 Triggers - momento n INSTEAD OF indica que a trigger irá ser executada no lugar da instrução que disparou a trigger. Literalmente, a instrução é substituída pela trigger n Essa técnica permite que façamos, por exemplo, alterações em uma tabela através de uma view. É usado nos casos em que a view não pode alterar uma tabela por não referenciar à uma coluna com a restrição not null. Nesse caso a trigger pode atualizar a coluna que a view não tem acesso Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 317 Triggers - momento n Dois detalhes muito importantes sobre INSTEAD OF: q q n Só funcionam com views É sempre de linha. Será considerado assim, mesmo que "FOR EACH ROW" for omitido Exemplo: CREATE OR REPLACE TRIGGER novo_func INSTEAD OF INSERT ON vemp FOR EACH ROW WHEN ... . . END; / Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 318 Triggers - evento n O evento define qual é a instrução DML que aciona/disparar a trigger. Pode ser: q q q n INSERT UPDATE DELETE Quando o evento for um UPDATE podemos informar quais colunas que, ao serem alteradas, irão disparar a trigger. O mesmo NÃO ocorre com INSERT e DELETE porque essas instruções sempre afetam a linha por inteiro Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 319 Triggers - evento n Exemplo: CREATE OR REPLACE TRIGGER novo_func AFTER INSERT ON emp . . END; / n O evento pode conter uma, duas ou todas as três operações DML em uma única linha de comando. Exemplo: CREATE OR REPLACE TRIGGER novo_func BEFORE INSERT OR UPDATE OR DELETE ON emp . . END; / Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 320 Triggers - tipo n O tipo define quantas vezes uma trigger será executa. A trigger pode ser executada uma vez para a instrução que a disparou ou ser disparada para cada linha afetada pela instrução que a disparou. Pode ser: q q Instrução (STATEMENT) Linha (ROW) Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 321 Triggers - tipo n Quando a trigger for do tipo instrução ela será disparada uma vez para cada evento de trigger, mesmo que nenhuma linha tenha sido afetada. São úteis para aquelas trigger que eventualmente não alteram dados ou para situações onde o que queremos é uma resposta da trigger, por exemplo, em uma restrição complexa de negócio. Por DEFAULT toda trigger é deste tipo. Exemplo: CREATE OR REPLACE TRIGGER novo_func BEFORE INSERT OR UPDATE OR DELETE ON emp FOR EACH STATEMENT . . END; / Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 322 Triggers - tipo n Quando a trigger for do tipo linha, a trigger será executada toda vez que a tabela for afetada pelo evento da trigger. Se nenhuma linha for afetada a trigger não será executada. São muito úteis quando a ação da trigger depende dos dados afetados pelo evento da trigger. Exemplo: CREATE OR REPLACE TRIGGER novo_func BEFORE INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW . . END; / Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 323 Triggers - corpo n O corpo define a ação que uma trigger irá executar quando acionada. O corpo de uma trigger é composto por um bloco PL/ SQL, a chamada de uma PROCEDURE ou por um procedimento JAVA. Por definição, o tamanho de uma trigger não pode ultrapassar 32K n Como, normalmente, precisamos trabalhar com os valores antes e depois da alteração dos dados, a trigger permite que façamos referencia aos valores antes da alteração (OLD) e após a alteração (NEW) n O nome de uma trigger deve ser único dentro de um mesmo esquema Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 324 Triggers - corpo n Sintaxe básica: CREATE [OR REPLACE] TRIGGER [schema.] nome_da_trigger [BEFORE|AFTER] [DELETE|OR INSERT|OR UPDATE[OF coluna]] ON [schema.] nome_da_tabela_ou_da_view [REFERENCING [OLD [AS] OLD] [NEW [AS] NEW] [FOR EACH ROW|STATEMENT] [WHEN [condição]] BLOCO PL/SQL q Bloco PL/SQL inicia-se normalmente com BEGIN e encerra com END Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 325 Triggers – exemplos práticos n Antes de trabalharmos com triggers em tabelas, vamos entender o seu funcionamento com alguns eventos de sistema, tais como: q q q q q AFTER SERVERERROR AFTER LOGON BEFORE LOGOFF AFTER STARTUP BEFORE SHUTDOWN Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 326 Triggers – exemplos práticos n O próximo exemplo irá auditar o nome do usuário e a data e hora que ele realizou login no sistema n Portanto, vamos criar uma tabela para armazenar isto: create table sys.AUDITORIA_LOGIN ( USUARIO varchar2(30) not null, DATA_ACESSO date not null ); Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 327 Triggers – exemplos práticos n Observe como ficaria a nossa trigger de auditoria: CREATE OR REPLACE TRIGGER audita_login AFTER LOGON ON DATABASE BEGIN INSERT INTO sys.AUDITORIA_LOGIN (USUARIO, DATA_ACESSO) values (USER, SYSDATE); COMMIT; END; / Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 328 Triggers – exemplos práticos n A trigger pode apresentar problemas no momento de sua criação e exibir mensagem como a abaixo: Warning: Trigger created with compilation errors. n Logo em seguida faça o comando "SHOW ERRORS" que ele exibirá o que ocorreu: SQL> show errors; Errors for TRIGGER AUDITA_LOGIN: LINE/COL -------2/3 2/19 ERROR ----------------------------------------------PL/SQL: SQL Statement ignored PL/SQL: ORA-00942: table or view does not exist Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 329 Triggers – exemplos práticos n Caso não seja logo em seguida, você pode a qualquer momento realizar o comando "SHOW ERRORS TRIGGER nome_da_trigger": SQL> SHOW ERRORS TRIGGER audita_login; No errors. n Caso deseje saber informações sobre as triggers basta consultar a table USER_TRIGGERS ou mesmo ALL_TRIGGERS. Exemplo: SQL> select TRIGGER_NAME from USER_TRIGGERS Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 330 Triggers – exemplos práticos n Até mesmo o corpo da trigger pode ser acessado pela USER_TRIGGERS: SELECT trigger_name, trigger_type, triggering_event, table_name, referencing_names, status, trigger_body FROM user_triggers WHERE trigger_name = 'NOME_DA_TRIGGER'; Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 331 Triggers – exemplos práticos n Caso descubra que não precisa mais da trigger existe duas formas de tratar a situação: eliminá-la ou desabilitá-la. n Eliminando a trigger: q n Desabilitando: q n drop trigger NOME_DA_TRIGGER alter trigger NOME_DA_TRIGGER disable Habilitando: q alter trigger NOME_DA_TRIGGER enable Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 332 Triggers – exemplos práticos n Vamos continuar com nossos exemplos práticos. Existe uma tabela chamada RELATORIO que possui uma única linha e possui um campo QTDE_FUNCIONARIO e TOTAL_SALARIO. Observe: create table RELATORIO ( ID_RELATORIO number(1) not null, QTDE_FUNCIONARIO number(4) not null, TOTAL_SALARIO number(11,2) not null, constraint pk_relatorio primary key (ID_RELATORIO), constraint ck_unico check (ID_RELATORIO=1) ); Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 333 Triggers – exemplos práticos n Suponha que exista a seguinte tabela: create table FUNCIONARIO ( ID_FUNCIONARIO number(4) not null, NOME varchar2(60) not null, SALARIO number(8,2) not null, constraint pk_empregado primary key (ID_FUNCIONARIO) ); Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 334 Triggers – exemplos práticos n Portanto, para iniciar a tabela RELATORIO basta: insert into RELATORIO (ID_RELATORIO, QTDE_FUNCIONARIO, TOTAL_SALARIO) values (1, (select count(*) from FUNCIONARIO), (select nvl(sum(SALARIO),0) from FUNCIONARIO) ); n Como manter este relatório sempre atualizado sem ter que ficar sempre contando todos os funcionários e somando todos os salários? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 335 Triggers – exemplos práticos n Manteremos a quantidade de funcionários sempre atualizada se: q q n ao inserir mais um funcionário, incrementarmos a quantidade ao excluir um funcionário, decrementarmos a quantidade Manteremos o total de salário sempre atualizado se: q q q ao inserir mais um funcionário, somarmos o seu salário ao excluir um funcionário, diminuirmos o seu salário ao modificar o salário de um funcionário, diminuirmos seu antigo salário e somarmos o seu novo salário Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 336 Triggers – exemplos práticos n Manteremos a quantidade de funcionários sempre atualizada se: q q ao inserir mais um funcionário, incrementarmos a quantidade ao excluir um funcionário, decrementarmos a quantidade create or replace trigger TRIGGER_QTDE_FUNC AFTER INSERT OR DELETE ON FUNCIONARIO FOR EACH ROW begin IF INSERTING THEN update RELATORIO set QTDE_FUNCIONARIO = QTDE_FUNCIONARIO+1; ELSIF DELETING THEN update RELATORIO set QTDE_FUNCIONARIO = QTDE_FUNCIONARIO-1; END IF; end; / Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 337 Triggers – exemplos práticos n Manteremos o total de salário sempre atualizado se: create or replace trigger TRIGGER_TOTAL_SALARIO AFTER INSERT OR DELETE OR UPDATE OF SALARIO ON FUNCIONARIO FOR EACH ROW begin IF INSERTING THEN update RELATORIO set TOTAL_SALARIO = TOTAL_SALARIO + :NEW.SALARIO; ELSIF DELETING THEN update RELATORIO set TOTAL_SALARIO = TOTAL_SALARIO - :OLD.SALARIO; ELSIF UPDATING THEN update RELATORIO set TOTAL_SALARIO = TOTAL_SALARIO :OLD.SALARIO + :NEW.SALARIO; END IF; end; / Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 338 Triggers – exemplos práticos n Teste: [1 0 0 ] (select * from RELATORIO) insert into FUNCIONARIO values (1, 'A', 43.9); [1 1 43.9 ] insert into FUNCIONARIO values (2, 'B', 106.1); [1 2 150.0 ] insert into FUNCIONARIO values (3, 'C', 40.0); [1 3 190.0 ] update FUNCIONARIO set SALARIO = 116.1 where ID_FUNCIONARIO = 2; [1 3 200.0 ] delete from FUNCIONARIO where ID_FUNCIONARIO = 3; [1 2 160.0 ] Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 339 Triggers – exemplos práticos n Solucionando herança por disjunção: n Um funcionário é peão ou chefe. Como impedir que seja criado um mesmo funcionário como peão e como chefe? Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 340 Triggers – exemplos práticos n Restrição por chave estrangeira não é suficiente: create table FUNCIONARIO ( ID_FUNCIONARIO number(3) not null, NOME varchar2(60) not null, constraint PK_FUNCIONARIO primary key (ID_FUNCIONARIO) ); create table CHEFE ( ID_CHEFE number(3) not null, SALARIO number(8,2) not null, constraint PK_CHEFE primary key (ID_CHEFE), constraint fk_chefe FOREIGN KEY (ID_CHEFE) references FUNCIONARIO(ID_FUNCIONARIO) ); create table PEAO ( ID_PEAO number(3) not null, CARGA_TRABALHO number(3) not null, constraint PK_PEAO primary key (ID_PEAO), constraint fk_peao FOREIGN KEY (ID_PEAO) references FUNCIONARIO(ID_FUNCIONARIO) ); Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 341 Triggers – exemplos práticos n Manteremos a integridade de disjunção se: q Ao vincular um funcionário como CHEFE, garantir que ele não seja PEÃO create or replace trigger TG_CHEFE BEFORE INSERT OR UPDATE OF ID_CHEFE ON CHEFE FOR EACH ROW declare contador NUMBER; begin select count(ID_PEAO) into contador from PEAO where ID_PEAO = :NEW.ID_CHEFE; IF contador != 0 THEN RAISE_APPLICATION_ERROR(-20515,'JA É PEÃO!'); END IF; end; / Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 342 Triggers – exemplos práticos n Manteremos a integridade de disjunção se: q Ao vincular um funcionário como PEÃO, garantir que ele não seja CHEFE create or replace trigger TG_PEAO BEFORE INSERT OR UPDATE OF ID_PEAO ON PEAO FOR EACH ROW declare contador NUMBER; begin select count(ID_CHEFE) into contador from CHEFE where ID_CHEFE = :NEW.ID_PEAO; IF contador != 0 THEN RAISE_APPLICATION_ERROR(-20515,'JA É CHEFE!'); END IF; end; / Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 343 Referência Bibliográfica n DAWES, Chip; THOMAS, Biju. OCA/OCP: Introduction to Oracle9i™ SQL. São Francisco: Sibex, 2002. n GOYA, Milton. Oracle – Trigger. Disponível em: <http:// www.linhadecodigo.com.br/Artigo.aspx?id=322>. Acesso em: 09 set. 2008. n GURSAHANI, Ajay. Materialized Views in Oracle. Disponível em: <http:// www.databasejournal.com/features/oracle/article.php/2192071>. Acesso em: 29 maio 2008. n ORACLE. Oracle Database Sample Schemas. Disponível em: <http:// download.oracle.com/docs/cd/B12037_01/server.101/b10771.pdf>. Acesso em: 22 fev. 2008. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 344 Referência Bibliográfica n Oracle PL/SQL Tutorial: Index. Disponível em: <http://www.java2s.com/ Tutorial/Oracle/0180__Index/0020__Create-Index.htm>. Acesso em: 06 jun. 2008. n Oracle Roles. Disponível em: <http://www.psoug.org/reference/roles.html>. Acesso em: 13 jun. 2008. n SILBERSCHATZ. A.; KORTH, H. F.; SUDARSHAN, S. Sistemas de bancos de dados. Tradução de Daniel Vieira. Rio de Janeiro: Editora Campus, 2006. Título original: Database system concepts. 5 ed. n TEOREY, T; LIGHTSTONE, S; NADEAU, T. Projeto e Modelagem de banco de dados. Rio de Janeiro: Elsevier, 2007. Ricardo Terra (rterrabh [at] gmail.com) Apostila Oracle Outubro, 2008 345