sql - 我在视图中为一个 inv_id 获得多行。但理想情况下，它应该只为一个 inv_id 获取一条记录

Question

create table staging (
  key_id number(10),
  inv_id number(10),
  i_name varchar2(30),
  req_id number(10)
);

insert into staging values(110,1001,'test1',1);
insert into staging values(111,1002,'test2',2);
insert into staging values(112,1003,'test3',3);

create table target_tab (
  key_id number(10),
  inv_id number(10),
  i_name varchar2(30),
  req_id number(10)
);

审计表

create table target_audit(
  operation varchar2(50),
  entry_date timestamp,
  inv_id number(10),
  i_name varchar2(30)
);

存储过程

create or replace NONEDITIONABLE procedure sp_main_target(iv_req_id IN NUMBER)
is
  lv_count number(10);
begin
  select count(1) into lv_count
  from staging where req_id = iv_req_id;

  if lv_count > 0 then
    dbms_output.put_line('Insertion into target table');
    MERGE INTO target_tab t
    USING (
      SELECT key_id, inv_id, i_name, req_id
      FROM staging
      WHERE req_id = iv_req_id
    ) S
      ON (t.inv_id = S.inv_id)
    WHEN MATCHED THEN
      UPDATE SET 
        t.key_id = s.key_id,
        t.i_name = s.i_name,
        t.req_id = s.req_id
    WHEN NOT MATCHED THEN
      INSERT (t.key_id,t.inv_id,t.i_name,t.req_id)
        VALUES (s.key_id,s.inv_id,s.i_name,s.req_id);
    commit;
  else
    dbms_output.put_line('Request id is not available');
  end if;
  commit;
end sp_main_target;

扳机

CREATE OR REPLACE trigger target_tab_audit
BEFORE INSERT OR DELETE OR UPDATE ON target_tab
FOR EACH ROW
ENABLE
declare
  l_operation varchar2(50) :=
    case when inserting then 'I'
      when updating then 'U'
      else 'D'
    end;
BEGIN
  
  IF INSERTING THEN
    INSERT INTO target_audit (operation,entry_date, inv_id, i_name) 
      VALUES(l_operation,current_timestamp,:NEW.INV_ID, :NEW.I_NAME);  
  ELSIF DELETING THEN
    INSERT INTO target_audit (operation,entry_date, inv_id, i_name)
      VALUES(l_operation,current_timestamp,:OLD.INV_ID,
        :OLD.I_NAME);
  ELSIF UPDATING THEN
    INSERT INTO target_audit (operation,entry_date, inv_id, i_name)
      VALUES(l_operation,current_timestamp,:NEW.INV_ID,
         :NEW.I_NAME);
  END IF;
END;

看法

CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "SYSTEM"."FIRST_VIEW" ("INV_ID", "I_NAME", "ENTRY_DATE", "MODIFIED_DATE") AS 
select tt.inv_id, tt.i_name,ta.entry_date,
  case
    when ta.operation = 'U' then ta.entry_date
  end as modified_date
from target_tab tt
  join target_audit ta on (ta.inv_id = tt.inv_id);

我面临的问题：

在此视图中，我只想从审核表中获取已修改且正在更新的特定inv_id日期。inv_id但是当我选择所需的列时，我会从审计表中获取所有值。我附上了下面的截图。但我只需要正在更新的记录。如果相同的记录已更新，则该日期应出现在该视图中inv_id。你能帮忙吗？

标签： sqloracle