Oracle Database 11g - Apostila by Carlos Washington

Oracle Database Para Administradores de Dados e Desenvolvedores Washington Menezes â&#x20AC;&#x201C; OCP

Sumário

Sumário Sumário .....................................................................................................................................................2 Capítulo 1 - Instalação ..............................................................................................................................5 Download do Oracle .............................................................................................................................5 Pré-requisitos de hardware para a instalação ........................................................................................6 Descompactando o arquivo / iniciando a instalação .............................................................................6 O assistente de instalação universal ......................................................................................................7 ORACLE_BASE e ORACLE_HOME .................................................................................................9 A arquitetura do SGBD Oracle ...........................................................................................................10 Uma Instância Oracle → Database Service & Listener .....................................................................10 Criando o Listener ............................................................................................................................... 11 Criando o Database Service ................................................................................................................12 Testando a Criação do Banco de Dados ..............................................................................................21 O Enterprise Manager (dbconsole) .....................................................................................................22 Habilitando o usuário HR ...................................................................................................................24 Exercício de revisão ............................................................................................................................26 RESPOSTAS .......................................................................................................................................28 Capítulo 2 – Visão Geral do Oracle Database .........................................................................................29 SQL e PL/SQL ....................................................................................................................................29 O que é um banco de dados no Oracle ................................................................................................30 Schemas e Tablespaces .......................................................................................................................30 O Schema de exemplo HR ..................................................................................................................32 O Oracle Net Client.............................................................................................................................32 Ferramentas do Desenvolvedor e do Administrador de Dados ...........................................................39 Exercício de revisão ............................................................................................................................43 RESPOSTAS .......................................................................................................................................45 Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários ......................................................................46 Manipulando usuários e controlando seus schemas: bancos de dados ...............................................46 Criando e controlando usuários...........................................................................................................47 As Tablespaces USERS e TEMP ........................................................................................................47 Privilégios mínimos para que um usuário se conecte ao Oracle e possa criar objetos .......................48 Removendo usuários ...........................................................................................................................50 Concedendo privilégios de acesso aos objetos de um schema para outro schema .............................51 Sinônimos............................................................................................................................................54 Sinônimos privados e sinônimos públicos ..........................................................................................54 Detalhes sobre o comando GRANT....................................................................................................56 Papéis (ROLES) ..................................................................................................................................56 Exercício de revisão ............................................................................................................................61 RESPOSTAS .......................................................................................................................................63 Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) .....................64 DDL – Data Definition Language – Elementos básicos .....................................................................64 Tipos básicos de dados ........................................................................................................................64 Funções para conversão de tipos de dados..........................................................................................72 DML – Data Manipulation Language – Elementos básicos ...............................................................72 Consultando e manipulando dados .....................................................................................................73 2

Sumário Consultando dados: o comando SELECT ...........................................................................................76 Restringindo o resultado, conforme um critério estabelecido: a cláusula WHERE. ..........................78 Usando funções “built-in” do Oracle ..................................................................................................79 Usando funções condicionais. .............................................................................................................86 DDL – Data Definition Language – Elementos adicionais .................................................................87 Exercício de revisão ............................................................................................................................91 RESPOSTAS .......................................................................................................................................93 Capítulo 5 – Oracle PL/SQL – Stored Procedures ..................................................................................94 Visão geral da PL/SQL – Declaração de PROCEDURES ..................................................................94 Estruturas de controle de fluxo .........................................................................................................101 Boas práticas I - subrotinas locais .....................................................................................................104 Exercício de revisão ..........................................................................................................................108 RESPOSTAS ..................................................................................................................................... 110 Capítulo 6 – Oracle PL/SQL – Packages .............................................................................................. 111 Estrutura básica de um Package ........................................................................................................ 111 Criando um package simples ............................................................................................................ 112 Parâmetros default............................................................................................................................. 118 Código privado ao package body ...................................................................................................... 119 Exercício de revisão ..........................................................................................................................121 RESPOSTAS .....................................................................................................................................123 Capítulo 7 – Oracle PL/SQL – Cursores ...............................................................................................124 Cursores implícitos ...........................................................................................................................124 Cursores explícitos ............................................................................................................................125 Boas práticas II – Consultas “enxutas” .............................................................................................129 Exercício de revisão ..........................................................................................................................131 RESPOSTAS .....................................................................................................................................133 Capítulo 8 – Oracle PL/SQL – Parâmetros ...........................................................................................134 Tipos de parâmetro ............................................................................................................................134 IN, OUT, IN OUT .............................................................................................................................134 Parâmetros de modo IN.....................................................................................................................135 Parâmetros de modo IN OUT ...........................................................................................................136 Parâmetros de modo OUT.................................................................................................................137 Exercício de revisão ..........................................................................................................................139 RESPOSTAS .....................................................................................................................................141 Capítulo 9 – Oracle PL/SQL – Retorno de Parâmetros ........................................................................142 Retornando Cursores para uma aplicação .........................................................................................142 Usando o SQL*Plus para passar e receber parâmetros .....................................................................145 Bind Variables ...................................................................................................................................145 Executando um programa via SQL*Plus ..........................................................................................146 Exercício de revisão ..........................................................................................................................149 RESPOSTAS .....................................................................................................................................151 Capítulo 10 – O Administrador de Dados .............................................................................................152 Estruturas lógicas X Estruturas físicas ..............................................................................................152 O papel de um Administrador de Dados ...........................................................................................152 Criação de Tabelas e Índices .............................................................................................................153 Criando uma tablespace para índices ................................................................................................155 Migrando os índices para a nova tablespace .....................................................................................158 Consultando o Dicionário de Dados .................................................................................................161 O dicionário de dados na prática .......................................................................................................165 3

Sumário Exercício de revisão ..........................................................................................................................167 RESPOSTAS .....................................................................................................................................169 ...............................................................................................................................................................170

Capítulo 1 - Instalação

Capítulo 1 - Instalação O Oracle database pode rodar em praticamente todos os sistemas operacionais em uso no mercado. Existem versões para Windows a partir do 2000 server, Linux, Solaris, AIX, HPUX Itanium e HP-UX RISC. Nesta apostila, por questões didáticas, iremos usar a versão para o sistema operacional Windows 32 bits – Windows XP. Desta forma, o aluno poderá fazer a instalação em sua casa, para acompanhar os exercícios. Se sua versão do Windows for diferente, basta fazer o download da versão mais apropriada, desde que seja a Oracle 11g Release 1 (11.1.0.6.0). Download do Oracle A página oficial para downloads do Sistema de Gerenciamento de Banco de Dados (SGBD) da Oracle pode ser encontrada no seguinte link: http://www.oracle.com/technology/software/products/database/index.html

E o link para o Oracle 11g Release 1 (11.1.0.6.0), versão Win32, é o seguinte: http://www.oracle.com/technology/software/products/database/oracle11g/111060_win32soft.html

Na figura abaixo, detalhes sobre a página de download.

Capítulo 1 - Instalação OBS.: Para fazer o download, é necessário que você tenha uma conta na OTN-Oracle Technology Network. Caso você ainda não tenha uma conta na OTN, será solicitado que você se inscreva durante o processo de download. Não se preocupe, a inscrição é gratuita e dura um ou dois minutos. Após selecionar Accept, o link de download será ativado. Será solicitada uma senha, que é a senha da sua conta na OTN. Pré-requisitos de hardware para a instalação Apesar de ser uma ferramenta extremamente poderosa, o Oracle pode ser instalado numa máquina razoavelmente “modesta”. Na figura abaixo, veja os pré-requisitos de hardware mínimos para a instalação do Oracle database no Windows.

Descompactando o arquivo / iniciando a instalação Após o download, faça a descompactação do arquivo em uma pasta temporária à sua escolha. Se preferir, você pode gravar o conteúdo da pasta onde você descompactou o arquivo para um DVD e iniciar a instalação a partir dele (Autorun). Mas nada impede que você faça a instalação diretamente da pasta. Basta executar o arquivo setup.exe.

Capítulo 1 - Instalação Na figura a seguir, como iniciamos o processo de instalação a partir da pasta temporária.

O assistente de instalação universal A seguir, uma série de figuras com o passo a passo da instalação:

Capítulo 1 - Instalação Selecione a Instalação Básica e não deixe de conferir a Localização do Oracle Base e a Localização do Oracle Home. É importante que fique em C:\oracle

e em

C:\oracle\product\11.1.0\db_1, respectivamente. Desative a opção Criar Banco de Dados Inicial. Embora seja possível criar um banco de dados padrão ao mesmo tempo em que instala o Oracle, convém deixar para depois da instalação. Dividindo o processo em duas etapas, será possível identificar mais facilmente eventuais problemas de instalação. Mesmo porque, depois de instalado o SGBD, a criação de um banco de dados é muito facilitada, através da ferramenta DBCA-DataBase Configuration Assistant. Clicando em próximo, e após uma pequena preparação, o assistente de instalação exibirá a seguinte tela:

Muito provavelmente você verá um tipo qualquer de advertência. Uma advertência não impede que você prossiga a instalação, mas é preciso selecioná-la e ler o que estiver escrito no painel de texto. No caso acima, o assistente está advertindo que o sistema operacional está configurado para usar DHCP, o que pode ser um problema, caso o IP mude a cada 8

Capítulo 1 - Instalação login. Numa situação real, o servidor teria um IP fixo e esta advertência não iria aparecer. Neste caso, basta selecionar o Check-Box junto da palavra Advertência, para seguir adiante. Isto é uma forma de dizer: “eu sei o que estou fazendo!” Clicando em próximo mais uma vez, será apresentada uma tela de resumo. Depois de uma conferida no resumo, basta clicar no botão instalar e o assistente dará início à instalação. A seguir, a tela que será exibida durante o processo:

ORACLE_BASE e ORACLE_HOME Enquanto o assistente faz a instalação, aproveite para ler uma coisa muito importante a respeito de um dado informado anteriormente: a Localização do Oracle Base e a Localização do Oracle Home. A Oracle procura padronizar os caminhos onde seus produtos são instalados. Isto busca facilitar o trabalho das pessoas que fazem manutenção. Se todos seguirem um padrão, não será necessário perder tempo tentando “adivinhar” onde alguém instalou um produto. ORACLE_BASE é uma variável de ambiente que identifica onde os produtos Oracle estão instalados em um sistema. No Windows, a variável ORACLE_BASE deve apontar sempre 9

Capítulo 1 - Instalação para C:\Oracle. ORACLE_HOME é uma variável de ambiente que aponta para onde o SGBD da Oracle está instalado. Para o Oracle 11g, no Windows, esta variável deve apontar para ORACLE_BASE\ product\11.1.0\db_1. Pode parecer estranho, mas lembre-se que o SGBD da Oracle é apenas um de seus vários produtos relacionados a bancos de dados e ORACLE_BASE aponta para o diretório raiz de seus produtos. Ao final da instalação, o assistente mostrará apenas a informação “Fim da Instalação”. Basta fechar o assistente, neste momento. A arquitetura do SGBD Oracle O que nós acabamos de fazer foi instalar o SGBD-Sistema de Gerenciamento de Banco de Dados da Oracle. Nós ainda não temos um banco de dados da forma como as pessoas entendem um banco de dados, principalmente aqueles que estão acostumados com outros tipos de SGBD! No mundo Oracle, as coisas são muito grandes! Um servidor de banco de dados para a Oracle se chama Instância. E em uma mesma máquina você pode ter várias instâncias. Estas instâncias, para efeitos práticos e de compreensão, são o que as pessoas normalmente entendem por um “servidor de banco de dados”. Para que um servidor de banco de dados baseado em Oracle possa funcionar perfeitamente, seja qual for a versão, é preciso que ao menos dois componentes fundamentais estejam funcionando: o Database Service e o Listener. Uma Instância Oracle → Database Service & Listener O Database Service é o componente principal. É o que nós chamamos de uma Instância. Depois que você instala um servidor de banco de dados Oracle, é preciso criar uma instância de banco de dados. Normalmente, durante o processo de instalação lhe é dada a opção de criar esta instância ou deixar para depois. Se tivéssemos escolhido criar a instância durante a instalação, perderíamos a oportunidade de compreender melhor o funcionamento do Oracle. Por isto deixamos para criar depois.

Capítulo 1 - Instalação Criando o Listener O Listener é um serviço que permite a comunicação entre o Database Server e os clientes. Os clientes a que me refiro são os usuários do banco de dados ou as aplicações que acessam o mesmo.

Sem o Listener, não há comunicação com o servidor de banco de dados (Database Server), mesmo que o Database Service esteja funcionando perfeitamente. Para criar um Listener, usamos o utilitário Assistente de Configuração de Rede. Para ter acesso a este e demais utilitários, basta clicar no botão iniciar e procurar pelo grupo de aplicativos configurados pelo assistente de instalação. Veja a figura abaixo.

Selecione Assistente de Configuração de Rede no menu iniciar, para que possamos criar o Listener. Feito isto, o assistente exibirá sua tela inicial com as opções disponíveis.

Capítulo 1 - Instalação

Para completar o processo, faça o seguinte: 1. Escolha Configuração do listener e clique em Próximo; 2. Aceite ou selecione Adicionar e clique em Próximo; 3. Aceite o nome default (LISTENER) e clique em Próximo; 4. Aceite o protocolo selecionado (TCP) e clique em Próximo; 5. Aceite a porta padrão (1521) e clique em Próximo; 6. Responda não à pergunta “Deseja configurar outro listener?” e clique em Próximo; 7. Ao ver a mensagem “Configuração do listener concluída”, clique em Próximo e depois em Finalizar. Pronto, o Listener está criado! Criando o Database Service Agora que criamos o Listener, vamos criar o Database Service. Para isto, execute o Assistente de Configuração de Banco de Dados. Veja a seguir sua tela inicial.

Capítulo 1 - Instalação

Clicando em Próximo, passamos à tela seguinte, com as opções disponíveis, conforme o contexto. Neste momento, como não há nenhum banco de dados, as opções Configurar e Deletar um banco de dados estão desabilitadas. Selecione Criar um Banco de Dados e clique em Próximo. Veja abaixo a tela com os Gabaritos de banco de dados disponíveis.

Capítulo 1 - Instalação Selecione o Gabarito “Finalidade Geral ou Processamento de Transações” e clique em Próximo.

Neste momento, o assistente pede que você informe o “Nome do Banco de Dados Global” e o SID. Veja na figura anterior as informações que você deve digitar. Vale a pena clicar no botão Ajuda e ler as informações que serão apresentadas. Vamos usar ORCL nos dois campos. Dentro das regras de nomeação documentadas na ajuda, você pode digitar o nome que achar mais conveniente para os teus propósitos. Mas optamos por usar ORCL, pois são os identificadores usados em toda a documentação e exemplos oferecidos pela Oracle. O SID (System IDentifier) é a identificação da Instância que estamos criando. Lembre-se que em uma mesma máquina é possível criar várias instâncias Oracle. Daí a importância de identificar cada uma delas de forma única e inequívoca. Opções de Gerenciamento: Agora clique em Próximo e, na tela seguinte, certifique-se de que as opções “Configurar o Enterprise Manager” e “Configurar o Controle de Banco de Dados para obter o gerenciamento local” estão selecionadas. Credenciais do Banco de Dados: Clique em Próximo, para informar as senhas de 14

Capítulo 1 - Instalação acesso à instância sendo criada. Selecione “Usar a Mesma Senha Administrativa para Todas as Contas” e informe uma senha. Não esqueça esta senha! Depois será possível você criar senhas individuais para cada conta em uma instância Oracle. A maneira adotada aqui é apenas para simplificar o processo. ATENÇÃO: Duas contas são fundamentais em uma instância Oracle: SYSTEM e SYS. Eles são os SYSDBA, ou os administradores do Banco de Dados. Tanto o usuário system quanto o usuário sys podem se conectar ao Oracle com a prerrogativa de sysdba. Esta prerrogativa dá poderes absolutos sobre o banco de dados, com ligeiras variações. Certas tarefas podem ser executadas apenas pelo usuário system; outras, somente pelo usuário sys. Mas para controlar a instância do banco de dados, você pode usar tanto um quanto o outro. SEGURANÇA: Estas duas contas devem ser usadas apenas quando absolutamente necessário e jamais criem suas próprias tabelas nestas contas! Feitas as ressalvas e advertências, informe uma senha, confirme e clique em Próximo. Opções de Armazenamento: Na tela a seguir, são exibidas as opções de armazenamento para os arquivos “físicos” da instância Oracle.

Este é um assunto vasto! Mais uma vez, você pode clicar no botão Ajuda e ler as 15

Capítulo 1 - Instalação informações que serão apresentadas. Para os nossos propósitos, selecione “Sistema de Arquivos” e clique em Próximo. Localização dos Arquivos: Nesta etapa, selecione a opção “Usar Localizações dos Arquivos do Banco de Dados do Modelo”. Antes de prosseguir, clique no botão “Variáveis de Localização de Arquivo”. Abaixo, a tela que será exibida:

Está vendo as variáveis ORACLE_BASE e ORACLE_HOME que falamos anteriormente? Observe o valor indicado por elas. Dentro destas pastas é que serão criados os “DataFiles” da instância. Agora clique em Próximo, para prosseguirmos. Configuração de Recuperação: Nesta etapa, selecione a opção “Especifique a Área de Recuperação Flash” e não selecione “Ativar Arquivamento”. Isto tem a ver com o sistema de Backup/Restore do banco de dados e iremos abordar o tema em um capítulo específico. Clique em Próximo. Exemplos de Schema: Selecione a opção “Exemplos de Esquema”. Como sempre, você pode clicar no botão Ajuda, caso deseja mais informações. Agora clique em Próximo. Parâmetros de Inicialização: Chegamos numa etapa importante! Até este momento, evitei colocar todas as figuras do processos de instalação, para não tomar muito espaço da apostila. Mas como as informações desta etapa são muito relevantes, vou colocar todas as figuras e tecer alguns comentários.

Capítulo 1 - Instalação 

A memória

A memória é um elemento crucial em qualquer sistema. E num SGBD então nem se fala. Se a memória não estiver bem dimensionada, o SGBD pode ficar muito lento e em alguns casos até mesmo “cair”. Veja na figura abaixo as opções de memória de uma instância Oracle.

Tipicamente, o assistente configura a instância para usar 40% da memória física. Em um sistema com 1GB de RAM, por exemplo, isto equivale a 409MB, no total. O fator mais crucial é a SGA – System Global Area. A seguir, uma figura para mostrar a importância e o significado da SGA.

Capítulo 1 - Instalação

É na SGA onde todos os processos do banco de dados são alocados. Daí ser crucial um bom dimensionamento da SGA. Boa parte do tempo de um DBA (DataBase Administrator) consistia em monitorar a SGA para que a instância permanecesse sempre ativa, dividindo o espaço da memória entre a SGA e outra área chamada de PGA-Program or Process Global Area. Apesar da advertência, não há motivos para muita preocupação, pois a partir da versão 10g a Oracle introduziu muitas facilidades de manutenção da SGA, inclusive um recurso chamado Gerenciamento Automático de Memória. Basta você estabelecer um tamanho máximo para a SGA que o próprio Oracle se encarregará de fazer a divisão mais conveniente entre as partes da memória. 

Dimensionamento

Nesta etapa você informa o número de processos de usuários que podem ser executados simultaneamente em uma instância Oracle. Se um sistema precisar que até 100 usuários trabalhem nele simultaneamente, este é o número que deve ser informado nesta etapa. Mais uma vez, a flexibilidade do Oracle irá permitir que você altere esta propriedade depois de instalado. Mas ela deve ser calculada corretamente. 

Conjunto de caracteres

O Oracle é o SGBD mais usado no mundo. Existem versões para todos os continentes e 18

Capítulo 1 - Instalação praticamente todos os países do mundo. Não há necessidade de qualquer restrição quanto ao uso de caracteres acentuados, símbolos monetários (e outros) num banco de dados. Basta você configurar adequadamente o conjunto de caracteres.

O assistente de criação de banco de dados geralmente detecta as configurações regionais do sistema operacional onde o banco de dados está sendo criado e a opção “Utilizar o default” é uma boa pedida. Apenas certifique-se de que o conjunto de caracteres selecionado seja o WE8MSWIN1252, como mostrado na figura acima. 

Definições de segurança

Use Esta página para proteger suas definições. Nela você pode selecionar as definições de segurança default do Oracle Database 11g ou reverter para uma configuração anterior do Oracle Database 11g. Aceite o recomendado e clique em Próximo. 

Tarefas de Manutenção Automáticas

Use esta página para gerenciar tarefas de manutenção automaticamente, como coleta de

Capítulo 1 - Instalação estatísticas do otimizador e relatórios pró-ativos do supervisor. Este recurso facilita o gerenciamento predefinido da distribuição de recursos (recursos de CPU e entrada-saída) entre várias tarefas de manutenção do banco de dados, como Coleta de Estatísticas Automáticas do Otimizador e Supervisor Automático do Segmento. Ele garante que a atividade do usuário final não seja impactada durante a operação de manutenção e que a atividade obtenha recursos suficientes para executar o trabalho até o final. Mantenha “Ativar tarefas de manutenção automáticas” e clique em Próximo.

Armazenamento do Banco de Dados: nesta etapa você poderá ver onde o assistente criará os “Data files” e adicionar parâmetros de armazenamento, o que não é o caso agora. Clique em Próximo. Opções de Criação: nesta etapa semifinal de nosso processo de criação do banco de dados são oferecidas algumas opções. 3 - Você pode salvar todas as configurações em forma de Scripts. Assim você pode editar os arquivos, para uma instalação mais avançada; 2 – Você pode salvar todas as tuas configurações na forma de um Gabarito, para futuras criações de outras instâncias; 1 – Criar o banco de dados diretamente a partir do assistente. Nossa opção será a opção 1. Agora clique no botão Finalizar. A tela exibida após clicar no botão Finalizar mostra um resumo das opções selecionadas e/ou definidas. Basta clicar em Ok, para iniciar a criação do banco de dados. O processo de criação é um pouco demorado (mais ou menos dez minutos). Ao final do processo, será exibida uma tela como a mostrada a seguir.

Capítulo 1 - Instalação

Testando a Criação do Banco de Dados Após a criação da instância Oracle, vamos verificar se as coisas estão em seu devido lugar. 

Serviços

Quando criamos o Listener e o Banco de Dados, os assistentes criaram alguns serviços no Windows que são essenciais para o funcionamento do SGBD como um todo. Estes serviços são: 1. OracleOraDb11g_home1TNSListener : O Listener; 2. OracleServiceORCL: O Database Service (a instância); 3. OracleDBConsoleORCL : O “servidor” do Enterprise Manager (ferramenta de administração via browser); 4. OracleJobSchedulerORCL : Uma espécie de “agendador de tarefas” da instância. Para que uma instância funcione efetivamente, é preciso que ao menos os dois primeiros

Capítulo 1 - Instalação serviços estejam ativos. Certifique-se de que os serviços citados estão todos ativos. Abaixo uma tela mostrando o gerenciador de serviços do windows.

O Enterprise Manager (dbconsole) O Enterprise Manager (EM) é a ferramenta para gerenciamento de uma instância Oracle, através de um browse. Para ter acesso ao EM, basta abrir um browser de sua preferência e digitar a seguinte URL na barra de endereços: https://host:1158/em Onde host é o endereço na rede do servidor ou (máquina) onde o Oracle foi instalado. Se você estiver trabalhando a partir da própria máquina onde o Oracle foi instalado (situação mais provável), basta digitar: https://localhost:1158/em O browse deverá exibir a seguinte tela:

Capítulo 1 - Instalação

Para o nosso teste, vamos usar o usuário SYSTEM e a senha que você definiu durante o processo de criação da instância. OBS.: Constantemente temos usado o termo instância, ao invés de Banco de Dados. Em momento mais oportuno iremos esclarecer melhor este aspecto. Por enquanto, acredite que estamos querendo fazer com que você se familiarize com a nomenclatura Oracle, muito diferente da usada por outros SGBD. Se tudo correu bem, a conexão será realizada com sucesso e o EM mostra a sua tela principal. A tela home exibe sete abas, que são: Home, Desempenho, Disponibilidade, Servidor, Esquema, Movimentação de Dados e Software e Suporte.

