Como retornar registros que envolvem conflito de datas?

[fsitjaParticipante]

Uma forma de implementar:

---


Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as FSITJA

SQL>
SQL> create table hist_vinculo (
2 COD_ORGAO_VINCULADO number(8) not null,
3 COD_ORGAO_VINCULADOR number(8) not null,
4 DT_INICIO date not null,
5 DT_FIM date,
6 primary key (COD_ORGAO_VINCULADO,
7 COD_ORGAO_VINCULADOR,
8 DT_INICIO));

Table created
SQL> alter table hist_vinculo add constraint ck_dt_fim check (dt_fim >= dt_inicio);

Table altered
SQL> CREATE OR REPLACE PACKAGE pack_hist_vinc IS
2 TYPE t_hist_vinculo IS TABLE OF hist_vinculo%ROWTYPE;
3 tab_hist t_hist_vinculo := t_hist_vinculo(NULL);
4 END;
5 /

Package created
SQL> create or replace trigger t_valida_hist_b_d
2 before insert or update on hist_vinculo
3 BEGIN
4 pack_hist_vinc.tab_hist.delete;
5 END;
6 /

Trigger created
SQL> create or replace trigger t_valida_hist_br_iu
2 after insert or update on hist_vinculo for each row
3 DECLARE
4 i NUMBER;
5 BEGIN
6 pack_hist_vinc.tab_hist.extend;
7 i := pack_hist_vinc.tab_hist.last;
8 pack_hist_vinc.tab_hist(i).cod_orgao_vinculado := :new.cod_orgao_vinculado;
9 pack_hist_vinc.tab_hist(i).cod_orgao_vinculador := :new.cod_orgao_vinculador;
10 pack_hist_vinc.tab_hist(i).dt_inicio := :new.dt_inicio;
11 END;
12 /

Trigger created
SQL> create or replace trigger t_valida_hist_a_iu
2 after insert or update on hist_vinculo
3 DECLARE
4 cont_dt_fim NUMBER;
5 BEGIN
6 FOR i IN 1 .. pack_hist_vinc.tab_hist.count
7 LOOP
8 SELECT COUNT()
9 INTO cont_dt_fim
10 FROM hist_vinculo
11 WHERE cod_orgao_vinculado = pack_hist_vinc.tab_hist(i).cod_orgao_vinculado
12 AND cod_orgao_vinculador = pack_hist_vinc.tab_hist(i).cod_orgao_vinculador
13 AND dt_fim IS NULL;
14 IF cont_dt_fim > 1
15 THEN
16 raise_application_error(-20000,
17 'Não pode haver mais de um vínculo aberto para COD_ORGAO_VINCULADO ' || pack_hist_vinc.tab_hist(i)
18 .cod_orgao_vinculado || ' e COD_ORGAO_VINCULADOR ' || pack_hist_vinc.tab_hist(i)
19 .cod_orgao_vinculador);
20 END IF;
21 SELECT COUNT()
22 INTO cont_dt_fim
23 FROM (SELECT cod_orgao_vinculado,
24 cod_orgao_vinculador,
25 dt_inicio,
26 dt_fim,
27 lead(dt_inicio) over(PARTITION BY cod_orgao_vinculado, cod_orgao_vinculador ORDER BY dt_inicio) prox_dt_ini
28 FROM hist_vinculo)
29 WHERE prox_dt_ini 0
31 THEN
32 raise_application_error(-20001, 'Data Fim sobrepõe com a próxima Data de Início');
33 END IF;
34 END LOOP;
35 END;
36 /

Trigger created
SQL> INSERT INTO hist_vinculo
2 (cod_orgao_vinculado, cod_orgao_vinculador, dt_inicio, dt_fim)
3 VALUES
4 (1, 2, to_date('01/01/2009', 'DD/MM/YYYY'), to_date('31/01/2009', 'DD/MM/YYYY'));

1 row inserted
SQL> INSERT INTO hist_vinculo
2 (cod_orgao_vinculado, cod_orgao_vinculador, dt_inicio, dt_fim)
3 VALUES
4 (1, 2, to_date('01/02/2009', 'DD/MM/YYYY'), to_date('31/03/2009', 'DD/MM/YYYY'));

1 row inserted
SQL> -- testa sobreposição de períodos
SQL> INSERT INTO hist_vinculo
2 (cod_orgao_vinculado, cod_orgao_vinculador, dt_inicio, dt_fim)
3 VALUES
4 (1, 2, to_date('30/03/2009', 'DD/MM/YYYY'), to_date('31/05/2009', 'DD/MM/YYYY'));

INSERT INTO hist_vinculo
(cod_orgao_vinculado, cod_orgao_vinculador, dt_inicio, dt_fim)
VALUES
(1, 2, to_date('30/03/2009', 'DD/MM/YYYY'), to_date('31/05/2009', 'DD/MM/YYYY'))

ORA-20001: Data Fim sobrepõe com a próxima Data de Início
ORA-06512: at "FSITJA.T_VALIDA_HIST_A_IU", line 30
ORA-04088: error during execution of trigger 'FSITJA.T_VALIDA_HIST_A_IU'
SQL> -- testa deixar 2 datas fim abertas
SQL> INSERT INTO hist_vinculo
2 (cod_orgao_vinculado, cod_orgao_vinculador, dt_inicio, dt_fim)
3 VALUES
4 (1, 2, to_date('01/04/2009', 'DD/MM/YYYY'), null);

1 row inserted
SQL> INSERT INTO hist_vinculo
2 (cod_orgao_vinculado, cod_orgao_vinculador, dt_inicio, dt_fim)
3 VALUES
4 (1, 2, to_date('01/05/2009', 'DD/MM/YYYY'), null);

INSERT INTO hist_vinculo
(cod_orgao_vinculado, cod_orgao_vinculador, dt_inicio, dt_fim)
VALUES
(1, 2, to_date('01/05/2009', 'DD/MM/YYYY'), null)

ORA-20000: Não pode haver mais de um vínculo aberto para COD_ORGAO_VINCULADO 1 e COD_ORGAO_VINCULADOR 2
ORA-06512: at "FSITJA.T_VALIDA_HIST_A_IU", line 14
ORA-04088: error during execution of trigger 'FSITJA.T_VALIDA_HIST_A_IU'

SQL>