Fazendo Rateio através de Funções Analíticas
Apr 11, 2012 SQL
Neste post, vamos mostrar como utilizar funções analíticas para fazer o rateio de um valor em diversas linhas de uma tabela.
Primeiro, vamos criar a tabela de teste:
CREATE TABLE glufke_teste (nro_nota NUMBER, item NUMBER, valor NUMBER); INSERT INTO glufke_teste VALUES ( 50, 1, 1.45); INSERT INTO glufke_teste VALUES ( 50, 2, 3.91); INSERT INTO glufke_teste VALUES ( 50, 3, 5.04);
SQL> SELECT * FROM glufke_teste;
NRO_NOTA ITEM VALOR
---------- ---------- ----------
50 1 1,45
50 2 3,91
50 3 5,04
SQL>
Nosso objetivo é ratear um valor proporcionalmente nessas linhas. Como exemplo prático, vamos distribuir o valor R$ 49,30!
Read the rest of this entry »
Tags: Analíticas, Funções, Matemática, Rateio, SQL, Utilidade
Buscar por INSERT/UPDATE dentro da ALL_SOURCE
Feb 15, 2012 SQL
É comum termos que procurar qual package ou qual procedure de banco que faz INSERT dentro de uma determinada tabela.
Nestes casos, podemos usar a ALL_SOURCE, que contém os códigos de todos programas de banco. Normalmente um select com LIKE resolve o problema:
SELECT * FROM all_source WHERE UPPER(text) LIKE '%INSERT%TABELA%'
O problema é que nem sempre o código está no padrão, por exemplo, o nome da tabela pode estar no lado ou na próxima linha!
INSERT INTO tabelax... INSERT INTO tabelax...
O mesmo vale pra UPDATES! Ou seja, se o comando INSERT estiver na linha anterior, essa consulta simples não vai retornar o que procuramos!
UPDATE tabelax SET... UPDATE tabelax SET...
Tags: all_source, fonte, source, SQL
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 »