Capítulo 1 - Instalação Na figura a seguir, mostramos a tela principal do EM.

Habilitando o usuário HR Nosso primeiro exercício será utilizar o EM para habilitar o usuário HR. O usuário HR é um dos esquemas (schemas) de exemplo que acompanham o Oracle. Como ele é muito utilizado na documentação oficial e será utilizado também em nossos exemplos, vamos habilitar o usuário HR. Clique na aba Servidor e depois no link Usuários, na seção Segurança. O EM deverá listar todos os usuários da instância ORCL. Localize o usuário HR, seguindo a coluna Nome do Usuário e clique no link HR. O EM então mostrará os dados do usuário HR. Clique no botão Editar, para que possamos ativá-lo.

Capítulo 1 - Instalação A seguir, uma figura mostrando a tela de edição do usuário HR.

Informe uma senha e confirme, clique em ( * ) Desbloqueado e depois clique no botão Aplicar, para ativar o usuário HR.

Capítulo 1 - Instalação Exercício de revisão Responda as questões a seguir: 1) Que considerações devem ser feitas antes de uma instalação do Oracle? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

2) Para que serve a tela de "Verificações de Pré-requisitos Específicos do Produto"? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 3) Para que servem as variáveis de ambiente ORACLE_BASE e ORACLE_HOME? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 4) Quais são os componentes mínimos que precisam estar operacionais para que se possa dizer que há um servidor de banco de dados Oracle funcionando? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 5) Que nome também se dá ao Database service, quando ele está "no ar"? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 6) O que é SID e para que serve? 26

Capítulo 1 - Instalação ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 7) É possível existir mais de uma instância em uma mesma máquina servidora? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 8) Para que servem as contas SYS e SYSTEM, criadas automaticamente quando você cria um Database service? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 9) O que é a SGA e para que serve? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 10) Para que serve o Enterprise Manager e como ter acesso à ele no servidor? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Capítulo 1 - Instalação RESPOSTAS 1) Em qual SO será instalado; quais as configurações mínimas de hardware e software. 2) Para verificar se o ambiente atende a todos os requisitos mínimos para a instalação dos produtos selecionados. 3) ORACLE_BASE aponta para o diretório base onde todas as aplicações oracle são instaladas; ORACLE_HOME aponta para o diretório base do SGBD Oracle. 4) Um Database Service e um Listener 5) Instância 6) SID significa System Identifier e serve para identificar uma instância. 7) Sim 8) Estas são as contas especiais para a administração do banco de dados como um todo. 9) A SGA é a System Global Area e é nela onde todos os processos do banco de dados são alocados. 10) O Enterprise Manager serve para gerenciar uma instância Oracle e podemos acessálo pelo browser, através da URL https://localhost:1158/em

Capítulo 2 – Visão Geral do Oracle Database

Capítulo 2 – Visão Geral do Oracle Database Neste capítulo apresentaremos uma visão geral do Oracle Database. Sem sombra de dúvidas, o Oracle é o SGBD que mais se aproxima da teoria original de bancos de dados relacionais, introduzida por Edgar Frank Codd. Iremos ver neste capítulo ferramentas e linguagens que podem ser utilizadas por um desenvolvedor ou administrador de dados para trabalhar com o Oracle. SQL e PL/SQL Como todo SGBD que se preze, o Oracle é fundamentalmente operado via SQL. SQL vem de Structured Query Language, que nada mais é que uma linguagem voltada a bancos de dados. Basicamente, os comandos da SQL são divididos nas categorias a seguir: 

DML - Linguagem de Manipulação de Dados



DDL - Linguagem de Definição de Dados



DCL - Linguagem de Controle de Dados



DTL - Linguagem de Transação de Dados



DQL - Linguagem de Consulta de Dados

As mais conhecidas e utilizadas são DML, DDL e DCL. A SQL provê comandos para uma variedade de tarefas, entre elas: 

Consulta a dados (DML)



Inserção, atualização e exclusão de linhas em uma tabela (DML)



Criação, substituição, alteração e exclusão de objetos (DDL)



Controle de acesso ao banco de dados e seus objetos (DCL)



Garantir a consistência e a integridade do banco de dados (DTL)

PL/SQL é a linguagem de programação para o banco de dados Oracle. Equivale à TSQL, da Microsoft e a tantas outras linguagens embutidas em bancos de dados. Através da PL/SQL, um desenvolvedor pode criar procedimentos e funções rodando diretamente no banco de dados. A PL/SQL também é a linguagem usada em Triggers: procedimentos especiais que são “disparados” em determinadas circunstâncias.

Capítulo 2 – Visão Geral do Oracle Database O que é um banco de dados no Oracle No capítulo anterior usamos constantemente o termo instância ao invés de banco de dados. Para os propósitos deste curso, uma instância é um conjunto de processos e serviços em execução, destinados a realizar tarefas pertinentes a um Servidor de Banco de Dados. Esta instância recebe um nome, que é usado como SID (System IDentifier); possui um conjunto de Processos que correspondem ao gerenciamento da instância; e possui um Listener, que é o canal de comunicação entre os Processos do servidor e os Clientes do banco de dados. Um banco de dados é uma coleção de dados tratados como uma unidade. O propósito de um banco de dados é armazenar e recuperar informações relacionadas. Um servidor de banco de dados é a chave para resolver problemas de gerenciamento de informação. Schemas e Tablespaces O Oracle trabalha com o conceito de “Schemas”. Numa tradução livre, “Schema” significa esquema. Você deve lembrar que quando criamos o banco de dados, selecionamos a opção “Exemplos de Esquema”. Ao fazermos isto, o assistente criou bancos de dados de exemplo, ou seja, schemas de exemplo. Um Schema é uma coleção de estruturas lógicas de dados, ou objetos. Cada usuário possui um único Schema. Enfim: quando você cria um usuário, o Oracle cria um schema para ele, com o mesmo nome do usuário. Acessando o Oracle com um usuário qualquer, você está acessando o schema do usuário e conseqüentemente o banco de dados do usuário. Os objetos de um Schema podem ser criados e manipulados com SQL (Structured Query Language) e incluem os seguintes tipos de objeto: 

Clusters



Database links



Database triggers



Dimensions



External procedure libraries



Indexes and index types



Java classes, Java resources, and Java sources

Capítulo 2 – Visão Geral do Oracle Database 

Materialized views and materialized view logs



Object tables, object types, and object views



Operators



Sequences



Stored functions, procedures, and packages



Synonyms



Tables and index-organized tables



Views

Na figura abaixo, uma representação de objetos / schemas, tablespaces e datafiles.

Outros tipos de objetos também podem ser armazenados no banco de dados, criados e manipulados via SQL, mas não estão contidos num Schema: 

Contexts



Directories



Profiles



Roles



Tablespaces

Capítulo 2 – Visão Geral do Oracle Database 

Users

Os objetos de um Schema são estruturas lógicas. Eles não têm uma correspondência um para um com arquivos físicos (datafiles) em um disco rígido. Ao invés disto, o Oracle armazena logicamente os objetos de um Schema dentro de uma Tablespace, como mostrado na figura anterior. As Tablespaces é que têm relação com arquivos físicos. Mas isto não é importante do ponto de vista do armazenamento lógico dos objetos de um esquema. Não há um relacionamento de exclusividade entre Schemas e Tablespaces: uma Tablespace pode conter objetos de diferentes Schemas, e os objetos de um Schema podem ser contidos em diferentes Tablespaces. Tablespaces é um assunto de interesse de um DBA e não de um desenvolvedor ou de um administrador de dados. Por isto não vamos nos aprofundar neste assunto nesta apostila. Basta saber que quando você cria um usuário, duas tablespaces estão envolvidas: a default tablespace e a temporary tablespace. No Oracle, estas tablespaces são USERS e TEMP, respectivamente. A default tablespace é a tablespace onde o Oracle armazena as estruturas lógicas que um usuário cria em seu Schema, caso não seja informada outra. A temporary tablespace é a tablespace onde o Oracle armazena temporariamente estruturas lógicas necessárias quando se está manipulando dados. Em determinadas situações de consultas muito complexas, por exemplo, o Oracle precisa criar estruturas lógicas temporariamente, para somente depois dar o resultado. É na temporary tablespace onde ele armazena tais estruturas. O Schema de exemplo HR Fechando este tópico, o usuário HR é na verdade um banco de dados! HR vem de Human Resources (Recursos Humanos). Em seu schema existem as tabelas típicas de um sistema de recursos humanos. O Oracle Net Client Até o momento vimos trabalhando no Oracle diretamente da máquina onde ele foi instalado. Em uma situação real, os trabalhos são feitos a partir de uma estação de trabalho. Para que seja possível acessar o Oracle a partir de uma estação de trabalho, é preciso instalar um Cliente Oracle, ou Oracle Net Client. 32

Capítulo 2 – Visão Geral do Oracle Database O Cliente Oracle ou Oracle Net Client é um conjunto de ferramentas e protocolos que deve ser instalado em uma estação de trabalho para que os clientes (usuários e aplicações) tenham acesso ao servidor. A Arquitetura de Redes da Oracle: Oracle Net Connections Oracle Net Connections é a camada de software que reside tanto no lado do cliente quanto no lado do servidor de banco de dados. É responsável por estabelecer e manter a conexão entre a aplicação cliente e o servidor, bem como o intercâmbio de mensagens entre eles, usando protocolos padrão da indústria. A Oracle Net é compreendida de dois componentes de software: Oracle Net foundation layer e Oracle protocol suport. Para resumir, a oracle net é uma camada de protocolo que roda sobre o protocolo padrão da rede, geralmente o TCP/IP. Download e Instalação do Cliente Oracle ATENÇÃO: Você não pode instalar o Cliente Oracle no servidor! Este tópico é para quando você precisar fazer a instalação dele em uma estação de trabalho. Para fazer o download e a instalação do Cliente Oracle você dever seguir os mesmos procedimentos iniciais adotados para o download e instalação do Oracle Server. Na página de download você seleciona Oracle Database 11g Release 1 Client (11.1.0.6.0) for Microsoft Windows (32-bit). Depois de feito o download e descompactado os arquivos, você executa o setup.exe. A seguir, uma série de figuras e os comentários necessários.

Capítulo 2 – Visão Geral do Oracle Database

A opção selecionada na figura anterior é a comumente escolhida por DBAs, pois inclui ferramentas de backup/restore e outras para gerenciamento do banco de dados do ponto de vista de uma DBA. Já um desenvolvedor e um Administrador de dados precisam apenas da opção Runtime. As ferramentas de runtime permitem que os clientes típicos do Oracle (usuários e aplicativos) possam se conectar sem problemas ao servidor. Selecione Administrador e clique em Próximo.

Capítulo 2 – Visão Geral do Oracle Database

Na tela acima, o cuidado que deve ser tomado ao informar o local onde o Cliente Oracle será instalado, principalmente se você roda o setup.exe a partir de um HD que não seja o C:\. Note que o caminho foi corrigido para C:\oracle\product\11.1.0\client_1, pois em nosso caso ele apontava para D:\ Configurando o Cliente Oracle Ao final da instalação do cliente oracle, o setup.exe abre automaticamente o Assistente de Configuração de Rede. Este assistente ajuda na configuração do cliente, de modo a poder acessar um servidor. Veja na figura a seguir como é sua tela inicial.

Capítulo 2 – Visão Geral do Oracle Database

Clique em Próximo, depois em Finalizar! Criando uma string ou identificador de conexão Abra o Assistente de Configuração de Rede, para que possamos criar uma String de Conexão. Selecione como mostrado na figura abaixo e clique em Próximo.

Capítulo 2 – Visão Geral do Oracle Database Selecione Adicionar e clique em Próximo. Como mostrado na figura abaixo, digite ORCL para o nome do serviço (o nome do serviço é, na verdade, o SID do banco de dados) e clique em Próximo mais uma vez.

Aceite o protocolo TCP e clique em Próximo, até chegar na seguinte tela:

Em Nome do host você deve digitar o nome do servidor onde o Oracle database foi instalado. Preferivelmente digite o IP da máquina, ao invés de seu nome. Note na figura 37

Capítulo 2 – Visão Geral do Oracle Database anterior que digitamos o IP da máquina onde o Oracle foi instalado. Clique em Próximo e o assistente irá perguntar se é para fazer um teste de conexão. Selecione “Sim, realize um teste” e clique em Próximo. O assistente tentará se conectar com o usuário SYSTEM, usando uma senha padrão e obviamente dará erro. Ignore o erro e clique em Alterar login e informe as credenciais do usuário HR que você deve ter habilitado anteriormente. Se tudo correu bem, a tela a seguir será exibida.

Clique em Próximo e informe o “Nome do Serviço de Rede”. Este nome é que usaremos quando for necessária uma conexão. Você pode informar qualquer nome aqui, mas sugerimos digitar ORCL. Conectando-se com o SQL*Plus Para testar a conexão de rede que acabamos de configurar de uma forma mais prática, abra o utilitário SQL*Plus, que pode ser encontrado no menu de programas do windows, na sub-pasta “Desenvolvimento de aplicações”, na pasta de programas do Cliente Oracle. Quando o SQL*Plus solicitar um nome de usuário, informe o nome sempre seguido por @ e o nome do serviço criado via Assistente de Configuração de Rede” e aperte Enter. Após isto, o SQL*Plus solicitará a senha do usuário informado. Veja a figura a seguir, para esclarecimentos. 38

Capítulo 2 – Visão Geral do Oracle Database

Observe na figura acima que foi digitado HR@ORCL quando solicitado o nome do usuário. HR é o nome do usuário. ORCL é o nome do serviço (String de Conexão) criado para conectar ao servidor. Deve ser interpretado como HR at ORCL ou HR em ORCL. Ferramentas do Desenvolvedor e do Administrador de Dados Basicamente, as ferramentas para se trabalhar com o Oracle são: o Enterprise Manager (EM), e o SQL*Plus. O SQL*Plus é a ferramenta mais usada por quem administra o Oracle, ou seja, por DBAs. Para facilitar a vida dos desenvolvedores, a Oracle criou uma ferramenta chamada SQL Developer e ela foi instalada junto com o Oracle 11g. O SQL Developer Como a interface do SQL*Plus é pouco amigável, o SQL Developer é uma alternativamente mais atraente. Sua interface gráfica baseada em JAVA é muito elegante, além de oferecer inúmeros recursos. DICA: Infelizmente, a versão 1.1.3 que é instalada junto com o Oracle está muito defasada. A versão mais recente do SQL Developer é a 1.5.4. Sugerimos que você ignore a versão instalada, e faça o download da versão mais nova. A página oficial do SQL Developer é a seguinte: http://www.oracle.com/technology/products/database/sql_developer/index.html 39

Capítulo 2 – Visão Geral do Oracle Database Nela, você pode encontrar o link para download. Por ser baseado em JAVA, é preciso que você tenha o JDK instalado em tua máquina. Se tiver alguma dúvida se tem ou não o JDK instalado, você pode baixar a versão que já traz consigo um JDK. A “instalação” é muito simples: basta descompactar o arquivo e já executar o sqldeveloper.exe. Por isto não vamos mostrar como fazer isto. Após a “instalação”, você precisa criar uma conexão com o banco de dados. O SQL Developer armazena várias configurações de conexão. Assim, você pode criar configurações para administrador, desenvolvedor, usuário, etc, para fins de administração, desenvolvimento e testes em um único ambiente. A seguir, apresentaremos três figuras: a primeira mostrando a tela de configuração de uma conexão a partir de uma máquina cliente, a segunda mostrando a tela de configuração de uma conexão a partir do servidor e a terceira mostrando a interface do SQL Developer, após conectar com o usuário HR.

Capítulo 2 – Visão Geral do Oracle Database Observe na figura anterior o “Nome do Host”, onde informamos o IP do servidor. Observe também o nome do serviço que criamos usando o Assistente de Configuração de Rede. Se estiver usando o SQL Developer a partir da máquina onde o Oracle foi instalado, você deve mudar o tipo de conexão para TNS e selecionar o Apelido da Rede (ORCL), como mostrado na figura abaixo.

Na figura a seguir, observe a aparência do SQL Developer e alguns detalhes a respeito do Schema HR. Do lado esquerdo é exibida uma árvore com os objetos do Schema HR. Observe que expandimos o segmento Tabelas. Do lado direito, na parte superior, digitamos um comando SQL e apertamos F9 (Executar instrução). O resultado é mostrado na parte inferior do lado direito.

Capítulo 2 – Visão Geral do Oracle Database

De fato, o SQL Developer é uma espécie de canivete suíço para desenvolvedores.

Capítulo 2 – Visão Geral do Oracle Database Exercício de revisão Responda as questões a seguir: 1) Qual a linguagem de programação embutida no banco de dados do Oracle? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 2) Sabemos que um banco de dados é uma coleção de dados e objetos tratados como uma unidade. Que nome se dá à estrutura geral onde estes dados e objetos ficam armazenados? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 3) Cite cinco tipos de objetos que podem ser contidos nesta estrutura geral de banco de dados. ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 4) Em uma estrutura cliente/servidor, o que é necessário para que uma estação de trabalho possa acessar um servidor oracle? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 5) Como se configura uma string ou identificador de conexão? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 43

Capítulo 2 – Visão Geral do Oracle Database 6) Além do Enterprise Manager, que outras ferramentas podem ser utilizadas para conectar e fazer manutenções no Oracle? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Capítulo 2 – Visão Geral do Oracle Database RESPOSTAS 1) PL/SQL 2) Em um Schema 3) Tabelas, views, sequências, índices e procedures 4) O cliente oracle 5) Através do Assistente de Configuração do Oracle NET 6) O SQL*Plus e o SQL Developer

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários A partir deste capítulo iremos estudar a SQL do Oracle, analisando suas características particulares. É importante que você tenha certo conhecimento e domínio sobre a SQL padrão. No capítulo 1, nós vimos como instalar o SGBD e criar uma instância Oracle. No capítulo 2, nós vimos uma visão geral do Oracle database e maneiras de se conectar a ele, seja do servidor ou de uma estação de trabalho. Vimos também quais ferramentas um desenvolvedor e um administrador de dados têm à disposição. Vamos começar este novo capítulo apresentando um aspecto crucial para o entendimento definitivo do que é um banco de dados (ao menos como os desenvolvedores estão acostumados a ver), só que na terminologia da Oracle. Manipulando usuários e controlando seus schemas: bancos de dados Do ponto de vista de um desenvolvedor ou de um administrador de dados, um banco de dados é um “lugar” onde ficam armazenadas as tabelas e demais objetos necessários para o desenvolvimento de um sistema. No Oracle, este “lugar” é um schema. Imagine que você é um desenvolvedor ou um administrador de dados e está envolvido na criação de um sistema para uma empresa de transportes. Naturalmente, você precisa de um “lugar” para criar as tabelas que representam o modelo de dados do sistema. No Oracle, você precisa criar um ou mais schemas para isto. Imagine também que este sistema seja composto de um módulo Financeiro, um módulo de Operações e um módulo de Pessoal, apenas para exemplificar. Você poderia criar um “schemão” chamado Sistema e nele criar todas as tabelas necessárias para todos os módulos do sistema. Mas você pode (e deve) criar schemas independentes para cada módulo: um schema chamado Financeiro, um schema chamado Operacoes e outro schema chamado Pessoal. Em cada um desses schemas você criaria as tabelas necessárias para cada módulo. Para o Oracle, tudo isto é UM banco de dados composto de vários schemas. E nada impede que você também veja desta forma. E se você estiver preocupado com prováveis relações entre tabelas de schemas diferentes, já lhe adiantamos que isto é muito simples de 46

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários implementar. No Oracle, você pode relacionar duas tabelas com muita facilidade, estejam elas no mesmo schema ou em schemas diferentes. Pode, ainda, relacionar tabelas entre instâncias diferentes, em diferentes pontos do planeta, desde que haja uma conexão de rede segura entre os bancos de dados! Criando e controlando usuários Para criar um usuário, você precisa estar conectado como um DBA. O usuário SYSTEM é o mais apropriado para esta e demais tarefas de um DBA. A sintaxe padrão para a criação de um usuário é a seguinte: CREATE USER Usuário IDENTIFIED BY Senha;

Embora as palavras-chave tenham sido escritas em letras maiúsculas, o Oracle não é “case sensitive” com relação às palavras chave. Você pode escrever tanto em maiúsculas quanto em minúsculas. A sintaxe completa para a criação de um usuário é a seguinte: CREATE USER Usuário IDENTIFIED BY Senha DEFAULT TABLESPACE Tablespace TEMPORARY TABLESPACE TemporaryTablespace;

Quando não informamos a Default Tablespace e a Temporary Tablespace, o Oracle usa as Tablespaces Default do banco de dados. No Oracle, estas Tablespaces são USERS e TEMP, respectivamente. As Tablespaces USERS e TEMP A Default TableSpace é a tablespace onde o Oracle armazena as estruturas lógicas que um usuário cria em seu Schema, caso não seja informada outra. Assim, se um usuário criasse uma tabela, através do comando: CREATE TABLE ASSUNTO_PROTOCOLO( ID_ASSUNTO INTEGER PRIMARY KEY, DS_ASSUNTO VARCHAR2(128) );

a estrutura lógica da tabela ASSUNTO_PROTOCOLO seria criada e armazenada dentro da Default Tablespace dele (geralmente, USERS). Mas nada impede o usuário de fazer o seguinte: CREATE TABLE ASSUNTO_PROTOCOLO( ID_ASSUNTO INTEGER PRIMARY KEY, DS_ASSUNTO VARCHAR2(128)

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários ) TABLESPACE NomeDaTableSpace;

Ou seja, ele poderia criar a tabela em seu Schema, mas armazenando sua estrutura lógica em outra Tablespace. A Temporary TableSpace é a tablespace onde o Oracle armazena temporariamente estruturas lógicas necessárias quando se está manipulando dados. Em determinadas situações de consultas muito complexas, por exemplo, o Oracle precisa criar estruturas lógicas temporariamente, para somente depois dar o resultado. É na Temporary TableSpace onde ele armazena tais estruturas. Mais uma vez vou dizer que esta característica ímpar abre uma janela incrível de possibilidades, mas vamos falar sobre isto em outra oportunidade. Privilégios mínimos para que um usuário se conecte ao Oracle e possa criar objetos Criar o usuário não basta! Nós ainda precisamos dizer o que ele pode fazer no Oracle. Fazemos isto através de outro comando da categoria DCL. Seu nome é GRANT. Em nosso exemplo faríamos: GRANT CONNECT, RESOURCE, UNLIMITED TABLESPACE TO Usuário;

Há inúmeros outros privilégios, mas estes permitem conectar-se ao servidor (CONNECT), criar recursos (RESOURCE) ou objetos, tais como tabelas e etc, sem nenhum limite estabelecido para armazenamento destes recursos (UNLIMITED TABLESPACE). Vamos praticar um pouco! Conecte-se ao Oracle via SQL*Plus, usando o usuário SYSTEM, e crie o usuário FINANCEIRO, como mostrado na figura a seguir:

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários

OBS.: Se você estiver se conectando a partir do servidor, não precisa usar o @ORCL após o nome do usuário, uma vez que no servidor não há a necessidade de uma “String de Conexão”. Note na figura que não informamos as default tablespaces quando criamos o usuário. Neste caso, o Oracle usou USERS e TEMP por default (existe um parâmetro de banco de dados que informa quais são as tablespaces default). Note também que depois da criação e concedidos os GRANTs, nós conectamos via conn financeiro/teste

conn é uma abreviação para connect, e note a sintaxe usuário/senha. O mais seguro é que você digite apenas o usuário e pressione Enter, para que o SQL*Plus solicite a senha em “modo sigiloso”. Isto evita que a senha seja gravada, caso você tenha ativado o recurso de log das operações realizadas pelo SQL*Plus. Se você não estiver “logando” suas atividades, fica a teu critério usar /senha... Para mostrarmos um recurso de segurança do Oracle, crie uma tabela dentro do schema de Financeiro. Pode ser a ASSUNTO_PROTOCOLO mostrada anteriormente. DICA: Existe um recurso interessante que é o famoso copiar/colar. Você pode copiar um texto qualquer e colar no SQL*Plus, acessando a opção Editar/Colar no menu do sistema da janela do SQL*Plus. Veja na figura abaixo como copiamos o comando de criação da tabela ASSUNTO_PROTOCOLO e colamos no SQL*Plus. 49

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários

Removendo usuários Para completar esta etapa do aprendizado, vamos remover o usuário financeiro. Para isto, conecte-se como SYSTEM e use o seguinte comando: DROP USER FINANCEIRO;

Qual foi o resultado? Isso mesmo, o Oracle não removeu o usuário e mostrou a seguinte mensagem: ERRO na linha 1: ORA-01922: CASCADE deve ser especificado para eliminar 'FINANCEIRO'

Isto ocorreu porque o schema financeiro não está vazio! Como ele tem ao menos um objeto, o Oracle faz o alerta e espera que você assuma a responsabilidade de digitar a cláusula CASCADE! Agora digite, finalmente: DROP USER FINANCEIRO CASCADE;

Veja o resultado na figura a seguir.

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários

Concedendo privilégios de acesso aos objetos de um schema para outro schema Um usuário é o dono (OWNER) absoluto dos objetos de seu schema. A princípio, só ele tem acesso livre aos objetos de seu schema. Para que outro usuário tenha acesso aos seus objetos, é necessário que ele conceda os privilégios adequados a quem for preciso. PRÁTICA: Vamos aproveitar o schema de exemplo HR para compreendermos este aspecto do Oracle. Conecte-se como HR e execute os seguintes comandos, observando que é para digitar o que estiver em negrito, a numeração das linhas é feita pelo SQL*Plus e listaremos o resultado em azul: SQL> select * from tab; TNAME -----------------------------COUNTRIES DEPARTMENTS EMPLOYEES EMP_DETAILS_VIEW JOBS JOB_HISTORY LOCATIONS REGIONS

TABTYPE CLUSTERID ------- ---------TABLE TABLE TABLE VIEW TABLE TABLE TABLE TABLE

8 linhas selecionadas.

SQL> desc locations Nome Nulo? Tipo ----------------------------------------- -------- -------------

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY STATE_PROVINCE COUNTRY_ID

NOT NULL NUMBER(4) VARCHAR2(40) VARCHAR2(12) NOT NULL VARCHAR2(30) VARCHAR2(25) CHAR(2)

SQL> select LOCATION_ID, CITY 2 FROM locations 3 where LOCATION_ID = 2800; LOCATION_ID CITY ----------- -----------------------------2800 Sao Paulo

SQL> update locations 2 set CITY = 'São Paulo' 3 where LOCATION_ID = 2800; 1 linha atualizada.

SQL> select LOCATION_ID, CITY 2 FROM locations 3 where LOCATION_ID = 2800; LOCATION_ID CITY ----------- -----------------------------2800 São Paulo

SQL> commit;

Commit concluído.

O que aconteceu? Bem: 

nós consultamos uma view do dicionário de dados (tab), para ver as tabelas e views de HR;



pedimos para ver a estrutura da tabela locations (desc não é SQL. É um comando do SQL*Plus);



selecionamos (select) e listamos dois atributos (LOCATION_ID e CITY) da tabela locations, cuja linha tenha o atributo LOCATION_ID = 2800;



atualizamos (update) a tabela locations, colocando “São Paulo” com acento;



listamos novamente a linha que contém LOCATION_ID = 2800, para verificar o resultado; 52

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários 

Confirmamos a alteração enviando-a (commit) para a tabela.

+PRÁTICA: Para prosseguir nosso exercício, crie um usuário chamado John e conceda a ele os privilégios mínimos (connect, resource, unlimited tablespace). Depois de criá-lo, conecte-se com ele e tente modificar de ‘Mexico City’ para ‘México’ o atributo CITY da tabela locations de HR, cujo LOCATION_ID = 3200. NOTA: Para que um usuário acesse uma tabela em outro schema, ele precisa informar em qual schema se encontra a tabela, precedendo-a por schema. (‘schema’ ‘ponto’). Ex.: SELECT * FROM HR.LOCATIONS; Certamente, você conseguiu criar o usuário John e se conectar com ele sem problema. Mas ao tentar acessar os objetos de HR, mesmo informando o caminho (HR.), você obteve uma mensagem de erro. Isto está correto! SEGURANÇA é tudo para o Oracle. A começar pelo fato de que criar um usuário não significa nada, se você não conceder os privilégios mínimos. Nem conectar ele poderia. Para completar o exercício, HR terá que conceder à John privilégios de SELECT e UPDATE sobre a tabela locations. Como fazer isto? Conecte-se como HR e digite: GRANT SELECT, UPDATE ON locations TO John; Concessão bem-sucedida.

Agora é só voltar a se conectar como John e completar o exercício: SQL> conn John/teste Conectado. SQL> SELECT LOCATION_ID, CITY 2 from HR.locations 3 WHERE LOCATION_ID = 3200;

LOCATION_ID CITY ----------- -----------------------------3200 Mexico City

SQL> update HR.locations 2 set CITY = 'México' 3 WHERE LOCATION_ID = 3200;

1 linha atualizada.

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários SQL> SELECT LOCATION_ID, CITY 2 from HR.locations 3 WHERE LOCATION_ID = 3200;

LOCATION_ID CITY ----------- -----------------------------3200 México

SQL> commit;

Commit concluído.

Sinônimos Quando o nome de um schema é muito grande ou você precisa trabalhar com múltiplos schemas, o uso de prefixos antes dos nomes dos objetos pode tornar a programação um ato ainda menos confortável. O Oracle oferece um recurso que visa facilitar a referência de objetos em outros schemas: os sinônimos. PRÁTICA: Antes de prosseguirmos, conecte como SYSTEM, para dar a John mais alguns privilégios (agora, alguns privilégios de sistema): grant create sequence, create synonym, create view to John; Finalmente, conecte como John e crie um sinônimo para a tabela locations, no schema HR: SQL> conn John/teste

Conectado.

SQL> create synonym locations for hr.locations;

Sinônimo criado.

Sinônimos privados e sinônimos públicos O que acabamos de fazer foi criar um sinônimo privado. Ele é privado porque é um sinônimo do schema de John. Somente ele pode usar este sinônimo. 54

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários Agora John pode fazer selects e updates em HR.locations de uma forma simplificada. Basta usar o sinônimo. Assim: Select * FROM locations;

OBS.: Em nosso exemplo, criamos um sinônimo com o mesmo nome da tabela original. Mas nada impede que você use outro nome (naturalmente, o outro nome deve lembrar o original). É por isto que este recurso se chama sinônimo! Em uma situação com muitos esquemas e dezenas (talvez centenas) de usuários, seria praticamente inviável criar sinônimos privados em todos os schemas, para que os usuários pudessem acessar um conjunto de esquemas desta forma. Para contornar esta dificuldade, é que existem os sinônimos públicos. Existe um schema especial chamado PUBLIC. Como o nome sugere, tudo neste schema é de uso público. Se criarmos um sinônimo neste schema, ele se tornará automaticamente disponível para todos os usuários do Oracle. PRÁTICA: Para criar um sinônimo público você precisa ser um DBA, ou ter recebido um privilégio específico. Então vamos conectar com o usuário SYSTEM, para criar um sinônimo público para HR.employees. SQL> conn system Informe a senha:

Conectado.

SQL> create public synonym employees for hr.employees;

Sinônimo criado.

PERGUNTA: O fato de termos criado um sinônimo público para HR.employees dá direito a qualquer usuário acessar a tabela employees, de HR? Se você respondeu NÃO, está começando a entender o Oracle! Obviamente, ainda é necessário que HR dê privilégios sobre employees a qualquer um que queira acessar esta tabela. PERGUNTA: E o usuário SYSTEM, será que ele pode acessar employees, mesmo sem HR lhe ter dado qualquer privilégio? 55

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários Se você respondeu SIM, parabéns, você é uma pessoa muito atenta! SYSTEM e SYS são usuários super poderosos. São os famosos SYSDBA. Por isto que eles devem ser usados com muito cuidado e somente quanto for absolutamente necessário. Qualquer deslize seu, e você poderá mandar um banco de dados inteiro para o “limbo”! EXERCÍCIO: Conecte como HR e dê privilégios a John de SELECT e UPDATE sobre a tabela employees. Detalhes sobre o comando GRANT Até agora usamos o comando GRANT meio que às cegas. Vamos nos aprofundar um pouco mais nele. Você usa GRANT para conceder: 

Privilégios de sistema (System privileges) para usuários e papéis (roles);



Papéis para usuários e outros papéis (roles);



Privilégios de Objeto de um objeto em particular para usuários, papéis e PUBLIC.

Através de GRANT, um usuário (tipicamente um usuário de banco de dados) concede os privilégios necessários aos mais diversos usuários, definindo o que cada um poderá ou não fazer sobre tabelas, views e demais objetos de seu schema. Além disso, os usuários podem receber privilégios de sistema, conforme a necessidade. Papéis (ROLES) A tarefa de dar privilégios aos usuários de um sistema pode se tornar impraticável se o número de usuários e objetos for muito grande. Imagine definir cada SELECT, INSERT ou UPDATE que cada um poderá ou não fazer sobre um sem número de objetos! Para facilitar esta tarefa, você pode criar papéis. Os papéis são parecidos com Grupos de Usuário em um sistema operacional. Você cria um papel e dá a ele os privilégios que achar necessários, geralmente agrupando-os por uma categoria ou funcionalidade. Depois, você pode facilmente atribuir estes papéis aos usuários. Para exemplificar, vamos criar uma situação hipotética sobre o schema HR. Digamos que existam grupos de usuários com os seguintes papéis: 

Usuários que só podem ver o conteúdo das tabelas (SELECT);



Usuários que podem atualizar (UPDATE) dados nas tabelas; 56

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários 

Usuários que podem inserir (INSERT) dados nas tabelas;



Usuários que podem excluir (DELETE) dados das tabelas;



E, finalmente, usuários que podem fazer tudo.

Nesta caso, o que podemos fazer é criar os seguintes papéis: 

HR_SELECT, para o primeiro grupo de usuários;



HR_UPDATE, para o segundo grupo de usuários;



HR_INSERT, para o terceiro grupo de usuários;



HR_DELETE, para o quarto grupo de usuários;



HR_TUDO, para o último grupo de usuários.

Criando papéis Para criar um papel (ROLE) um usuário precisa ter o privilégio de sistema CREATE ROLE. Então precisamos dar a HR este privilégio. Usando o usuário SYSTEM, basta executar o seguinte comando: GRANT CREATE ROLE TO HR;

OBS.: Naturalmente, você poderia usar o SYSTEM para criar os papéis e depois HR poderia utilizá-lo. Agora conectados como HR, podemos criar os papéis: SQL> conn hr/hr Conectado. SQL> create role HR_SELECT NOT IDENTIFIED; Atribuição criada. SQL> create role HR_UPDATE NOT IDENTIFIED; Atribuição criada. SQL> create role HR_INSERT NOT IDENTIFIED; Atribuição criada. SQL> create role HR_DELETE NOT IDENTIFIED;

Atribuição criada. SQL> create role HR_TUDO NOT IDENTIFIED;

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários Atribuição criada.

Dando atribuições aos papéis Lembrando quais são as tabelas de HR: SQL> select * from tab;

TNAME -----------------------------COUNTRIES DEPARTMENTS EMPLOYEES EMP_DETAILS_VIEW JOBS JOB_HISTORY LOCATIONS REGIONS

TABTYPE CLUSTERID ------- ---------TABLE TABLE TABLE VIEW TABLE TABLE TABLE TABLE

8 linhas selecionadas.

Vamos fazer as devidas atribuições para cada papel criado: -- SELECT grant SELECT grant SELECT grant SELECT grant SELECT grant SELECT grant SELECT grant SELECT -- UPDATE grant UPDATE grant UPDATE grant UPDATE grant UPDATE grant UPDATE grant UPDATE grant UPDATE -- INSERT grant INSERT grant INSERT grant INSERT grant INSERT grant INSERT grant INSERT grant INSERT -- DELETE grant DELETE grant DELETE grant DELETE grant DELETE grant DELETE grant DELETE grant DELETE

on on on on on on on

COUNTRIES TO HR_SELECT; DEPARTMENTS TO HR_SELECT; EMPLOYEES TO HR_SELECT; JOBS TO HR_SELECT; JOB_HISTORY TO HR_SELECT; LOCATIONS TO HR_SELECT; REGIONS TO HR_SELECT;

on on on on on on on

COUNTRIES TO HR_UPDATE; DEPARTMENTS TO HR_UPDATE; EMPLOYEES TO HR_UPDATE; JOBS TO HR_UPDATE; JOB_HISTORY TO HR_UPDATE; LOCATIONS TO HR_UPDATE; REGIONS TO HR_UPDATE;

on on on on on on on

COUNTRIES TO HR_INSERT; DEPARTMENTS TO HR_INSERT; EMPLOYEES TO HR_INSERT; JOBS TO HR_INSERT; JOB_HISTORY TO HR_INSERT; LOCATIONS TO HR_INSERT; REGIONS TO HR_INSERT;

on on on on on on on

COUNTRIES TO HR_DELETE; DEPARTMENTS TO HR_DELETE; EMPLOYEES TO HR_DELETE; JOBS TO HR_DELETE; JOB_HISTORY TO HR_DELETE; LOCATIONS TO HR_DELETE; REGIONS TO HR_DELETE;

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários -- TUDO grant HR_SELECT, HR_UPDATE, HR_INSERT, HR_DELETE TO HR_TUDO;

DICA: Este é um caso típico para se criar um Script SQL. Copie todos os comandos acima, a partir da linha – SELECT, e salve em um arquivo do bloco de notas, com o nome HR_ROLES.SQL em C:\. Veja a figura abaixo, para esclarecimentos.

Criado o script, basta rodá-lo via SQL*Plus, conectado como HR, usando o seguinte comando: SQL>@C:\HR_ROLES

Como resultado, o SQL*Plus irá mostrar, repetidamente, a seguinte mensagem: Concessão bem-sucedida.

Queremos chamar a atenção para dois detalhes neste script: 1. Comentários: você pode inserir comentários em Scripts ou em qualquer código SQL Oracle, usando -- (dois traços juntos); 59

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários 2. O último comando atribuiu uma série de papéis previamente criados a um outro papel. Assim, HR_TUDO é a “soma” de todos os papéis atribuídos a ele. Com os papéis criados, fica fácil agora dar atribuições aos usuários. Exemplo: -- Julia pode fazer select em todas as tabelas de HR; GRANT HR_SELECT TO Julia; -- Anderson pode fazer SELECT e UPDATE em todas as tabelas de HR; GRANT HR_SELECT, HR_UPDATE TO Anderson; -- Maria pode fazer SELECT e deletar dados de qualquer tabela de HR; GRANT HR_SELECT, HR_DELETE TO Maria; -- Juca pode fazer (quase) tudo com as tabelas de HR, exceto EXCLUIR (DROP) uma tabela. GRANT HR_TUDO TO Juca;

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários Exercício de revisão Responda as questões a seguir: 1) Qual a relação entre usuário e schema? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 2) Qual a relação entre schema e tablespace? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 3) Qual a relação da tablespace USERS com a criação de usuários? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 4) Quais privilégios mínimos um usuário precisa para conectar e criar objetos? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 5) Através de qual comando se dá privilégios a um usuário? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 6) Qual comando exclui um usuário e para que serve a cláusula CASCADE? ______________________________________________________________________ ______________________________________________________________________ 61

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários 7) Após a exclusão de um usuário com a cláusula CASCADE, o que ocorreu com os objetos que ele havia criado? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 8) O que é necessário fazer para que um usuário tenha acesso aos objetos de outro usuário? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 9) Qual recurso pode ser utilizado para que um usuário não precise informar em qual schema se encontra um objeto de outro usuário? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 10) Qual a vantagem de se utilizar um papel (role)? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Capítulo 3 – Oracle SQL – Bancos de Dados & Usuários RESPOSTAS 1) Um usuário é o dono de um schema que tem o mesmo nome que o usuário 2) Os objetos criados por um usuário em seu schema são armazenados logicamente em uma tablespace default 3) Ela é a tablespace default do banco de dados. Todo usuário criado, salvo indicação em contrário, terão os objetos de seus schema armazenados nesta tablespace. 4) CONNECT, RESOURCE e UNLIMITED TABLESPACE 5) GRANT 6) O comando é DROP USER e a cláusula CASCADE serve para forçar a exclusão do usuário, mesmo que ele tenha objetos criados. 7) Foram eliminados definitivamente do banco de dados. 8) Conceder provilégios 9) Criar um sinônimo público ou em seu schema 10) Com ele você evita ter que conceder vários privilégios um-por-um a um usuário. Basta criar um papel e conceder a ele todos os privilégios necessários e no final conceder este papel aos usuários que for preciso.

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML)

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) Neste capítulo iremos estudar os aspectos particulares do Oracle, com respeito à definição e manipulação de dados. A parte da SQL que cuida da definição de dados é a DDL – Data Definition Language e a que cuida da manipulação de dados é a DML - Data Manipulation Language. É um assunto vasto! Para se ter uma idéia, apenas o comando SELECT ocupa 48 páginas do manual de referência de SQL da Oracle! Por isto, vamos cobrir os aspectos mais relevantes e, principalmente, os mais específicos do Oracle.

DDL – Data Definition Language – Elementos básicos

Neste tópico, faremos um resumo dos tipos de dados do Oracle e sua relação com o padrão ANSI. Além disso, mostraremos as particularidades dos comandos de criação de objetos. Tipos básicos de dados A seguir, duas tabelas extraídas do manual de referência: Table 2-1 Built-in Datatype Summary Code Datatype

Description

VARCHAR2(size [BYTE | CHAR])

Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2. BYTE indicates that the column will have byte length semantics. CHAR indicates that the column will have character semantics.

NVARCHAR2(size)

Variable-length Unicode character string having maximum length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify size for 64

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) Code Datatype

Description NVARCHAR2.

NUMBER

[ (p [, s]) ]

FLOAT

LONG

Character data of variable length up to 2 gigabytes, or 231 -1 bytes. Provided for backward compatibility.

DATE

Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.

BINARY_FLOAT

32-bit floating point number. This datatype requires 5 bytes, including the length byte.

BINARY_DOUBLE

64-bit floating point number. This datatype requires 9 bytes, including the length byte.

180

TIMESTAMP [(fractional_seconds_precision)]

Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.

181

TIMESTAMP [(fractional_seconds)] WITH TIME ZONE

All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 13 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR,

[(p)]

Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127. Both precision and scale are in decimal digits. A NUMBER value requires from 1 to 22 bytes. A subtype of the NUMBER datatype having precision p. A FLOAT value is represented internally as NUMBER. The precision p can range from 1 to 126 binary digits. A FLOAT value requires from 1 to 22 bytes.

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) Code Datatype

Description and TIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.

231

TIMESTAMP [(fractional_seconds)] WITH LOCAL TIME ZONE

All values of TIMESTAMP WITH TIME ZONE, with the following exceptions:  Data is normalized to the database time zone when it is stored in the database.  When the data is retrieved, users see the data in the session time zone. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision.

182

INTERVAL YEAR [(year_precision)] TO MONTH

Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.

183

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)]

Stores a period of time in days, hours, minutes, and seconds, where  day_precision is the maximum number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.  fractional_seconds_precision is the number of digits in the fractional part of the SECOND field. Accepted values are 0 to 9. The default is 6. The size is fixed at 11 bytes.

RAW (size)

Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.

LONG RAW

Raw binary data of variable length up to 2 gigabytes.

ROWID

Base 64 string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.

208

UROWID

CHAR

NCHAR[(size)]

[(size)]

[(size [BYTE | CHAR])]

Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes. Fixed-length character data of length size bytes or characters. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte. BYTE and CHAR have the same semantics as for VARCHAR2. Fixed-length character data of length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national 66

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) Code Datatype

Description character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.

112

CLOB

A character large object containing single-byte or multibyte characters. Both fixed-width and variablewidth character sets are supported, both using the database character set. Maximum size is (4 gigabytes 1) * (database block size).

112

NCLOB

A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. Maximum size is (4 gigabytes - 1) * (database block size). Stores national character set data.

113

BLOB

A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).

114

BFILE

Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.

Table 2-6 ANSI Datatypes Converted to Oracle Datatypes ANSI SQL Datatype

Oracle Datatype

CHARACTER(n) CHAR(n)

CHAR(n)

CHARACTER VARYING(n) CHAR VARYING(n)

VARCHAR2(n)

NATIONAL CHARACTER(n) NATIONAL CHAR(n) NCHAR(n)

NCHAR(n)

NATIONAL CHARACTER VARYING(n) NATIONAL CHAR VARYING(n) NCHAR VARYING(n)

NVARCHAR2(n)

NUMERIC[(p,s)] DECIMAL[(p,s)] (Note

NUMBER(p,s)

INTEGER INT SMALLINT

NUMBER(38)

FLOAT (Note b) DOUBLE PRECISION REAL (Note d)

FLOAT(126) FLOAT(126) FLOAT(63)

(Note c)

Notes:

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) a. The NUMERIC and DECIMAL datatypes can specify only fixed-point numbers. For those datatypes, the scale (s) defaults to 0. b. The FLOAT datatype is a floating-point number with a binary precision b. The default precision for this datatype is 126 binary, or 38 decimal. c. The DOUBLE PRECISION datatype is a floating-point number with binary precision 126. The REAL datatype is a floating-point number with a binary precision of 63, or 18 decimal. Nós resistimos à tentação de traduzir para o português. Toda e qualquer tradução sempre insere distorções. E quando o assunto é técnico, isto se agrava. Mesmo porque, é praticamente impossível um profissional de TI não ter ao menos um bom inglês técnico. Todavia, vamos fazer uma explanação dos tipos de dados mais relevantes e provavelmente os mais utilizados. Dados do tipo texto (string) ou caractere (char) Nós sempre precisamos armazenar dados do tipo texto em um banco de dados. E o tamanho a ser armazenado é que vai determinar qual o tipo mais adequado oferecido pelo Oracle. 

Textos de tamanho fixo

Para certos tipos de dados, como CPF, CNPJ e números de telefone, o tipo CHAR é o mais adequado. CHAR [(tamanho [BYTE Dado tipo caractere de tamanho fixo. O tamanho máximo é de 2000 bytes ou | CHAR])] caracteres. O tamanho mínimo é de 1 (default). CHAR | BYTE e têm a mesma semântica que para VARCHAR2.

O inconveniente deste tipo de dado é o seu tamanho fixo, ou seja, se você criar um campo do tipo CHAR(32), por exemplo, ele sempre vai ocupar 32 bytes, mesmo que o dado armazenado tenha menos caracteres. O restante do espaço à direita será completado com o caractere ASCII 32 (espaço). Ele é indicado para tipos de dado de tamanho bem definido, como os já citados. DICA: Como o Oracle não tem o tipo Boolean ou lógico (Sim / Não | Verdadeiro / Falso), o tipo CHAR certamente é o mais indicado para este caso. Você cria um campo CHAR(1) e define as regras para Sim / Não | Verdadeiro / Falso (armazenar neste campo S ou N, V ou F, ou ainda 1 ou 0). 

Textos de tamanho variado

Para textos de tamanho variado, como nome de pessoas ou ruas, por exemplo, o tipo 68

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) recomendado é o VARCHAR2. VARCHAR2(tamanho [BYTE | CHAR])

String de caracteres de comprimento variável, com um comprimento máximo definido em bytes ou caracteres. O tamanho máximo é 4.000 bytes ou caracteres, e um mínimo de 1 byte ou 1 caractere. Você deve especificar um tamanho para VARCHAR2. BYTE indica que a coluna terá semântica de comprimento de byte. CHAR indica que a coluna terá semântica de comprimento de caractere.

Para campos tipo texto com um tamanho razoável, o melhor a fazer é usar este tipo de dado. O Oracle armazena na tabela um ponteiro para o dado que será armazenado dinamicamente no banco de dados. Quanto menos caracteres no texto, menos espaço de armazenamento. 

Textos gigantes

