ESTRUTURAS DE ARMAZENAMENTO

No banco existe uma abstração entre o armazenamento físico e o armazenamento lógico. O armazenamento lógico é feito em segmentos, um exemplo de segmento são as tabelas. Os segmentos são armazenados fisicamente em arquivos de dados, no disco. A abstração entre esses armazenamentos é feita pelas tablespaces, os relacionamentos entre as estruturas físicas e lógicas são mantidos no dicionário de dados.

As Estruturas Físicas

Os arquivos obrigatórios no banco de dados Oracle são: Arquivo de Controle (Controlfile), os Arquivos de Redo Log Online e os Arquivos de Dados (Datafile).

Os arquivos externos que normalmente estão presentes são: o Arquivo de Parâmetros de Inicialização, o Arquivo de Senha e os Arquivos de Redo Log Arquivados, além dos arquivos de log e de rastreamento.

Arquivo de Controle

É responsável pelos ponteiros para o restante do banco de dados: a localização do redo log online e dos arquivos de dados, e dos redo log arquivados mais recentes, se o banco estiver no modo archivelog. O arquivo de controle pode ser multiplexado, ou seja, pode ter várias cópias, essa é uma boa prática, a manutenção dos arquivos de cópia é o próprio banco que faz, você apenas definirá quantas cópias existirão.

Qualquer dano em qualquer cópia do arquivo de controle, fará com que a instância seja finalizada imediatamente. A Oracle não permite um banco funcionando com menor quantidade de arquivos de controle do que o que foi definido.

Arquivos de Redo Log Online

Armazena cada vetor de alteração que foi aplicado ao banco de dados, na ordem em que ocorreram. Se houver falha nos arquivos de dados ou todo o banco for danificado, os vetores poderão ser aplicados aos backups. O redo log é composto de dois tipos de arquivos: os arquivos de redo log online (que são obrigatórios) e os arquivos de redo log arquivados (que são opcionais). Cada banco de dados deve ter ao menos dois grupos de arquivos de redo log online para funcionar. Cada grupo deve ter ao menos dois membros por questão de segurança.

O LGWR grava o log de buffer no grupo de redo log online em uso (current), quando arquivo atual fica “cheio”, o LGWR passa a gravar no segundo grupo, que se torna então o atual. O outro redo log passa então a ser arquivado pelo ARCn, se o banco estiver o modo archivelog. E assim por diante, os grupos vão sendo revezados.

Arquivos de Dados

Deve haver no mínimo dois arquivos de dados, criados no momento da criação do banco de dados. Os arquivos de dados são o repositório de dados, as estruturas físicas visíveis para os administradores do sistema. Onde guardam-se os segmentos lógicos, em outras palavras é onde estão guardados de fato os dados do banco, fisicamente falando. Tudo o que você guarda no banco, nas tabelas, todos as entradas, tudo está guardado lá, o resultado final.

Os arquivos de dados podem ser renomeados, redimensionados, movidos, adicionados ou deletados a qualquer momento no tempo de vida do banco de dados, mas lembre-se de que algumas operações em alguns arquivos de dados podem requerer tempo de inatividade.

Outros Arquivos de Dados (Arquivos Externos)

São arquivos necessários, mas estão externos ao banco de dados, são eles: Arquivo de Parâmetros da Instância, Arquivo de Senhas (Password File), Arquivo de Redo Log Arquivado e o Arquivo de Rastreamento e Log de Alerta.

Arquivo de Parâmetro da Instância: defini os parâmetros para inicialização da instância, o único parâmetro requerido é o DB_NAME, para todos os outros existem valores padrões.

Arquivo de Senhas: Contém um pequeno número (normalmente, menor do que meia dúzia) de nomes de usuário e senhas existentes fora do dicionário de dados e que podem, portanto, ser usados para conectar a uma instância antes do dicionário ficar disponível.

Arquivo de Redo Log Arquivado: Arquivo que recebe o backup do redo log online.

Arquivo de Rastreamento e Log de Alerta: O log de alerta é um fluxo de gravação contínuo de mensagens referente a certas operações críticas que afetam a instância e o banco de dados. Nem tudo é registrado em log: somente os eventos considerados realmente importantes, como inicialização e shutdown, alterações às estruturas físicas do banco de dados e alterações aos parâmetros que controlam a instância. Os arquivos de rastreamento são gerados pelos processos de segundo plano quando eles detectam condições de erro e, às vezes, para reportar certas ações.

Estruturas Lógicas do Banco de Dados

São as estruturas com as quais o usuário interage, são os segmentos, que podem ser: Segmentos de Tabelas, Segmentos de Índices e Segmento de Undo.

