Tipos de Dados - Demais tipos

    Além dos tipos de dados que já vimos, onde podemos manipular e armazenar, caracteres, dígitos e datas, temos outros tipos de dados que veremos abaixo.

LONG

    Atualmente não é mais recomendável utilizar colunas do tipo LONG, pois estão defasadas, devendo ser utilizadas BLOB, CLOB ou NCLOB, sendo recomendando também a conversão de colunas LONG para LOB. Contudo, o banco de dados ainda suporta esse tipo de dados por questões de retro compatibilidade.

O tipo de dados LONG é um tipo que permite armazenar sequências de caracteres de até 2Gb. Esse tipo de dados possui várias peculiaridades entre elas:

• Somente uma coluna long por tabela;
• Colunas long não podem ser utilizadas em índices;
• Não podem ser utilizadas em expressões regulares;
• Não podem ser utilizadas na cláusula WHERE ou em constraints de integridade.
• Não podem ser utilizadas em GROUP BY, ORDER BY, CONNECT BY ou no select com função DISTINCT.

RAW e LONG RAW

    Os tipos RAW e LONG RAW são utilizados para armazenar dados que não são convertidos explicitamente pelo banco de dados Oracle quando move os dados entre diferentes sistemas. Podem ser utilizados para armazenamento de áudios, gráficos, imagens ou documentos. Assim como ocorre com o tipo LONG, é recomendável utilizar os tipos de dados LOB ao invés do LONG RAW. Tipos RAW funcionam como VARCHAR2, pois tem tamanho variável. Os tipos RAW podem ser implicitamente convertidos em CHAR, VARCHAR2 ou LONG e também podem ser explicitamente convertidos em hexadecimal utilizando a função RAWTOHEX e HEXTORAW. 

ROWID

    Todas as linhas no armazenadas no banco de dados Oracle tem um endereço específico que é utilizada para a organização dos dados dentro do banco, esses endereços de cada linha são chamados de rowids. Para saber qual o endereço de um linha basta incluir a pseudocoluna ROWID na área de select da consulta. A pseudocoluna ROWID também pode ser utilizada no campo WHERE.

    Os valores de rowid são formados na base de 64, podem conter caracteres de a-z ou A-Z, dígitos de 0-9 e os sinais + (mais) e / (barra). O tamanho do conteúdo de rowid vai depender também do sistema operacional utilizado no banco de dados.

UROWID

    Urowid significa universal rowid e são utilizados quando os rowids não estão armazenados dentro do banco de dados Oracle, podendo ser tabelas externas do banco ou mesmo de outros bancos de dados acessados pelo Oracle por meio de Gateways.

Tipos LOB

    O tipo de dados LOB significa Large Objects, ou seja objetos grandes. Podemos classificar o dados LOB em internos e externos de acordo com a maneira que é armazenado no banco de dados, os  LOBs internos são armazenados dentro do banco de dados e podem ser do tipo CLOB, NCLOB ou BLOB, enquanto o LOB externo é armazenado fora do banco de dados e é chamado de BFILE. Esses tipos de dados servem para armazenar informações grandes ou fora de padrões, como áudios e imagens. Os tipos LOB podem armazenar até 4000 bytes na forma de coluna, no caso de ter mais de 4000bytes serão sempre armazenados externamente. Colunas LOBs possuem localizadores que referenciam se os valores estão armazenados internamente ou externamente, quando fazendo um select em uma coluna LOB, o banco de dados retorna os localizadores dos registros dessa coluna.

CLOB

    O tipo de dados CLOB armazena dados em formato caractere tanto de byte único como multibyte, são suportados caracteres de tamanho fixo ou variável. Objetos CLOB tem suporte completo para transações, podendo ter commits ou rollbacks em seus dados. As colunas do tipo CLOB podem armazenar até (4Gb-1) * (valor do parâmetro CHUNK), caso o valor para o parâmetro CHUNK padrão tenha sido utilizado na definição da coluna nesse caso o valor que poderá ser armazenado será definido por (4Gb -1) * (tamanho do bloco do banco de dados).

NCLOB

    O tipo de dados NCLOB armazena dados em formato Unicode, são suportados caracteres de tamanho fixo ou variável com ambos utilizando o conjunto de caracters nacional. Objetos NCLOB tem suporte completo para transações, podendo ter commits ou rollbacks em seus dados. As colunas do tipo NCLOB podem armazenar até (4Gb-1) * (valor do parâmetro CHUNK), caso o valor para o parâmetro CHUNK padrão tenha sido utilizado na definição da coluna nesse caso o valor que poderá ser armazenado será definido por (4Gb -1) * (tamanho do bloco do banco de dados).

BLOB

    BLOB significa Binary Large OBjects e armazena dados binários grandes e não estruturados, objetos BLOB tem suporte completo para transações, podendo ter commits ou rollbacks em seus dados. As colunas do tipo BLOB podem armazenar até (4Gb-1) * (valor do parâmetro CHUNK), caso o valor para o parâmetro CHUNK padrão tenha sido utilizado na definição da coluna nesse caso o valor que poderá ser armazenado será definido por (4Gb -1) * (tamanho do bloco do banco de dados).

BFILE

    O tipo BFILE serve para acessar arquivos binários que estão armazenados fora do banco de dados. É possível realizar a troca do caminho onde o arquivo está armazenado ou mesmo o nome do arquivo sem afetar os dados do mesmo utilizando a função BFILENAME. Os dados dentro de um BFILE são apenas para leitura, não sendo possível realizar alterações e é tarefa do DBA garantir que os processos do banco de dados Oracle possuam permissão de leitura nos arquivos BFILE. Um arquivo BFILE poderá ter no máximo 264-1 bytes, contudo esse valor poderá ser menor de acordo com o sistema operacional.

JSON

    Introduzido a partir da versão 21c serve para armazenar arquivos JSON de maneira nativa em formato binário, isso melhora o desempenho, pois não há mais necessidade de conversão. Além do tipo de dados JSON, os arquivos JSON são suportados pelos tipos VARCHAR2, BLOB e CLOB.

Fonte:

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Data-Types.html#GUID-A3C0D836-BADB-44E5-A5D4-265BA5968483

Tipos de Dados - Datas

    Os tipos de dados que trabalham com datas se dividem em 2 tipos: datahora e intervalos, onde os tipos datahora armazenam datas e horários de fato e intervalos retornam os intervalos entre 2 datas.

    Lembrando que sempre que for trabalhar com tipos de dados de data é bom verificar antes como está a configuração do banco de dados para estes tipos de dados, pois isso pode impactar nos resultados esperados. Para verificar como estão as configurações podemos fazer 2 consultas na tabela dummy DUAL.

    select DBTIMEZONE from dual;

    select SESSIONTIMEZONE from dual;

Tipo de data	Valores validos para Data	Valores validos para  INTERVALOS
YEAR	-4712 até 9999 (excluindo ano 0)	Qualquer inteiro positivo ou negativo
MONTH	01 até 12	0 até 11
DAY	01 até 31 (limitado de acordo com o mês e ano)	Qualquer inteiro positivo ou negativo
HOUR	00 até 23	0 até 23
MINUTE	00 até 59	0 até 59
SECOND	00 até 59.9(n), onde 9(n) é a precisão de milissegundos . A opção 9(n) não se aplica para DATE.	0 até 59.9(n), onde 9(n) é a precisão de milissegundos
TIMEZONE_HOUR	-12 até 14 (Esses valores consideram horários de verão) Não se aplica para DATE ou TIMESTAMP.	Não se aplica
TIMEZONE_MINUTE	00 até 59. Não se aplica para DATE ou TIMESTAMP.	Não se aplica
TIMEZONE_REGION	Consulte a coluna TZNAME na view V$TIMEZONE_NAMES. Não se aplica para DATE ou TIMESTAMP.	Não se aplica
TIMEZONE_ABBR	Consulte a coluna TZABBREV na view V$TIMEZONE_NAMES. Não se aplica para DATE ou TIMESTAMP.	Não se aplica

DATE

    O tipo de dados DATE armazena informações de dia e hora, podemos até mesmo utilizar outros tipos de dados para armazenar datas, como um tipo de dados de caracteres por exemplo, contudo essa não é a solução mais adequada visto que para isso temos os tipos de dados de datas, em que é possível diferenciar cada parte da data como dia, mês, ano, hora, minuto e segundo. Também é possível fazer conversões de dados armazenados em colunas do tipo de data para formatos de número ou caractere através das funções TO_CHAR e TO_NUMBER, que veremos mais adiante. Utilizando essas funções é possível criar uma data a partir de um literal.

Como faço para descobrir a data atual no banco de dados? Simples, basta realizar uma consulta na tabela DUAL retornando SYSDATE.

TIMESTAMP

    O Tipo TIMESTAMP funciona como o tipo DATE mas ele armazena a data, a hora e a fração de segundos, podendo ir de 0 a 9 casas para os milissegundos sendo o padrão o valor 6. Assim como é possível retornar a data do sistema, é possível retornar o valor atual de um timestamp no sistema.

    Também é possível utilizar a função TO_TIMESTAMP para converter, por exemplo, uma data para o formato timestamp.

TIMESTAMP WITH TIME ZONE

  O tipo de dados TIMESTAMP[(frações de milissegundos)] WITH TIME ZONE funciona da mesma maneira que o tipo TIMESTAMP, porém, armazena além do timestamp o time zone, que pode estar no formato TIMEZONE REGION NAME ou TIME ZONE OFFSET. Assim como no tipo TIMESTAMP, a fração de segundos pertimitida pode variar de 0 até 9 e quando omitida na declaração da coluna o valor padrão assumido será 6.

TIMESTAMP WITH LOCAL TIME ZONE

    Essa é mais uma variante do tipo TIMESTAMP, porem diferente do TIMESTAMP WITH TIME ZONE na coluna não é armazenado as informações do timestamp do banco de dados e quando é solicitado em um select é retornado de acordo com com o timezone da sessão do usuário. Este tipo de dados é bastante útil quando há uma aplicação que trabalha com diferentes timezones e deve ser exibido as datas e horários de acordo com a configuração de cada usuário. Assim como os demais tipos de dados derivados de timestamp, este também permite determinar a quantidade de casas decimais que serão armazenados para os segundos, podendo ser de 0 a 9 e caso omitido o padrão será 6. Para criar uma coluna utilizando esse tipo é necessário utilizar a seguinte sintaxe:

    TIMESTAMP [(precisão de segundos)] WITH LOCAL TIME ZONE

INTERVALOS

    Intervalos são tipos de dados que armazenam períodos de tempo, temos a possibilidade de armazenar valores de 2 formas: intervalos de anos e meses ou intervalos de dias e segundos. É possível utilizar intervalos como validações na cláusula WHERE de uma consulta.

INTERVAL YEAR TO MONTH

    Armazena um período de tempo baseado em anos e meses, para utilizar temos a seguinte sintaxe:

    INTEVAL YEAR [(precisão dos anos)] TO MONTH

Caso a precisão não seja especificada o valor padrão é 2 e deve ser um número inteiro.

Abaixo alguns exemplos.

Intervalo de 300 meses, será armazenado como 25 anos, enquanto um intervalo de 306 meses é armazenado no como 25 anos e 6 meses.

    A precisão deve ser respeitada, caso contrário irá retornar erro, no exemplo abaixo, não foi determinada a precisão dos anos, que nesse caso assume o valor padrão que é 2, como o literal informado para o ano tem 3 dígitos, excede a precisão de 2 e pode ser resolvido incluindo a precisão após a palavra YEAR.

INTERVAL DAY TO SECOND

    Este intervalo armazena um período de tempo que pode ser em dias, horas, minutos ou segundos, esse tipo de dados é muito útil quanto é necessário armazenar uma diferença precisa entre duas datas e horários.

Sua sintaxe é a seguinte:

    INTEVAL DAY[(precisão do dia)] TO SECOND [(precisão da fração de segundos)]

    Note que neste tipo de dados, podemos informar ou não tanto a precisão do dia quanto a fração de segundos. A fração de segundos, assim como nos tipos TIMESTAMP, pode ser de 0 a 9 e tem como padrão o valor 2, já a precisão dos dias aceita os mesmos valores e também tem a precisão com valor 2 como padrão.

    Também é possível realizar operações entre os tipos DAY TO SECOND. Note no exemplo abaixo, que diminuindo 1 dia de 1440 minutos, o resultado é 0.

Fonte:

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Data-Types.html#GUID-A3C0D836-BADB-44E5-A5D4-265BA5968483

Tipos de Dados

    Dentro do banco de dados Oracle temos as tabelas e cada tabela é formada por colunas, que representam tipos de dados semelhantes, ou seja, em cada linha da tabela os tipos de dados de cada coluna será igual.

E como podemos descobrir qual o tipo de dados de cada coluna de uma tabela? Simples, basta executar o comando abaixo.

    describe nome_tabela;

ou ainda, podemos abreviar o comando da seguinte maneira

    desc nome_tabela;

    O resultados destes comando é uma listagem das colunas da tabela com seu respectivo tipo de dados.

    Agora que já sabemos como identificar o tipo de dados das colunas de uma tabela precisamos saber o que são cada um deles. A importância do tipo de dados se deve a otimização de um banco de dados, por exemplo, em um banco bem modelado, um campo que contém datas deverá ser do tipo DATE, podemos até armazenar em uma coluna que armazene uma cadeia de caracteres, porém, isso não é o ideal, a maneira que o banco de dados trabalha será prejudicada nesse caso, da mesma maneira um número, podemos armazena-lo em uma coluna que não seja do tipo NUMBER, mas nesse caso ele será tratado pelo banco de dados não como número e sim como um caractere alfanumérico.

Fonte:

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Data-Types.html#GUID-A3C0D836-BADB-44E5-A5D4-265BA5968483