Bonjour,

Pourriez-vous m'aider concernant l'élaboration d'une vue en SQL sur une base de données contennat des tables d'historisation? Je ne sais pas trop comment m'y prendre pour que ce soit le plus efficace possible

Voici le Contexte:

Mon application permet de gérer des contrats (un contrat contient un ensemble d'informations générales et est lié à des contacts, des "legal references" et des portfolios). Quand une mise à jour est réalisée, une nouvelle entrée est insérée dans les tables d'historisation. Si une modification est faite sur les contacts, legal reference ou portfolio, une nouvelle ligne est également ajoutée dans la table contrat_HIST.

Mon objectif est de créér une vue qui me permettrait sur base des différentes tables d'historisation d'afficher les mises à jour (par rapport à un ID HIST donné).

Je souhaiterais avoir quelque chose comme cela:

Nom : Cap.PNG Affichages : 505 Taille : 18,9 Ko

Ainsi pour chaque nouvelle mise à jour (à une date t grâce à ID HIST), on peut savoir si ce sont les données générales, les contacts, les légales références ou/et les activités qui ont été mis à jour.

Voici la structure des tables de ma base de données:

Table contrat HIST (informations générales)

ID HIST Date Creation ID Contrat Titre Contrat Description Budget
1 01/01/2015 1 Contrat 1 Contrat Informatique 20 000
2 15/01/2015 1 Contrat 1 Contrat Informatique 50 000
3 02/02/2015 2 Contrat 2 Contrat Santé 10 000
4 01/03/2015 2 Contrat 2 Contrat Consommateur 30 000
5 01/07/2015 1 Contrat 1 Contrat Informatique 50 000

Table contact HIST (liaison avec les contacts)

ID HIST ID Contrat Contact name
1 1 Bernard
1 1 Jean
2 1 Nicolas
2 1 Jean
3 2 Nicolas
5 2 Nicolas

Table Legal Refrence HIST (liaison avec les Legal Reference)

ID HIST ID Contrat LegalRef name
1 1 45 - Technologies et Systeme d'Information
3 2 105 - Consommateur et Santé
5 1 27 - Services

Table Portfolio HIST (liaison avec les Portfolios)

ID HIST ID Contrat ID Portfolio Portfolio name Portfolio Value
2 1 1 Portfolio 1 5000
2 1 2 Portfolio 2 7000
4 2 1 Portfolio 1 2000
4 2 2 Portfolio 2 8000

Ainsi je souhaiterais avec cette vue avoir cela:


ID Contrat ID HIST Date Creation GENERAL INFO CONTACT LEGAL REFERENCE PORTFOLIO
1 1 01/01/2015 Updated Updated Updated No Update
1 2 15/01/2015 Updated Updated Updated Updated
1 5 01/07/2015 No Update Updated Updated No Update
2 3 02/02/2015 Updated Updated No Update No Update
2 4 01/03/2015 Updated No Update No Update Updated


Je joints ici les scripts pour la BDD:

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
--------------------------------------------------------
--  DDL for Table CONTACT_HIST
--------------------------------------------------------
 
  CREATE TABLE "CONTACT_HIST" 
   (	"ID_HIST" NUMBER, 
	"ID_CONTRAT" NUMBER, 
	"NAME_CONTACT" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "RAM" ;
REM INSERTING into BO.CONTACT_HIST
SET DEFINE OFF;
Insert into BO.CONTACT_HIST (ID_HIST,ID_CONTRAT,NAME_CONTACT) values (1,1,'Bernard');
Insert into BO.CONTACT_HIST (ID_HIST,ID_CONTRAT,NAME_CONTACT) values (1,1,'Jean');
Insert into BO.CONTACT_HIST (ID_HIST,ID_CONTRAT,NAME_CONTACT) values (2,1,'Nicolas');
Insert into BO.CONTACT_HIST (ID_HIST,ID_CONTRAT,NAME_CONTACT) values (2,1,'Jean');
Insert into BO.CONTACT_HIST (ID_HIST,ID_CONTRAT,NAME_CONTACT) values (3,2,'Nicolas');
Insert into BO.CONTACT_HIST (ID_HIST,ID_CONTRAT,NAME_CONTACT) values (5,2,'Nicolas');
 
--------------------------------------------------------
--  DDL for Table CONTRAT_HIST
--------------------------------------------------------
 
  CREATE TABLE "BO"."CONTRAT_HIST" 
   (	"ID_HIST" NUMBER, 
	"DATE_CREATION" DATE, 
	"ID_CONTRAT" NUMBER, 
	"TITRE_CONTRAT" VARCHAR2(250 BYTE), 
	"DESCRIPTION" VARCHAR2(250 BYTE), 
	"BUDGET" NUMBER
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "RAM" ;
REM INSERTING into BO.CONTRAT_HIST
SET DEFINE OFF;
Insert into BO.CONTRAT_HIST (ID_HIST,DATE_CREATION,ID_CONTRAT,TITRE_CONTRAT,DESCRIPTION,BUDGET) values (1,to_date('01-JAN-15','DD-MON-RR'),1,'Contrat 1 ','Contrat Informatique ',20000);
Insert into BO.CONTRAT_HIST (ID_HIST,DATE_CREATION,ID_CONTRAT,TITRE_CONTRAT,DESCRIPTION,BUDGET) values (2,to_date('15-JAN-15','DD-MON-RR'),1,'Contrat 1 ','Contrat Informatique ',50000);
Insert into BO.CONTRAT_HIST (ID_HIST,DATE_CREATION,ID_CONTRAT,TITRE_CONTRAT,DESCRIPTION,BUDGET) values (3,to_date('02-FEB-15','DD-MON-RR'),2,'Contrat 2 ','Contrat Santé ',10000);
Insert into BO.CONTRAT_HIST (ID_HIST,DATE_CREATION,ID_CONTRAT,TITRE_CONTRAT,DESCRIPTION,BUDGET) values (4,to_date('01-MAR-15','DD-MON-RR'),2,'Contrat 2 ','Contrat Consommateur ',30000);
Insert into BO.CONTRAT_HIST (ID_HIST,DATE_CREATION,ID_CONTRAT,TITRE_CONTRAT,DESCRIPTION,BUDGET) values (5,to_date('01-JUL-15','DD-MON-RR'),1,'Contrat 1 ','Contrat Informatique ',50000);
--------------------------------------------------------
--  DDL for Index CONTRAT_HIST_PK
--------------------------------------------------------
 
  CREATE UNIQUE INDEX "BO"."CONTRAT_HIST_PK" ON "BO"."CONTRAT_HIST" ("ID_HIST") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "RAM" ;
--------------------------------------------------------
--  Constraints for Table CONTRAT_HIST
--------------------------------------------------------
 
  ALTER TABLE "BO"."CONTRAT_HIST" ADD CONSTRAINT "CONTRAT_HIST_PK" PRIMARY KEY ("ID_HIST")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "RAM"  ENABLE;
  ALTER TABLE "BO"."CONTRAT_HIST" MODIFY ("ID_HIST" NOT NULL ENABLE);
 
--------------------------------------------------------
--  DDL for Table LEGAL_REFERENCE_HIST
--------------------------------------------------------
 
  CREATE TABLE "BO"."LEGAL_REFERENCE_HIST" 
   (	"ID_HIST" NUMBER, 
	"ID_CONTRAT" NUMBER, 
	"LEG_REF_NAME" VARCHAR2(250 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "RAM" ;
REM INSERTING into BO.LEGAL_REFERENCE_HIST
SET DEFINE OFF;
Insert into BO.LEGAL_REFERENCE_HIST (ID_HIST,ID_CONTRAT,LEG_REF_NAME) values (1,1,'45 - Technologies et Systeme d''Information');
Insert into BO.LEGAL_REFERENCE_HIST (ID_HIST,ID_CONTRAT,LEG_REF_NAME) values (3,2,'105 - Consommateur et Santé');
Insert into BO.LEGAL_REFERENCE_HIST (ID_HIST,ID_CONTRAT,LEG_REF_NAME) values (5,1,'27 - Services');
 
--------------------------------------------------------
--  DDL for Table PORTFOLIO_HIST
--------------------------------------------------------
 
  CREATE TABLE "BO"."PORTFOLIO_HIST" 
   (	"ID_HIST" NUMBER, 
	"ID_CONTRAT" NUMBER, 
	"PORTFOLIO_ID" NUMBER, 
	"PORTFOLIO_NAME" VARCHAR2(250 BYTE), 
	"PORTFOLIO_VALUE" NUMBER
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "RAM" ;
REM INSERTING into BO.PORTFOLIO_HIST
SET DEFINE OFF;
Insert into BO.PORTFOLIO_HIST (ID_HIST,ID_CONTRAT,PORTFOLIO_ID,PORTFOLIO_NAME,PORTFOLIO_VALUE) values (2,1,1,'Portfolio 1',5000);
Insert into BO.PORTFOLIO_HIST (ID_HIST,ID_CONTRAT,PORTFOLIO_ID,PORTFOLIO_NAME,PORTFOLIO_VALUE) values (2,1,2,'Portfolio 2',7000);
Insert into BO.PORTFOLIO_HIST (ID_HIST,ID_CONTRAT,PORTFOLIO_ID,PORTFOLIO_NAME,PORTFOLIO_VALUE) values (4,2,1,'Portfolio 1',2000);
Insert into BO.PORTFOLIO_HIST (ID_HIST,ID_CONTRAT,PORTFOLIO_ID,PORTFOLIO_NAME,PORTFOLIO_VALUE) values (4,2,2,'Portfolio 2',8000);
commit;

Merci d'avance pour votre aide.

Seb