CREATE FUNCTION [dbo].[FN_DIAUTIL](@DataMes DateTime)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @FORMATO VARCHAR(3)
SELECT @FORMATO = DATEFORMAT FROM sys.syslanguages S WHERE S.NAME = @@LANGUAGE
DECLARE @PASCOA DATETIME
SELECT @PASCOA = CASE DATEPART(WeekDay,CAST(DD.DIADOURADO AS DATETIME))
WHEN 1 THEN DATEPART(WeekDay,CAST(DD.DIADOURADO AS DATETIME))
ELSE DATEADD(day, (8-DATEPART(WeekDay,CAST(DD.DIADOURADO AS DATETIME))) ,DD.DIADOURADO)
END
FROM (SELECT
CASE
WHEN (YEAR(@DataMes) % 19)+1 = 1 AND @FORMATO = 'mdy' THEN '04.14.'
WHEN (YEAR(@DataMes) % 19)+1 = 2 AND @FORMATO = 'mdy' THEN '04.03.'
WHEN (YEAR(@DataMes) % 19)+1 = 3 AND @FORMATO = 'mdy' THEN '03.23.'
WHEN (YEAR(@DataMes) % 19)+1 = 4 AND @FORMATO = 'mdy' THEN '04.11.'
WHEN (YEAR(@DataMes) % 19)+1 = 5 AND @FORMATO = 'mdy' THEN '03.31.'
WHEN (YEAR(@DataMes) % 19)+1 = 6 AND @FORMATO = 'mdy' THEN '04.18.'
WHEN (YEAR(@DataMes) % 19)+1 = 7 AND @FORMATO = 'mdy' THEN '04.08.'
WHEN (YEAR(@DataMes) % 19)+1 = 8 AND @FORMATO = 'mdy' THEN '03.28.'
WHEN (YEAR(@DataMes) % 19)+1 = 9 AND @FORMATO = 'mdy' THEN '04.16.'
WHEN (YEAR(@DataMes) % 19)+1 =10 AND @FORMATO = 'mdy' THEN '04.05.'
WHEN (YEAR(@DataMes) % 19)+1 =11 AND @FORMATO = 'mdy' THEN '03.25.'
WHEN (YEAR(@DataMes) % 19)+1 =12 AND @FORMATO = 'mdy' THEN '04.13.'
WHEN (YEAR(@DataMes) % 19)+1 =13 AND @FORMATO = 'mdy' THEN '04.02.'
WHEN (YEAR(@DataMes) % 19)+1 =14 AND @FORMATO = 'mdy' THEN '03.22.'
WHEN (YEAR(@DataMes) % 19)+1 =15 AND @FORMATO = 'mdy' THEN '04.10.'
WHEN (YEAR(@DataMes) % 19)+1 =16 AND @FORMATO = 'mdy' THEN '03.30.'
WHEN (YEAR(@DataMes) % 19)+1 =17 AND @FORMATO = 'mdy' THEN '04.17.'
WHEN (YEAR(@DataMes) % 19)+1 =18 AND @FORMATO = 'mdy' THEN '04.07.'
WHEN (YEAR(@DataMes) % 19)+1 =19 AND @FORMATO = 'mdy' THEN '03.27.'
WHEN (YEAR(@DataMes) % 19)+1 = 1 AND @FORMATO = 'dmy' THEN '14.04.'
WHEN (YEAR(@DataMes) % 19)+1 = 2 AND @FORMATO = 'dmy' THEN '03.04.'
WHEN (YEAR(@DataMes) % 19)+1 = 3 AND @FORMATO = 'dmy' THEN '23.03.'
WHEN (YEAR(@DataMes) % 19)+1 = 4 AND @FORMATO = 'dmy' THEN '11.04.'
WHEN (YEAR(@DataMes) % 19)+1 = 5 AND @FORMATO = 'dmy' THEN '31.03.'
WHEN (YEAR(@DataMes) % 19)+1 = 6 AND @FORMATO = 'dmy' THEN '18.04.'
WHEN (YEAR(@DataMes) % 19)+1 = 7 AND @FORMATO = 'dmy' THEN '08.04.'
WHEN (YEAR(@DataMes) % 19)+1 = 8 AND @FORMATO = 'dmy' THEN '28.03.'
WHEN (YEAR(@DataMes) % 19)+1 = 9 AND @FORMATO = 'dmy' THEN '16.04.'
WHEN (YEAR(@DataMes) % 19)+1 =10 AND @FORMATO = 'dmy' THEN '05.04.'
WHEN (YEAR(@DataMes) % 19)+1 =11 AND @FORMATO = 'dmy' THEN '25.03.'
WHEN (YEAR(@DataMes) % 19)+1 =12 AND @FORMATO = 'dmy' THEN '13.04.'
WHEN (YEAR(@DataMes) % 19)+1 =13 AND @FORMATO = 'dmy' THEN '02.04.'
WHEN (YEAR(@DataMes) % 19)+1 =14 AND @FORMATO = 'dmy' THEN '22.03.'
WHEN (YEAR(@DataMes) % 19)+1 =15 AND @FORMATO = 'dmy' THEN '10.04.'
WHEN (YEAR(@DataMes) % 19)+1 =16 AND @FORMATO = 'dmy' THEN '30.03.'
WHEN (YEAR(@DataMes) % 19)+1 =17 AND @FORMATO = 'dmy' THEN '17.04.'
WHEN (YEAR(@DataMes) % 19)+1 =18 AND @FORMATO = 'dmy' THEN '07.04.'
WHEN (YEAR(@DataMes) % 19)+1 =19 AND @FORMATO = 'dmy' THEN '27.03.'
END + CAST(YEAR(@DataMes) AS VARCHAR(4)) AS DIADOURADO
) AS DD
DECLARE @TB_FERIADOS TABLE (DataMes DateTime, Descricao VARCHAR(100))
IF @FORMATO = 'dmy'
BEGIN
INSERT INTO @TB_FERIADOS SELECT '01-01-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Confraternização Universal'
INSERT INTO @TB_FERIADOS SELECT '21-04-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Tiradentes'
INSERT INTO @TB_FERIADOS SELECT '01-05-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Dia do Trabalho'
INSERT INTO @TB_FERIADOS SELECT '07-09-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Independencia do Brasil'
INSERT INTO @TB_FERIADOS SELECT '12-10-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Nossa Senhora Aparecida'
INSERT INTO @TB_FERIADOS SELECT '02-11-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Finados'
INSERT INTO @TB_FERIADOS SELECT '15-11-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Proclamação da República'
INSERT INTO @TB_FERIADOS SELECT '25-12-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Natal'
END
IF @FORMATO = 'mdy'
BEGIN
INSERT INTO @TB_FERIADOS SELECT '01-01-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Confraternização Universal'
INSERT INTO @TB_FERIADOS SELECT '04-21-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Tiradentes'
INSERT INTO @TB_FERIADOS SELECT '05-01-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Dia do Trabalho'
INSERT INTO @TB_FERIADOS SELECT '09-07-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Independencia do Brasil'
INSERT INTO @TB_FERIADOS SELECT '10-12-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Nossa Senhora Aparecida'
INSERT INTO @TB_FERIADOS SELECT '11-02-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Finados'
INSERT INTO @TB_FERIADOS SELECT '11-15-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Proclamação da República'
INSERT INTO @TB_FERIADOS SELECT '12-25-'+CAST(YEAR(@DataMes) AS VARCHAR(4)) ,'Natal'
END
INSERT INTO @TB_FERIADOS SELECT @Pascoa-47 ,'Carnaval'
INSERT INTO @TB_FERIADOS SELECT @Pascoa-2 ,'Sexta Feira Santa'
INSERT INTO @TB_FERIADOS SELECT @Pascoa+60 ,'Corpus Christi'
DECLARE @NOMEDIA VARCHAR(13)
SET @NOMEDIA = CASE DatePart(WeekDay, @DataMes)
WHEN 1 THEN 'Domingo'
WHEN 2 THEN 'Segunda-Feira'
WHEN 3 THEN 'Terça-Feira'
WHEN 4 THEN 'Quarta-Feira'
WHEN 5 THEN 'Quinta-Feira'
WHEN 6 THEN 'Sexta-Feira'
WHEN 7 THEN 'Sábado'
END
DECLARE @RETORNO VARCHAR(100)
IF (DatePart(WeekDay, @DataMes) BETWEEN 2 AND 6) AND @DataMes NOT IN (SELECT DataMes FROM @TB_FERIADOS)
SET @RETORNO = 'S'
ELSE
SET @RETORNO = 'N'
SET @RETORNO = @RETORNO + ' - ' + @NOMEDIA
IF @DataMes IN (SELECT DataMes FROM @TB_FERIADOS)
SET @RETORNO = @RETORNO + ' - ' +(SELECT Descricao FROM @TB_FERIADOS WHERE DataMes = @DataMes)
RETURN @RETORNO
END
SELECT DBO.FN_DIAUTIL('11.02.2015') AS DIA_UTIL
Mostrando postagens com marcador Database. Mostrar todas as postagens
Mostrando postagens com marcador Database. Mostrar todas as postagens
sábado, 16 de janeiro de 2016
Funcao Dia Util SQL Server
Função que recebe uma data como parâmetro e apresenta se o dia é útil, considerando feriados nacionais brasileiros.
Funcao Valida CPF ou CNPJ
CREATE FUNCTION [dbo].[Valida_CPFCNPJ](@TEXTO varchar(20)) RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @RESULTADO VARCHAR(20)
DECLARE @CPF_CNPJ VARCHAR(20)
WHILE PATINDEX('%[^0-9]%', @TEXTO) > 0
BEGIN
SET @TEXTO = STUFF(@TEXTO, PATINDEX('%[^0-9]%', @TEXTO), 1, '')
END
SET @CPF_CNPJ = @TEXTO
IF LEN(@CPF_CNPJ) NOT IN (11, 14)
BEGIN
SET @RESULTADO = 'Inválido'
RETURN @RESULTADO
END
DECLARE @DIGITO1 INT, @DIGITO2 INT, @VALOR1 INT, @VALOR2 INT
DECLARE @I INT, @J INT, @TOTAL_TMP INT, @COEFICIENTE_TMP INT
, @DIGITO_TMP INT, @VALOR_TMP INT
SET @DIGITO1 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ) - 1, 1)
SET @DIGITO2 = SUBSTRING(@CPF_CNPJ, LEN(@CPF_CNPJ), 1)
SET @J = 1
WHILE @J <= 2
BEGIN
SELECT @TOTAL_TMP = 0, @COEFICIENTE_TMP = 2
SET @I = ((LEN(@CPF_CNPJ) - 3) + @J)
WHILE @I >= 0
BEGIN
SELECT
@DIGITO_TMP = SUBSTRING(@CPF_CNPJ, @I, 1),
@TOTAL_TMP = @TOTAL_TMP + (@DIGITO_TMP * @COEFICIENTE_TMP),
@COEFICIENTE_TMP = @COEFICIENTE_TMP + 1
IF (@COEFICIENTE_TMP > 9) AND LEN(@CPF_CNPJ) = 14
SET @COEFICIENTE_TMP = 2
SET @I = @I - 1
END
SET @VALOR_TMP = 11 - (@TOTAL_TMP % 11)
IF (@VALOR_TMP >= 10)
SET @VALOR_TMP = 0
IF @J = 1
SET @VALOR1 = @VALOR_TMP
ELSE
SET @VALOR2 = @VALOR_TMP
SET @J = @J + 1
END
SELECT @RESULTADO =
CASE WHEN @VALOR1 = @DIGITO1 AND @VALOR2 = @DIGITO2
THEN 'Válido'
ELSE 'Inválido'
END
RETURN @RESULTADO
END
Dependências em SQL Server
DECLARE @NOME_TABELA VARCHAR(100)
SET @NOME_TABELA = 'TB_TABELA'
-- Retorna todos os objetos que dependem da Tabela
EXEC sp_MSdependencies @NOME_TABELA, NULL, 1315327
-- Retorna todos os objetos que a Tabela depende
EXEC sp_MSdependencies @NOME_TABELA, null, 1053183
quarta-feira, 16 de outubro de 2013
Oracle com XML? Sim veja como...
Fazendo umas pesquisas necessitei usar XML com Oracle, para compartilhar com os amigos segue a minha brincadeira, que coloco as duas formas mais comuns com elementos e com atributos:
Resultado da brincadeira:
Espero que isso ajude alguém, abraços
SELECT XMLElement("Tempo"
,XMLAttributes(to_char(current_date,'dd/mm/yyyy') AS "Data",
to_char(current_date,'hh24:mi:ss') AS "Hora")
, XMLElement("Dia",to_char(current_date,'dd'))
, XMLElement("Mes",to_char(current_date,'mm'))
, XMLElement("Ano",to_char(current_date,'yyyy'))
, XMLElement("Horas",to_char(current_date,'hh24'))
, XMLElement("Minutos",to_char(current_date,'mi'))
, XMLElement("Segundos",to_char(current_date,'ss'))
) AS "XML_RESULT" FROM DUAL
Resultado da brincadeira:
<Tempo Data="16/10/2013" Hora="19:28:53">
<Dia>16</Dia>
<Mes>10</Mes>
<Ano>2013</Ano>
<Horas>19</Horas>
<Minutos>28</Minutos>
<Segundos>53</Segundos>
</Tempo>
Espero que isso ajude alguém, abraços
sábado, 27 de julho de 2013
Brincando com os operadores de comparação ( ANY | SOME e ALL )
Vou apresentar desta vez operadores de comparação que muitos desconhecem, são eles: ( ANY |SOME e ALL )
DECLARE @TABELA TABLE(ID INT)
INSERT INTO @TABELA SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
--ANY retorna verdadeiro comparando por QUALQUER valor de um Conjunto
IF 2 < ANY(SELECT ID FROM @TABELA)
BEGIN
PRINT '2 É MENOR QUE QUALQUER VALOR DA TABELA'
END
--SOME retorna verdadeiro comparando por ALGUM valor de um Conjunto
IF 2 < SOME(SELECT ID FROM @TABELA)
BEGIN
PRINT '2 É MENOR QUE ALGUM VALOR DA TABELA'
END
--ALL retorna verdadeiro comparando por TODOS valores de um Conjunto
IF 0 < ALL(SELECT ID FROM @TABELA)
BEGIN
PRINT '0 É MENOR QUE TODOS OS VALORES DA TABELA'
END
--CURIOSIDADES: EQUIVALENCIA ANY E IN
--Trazer dados de @TABELA que seja IGUAL a QUALQUER valor do Conjunto
SELECT *
FROM @TABELA
WHERE ID = ANY(SELECT 1 UNION SELECT 2 UNION SELECT 5)
--Trazer dados de @TABELA que seja valores CONTIDOS num Conjunto
SELECT *
FROM @TABELA
WHERE ID IN(SELECT 1 UNION SELECT 2 UNION SELECT 5)
/*
Resultado em ambos os casos
ID
-----------
1
2
*/
--CURIOSIDADES: EQUIVALENCIA ALL e NOT IN
--Trazer dados de @TABELA que seja diferente de TODOS valores do Conjunto
SELECT *
FROM @TABELA
WHERE ID <> ALL(SELECT 1 UNION SELECT 2 UNION SELECT 5)
--Trazer dados de @TABELA que seja valores NAO CONTIDOS em um Conjunto
SELECT *
FROM @TABELA
WHERE ID NOT IN (SELECT 1 UNION SELECT 2 UNION SELECT 5)
/*
Resultado em ambos os casos
ID
-----------
3
4
*/
domingo, 26 de maio de 2013
Cursor em SQL-Server (Números Primos)
Lembrando que um cursor deve ser usado somente quando não é possível resolver o que se necessita com apenas SELECT,
Para o nosso exemplo de Cursor, utilizo números que estão em uma "Tabela" e quero saber quais deles são primos.
Resultado:
DECLARE @ID INT
DECLARE @DESCRICAO VARCHAR(100)
DECLARE @DIVISOR INT
DECLARE @PRIMO BIT
--Declaração de um Cursor.
DECLARE CUR CURSOR FOR SELECT 1 ID,'Um' DESCRICAO UNION ALL
SELECT 2, 'Dois' UNION ALL
SELECT 3, 'Tres' UNION ALL
SELECT 4, 'Quatro' UNION ALL
SELECT 5, 'Cinco' UNION ALL
SELECT 6, 'Seis' UNION ALL
SELECT 7, 'Sete'
--Abrindo um Cursor para uso.
OPEN CUR
--Leitura da tupla do cursor dentro das variáveis
FETCH NEXT FROM CUR INTO @ID, @DESCRICAO
--Caso exista dados no Select do Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PRIMO = 1 --Número é primo
SET @DIVISOR = @ID-1
WHILE @DIVISOR > 1
BEGIN
IF @ID % @DIVISOR = 0
BEGIN
SET @PRIMO = 0 --Número não é primo
BREAK
END
SET @DIVISOR = @DIVISOR -1
END
IF @PRIMO = 0 PRINT 'O número '+@DESCRICAO+' não é primo!'
ELSE PRINT 'O número '+@DESCRICAO+' é primo!'
--Leitura da tupla do cursor dentro das variáveis
FETCH NEXT FROM CUR INTO @ID, @DESCRICAO
END
--Fechando um cursor.
CLOSE CUR
--Liberando o cursor da memória (obrigatório)
DEALLOCATE CUR
Resultado:
O número Um é primo!
O número Dois é primo!
O número Tres é primo!
O número Quatro não é primo!
O número Cinco é primo!
O número Seis não é primo!
O número Sete é primo!
sábado, 25 de maio de 2013
Preenchendo texto a direita com caracter
Neste exemplo vamos preencher um texto com algum caracter (*) a direita:
Resultado:
DECLARE @NUMBER VARCHAR(20)
DECLARE @COUNT INTEGER
SET @NUMBER = 'Texto'
SET @COUNT = 10
SELECT '1234567890' AS FORMATED UNION ALL
SELECT @NUMBER + REPLICATE('*',@COUNT - LEN(@NUMBER))
Resultado:
FORMATED
-----------
1234567890
Texto*****
Formatando Números com Zeros a Esquerda em SQL-Server
No exemplo abaixo vamos realizar uma formatação numérica com zeros a esquerda usando SQL-Server
Conforme a variável @COUNT informamos qual o tamanho do número:
Resultado:
Conforme a variável @COUNT informamos qual o tamanho do número:
DECLARE @NUMBER VARCHAR(20)
DECLARE @COUNT INTEGER
SET @NUMBER = '1234'
SET @COUNT = 8
SELECT REPLICATE('0',@COUNT - LEN(@NUMBER)) + @NUMBER AS FORMATED
Resultado:
Apenas Números em SQL-Server
Acontece de encontrarmos situações para:
>> Corrigir dados já persistidos por um sistema
>> Precisarmos validar um arquivo de carga para efetivarmos no banco de dados
Uma dessas validações é exatamente retirar dados não numéricos de um texto, segue um código para deixar apenas números a partir de um texto:
Resultado:
>> Corrigir dados já persistidos por um sistema
>> Precisarmos validar um arquivo de carga para efetivarmos no banco de dados
Uma dessas validações é exatamente retirar dados não numéricos de um texto, segue um código para deixar apenas números a partir de um texto:
DECLARE @TEXTO VARCHAR(MAX)
SET @TEXTO = 'abc123dc4fda$%#'
DECLARE @INDEX SMALLINT
SET @INDEX = PATINDEX('%[^0-9]%', @TEXTO)
WHILE @INDEX > 0
BEGIN
SET @TEXTO = STUFF(@TEXTO, @INDEX, 1, '')
SET @INDEX = PATINDEX('%[^0-9]%', @TEXTO)
END
SELECT @TEXTO AS NUMERO
Resultado:
Trabalhando com Datas (Listagem de Meses para uma caixa de seleção)
Usando SQL-Server, segue código que nos retornará uma listagem dos Meses
Resultado
SET LANGUAGE 'Brazilian'
DECLARE @MESES TABLE (ID VARCHAR(2), MES VARCHAR(10))
DECLARE @NUM INT
SET @NUM = 0
WHILE @NUM < 12
BEGIN
INSERT INTO @MESES
SELECT REPLICATE('0',2-LEN(CAST(@NUM+1 AS VARCHAR(2))))+
CONVERT(VARCHAR,@NUM+1)
,DATENAME(MONTH,DATEADD(MONTH, @NUM, 0))
SET @NUM = @NUM + 1
END
SELECT * FROM @MESES
Resultado
quarta-feira, 17 de abril de 2013
Oracle - PL-SQL - Comandos (Cursores)
Apresento como trabalhar com Cursores no Oracle
Exemplo cursor LOOP Simples
SET SERVEROUTPUT ON
DECLARE CURSOR cur IS SELECT * FROM tb_tabela;
linha cur%ROWTYPE;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO linha;
EXIT WHEN cur%NOTFOUND;
/*
comandos
DBMS_OUTPUT.PUT_LINE(linha.campo_tabela);
*/
END LOOP;
CLOSE cur;
END;
Exemplo cursor LOOP WHILE
SET SERVEROUTPUT ON
DECLARE CURSOR cur IS SELECT * FROM tb_tabela;
linha cur%ROWTYPE;
BEGIN
OPEN cur;
FETCH cur INTO linha;
WHILE cur%FOUND LOOP
/*
comandos
DBMS_OUTPUT.PUT_LINE(linha.campo_tabela);
*/
FETCH cur INTO linha;
END LOOP;
CLOSE cur;
END;
Exemplo cursor LOOP FOR
SET SERVEROUTPUT ON
DECLARE CURSOR cur IS SELECT * FROM tb_tabela;
BEGIN
FOR linha IN cur LOOP
/*
comandos
DBMS_OUTPUT.PUT_LINE(linha.campo_tabela);
*/
END LOOP;
END;
Exemplo cursor LOOP FOR IMPLICITO
SET SERVEROUTPUT ON
BEGIN
FOR linha IN (SELECT * FROM tb_tabela) LOOP
/*
comandos
DBMS_OUTPUT.PUT_LINE(linha.campo_tabela);
*/
END LOOP;
END;
Exemplo cursor LOOP FOR (FOR UPDATE)
DECLARE CURSOR cur SELECT * FROM tb_tabela FOR UPDATE;
variavel NUMBER;
BEGIN
variavel := 1.0;
FOR linha IN cur LOOP
IF linha.campo_media >7 THEN
variavel := 1.5;
END IF;
UPDATE tb_tabela
SET campo_tabela = variavel
WHERE CURRENT OF cur;
END LOOP;
END;
cur%ISOPEN --- VERIFICANDO CURSOR ABERTO
DECLARE CURSOR cur SELECT * FROM tb_tabela
IF cur%ISOPEN THEN
CLOSE cur;
END IF;
OPEN cur;
SQL%FOUND oposto SQL%NOTFOUND
SET SERVEROUTPUT ON
DELETE FROM tb_tabela WHERE condicao_verdadeira;
IF SQL%FOUND THEN -- Sucesso na Cláusula DELETE
DBMS_OUTPUT.PUT_LINE( 'Linhas afetadas na instrução DELETE: '|| TO_CHAR(SQL%ROWCOUNT));
END IF;
tb_tabela.campo_tabela%TYPE
Declarando variável do mesmo tipo de uma coluna de uma tabela
DECLARE variavelCampo tb_tabela.campo_tabela%TYPE;
tb_tabela%ROWTYPE
Declarando uma variável com estrutura de uma tupla de uma tabela
DECLARE linha tb_tabela%ROWTYPE;
Criando um Tipo de dados
DECLARE
TYPE novoTipo IS TABLE OF tb_tabela.campo_tabela%TYPE;
variavelNovoTipo novoTipo;
CURSOR cur IS
SELECT campo_tabela
FROM tb_tabela
WHERE condicao_verdadeira;
BEGIN
OPEN cur;
FETCH cur BULK COLLECT INTO variavelNovoTipo;
CLOSE cur;
FOR i IN variavelNovoTipo.FIRST .. variavelNovoTipo.LAST
LOOP
DBMS_OUTPUT.PUT_LINE( variavelNovoTipo(i) );
END LOOP;
END;
Oracle - PL-SQL - Comandos (Laços)
Abaixo os Laços possíveis que podemos usar no Oracle
LOOP FINITO CONDICIONAL
LOOP
/*
comandos
*/
EXIT WHEN condicao_verdadeira
END LOOP;
LOOP FOR N VEZES
FOR i IN 1..N LOOP
/*
comandos
quando i for igual a N sai do loop
*/
END LOOP;
LOOP FOR N VEZES INVERTIDO
FOR i IN REVERSE 1..N LOOP
/*
comandos
quando i for igual a 1 sai do loop
*/
END LOOP;
LOOP WHILE CONDICIONAL
DECLARE i NUMBER := 0;
DECLARE n NUMBER := 10;
WHILE i < n LOOP
/*
comandos
quando i for igual a 'n' sai do loop
*/
i := i + 1;
END LOOP;
domingo, 14 de abril de 2013
Por que um Datawarehouse?
A pedido do meu amigo Serginho [Sheep] estou escrevendo este post sobre Datawarehouse.
Este post não tem o foco de dizer como construir um Datawarehouse mas dizer o que é e para que serve. Então vou contar uma historinha.....(risos)
Datawarehouse como o próprio nome diz, do inglês "Armazém de Dados" é um repositório que contém dados para finalidades exclusivas de análise de dados para facilitar em tomadas de decisões em um ambiente corporativo, também conhecido pelas siglas DW, DWH ou EDW (Enterprise Datawarehouse).
Em grandes ambientes corporativos que tem um sistema de informação bem definido, (nos moldes tradicionais, dentro dele possui relatórios de finalidade de gerenciamento), muitas vezes seus relatórios não são suficientemente ideais para que diretores realize a tomada de decisões na empresa em que trabalha, e nestes sistemas quando criados, podemos considerar que um relatório não "nasce" com o sistema, pois, são de tempos em tempos solicitados pelos diretores.
Pensando que a cada dia um novo relatório "nasce", e que um relatório quando solicitado, pode levar horas ou até mesmo dias para ser visualizado pelo gestor, além de possuir um custo por passar por um processo de desenvolvimento, surgiu a necessidade de uma solução que permitisse aos diretores e gestores uma tomada de decisão mais ágil e evitar esse tempo "perdido" até ter a informação, assim surgiu o Datawarehouse.
Um DW é um banco de dados que deve ser construído de maneira que facilite a visualização de dados cruzados, o mesmo normalmente é "alimentado" por meio de dados já persistidos em outro banco de dados (normalmente um relacional). Não devemos pensar que o mesmo será a solução para todos os relatórios possíveis do sistema, suas possibilidades são permitidas conforme uma análise da necessidade e essência do sistema, um trabalho conjunto entre Administradores de Banco de Dados e Analistas de Sistemas pode ser importante para definir bem o foco das possíveis necessidades do cliente, porém o responsável por construir e definir as regras para criação do Datawarehouse é o DBA (DataBase Administrator).
O mais importante é saber que o DW não veio para substituir todos os relatórios de um sistema, em um DW serão armazenadas apenas informações históricas, são dados que não podem sofrer alterações, pois normalmente os gestores tomam decisões com base em informações passadas, então como exemplo: pode se adotar que no sistema serão visíveis dados apenas de até 1 anos atraz, sendo os demais anos anteriores os dados visiveis apenas em um DW.
Exitem ferramentas gerenciais de BI (Business Inteligence) que permitem o acesso de maneira eficiente de um DW.
segunda-feira, 25 de março de 2013
Postgresql - Paginação com Filtros e Ordenação
Para o caso de precisarmos criar uma consulta que nos retorne os dados paginados com filtros e ordenados
Veja no exemplo abaixo como é facil usando Postgresql
Veja no exemplo abaixo como é facil usando Postgresql
------------------------------------------------------------------
SELECT Z.* FROM
( SELECT 1 AS ID, 'UM' AS DESCRICAO ,12 AS NUMERO
UNION SELECT 2 AS ID, 'DOIS' AS DESCRICAO ,15 AS NUMERO
UNION SELECT 3 AS ID, 'TRÊS' AS DESCRICAO ,26 AS NUMERO
UNION SELECT 4 AS ID, 'QUATRO' AS DESCRICAO ,20 AS NUMERO
UNION SELECT 5 AS ID, 'CINCO' AS DESCRICAO ,23 AS NUMERO
UNION SELECT 6 AS ID, 'SEIS' AS DESCRICAO ,32 AS NUMERO
UNION SELECT 7 AS ID, 'SETE' AS DESCRICAO ,14 AS NUMERO
UNION SELECT 8 AS ID, 'OITO' AS DESCRICAO ,27 AS NUMERO
UNION SELECT 9 AS ID, 'NOVE' AS DESCRICAO ,11 AS NUMERO
UNION SELECT 10 AS ID, 'DEZ' AS DESCRICAO ,21 AS NUMERO
) Z
WHERE UPPER(Z.DESCRICAO) LIKE UPPER('%%') --filtros diversos
ORDER BY Z.ID -- Ordenação
LIMIT 3 -- Quantidade de registros por página
OFFSET (3 - 1) * 3
--(Numero da Pagina -1) * Quantidade registros por página
------------------------------------------------------------------
SQL Server 2000 - Paginação com Filtros e Ordenação
Bom, em se tratando de listagem de dados, algumas vezes encontramos a necessidade de fazer uma query que nos retorne muitos registros, neste momento é hora de pensar em dados paginados, onde é apresentado alguns registros a cada página, mas em muitos casos é necessário deixar disponível a ordenação a cargo do usuário, ao clicar no cabeçalho executa ordenação pela mesma.
Segue abaixo uma solução para criar uma Stored Procedure, que a consulta retorne apenas o que queremos evitando muitos registros ocultos ou depender de componentes externos, além de poder aplicar filtros e ordenação.
Segue abaixo uma solução para criar uma Stored Procedure, que a consulta retorne apenas o que queremos evitando muitos registros ocultos ou depender de componentes externos, além de poder aplicar filtros e ordenação.
-----------------------------------------------------------------------
--Populando uma tabela para o exemplo
-----------------------------------------------------------------------
DECLARE @TABELA TABLE(
ID INT
,DESCRICAO VARCHAR(100)
,NUMERO INT
)
INSERT INTO @TABELA
SELECT 1, 'UM' ,12
UNION SELECT 2, 'DOIS' ,15
UNION SELECT 3, 'TRÊS' ,26
UNION SELECT 4, 'QUATRO',20
UNION SELECT 5, 'CINCO' ,23
UNION SELECT 6, 'SEIS' ,32
UNION SELECT 7, 'SETE' ,14
UNION SELECT 8, 'OITO' ,27
UNION SELECT 9, 'NOVE' ,11
UNION SELECT 10, 'DEZ' ,21
-----------------------------------------------------------------------
--Parâmetros da StoredProcedure
-----------------------------------------------------------------------
--Filtros
DECLARE @Filtro_ID INT --ID EXATO
DECLARE @Filtro_DESCRICAO VARCHAR(100) --PARTE DA DESCRICAO
DECLARE @Filtro_NUMERO INT --NUMERO EXATO
--Ordenação
DECLARE @Ordenacao INT --ATRAVEZ DO INDICE INFORMADO
--Paginação
DECLARE @NrPagina INT --INFORMA O NUMERO DA PÁGINA
DECLARE @PorPagina INT --INFORMA REGISTROS POR PÁGINA
DECLARE @TemProxima CHAR(1) --RETORNA 'S' COM PROXIMA PÁGINA
-- OU 'N' SEM PROXIMA PÁGINA
-----------------------------------------------------------------------
--Valores para exemplificar este seriam os dados de entrada da Procedure
SET @Filtro_ID = NULL
SET @Filtro_DESCRICAO = NULL
SET @Filtro_NUMERO = NULL
SET @NrPagina = 1
SET @PorPagina = 3
SET @Ordenacao = NULL
-----------------------------------------------------------------------
DECLARE @IndexInicial INT
SET @IndexInicial = (@NrPagina * @PorPagina ) - @PorPagina
--CRIANDO ESTRUTURA IGUAL TABELA MAS COM COLUNA ROWID ORDENADO E FILTRADO
DECLARE @TABELA_PRONTA TABLE(
ROWID INT IDENTITY(1,1)
, ID INT
, DESCRICAO VARCHAR(100)
, NUMERO INT
)
--INSERINDO DADOS PRONTOS PARA SEREM APRESENTADOS
INSERT INTO @TABELA_PRONTA (ID, DESCRICAO, NUMERO)
SELECT E.ID ,E.DESCRICAO ,E.NUMERO
FROM @TABELA E
WHERE ( E.ID = @ID OR @ID IS NULL )
AND ( UPPER(E.DESCRICAO) LIKE '%'+UPPER(@DESCRICAO)+'%'
OR @DESCRICAO IS NULL )
AND (E.NUMERO = @NUMERO OR @NUMERO IS NULL)
ORDER BY CASE --ORDENACOES INTEIRAS
WHEN @Ordenacao = 1 THEN E.ID
WHEN @Ordenacao = 3 THEN E.NUMERO
END,
CASE --ORDENACOES VARCHAR
WHEN @Ordenacao = 2 THEN E.DESCRICAO
END
--PAGINAÇÃO
SET ROWCOUNT @PorPagina
SELECT E.ROWID
,E.ID
,E.DESCRICAO
,E.NUMERO
FROM @TABELA_PRONTA E
WHERE ROWID > @IndexInicial
SET ROWCOUNT 0
--Informando se existe próxima página
SELECT @TemProxima = CASE
WHEN COUNT(ID) > (@NrPagina * @PorPagina ) THEN
'S'
ELSE
'N'
END
FROM @TABELA
-----------------------------------------------------------------------
sábado, 16 de março de 2013
SQL Server 2000 - Stored Procedure com parâmetro para IN
Em alguns momentos em nossa jornada de trabalho podemos precisar criar uma Stored Procedure no qual precisamos passar como parâmetro de entrada um conjunto de dados (para a clausula IN da Query) com a finalidade de realizar o filtro dos dados.
O trecho de código abaixo foi construido para ser executado sem a necessidade de criação de objetos, mantendo a lógica, basta criar a Stored Procedure de acordo com a necessidade e usando as tabelas reais.
Basta copiar, colar e executar.
Basta copiar, colar e executar.
-----------------------------------------------------------------------
--Criamos uma tabela populada para montar nosso exemplo
-----------------------------------------------------------------------
DECLARE @TABELA TABLE(ID INT, DESCRICAO VARCHAR(100))
INSERT INTO @TABELA
SELECT 1, 'UM' UNION SELECT 2, 'DOIS'
UNION SELECT 3, 'TRÊS' UNION SELECT 4, 'QUATRO'
UNION SELECT 5, 'CINCO' UNION SELECT 6, 'SEIS'
UNION SELECT 7, 'SETE' UNION SELECT 8, 'OITO'
UNION SELECT 9, 'NOVE' UNION SELECT 10, 'DEZ'
-----------------------------------------------------------------------
DECLARE @SEPARADOR CHAR(1) --Parâmetro de Entrada
DECLARE @DADOS VARCHAR(8000) --Parâmetro de Entrada
DECLARE @VALOR VARCHAR(8000)
DECLARE @TABELA_IN TABLE (VALOR VARCHAR(8000))
--Exemplo de dados nos parâmetros de entrada
SET @SEPARADOR = ','
SET @DADOS = 'UM,TRÊS,CINCO,SETE,NOVE'
SET @DADOS = @DADOS + @SEPARADOR
--Percorrendo @DADOS para gravar os Itens na tabela temporária buscando
--por meio do @SEPARADOR.
WHILE LEN(@DADOS) > 0
BEGIN
SET @VALOR =
LTRIM(SUBSTRING(@DADOS, 1, CHARINDEX(@SEPARADOR, @DADOS) - 1))
SET @DADOS =
SUBSTRING(@DADOS, CHARINDEX(@SEPARADOR, @DADOS) + 1, LEN(@DADOS))
INSERT INTO @TABELA_IN (VALOR) VALUES (@VALOR)
END
--Realizando a consulta desejada
SELECT T.ID, T.DESCRICAO
FROM @TABELA T
WHERE T.DESCRICAO IN (SELECT VALOR FROM @TABELA_IN)
-----------------------------------------------------------------------
Assinar:
Postagens (Atom)