Oracle Magazine

Olá!

A Oracle Magazine, revista voltada para os produtos Oracle principalmente o banco de dados, você pode receber ela impressa em casa no Brasil e sem pagar nada, para isso basta fazer a inscrição clicando nesse link.
Apesar da revista ser voltada para desenvolvedores Oracle, as vezes ela traz ótimos artigos sobre Java e .NET.


Até mais,

Oracle - PIPELINED Table Function

Olá!


Este é um pequeno artigo sobre o uso de um recurso do banco de dados Oracle que poucas pessoas conhecem, o nome desse recurso é PIPELINED Table Function ou simplesmente PIPELINED Function.
As ferramentas utilizadas nas implementações foram o Oracle SQL Developer e o Oracle 10g Express Edition rodam em Windows Vista.

Smartphone VoIP Livros de Java Apartamentos

###############
1. INTRODUÇÃO

Uma PIPELINED Table Function é uma função capaz de devolver várias linhas de resultados e são chamadas na cláusula FROM de uma instrução SELECT como se fosse uma tabela.
Nada melhor para aprender um novo recurso do que um exemplo bem simples.

O exemplo abaixo implementa um PIPELINED function que devolve a quantidade de linhas informada no parametro qtd_num.

CREATE OR REPLACE PACKAGE pac_fun_PIPELINED AS

type numeros IS TABLE OF NUMBER;

FUNCTION fun_gera_numeros(qtd_num number) RETURN numeros PIPELINED;

END pac_fun_PIPELINED;

CREATE OR REPLACE PACKAGE BODY pac_fun_PIPELINED AS

FUNCTION fun_gera_numeros(qtd_num NUMBER) RETURN numeros PIPELINED IS
BEGIN
  FOR cont IN 1 .. qtd_num
  LOOP
    pipe ROW(cont * cont); -- Eleva ao quadrado
  END LOOP;

  RETURN;

END fun_gera_numeros;

END pac_fun_PIPELINED;

Para executar a function use a instrução SELECT abaixo:

SELECT * FROM TABLE(pac_fun_PIPELINED.fun_gera_numeros(9));

O resultado é exibido na figura abaixo.

Viu porque essa function pode ser chamada de "virtual table"?


Três itens nesse exemplo são caracteristicas de uma PIPELINED function:
1. A palavra-chave PIPELINED;
2. O comando PIPE ROW que joga o registro no result set;
3. E o comando RETURN; em "branco" apesar do tipo de retorno da function indicar numeros.

Geralmente os types e as functions são implementadas dentra de uma package, nada impede que você as crie fora. Só a titulo de curiosidade, quando uma package é solicitada, tudo o que foi implementada dentro dela sobe para a memória. Então, dependendo das suas necessidades não é interessante implementar uma package.

Apesar de ser permitido, no exemplo não foi indicado se o paramêtro da function é de entrada (IN) ou de saída (OUT).

A vantagem entre uma função PIPELINED e outra não PIPELINED é que a função PIPELINED processa os dados e joga os resultados imediatamente para o result set do SELECT, assim o chamador pode usar os dados mais rapidamente sem ter que esperar todo o processamento.
por outro lado a função que não é PIPELINED processa os dados e vai acumulando os resultados para depois devolver(usando a cláusula RETURN ou usando paramêtro de OUT) os resultados para chamador da função, só então ele poderá usar os dados.

###############
2. USANDO TIPOS DEFINIDOS

Agora será implementado um exemplo usando tipos definidos pelo programador.
Neste exemplo será criado uma function que calcula a tabuada de um número (num_tabuada).

create or replace
PACKAGE pac_fun_PIPELINED AS

type registro is record (
  indice number,
  multiplicador number,
  resultado number
);

type tabuada IS TABLE OF registro;

FUNCTION fun_tabuada(num_tabuada    number,
                     qtd_resultados number)
RETURN tabuada PIPELINED;

END pac_fun_PIPELINED;

create or replace
PACKAGE BODY pac_fun_PIPELINED AS

FUNCTION fun_tabuada(num_tabuada    number,
                     qtd_resultados number)
RETURN tabuada PIPELINED IS
aux registro;
BEGIN

  FOR cont IN 1 .. qtd_resultados
  LOOP
    aux.indice := cont;
    aux.multiplicador := num_tabuada;
    aux.resultado := cont * num_tabuada;
    pipe ROW(aux);
  END LOOP;

  RETURN;

END fun_tabuada;

END pac_fun_PIPELINED;

Para calcular a tabuada do 7, a function será executada com o comando abaixo:

SELECT * FROM TABLE(pac_fun_PIPELINED.fun_tabuada(7, 10));

O resultado é exibido na figura abaixo.


#############################
3. USANDO JUNTO COM TABELAS DE VERDADE

Para este exemplo será necessário criar uma tabela, dois tipo, uma função e uma package

CREATE TABLE "CAD_PEDIDO_TABUADA"
( "NUM_TABUADA" NUMBER NOT NULL ENABLE,
 "DATA_PEDIDO" DATE NOT NULL ENABLE,
 "QTD_LINHAS" NUMBER,
 "CODIGO" NUMBER,
  CONSTRAINT "PK_CAD_PEDIDO_TABUADA" PRIMARY KEY ("CODIGO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"  ENABLE
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;

Inserindo dados na tabela.

INSERT INTO "CAD_PEDIDO_TABUADA" (NUM_TABUADA, DATA_PEDIDO, QTD_LINHAS, CODIGO) VALUES ('7', TO_DATE('05/05/08', 'DD/MM/RR'), '5', '2');
INSERT INTO "CAD_PEDIDO_TABUADA" (NUM_TABUADA, DATA_PEDIDO, QTD_LINHAS, CODIGO) VALUES ('2', TO_DATE('05/05/08', 'DD/MM/RR'), '11', '1');

Abaixo a package com tudo o que foi criado até agora para os exemplos.

CREATE OR REPLACE PACKAGE pac_fun_PIPELINED AS

-- Exemplo 1
type numeros IS TABLE OF NUMBER;

-- Exemplo 2
type registro IS record(indice NUMBER,   multiplicador NUMBER,   resultado NUMBER);
type tabuada IS TABLE OF registro;

-- Exemplo 3
type pedido_tabuada IS TABLE OF cad_pedido_tabuada % rowtype;
TYPE tab_refcursor IS REF CURSOR RETURN cad_pedido_tabuada%ROWTYPE;

----------------------------------------------------------  
-- Exemplo 1
FUNCTION fun_gera_numeros(qtd_num IN NUMBER) RETURN numeros PIPELINED;

-- Exemplo 2
FUNCTION fun_tabuada(num_tabuada NUMBER,   qtd_resultados NUMBER) RETURN tabuada PIPELINED;

-- Exemplo 3
FUNCTION fun_cad_tabuada(cursor_tabuada in tab_refcursor) RETURN tabuada PIPELINED;

END pac_fun_PIPELINED;

create or replace
PACKAGE BODY pac_fun_PIPELINED AS

FUNCTION fun_gera_numeros(qtd_num in NUMBER) RETURN numeros PIPELINED IS
BEGIN
  FOR cont IN 1 .. qtd_num
  LOOP
    pipe ROW(cont * cont); -- Eleva ao quadrado
  END LOOP;

  RETURN;

END fun_gera_numeros;

FUNCTION fun_tabuada(num_tabuada    number,
                     qtd_resultados number)
RETURN tabuada PIPELINED IS
aux registro;
BEGIN

  FOR cont IN 1 .. qtd_resultados
  LOOP
    aux.indice := cont;
    aux.multiplicador := num_tabuada;
    aux.resultado := cont * num_tabuada;
    pipe ROW(aux);
  END LOOP;

  RETURN;

END fun_tabuada;

FUNCTION fun_cad_tabuada(cursor_tabuada in tab_refcursor) RETURN tabuada PIPELINED IS
  temp cad_pedido_tabuada%rowtype;
  aux registro;
  erro varchar2(500) := 'ERRO: ';
BEGIN

  loop
      fetch cursor_tabuada into temp;
      exit when cursor_tabuada%notfound;
     
        -- Calcula a tabuada até a quantidade de linhas cadastradas.
        for contador IN 1..temp.qtd_linhas loop
          aux.indice := contador;
          aux.multiplicador := temp.NUM_TABUADA;
          aux.resultado := contador * temp.NUM_TABUADA;

          PIPE ROW (aux);
        end loop;
                      
  end loop;

EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(erro || sqlerrm);

END fun_cad_tabuada;

END pac_fun_PIPELINED;

Para executar a function use a instrução abaixo:

SELECT indice, multiplicador, resultado
FROM TABLE(pac_fun_PIPELINED.fun_cad_tabuada(
cursor(select NUM_TABUADA,DATA_PEDIDO,DATA_PROCESS,QTD_LINHAS,CODIGO
       from CAD_PEDIDO_TABUADA
       where codigo = 1)
))

O resultado é exibido na figura abaixo.

Não foi possível percorrer todos os registros da tabela cad_pedido_tabuada retirando a claúsula WHERE, o Oracle sempre processa o primeiro registro somente. Não descobri porque é assim e o motivo dessa limitação.
Não foi possível dar commit dentro da function, também não descobri por que razão a Oracle limitou o uso desse recurso.

###############
4. PARALLEL PIPELINED FUNCTIONS

As funções PIPELINED podem se tornar ainda mais poderosas, pois são paralelizáveis, com isso o processamento dos dados ficam mais rápidos.
Veja nesse link como funciona as PIPELINED and parallel functions.
Para utilizar os recursos de paralelização é preciso seguir algumas regras.
a) A função deve ser assinada com a palavra-chave PARALLEL_ENABLE;
b) A função tem de ser alimentada com um ref cursor.
Esse tópico é apenas para divulgação, até porque eu não possuo conhecimento sobre esse recurso do Oracle.
Para saber mais recomendo acessar o link: Parallel Table Functions.


###############
5. LINKS PARA SABER MAIS

http://www.codeguru.com/cpp/data/mfc_database/oracle/article.php/c4285/
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10800/dcitblfns.htm

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28425/pipe_paral_tbl.htm
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10800/dcitblfnxemp.htm

Até mais,

Carro novo
Blue-ray
Monitor
Notebooks
Playstation 3
Celular
Música

Conheçe a função PIPELINED?

Olá!

Você já ouviu em pipelined functions? Não?!
Em se tratando de Oracle as funções pipelined permitem obter facilmente dados que não sejam oriundos de tabelas. Se você tem um cálculo muito complexo já implementado em procedures e não quer ter o trabalho de re-escrever em Java você pode criar um function pipelined e usar um simples select * from table(fun_calc_complexo(1)) para obter os valores necessários para sua aplicação como se fosse uma tabela.
Isso ajuda muito quando o retorno esperado de uma function ou precedure é um type of table/record, por que é muito dificil obter isso diretamente do paramêtro de out da procedure com Java.

Links em inglês para estudos:

1. Using PIPELINED in PL/SQL
2. Oracle Pipelined Table Functions
3. Oracle Tips by Burleson
   

Em breve estarei postando aqui um pequeno tutorial em português sobre esse tema.

Até mais,

Queries hierarquicas no Oracle

Olá

O Oracle tem uma função muito interessante chamada connect by.
Com essa função você pode criar queries hierarquicas, muito útil para montar menus, principalmente os menus do tipo árvore (Tree view).

Sintaxe:

START WITH  CONNECT BY [NOCYCLE] 

ou

CONNECT BY [NOCYCLE]  START WITH 

Exemplo:
Eu tenho uma tabela com a seguinte estrutura:

create table TAB_TREE_VIEW(
COD_ITEM_TREE_VIEW       NUMBER not null,
COD_ITEM_PAI_TREE_VIEW   NUMBER,
DESCRICAO                VARCHAR2(20),
URL                      VARCHAR2(100),
DATA_INCLUSAO            DATE not null);

Nessa tabela existe um auto-relacionamento, para adicionar um registro o valor da coluna COD_ITEM_PAI_TREE_VIEW deve existir na coluna COD_ITEM_TREE_VIEW.

Abaixo os constraints da tabela, observe a foreign key:

alter table TAB_TREE_VIEW
add constraint PK_TAB_TREE_VIEW primary key (COD_ITEM_TREE_VIEW)
using index
...
alter table TAB_TREE_VIEW
add constraint FK_TAB_TREE_VIEW foreign key (COD_ITEM_TREE_VIEW)
references TAB_TREE_VIEW (COD_ITEM_TREE_VIEW) on delete cascade;

Depois de inserir alguns dados para testes, eu criei a seguinte consulta:

select cod_item_tree_view,
cod_item_pai_tree_view,
level,
descricao,
url,
data_inclusao
from   tab_tree_view
start with cod_item_pai_tree_view is null
connect by prior cod_item_tree_view = cod_item_pai_tree_view
order by level

E ela me retornou:

COD_ITEM_TREE_VIEW
COD_ITEM_PAI_TREE_VIEW
LEVEL
DESCRICAO
URL
DATA_INCLUSAO
0

null
1
SITES FAVORITOS
http://www.furutani.eti.br
21-Feb-08
13

null
1
IMPORTANTES
http://www.furutani.eti.br
21-Feb-08
1
0
2
BUSCADORES
http://www.furutani.eti.br
21-Feb-08
5
0
2
PORTAIS
http://www.furutani.eti.br
21-Feb-08
15
13
2
GLOBO
http://www.globo.com
21-Feb-08
14
13
2
GOOGLE
http://www.google.com.br
21-Feb-08
9
0
2
BLOGS
http://www.furutani.eti.br/blog
21-Feb-08
2
1
3
GOOGLE
http://www.google.com.br
21-Feb-08
7
5
3
TERRA
http://www.terra.com.br
21-Feb-08
11
9
3
TESTE
http://www.TESTE.COM
21-Feb-08
12
9
3
BLOGGER
http://www.blogger.com
21-Feb-08
10
9
3
MEU
http://www.robertofurutani.com
21-Feb-08
8
5
3
GLOBO
http://www.globo.com
21-Feb-08
6
5
3
UOL
http://www.uol.com.br
21-Feb-08
3
1
3
CADE
http://www.CADE.com.br
21-Feb-08
4
1
3
MSN
http://www.MSN.com.br
21-Feb-08

O que tem de diferente nessa query?

1. A coluna level - Isso é uma pseudo-coluna que indica qual o nível de hierarquia do registro;
2. A condição start with cod_item_pai_tree_view is null - Especifica a condição de inicio da hierarquia, nesse caso a hierarquica inicia-se com os registros onde cod_item_pai_tree_view é null;
3. A condição connect by prior cod_item_tree_view = cod_item_pai_tree_view - Especifica a condição de ligação entre pais e filhos. Nesse caso o cod_item_pai_tree_view indica de quem o registro é filho.

Abaixo descrevo as pseudo-colunas e operadores relacionados com connect by.


LEVEL - Retorna 1 se é raiz, 2 se é filho da raiz, 3 se é filho o filho da raiz, e assim por diante;
CONNECT_BY_ISLEAF - Retorna 1 se é a ponta da hierarquia, 0 caso contrário;
CONNECT_BY_ISCYCLE - Retorna 1 se o registro tem um filho que também é seu pai.
ORDER SIBLINGS BY - Ordena os filhos de um mesmo pai;
CONNECT_BY_ROOT - Retorna o valor da coluna do pai da linha;
SYS_CONNECT_BY_PATH - Retorna o caminho para a linha atual com o separador informado.

Mais informações acesse: Hierarchical Queries
http://www.psoug.org/reference/connectby.html

Até mais

Cálculo de tempo com Oracle PL/SQL

Olá

Vou postar um exemplo de código em PL/SQL para somar e subtrair segundos, minutos e horas de um campo date.
Espero que seja útil.

declare
horas_dia integer := 24;
minutos_hora integer := 60;
segundos_minuto integer := 60;

total_horas_dia integer;
total_minutos_dia integer;
total_segundos_dia integer;
atual date;
begin

total_horas_dia := horas_dia;
total_minutos_dia := minutos_hora * horas_dia;
total_segundos_dia := segundos_minuto * minutos_hora * horas_dia;

atual := sysdate;


dbms_output.put_line('Data atual...: ' || to_char(atual, 'dd/mon/yyyy hh24:mi:ss'));

-- Somando 15 horas a data corrente
dbms_output.put_line('+ 15 horas...: ' || to_char(atual + (15 / total_horas_dia), 'dd/mon/yyyy hh24:mi:ss'));

-- Subtraindo 15 horas a data corrente
dbms_output.put_line('- 15 horas...: ' || to_char(atual - (15 / total_horas_dia), 'dd/mon/yyyy hh24:mi:ss'));


-- Somando 15 minutos a data corrente
dbms_output.put_line('+ 30 minutos.: ' || to_char(atual + (15 / total_minutos_dia), 'dd/mon/yyyy hh24:mi:ss'));

-- Subtraindo 15 minutos a data corrente
dbms_output.put_line('- 30 minutos.: ' || to_char(atual - (15 / total_minutos_dia), 'dd/mon/yyyy hh24:mi:ss'));


-- Somando 20 segundos a data corrente
dbms_output.put_line('+ 20 segundos: ' || to_char(atual + (20 / total_segundos_dia), 'dd/mon/yyyy hh24:mi:ss'));

-- Subtraindo 20 segundos a data corrente
dbms_output.put_line('- 20 segundos: ' || to_char(atual - (20 / total_segundos_dia), 'dd/mon/yyyy hh24:mi:ss'));


A saída será parecida com essa:

Data atual...: 11/feb/2008 11:23:00
+ 15 horas...: 12/feb/2008 02:23:00
- 15 horas...: 10/feb/2008 20:23:00
+ 30 minutos.: 11/feb/2008 11:38:00
- 30 minutos.: 11/feb/2008 11:08:00
+ 20 segundos: 11/feb/2008 11:23:20
- 20 segundos: 11/feb/2008 11:22:40

Até mais