基于历史表创建视图

Question

基于历史表创建视图

5

我想基于包含历史表的数据库创建SQL视图。

在无法更新数据库表的情况下，您的最佳解决方案（快速而有效）是什么。我只能创建视图。

这是背景信息：

我的应用程序管理合同。合同具有一般信息，并与联系人，法律引用和组合相关联。当更新完成时，在历史记录表中添加新行（新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;

- coeurdange57

2

你能把这个做成一个fiddle吗？ - Thomas Tschernich

你好，coeurdange57，我的解决方案怎么样了？有帮到你吗？ - PT_STAR

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- PT_STAR · Accepted Answer

首先：创建一个存储函数（或在包中创建一个函数），如下所示：

create or replace function test_history(i_contract_id  in number,
                                        i_date_created in date,
                                        i_type         in varchar2)
  return varchar2 is
  l_sql    varchar2(1000);
  l_result number;
begin
  l_sql := 'select 1 from test_history_tb where id_contract = :1 and date_creation = :2 and ' ||
           i_type || ' = :3 and rownum = 1';
  execute immediate l_sql
    into l_result
    using i_contract_id, i_date_created, 'update';

  return('update');

exception
  when no_data_found then
    return('no_update');
end;

第二步：根据函数创建查询。

create view xxx as

select id_contract, date_creation,
       test_history(a.id_contract, a.date_creation, 'general_info') general_info,
       test_history(a.id_contract, a.date_creation, 'contract') contract,
       test_history(a.id_contract, a.date_creation, 'legal') legal,
       test_history(a.id_contract, a.date_creation, 'portfolio') portfolio
from test_history_tb a
group by a.id_contract, a.date_creation;

这个解决方案不是很快，因为对于每一行，我们都需要调用一个函数。但是如果你使用where子句过滤数据，它会是一种替代方法。

表格中的示例数据:

... 这是视图查询的样子: