Pipelined Table Functions – Funções para retornar “tabelas virtuais”
Neste artigo irei apresentar um recurso muito bom que existe no Oracle Database desde a versão 9i e chama-se Pipelined Table Function.
Este recurso permite criar funções que retornam dados como se fossem uma tabela virtual, podendo transformar os dados de retorno enquanto eles são produzidos, ou seja, é possível alterar os dados pesquisados em uma tabela, linha por linha, enquanto eles são processados, sem ter que esperar pelo retorno completo do “result set” (conjunto de dados que são retornados pela função).
Este recurso é ótimo para ETL (Extract, Transform, and Load), pois é rápido e consome menos memória que outros métodos que podem ser utilizados para o mesmo objetivo, como por exemplo, preencher um cursor e percorrê-lo para transformar e retornar dados.
Seguem abaixo 3 scripts que demonstram como criar e testar uma Pipelined Table Function. Os scripts utilizam a tabela EMPLOYEES do schema de exemplo HR.
Para iniciar o passo-a-passo dos itens abaixo, é necessário conectar-se previamente no Banco de Dados desejado, através do SQL Plus, com um usuário com privilégios administrativos (usuário contendo a role DBA ou o privilégio de sistema SYSDBA) ou com o usuário HR.
1- Criando a package HR.PKG_TYPES
A package HR.PKG_TYPES contém os tipos de dados que são criados para retornarem uma tabela virtual na função que será criada no próximo passo:
create or replace package HR.PKG_TYPES as
TYPE TABLEEMPTYPE IS TABLE OF EMPLOYEES%ROWTYPE;
TYPE ROWEMPTYPE IS RECORD(
EMPLOYEE_ID EMPLOYEES.EMPLOYEE_ID%TYPE,
FIRST_NAME EMPLOYEES.FIRST_NAME%TYPE,
LAST_NAME EMPLOYEES.LAST_NAME%TYPE,
EMAIL EMPLOYEES.EMAIL%TYPE,
PHONE EMPLOYEES.PHONE_NUMBER%TYPE,
HIRE_DATE EMPLOYEES.HIRE_DATE%TYPE,
JOB_ID EMPLOYEES.JOB_ID%TYPE,
SALARY EMPLOYEES.SALARY%TYPE,
COMMISSION_PCT EMPLOYEES.COMMISSION_PCT%TYPE,
MANAGER_ID EMPLOYEES.MANAGER_ID%TYPE,
DEPARTMENT_ID EMPLOYEES.DEPARTMENT_ID%TYPE
);
END;
/
Melhorias nos EXTERNAL TABLES do 10g
Jan 13, 2009 SQL
A partir do Oracle 9i foi criado o conceito de EXTERNAL TABLES, ou seja, você cria uma tabela baseado num arquivo texto no sistema operacional e pode fazer consultas SQL nessa tabela (ou seja, diretamente no arquivo texto como se fosse uma tabela) Read the rest of this entry »
Agora a partir do oracle 10g é possível também criar um arquivo texto baseado numa tabela do banco usando o novo driver de Data Pump existente.
Tags: External Table, Oracle 10g, SQL
Novas funcionalidades com CONNECT BY
Jan 12, 2009 SQL
A partir do Oracle 10g temos mais algumas funcionalidades no uso da cláusula CONNECT BY dentro dos comandos SELECT. Essas mudanças se aplicam a queries hiearquicas permitindo o retorno de não apenas PAIS, FILHOS mas também “ancestrais”. São 3 as novas cláusulas disponíveis com CONNECT BY.
Read the rest of this entry »
Tags: Connect By, Oracle 10g, SQL
Cláusula RETURNING com funções somatórias
Jan 9, 2009 SQL
Oracle 10g agora permite o uso de funções somatórias na cláusula RETURNING. O seguinte exemplo faz um UPDATE no salário de todos empregados e retorna a média salarial resultante para as linhas afetadas.
Read the rest of this entry »
Tags: Oracle 10g, SQL
Ignorando Acentos e Maiúsculas/Minúsculas no 10g
Jan 8, 2009 SQL
O Oracle 10g agora permite fazer consultas considerando ou não acentos e maiúsculas/minúsculas. Isso é feito pelo uso do parâmetro NLS_SORT. Vamos aos exemplos.
Read the rest of this entry »
Tags: Acentuação, Oracle 10g, SQL
Select que retorna os Números Primos
Oct 11, 2007 SQL
Esse post vai mais a título de curiosidade. Sabemos que os números primos são números naturais que podem ser divididos apenas por dois números naturais, o 1 (um) e ele mesmo. Pois a tempos que eu queria um SQL que retornasse esses números!
Read the rest of this entry »
Tags: Curiosidade, Matemática, SQL
Como usar Índices Virtuais – NOSEGMENT INDEXES
Sep 25, 2007 SQL
Essa é para quem faz tunning de SQL. Digamos que você quer verificar se a criação de um novo índice vai ser realmente usada pelo otimizador. Antes de criá-lo, você pode testar se ele resolverá seu problema atraves de INDICE VIRTUAL.
Read the rest of this entry »
Ver Plano de execução de um SQL rodando
Sep 25, 2007 SQL
Imagine que você rodou um SQL que está demorando muito. O Trace está desligado e você quer saber o que pode estar errado com o SQL. Com essa dica, você enxerga o plano de execução deste SQL.
Read the rest of this entry »
CASE WHEN versus DECODE
Sep 7, 2007 SQL
As vezes o CASE WHEN quebra o galho quando temos que usar condições mais complexas… Mas o que fazer quando não é possível usá-lo? (como nas versões anteriores ao 8i, e no PL*SQL anterior ao 9i). É muito simples!
Read the rest of this entry »
“IN” pode concatenar espaços em branco
Aug 24, 2007 SQL
Este caso é no mínimo curioso! É bem comum usarmos “IN” quando queremos que o banco considere várias opções para um determinado campo. Neste exemplo, vamos demonstrar que o ORACLE concatena espaços em branco em comparações com IN. Vamos aos exemplos:
Read the rest of this entry »
Tags: Curiosidade, Funções, SQL
