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 - Numéricos

    Seguindo conhecendo os tipos de dados suportados pelo Banco de Dados Oracle, agora vamos entender como funcionam os tipos de dados numéricos.

NUMBER

    O tipo de dados NUMBER pode armazenar 0, valores negativos até  1.0 x 10-130 e valores positivos até 1.0 x 10126 sem incluí-lo, caso seja igual ou maior retornará erro. 

    A sintaxe para criação de uma coluna do tipo NUMBER é NUMBER(p,e) onde p indica precisão e o e escala. Mas o que seriam precisão e escala para uma coluna NUMBER?

Precisão de uma coluna NUMBER indica quantas dígitos serão armazenados.

Já a escala determina a quantidade de casas decimais o número irá suportar incluindo esse valor, no caso de um valor positivo. No caso de um valor negativo, a precisão irá arredondar a quantidade de casas log antes da virgula.

    Também é possível criar uma coluna determinando apenas a precisão, nesse caso a sintaxe do tipo da coluna seria NUMBER(p). Seguem alguns exemplos de configurações utilizadas como NUMBER:

Dado informado	Definição da Coluna	Armazenado como
123.89	NUMBER	123.89
123.89	NUMBER(3)	124
123.89	NUMBER(3,2)	excede a precisão
123.89	NUMBER(4,2)	excede a precisão
123.89	NUMBER(5,2)	123.89
123.89	NUMBER(6,1)	123.9
123.89	NUMBER(6,-2)	100
.01234	NUMBER(4,5)	.01234
.00012	NUMBER(4,5)	.00012
.000127	NUMBER(4,5)	.00013
.0000012	NUMBER(2,7)	.0000012
.00000123	NUMBER(2,7)	.0000012
1.2e-4	NUMBER(2,5)	0.00012
1.2e-5	NUMBER(2,5)	0.00001

FLOAT

    O tipo de dados FLOAT é considerado um subtipo do tipo NUMBER, esse tipo também permite sua declaração com ou sem precisão da mesma maneira que o tipo NUMBER, já a escala não pode ser definida e é interpretada pelos dados. O tipo FLOAT é utilizado com frequência para o armazenamento de pontos flutuantes, mas o que são pontos flutuantes? Basicamente é um número real, que pode ter uma vírgula em qualquer posição dele ou simplesmente não ter uma vírgula, podendo ser representado por notação exponencial.

Segue um comparativo do armazenamento de dados em colunas do tipo NUMBER e FLOAT.

CREATE TABLE test (col1 NUMBER(5,2), col2 FLOAT(5));

INSERT INTO test VALUES (1.23, 1.23);
INSERT INTO test VALUES (7.89, 7.89);
INSERT INTO test VALUES (12.79, 12.79);
INSERT INTO test VALUES (123.45, 123.45);

SELECT * FROM test;

      COL1       COL2
---------- ----------
      1.23        1.2
      7.89        7.9
     12.79         13
    123.45        120

    A conversão de um número binário para precisão decimal se faz pela multiplicação de n por 0.30103, já para a conversão de um decimal para precisão binária a multiplicação é feita por 3.32193. O valor máximo armazenado é de 126 dígitos com precisão binária que é equivalente a 38 dígitos de precisão decimal.

BINARY_FLOAT

    O tipo de dados BINARY_FLOAT é um dado de ponto flutuante de 32 bits com precisão individual e que cada BINARY_FLOAT requer 4 bytes.

BINARY_DOUBLE

    É um tipo de dados de ponto flutuante de 64 bits com precisão de duas casas e que cada BINARY_DOUBLE requer 8 bytes.

    A diferença entre tipos de NUMBER e os BINARY é que colunas NUMBER tem precisão decimal enquanto os BINARY a precisão é binária. Os tipos binários suportam valores especiais infinitos e valores Not a Number (NaN).

	BINARY_FLOAT	BINARY_DOUBLE
Valor máximo positivo finito	3.40282E+38F	1.79769313486231E+308
Valor mínimo posiitivo finito	1.17549E-38F	2.22507485850720E-308

Fonte:

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

Tipos de Dados - Caracteres

    Seguindo com os tipos de dados vamos entender os tipos de dados de caracteres, que basicamente são CHAR, NCHAR, VARCHAR2 e NVARCHAR2.

VARCHAR2

    Destinado para armazenar cadeia de caracteres de forma variável, dever ser informado o tamanho do campo no momento da criação da coluna, por exemplo, VARCHAR2(n) onde o n indica a quantidade máxima de caracteres que poderá ser armazenada na coluna, esse valor tem um mínimo de 1 byte ou caractere e pode ter no máximo 32767 bytes ou caracteres no caso do parâmetro MAX_STRING_SIZE ter o valor igual a EXTENDED ou no máximo 4000 bytes no caso do parâmetro estar configurado com o valor igual a STANDARD.

Como o tamanho é variável, em uma coluna VARCHAR(10), por exemplo, podemos ter valores como abaixo:

'asd'             -> Tamanho ocupado 3

'asdf12'        -> Tamanho ocupado 6

'asdfg12345' -> Tamanho ocupado 10

NVARCHAR2

    Funciona da mesma maneira que o tipo VARCHAR2, contudo o tipo NVARCHAR2 permite definir a forma de codificação da coluna entre AL16UTF16 OU UTF8. Essa configuração também altera os tamanhos máximos suportados nesse tipo de coluna.

    Caso o parâmetro MAX_STRING_SIZE esteja com o valor STANDARD, a coluna irá suportar 2000 bytes para codificação AL16UTF16 e 4000 bytes para UTF8. Se o parâmetro estiver configurado para EXTENDED o tamanho suportado será de 16383 bytes para AL16URF16 e 32767 bytes para UTF8.

CHAR

    Armazena caracteres (alfanuméricos) com tamanho fixo no campo, ou seja, caso a coluna esteva definida com tamanho 10 e for inserido no campo o valor 'asd' os outros 7 espaços serão ocupados com espaços em branco ficando armazenado como 'asd       ' , sendo o mínimo 1 byte e o máximo 2000 bytes, na definição de uma coluna char devemos passar o tamanho do campo de acordo com a sintaxe char(n) onde n indica o tamanho em bytes. No exemplo citado acima em que coluna char(10), teríamos a coluna ocupada da seguinte maneira:

'asd       '      -> Tamanho ocupado 10

'asdfg12   '    -> Tamanho ocupado 10

'asdfg12345'  -> Tamanho ocupado 10

NCHAR

    Assim como o CHAR, armazena caracteres alfanuméricos  com tamanho fixo, contudo, pode variar de acordo com a codificação utilizada, assim como o NVARCHAR2. Quando não é informado na declaração o tamanho do campo ele assume o valor 1 como padrão. Os valores máximos são 1000 para codificação AL16UTF16 e 2000 caracteres para UTF8. Caso um valor CHAR tente ser armazenado em uma coluna NCHAR, o valor poderá ser aceito, pois o banco de dados fará uma conversão para o formato codificado na coluna NCHAR.

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

Utilizando ALIAS

    Já vimos como fazer uma consulta básica utilizando SQL no neste tópico, agora vamos aprofundar o conhecimento.

    Muitas vezes temos nomes de colunas que não são muito amigáveis para exibição. Por exemplo, podemos ter o nome de uma coluna em outro idioma do qual queremos exibir os resultados da consulta, nesse caso, podemos criar uma solução com a utilização de um alias. Um alias basicamente é um apelido para a coluna, que é válido para a exibição dos resultados da consulta em questão.

    Exemplo prático, utilizando a tabela EMPLOYEES do schema HR, precisamos retornar apenas 5 colunas, para isso basta escrever o nome das colunas que queremos exibir separadas por virgula ( , ).

O resultado é exibido com o nome das colunas em letra maiúsculas.

Para utilizar um alias podemos simplesmente incluir a palavra que queremos exibir após o nome da coluna e antes da virgula (,). Contudo, existem algumas regras.

Se for um nome composto ou que possua caracteres especiais devemos colocar entre aspas duplas ( " )

Se for necessário manter a formatação de maiúsculas e minúsculas também devemos utilizar as aspas duplas, caso o alias seja colocado sem aspas duplas, será sempre exibido em letras maiúsculas.

No exemplo acima, podemos ver vários exemplos de alias em uso. A sintaxe básica é:

coluna AS "alias"

A palavra reservada AS pode ser omitida, bem como as aspas duplas em alguns casos.

Os alias não podem ser utilizados na cláusula WHERE, porem, podemos utilizá-los na cláusula ORDER BY.

Ao tentar utilizar um alias no WHERE temos como retorno o erro: ORA-00904.

Abaixo exemplo utilizando o alias na ordenação.

Por que podemos utilizar um alias na ordenação mas não na filtragem?

    Isso ocorre pela maneira como o Oracle executa a consulta. Primeiro é realizada a busca na tabela pelas condições de filtragem, ou seja, os dados que estão na cláusula WHERE, após isso os dados são preparados para exibição, nesse ponto entra o alias para a coluna, e por fim a ordenação dos dados e nesse ponto o alias já existe para a coluna.