As tabelas possuem as linhas de informações, os índices são mecanismos para fornecer acesso rápido a alguma linha específica e os segmentos de undo são estruturas de dados usadas para armazenar as informações necessárias para realizar o rollback de uma transação. Portanto, os administradores de sistema veem os arquivos de dados físicos e os programadores veem os segmentos lógicos, é por meio das tablespaces que acontece a abstração entre lógico e físico.

A Figura abaixo mostra a hierarquia de armazenamento de dados do Oracle, com a separação entre o armazenamento lógico e o físico.

O Dicionário de Dados

É um conjunto de metadados: dados sobre dados. Todo o banco é descrito no dicionário, tanto física quanto logicamente e sobre o seu conteúdo. Um conjunto de segmentos armazenados nas tablespaces SYSTEM e SYSAUX. Não é possível manipular o dicionário com instruções DML, apenas é possível consultar suas visões, a nível de DBA_, ALL_ ou USER_. O que será visto, depende do nível de permissões do usuário que está pesquisando (GRANT). A criação de um dicionário de dados é parte do processo de criação do banco de dados. Ele é mantido subsequentemente pelos comandos de linguagem de definição de dados. Quando você̂ emite o comando CREATE TABLE, está inserindo linhas nas tabelas do dicionário de dados, como quando emite comandos como CREATE USER ou GRANT.

Quem armazena as informações de relacionamento entre as tablespaces e os arquivos de dados é o arquivo de controle do banco, ele que lista os datafiles, e diz a qual tablespace pertencem, sem o Controlfile, não tem como localizar os arquivos que compõem a SYSTEM, por isso que o Controlfile é vital.

Qualquer consulta, bloco PL/SQL, fará acesso ao dicionário de dados. Verificando as estruturas mencionadas pelo código, validando, verificando a existência de grants, etc.

As Estruturas de Processos do Oracle

Um breve "olá"

Faz tempo que não posto nada, no entanto, objetivo é o exame 1Z0-052. Eu sei, já deveria ter feito, mas... Não deu, até agora. Estou voltando aos estudos, e vou tentar postar aqui o que eu puder acerca do conteúdo da segunda prova. O texto abaixo vai dar uma "explicada" nos principais processos de segundo plano do banco de dados. Espero que ajude, boa leitura.

Estrutura de Processos de Segundo Plano

O que são?

Os processos em segundo plano são aqueles que são iniciados juntamente quando a instância é iniciada e executados até que esta seja finalizada. Existem cinco processos que eu diria que são os principais: SMON – System Monitor, PMON – Process Monitor, DBWn – Database Writer, LGWRn – Log Writer, CKPT – Checkpoint Process. Em releases recentes, novos processos foram adicionados, tais como MMON – Manageability Monitor e o MMAN – Memory Manager. Outros não são essenciais, mas existirão em quase todas as instâncias, o ARCn – Archiver e o RECO – Recoverer.

SMON (System Monitor)

É responsável por montar e abrir o banco de dados. Monta o banco, localizando e validando o arquivo de controle. Depois, abre o banco, localizando e validando os arquivos de dados e os arquivos de log. Após aberto, o SMON cuida da organização doméstica da instância, como organizar o espaço no arquivo de dados.

PMON (Process Monitor)

O PMON monitora todos os processos de servidor e detecta os problemas com as sessões. Se uma sessão terminar de maneira anormal, o PMON destruirá o processo de servidor, retornará sua memória ao pool de memória livre do sistema operacional e executará rollback de todas as transações incompletas que possam estar em andamento.

DBWn (Database Writer)

As sessões não têm como regra geral gravar no disco, elas gravam em buffers no cache de buffer do banco, o DBWn que grava os dados no disco.

O DBWn grava os buffers sujos do cache de buffer no banco de dados, mas não assim que são “sujos”, pelo contrário, gravam o menor número de buffers possível, o suficiente para resolver o problema. Pois acessar ao disco prejudica o desempenho, quando uma sessão modifica em bloco é possível que o mesmo bloco seja modificado novamente, pela mesma sessão ou por uma sessão diferente. Em geral, serão gravados no disco buffers que tenham sido pouco acessados ou modificados recentemente. Gravar a menor quantidade possível, o mais raramente possível. Os DBWn gravaram caso não existam mais buffers livres, um excesso de buffer sujo, um tempo limite de 3 segundos quando o DBWn gravará alguns buffers e quando há um checkpoint, quando ocorre um checkpoint todos os buffers sujos serão gravados, o que causará uma diminuição no desempenho e os usuários reclamarão, o ideal é não utilizar checkpoints. Checkpoints automáticos ocorrem em processos de shutdown, quando a instância está sendo fechada, então os buffers são gravados a fim de que os arquivos de dados estejam sincronizados. O checkpoint forçado pode ser realizado com a instrução: alter system checkpoint.