Para textos absurdamente grandes, o tipo indicado é o CLOB. CLOB Um objeto muito grande do tipo caractere para tipos de byte único ou caracteres multibyte. O tipo de caractere é suportado pelas configurações do banco de dados (UTF, etc). O tamanho máximo é (4 gigabytes - 1) * (tamanho bloco de dados)

Todos os tipos citados até aqui têm seu correspondente N: NCHAR, NVARCHAR2 e NCLOB. N vem de National, que vem de nacionalização. Só faz sentido usar estes tipos quando a aplicação ou sistema visar o mercado internacional, pois as regras para trabalhar com os tipos N ganham uma complexidade e um custo adicionais. Dados do tipo numérico Outra coisa que invariavelmente precisamos armazenar em bancos de dados são números. Parecido com os tipos texto, a necessidade de tamanho e precisão dos números é que vai determinar o melhor tipo a ser utilizado. Vamos destacar alguns. 

Números de precisão simples e números inteiros

Um único tipo atende a esta necessidade: o NUMBER. NUMBER [ (p Número contendo uma precisão p e uma escala s. A precisão pode ir de 1 até 38. A escala s [, s]) ] pode ir de -84 até 127. tanto a precisão quanto a escala são especificadas em números decimais.

Problema de tradução detectado! A precisão p, na verdade, é a quantidade de dígitos do número, tanto à esquerda quanto à direita da vírgula; e a escala s é a quantidade de casas decimais, ou seja quantos números podem aparecer à direita da vírgula. Exemplo:

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) NUMBER(10,2): pode armazenar de 0,00 até 99999999,99 Intuitivamente, todos pensam que podem armazenar 9999999999,99, ou seja, dez dígitos antes da vírgula e dois dígitos depois da vírgula. Mas não é assim que funciona. 10 é a precisão, ou seja, a precisão é a quantidade total de dígitos que um número pode ter! EXERCÍCIO: Conecte com o usuário HR e crie a seguinte tabela: create table teste( campo1 number(10,2) );

OBS.: Ao inserir dados numéricos em uma tabela via SQL, use sempre o . (ponto) como separador das casas decimais. Tente inserir o número 9999999999,99 na tabela teste. Não conseguiu, não foi? Agora insira o número 99999999,99. Para encerrar o exercício, adicione mais um campo na tabela teste: alter table teste add campo2 number(10);

Agora pode inserir os números 99999999,99 em campo1 e 9999999999 em campo2. Números inteiros Para declarar um número inteiro, basta usar NUMBER(n), onde n é a quantidade de dígitos que você vai precisar neste inteiro. Só isto! Embora você possa declarar um campo como sendo do tipo INTEGER, na verdade o que o Oracle faz é converter isto para NUMBER(38). Dados do tipo Data e Hora Datas e horas são outros tipos de dados que necessitamos armazenar em bancos de dados. Embora existam tipos tão exóticos quanto o TIMESTAMP, INTERVAL YEAR e INTERVAL DAY, certamente em 99,99% dos casos você vai precisar do tipo DATE. DATE Data válida que pode ser de 1º de Janeiro de 4712 AC até 31 de Dezembro de 9999 DC. O formato padrão da data é explicitamente determinado pelo parâmetro NLS_DATE_FORMAT ou implicitamente pelo parâmetro NLS_TERRITORY. Este tipo de dados contém os campos data/hora ANO, MÊS, DIA, HORA, MINUTO, e SEGUNDO. Ele não contém frações de segundo ou informações de fuso horário.

Se você, além disso tudo, precisar de frações de segundo, então o tipo indicado é o

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) TIMESTAMP. Datas e horas são sempre armazenadas no Oracle desta forma. A depender da ferramenta que você utilizar para visualizar este tipo de dado, a informação pode ser mostrada parcialmente. Se você, por exemplo, solicitar para visualizar a data do sistema, acessando a variável interna SYSDATE, o resultado pode ser este: SQL> select sysdate as DataHora 2 from dual; SYSDATE -------15/07/09

Como podemos ver, foi informada apenas a parte da data! Para facilitar o trabalho com datas e horas, o Oracle oferece a função TO_DATE, que converte textos contendo informações de data / hora para o tipo DATE e a função TO_CHAR, que além de servir para inúmeros outros fins, também pode ser usada para converter o tipo DATE para um formato mais “elegante”. Exemplos: SQL> to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') as DataHora 2 from dual; DATAHORA ------------------15/07/2009 20:08:22

ou insert into TabelaTal (CampoData) values (to_date('15/07/2009 20:09:00', 'dd/mm/yyyy hh24:mi:ss'));

se quiséssemos inserir uma data e hora numa tabela. As funções TO_CHAR e TO_DATE serão estudadas detalhadamente mais adiante. CURIOSIDADE: a pseudo-tabela dual. Por uma questão sintática, todo comando SELECT precisa de um FROM. Para aquelas situações onde você precisa usar SELECT, mas que na verdade não está buscando informações em uma tabela, o Oracle resolve isto com a pseudo-tabela dual. Foi isto que fizemos quando precisamos ver a data e hora do sistema, fazendo um SELECT na variável sysdate. Existem várias outras situações em que será necessário utilizar esta pseudo-tabela. Mas fica aqui a introdução! Dados de tipos especiais 71

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) Para atender a demandas mais exóticas, podemos citar os tipos RAW, LONG RAW e BLOB, sendo este último o mais utilizado até mesmo no lugar dos dois primeiros. BLOB

Um enorme objeto binário. O tamanho máximo é (4 gigabytes - 1) * (database block size).

Imagens (JPG, BMP, etc) e outros tipos de dados “especiais” podem ser armazenados facilmente em campos deste tipo. Funções para conversão de tipos de dados Mostramos abaixo uma tabela simplificada com funções que o Oracle disponibiliza para conversão de tipos de dados. to CHAR,VARCHAR2,NCHAR,NVARCHAR2 to NUMBER from CHAR, VARCHAR2, NCHAR, NVARCHAR2

TO_CHAR (char.)

to Datetime/Interval

TO_NUMBER TO_DATE

TO_NCHAR (char.)

TO_TIMESTAMP TO_TIMESTAMP_TZ TO_YMINTERVAL TO_DSINTERVAL

from NUMBER

TO_CHAR (number)

TO_NCHAR (number)

TO_DATE NUMTOYMINTERVAL NUMTODSINTERVAL

from Datetime/ Interval

TO_CHAR (date)

TO_NCHAR (datetime)

DML – Data Manipulation Language – Elementos básicos

De nada adianta criar tabelas e armazenas dados nelas se não tivermos meios eficazes para a obtenção e visualização destes dados. Como esta apostila parte do princípio que você já tem um conhecimento básico de SQL e está apenas querendo conhecer o Oracle, não vamos abordar a SQL padrão mas sim a SQL no contexto do Oracle. Então observe atentamente os exemplos, para encontrar as “pérolas”. 72

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) Consultando e manipulando dados Com o SQL Developer é possível visualizar e manipular os objetos de um schema, expandindo a árvore de objetos. Veja uma amostra na figura abaixo.

Como é possível observar, existem Tabelas, Views, Índices, Packages, Procedures, etc... Navegando pela árvore de objetos, podemos visualizá-los e também manipulá-los. Expandindo o item Tabelas, por exemplo, visualizamos as tabelas do schema. Clicando em uma tabela, podemos analisar sua estrutura, ver seus dados, “constraints”, concessões e etc. Veja na figura a seguir, o SQL Developer exibindo a estrutura da tabela employees.

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML)

Lembra-se dos ROLES que criamos e das concessões que HR deu sobre suas tabelas? Veja na figura a seguir estas concessões.

Finalmente, na tela a seguir, o SQL Developer exibe os dados de employees, permitindo a 74

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) edição deles.

Embora isto seja muito interessante, não é muito didático usar ferramentas visuais para este tipo de atividade em um curso. Por isto, vamos continuar usando o SQL Developer, mas digitando os comandos SQL diretamente, usando o “SQL Worksheet pane”. Há duas formas de listar o resultado de um comando dado pelo “SQL Worksheet pane”. Uma é clicar no botão “Executar instrução” (ao apertar F9) e outra é clicar no botão “Executar script” (ou apertar F5). A diferença de como o resultado do comando é mostrado pode ser vista nas duas figuras a seguir. Para poupar espaço na apostila, mostraremos apenas o resultado dos comandos obtido da “Saída do script” o colados aqui e usaremos figuras quando forem absolutamente necessárias para a compreensão do procedimento sendo abordado.

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML)

Consultando dados: o comando SELECT Uma consulta simples tem a seguinte estrutura: SELECT lista_do_select FROM origem_da_lista;

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) Para trazer todos os dados da tabela employees, por exemplo, usamos: SELECT * FROM employees; EMPLOYEE_ID ---------------------100 101 102 ... 107 rows selected

FIRST_NAME -------------------Steven Neena Lex

LAST_NAME ------------------------King Kochhar De Haan

... ... ... ... ...

Para trazer apenas algumas colunas de employees, usamos: SELECT first_name, last_name, hire_date FROM employees; FIRST_NAME ---------------------Steven Neena Lex ... 107 rows selected

LAST_NAME ------------------King Kochhar De Haan

HIRE_DATE ------------------------17-JUN-87 21-SEP-89 13-JAN-93

Apelidos (alias) de colunas Para tornar as coisas mais elegantes, podemos usar apelidos para as colunas, evitando exibir o resultado com os nomes originais. A seguir, um exemplo simples: SELECT FROM

first_name name1, last_name name2, hire_date hired employees;

NAME1 --------------------Steven Neena Lex ... 107 rows selected

NAME2 -------------------King Kochhar De Haan

HIRED ------------------------17-JUN-87 21-SEP-89 13-JAN-93

Agora um exemplo mais “elegante”: SELECT first_name "First", last_name "Last", hire_date "Date Started" FROM employees; First ---------------------Steven Neena Lex ... 107 rows selected

Last -------------------King Kochhar De Haan

Date Started ------------------------17-JUN-87 21-SEP-89 13-JAN-93

Note que, graças as aspas duplas, além de ser possível exibir os apelidos usando 77

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) maiúsculas e minúsculas, foi possível usar um apelido contendo duas palavras separadas por um espaço (compare com o exemplo anterior). Restringindo o resultado, conforme um critério estabelecido: a cláusula WHERE. A cláusula WHERE permite estabelecer condições para o resultado de uma consulta. Os operadores de comparação que podem ser usados com WHERE são listados na tabela a seguir: Operador

Descrição

Testa uma igualdade

!=, <>

Testa uma diferença

Testa se maior que

Testa se maior ou igual a

Testa se menor que

Testa se menor ou igual a

BETWEEN a AND b

Testa se a condição está dentro de uma faixa, incluindo os valores a e b

Testa uma compatibilidade em uma string, usando caracteres coringa: % para zero ou múltiplos caracteres, ou _ para um só caractere

IN()

Testa se um dado está dentro de uma lista especificada de valores

NOT IN()

Testa se um dado não está dentro de uma lista especificada de valores

IS NULL

Testa se o valor é nulo

IS NOT NULL

Testa se o valor não é nulo

Por uma questão “filosófica”, você não pode testar se um campo é ou não nulo usando os operadores tradicionais de igualdade e diferença. A “lógica” é que o tipo nada (NULL) não pode ser comparado com coisa alguma. Daí operadores especiais para isto. Testando uma condição simples SELECT FROM WHERE

first_name "First", last_name "Last" employees department_id=90;

First -------------------Steven Neena Lex

Last ------------------------King Kochhar De Haan

3 rows selected

Testando múltiplas condições

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) SELECT first_name "First", last_name "Last", SALARY "Salary", COMMISSION_PCT "%" FROM employees WHERE salary >=11000 AND commission_pct IS NOT NULL; First -------------------John Karen Alberto ... 6 rows selected

Last --------------------Russell Partners Errazuriz

Salary -------------------14000 13500 12000

% ----0.4 0.3 0.3

Testando uma compatibilidade em um string SELECT FROM WHERE

first_name "First", last_name "Last" employees last_name LIKE 'Ma%';

First -------------------Jason Steven James ... 6 rows selected

Last ------------------------Mallin Markle Marlow

Testando se um dado está em uma lista SELECT FROM WHERE

first_name "First", last_name "Last", department_id "Department" employees department_id IN (100, 110, 120);

First -------------------John Daniel William ... 8 rows selected

Last ------------------------Chen Faviet Gietz

Department ---------------------100 100 110

Usando funções “built-in” do Oracle O Oracle oferece enorme quantidade de funções prontas que podem ser usadas para conversão, formatação e um sem número de outras possibilidades sobre os dados pesquisados. Vamos mostrar alguns exemplos. 

Funções numéricas

Para arredondar ou truncar valores de uma expressão numérica onde podem aparecer várias casas decimais, é possível TRUNC e ROUND.

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) Arrendondando com ROUND SELECT

first_name "First", last_name "Last", ROUND(salary/30, 2) "Daily Compensation" employees;

FROM

First -------------------Steven Neena Lex ... 107 rows selected

Last ------------------------King Kochhar De Haan

Daily Compensation ---------------------800 566.67 566.67

Usando TRUNC SELECT

first_name "First", last_name "Last", TRUNC(salary/30, 0) "Daily Compensation" employees;

FROM

First -------------------Steven Neena Lex ... 107 rows selected

Last ------------------------King Kochhar De Haan

Daily Compensation ---------------------800 566 566

OBS.: O zero usado no segundo parâmetro de TRUNC informa que você quer zero casas decimais. Se você informar um número negativo, a “truncagem” avançará para a esquerda da vírgula. 

Funções de caractere

Em muitas situações as funções de caractere do Oracle podem ser bastante úteis. A seguir, alguns exemplos. Manipulando maiúsculas e minúsculas SELECT FROM

UPPER(first_name) "First upper", LOWER(last_name) "Last lower", INITCAP(email) "E-Mail" employees;

First upper -------------------STEVEN NEENA LEX

Last lower ------------------------king kochhar de haan

E-Mail ------------------------Sking Nkochhar Ldehaan

Atenção especial para INITCAP, considerando que o e-mail está armazenado com todas

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) as letras em maiúsculas. Concatenando campos do tipo caractere SELECT

e.first_name || ' ' || e.last_name "Name", l.city || ', ' || c.country_name "Location" FROM employees e, departments d, locations l, countries c WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND l.country_id=c.country_id ORDER BY last_name; Name ----------------------------Ellen Abel Sundar Ande Mozhe Atkinson ... 106 rows selected

Location -------------------------------------------Oxford, United Kingdom Oxford, United Kingdom South San Francisco, United States of America

Obtendo substrings SELECT

SUBSTR(first_name, 1, 1) || '. ' || last_name "Name", SUBSTR(phone_number, 5, 8) "Phone" employees;

FROM

Name ---------------------------S. King N. Kochhar L. De Haan ... 107 rows selected

Phone -------123.4567 123.4568 123.4569

Substituindo substrings SELECT

SUBSTR(first_name, 1, 1) || '. ' || last_name "Name", REPLACE(job_id, 'SH', 'SHIPPING') "Job" employees SUBSTR(job_id, 1, 2) = 'SH';

FROM WHERE

Name ----------------------W. Taylor J. Fleaur M. Sullivan ... 20 rows selected

Job -----------------------SHIPPING CLERK SHIPPING_CLERK SHIPPING_CLERK

OBS.: O valor original em job_id é SH_CLERK. 

Funções de data e hora

Agora uma série de exemplos de como se beneficiar das funções Oracle relacionadas a data e hora. 81

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) O número de meses entre duas datas SELECT

e.first_name || ' ' || e.last_name "Name", TRUNC(MONTHS_BETWEEN(j.end_date, j.start_date)) "Months Worked" FROM employees e, job_history j WHERE e.employee_id = j.employee_id ORDER BY "Months Worked"; Name Months Worked ---------------------------------------------- ------------Jonathon Taylor 9 Payam Kaufling 11 Jonathon Taylor 11 ... 10 rows selected

O número de anos entre duas datas SELECT

first_name || ' ' || last_name "Name", (EXTRACT(YEAR from SYSDATE) - EXTRACT(YEAR FROM hire_date)) "Years Employed"

FROM

employees;

Name Years Employed ---------------------------------------------- -------------Steven King 22 Neena Kochhar 20 Lex De Haan 16 ... 107 rows selected

Obtendo o último dia do mês de uma da especificada SELECT FROM

first_name || ' ' || last_name "Name", hire_date "Date Started", LAST_DAY(hire_date) "End of Month" employees;

Name -----------------------------Steven King Neena Kochhar Lex De Haan ... 107 rows selected

Date Started -----------------17-JUN-87 21-SEP-89 13-JAN-93

End of Month ------------------------30-JUN-87 30-SEP-89 31-JAN-93

Adicionando meses a uma data SELECT FROM

first_name || ' ' || last_name "Name", hire_date "Date Started", ADD_MONTHS(hire_date, 6) "New Date" -- adicionando 6 meses employees;

Name -----------------------Steven King Neena Kochhar Lex De Haan ...

Date Started ------------------------17-JUN-87 21-SEP-89 13-JAN-93

New Date ------------------------17-DEC-87 21-MAR-90 13-JUL-93

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) 107 rows selected

Usando TO_CHAR para converter data, usando uma máscara. SELECT FROM

first_name || ' ' || last_name "Name", TO_CHAR(hire_date, 'YYYY DD "de" FMMonth') "Date Started" employees;

Name ---------------------------------------------Steven King Neena Kochhar Lex De Haan ... 107 rows selected

Date Started ----------------1987 17 de Junho 1989 21 de Setembro 1993 13 de Janeiro

Usando TO_CHAR para converter data, usando formatação padrão. SELECT FROM

first_name || ' ' || last_name "Name", TO_CHAR(hire_date, 'DS') "Short Date", TO_CHAR(hire_date, 'DL') "Long Date" employees;

Name --------------------------Steven King Neera Kochhar Lex De Haen ... 107 rows selected

Short Date ---------6/17/1987 9/21/19889 1/13/1993

Long Date ------------------------quarta-feira, 17 de Junho de 1987 quinta-feira, 21 de Sepembro de 1989 quarta-feira, 13 de Janeiro de 1993

Usando TO_CHAR para converter um número em formato moeda. SELECT FROM

first_name || ' ' || last_name "Name", TO_CHAR(salary, 'L99999D99') "Salary" employees;

Name Salary ---------------------------------------------- ----------Steven King R$24000,00 Neena Kochhar R$17000,00 Lex De Haan R$17000,00 ... 107 rows selected



Funções de agrupamento

As funções de agrupamento trabalham sobre um conjunto de linhas em uma tabela, ou na tabela inteira, para dar informações sumarizadas, tais como Mínimos (MIN), Máximos (MAX), Somas (SUM), Médias (AVG), Número de elementos (COUNT), etc. A seguir, uma série de exemplos de uso destas funções no Oracle. Contando o número de linhas que satisfazem uma expressão. 83

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) SELECT

manager_id "Gerente", COUNT(*) "Número de subordinados " FROM employees GROUP BY manager_id; Gerente Número de subordinados --------- ---------------------1 100 14 123 8 ... 19 rows selected

Exibindo informações estatísticas de salários, agrupando por função. SELECT

job_id "Job", COUNT(*) "#", MIN(salary) "Minimum", ROUND(AVG(salary), 0) "Average", MEDIAN(salary) "Median", MAX(salary) "Maximum", ROUND(STDDEV(salary)) "Std Dev" FROM employees GROUP BY job_id ORDER BY job_id; Job # Minimum Average Median Maximum Std Dev ---------- ---------- ---------- ---------- ---------- ---------- ---------AC_ACCOUNT 1 8300 8300 8300 8300 0 AC_MGR 1 12000 12000 12000 12000 0 AD_ASST 1 4400 4400 4400 4400 0 AD_PRES 1 24000 24000 24000 24000 0 AD_VP 2 17000 17000 17000 17000 0 FI_ACCOUNT 5 6900 7920 7800 9000 766 FI_MGR 1 12000 12000 12000 12000 0 HR_REP 1 6500 6500 6500 6500 0 IT_PROG 5 4200 5760 4800 9000 1926 MK_MAN 1 13000 13000 13000 13000 0 MK_REP 1 6000 6000 6000 6000 0 ... 19 rows selected



Cuidado com valores nulos

Digamos que a Diretoria decidiu que além de pagar a costumeira comissão sobre as vendas ao seguinte grupo de funcionários, daria um bônus de R$ 1.000,00 a todos eles, mesmo aos que não tenham um percentual de comissão. SELECT first_name || ' ' || last_name "Name", commission_pct FROM employees WHERE job_id LIKE 'S%_M%' Name COMMISSION_PCT ---------------------------------------------- ---------------------Matthew Weiss Adam Fripp Payam Kaufling Shanta Vollman Kevin Mourgos John Russell 0,4

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) Karen Partners Alberto Errazuriz Gerald Cambrault Eleni Zlotkey

0,3 0,3 0,3 0,2

10 rows selected

Para simplificar, digamos que o lucro sobre as vendas tenha sido R$ 3.000.000,00. Intuitivamente, você faria: SELECT FROM WHERE

first_name || ' ' || last_name "Name", ((commission_pct * 3000000) / 100) + 1000 "Bônus Anual" employees job_id LIKE 'S%_M%';

Mas veja o resultado. Name ---------------------------------------------Matthew Weiss Adam Fripp Payam Kaufling Shanta Vollman Kevin Mourgos John Russell Karen Partners Alberto Errazuriz Gerald Cambrault Eleni Zlotkey

Bônus Anual ----------------------

13000 10000 10000 10000 7000

10 rows selected

O que aconteceu com o bônus de R$ 1.000,00 dos cinco primeiros indivíduos? Quando em uma expressão, por mais complexa que ela seja, aparece um valor NULL, o resultado é sempre NULL. Este é o problema! Para evitar isto, usamos a função NVL, desta forma: SELECT FROM WHERE

first_name || ' ' || last_name "Name", ((NVL(commission_pct,0) * 3000000) / 100) + 1000 "Bônus Anual" employees job_id LIKE 'S%_M%';

Name ---------------------------------------------Matthew Weiss Adam Fripp Payam Kaufling Shanta Vollman Kevin Mourgos John Russell Karen Partners Alberto Errazuriz Gerald Cambrault Eleni Zlotkey

Bônus Anual ---------------------1000 1000 1000 1000 1000 13000 10000 10000 10000 7000

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) 10 rows selected

O que a função NVL faz é avaliar se uma expressão é NULL ou não. Se não for NULL, ela retorna o resultado da própria expressão avaliada. Mas se for NULL, ela retorna o segundo argumento da função. Neste exemplo, caso commission_pct seja NULL, NVL retorna 0 (zero), garantindo que a expressão aritmética retorne o resultado desejado. OBS.: A expressão (NVL(commission_pct * 3000000, 0) / 100) + 1000 daria o mesmo resultado. Usando funções condicionais. O Oracle possui um conjunto de estruturas condicionais (chamadas pela Oracle de funções condicionais) que podem ser usadas junto com SELECT para resolver situações complexas. A seguir, alguns exemplos. 

CASE

Vamos agora imaginar que a Diretoria está planejando o aumento de salários e pretende usar os seguintes critérios: 15% de aumento para quem foi contratado antes de 1990, 10% de aumento para quem foi contratado antes de 1995 e 5% de aumento para quem foi contratado antes de 2000. Para fazer uma projeção, poderíamos usar a função CASE desta forma: SELECT

FROM

first_name || ' ' || last_name "Nome", hire_date "Contratado em", salary "Salário atual", CASE WHEN hire_date < TO_DATE('01-Jan-90') THEN TRUNC(salary*1.15, 0) WHEN hire_date < TO_DATE('01-Jan-95') THEN TRUNC(salary*1.10, 0) WHEN hire_date < TO_DATE('01-Jan-00') THEN TRUNC(salary*1.05, 0) ELSE salary END "Salário proposto" employees;

Nome -------------------------Steven King Neena Kochhar Lex De Haen ... 107 rows selected



Contratado em ---------------17-JUN-87 21-SEP-89 13-JAN-93

Salário atual --------------24000 17000 17000

Salário proposto ---------------27600 19550 18700

DECODE

Vamos agora imaginar uma situação semelhante, só que o critério agora é o cargo. 10% para o cargo PU_CLERK, 15% para o cargo SH_CLERK e 20% para o cargo ST_CLERK. 86

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) Para fazer uma projeção, poderíamos usar a função DECODE desta forma: SELECT

