25 de mar de 2013

Olá pessoal!

Tive uma semana conturbada e não deu para postar nada, mas comecei a escrever algo e terminei há pouco.

Bom...

Hoje vou falar sobre duas funções de linha simples: round e trunc.

Só relembrando as primeiras aulas de banco de dados da faculdade, funções de linha simples são as que operam com uma linha de conjunto de dados por vez. Se uma instrução SQL seleciona 20 linhas (registros), a função será executada 20 vezes, uma para cada linha.

As funções round e trunc podem ser usadas em campos do tipo numérico e data, mas minha abordagem será apenas sobre número.

A função ROUND
Essa função realiza uma operação de arredondamento de um valor com base em uma precisão especificada.

A sintaxe da função ROUND pode ser observada na Figura 1.

Figura 1 - Sintaxe da função ROUND. (Fonte: Oracle Corporation)

O grau de arredondamento ou precisão é opcional e caso exista, deve ser um valor decimal, e para o caso desse valor ser “n”, onde n > 0, o dígito significante ao arredondamento é encontrado (n+1) do lado direito do ponto decimal, ou seja:
SELECT ROUND (1.259, 2) valor FROM DUAL;

A precisão é igual a 2 nesse exemplo, portanto o dígito de arredondamento nesse caso será o de posição 2 após (lado direito) o ponto, nesse caso o valor “5”.

Para o caso do valor da precisão ser um número negativo, o dígito significante ao arredondamento é “n” colocado do lado esquerdo do ponto:
SELECT ROUND (136.479, -1) valor FROM DUAL;

Nesse caso a precisão é -1, então o arredondamento será feito no primeiro dígito após o ponto decimal, nesse caso o valor “6”.

Se o valor numérico do dígito significante for maior ou igual a 5, um arredondamento para cima ocorrerá, caso contrário será um arredondamento para baixo. Se não for informada a precisão de arredondamento para a função round, o grau padrão de arredondamento é zero, significando que a origem será arredondada para o número inteiro mais próximo (lado esquerdo do ponto decimal).

Vou dar uns exemplos para facilitar o entendimento:

SELECT ROUND (1652.479, 2) AS valor FROM DUAL
UNION ALL
SELECT ROUND (1652.479, 1) AS valor FROM DUAL
UNION ALL
SELECT ROUND (1652.479) AS valor FROM DUAL
UNION ALL
SELECT ROUND (1652.479, -1) AS valor FROM DUAL
UNION ALL
SELECT ROUND (1652.479, -2) AS valor FROM DUAL;

O resultado para as consultas do exemplo pode ser conferido na Figura 2.

Figura 2 - Resultado de consultas utilizando o comando round. (Fonte: autoria própria)

A função TRUNC
A função trunc realiza uma operação de truncamento em um valor numérico com base em uma precisão decimal especificada, que assim como a função round, é opcional. A diferença entre as funções round e trunc é que a segunda função “quebra” o resultado na precisão decimal especificada e não tenta arredondar para cima ou para baixo.

A sintaxe do comando trunc pode ser configurada na Figura 3.

Figura 3 - Sintaxe da função TRUNC. (Fonte: Oracle Corporation)

Como os conceitos entre as funções round e trunc são bem parecidos, vou partir para exemplos do comando TRUNC para facilitar o entendimento:

SELECT TRUNC (1652.479123, 3) AS valor FROM DUAL
UNION ALL
SELECT TRUNC (1652.479123, 2) AS valor FROM DUAL
UNION ALL
SELECT TRUNC (1652.479123, 1) AS valor FROM DUAL
UNION ALL
SELECT TRUNC (1652.479123) AS valor FROM DUAL
UNION ALL
SELECT TRUNC (1652.479123, -1) AS valor FROM DUAL
UNION ALL
SELECT TRUNC (1652.479123, -2) AS valor FROM DUAL
UNION ALL
SELECT TRUNC (1652.479123, -3) AS valor FROM DUAL;


Figura 4 - Resultado de consultas utilizando o comando trunc. (Fonte: autoria própria)

Esses comandos de linhas simples são bastante eficientes e muito útil quando se precisa trabalhar com precisões.

Espero ter conseguido passar o entendimento sobre as funções.

Grande abraço e até a próxima!

Referências:

WATSON, JOHN – Ramklass, Roopesh (2010) – OCA Oracle Database 11g – Fundamentos I SQL – Manual do Exame 1z0-051. Editora: ALTABOOKS.

ORACLE CORPORATION – ROUND (number). Disponível em: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm. Acessado em 24/03/2013.              

ORACLE CORPORATION – TRUNC (number). Disponível em: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions200.htm. Acessado em 24/03/2013.

Raphael Fernandes, segunda-feira, março 25, 2013

8 comentários

20 de mar de 2013


Olá pessoal,

Hoje vou falar sobre consultas hierárquicas no banco de dados Oracle. Esse tipo de estrutura é muito útil para várias situações desde criação de itens de um menu até uma simulação de informação.

Bom...

Uma consulta hierárquica relaciona registros com base em uma relação de parentesco. Para que seja possível realizar uma consulta de forma hierárquica de um conjunto de dados, é necessário que exista uma relação de lógica entre os registros (pai e filho).

Vou dar um exemplo mais prático: criação de itens de menu. Digamos que queiramos criar um menu da seguinte forma:

1.Item 1
--1.1.Item 1, parte 1
--1.2.Item 1, parte 2
----1.2.1.Item 1, parte 2, seção 1
----1.2.2.Item 1, parte 2, seção 2
2.Item 2
--2.1.Item 2, parte 1
--2.2.Item 2, parte 2

Vou criar uma tabela chamada MENU para ajudar no entendimento:

CREATE TABLE menu
(
   cod      NUMBER (4),
   codpai   NUMBER (4),
   campo    VARCHAR (30),
   CONSTRAINT PK_MENU PRIMARY KEY (COD),
   CONSTRAINT FK_MENU_CODPAI FOREIGN KEY (CODPAI) REFERENCES DIARIO.MENU (COD)
);

Agora vou popular a tabela com registros de mesmo valor do citado no exemplo literal acima:

INSERT INTO MENU VALUES (1, NULL, 'Item 1');
INSERT INTO MENU VALUES (2,1,'Item 1,parte 1');
INSERT INTO MENU VALUES (3,1,'Item 1,parte 2');
INSERT INTO MENU VALUES (4,3,'Item 1,parte 2, seção 1');
INSERT INTO MENU VALUES (5,3,'Item 1,parte 2, seção 2');
INSERT INTO MENU VALUES (6,NULL,'Item 2');
INSERT INTO MENU VALUES (7,6,'Item 2,parte 1');
INSERT INTO MENU VALUES (8,6,'Item 2,parte 2');
COMMIT;

Verificando de forma normal os registros executando a consulta abaixo temos o resultado da Figura 2:

SELECT cod, codpai, campo FROM MENU ORDER BY 1 ASC, 2 ASC;

 Figura 1 - Resultado da consulta do exemplo 1. (Fonte: autoria própria)

Agora vamos ver como realizar uma consulta hierárquica contra essa tabela. Para isso, vamos entender um pouco do conceito.

A estrutura utilizada para realizar consultas dessa natureza é: START WITH ... CONNECT BY, e sua sintaxe pode ser vista na Figura 2.
Figura 2 - Sintaxe para consultas hierárquicas. (Fonte: Oracle Corporation)

O comando START WITH:
Determina quais são os registros “raiz” que devem ser utilizados para iniciar a consulta. No nosso caso, serão os registros com “codpai” nulo.

O comando CONNECT BY PRIOR:
Este comando especifica a relação entre registros pai e filho. No nosso caso, “cod” deverá ter um correspondente em “codpai”. É importante ressaltar que se a igualdade for invertida, ou seja, “codpai” foi igual a “cod” o resultado será totalmente diferente, pois o Oracle considera o argumento da esquerda como pai (ou raiz) e o da direita da igualdade como filho.

A consulta hierárquica para atender a solução é a seguinte:

SELECT cod,
           codpai,
           LEVEL,
           LPAD (' ',(LEVEL - 1) * 3, '-') || campo AS "Descrição"
      FROM menu
START WITH codpai IS NULL
CONNECT BY PRIOR cod = codpai
ORDER BY cod ASC, codpai ASC, campo ASC;

A pseudo-coluna LEVEL sempre está disponível para consultas hierárquicas e serve para indicar o nível em que o registro se encontra em relação à raiz.

O resultado da consulta acima pode ser conferido na Figura 3.

Figura 3 - Resultado da consulta do exemplo 1. (Fonte: autoria própria)

Consultas hierárquicas podem ter muitas utilidades, e os comandos connect by e start with podem nos ajudar de várias outras formas.

Por exemplo, uma simulação de valores de 1 a 10:

SELECT LEVEL "Número" FROM DUAL CONNECT BY LEVEL <= 10 ORDER BY 1 ASC;

O resultado da consulta acima pode ser conferido na Figura 4.
Figura 4 - Simulação de contagem de 1 a 10. (Fonte: autoria própria)

Recentemente passei por uma situação e usei connect by e start with para resolver. A situação foi a seguinte, precisava saber qual o primeiro domingo do mês corrente.

Alguém se habilita com uma solução?

Bom...

A que usei foi a seguinte: simulei dados para os 7 primeiros dias do mês (e com certeza o primeiro domingo estará entre eles), e numa consulta mais externa verifiquei qual das datas correspondia ao primeiro dia da semana, no caso o domingo. A consulta foi a seguinte:

SELECT dia
  FROM (SELECT TRUNC (SYSDATE, 'MM') + b.simulado dia FROM DUAL a,
                ( SELECT LEVEL - 1 simulado FROM DUAL CONNECT BY LEVEL <= 7) b
             )
 WHERE TO_CHAR (dia, 'D') = '1';

O resultado para a consulta pode ser conferida na Figura 5.
Figura 5 – Consulta para saber o primeiro domingo do mês corrente. (Fonte: autoria própria)

Essa foi uma forma simples e prática para a solução do meu problema.

Por hoje é isso, pessoal!

Espero ter conseguido passar o entendimento de forma clara e objetiva.

Até a próxima!

Referências:
Oracle Corporation – Hierarchical Queries. Disponível em: http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries003.htm. Acessado: 18/03/2013.

Lusa, Diego Antonio - Uso do CONNECT BY no Oracle. Disponível em: http://www.devmedia.com.br/uso-do-connect-by-no-oracle/23647. Acessado:18/03/2013.

Raphael Fernandes, quarta-feira, março 20, 2013

3 comentários