我想基于包含历史表的数据库创建SQL视图。
在无法更新数据库表的情况下,您的最佳解决方案(快速而有效)是什么。我只能创建视图。
这是背景信息:
我的应用程序管理合同。合同具有一般信息,并与联系人,法律引用和组合相关联。当更新完成时,在历史记录表中添加新行(新ID_HIST)。如果对联系人,法律引用或组合进行更新,则在contract_HIST表中也会添加新行(具有相同的ID_HIST)。
我的目的是创建一个视图,以显示对行(ID HIST)进行的更新与先前ID HIST相比如何: 因此,对于每个新的更新(对于特定日期的要求,由ID HIST给出),我们可以看到一般信息、联系人、法律参考或/和组合是否已更新。
以下是数据库的结构: 在此表格中,一个或多个投资组合可以被指定给同一更新的合同。
信息提示:例如,在新的更新过程中,如果已删除合同的联系人,则为该合同添加了一行新的ID_HIST的contract_hist,但不会在contact_hist表中添加新行。法律参考和投资组合也是如此。
这里的视图应该显示:
在无法更新数据库表的情况下,您的最佳解决方案(快速而有效)是什么。我只能创建视图。
这是背景信息:
我的应用程序管理合同。合同具有一般信息,并与联系人,法律引用和组合相关联。当更新完成时,在历史记录表中添加新行(新ID_HIST)。如果对联系人,法律引用或组合进行更新,则在contract_HIST表中也会添加新行(具有相同的ID_HIST)。
我的目的是创建一个视图,以显示对行(ID HIST)进行的更新与先前ID HIST相比如何: 因此,对于每个新的更新(对于特定日期的要求,由ID HIST给出),我们可以看到一般信息、联系人、法律参考或/和组合是否已更新。
以下是数据库的结构: 在此表格中,一个或多个投资组合可以被指定给同一更新的合同。
信息提示:例如,在新的更新过程中,如果已删除合同的联系人,则为该合同添加了一行新的ID_HIST的contract_hist,但不会在contact_hist表中添加新行。法律参考和投资组合也是如此。
这里的视图应该显示:
这里是用于测试的数据库脚本:
--------------------------------------------------------
-- 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 (2,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;