/* AUTOR: FRANKLIN NASCIMENTO DATA: 19/05/2011 DESCRIÇÃO: PACOTE COTENDO SCRIPTS SQL PARA CONSTRUÇÃO DE TABELA DIMENSÃO TEMPO, QUE CHAMAREMOS DE TD_TEMPO, PROCEDURE PARA POPULAR A TABELA E NA ULTIMA PÁGINA UM EXEMPLO PARA EXECUTAR A PROCEDURE. DESENVOLVIDO PARA ORACLE 10G OU SUPERIOR. VIDE DESCRIÇÃO DA PROCEDURE PARA MAIS DETALHES DÚVIDAS, SUGESTÕES:
[email protected] */ -- Create table create table TD_TEMPO ( CO_SEQ_TEMPO NUMBER not null null, , DT_DATA DATE, DATE , CO_DIA NUMBER, NUMBER , CO_DIA_SEMANA NUMBER NUMBER, , DS_DIA_SEMANA VARCHAR2 VARCHAR2( (15 15), ), CO_MES NUMBER, NUMBER , DS_MES VARCHAR2( VARCHAR2 (10 10), ), CO_MES_ANO NUMBER, NUMBER , DS_MES_ANO VARCHAR2( VARCHAR2 (8), CO_BIMESTRE NUMBER, NUMBER , DS_BIMESTRE VARCHAR2( VARCHAR2 (11 11), ), CO_TRIMESTRE NUMBER, NUMBER , DS_TRIMESTRE VARCHAR2( VARCHAR2 (12 12), ), CO_ESTACAO NUMBER, NUMBER , DS_ESTACAO VARCHAR2( VARCHAR2 (9), CO_SEMESTRE NUMBER, NUMBER , DS_SEMESTRE VARCHAR2( VARCHAR2 (11 11), ), CO_ANO NUMBER, NUMBER , CO_MES_ANT NUMBER, NUMBER , DS_MES_ANT VARCHAR2( VARCHAR2 (8), CO_MES_ANO_ANT NUMBER NUMBER, , DS_MES_ANO_ANT VARCHAR2 VARCHAR2( (8), CO_ANO_ANT NUMBER ) tablespace
-- Utilize seu tablespace pctfree 10 initrans 1 maxtrans 255 storage ( initial 64 64K K next 1M minextents 1 maxextents unlimited ); -- Create/Recreate primary, unique and foreign key constraints alter table TD_TEMPO add constraint PK_TD_TEMPO primary key (CO_SEQ_TEMPO) using index tablespace -- Utilize seu tablespace pctfree 10 initrans 2 maxtrans 255 storage ( initial 64 64K K next 1M
minextents 1 maxextents unlimited ); /
CREATE OR REPLACE PROCEDURE PROC_CARGA_TEMPO(DATA_INICIAL IN DATE DATE, , DATA_FINAL IN DATE DATE) ) AS /*====================================================================================== AUTOR: FRANKLIN NASCIMENTO DATA: 19/05/2011 DESCRIÇÃO: * PROCEDURE QUE CARREGA TABELA DE DIMENSÃO TEMPO PARA FINS DE DATA WHAREHOUSE * INSERE DATA INFORMADA NO PARÂMETRO INICIAL ATÉ DATA FINAL INCLUSIVE * CASO EXISTA REGISTROS A DATA_INICIAL É DESCONCIDERADA E UTILIZA-SE A ULTIMA DATA REGISTRADA. * CASO SEJA INSERIDA UMA DATA_FINAL MENOR QUE A DATA_INICIAL NENHUM REGISTRO É INSERIDO, ASSIM COMO A DATA FINAL SEJA MENOR QUE A ULTIMA DATA REGISTRADA
DÚVIDAS, SUGESTÕES: [email protected] ======================================================================================*/ ULTIMA_DATA DATE; DATE ; DATA_CORRENTE DATE DATE; ; MES_CORRENTE NUMBER NUMBER; ; DIA_CORRENTE NUMBER NUMBER; ; QTD_DIAS NUMBER; NUMBER ; BEGIN SELECT MAX MAX(DT_DATA) (DT_DATA) INTO ULTIMA_DATA FROM TD_TEMPO;
/* RETORNA A ULTIMA DATA EXISTENTE NA TABELA */
IF ULTIMA_DATA IS NULL THEN DATA_CORRENTE := DATA_INICIAL; /*PARA PRIMEIRA CARGA INSERE-SE VALORES PADRÕES PARA DATAS INVÁLIDAS E DATAS EM BRANCO (NÃO ENCONTRADA) ENCONTRADA)*/ */ INSERT INTO TD_TEMPO VALUES VALUES((-2 2,'02/01/0001' ,,-2 2,,-2 2,'DATA INVÁLIDA',INVÁLIDA' ,-2 2,'DI' 'DI',,2,'DI' 'DI',,-2 2,'DI' 'DI',,-2 2,'DI' 'DI',,-2 2,'DI' 'DI',,-2 2,'DI' 'DI',,-2 2,,-2 2,'DI' 'DI',,-2 2,'DI' 'DI',,-2 2); INSERT INTO TD_TEMPO VALUES VALUES((-1 1,'01/01/0001' ,,-1 1,,-1 1,'NÃO INFORMADO',INFORMADO' ,-1 1,'NI' 'NI',,1,'NI' 'NI',,-1 1,'NI' 'NI',,-1 1,'NI' 'NI',,-1 1,'NI' 'NI',,-1 1,'NI' 'NI',,-1 1,,-1 1,'NI' 'NI',,-1 1,'NI' 'NI',,-1 1); ELSE DATA_CORRENTE := ULTIMA_DATA + 1; END IF IF; ; /* CASO ULTIMA_DATA SEJA NULA UTILIZA-SE A DATA_INICIAL SE NÃO USA-SE PROXIMA DATA */ QTD_DIAS := DATA_FINAL - DATA_CORRENT DATA_CORRENTE; E;
--QUANTIDADE DE DIAS A SER INSERIDO
FOR REG IN 0..QTD_DIAS --WHILE QTD_DIAS > 0 AND DATA_CORRENT DATA_CORRENTE E < ULTIMA_DATA LOOP MES_CORRENTE := TO_NUMBER(TO_CHAR(DATA_CORRENTE, 'MM')); 'MM')); DIA_CORRENTE := TO_NUMBER(TO_CHAR(DATA_CORRENTE, 'DD' 'DD')); )); INSERT INTO TD_TEMPO (CO_SEQ_TEMPO, DT_DATA, CO_DIA, CO_DIA_SEMANA, DS_DIA_SEMANA, CO_MES, DS_MES, CO_MES_ANO, DS_MES_ANO, CO_BIMESTRE, DS_BIMESTRE,
CO_TRIMESTRE, DS_TRIMESTRE, CO_ESTACAO, DS_ESTACAO, CO_SEMESTRE, DS_SEMESTRE, CO_ANO, CO_MES_ANT, DS_MES_ANT, CO_MES_ANO_ANT, DS_MES_ANO_ANT, CO_ANO_ANT ) VALUES( VALUES ( TO_NUMBER(TO_CHAR(DATA_CORRENTE, 'YYYYMMDD' )),--CO_SEQ_TEMPO DATA_CORRENTE, --DT_DATA DIA_CORRENTE, --CO_DIA TO_NUMBER(TO_CHAR(DATA_CORRENTE, 'D' 'D')), )), --CO_DIA_SEMANA TO_CHAR(DATA_CORRENTE, 'DAY' 'DAY'), ), --DS_DIA_SEMANA MES_CORRENTE, --CO_MES TO_CHAR(DATA_CORRENTE, 'MONTH' 'MONTH'), ), --DS_MES TO_NUMBER(TO_CHAR(DATA_CORRENTE, 'YYYYMM' 'YYYYMM')), )),--CO_MES_ANO TO_CHAR(DATA_CORRENTE, 'MON YYYY'), YYYY' ), --DS_MES_ANO CASE --CO_BIMESTRE WHEN MES_CORRENTE IN (1,2) THEN 1 WHEN MES_CORRENTE IN (3,4) THEN 2 WHEN MES_CORRENTE IN (5,6) THEN 3 WHEN MES_CORRENTE IN (7,8) THEN 4 WHEN MES_CORRENTE IN (9,10 10) ) THEN 5 WHEN MES_CORRENTE IN (11 11, ,12 12) ) THEN 6 END , CASE --DS_BIMESTRE WHEN MES_CORRENTE IN (1,2) THEN '1º BIMESTRE' WHEN MES_CORRENTE IN (3,4) THEN '2º BIMESTRE' WHEN MES_CORRENTE IN (5,6) THEN '3º BIMESTRE' WHEN MES_CORRENTE IN (7,8) THEN '4º BIMESTRE' WHEN MES_CORRENTE IN (9,10 10) ) THEN '5º BIMESTRE' WHEN MES_CORRENTE IN (11 11, ,12 12) ) THEN '6º BIMESTRE' END, END , CASE --CO_TRIMESTRE WHEN MES_CORRENTE IN (1,2,3) THEN 1 WHEN MES_CORRENTE IN (4,5,6) THEN 2 WHEN MES_CORRENTE IN (7,8,9) THEN 3 WHEN MES_CORRENTE IN (10 10, ,11 11, ,12 12) )THEN 4 END , CASE --DS_TRIMESTRE WHEN MES_CORRENTE IN (1,2,3) THEN '1º TRIMESTRE' WHEN MES_CORRENTE IN (4,5,6) THEN '2º TRIMESTRE' WHEN MES_CORRENTE IN (7,8,9) THEN '3º TRIMESTRE' WHEN MES_CORRENTE IN (10 10, ,11 11, ,12 12) )THEN '4º TRIMESTRE' END, END , CASE --CO_ESTACAO --CO_ESTACA O --(ESTAÇÕES DO ANO) WHEN (MES_CORRENTE = 12 AND DIA_CORRENT DIA_CORRENTE E >= 21 21) ) OR MES_CORRENTE IN (1,2) OR (MES_CORRENTE = 3 AND DIA_CORRENTE < 21 21) ) THEN 1 WHEN (MES_CORRENTE = 3 AND DIA_CORRENTE >= 21 21) ) OR MES_CORRENTE IN (4,5) OR (MES_CORRENTE (MES_CORRENT E = 6 AND DIA_CORRENT DIA_CORRENTE E < 21 21) ) THEN 2 WHEN (MES_CORRENTE = 6 AND DIA_CORRENTE >= 21 21) ) OR MES_CORRENTE IN (7,8) OR (MES_CORRENTE (MES_CORRENT E = 9 AND DIA_CORRENT DIA_CORRENTE E < 21 21) ) THEN 3
WHEN (MES_CORRENTE = 9 AND DIA_CORRENTE >= 21 21) ) OR MES_CORRENTE IN (10 10, ,11 11) ) OR (MES_CORRENTE = 12 AND DIA_CORRENT DIA_CORRENTE E < 21 21) ) THEN 4 END, END , CASE --DS_ESTACAO WHEN (MES_CORRENTE = 12 AND DIA_CORRENT DIA_CORRENTE E >= 21 21) ) OR MES_CORRENTE IN (1,2) OR (MES_CORRENTE = 3 AND DIA_CORRENTE < 21 21) ) THEN 'VERÃO' WHEN (MES_CORRENTE = 3 AND DIA_CORRENTE >= 21 21) ) OR MES_CORRENTE IN (4,5) OR (MES_CORRENTE (MES_CORRENT E = 6 AND DIA_CORRENT DIA_CORRENTE E < 21 21) ) THEN 'OUTONO' WHEN (MES_CORRENTE = 6 AND DIA_CORRENTE >= 21 21) ) OR MES_CORRENTE IN (7,8) OR (MES_CORRENTE (MES_CORRENT E = 9 AND DIA_CORRENT DIA_CORRENTE E < 21 21) ) THEN 'INVERNO' WHEN (MES_CORRENTE = 9 AND DIA_CORRENTE >= 21 21) ) OR MES_CORRENTE IN (10 10, ,11 11) ) OR (MES_CORRENTE = 12 AND DIA_CORRENT DIA_CORRENTE E < 21 21) ) THEN 'PRIMAVERA' END, END , CASE --CO_SEMESTRE WHEN MES_CORRENTE IN (1,2,3,4,5,6) THEN 1 WHEN MES_CORRENTE IN (7,8,9,10 10, ,11 11, ,12 12) )THEN 2 END , CASE --DS_SEMESTRE WHEN MES_CORRENTE IN (1,2,3,4,5,6) THEN '1º SEMESTRE' WHEN MES_CORRENTE IN (7,8,9,10 10, ,11 11, ,12 12) )THEN '2º SEMESTRE' END, END , TO_NUMBER(TO_CHAR(DATA_CORRENTE, 'YYYY' 'YYYY')), )), --CO_ANO TO_NUMBER(TO_CHAR(ADD_MONTHS(DATA_CORRENTE,- 1), ),'YYYYMM' 'YYYYMM')), )), --CO_MES_ANT TO_CHAR(ADD_MONTHS(DATA_CORRENTE,- 1), ),'MON 'MON YYYY'), YYYY' ), --DS_MES_ANT TO_NUMBER(TO_CHAR(ADD_MONTHS(DATA_CORRENTE,- 12 12), ),'YYYYMM' 'YYYYMM')), )),--CO_MES_ANO_ANT, TO_CHAR(ADD_MONTHS(DATA_CORRENTE,- 12 12), ),'MON 'MON YYYY'), YYYY' ), --DS_MES_ANO_ANT TO_NUMBER(TO_CHAR(ADD_MONTHS(DATA_CORRENTE,- 12 12), ),'YYYY' 'YYYY')) )) --CO_ANO_ANT ); DATA_CORRENTE DATA_CORRENT E := DATA_CORRENT DATA_CORRENTE E + 1; END LOOP LOOP; ; COMMIT; COMMIT ; END; END;
begin -- Call the procedure proc_carga_tempo(data_inicial => '01/01/1900' , data_final => '31/12/2112' ); end; end ;
