/* Geralmente é complicado obter o ultimo dia válido considerando os feriados nacionais,
muito usado no ramo financeiro. */
/* Esse artigo pretende dá uma opção de como obter o ultimo dia util dentre várias soluções
encontradas na net, com o uso de stores functions do Oracle PL/SQL */
/* Mostrando como criar uma função SQL Oracle isto é em PL/SQL para obter o ultimo dia útil, considerando os feriados nacionais dentre outros assuntos correlacionados. */
/* Primeiro criamos uma tabela feriado para melhor elucidar as store functions, conforme script abaixo: */
-- DROP TABLE FERIADO;
CREATE TABLE FERIADO
(
ID_FERIADO NUMBER(10,0) NOT NULL ENABLE
, DATA DATE NOT NULL ENABLE
, DESCRICAO VARCHAR2(50 BYTE) NOT NULL ENABLE
, ID_TIPO_FERIADO NUMBER(3,0) NOT NULL ENABLE -- 1 NACIONAL, 2 ESTADUAL, 3 MUNICIPAL, 4 OUTROS
, OBS NVARCHAR2(15)
, CONSTRAINT PK_FERIADO_ID_FERIADO PRIMARY KEY (ID_FERIADO)
, CONSTRAINT UQ_FERIADO_DATA UNIQUE (DATA)
);
-- DROP SEQUENCE FERIADO_FCSEQ;
CREATE SEQUENCE FERIADO_FCSEQ
INCREMENT BY 1
START WITH 1
NOCYCLE;
-- DROP TRIGGER TG_FERIADO_ID_FERIADO_BI;
CREATE OR REPLACE TRIGGER TG_FERIADO_ID_FERIADO_BI BEFORE INSERT ON FERIADO
FOR EACH ROW
WHEN (new.ID_FERIADO IS NULL) BEGIN
SELECT FERIADO_FCSEQ.NEXTVAL INTO :new.ID_FERIADO FROM dual;
END;
/
ALTER TRIGGER TG_FERIADO_ID_FERIADO_BI ENABLE;
/* Se desejar usar o o arquivo CSV dos feriados chamado
FERIADOS.CSV com CRLF no final de cada linha, use o SQL Loader do Oracle para dá carga do arquivo, evitando assim digitação de datas, é possível obtê-lo
CLICANDO AQUI! */
/* Criando o arquivo de controle chamado
FERIADO.CTL salvo no diretorio C:\TEMP */
load data
infile 'FERIADO.CSV'
into table feriado
fields terminated by "|"
(
data DATE "YYYY-MM-DD"
, descricao
, id_tipo_feriado
, obs
)
/* Depois executar o SQL Loader, no Linux é igual ao do Windows considerando nome da pasta, no Windows fica assim: */
/* Lembrando de considerar, isto é, permutar seu usuario e senha, ip_servidor e SID do Oracle do seu servidor*/
sqlldr usuario/senha@ip_servidor/SID control=FERIADO.CTL log=X.LOG bad-Z.BAD READSIZE=10000000
/* Depois criamos uma store function para idenficar dias feriados usando a tabela feriado conforme script abaixo: */
CREATE OR REPLACE FUNCTION usf_feriado (d in date)
RETURN integer
AS
x integer;
BEGIN
SELECT count(*)
INTO x
FROM feriado
WHERE to_char(data,'YYYY-MM-DD') = to_char(d,'YYYY-MM-DD');
IF (x >= 1) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END usf_feriado;
/* Logo em seguida criamos um store function para tratar o ultimo dia util, conforme script abaixo: */
CREATE OR REPLACE FUNCTION usf_ultimo_dia_util ( dt_base in date )
RETURN date
AS
dt_basex date;
bo_fimx boolean;
BEGIN
dt_basex := dt_base;
bo_fimx := false;
WHILE NOT (bo_fimx) LOOP
bo_fimx := to_char(dt_basex,'d') NOT IN ('1','7');
IF usf_feriado(dt_basex) = 1 THEN
bo_fimx := false;
END IF;
IF NOT (bo_fimx) THEN
dt_basex := dt_basex - 1;
END IF;
END LOOP;
RETURN dt_basex;
EXCEPTION
WHEN others THEN
RAISE;
END usf_ultimo_dia_util;
/* Testando o uso da function em SQL puro */
SELECT usf_feriado(sysdate) AS DT FROM DUAL; -- RETORNA 1 PARA FERIADO E 0 PARA OUTROS DIAS
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; -- ALTERA DATA PARA FORMATO 'YYYY-MM-DD'
SELECT usf_ultimo_dia_util ('2015-04-05') from dual; -- RETORNA O ULTIMO DIA UTIL -> 2015-04-02
/* Espero ter ajudado! */
/* Seja Feliz!! */
/* APdSJC */
Caraca, véi! Que Massa! Parabéns Pelo Blog! Ajudou muito!
ResponderExcluir