Também existem os checkpoints parciais, que é quando um arquivo de dados ou tablespace é colocado em Offline, quando o tablespace é colocado no modo backup, quando o tablespace é configurado para somente leitura. São menos drásticos do que um checkpoint total e ocorrem apenas nessas circunstâncias.

LGWRn – Log Writer

O LGWRn grava o conteúdo do buffer de log nos arquivos de log no disco. Uma gravação do do buffer de log nos arquivos de redo log online é geralmente referenciada como fazer flush do buffer de log.

Quando você aplica uma alteração a um bloco no cache de buffer, o vetor dessa alteração é gravado o log de buffer, o LGWRn grava o conteúdo do buffer do log no redo log online no disco, praticamente em tempo real. Quando uma sessão emite um COMMIT, a sessão é suspensa enquanto o LGWRn grava o buffer no disco, após gravar, o commit é confirmado e a transação se torna irreversível. Existem três circunstancias que obrigarão o LGWR a fazer flush para o buffer de log: se uma sessão emitir um comando COMMIT, se o buffer de log estiver 1/3 completo e se o DBWn estiver para gravar buffers sujos.

CKPT (Checkpoint Process)

CKPT sinalização quando os buffers sujos devem ser gravados no disco. A partir da versão 8i passam a ser possíveis fazer checkpoints incrementais. O mecanismo de checkpoint incremental instrui o DBWn a gravar os buffers sujos a uma taxa constante, para que haja sempre um intervalo previsível entre o DBWn (que grava os blocos usando um algoritmo preguiçoso) e o LGWR (que grava os vetores de alteração praticamente em tempo real). Os checkpoints incrementais resultam em um desempenho muito mais suave e tempos de recuperação mais previsíveis do que o mecanismo de checkpoint completo mais antigo. Os checkpoints totais só ocorreram se houver uma solicitação do DBA ou como parte de uma processo de shutdown do banco de dados.

MMON (Manageability Monitor)

Processo introduzido após a versão 10g, é o processo de ativação de grande parte dos recursos de automonitoramento e autoajuste do banco de dados. O MMON guarda no dicionário de dados, informações que captura as estatísticas da SGA. Podem ser armazenadas indefinidamente, mas em geral ficam oito dias. O MMON também monitora o banco de dados e a instância para verificar se alertas devem ser disparados.

MMAN (Memory Manager)

O MMAN permite o gerenciamento automático de memória. Antes da versão 9i do banco de dados, o gerenciamento de memória no ambiente Oracle estava longe de ser satisfatório. A memória PGA associada aos processos de servidor de sessão era não transferível: o processo de servidor ocupava a memória do pool de memória livre do sistema operacional e nunca a retornava – mesmo que ela só́ fosse necessária por um curto período de tempo. As estruturas da memória SGA eram estáticas: definidas na hora da inicialização da instância e inalteráveis a menos que a instância fosse desligada e reiniciada.

A versão 9i mudou isso: as PGAs podem crescer e encolher, com o servidor passando memória para as sessões sob demanda, ao mesmo tempo garantindo que a memória PGA total alocada permaneça dentro de certos limites. A SGA e seus componentes (com a notável exceção do buffer de log) também pode ser redimensionada. A versão 10g automatizou o redimensionamento da SGA: o MMAN monitora a demanda por estruturas de memória SGA e pode redimensioná-las conforme o necessário. A versão 11g leva o gerenciamento de memória mais adiante: tudo o que o DBA precisa é definir um objetivo global para o uso da memória e o MMAN observará a de "manda pela memória PGA e a memória SGA e alocará a memória para as sessões e para as estruturas SGA conforme o necessário, mantendo o total de memória alocada dentro de um limite definido pelo DBA.

ARCn (Archiver)

Processo opcional do Oracle, mas geralmente muito utilizado no dia-a-dia empresarial. Todos os vetores de alteração aplicados aos blocos de dados são gravados no buffer de log (pelas sessões que fazem as alterações) e, em seguida, nos arquivos de redo log online (pelo LGWR). Os arquivos de redo log online são de número e tamanho fixos: uma vez que tenham sido preenchidos, o LGWR os sobrescreverá com mais dados de redo. O tempo decorrido antes que isso aconteça depende do tamanho e do número de arquivos de log online e da quantidade de atividade DML (e, portanto, a quantidade de informações de redo gerada) no banco de dados. Isso significa que o redo log online só́ armazena vetores de alteração de atividade recente. Para preservar um histórico completo de todas as alterações, os arquivos de log online devem ser copiados à medida que forem preenchidos e antes de serem reutilizados. O ARCn é responsável por isso. Desde que essas cópias, conhecidas como arquivos de redo log arquivados, estejam disponíveis, será́ sempre possível recuperar o banco de dados de qualquer dano restaurando os backups do arquivo de dados e aplicando neles os vetores de alteração extraídos de todos os arquivos de redo log arquivados gerados desde que os backups foram criados. Então, a recuperação final, que irá recuperar o backup atualizado, virá dos arquivos de redo log online. A maioria dos bancos de dados transacionais de produção executarão no modo archive log, o que significa que o ARCn é iniciado automaticamente e que o LGWR não pode sobrescrever um arquivo de log online até que o ARCn o tenha arquivado com êxito em um arquivo de log arquivado.

RECO (Recoverer Process)

Uma transação distribuída é uma transação que envolve atualizações a dois ou mais bancos de dados, é projetada por programadores e opera por meio de links de bancos de dados.

Considere este exemplo:

update employees set salary=salary * 1.1 where employee_id=1000;

update employees@dev set salary=salary * 1.1 where employee_id=1000;
commit;

A primeira atualização é aplicada a uma linha no banco de dados local. A segunda, a uma linha em um banco de dados remoto identificado pelo link de banco de dados DEV.

O comando COMMIT instrui os dois bancos de dados a confirmar a transação, que consiste em ambas as instruções. As transações distribuídas requerem um commit de duas fases (two phase commit). O commit em cada banco de dados deve ser coordenado: se um falhar e o outro for bem-sucedido, os dados globais estariam em um estado inconsistente. Um commit de duas fases prepara cada banco de dados instruindo seus LGWRs para fazer flush do buffer de log para o disco (a primeira fase) e uma vez que isso é confirmado, a transação é marcada como confirmada em todos os lugares (a segunda fase). Se algo sair errado em algum lugar entre as duas fases, o RECO assume o controle para cancelar o commit e fazer um rollback do trabalho em todos os bancos de dados.

Existem outros vários processos de segundo plano, os quais podem ser consultados com a instrução:

select program from v$process order by program;

Funções de Grupo

Como o nome sugere, as Funções de Grupo atuam sobre conjuntos de linhas trazendo o resultado da consulta por grupo. As Funções de Linha atuavam em cada linha da consulta, enquanto que as Funções de Grupo atuam sobre um conjunto de linhas. As principais Funções de Grupos são:

Com o uso do DISTINCT a função irá considerar apenas dados não duplicados, quando não especificado o default é a forma ALL é que considera todas as linhas, inclusive as linhas duplicadas;

Todas as funções ignoram as linhas com valores NULOS, ou seja, para que essas linhas sejam consideradas no resultado, deverá ser utilizado alguma função que trate NULOS, como NVL, NVL2 ou COALESCE.

AVG e SUM

As funções AVG e SUM só podem ser utilizadas com números, visto que calculam a média e a soma de valores , respectivamente.

MIN e MAX

As funções MIN e MAX podem ser utilizadas para tipos numéricos, de caractere ou de data.

COUNT

A função COUNT pode ser utilizada de três maneiras:

1. COUNT(*): Retorna a quantidade de linhas que atendem a condição do SELECT, se não houver cláusula SELECT, retornará a quantidade de linhas existente na tabela;
2. COUNT(expr): Retorna o número de valores não nulos da expressão|coluna especificada;
3. COUNT(DISTINCT expr): Retorna o número de valores não nulos exclusivos da expressão|coluna especificada;

Cláusula GROUP BY

O uso da cláusula GROUP BY permite que seja feito agrupamento do resultado por um grupo de colunas especificado. 

Se você utilizar uma Função de Grupo e também listar uma outra coluna na consulta SELECT, será obrigatório o uso da cláusula GROUP BY.

Não é possível utilizar na cláusula GROUP BY apelidos de colunas, como ocorre na ORDER BY.

Exemplos de utilização das Funções de Grupo

Exemplo 1:

O exemplo a seguir abrange as funções AVG, MIN, MAX. Digamos que uma escola armazene a média final anual de cada aluno em uma tabela, você precisa fazer um relatório informando a menor, a maior, a média geral das notas, independente da série.

Para efeito de visualização segue uma consulta mostrando os dados existentes nas tabelas, são 19 alunos cadastrados, segue abaixo uma imagem da consulta feita sem a utilização das funções de grupo, mostrando a nota final de cada um (clique na imagem para ampliar):

Agora vamos fazer o relatório da conforme solicitado, deve exibir a média das notas, a maior nota e a menor nota, apenas filtrando pelo ano letivo 2014. Para essa consulta, não será necessário fazer o join entre tabelas, tudo o que nós precisamos exibir está na tabela NOTAS_FINAIS. A consulta seria:

É necessário atentar para a função AVG, nenhum dos alunos cadastrados possuía a NOTA_FINAL null, sendo assim o calculo está correto. Caso existisse algum aluno sem a nota final, a função AVG desconsideraria esse registro, o que traria uma inconsistência no resultado, nesse caso você precisa usar as funções NVL, NVL2 ou COALESCE para tratar os valores nulos, como já mencionado anteriormente. No exemplo, eu não usei a função SUM, mas enfim, ela soma os valores, sendo necessário ficar atentos aos valores nulos, que também seriam ignorados.

Exemplo 2:

Agora você precisará agrupar os resultados por turma, nesse caso precisaremos unir as tabelas, fazendo um JOIN, pois a tabela NOTAS_FINAIS se relaciona com a tabela ALUNO, que por sua vez possui o COD_TURMA, que é o código de cada turma. 

Primeiro, agrupando pelo código da turma, mas sem exibir o código no relatório. Você pode agrupar por uma coluna sem que esta esteja listada no SELECT. Veja:

Depois, a mesma consulta, com a diferença que eu estou exibindo o código das turmas, no primeiro exemplo a cláusula GROUP BY não é obrigatória, já neste exemplo ela é obrigatória, todas as colunas que são citadas no SELECT que NÃO estejam em uma função de GRUPO devem estar obrigatoriamente no GROUP BY, veja a imagem:

Observação: Lembrando que as funções MIN e MAX podem ser utilizadas também com tipos de dados VARCHAR2, CHAR ou DATE. Para os tipos de dados caractere, será considerada a ordem alfabética, no caso de datas será considerado o fator cronológico.

Exemplo 3:

Agora um exemplo utilizando a função COUNT. Como vimos anteriormente, esta função pode ser utilizada em três formas, será feito um exemplo para cada forma:

• COUNT(*)

• COUNT(expr)

Observação: Caso algum dos registros estivesse com o campo ANO_LETIVO nulo, essa linha não seria contabilizada nesse resultado, pois só conta os registros não nulos.

• COUNT(DISTINCT expr)

Observação: Nós sabemos que existem 19 registros nessa tabela, no entanto todos os registros de ANO_LETIVO são preenchidos com o valor 2014, nesse exemplo utilizamos a palavra-chave DISTINCT, o resultado é referente aos valores não nulos e EXCLUSIVOS na coluna ANO_LETIVO, sendo assim temos apenas 1 linha com valor exclusivo.

Cláusula HAVING

A cláusula HAVING é utilizada para restringir a consulta a partir de uma Função de Grupo, não é permitido usar a cláusula WHERE para essas restrições. No Exemplo 2 acima, exibimos a média, a menor e a maior nota final agrupadas por turmas. Digamos que só interesse saber a turma que teve resultados médios ruins, abaixo da média, ou seja < 7,0. Nesse caso, seria necessário filtrar a consulta pela Função de Grupo AVG(NOTA_FINAL) < 7.0, esse filtro é feito utilizando a cláusula HAVING. Seguem a seguir dois exemplos, um com erro e outro com a sintaxe correta:

Com erro:

Observação: Como podemos ver, o erro ORA-00934 diz que a função de grupo está sendo usada de forma não permitida.

Sintaxe correta:

Observação: Como podemos ver, a cláusula HAVING está sendo usada para restringir os resultados onde a média foi abaixo de 7.0.

Por hoje, é só! Obrigada pela visita. =)

Expressões Condicionais

Várias vezes você precisa utilizar a lógica IF-THEN-ELSE em uma consulta, por questões de regra de negócio por exemplo. A expressão CASE e a função DECODE resolvem essa necessidade, sendo a função DECODE exclusiva da sintaxe Oracle,  a expressão CASE está de acordo com o padrão ANSI SQL.

CASE

Sintaxe:

Quando você utiliza a expressão CASE o servidor verifica as condições, na ordem, a primeira expressão verdadeira é a que terá o valor da cláusula THEN retornado, caso nenhuma condição seja verdade e existir um valor default definido na cláusula ELSE esse valor será retornado, caso nenhuma condição seja verdadeira e não exista valor default definido o valor null é retornado.

Veja o exemplo abaixo, digamos que o departamento pessoal solicita uma consulta onde você deverá exibir em que nível de carreira um funcionário se enquadra de acordo com o salário dele, a regra é:

Nível de carreira Júnior para salários até 3.000,00R$, Pleno para salários entre 3.000,01R$ à 5.000,00R$, Sênior para salários entre 5.000,01R$ à 7.000,00R$, Gerente para salários entre 7.000,01R$ à 10.000,00R$, caso nenhuma das condições fossem verdadeiras o nível de carreira em questão é o de Diretor. A regra foi aplicada na consulta da seguinte forma:

DECODE

Sintaxe:

A função DECODE também implementa a lógica IF-THEN-ELSE, o valor da coluna ou expressão passada no primeiro parâmetro será comparada ao search1, se for igual o result1 será retornado, assim será para todos os outros pares de parâmetros listados na função, você também pode definir um valor default, que será retornado se a comparação não satisfazer nenhum critério de busca listado nos parâmetros, se não existir valor default definido será retornado null. A diferença com a expressão CASE é que no DECODE você não consegue fazer comparações usando testes lógicos diferente do da igualdade, como no exemplo dado no CASE, onde um teste lógico diferente do teste de igualdade é feito na cláusula WHEN (WHEN SALARIO BETWEEN 3000.01 AND 5000 THEN...), é feito um teste considerando um conjunto de valores.

No exemplo abaixo, o caso seria nomear o departamento de acordo com o ID, existe a tabela DEPARTAMENTOS que contém a descrição de cada departamento, mas suponha que você queira uma descrição diferente da "oficial":

Para DEPARTAMENTO_ID igual a 3, teremos o a descrição "MANDA-CHUVA", para DEPARTAMENTO_ID igual a 5, teremos a descrição "PEÃO" e os demais casos serão listados como "NOT FOUND".

É isso aí. Espero ter ajudado! =)

Funções Gerais

As Funções Gerais são funções de linha que você pode utilizar com qualquer tipo de dados. As mais utilizadas são:

• NVL(entrada, saída_se_null);
• NVL2(entrada, saída_se_não_null, saída_se_null);
• NULLIF(entrada_1, entrada_2);
• COALESCE(entrada_1, entrada_2, ..., entrada_n).

DEFINIÇÕES

A função NVL(entrada, saída_se_null) recebe o parâmetro de entrada e caso o seu valor seja null ela retorna saída_se_null, que pode ser um valor literal ou uma expressão e até mesmo uma outra coluna. Essa função é muito importante ao realizar operações aritméticas entre colunas, pois se um número é aritmeticamente manipulado com valores nulos o resultado será um null e não um número. Caso a coluna ou variável ou expressão utilizada na operação não garanta que os valores serão todos não-nulos, você pode utilizar a função NVL para definir qual valor deverá ser considerado ao invés de null. O tipo retornado da função NVL será sempre o mesmo tipo da entrada.

A função NVL2(entrada, saída_se_não_null, saída_se_null) recebe o parâmetro entrada e realiza o tratamento, caso ele não seja null ou not null, ao contrário da NVL, que só trata os valores null. Se o valor de entrada for não-nulo, será retornado a expressão, valor ou coluna especificada em saída_se_não_null, caso contrário (sendo nulo) o valor que será retornado será a expressão, valor ou coluna especificada em saída_se_null.

A função NULLIF(entrada_1, entrada_2) compara os parâmetros entrada_1 e entrada_2 referente a sua igualdade, se esses valores forem igual a função retorna null, caso contrário entrada_1 será retornado, lembrando que avalia a igualdade também de strings de caractere, não apenas números.

A função COALESCE(entrada_1, entrada_2, ..., entrada_n) retorna o valor do primeiro parâmetro não-nulo, todas as entradas são do mesmo tipo de dados.

EXEMPLOS

O primeiro exemplo é a respeito da utilização das funções NVL e NVL2 (clique na imagem para ampliar):

Considerações:

• Você pode observar que a utilização do NVL na coluna ENDERECO fez com que ao invés da consulta exibir null nos endereços não cadastrados, exibisse a string 'FUNC SEM ENDERECO CADASTRADO', coluna que chamei de END_NVL;
• A NVL2 foi utilizada para classificar se o endereço cadastrado está ou não completo (null ou not null respectivamente);
• Coloquei um exemplo do tipo de erro que pode ocorrer ao se trabalhar com valores nulos em expressões aritméticas, na coluna que nomeei de SAL_PLUS_COMISSAO, nós temos o salário do funcionário somado ao valor real da sua comissão, a coluna VAL_COMISSAO_PERC contém o valor da porcentagem da comissão do funcionário, sendo que alguns não recebem comissão, estando esse campo null para esses colaboradores. Isso faz com que o resultado do salário do empregado que não possui comissão, somado com a comissão null, seja também null, o que não é verdade, o valor tem que ser o próprio salário sem o "plus" da comissão. A forma certa está na coluna que nomeei de SAL_PLUS_COMISSAO_NVL, que traz o resultado correto para os casos em que o funcionário possui ou não comissão, utilizando o NVL e tratando o percentual de comissão para 0(zero) caso o valor na coluna seja null. Deu para entender? =)

O segundo exemplo é a respeito da utilização das funções COALESCE e NULLIF  (clique na imagem para ampliar):

Considerações:

• A função COALESCE foi utilizada para recuperar o primeiro telefone não nulo, o que eu chamei de TEL_VALIDO;
• A função NULLIF foi usada para comparar os campos TELEFONE_1 e TELEFONE_2, como em nenhum dos casos os campos são iguais, o retorno foi o TELEFONE_1, nesse caso cabe uma observação, sabemos que se os valores forem iguais a NULLIF retornará null, você pode ver que alguns registros retornaram null (linhas 1,2 e 5), mas nesse caso o resultado é null não por TELEFONE_1 e TELEFONE_2 serem iguais, mas sim pelo fato de TELEFONE_1 ser null, nós poderiamos utilizar a função NVL em conjunto com a NULLIF, para garantir que só retornaria null se os valores das duas colunas fossem realmente iguais.

É isso. Espero ter ajudado!

Funções de Conversão

A conversão de dados no Oracle pode ocorrer de duas formas: Explicitamente ou Implicitamente.

Conversão Implícita de Tipos de Dados

O Oracle faz a conversão implícita de dados, para os casos em que o tipo de dados do valor usado é diferente do tipo esperado. Por exemplo, digamos que você faça uma consulta por um valor de salário, sendo a coluna SALARIO numérica, o tipo que se espera para a comparação é um número, mas a consulta SALARIO = '2000' será reconhecida, pois implicitamente o Oracle converte a string '2000' para o número 2000 e recupera as linhas que se adequam a essa condição. 

O Oracle pode converter automaticamente os tipos:

Para que a expressão VARCHAR2 ou CHAR seja convertida em NUMBER com sucesso, é necessário que não exista caractere diferente de números formando a string, por exemplo, SALARIO = '215A3 7', ocorreria um erro de número inválido. De VARCHAR2  ou CHAR para DATE é necessário que a string esteja em um modelo reconhecido, os quais serão descritos adiante.

Conversão Explícita de Tipos de Dados

As Funções de Conversão Explícitas mais comuns são TO_CHAR, TO_NUMBER e TO_DATE. Segue detalhes sobre o uso de cada uma delas:

• TO_CHAR (number | date, [fmt], [nlsparams]): Converte um número ou data em uma string de caracteres, fmt refere-se ao modelo de formato. Conversão de Número: o parâmetro nlsparams define os seguintes caracteres, que são retornados pelos elementos de formato numérico - Caractere Decimal, Separador de Grupos, Símbolo de Moeda Nacional, Símbolo de Moeda Internacional. Se não for definido, será usado o padrão da sessão. Conversão de Data: O parâmetro nlsparams especifíca o idioma em que são retornadas as abreviações e dias, meses. Se for omitido será retornado o padrão da sessão.
• TO_NUMBER (char, [fmt], [nlsparams]): Converte strings de caractere composta de números em um número. O formato fmt pode ser específicado. O parâmetro nlsparams funciona como na função TO_CHAR, referente a conversão de números.
• TO_DATE (char, [fmt], [nlsparams]): Converte uma string que representa uma data em uma data com o formato (fmt) informado, o valor default de fmt será DD-MON-YY.

Função TO_CHAR com Datas

Você pode usar a função TO_CHAR para escrever uma data com modelo diferente do modelo (formato) default, você pode manipular o resultado de acordo com a sua necessidade.

TO_CHAR (data, 'formato')

Observações importantes:

• O modelo do formato deve ser delimitado por aspas simples;
• O formato informado pode ser qualquer elemento de formato de data válido, ex: DD, MM, MI, SS, YYYY, D, MONTH, YEAR, DAY, e assim por diante;

Exemplos de Elementos de Formato de Data Válidos

Outros formatos:

• / . , : A pontuação é reproduzida no resultado;
• "of the": String entre aspas é reproduzida no resultado.

Utilização de sufixos que alteram a exibição de números:

• TH: Número ordinal;
• SP: Números por extenso;
• SPTH ou THSP: Números decimais por extenso.

Função TO_CHAR com Números

Você pode utilizar a função do TO_CHAR com números, para transformar o número em uma string de caratere com um formato desejado. Você pode utilizar os elementos de formato abaixo:

Função TO_NUMBER e TO_DATE

Você pode converter uma string de caracteres em um número ou uma data, utilizando as funções TO_NUMBER e TO_DATE.

TO_NUMBER(char [,formato]);

TO_DATE(char [,formato]);

Para que a conversão ocorra com sucesso na conversão para números, a string não pode conter elementos inválidos, como letras, ou símbolos. Caso contrário, o erro informando que o número não é válido é retornado. Ex: SELECT TO_NUMBER('23JR456') FROM DUAL; Ocorrerá o erro ORA-01722: número inválido.

Caso utilize pontos e vírgulas, utilize o parâmetro de formatação para que o número seja convertido. Ex: SELECT TO_NUMBER('23,456.00','99,999.99') FROM DUAL;

No caso da conversão para datas, os elementos válidos de formatação são os mesmos listados na utilização de TO_CHAR com tipo data.

Elemento de Formato de Data RR

O elemento de data RR é semelhante ao elemento YY, também recupera o ano. Mas o RR se comporta diferente ao recuperar o século, considerando a data atual e os últimos 2 dígitos do ano informado. Observe o comportamento no quadro abaixo:

Espero que tenha sido útil! =)

Funções de Data

Antes de começar o assunto, é importante mencionar a função SYSDATE, que é uma função nativa do Oracle e a sua função é recuperar a data e hora atual do servidor do banco de dados. Pode ser usada em consultas e em objetos PL/SQL. Um exemplo de como utilizar:

SELECT SYSDATE FROM DUAL;

Funções de Data

As Funções de Data operam em datas Oracle, quase todas retornam datas, a única exceção é a função MONTHS_BETWEEN, que retorna um valor numérico, correspondente a quantidade de meses entre duas datas.

Algumas das mais utilizadas sãos:

• MONTHS_BETWEEN (data_1, data_2): Obtém a quantidade de meses entre a data_1 e a data_2, o resultado pode não ser um número inteiro, que representa uma parte do mês. Caso a data_1 for anterior a data_2 o resultado será negativo;
• ADD_MONTHS (data, n): Adiciona n (número de meses) meses à data, n deve ser inteiro e pode ser negativo, fazendo nesse caso uma subtração de meses;
• NEXT_DAY (data, 'char'): Obtém a data do próximo dia da semana ('char'), após a data em questão. O valor de 'char' pode ser um número que represente um dia ou uma string de caracteres;
• LAST_DAY (data): Retorna a data do último dia do mês que contém a data em questão.
• ROUND (data [, 'fmt']): Retorna a data arredondada até a unidade especificada pelo modelo de formato fmt. Se o modelo fmt for omitido, já que não é obrigatório, a data será arredondada até o dia mais próximo;
• TRUNC (data [,fmt]): Retorna a data com a parte do horário do dia truncada até a unidade especificada pelo modelo de formato fmt. Se o modelo de formato fmt não for especificado, a data será truncada até o dia mais próximo.

Abaixo uma imagem com exemplos acerca da utilização de cada função, clique na imagem para expandi-la:

Observações Importantes Sobre o Exemplo:

• A primeira coluna (MONTHS_BETWEEN) retornou a quantidade de meses desde 11/07/1986 até 30/10/2014, aproximadamente 339 meses, a parte decimal é referente aos dias entre 11 e 30 de outubro. Se a pesquisa fosse MONTHS_BETWEEN('11/10/2014','11/07/1986') o resultado seria 339, um número inteiro;
• ADD_MONTHS adicionou 28 meses à data 11/07/1986;
• A função NEXT_DAY recupera a data da próxima segunda-feira após 30/10/14, e a resposta é 03/11/14. É importante checar em que idioma está a base, pois se o idioma fosse inglês, a consulta retornaria um erro de dia inválido da semana (ORA-01846: não é um dia da semana válido) e o correto seria usar 'MONDAY', para verificar o idioma basta realizar uma consulta simples utilizando conversão da data para um caractere: SELECT TO_CHAR(SYSDATE , 'DAY/MONTH/YEAR') FROM DUAL; É possível utilizar também a numeração referente ao dia da semana, por exemplo, 1 é o número para o domingo, assim temos uma numeração de 1 à 7 para os dias da semana, essa mesma consulta do exemplo pode ser substituída por NEXT_DAY('30/10/2014', 2) AS NEXT_DAY;
• LAST_DAY: Retornou o último dia do mês de outubro;
• ROUND: São três situações no exemplo, no primeiro o parâmetro de modelo não é informado, desse modo a data é arredondada para o próximo dia, 31/10/2014. No segundo exemplo, o parâmetro de formato é informado e solicita que a data seja arredondada para o próximo ano, o resultado foi que a data de 30/10/2014 foi arredondada para 01/01/2015. No terceiro exemplo, a data foi arredondada considerando o mês, o resultado 01/11/14;
• TRUNC: São três situações no exemplo, no primeiro o parâmetro de modelo não é informado, desse modo a data é truncada para o dia 30/10/2014 (o mesmo dia, mas sem a hora/minuto/segundo). No segundo exemplo, o parâmetro de formato é informado e solicita que a data seja truncada considerando o ano, o resultado foi que a data de 30/10/2014 foi truncada para 01/01/2014. No terceiro exemplo, a data foi truncada considerando o mês, o resultado 01/10/14;
• Sobre o ROUND e TRUNC percebemos que são bastante parecidas, sendo que a função ROUND busca sempre a data posterior à data de entrada da função, enquanto que a TRUNC vai pegar sempre a data mais próxima anterior.

É isso. =)