first_name || ' ' || last_name "Nome", job_id "Cargo", salary "Salário atual", DECODE(job_id, 'PU_CLERK', salary * 1.10, 'SH_CLERK', salary * 1.15, 'ST_CLERK', salary * 1.20, salary) "Salário proposto" FROM employees; Nome -------------------------... Alexander Khoo ... Julia Nayer ... Winston Taylor ... 107 rows selected

Cargo ----------------

Salário atual ---------------

Salário proposto ----------------

PU-CLERK

3100

3410

ST_CLERK

3200

3840

SH_CLERK

3200

3680

Como DECODE não é tão óbvia quanto a função CASE, aqui vai a sintaxe de DECODE: DECODE(expressão, Critério1, Resultado1, Critério2, Resultado2, Critério3, Resultado3, ...,..., default)

A expressão é avaliada. Se Critério1 se compatibilizar com expressão, Resultado1 será retornado; Se Critério2 se compatibilizar com expressão, Resultado2 será retornado; Se Critério3 se compatibilizar com a expressão, Resultado3 será retornado; e assim sucessivamente por todos os critérios que existirem. Caso nenhum dos critérios se compatibilize com expressão, o resultado será o default.

DDL – Data Definition Language – Elementos adicionais

Basicamente, o comando CREATE objeto é o mais significativo para definição de dados. Com ele podemos criar tabelas, views, índices e etc. Seria “chover no molhado” gastar algumas páginas com exemplos de criação dos objetos citados. Todavia, há algumas capacidades não muito óbvias que precisam ser mostradas. 

“Clonando” tabelas

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) Todo mundo sabe criar uma tabela com o comando CREATE TABLE. Mas digamos que você queira “clonar” uma tabela. Ao invés de analisar sua estrutura e usar um enorme comando CREATE, poderíamos fazer assim: CREATE TABLE TabelaClone AS SELECT * FROM TabelaOriginal;

A TabelaClone é uma cópia de TabelaOriginal, incluindo seus dados. Entretanto, esta maneira de “clonar” uma tabela não faz uma réplica 100% da original: ficam de fora as “constraints” de PK e FK, índices, concessões (GRANTs) e etc. Basicamente, esta forma “clona” a estrutura, dados e as “constraints” de atributos, tais como CHECK. Se você quiser “clonar” a tabela mas sem trazer seus dados, basta criar uma condição (WHERE) falsa. Ex.: CREATE TABLE TabelaClone AS SELECT * FROM TabelaOriginal WHERE 1=2;

Se você quiser “clonar” a tabela e trazer alguns dados, basta criar as condições necessárias. Ex.: CREATE TABLE EmployeesClone AS SELECT * FROM employess WHERE JOB_ID = ‘ST_CLERK’;

Para trazer apenas aqueles cujo job_id seja ST_CLERK. E se você quiser “clonar” parcialmente a tabela, sem trazer todos os atributos, é só colocar os atributos que quiser na linha do SELECT. Ex.: CREATE TABLE EmployeesClone AS SELECT Employee_ID, FirstName, LastName FROM employees;

Enfim, é só usar a imaginação, conforme a necessidade. 

SEQUENCES

Algumas pessoas sentem falta do tipo AutoIncremento no Oracle. Aliás, este “tipo” é uma aberração criada por alguns produtores de SGBDs. Todavia, a necessidade de inserir dados 88

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) seqüenciais em tabelas existe. É o caso das chaves primárias, por exemplo. No schema HR podemos encontrar três sequences: departments_seq, employees_seq, and locations_seq, destinadas a gerarem códigos para department_id, employees_id, i locations_id, respectivamente. Criar uma sequence é muito simples. O comando básico é: CREATE SEQUENCE NomeSeq START WITH nInicial INCREMENT BY nIncremento;

Onde NomeSeq é o nome da seqüência, nInicial é o número inicial da seqüência e nIncremento é de quanto em quanto será o incremento. Uma seqüência é utilizada acessando duas propriedades ou pseudo-colunas (segundo a nomenclatura da Oracle): NEXTVAL e CURRVAL. NEXTVAL traz como resultado o próximo número na seqüência e CURRVAL o número atualmente na seqüência (o último gerado). Para que você acesse CURRVAL em uma seção, é preciso que NEXTVAL tenha sido acessada ao menos uma vez. EXERCÍCIO: Vamos conectar no schema HR e criar uma seqüência, para fazermos uns testes. Crie a seguinte seqüência: CREATE SEQUENCE SeqTeste START WITH 5 INCREMENT BY 5;

Agora use a seqüência: SELECT SeqTeste.NEXTVAL FROM DUAL; NEXTVAL ---------------------5 1 rows selected SELECT SeqTeste.CURRVAL FROM DUAL; CURRVAL ---------------------5 1 rows selected SELECT SeqTeste.NEXTVAL FROM DUAL;

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) NEXTVAL ---------------------10 1 rows selected SELECT SeqTeste.NEXTVAL FROM DUAL; NEXTVAL ---------------------15 1 rows selected

FIM DO EXERCÍCIO: Para apagar um seqüência, usamos o comando DROP SEQUENCE NomeSeq; Apague a seqüência SeqTeste. Uma vez que você tenha criado uma seqüência para usá-la como gerador de valores para o campo chave de uma tabela, por exemplo, você pode utilizá-la assim: INSERT INTO TabelaTal (CampoChave, Campo1, Campo2, ...) VALUES (SeqTabelaTal.NEXTVAL, Valor1, Valor2, ...);

Quando estudarmos triggers, veremos uma maneira de fazer com que a seqüência seja chamada automaticamente, sempre que uma linha for inserida na tabela. Assim, um desenvolvedor nem precisa se preocupar em enviar o valor do campo chave para a tabela. Por exemplo, o comando anterior poderia ser assim: INSERT INTO TabelaTal (Campo1, Campo2, ...) VALUES (Valor1, Valor2, ...);

Embora não tenha sido enviado um valor para o campo chave, o trigger se encarregaria de colocar um valor no campo chave, antes de a inclusão dos valores na tabela ser efetivada.

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) Exercício de revisão Responda as questões a seguir: 1) Qual a diferença básica entre os tipos CHAR e VARCHAR2? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 2) Qual destes tipos é o mais adequado para armazenar CPF ou CNPJ, por exemplo? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 3) Que tipo de dado deve ser usado para armazenar textos gigantes? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 4) Uma vez que você tenha criado um campo com o tipo NUMBER(10,2), qual o maior número que poderá ser armazenado neste campo? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 5) Para que servem os parâmetros NLS_DATE_FORMAT e NLS_TERRITORY? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 6) Se você precisar trabalhar com frações de segundo, qual o tipo de dados que deve ser usado?

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 7) Qual a forma correta de verificar se um campo é nulo? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 8) O que é uma função "buit-in"? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 9) Qual cuidado deve ser tomado com expressões envolvendo valores nulos? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 10) Por quê? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Capítulo 4 – Oracle SQL – Data Definition and Manipulation Language (DDL e DML) RESPOSTAS 1) CHAR é de tamanho fixo e VARCHAR2 é de tamanho variável 2) CHAR 3) CLOB 4) 99999999,99 5) Ambos definem o formato da data. O primeiro define de forma explícita e o segundo de forma implícita. 6) TIMESTAMP 7) Usar o operador IS NULL 8) Uma função pronta para uso, que é parte integrante do Oracle. 9) Usar a função NVL 10) Por que toda expressão envolvendo um valor nulo tem como resultado um valor nulo.

Capítulo 5 – Oracle PL/SQL – Stored Procedures

Capítulo 5 – Oracle PL/SQL – Stored Procedures Neste capítulo iremos estudar uma das linguagens que podem ser usadas para escrever procedimentos armazenados (stored procedures) no banco de dados. As stored procedures são uma ferramenta muito poderosa e deveriam ser utilizadas amplamente pelos desenvolvedores. Em uma arquitetura cliente / servidor, ao invés de escrever códigos rebuscados para processar dados na camada de interface, o local mais adequado é o banco de dados. Desta forma, não só as regras de negócio ficam próximas e unificadas no banco de dos dados, como também diminui o tráfego na rede. Outra vantagem é que uma vez criado e testado o código, ele pode ser utilizado nas mais variadas situações de interfaces, estejam elas numa arquitetura cliente / servidor, usando uma aplicação de interface gráfica (GUI), ou até mesmo na Web. A PL/SQL é uma linguagem criada pela Oracle para seu banco de dados. Além de você poder usar qualquer comando da SQL em uma stored procedure, a PL/SQL oferece um dialeto muito parecido com a linguagem Pascal. De fato, PL/SQL é muito fácil de aprender. Visão geral da PL/SQL – Declaração de PROCEDURES Os blocos de código que você pode criar com PL/SQL são procedures, functions e packages. Procedures e functions são códigos stand-alone, ou seja, blocos de código isolados que você cria no banco de dados e que você pode chamar quando precisar. Um package (pacote) é um conjunto de procedures e functions encapsulados numa estrutura. É altamente recomendável que você organize seus blocos de código em categorias e os escreva dentro de pacotes, ao invés de espalhados pelo banco de dados. Você poderia, por exemplo, criar um package chamado pckg_folha, contendo inúmeras procedures e functions relacionadas ao processamento de uma folha de pagamento. Assim, uma aplicação poderia organizadamente chamar estes procedures e functions via pacote, tipo: pckg_folha.ProcessaHoraExtra ou pckg_folha.CalculaAbonoDeFerias( employee_id ) e etc. Mas, como para fazer uma casa, primeiro você tem que fazer os tijolos, vamos começar estudando como criar procedures e functions. 94

Capítulo 5 – Oracle PL/SQL – Stored Procedures 

PROCEDURES

A sintaxe básica para a criação de um procedimento é CREATE OR REPLACE procedure_name(arg1 data_type, ...) AS BEGIN .... END procedure_name;

Se você precisar declarar variáveis locais em um procedimento, o local é entre a cláusula AS e o BEGIN do procedimento. CREATE OR REPLACE procedure_name(arg1 data_type, ...) AS n_score NUMBER(1,0); -- a variable n_weight NUMBER; -- a variable max_score CONSTANT NUMBER(1,0) := 9; -- a constant limit check max_weight CONSTANT NUMBER(8,8) := 1; -- a constant limit check BEGIN .... END procedure_name;

Para não ficarmos aprendendo na base do conta-gotas, vamos criar uma situação que nos permita aprender algumas coisas úteis. Vamos tomar por base a tabela regions, do schema HR. Seu conteúdo original é: select * from regions; REGION_ID ---------------------1 2 3 4

REGION_NAME ------------------------Europe Americas Asia Middle East and Africa

4 rows selected

Vamos criar um procedimento para inserir dados em regions! Como regions tem dois atributos, nosso procedimento precisará de dois argumentos: o valor para region_id e o valor para REGION_NAME. Não existe no schema HR uma sequence para a tabela regions, o que é bastante apropriado para nosso exercício  Podemos criar nosso procedimento assim: CREATE OR REPLACE PROCEDURE P_NEWREGION( RegID IN NUMBER, RegName IN VARCHAR2) AS BEGIN INSERT INTO REGIONS VALUES (RegID, RegName); COMMIT; END P_NEWREGION; /

Capítulo 5 – Oracle PL/SQL – Stored Procedures OBS.: Note a barra (/) no final do comando. Ela significa “compile agora”. O IN presente nas declarações de variáveis informa que as variáveis são parâmetros de entrada. Veja na figura abaixo o resultado de copiar e colar este código no SQL*Plus, conectado como HR.

Agora veja na figura abaixo como executar o procedimento via SQL*Plus e o resultado.

Note que usamos o comando exec P_NEWREGION( 5, ‘Brasil!’); Este mesmo procedimento poderia ter sido criado através do SQL Developer. Veja na 96

Capítulo 5 – Oracle PL/SQL – Stored Procedures figura a seguir o processo de criação deste procedimento pelo SQL Developer.

OBS.: Neste caso, a barra de execução (/) é dispensável e pressionamos F5 (Executar script). No SQL Developer, nós podemos executar um procedimento da mesma forma que fizemos no SQL*Plus, usando o comando exec. Mas o SQL Developer é um ambiente muito mais “amigável” para escrever e testar código PL/SQL do que o SQL*Plus, por isto iremos utilizá-lo daqui pra frente. Agora que o procedimento está criado, podemos verificar sua existência na árvore de objetos do schema de HR. Expanda a seção Procedures, para visualizar as procedures de HR. OBS.: Caso não esteja vendo P_NEWREGION, clique com o botão direito sobre a palavra Procedures e selecione Atualizar. Veja P_NEWREGION na figura abaixo.

Clicando no nome de um procedimento, o SQL Developer exibe seu código. Veja na figura 97

Capítulo 5 – Oracle PL/SQL – Stored Procedures abaixo o código de P_NEWREGION.

Clicando no botão Editar, o SQL Developer exibe outra aba, permitindo a edição, compilação, execução e testes do procedimento. Veja a figura abaixo.

Atenção especial para os botões Run

e Compile

Clicando no botão Run, podemos rodar o procedimento ou função que estiver sendo editado. O SQL Developer mostrará uma tela como a seguir:

Capítulo 5 – Oracle PL/SQL – Stored Procedures

Observe o quadro intitulado Bloco PL/SQL. Neste quadro o SQL Developer cria um bloco de código preparatório para a execução do procedimento ou função sendo testado. Quaisquer eventuais argumentos (parâmetros) necessários devem ser definidos neste ponto. Observem neste bloco de código as duas linhas logo após o BEGIN. Eles são os dois argumentos do nosso procedimento e precisam ser definidos. Vejam na figura a seguir como fizemos as modificações necessárias, antes de executarmos o teste.

Capítulo 5 – Oracle PL/SQL – Stored Procedures

Clicando em Ok, o SQL Developer executa o procedimento e mostra a seguinte mensagem.

Como não apareceu nenhuma mensagem de erro, é provável que tudo deu certo. Basta fazer um select em regions, para conferir o resultado. 

DECLARANDO VARIÁVEIS COM A MESMA ESTRUTURA DAS COLUNAS DE UMA TABELA

A nossa procedure de exemplo possui dois argumentos que são valores a serem 100

Capítulo 5 – Oracle PL/SQL – Stored Procedures passados aos campos da tabela regions. Esta situação é muito comum quando se desenvolve procedures. Para declarar os tipos de variáveis corretamente, tivemos que olhar a estrutura de regions, para ver os tipos de dados envolvidos. SE por uma razão qualquer os tipos de dados envolvidos na tabela forem modificados, nossa procedure pode ficar inválida. Para evitar que isto aconteça, e até mesmo para facilitar a declaração de variáveis que precisam ter o mesmo tipo de dados que campos de uma tabela, o Oracle oferece os qualificadores especiais %TYPE e %ROWTYPE. %TYPE fornece o tipo de dado de uma coluna em uma tabela ou o tipo de dado de outra variável. %ROWTYPE fornece uma estrutura tipo RECORD que é idêntica à estrutura de uma linha (registro) de uma tabela. Com base neste conhecimento, nós podemos mudar a declaração de variáveis em nossa procedure desta forma: ...( RegID IN regions.region_id%TYPE, RegName IN regions.region_name%TYPE)...

regions.region_id%TYPE fornece o tipo de dado da coluna region_id de regions e regions.region_name%TYPE fornece o tipo de dado da coluna regiona_name. Estruturas de controle de fluxo Para que possamos nos aprofundar na criação de procedures e functions, precisamos conhecer a sintaxe das estruturas de controle de fluxo da PL/SQL. Basicamente, todo código precisa resolver questões de controle de fluxo tal como mostrado na figura a seguir.

101

Capítulo 5 – Oracle PL/SQL – Stored Procedures 

Controle de Seleção Condicionada

IF...THEN...ELSE é a estrutura que permite selcionar uma lista de comandos, condicionalmente. Você também pode selecionar baseado em múltiplas condições usando IF...THEN...[ELSEIF]...ELSE. A sintaxe básica é a seguinte: IF condition_1 THEN ...; ELSIF condition_2 THEN ...; ELSE ...; END IF;



-- opcional -- opcional

FUNCTIONS

Imagine que a empresa tenha uma regra de que todo funcionário precisa ser avaliado duas vezes ao ano, nos primeiros dez anos de trabalho. A partir desta data, ele só precisa ser avaliado uma vez ao ano. Para este propósito, vamos criar uma função chamada eval_frequency com o seguinte código: CREATE OR REPLACE FUNCTION eval_frequency (pEmployee_id IN employees.employee_id%TYPE) RETURN PLS_INTEGER AS dHire_date employees.hire_date%TYPE; -- start of employment dToday employees.hire_date%TYPE; -- today's date iEval_freq PLS_INTEGER; -- frequency of evaluations BEGIN SELECT SYSDATE INTO dToday FROM DUAL; -- set today's date SELECT e.hire_date INTO dHire_date -- determine when employee started FROM employees e WHERE e.employee_id = pEmployee_id; IF((dHire_date + (INTERVAL '120' MONTH)) < dToday) THEN iEval_freq := 1; ELSE iEval_freq := 2; END IF; RETURN iEval_freq; END eval_frequency;

Agora que criamos a função, podemos utilizá-la mais ou menos assim: SELECT first_name || ' ' || last_name "Nome", eval_frequency( employee_id ) "Frequência de avaliação anual" FROM employees; Nome Frequência de avaliação anual ---------------------------------------------- ----------------------------Donald OConnell 1

102

Capítulo 5 – Oracle PL/SQL – Stored Procedures Douglas Grant Jennifer Whalen ... 107 rows selected



2 1

Repetições ou Iterações

As estrururas de controle de repetição de fluxo são FOR...LOOP, WHILE...LOOP e LOOP...EXIT WHEN. Vamos experimentá-las. Executando um bloco de código anônimo O Oracle permite que você execute um bloco de código sem precisar criar uma função ou procedimento. Vimos isto sendo feito pelo SQL Developer quando testamos o procedimento P_NEWREGION. DECLARE REGID NUMBER; REGNAME VARCHAR2(25); BEGIN REGID := 5; REGNAME := 'Brasil!'; P_NEWREGION( REGID => REGID, REGNAME => REGNAME ); END;

Isto se chama bloco de código anônimo. Vamos tirar proveito deste recurso para praticarmos sem a necessidade de criar um procedimento ou função somente para testar comandos. Você pode fazer isto tanto no SQL*Plus, quanto no SQL Developer, sendo que neste último você deve acionar a opção Executar script (F5) ao invés de Executar instrução (F9). FOR...LOOP Para treinar a sintaxe de FOR...LOOP, digite e execute o seguinte bloco anônimo: DECLARE I INTEGER; BEGIN FOR I IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE( 'I = ' || I ); END LOOP; END; /

DBMS_OUTPUT é um Package “built-in” do Oracle e oferece inúmeras funções e 103

Capítulo 5 – Oracle PL/SQL – Stored Procedures procedimentos prontos para uso. O procedimento PUT_LINE envia uma linha para a saída padrão do sistema. OBS.: Caso o SQL*Plus ou Developer não tenha exibido a saída de PUT_LINE, digite o comando set serveroutput on e repita a operação. WHILE...LOOP Para executar a mesma operação usando WHILE ao invés de FOR, usaríamos: DECLARE I INTEGER; BEGIN I := 1; WHILE I <= 5 LOOP DBMS_OUTPUT.PUT_LINE( 'I = ' || I ); I := I + 1; END LOOP; END; /

LOOP...EXIT WHEN Agora usando LOOP...EXIT WHEN: DECLARE I INTEGER; BEGIN I := 1; LOOP DBMS_OUTPUT.PUT_LINE( 'I = ' || I ); I := I + 1; EXIT WHEN I > 5; END LOOP; END; /

Boas práticas I - subrotinas locais A PL/SQL é uma linguagem estruturada. Isto já deu para perceber. Um bom desenvolvedor deve procurar manter seu código claro, comentado, fácil de ler e interpretar. Uma boa prática de programação é que os algoritmos não devem ter centenas ou milhares de linhas, com dezenas de loops aninhados, fazendo um sem número de coisas alheias ao algoritmo. O melhor é que existam poucas linhas fazendo a proposta do algoritmo e que as tarefas secundárias sejam feitas em outra parte. A PL/SQL permite que você crie subprogramas dentro de um procedimento ou função, sem a necessidade de ter que criar explicitamente (dando um nome) outra função ou 104

Capítulo 5 – Oracle PL/SQL – Stored Procedures procedimento. Se durante a elaboração de um algoritmo você precisar de uma tarefa secundária, ao invés de escrever toda a complexidade das tarefas secundárias no fluxo normal do algoritmo, você cria um subprograma para isto. Veja um exemplo a seguir: 

Um algoritmo sem subprogramas

DECLARE -- declaring buffer variables for cursor data rEmp employees%ROWTYPE; conta PLS_INTEGER; BEGIN -- set serveroutput on, se quiser ver o resultado! -- Faz uma listagem dos empregados de 101 a 201 DBMS_OUTPUT.PUT_LINE('Listando os empregados de 101 a 201'); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID FIRST_NAME LAST_NAME'); DBMS_OUTPUT.PUT_LINE('----------- -------------------- --------------------'); FOR conta IN 101..201 LOOP SELECT employee_id, first_name, last_name INTO rEmp.employee_id, rEmp.first_name, rEmp.last_name FROM employees WHERE employee_id = conta; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE( RPAD( rEmp.employee_id, 12, ' ') || RPAD( rEmp.first_name, 22, ' ') || RPAD( rEmp.last_name, 20, ' ') ); END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------'); END; /



O mesmo algoritmo com subprogramas

Agora compare com este código DECLARE -- declaring buffer variables for cursor data rEmp employees%ROWTYPE; conta PLS_INTEGER; --******************************************** --*************** SUBPROGRAMAS *************** --********************************************

105

Capítulo 5 – Oracle PL/SQL – Stored Procedures PROCEDURE GeraCabecalho IS BEGIN DBMS_OUTPUT.PUT_LINE('Listando os empregados de 101 a 201'); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID FIRST_NAME LAST_NAME'); DBMS_OUTPUT.PUT_LINE('----------- -------------------- -------------------'); END; PROCEDURE GeraLinha( id employees.employee_id%TYPE, FName employees.first_name%TYPE, LName employees.last_name%TYPE ) IS BEGIN DBMS_OUTPUT.PUT_LINE( RPAD( id, 12, ' ') || RPAD( FName, LName, 20, ' ') ); END;

22, ' ') || RPAD(

PROCEDURE GeraRodape IS BEGIN DBMS_OUTPUT.PUT_LINE('----------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('Listagem finalizada.'); END; --************************************************** --*************** PROGRAMA PRINCIPAL *************** --************************************************** BEGIN -- set serveroutput on, se quiser ver o resultado! -- Faz uma listagem dos empregados de 101 a 201 GeraCabecalho; FOR conta IN 101..201 LOOP SELECT employee_id, first_name, last_name INTO rEmp.employee_id, rEmp.first_name, rEmp.last_name FROM employees WHERE employee_id = conta; IF SQL%FOUND THEN -- Testa se o SELECT trouxe algo GeraLinha( rEmp.employee_id, rEmp.first_name, rEmp.last_name ); END IF; END LOOP; GeraRodape; END; /

Observando agora o algoritmo (programa principal), fica mais fácil enxergar o que ele faz: uma listagem de empregados. As tarefas secundárias de gerar cabeçalho, imprimir as linhas 106

Capítulo 5 – Oracle PL/SQL – Stored Procedures com uma formatação e gerar um rodapé, foram colocadas em outra parte. Desta forma, o algoritmo principal ficou mais simples de entender. Se outro desenvolvedor tiver que fazer manutenção neste procedimento de listar empregados, ele vai perceber rapidamente como ele funciona, apenas olhando o bloco principal. Se sentir necessidade de mais detalhes, ele vai analisar mais de perto os subprogramas. Claro que num exemplo pequeno como este não dá para perceber muito as vantagens. Parece até que o programa ficou bem maior! Mas imagine que as subtarefas de gerar cabeçalho e rodapé fossem bem mais complexas, e a formatação da linha fosse bem mais sofisticada. Mesmo que a quantidade de linhas nos subprogramas aumentasse vertiginosamente, o programa principal continuaria tão enxuto quanto está agora. NOTA: Neste exemplo apareceram algumas instruções novas, como SELECT...INTO, DBMS_OUTPUT.PUT_LINE, RPAD e SQL%FOUND. No capítulo 7 você irá descobrir como elas funcionam.

107

Capítulo 5 – Oracle PL/SQL – Stored Procedures Exercício de revisão Responda as questões a seguir: 1) O que é uma "stored procedure"? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 2) Qual a linguagem usada no Oracle para se escrever stored procedures? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 3) Qual a estrutura sintática básica de uma stored procedure?

4) Se você precisar de variáveis além dos argumentos da stored procedure, onde elas dever ser declaradas? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 5) O que é um package? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 108

Capítulo 5 – Oracle PL/SQL – Stored Procedures 6) Para que servem os qualificadores especiais %TYPE e %ROWTYPE? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 7) Qual a diferença básica entre uma PROCEDURE e uma FUNCTION? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 8) O que é um bloco de código anônimo? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 9) O que é um subprograma e qual a vantagem em utilizá-los? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

109

Capítulo 5 – Oracle PL/SQL – Stored Procedures RESPOSTAS 1) É um programa escrito dentro do banco de dados. 2) PL/SQL 3) CREATE OR REPLACE procedure_name(arg1 data_type, ...) AS BEGIN .... END procedure_name;

4) Entre as cláusulas AS/IS e BEGIN 5) Um package (pacote) é um conjunto de procedures e functions encapsulados numa estrutura 6) Facilitar a declaração de variáveis com os mesmos tipos de dados de uma coluna ou de uma linha, respectivamente. 7) É que uma FUNCTION retorna um valor que é atribuido à uma variável. 8) É um bloco de código sem nome, ou seja, declarado e executado ao mesmo instante, sem a necessidade de se atribuir a ele um nome de procedure. 9) Um subprograma é um procedimento ou função declarado dentro de outro. A vantagem é que ajuda a tornar um código mais estruturado, dividindo-o em subtarefas.

110

Capítulo 6 – Oracle PL/SQL – Packages

Capítulo 6 – Oracle PL/SQL – Packages Neste capítulo iremos estudar a criação de packages. Os packages são uma forma elegante e profissional de organizar funções e procedimentos em um pacote. Agrupar códigos por categorias funcionais é uma técnica que além de permitir maior organização e documentação dos sistemas, facilita muito sua reutilização. Além disso, o uso de procedimentos e funções “standalone” é limitado aos tipos de dados escalares, ou seja, eles só podem receber e retornar parâmetros baseados nos tipos básicos de dado. Você não pode, por exemplo, usar estruturas tipo RECORD, CURSORES e outras mais sofisticadas. Estrutura básica de um Package Um package é composto de duas partes: package specification e package body. A seção specification é a interface do package. Nela você declara types, variables, constants, exceptions, cursors, functions e procedures, que podem ser referenciadas de fora do package. A seção body é onde você implementa o código do package. A seguir, a especificação padrão de um package: CREATE OR REPLACE PACKAGE package_name AS type definitions for records, index-by tables constants exceptions global variable declarations procedure procedure_1(arg1, ...); ... function function_1(arg1,...) return datat_ype; ... END package_name;

OBS.: Qualquer constante, tipo ou variável declarada na seção de especificação é pública no package, ou seja, poderão ser usadas por qualquer procedure ou function do package. E a seguir o package body padrão: CREATE OR REPLACE PACKAGE BODY package_name AS PROCEDURE procedure_1(arg1,...) IS BEGIN ... EXCEPTION ... END procedure_1; ... FUNCTION function_1(arg1,...) RETURN data_type IS result_variable data_type

111

Capítulo 6 – Oracle PL/SQL – Packages BEGIN ... RETURN result_variable; EXCEPTION ... END function_1; ... END package_name;

OBS.: É privada toda constante, tipo ou variável que for declarada dentro de uma procedure ou function na seção body. Ou seja, só pode ser usada dentro da procedure ou function que a declarou. Criando um package simples EXERCÍCIO: Para exercitar, vamos criar um package simples, com as seguintes características: Nome do Package: pckg_HR FUNCTION strNNumero5: Dado um número inteiro de cinco dígitos, retorna o string formatado NNNNN-D onde NNNNN é a parte numérica completada com zeros à esquerda e D é o dígito verificador módulo 11. (usada para gerar seqüências de NossoNumero para boletos  ) FUNCTION fPronome: dados os sexo (M / F) e estado civil (C – Casado / <>C – Não casado), retorna a forma de tratamento Sra. ou Srta. Para F e Sr. para M. As declarações das duas funções são como mostrado a seguir: FUNCTION strNNumero5( N IN INTEGER ) RETURN CHAR; FUNCTION f_pronome( pin_sexo pin_estado_civil ) RETURN VARCHAR2;

IN IN

CHAR, CHAR

OBS.: Curiosamente, não é preciso (nem possível) especificar o tamanho dos dados, sejam numéricos ou caractere, na declaração de parâmetros. É como se fossem ponteiros. É muito mais prático usar o SQL Developer para criar packages, embora seja possível criá-los também através do SQL*Plus. Para criar um package no SQL Developer, expanda a árvore de objetos do schema, para visualizar a lista de objetos, clique com o botão direito na seção package, e selecione Novo package... 112

Capítulo 6 – Oracle PL/SQL – Packages Feito isto, o SQL Developer mostrará um diálogo solicitando o nome do package e em qual schema ele será criado. Digite o nome do package e clique em Ok, como mostrado na figura abaixo.

Depois que você clica em Ok, o SQL Developer abre uma nova aba, com a declaração do package, como mostrado na figura abaixo.

Acrescente as linhas de declaração de nossas funções e clique em compilar. O resultado deve ser sem erros, como mostrado na figura abaixo.

113

Capítulo 6 – Oracle PL/SQL – Packages Note a mensagem “PCKG_HR Compilado” e que ele aparece agora na árvore de objetos. Clicando com o botão direito sobre o nome do package na árvore de objetos, o SQL Developer exibe um menu de contexto com algumas opções. Uma delas nos interessa agora: Criar Corpo. Veja a figura abaixo.

Selecione criar corpo e o SQL Developer exibirá uma nova aba com o corpo (body) do package, como mostrado na figura abaixo.

Note que o SQL Developer criou o “esqueleto” do corpo do package. “Só” falta agora implementar o código das funções. 

strNNumero5 114

Capítulo 6 – Oracle PL/SQL – Packages Apague a linha RETURN NULL de strNNumero5 e em seu lugar escreva o seguinte código: strNum := REPLACE(LPAD(TO_CHAR(N),5, ' '), ' ', '0'); N5 N4 N3 N2 N1

:= := := := :=

SUBSTR(strNum,5,1); SUBSTR(strNum,4,1); SUBSTR(strNum,3,1); SUBSTR(strNum,2,1); SUBSTR(strNum,1,1);

T1 T2 T3 T4 T5

:= := := := :=

TO_NUMBER(N5) TO_NUMBER(N4) TO_NUMBER(N3) TO_NUMBER(N2) TO_NUMBER(N1)

T6 T7 T8 T9 TA TB

:= := := := := :=

5 2 1 8 3 1

* * * * * *

4 3 2 9 8 7

+ + + + + +

* * * * *

9; 8 + 7 + 6 + 5 +

T1; T2; T3; T4;

T5; T6; T7; T8; T9; TA;

RESTO := TB MOD 11; IF RESTO = 10 Then Digito := 'X'; Else Digito := TO_CHAR(RESTO); END IF; RETURN strNum || DIGITO;

Note que estamos usando algumas variáveis locais. Elas precisam ser declaradas entre a cláusula AS e BEGIN da função strNNumero5. A seguir, a declaração destas variáveis: N1 CHAR(5); N2 CHAR(5); N3 CHAR(5); N4 CHAR(5); N5 CHAR(5); Digito CHAR(5); strNum CHAR(5); T1 INTEGER; T2 INTEGER; T3 INTEGER; T4 INTEGER; T5 INTEGER; T6 INTEGER; T7 INTEGER; T8 INTEGER; T9 INTEGER; TA INTEGER; TB INTEGER; RESTO INTEGER;

Na figura a seguir, mostramos uma visão parcial da implementação de strNNumero5. Dá para ver onde as variáveis foram declaradas e as primeiras instruções do corpo da função.

115

Capítulo 6 – Oracle PL/SQL – Packages

Uma vez que tudo tenha sido digitado corretamente, basta clicar no botão compilar e conferir o resultado em “Mensagens – Log”. Se não tiver nenhum erro, a mensagem será um simples “PCKG_HR Body Compilado”. Veja na figura a seguir uma compilação bem sucedida.



fPronome

O código completo de fPronome é como o mostrado a seguir. FUNCTION f_pronome( pin_sexo IN CHAR, pin_estado_civil IN CHAR ) RETURN VARCHAR2 AS --String v_result VARCHAR2 (4000) := ''; BEGIN v_result := '';

116

Capítulo 6 – Oracle PL/SQL – Packages --FEMININO IF (NVL (pin_sexo, ' ') = 'F') THEN IF (NVL (pin_estado_civil, ' ') = 'C') THEN v_result := 'Sra. '; ELSE v_result := 'Srta. '; END IF; --MASCULINO ELSE v_result := 'Sr. '; END IF; RETURN v_result; END f_pronome;

Faça da mesma forma como fizemos com strNNumero5 e implemente o corpo da função f_pronome. 

Testando o package

select pckg_HR.strNNumero5( 12342 ) "NossoNumero" from dual; NossoNumero -----------12342X 1 rows selected select pckg_HR.f_pronome( 'F', '') "Forma de Tratamento" from dual; Forma de Tratamento -----------------------Srta. 1 rows selected select pckg_HR.f_pronome( 'F', 'C') "Forma de Tratamento" from dual; Forma de Tratamento -----------------------Sra. 1 rows selected select pckg_HR.f_pronome( 'M', '') "Forma de Tratamento" from dual; Forma de Tratamento -----------------------Sr. 1 rows selected

117

Capítulo 6 – Oracle PL/SQL – Packages

Basicamente, este é o tipo de atividade envolvida na criação de uma package. Pois complexo que seja o package, as tarefas consistem em declarar sua interface e implementar o código. A diferença está no esforço intelectual para implementar uma lógica mais complexa. Parâmetros default A função f_pronome espera dois parâmetros e embora o segundo seja significativo apenas quando você informa ‘C’, para casado ou casada, não é possível deixar de informálo. Tem situações onde há certa conveniência em definir um parâmetro como default. Em tais casos, a chamada da função ou procedimento não obriga que ele seja informado. Para exemplificar, vamos modificar a função f_pronome para que ela retorne a forma de tratamento de duas formas: resumida, como é feito agora, ou por extenso. Para isto, vamos adicionar um terceiro parâmetro, que poderá receber os valores R, para que a função retorne a forma resumida e será o default ou E, para retornar a forma de tratamento por extenso. Para isto, vamos mudar a declaração e o código de f_pronome, adicionando o parâmetro default que definimos e as instruções necessárias para o tratamento do novo parâmetro. A declaração deverá ficar assim, tanto no package body, quanto no package specification: FUNCTION f_pronome( pin_sexo pin_estado_civil pin_forma ) RETURN VARCHAR2...

IN IN IN

CHAR, CHAR, CHAR DEFAULT 'R'

Agora acrescente o seguinte código, antes da última linha da função f_pronome. -- Tratando o parâmetro pin_forma IF pin_forma = 'E' THEN CASE v_result WHEN 'Sra.' THEN v_result := 'Senhora'; WHEN 'Srta.' THEN v_result := 'Senhorita'; ELSE v_result := 'Senhor'; END CASE; END IF; RETURN v_result;

118

Capítulo 6 – Oracle PL/SQL – Packages Desta forma, podemos continuar chamando do mesmo jeito f_pronome: select pckg_HR.f_pronome( 'F', 'C') "Forma de Tratamento" from dual; Forma de Tratamento -----------------------Sra. 1 rows selected

Como pin_forma tem um valor default, não é obrigatório informá-lo. Mas se precisarmos da forma de tratamento por extenso, basta enviar um valor: select pckg_HR.f_pronome( 'F', 'C', 'E') "Forma de Tratamento" from dual; Forma de Tratamento -----------------------Senhora 1 rows selected

Como pudemos ver, para que um parâmetro se torne default basta que você lhe atribua um valor na declaração, colocando a cláusula DEFAULT e um valor logo após a informação do tipo de dado. Existe outra forma igualmente válida para atribuir um parâmetro default. Basta usar o operador de atribuição, no lugar da cláusula DEFAULT. Exemplo: pin_forma

CHAR := 'R'

Código privado ao package body Um package body pode conter funções que não façam parte da package specification. Isto ocorre quando você precisa de sub-rotinas para lhe auxiliar numa tarefa mais complexa, mas estas sub-rotinas não interessam ao restante do sistema. Para exemplificar isto, vamos retirar de f_pronome o código que trata o parâmetro pin_forma

colocá-lo

uma

sub-rotina.

Vamos

chamar

esta

sub-rotina

f_pronome_extenso, com o seguinte código: FUNCTION f_pronome_extenso ( pFormaResumida IN CHAR ) RETURN VARCHAR2 AS BEGIN CASE pFormaResumida WHEN 'Sra.' THEN RETURN 'Senhora'; WHEN 'Srta.' THEN RETURN 'Senhorita'; ELSE RETURN 'Senhor'; END CASE; END;

119

Capítulo 6 – Oracle PL/SQL – Packages E deverá ser implementada logo no início do package body. Agora podemos simplificar f_pronome, alterando as linhas que tratam do novo parâmetro. Deve ficar assim: -- Tratando o parâmetro pin_forma IF pin_forma = 'E' THEN v_result := f_pronome_extenso( v_result ); END IF;

Por não ser declarada em package specification, f_pronome_extenso é visível apenas dentro do package body e não poderá ser usada de nenhum outro lugar.

120

Capítulo 6 – Oracle PL/SQL – Packages Exercício de revisão Responda as questões a seguir: 1) Quais as partes de um package e para que serve cada uma delas? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 2) Qual a diferença entre uma variável declarada na seção de especificação e outra declarada no corpo (body) do pacote? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 3) Como deixar um parâmetro recebendo um valor default? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 4) Você pode criar uma função ou um procedimento no "package body" sem que tenham sido declarados no "package specification"? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 5) Se isto for possível, qual a implicação? ______________________________________________________________________

121

Capítulo 6 – Oracle PL/SQL – Packages ______________________________________________________________________ ______________________________________________________________________

122

Capítulo 6 – Oracle PL/SQL – Packages RESPOSTAS 1) Um package é composto de duas partes: package specification e package body. A seção specification é a interface do package. Nela você declara types, variables, constants, exceptions, cursors, functions e procedures, que podem ser referenciadas de fora do package. A seção body é onde você implementa o código do package. 2) É que a primeira é pública, ou seja, pode ser usada por todos as funções e procedures do package. A segunda é local, ou seja, só pode ser usada dentro da função ou procedure que a declarou. 3) Usando a cláusula DEFAULT <valor> ou o operador de atribuição := <valor> 4) Sim. 5) Uma função ou procedure que tenha sido criada no package body, sem uma declaração no package specification, só pode ser usada dentro do pacote. Ou seja, não são visíveis em uma aplicação.

123

Capítulo 7 – Oracle PL/SQL – Cursores

Capítulo 7 – Oracle PL/SQL – Cursores Um cursor é um tipo de ponteiro embutido em PL/SQL para fazer consultas ao banco de dados, retornando um conjunto de registros (um result set), permitindo ao desenvolvedor acessar estes registros uma linha de cada vez. Um cursor é um manipulador (handle) ou um nome para uma área privada de memória, que armazena informações retornadas por um comando SQL. O Oracle manipula cursores implicitamente. Entretanto, há algumas interfaces que permitem ao desenvolvedor usar cursores explicitamente, para processá-los mais eficientemente. Os dois tipos principais de cursores são definidos como: 

Cursores implícitos, que podem ser usados em PL/SQL sem que haja um código específico nomeando um cursor. Um conjunto de registros (result set) afetados por um cursor implícito pode ser tratado programaticamente, mas não há um controle efetivo sobre o cursor em si e as linhas de registro tratadas por ele;



Cursores

explícitos,

que

permitem

desenvolvedor

manipulá-los

programaticamente e dão um grande nível de controle sobre o conjunto de registros retornados por ele. Cada seção de usuário pode conter muitos cursores abertos, até o limite definido pelo parâmetro de inicialização OPEN_CURSORS, cujo default é 50. Um desenvolvedor deve se certificar de que sua aplicação ou código feche os cursores depois de usados, para preservar a memória do sistema. Cursores implícitos Sempre que você executa um comando SQL do tipo DML ou DDL dentro de um código PL/SQL, você está criando um cursor. Os cursores explícitos têm sempre um nome que é definido pelo desenvolvedor; mas um cursor implícito tem sempre o nome de SQL. Por exemplo: Quando nós criamos nosso procedimento P_NEWREGION, nós usamos o seguinte comando DML, para inserir um registro em regions: INSERT INTO REGIONS VALUES (RegID, RegName);

Isto implica em um cursor implícito! Claro que era só um teste, mas nós simplesmente confiamos cegamente que tudo daria certo, a ponto de logo depois usarmos um COMMIT, 124

Capítulo 7 – Oracle PL/SQL – Cursores para efetivar a transação. Nós poderíamos ter usado o cursor implícito para saber se o comando resultou em alguma coisa checando o atributo %ROWCOUNT do cursor. Exemplo: INSERT INTO REGIONS VALUES (RegID, RegName); IF SQL%ROWCOUNT > 0 – incluiu uma linha? THEN COMMIT; END IF;

É um exemplo muito bobo e até meio ilógico, mas dá para mostrar o uso do identificador de cursor implícito: SQL. 

Os atributos de um cursor

Os atributos de um cursor são:  %FOUND: O DML afetou alguma linha?  %ISOPEN: Este é óbvio! Mas é sempre falso para cursores SQL (implícitos)  %NOTFOUND: O DML não afetou nenhuma linha?  %ROWCOUNT: Quantas linhas foram afetadas pelo comando? NOTA.: %NOTFOUND pode parecer confuso. Mas seu significado não é que um comando tenha deixado de fazer alguma coisa que era para fazer. Na verdade, é quando o comando executado não afetou nada. Ex.: update employees set first_name = ‘Juca’ where employee_id = 999; Como não há nenhum employee_id = 999, o comando não afetou nada. Neste caso, %NOTFOUND seria verdadeiro. Cursores explícitos Um cursor explícito deve ser declarado como uma variável do mesmo tipo que as colunas que ele obtém; o tipo de dado do registro é derivado da definição do cursor. Cursores explícitos devem ser abertos e podem obter linhas dentro de uma estrutura LOOP...EXIT WHEN e então fechado. A forma básica de usar cursores explícitos é mostrada a seguir: DECLARE CURSOR cursor_name TYPE IS query_definition; OPEN cursor_name LOOP FETCH record; EXIT WHEN cursor_name%NOTFOUND; ...; -- process fetched row END LOOP; CLOSE cursor_name;

125

Capítulo 7 – Oracle PL/SQL – Cursores

Isto é o que acontece durante o ciclo de vida de um cursor: 

O comando OPEN executa a consulta (query) identificada pelo cursor, processa os parâmetros, e garante que o desenvolvedor possa processar os registros obtidos como resultado;



O comando FETCH roda a consulta e então procura e obtém as linhas compatíveis;



O comando CLOSE finaliza o processamento e fecha o cursor. Note que uma vez fechado o cursor, você não pode continuar obtendo registros adicionais.

EXERCÍCIO: Agora que já sabemos como funcionam cursores explícitos, vamos criar um procedimento simples dentro de pckg_HR, com a finalidade de listar os empregados de um determinado departamento. No capítulo 5, fizemos uma amostra de como criar subrotinas dentro de um procedimento. Naquele exemplo, usamos um loop do tipo FOR para ler os empregados de 101 a 201 diretamente em uma estrutura, usando um cursor implícito. Agora vamos ver como fazer a mesma coisa, mas usando um cursor explícito. A declaração deste procedimento deve ser: PROCEDURE lista_departamento (pDepartment_id IN employees.department_id%TYPE);

E o corpo do procedimento como mostrado a seguir: PROCEDURE lista_departamento(pDepartment_id IN employees.department_id%TYPE) AS -- declaring buffer variables for cursor data rEmp employees%ROWTYPE; conta PLS_INTEGER; -- declaring the cursor CURSOR emp_cursor IS SELECT * FROM employees e WHERE e.department_id = pDepartment_id; --******************************************** --*************** SUBPROGRAMAS *************** --******************************************** PROCEDURE GeraCabecalho( NoDept NUMBER ) AS BEGIN DBMS_OUTPUT.PUT_LINE('Listando os empregados do departamento ' || NoDept ); DBMS_OUTPUT.PUT_LINE(' '); DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID FIRST_NAME LAST_NAME'); DBMS_OUTPUT.PUT_LINE('----------- -------------------- -------------------'); END;

126

Capítulo 7 – Oracle PL/SQL – Cursores PROCEDURE GeraLinha( id employees.employee_id%TYPE, FName employees.first_name%TYPE, LName employees.last_name%TYPE ) AS BEGIN DBMS_OUTPUT.PUT_LINE( RPAD( id, 12, ' ') || RPAD( FName, 22, ' ') || RPAD( LName, 20, ' ') ); END; PROCEDURE GeraRodape( RowCount INTEGER ) AS BEGIN DBMS_OUTPUT.PUT_LINE('----------------------------------------------------'); DBMS_OUTPUT.PUT_LINE('Foram processados ' || RowCount || ' registros.'); END; --************************************************** --*************** PROGRAMA PRINCIPAL *************** --************************************************** BEGIN -- set serveroutput on, se quiser ver o resultado! GeraCabecalho( pDepartment_id ); OPEN emp_cursor; LOOP FETCH emp_cursor INTO rEmp; -- getting specific record EXIT WHEN emp_cursor%NOTFOUND; -- all records are been processed GeraLinha( rEmp.employee_id, rEmp.first_name, rEmp.last_name ); END LOOP; GeraRodape(emp_cursor%ROWCOUNT); CLOSE emp_cursor; END;

Para testar o novo procedimento de pckg_HR, basta comandar: set serveroutput on exec pckg_hr.lista_departamento( 50 ); anonymous block completed Listando os empregados do Departamento: 50 EMPLOYEE_ID ----------198 199 120 ... ... 195

FIRST_NAME LAST_NAME -------------------- -------------------Donald OConnell Douglas Grant Matthew Weiss ... ... ... ... Vance Jones

127

Capítulo 7 – Oracle PL/SQL – Cursores 196 Alana Walsh 197 Kevin Feeney ----------------------------------------------------Foram processados 45 registros.



Dissecando o procedimento

Para apararmos algumas arestas, vamos analisar mais de perto o código deste procedimento. Em primeiro lugar, compare o código que fizemos aqui com o que foi feito no capítulo 5. Note como alteramos as subrotinas de fazer o cabeçalho e o rodapé, de modo que elas pudessem receber as informações do departamento e de quantas linhas foram processadas. Agora vamos analisar esta nova versão. -- declaring buffer variables for cursor data rEmp employees%ROWTYPE;

O comando FETCH precisa de uma estrutura ou variável onde colocar o resultado da consulta feita pelo cursor. Como já estudamos antes, %ROWTYPE fornece uma estrutura tipo RECORD que é idêntica à estrutura de uma linha (registro) de uma tabela. Por isto declaramos rEmp desta forma. -- declaring the cursor CURSOR emp_cursor IS SELECT * FROM employees e WHERE e.department_id = pDepartment_id;

Ao declararmos o cursor emp_cursor, já devemos informar o comando SELECT que será processado por ele. Observe o parâmetro pDepartment_id sendo utilizado como critério para a consulta. FETCH emp_cursor INTO rEmp; -- getting specific record EXIT WHEN emp_cursor%NOTFOUND; -- all records are been processed

Após o cursor ter sido aberto pelo comando OPEN emp_cursor, nós passamos a utilizá-lo dentro de LOOP...EXIT WHEN. O comando FETCH lê (ou tenta ler) uma linha do cursor. Caso a consulte não resulte alguma coisa, ou a última linha do result set já tenha sido atingida, a condição na cláusula EXIT WHEN é verdadeira e o fluxo sai do loop. OBS.: Apenas para não deixar nenhuma dúvida, EXIT WHEN pode ser usado dentro de qualquer estrutura de loop (FOR, WHILE, etc.) e não apenas na que mostramos aqui. Após um FETCH...INTO bem sucedido, nós podemos trabalhar com os dados que ele 128

Capítulo 7 – Oracle PL/SQL – Cursores coloca dentro da estrutura rEmp. E é o que fazemos logo em seguida. GeraLinha( rEmp.employee_id, rEmp.first_name, rEmp.last_name );

Em GeraLinha nós concatenamos (||) os dados que nos interessam, formatando-os conforma a necessidade. RPAD é uma função built-in do Oracle muito útil para formatar textos. Sua função é completar uma string à direita com N caracteres, usando o caractere informado como terceiro parâmetro. OBS.: O primeiro parâmetro de RPAD é a string a ser formatada, que, obviamente, deve ser de um tipo caractere. Embora employee_id seja do tipo numérico, o Oracle é “inteligente” o bastante para fazer a conversão em certos tipos básicos de dados. Entre eles, NUMBER e CHAR.

Isto

nos

permitiu

não

utilização

uma

conversão

explícita

como

TO_CHAR(emp_record.employee_id). CLOSE emp_cursor;

Não devemos esquecer de fechar um cursor, após terminarmos de trabalhar com ele!

Boas práticas II – Consultas “enxutas” Em nosso exemplo usamos SELECT * FROM EMPLOYEES. Fizemos isto por didática, para simplificar as coisas. Mas vamos deixar uma coisa bem clara neste momento: SELECT * É CONSIDERADA UMA PÉSSIMA PRÁTICA!  A regra de ouro de consultas a banco de dados é que você deve trazer somente aquilo que vai precisar. Nada mais e nada a menos que isto! Como um exercício de fixação deste conceito de boas práticas, vamos modificar o modo como fizemos a consulta, trazendo apenas os campos que vamos utilizar. O cursor deve ser modificado para -- declaring the cursor CURSOR emp_cursor IS SELECT employee_id, first_name, last_name FROM employees e WHERE e.department_id = pDepartment_id;

Naturalmente, vamos ter que mudar a declaração de rEmp. TYPE tEmp_record IS RECORD( employee_id employees.employee_id%TYPE, first_name employees.first_name%TYPE, last_name employees.last_name%TYPE );

129

Capítulo 7 – Oracle PL/SQL – Cursores -- declaring buffer variables for cursor data rEmp tEmp_record;

Observe como criamos um tipo RECORD com as três variáveis necessárias, uma para cada campo que trazemos no SELECT. Observe também como usamos o qualificador especial %TYPE para “pescar” o tipo de dado de cada campo. Seja usando cursores, ou em outra situação qualquer, um desenvolvedor deve evitar ao máximo usar SELECT *, ainda que isto lhe custe algumas linhas adicionais no código. É melhor ter linhas adicionais no código, do que K ou M Bytes de dados trafegando desnecessariamente pela rede! EXERCÍCIO: Modificar GeraLinha de modo a receber o registro inteiro, ao invés de campo por campo.

130

Capítulo 7 – Oracle PL/SQL – Cursores Exercício de revisão Responda as questões a seguir: 1) Existem os cursores implícitos e explícitos. Que tipo de cursor está sempre envolvdido quando se executa um comando SQL dentro de um código PL/SQL? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 2) Qual o nome deste cursor? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 3) Descreva para que servem os atributos de um cursor: ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 4) Para cursores implícitos, qual o resultado do atributo %ISOPEN? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 5) De que forma é declarado um cursor? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 6) No ciclo de vida de um cursor, nós usamos os comandos OPEN, FETCH e CLOSE. Descreve o que faz cada um deles.

131

Capítulo 7 – Oracle PL/SQL – Cursores ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 7) Por que devemos evitar usar SELECT * em nossas consultas? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

132

Capítulo 7 – Oracle PL/SQL – Cursores RESPOSTAS 1) Implícitos. 2) SQL 3) %FOUND: Indica se o comando afetou alguma linha. %ISOPEN: Indica se o cursor está aberto. %NOTFOUND: Indica se o comando NÃO afetou alguma linha. %ROWCOUNT: Indica quantas linhas foram afetadas pelo comando. 4) Sempre falso. 5) CURSOR cursor_name TYPE IS query_definition; 6) OPEN executa a consulta (query) identificada pelo cursor, processa os parâmetros, e garante que o desenvolvedor possa processar os registros obtidos como resultado; FETCH roda a consulta e então procura e obtém as linhas compatíveis; O comando CLOSE finaliza o processamento e fecha o cursor. 7) Em consultas a banco de dados, devemos trazer somente aquilo que vamos precisar. SELECT * causa muito tráfego na rede e sobrecarrega desnecessariamente o servidor.

133

Capítulo 8 – Oracle PL/SQL – Parâmetros

Capítulo 8 – Oracle PL/SQL – Parâmetros Agora que já sabemos como criar procedimentos, funções, packages e também como trabalhar com cursores, vamos examinar mais detalhadamente um assunto que certamente os desenvolvedores usam muito: os parâmetros. Tipos de parâmetro A sintaxe completa para a declaração de um parâmetro tem a seguinte estrutura, conforme a documentação oficial: Syntax parameter_declaration ::=

IN, OUT, IN OUT Define o modo do parâmetro. A tabela abaixa explica o significado destas cláusulas. IN

OUT

IN OUT

O default

Precisa ser especificado

Passa um valor ao subprograma

Retorna um valor ao ponto de chamada

Passa um valor inicial ao subprograma e retorna um valor atualizado ao ponto de chamada

O identificador Formal atua como uma constante

O identificador Formal atua como uma variável não inicializada

O identificador Formal atua como uma variável inicializada

A um identificador Formal não Um identificador Formal deve pode ser atribuído nenhum valor receber algum valor

Um identificador Formal deveria receber algum valor

O parâmetro Actual pode ser uma constante, uma variável inicializada, ou uma expressão

O parâmetro Actual deve ser uma variável

O parâmetro Actual é passado por referência (o ponto de chamada passa um ponteiro para o valor ao subprograma)

O parâmetro Actual é passado por valor (o subprograma passa ao ponto de chamada uma cópia do valor) a não ser que NOCOPY seja especificado

O parâmetro Actual é passado por valor (o ponto de chamada passa uma cópia do valor ao subprograma e o subprograma passa ao ponto de chamada uma cópia do valor) a não ser que NOCOPY seja especificado

Identificador Formal é a variável declarada no cabeçalho do subprograma para receber 134

Capítulo 8 – Oracle PL/SQL – Parâmetros um parâmetro, e que é referenciada no código do subprograma. O parâmetro Actual é uma variável ou expressão que você passa ao subprograma quando você o invoca. Em outras palavras: o identificador Formal é o identificador que recebe o dado. E o parâmetro Actual é o identificador ou expressão com o dado a ser enviado. Talvez uma codificação básica esclareça um pouco mais. O código a seguir declara e chama um subprograma (raise_salary), cuja função é aumentar o salário de um empregado, passados o código do empregado e o valor a ser aumentado. -- Bloco do programa DECLARE emp_num NUMBER(6) := 120; -- employee_id bonus NUMBER(6) := 100; -- bônus merit NUMBER(4) := 50; -- mérito -- Bloco do subprograma PROCEDURE raise_salary ( emp_id NUMBER, -- identificador formal amount NUMBER -- identificador formal ) IS BEGIN UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id; END raise_salary; -- Fim do bloco do sub-programa BEGIN raise_salary(emp_num, bonus); -- parâmetro actual raise_salary(emp_num, merit + bonus); -- parâmetro actual END; /

Perceba que este código mostra um programa (linhas em azul) e um subprograma (as linhas em preto). O contexto do programa é o que é chamado na tabela anterior de ponto de chamada. O programa chama o subprograma passando emp_num e bonus como parâmetro. Para efeitos de compreensão da tabela, estes são parâmetros Actual. Qual o significado disto? Consulte a tabela e prossiga a leitura depois. Parâmetros de modo IN Por default, os dois identificadores de parâmetro de raise_salary são parâmetros cujo modo é IN. Isto é que vai determinar como emp_id e amount (formal) podem ser utilizados dentro de raise_salary e se emp_num e bonus (actual) serão afetados por raise_salary. 135

Capítulo 8 – Oracle PL/SQL – Parâmetros Exemplo: É possível atribuir algum valor à emp_id ou amount, ainda que dentro de raise_salary? IN  formal: resposta não! Na chamada de raise_salary, seria possível passar uma constante 120, no lugar de emp_num? IN  formal: resposta sim! Parâmetros de modo IN OUT Digamos que agora nós queremos saber o valor do novo salário do empregado. Podemos aproveitar o parâmetro amount para retornar o novo salário. É logicamente aceitável: ele leva um valor a ser somado ao salário atual do empregado e de volta trás o novo salário. O que seria necessário fazer? Em primeiro lugar, ele deve ser um parâmetro de modo IN OUT. Em segundo lugar, o subprograma deve de alguma forma atribuir a ele o novo salário. O novo código seria este: -- Bloco do programa DECLARE emp_num NUMBER(6) := 120; -- employee_id bonus employees.salary%TYPE := 100; -- bônus merit NUMBER(4) := 50; -- mérito -- Bloco do subprograma PROCEDURE raise_salary ( emp_id NUMBER, -- identificador formal amount IN OUT NUMBER -- identificador formal ) IS BEGIN UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id; SELECT salary INTO amount FROM employees WHERE employee_id = emp_id; END raise_salary; -- Fim do bloco do sub-programa BEGIN raise_salary(emp_num, bonus); -- parâmetro actual DBMS_OUTPUT.PUT_LINE('O novo salário é ' || TO_CHAR(bonus)); -- raise_salary(emp_num, merit + bonus); -- parâmetro actual END; /

Percebeu como ao atribuirmos um valor à amount dentro de raise_salary o valor de 136

Capítulo 8 – Oracle PL/SQL – Parâmetros bonus foi afetado? Este é a lógica do modo IN OUT! Percebeu também que colocamos um comentário na linha -- raise_salary(emp_num, merit + bonus); -- parâmetro actual

Você saberia dizer por quê? (por isto que usamos o termo seria, lá atrás) Resposta: merit + bonus formam uma expressão. E um parâmetro Actual, no modo IN OUT deve ser uma variável! Se tentássemos compilar esta linha, seria gerado um erro ou de compilação ou de execução. Faz sentido já que, por ser IN OUT, ele retorna um valor. E para quem ele retornaria o valor na expressão, para merit ou para bonus?!!! Os computadores ainda não são capazes de ler nossos pensamentos e corrigir nossa “irracionalidade”  ! Finalmente, tivemos também que mudar a declaração de bonus, já que o salário aceita frações (salary, em employees, é declarado como NUMBER(8,2)). Se tivéssemos deixado ele como NUMBER(6), qualquer fração no salário seria truncada! Como alguém disse certa vez: “Na programação, o demônio se esconde nos detalhes!” Parâmetros de modo OUT Para que possamos continuar chamando raise_salary passando merit + bonus como parâmetro, o ideal seria criar outro parâmetro do tipo OUT, somente para retornar o novo salário. O código final de nosso exercício passa a ser, então, este. -- Bloco do programa DECLARE emp_num NUMBER(6) := 120; -- employee_id bonus NUMBER(6) := 100; -- bônus merit NUMBER(4) := 50; -- mérito new_sal employees.salary%TYPE; -- novo salário -- Bloco do subprograma PROCEDURE raise_salary ( emp_id NUMBER, -- identificador formal amount NUMBER, -- identificador formal new_salary OUT employees.salary%TYPE -- identificador formal ) IS BEGIN UPDATE employees SET salary = salary + amount WHERE employee_id = emp_id; SELECT salary INTO new_salary FROM employees WHERE employee_id = emp_id; END raise_salary; -- Fim do bloco do sub-programa

137

Capítulo 8 – Oracle PL/SQL – Parâmetros BEGIN raise_salary(emp_num, bonus, new_sal); -- parâmetro actual DBMS_OUTPUT.PUT_LINE('O novo salário é ' || TO_CHAR(new_sal)); raise_salary(emp_num, merit + bonus, new_sal); -- parâmetro actual END; /

138

Capítulo 8 – Oracle PL/SQL – Parâmetros Exercício de revisão Responda as questões a seguir: 1) Quais são os tipos de parâmetros que podem ser passados à uma função ou procedure e qual o tipo default? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ Dada a seguinte declaração, responda o que se segue: PROCEDURE XPTO ( id NUMBER, result1 VARCHAR2 IN OUT, result2 OUT NUMBER ) IS ...

2) No código de XPTO, é possível atribuir algum valor ao parâmetro id? ______________________________________________________________________ 3) A chamada a seguir está correta? DECLARE Var1 NUMBER; Var2 VARCHAR2(240); BEGIN XPTO( 1, '1, 2, 3, testando...', Var1); END; /

______________________________________________________________________ 4) Esta chamada está correta? DECLARE Var1 NUMBER; Var2 VARCHAR2(240); BEGIN Var1 := 1; Var2 := '1, 2, 3, testando...'; XPTO( Var1, Var2, 1); END; /

______________________________________________________________________

139

Capítulo 8 – Oracle PL/SQL – Parâmetros 5) Esta chamada está correta? DECLARE Var1 NUMBER; Var2 VARCHAR2(240); BEGIN Var1 := 1; XPTO( 1 + Var1, Var2, Var1); END; /

______________________________________________________________________

140

Capítulo 8 – Oracle PL/SQL – Parâmetros RESPOSTAS 1) IN, OUT, IN OUT O tipo default é o IN 2) Não 3) Sim 4) Não 5) Sim

141

Capítulo 9 – Oracle PL/SQL – Retorno de Parâmetros

Capítulo 9 – Oracle PL/SQL – Retorno de Parâmetros Quando desenvolvemos aplicações, usar funções e procedimentos de banco de dados para implementar as regras de negócio é uma prática muito recomendada. Invariavelmente, as aplicações que chamam estes procedimentos ou funções precisam manipular de alguma forma o resultado das operações. Uma forma muito prática é o retorno de parâmetros. Até o momento, vimos utilizando um recurso que, embora didático, não atende muito bem aos propósitos de uma aplicação. O recurso do qual falamos é o PUT_LINE, do package DBMS_OUTPUT. Em uma situação real, é mais prática retornar um DataSet ou uma collection, contendo uma lista de dados. Retornando Cursores para uma aplicação Para mostrar como uma aplicação pode executar um procedimento no banco de dados e obter de volta o resultado de uma consulta (SELECT), vamos adicionar um procedimento ao package pckg_hr. Seu nome será CONSULTA_DEPARTAMENTO. A idéia é passar o código do departamento como parâmetro e obter de volta a lista de empregados daquele departamento. Para isto, adicione as linhas a seguir na declaração de pckg_hr: ... type cursorType is ref cursor; ... PROCEDURE consulta_departamento(pDepartment_id IN employees.department_id%TYPE, presult IN OUT cursortype); ...

Em type cursorType IS REF cursor, nós criamos um tipo necessário para o retorno do parâmetro. Um REF cursor é um tipo de dado que aponta para um cursor. Em presult IN OUT cursortype nós declaramos a variável (parâmetro) que servirá de ponteiro para o pesquisa que será feita dentro do procedimento e desta forma retornar (por referência) ao aplicativo que chamar o procedimento. Agora, no corpo do package (package body), acrescente o seguinte código: PROCEDURE consulta_departamento(pDepartment_id IN employees.department_id%TYPE, presult IN OUT cursortype) AS

142

Capítulo 9 – Oracle PL/SQL – Retorno de Parâmetros BEGIN OPEN presult FOR SELECT employee_id, first_name, last_name FROM employees e WHERE e.department_id = pDepartment_id; END;

Ele é absurdamente simples; mas serve para mostrar o mecanismo necessário para retornar uma consulta ao ponto de chamada! OPEN presult FOR SELECT... é a chave do “mistério”! Como presult foi declarado REF cursor, o Oracle sabe o que fazer com o comando OPEN presult: ele executa o SELECT e faz presult apontar para o resultado da consulta. Nas figuras a seguir, mostramos as configurações de uma aplicação em Delphi de modo a executar o procedimento CONSULTA_DEPARTAMENTO e exibir o resultado. 

Elementos visuais da aplicação

 oraSP é um componente para executar procedures em um banco de dados Oracle;  Sessao é um componente para fazer uma conexão a um banco de dados Oracle;  DataSource é um componente que faz a conexão entre um DataSet (no caso o oraSP) e um componente de visualização de dados (no caso, um DBGrid).

143

Capítulo 9 – Oracle PL/SQL – Retorno de Parâmetros 

Configurando o componente que executa procedures (oraSP)



Componente configurado. Observe o bloco de código que chama o procedimento, passando os parâmetros

144

Capítulo 9 – Oracle PL/SQL – Retorno de Parâmetros 

Trecho do código em Delphi que passa os parâmetros e executa o procedimento (código do botão [Consultar])

procedure TfrmMain.btnConsultarClick(Sender: TObject); begin oraSP.Active := False; oraSP.ParamByName('PDEPARTMENT_ID').Value := StrToInt( ediNoDpto.Text ); oraSP.Active := True; end;



Programa em execução, mostrando o resultado para o departamento 30

Usando o SQL*Plus para passar e receber parâmetros O que foi feito anteriormente, quando usamos uma aplicação em Delphi para testar o retorno de cursores, pode também ser feito no SQL*Plus. Mas antes de mostrarmos como fazer isto, vamos aprender um pouco mais sobre o SQL*Plus. Bind Variables Bind variables são variáveis que você cria no SQL*Plus e as referencia em um bloco PL/SQL ou em comandos SQL. Ao criar uma bind variable no SQL*Plus você pode usá-la do mesmo jeito que as variáveis que você declara em um programa PL/SQL. Você pode usar estas variáveis para passar e receber parâmetros de um subprograma qualquer. Declarando Bind Variables 145

Capítulo 9 – Oracle PL/SQL – Retorno de Parâmetros Você declara uma variável no SQL*Plus usando o comando VARIABLE. Ex.: VARIABLE ret_val NUMBER

Referenciando Bind Variables Para referenciar uma variável bind você usa o sinal de dois pontos antes de seu nome. Ex.: :ret_val := 4;

Para atribuir diretamente um valor, você precisa colocá-la dentro de um bloco de código PL/SQL. Assim: BEGIN :ret_val:=4; END; / Procedimento PL/SQL concluído com sucesso.

Exibindo o conteúdo de Bind Variables Para exibir no SQL*Plus o conteúdo de uma variável bind, você simplesmente usa o comando PRINT. SQL> print ret_val RET_VAL ---------4 SQL>

Executando um programa via SQL*Plus Agora que já sabemos declarar variáveis bind para passar e receber parâmetros, vamos fazer um exercício. Vamos chamar a função f_pronome, do package pckg_hr, via SQL*Plus. SQL> VARIABLE strPronome VARCHAR2(20) SQL> EXECUTE :strPronome := pckg_hr.f_pronome('F','C'); Procedimento PL/SQL concluído com sucesso.

OBS.: Note que por ser uma função, bastou atribuir o resultado da função à strPronome.

146

Capítulo 9 – Oracle PL/SQL – Retorno de Parâmetros Agora vamos ver como executar uma procedure. Vamos usar como teste a procedure consulta_departamento, do package pckg_hr. SQL> VARIABLE cv REFCURSOR SQL> EXECUTE pckg_hr.consulta_departamento(30, :cv);

Procedimento PL/SQL concluído com sucesso.

OBS.: Note que por ser uma procedure, passamos cv como um parâmetro. Agora para ver o resultado, basta usar o comando PRINT. SQL> print cv EMPLOYEE_ID ----------114 115 116 117 118 119

FIRST_NAME -------------------Den Alexander Shelli Sigal Guy Karen

LAST_NAME ------------------------Raphaely Khoo Baida Tobias Himuro Colmenares

6 linhas selecionadas.

Usando bind variables com comandos SQL. Nada impede que você utilize a mesma variável para outra operação, desde que seja válida. Vamos usar a variável já declara (cv) para outra operação, desta vez usando um comando SELECT. BEGIN OPEN :cv FOR SELECT EMPLOYEE_ID, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN' ; END; / Procedimento PL/SQL concluído com sucesso.

Para ver o resultado, basta usar o já conhecido comando PRINT. SQL> print cv EMPLOYEE_ID SALARY ----------- ---------145 14000 146 13500 147 12000 148 11000 149 10500 SQL>

147

Capítulo 9 – Oracle PL/SQL – Retorno de Parâmetros Lembre-se que cv é uma variável do tipo REF CURSOR. Por isso usamos o comando OPEN...FOR...SELECT.

148

Capítulo 9 – Oracle PL/SQL – Retorno de Parâmetros Exercício de revisão Responda as questões a seguir: 1) O que é um Um REF cursor? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 2) Quando se usar um Um REF cursor? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 3) Qual comando deve ser usado, para se retornar um cursor? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 4) O que são Bind variables? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 5) Como declarar uma Bind variables? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 6) Após declarada, como referenciar uma Bind variables? ______________________________________________________________________ ______________________________________________________________________ 149

Capítulo 9 – Oracle PL/SQL – Retorno de Parâmetros ______________________________________________________________________ 7) Qual comando deve ser utilizado para exibir no SQL*Plus o conteúdo de uma variável bind? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 8) Como declarar uma bind variable do tipo refcursor no SQL*Plus? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

150

Capítulo 9 – Oracle PL/SQL – Retorno de Parâmetros RESPOSTAS 1) Um REF cursor é um tipo de dado que aponta para um cursor. 2) Quando se desejar retornar um cursor como parâmetro. 3) OPEN <ref_cursor> FOR SELECT... 4) Bind variables são variáveis que você cria no SQL*Plus e as referencia em um bloco PL/SQL ou em comandos SQL 5) Usando o comando VARIABLE 6) Usando o sinal de dois pontos antes de seu nome. 7) O comando PRINT. 8) VARIABLE <variavel> REFCURSOR

151

Capítulo 10 – O Administrador de Dados

Capítulo 10 – O Administrador de Dados Nos capítulos 2 e 3, nós vimos a estrutura geral do SGBD Oracle e como funciona a lógica dos bancos de dados. Neste capítulo, vamos iniciar alguns conceitos de AD – Administração de Dados. Estruturas lógicas X Estruturas físicas Do ponto de vista de um desenvolvedor, um banco de dados é uma estrutura lógica. Ele cria objetos em um schema (tabelas, índices, views, etc) e os manipula através de um nome lógico. Do ponto de vista de uma DBA (DataBase Administrator), um banco de dados é mais que uma estrutura lógica. É, além disso, um conjunto de arquivos físicos (database files), dispostos em um ou mais discos rígidos. Entre a visão de um desenvolvedor e a visão de um DBA existe outra visão especializada, que é a de um Administrador de Dados (AD). Embora um AD também seja responsável pela manutenção de um banco de dados, esta manutenção, geralmente, se dá também num nível lógico. Ou seja, ele não é encarregado de fazer backups do banco de dados, nem de como a distribuição dos arquivos físicos deve ser feita em um sistema de discos. Na maior parte do tempo, seu trabalho é, em apoio aos desenvolvedores, cuidar da criação de tabelas, índices, views e demais objetos de um sistema, além de cuidar da otimização do banco de dados, do ponto de vista de sua utilização pelos desenvolvedores e usuários. Não é raro encontrarmos sistemas cujos usuários reclamam da demora em uma consulta mostrar o resultado. E não é raro também um AD perceber que a demora em a consulta mostrar o resultado se deve ao simples fato de faltar um índice na tabela! Em outros casos, os usuários reclamam que, ao incluir um registro na tabela, o sistema “congela” por algum tempo e demora a retornar o foco para a aplicação. Nestes casos, não é raro um AD perceber que existem índices demais na tabela! O papel de um Administrador de Dados Em uma empresa com grande estrutura organizacional, sempre haverá a necessidade de 152

Capítulo 10 – O Administrador de Dados um profissional que mantenha a consistência das informações e promova a melhor maneira com que todos os sistemas compartilhem destas informações. Normalmente, todos pensam que este é o papel de um Analista de Sistemas. Mas depende, pois em empresas com grande número de empregados, existem vários sistemas, bastante diferentes e que compartilham dados para produzirem informações de acordo com o interesse de cada área; o analista cuida especificamente de um sistema e sua relação com a área para a qual ele foi criado. Já o AD cuida de todos os dados sendo utilizados pelos sistemas na organização. Também é atividade do AD a perfeita normalização e aderência dos dados ao contexto interno e externo à empresa, bem como a sua reutilização em tecnologias vigentes. Abaixo vamos enumerar algumas atribuições do AD: 1. Elaborar, propor e manter modelos de dados; 2. Definir os dados necessários à obtenção de informações solicitadas; 3. Definir os níveis de integridade e segurança dos dados nos diversos níveis em que as informações solicitadas progredirem; 4. Ter perfeito conhecimento dos processos empresariais como um todo; 5. Elaborar e promover padrões de dados como dicionários, nomes, tipos, etc. Criação de Tabelas e Índices Deixando de lado as questões de estratégia empresarial e de como os dados serão organizados no banco de dados, vamos nos ater aos detalhes técnicos que devem ser observados quando da criação de tabelas e índices. Tablespaces independentes para tabelas e índices Vimos nos capítulos 2 e 3 que as tabelas e índices são objetos criados no schema de um usuário. Além disso, vimos que há uma tablespace default sempre associada ao usuário detentor dos objetos. Ocorre que, salvo instrução em contrário, tudo o que for criado num schema fica armazenado logicamente na tablespace default do usuário. Mais importante que armazenar os dados, é obter informações a partir destes dados. Isto é feito através de consultas, via comando SELECT, basicamente. Ao se consultar dados, o SGBD usa de artifícios para retornar a informação solicitada o mais rapidamente possível. Um dos artifícios mais importantes, sem dúvida, são os índices. 153

Capítulo 10 – O Administrador de Dados Tanto é importante, que o SGBD cria índices automaticamente para todos os campos chave (chaves primárias) das tabelas, ainda que você não tenha definido explicitamente que queria isto! O SGBD cria índices automaticamente para todas as chaves primárias porque, basicamente, este campo é usado para identificar inequivocamente um registro na tabela e será muito utilizado em consultas. “Ora, mas isto é óbvio!” – podem estar pensando. É verdade. Isto é muito óbvio. Por isto não vamos chover no molhado e dar uma aula de porque os índices existem e devem ser criados. Mas uma coisa não muito óbvia é a concorrência da cabeça de leitura/gravação dos discos, quando se consulta dados em um banco de dados. Tomemos como exemplo a consulta abaixo: SQL> select First_Name, Last_name 2 from employees 3 where employee_id between 150 and 160; FIRST_NAME -------------------Peter David Peter Christopher Nanette Oliver Janette Patrick Allan Lindsey Louise

LAST_NAME ------------------------Tucker Bernstein Hall Olsen Cambrault Tuvault King Sully McEwen Smith Doran

11 linhas selecionadas.

Conseguem imaginar o que aconteceu? Os dados estão gravados em pelo menos um disco rígido. O SGBD, com base na consulta, faz uma varredura na tabela employees, trazendo os dados solicitados e que estejam dentro do critério da cláusula where. Como o campo employee_id é um campo indexado (ele é o campo chave da tabela), o SGBD, para não fazer uma full table scan, acessa o índice de employee_id e trás rapidamente os dados solicitados. Este é o mecanismo dos índices e estudamos isto na faculdade... Porém (sempre tem um porém), se as tabelas e índices estiverem armazenados no mesmo disco rígido, o que vai ocorrer? A cabeça de leitura do disco rígido fica alternando entre o índice e a tabela, ora para pesquisar o código de employee_id no índice, ora para

154

Capítulo 10 – O Administrador de Dados obter os dados da tabela. Isto é o que chamamos de concorrência da cabeça de leitura! Mas se as tabelas e índices estiverem em discos separados (na verdade, em tablespaces que apontam para discos diferentes), esta concorrência não ocorrerá. Não é novidade para ninguém que, atualmente, os discos rígidos são os maiores “gargalos” de tempo de acesso às informações. Separando índices e tabelas, colocando-os em discos rígidos separados, gera um ganho de tempo de acesso que em alguns casos chega a mais de 200% Criando uma tablespace para índices A criação de tablespaces é uma tarefa de DBA. Da mesma forma que numa situação ideal não são os desenvolvedores quem criam as tabelas e demais objetos de uma banco de dados, mas sim um AD, os AD solicitam à um DBA que crie uma tablespace no banco de dados. Mas nem sempre temos um DBA à mão, e temos que fazer o papel de AD e DBA. Por isto vamos “invadir” um pouco o espaço dos DBA e criar nós mesmos uma tablespace para os índices. Podemos criar uma tablespace de várias maneiras, via SQL*Plus, SQL Developer e até mesmo pelo Enterprise Manager(EM). Vamos usar o EM neste exemplo. Abram o browser e se conectem ao EM, usando o usuário SYSTEM, pela url https://localhost:1158/em/. Após isto, localizem o link Tablespaces, na aba Servidor, como mostrado na figura abaixo.

155

Capítulo 10 – O Administrador de Dados A tela exibida ao se clicar no link tablespaces é mais ou menos como a mostrada a seguir.

Como queremos criar uma tablespace, vamos clicar no botão [Criar]. Feito isto, o EM exibe uma tela parecida com a mostrada na figura a seguir. Aproveite e preencha o campo * Nome, como indicado na figura. Se não estiver claro na figura, o nome da tablespace é HR_INDICES. Não clique ainda em [Ok]! Uma tablespace precisa de ao menos um arquivo de dados. Por isto, precisamos clicar no botão [Adicionar] na parte inferior da tela e siga as instruções abaixo: Digite HR_INDICES.DBF no campo *Nome do arquivo; Selecione Expande automaticamente o arquivo de dados quando cheio (AUTOEXTEND); Configure o Incremento para 1 MB; Clique no botão [Continuar]. OBS.: Obviamente, em *Diretório do arquivo nós informaríamos outro HD para o arquivo, pelas razões expostas neste capítulo! Mas como estamos num ambiente de testes, vamos deixá-lo na mesma unidade de disco.

156

Capítulo 10 – O Administrador de Dados

Acima, entrando os dados da tablespace. Abaixo, informando o nome do arquivo de dados da tablespace.

Relembrando a observação anterior, neste ponto informamos outra unidade de disco para o arquivo físico associado à tablespace. De volta à tela de criação da tablespace, basta clicar no botão [Ok]. Se quiser ver como é o comando SQL de criação da tablespace, basta clicar no botão [Mostrar SQL]. O comando 157

Capítulo 10 – O Administrador de Dados será algo como: CREATE SMALLFILE TABLESPACE "HR_INDICES" DATAFILE 'C:\ORACLE\ORADATA\ORCL\HR_INDICES.DBF' SIZE 100M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT NOCOMPRESS

Se tudo correu bem, voltamos à tela de exibição das tablespaces e podemos ver a tablespace criada.

Migrando os índices para a nova tablespace Como os criadores do schema HR não cuidaram de uma tablespace independente para os índices, é óbvio que os índices se encontram na mesma tablespace que as tabelas (USERS). Como migrar os índices para a nova tablespace? No Oracle, podemos fazer isto de forma bastante simples, usando o comando ALTER INDEX <index_name> REBUILD TABLESPACE <tablespace_name>;

Hmmmmm, e quais são os índices de HR?!!! O Oracle disponibiliza para os AD e DBA uma vasta biblioteca de informações, mais conhecida como Dicionário de Dados. No dicionário de dados do Oracle existem centenas de views com informações preciosas a respeito do banco de dados. A que nos interessa agora se chama ALL_INDEXES. Através do comando DESC, podemos “espionar” a estrutura desta view e saber que informações ela contem. Veja a seguir:

158

Capítulo 10 – O Administrador de Dados desc all_indexes Nome -----------------------------OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE ... TABLESPACE_NAME ...

Nulo -------NOT NULL NOT NULL

Tipo -------------VARCHAR2(30) VARCHAR2(30) VARCHAR2(27) NOT NULL VARCHAR2(30) NOT NULL VARCHAR2(30) CHAR(5) VARCHAR2(30)

55 rows selected

São 55 atributos; muita coisa! Por isto destacamos os relevantes no momento. Para sabermos quais os índices de todas as tabelas de um schema, podemos fazer algo como: SQL> select INDEX_NAME 2 from all_indexes 3 where owner = 'HR'; INDEX_NAME -----------------------------JHIST_DEPARTMENT_IX JHIST_EMPLOYEE_IX JHIST_JOB_IX JHIST_EMP_ID_ST_DATE_PK EMP_NAME_IX EMP_MANAGER_IX EMP_JOB_IX EMP_DEPARTMENT_IX EMP_EMP_ID_PK EMP_EMAIL_UK JOB_ID_PK DEPT_LOCATION_IX DEPT_ID_PK LOC_COUNTRY_IX LOC_STATE_PROVINCE_IX LOC_CITY_IX LOC_ID_PK COUNTRY_C_ID_PK REG_ID_PK 19 linhas selecionadas.

Pronto, estes são os índices de HR que devemos migrar para a nova tablespace. Poderíamos fazer isto um-por-um mas, convenhamos, ninguém gosta de tarefas repetitivas. Então vamos utilizar um recurso interessante e criar um script. Observe os seguintes comandos, que devem ser executados via SQL*Plus e conectado com o usuário HR:

159

Capítulo 10 – O Administrador de Dados SQL> SQL> SQL> SQL> SQL> 2 3

set heading off set feedback off set echo off spool c:\rebuild.sql select 'ALTER INDEX ' || INDEX_NAME || ' REBUILD TABLESPACE HR_INDICES;' from all_indexes where owner = 'HR';

ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER

INDEX INDEX INDEX INDEX INDEX INDEX INDEX INDEX INDEX INDEX INDEX

JHIST_DEPARTMENT_IX REBUILD TABLESPACE HR_INDICES; JHIST_EMPLOYEE_IX REBUILD TABLESPACE HR_INDICES; JHIST_JOB_IX REBUILD TABLESPACE HR_INDICES; JHIST_EMP_ID_ST_DATE_PK REBUILD TABLESPACE HR_INDICES; EMP_NAME_IX REBUILD TABLESPACE HR_INDICES; EMP_MANAGER_IX REBUILD TABLESPACE HR_INDICES; EMP_JOB_IX REBUILD TABLESPACE HR_INDICES; EMP_DEPARTMENT_IX REBUILD TABLESPACE HR_INDICES; EMP_EMP_ID_PK REBUILD TABLESPACE HR_INDICES; EMP_EMAIL_UK REBUILD TABLESPACE HR_INDICES; JOB_ID_PK REBUILD TABLESPACE HR_INDICES;

ALTER ALTER ALTER ALTER ALTER ALTER ALTER ALTER

INDEX INDEX INDEX INDEX INDEX INDEX INDEX INDEX

DEPT_LOCATION_IX REBUILD TABLESPACE HR_INDICES; DEPT_ID_PK REBUILD TABLESPACE HR_INDICES; LOC_COUNTRY_IX REBUILD TABLESPACE HR_INDICES; LOC_STATE_PROVINCE_IX REBUILD TABLESPACE HR_INDICES; LOC_CITY_IX REBUILD TABLESPACE HR_INDICES; LOC_ID_PK REBUILD TABLESPACE HR_INDICES; COUNTRY_C_ID_PK REBUILD TABLESPACE HR_INDICES; REG_ID_PK REBUILD TABLESPACE HR_INDICES;

SQL> spool off

Após isto, no diretório C:\ você encontrará um arquivo chamado rebuild.sql, contendo as instruções necessárias para migrar os índices. Infelizmente, o SQL*Plus adicionou algumas linhas indesejáveis que precisamos remover. É só abrir o arquivo rebuild.sql com o bloco de notas e “retocá-lo” de modo a que fiquem apenas as linhas acima, mostradas em azul. Agora é só voltar ao SQL*Plus e rodar o script: SQL> set echo on SQL>@C:\rebuild.sql

Muito provavelmente, ocorrerá um erro nesta linha: ALTER INDEX COUNTRY_C_ID_PK REBUILD TABLESPACE HR_INDICES;

Isto se deve ao fato de que este índice é parte da tabela COUNTRIES e ela é uma tabela do tipo IOT (Index Organized Table). Ou seja, o índice é criado embutido nela mesma. De modo que seria necessário movê-la para outra tablespace. E não é isto que queremos. Poderíamos tê-la excluído da lista, acrescentando um and index_type = 'NORMAL' ao comando.

160

Capítulo 10 – O Administrador de Dados Consultando o Dicionário de Dados Vimos no tópico anterior como consultamos a view all_indexes para resolver um problema. A vida de um AD e de um DBA seria impossível se não existisse o dicionário de dados! Não é raro um profissional assumir um trabalho numa empresa onde existe um legado de mais de 50 sistemas e dezenas de bancos de dados. Então pedem a ele para alterar a estrutura de uma tabela, criar um índice, ... Sem o dicionário de dados ele não saberia nem por onde começar. Alguém pode estar pensando: “Ué, e a documentação do sistema?!” Sinto desapontá-los mas, na teoria, a teoria e a prática são a mesma coisa; mas na prática... O dicionário de dados do Oracle tem três grupos principais, com as funções relacionadas a seguir: User_  Este grupo tem como finalidade exibir todos os objetos do usuário, criadas no schema do usuário. All_  Este grupo lista todos os objetos associados ao seu schema e aos outros schemas que você tem autorização. DBA_  Este grupo, tem a função de exibir todos os objetos do banco de dados. Você precisa de privilégios de DBA para acessar este grupo. Dentro de cada um destes grupos existem as views abaixo relacionadas: •

Objects;

•

Tables;

•

Tab_Columns;

•

Tab_Partitions;

•

Indexes;

•

Ind_Columns;

•

Ind_Partitions;

•

Constraints;

•

Cons_Columns;

161

Capítulo 10 – O Administrador de Dados •

Sequences;

•

Synonyms;

•

Users;

•

Views;

•

Updatable_Columns;

•

Types;

•

Type_Attrs;

•

Type_Methods;

•

Nested_Tables;

•

Object_Tables;

•

Lobs;

•

Method_Params;

•

Method_Results.

Cada view citada acima tem uma função especial dentro do sistema, que será utilizada em conjunto com os grupos mostrados anteriormente. Ou seja, cada uma destas views deve ser prefixada pelo grupo desejado. Exemplo: All_ Sequences ou User_ Sequences ou DBA_ Sequences, conforme o caso. Se você estiver conectado como HR e digitar SELECT * FROM User_Sequences;

Irá ver todas as sequences de HR, uma vez que é “ele” o User_ em questão. Mas se você digitar SELECT * FROM All_Sequences;

Irá ver TODAS as sequences que existem no banco de dados! Na verdade, na verdade, um AD e um DBA trabalham com o grupo DBA_. Ele, sim, é o “Master of the Oracle Universe”. Através deste grupo você terá acesso a todas as infromações de tudo o que existe no banco de dados. Por esta razão, vamos nos concentrar neste grupo, listando algumas views bastante úteis e suas aplicações. SQL > DESC DBA_Objects; Esta view tem como finalidade mostrar todos os objetos do banco de dados, como 162

Capítulo 10 – O Administrador de Dados tabelas, índices, partições, Lobs, views e etc. É um dos comandos mais básicos que um DBA utiliza no serviço, pois com ele conseguimos ver todos os objetos criados dentro do banco de dados com mais detalhes, e conseguirmos traçar um plano de tuning futuramente. SQL > DESC DBA_Tables; O nome já diz tudo. Esta view contém a descrição de todas as tabelas do banco de dados, nos informando em qual tablespace a tabela está, qual a porcentagem do crescimento dos Extents, números de linhas, blocos de dados alocados e etc. Ótimo recurso para melhorar os planos de segurança do banco de dados e tuning. SQL > DESC DBA_Tab_columns; Esta view mostra a descrição de todas as colunas de todas as tabelas, views e clusters do banco de dados. Excelente para podermos achar problemas de tamanho de coluna de uma determinada tabela ou saber qual o tipo de dados da coluna. SQL > DESC DBA_Tab_Partitions; Aqui teremos as informações sobre as partições de tabelas. As partições começaram a ser utilizadas depois da versão 8i, então nossa querida Oracle criou na versão 8i, 9i e 10g. Nesta view podemos saber que tipo de particionamento está em nossa tabela e saber sua localização. Um recurso bem interessante mesmo. SQL > DESC DBA_Indexes; Esta view mostra todos os índices do banco de dados, o verdadeiro "dedo-duro". Ele passa informações como nome do índice, tabela, tamanho, tipo de tabela, localização, tablespace e etc. Excelente recurso do dicionário para fazer melhoramentos no sistema (tuning), pois com ele conseguimos distribuir e remanejar melhor os índices das tabelas. SQL > DESC DBA_Ind_Columns; Esta view nos fornece as informações exatas sobre as colunas que são chaves dos índices, nome, tabela, localização, tamanho e etc. SQL > DESC DBA_Ind_Partitions; Aqui podemos encontrar as informações sobre as partições de índices. SQL > DESC DBA_Constraints;

163

Capítulo 10 – O Administrador de Dados As constraints são regras de integridade ou validação dos dados de entrada da tabela, e nesta view serão passadas todas as regras que estão nas tabelas. Muito bom para poder solucionar erros, por que os desenvolvedores nunca lembram de todas as regras de integridade de dados das tabelas. SQL > DESC DBA_Cons_Columns; Fornece as informações sobre as colunas, quais as definição de regras de integridade, as constraints. SQL > DESC DBA_Sequences; Listagem completa de todos os objetos sequences criados no banco de dados. SQL > DESC DBA_Synonyms; Mostra todos os synonyms criados no banco de dados. Ajuda a não criar synonyms com o mesmo objetivo. SQL > DESC DBA_Users; Esta view mostra todos os usuários do banco de dados, e passa informações precisas como USERNAME, USER_ID, ACCOUNT_STATUS, SESSION, PROCESS e etc. Ela nos ajuda a monitorar nosso banco de dados. Ex.: Quando houver um deadlock. Com esta view, podemos pegar seu ID e PROCESS e matar a sessão, liberando os bloqueios. SQL > DESC DBA_Views; Lista todas as views do banco de dados. SQL > DESC DBA_Updatable_Columns; Esta view permite saber se as colunas de uma tabela podem ser alteradas, inseridas ou excluídas. Muito bom também para melhorar os planos de segurança do banco de dados. SQL > DESC DBA_Types; Lista todos os types criados no banco de dados. SQL > DESC DBA_Type_Attrs; Lista todos os atributos criados para os Types do banco de dados. SQL > DESC DBA_Type_Methods; 164

Capítulo 10 – O Administrador de Dados Descreve os métodos criados aos types do banco de dados. SQL > DESC DBA_Nested_Tables; Mostra a relação entre tabelas nested (aninhadas) e a tabela no qual está relacionada. SQL > DESC DBA_Objects_Tables; Esta view fornece todas as informações sobre os objetos da tabela relativas a utilização dentro do banco de dados. SQL > DESC DBA_Lobs; Aqui temos o mapeamento dos Large Objects (Lobs). Esta view nos permite saber se a LOB está com índice, em qual tabela ela está, nome do segmento e etc. Como muitos desenvolvedores tem problemas para trabalhar com eles, esse recurso do dicionário irá auxiliar muito no momento do desenvolvimento. SQL > DESC DBA_Method_Params; Lista todos os parâmetros associados a cada método dos types do banco de dados. SQL > DESC DBA_Method_Results; Aqui vemos a descrição dos resultados dos types do banco de dados.

OBS.: Embora na listagem tenhamos usado o grupo DBA_ em todas as views, em alguns casos (a depender de como você está conectado) você não terá acesso. Nestes casos use um dos outros dois grupos (USER_ ou ALL_).

O dicionário de dados na prática A maioria das views listadas é de uso exclusivo das atividades de um DBA. Mas grande parte delas pode ser usada por Administradores de Dados não só para desempenhar seu papel, como também para auxiliar na solução de problemas demandados pelos desenvolvedores. A melhor maneira de do usar o dicionário de dados é descobrir qual view pode ter a informação necessária para resolver um problema. Exemplo:

165

Capítulo 10 – O Administrador de Dados Os desenvolvedores estão com problemas na inclusão de dados em uma tabela, pois uma constraint não está permitindo a inclusão de um determinado valor (ou a ausência de um). Um rápida consulta à view Constraints permite detectar o problema. SQL> SQL> 2 3 4 5

column SEARCH_CONDITION FORMAT A40 select TABLE_NAME, SEARCH_CONDITION from All_constraints where owner = 'HR' and CONSTRAINT_TYPE = 'C' order by TABLE_NAME;

TABLE_NAME -----------------------------COUNTRIES DEPARTMENTS EMPLOYEES EMPLOYEES EMPLOYEES EMPLOYEES EMPLOYEES JOB_HISTORY JOB_HISTORY JOB_HISTORY JOB_HISTORY JOB_HISTORY JOBS LOCATIONS REGIONS

SEARCH_CONDITION ---------------------------------------"COUNTRY_ID" IS NOT NULL "DEPARTMENT_NAME" IS NOT NULL "EMAIL" IS NOT NULL salary > 0 "LAST_NAME" IS NOT NULL "HIRE_DATE" IS NOT NULL "JOB_ID" IS NOT NULL end_date > start_date "START_DATE" IS NOT NULL "EMPLOYEE_ID" IS NOT NULL "END_DATE" IS NOT NULL "JOB_ID" IS NOT NULL "JOB_TITLE" IS NOT NULL "CITY" IS NOT NULL "REGION_ID" IS NOT NULL

15 linhas selecionadas.

A coluna SEARCH_CONDITION desta view em particular informa o texto usado para a “regra” da constraint. Já CONSTRAINT_TYPE informa qual o tipo de constraint. No exemplo, nós só queríamos ver as constraints do tipo CHECK. Por isto usamos a condição CONSTRAINT_TYPE = ‘C’.

166

Capítulo 10 – O Administrador de Dados Exercício de revisão Responda as questões a seguir: 1) Quais as atividades mais comuns de um Administrador de Dados? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 2) Em uma empresa com grande estrutura organizacional, qual o papel de um Administrador de Dados? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 3) Qual a vantagem de se utilizar discos diferentes para as tablespaces de dados e índices? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 4) Qual profissional é o mais indicado para criar tablespaces, um AD ou um DBA? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 5) Do ponto de vista do banco de dados, qual usuário é o mais indicado para se criar tablespaces? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 6) Podemos usar o enterprise manager (EM) para criar tablespaces, ou só podemos fazer 167

Capítulo 10 – O Administrador de Dados isto via SQL*Plus? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 7) O que é um Dicionário de Dados? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 8) O dicionário de dados do Oracle tem três grupos principais: User_, All_ e DBA_. Tente descrever a finalidade de cada um destes grupos. ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ 9) Qual dos três grupos é o mais poderoso e o que é necessário para poder acessá-lo? ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

168

Capítulo 10 – O Administrador de Dados RESPOSTAS 1) Apoiar os desenvolvedores, cuidando da criação de tabelas, índices, views e demais objetos de um sistema, além de cuidar da otimização do banco de dados. 2) Manter a consistência das informações e promover a melhor maneira de todos os sistemas compartilharem destas informações. 3) Evitar a concorrência da cabeça de leitura/gravação dos discos 4) Um DBA 5) SYSTEM 6) Sim, podemos. 7) Uma vasta biblioteca de informações, onde existem centenas de views com informações a respeito do banco de dados. 8) User_: tem como finalidade exibir todos os objetos do usuário, criadas no schema do usuário. All_: listar todos os objetos associados ao seu schema e aos outros schemas que você tem autorização. DBA_: exibir todos os objetos do banco de dados. 9) O DBA_. E é necessário ter privilégio de DBA para acessá-lo.

169

. .

170