PROSAGA码农传奇-时空索引-Oracle：触发将相关表B中的行替换为空间表A，以获取具有相同ID的记录

<div class =“post-text”itemprop =“text”>
  
    您可以使用一个过程（而不是触发器），只要向table_b添加一行，就会插入到table_b中并更新table_a。示例（分别使用Oracle 12c和11g测试）：
  
  
    Table_A - “master”，包含所有记录植物的列表，包括最新观察：
  
   <pre>
 <code>
 create table table_a (
 f_id number unique 
, dbh number 
, species varchar2(64) 
, height number(6,2) 
, family varchar2(64) 
, notes varchar2(4000) 
, t_id number
) ;

insert into table_a ( f_id, dbh, species, height, family, notes, t_id )
select 1                 as F_ID
, 10.5                   as DBH
, 'Acer rubrum'          as Species
, 25                     as Height
, 'Sapindaceae'          as Family
, 'Gifted by person xyz' as Notes
, 1000                   as T_ID from dual union all
select 2, 28.2, 'Carya illinoinensis', 39, 'Juglandaceae', 'Next to building 2', 2000 from dual union all
select 3, 26, 'Pinus virginiana', 52.5, 'Pinaceae', 'Planted by xyz for opening celebration', 3000 from dual ;

</code>
 </pre>
  
    Table_A包含......
  
   <pre>
 <code>
 SQL> select * from table_a ;
F_ID DBH SPECIES HEIGHT FAMILY NOTES T_ID 
1 10.5 Acer rubrum 25 Sapindaceae Gifted by person xyz 1000 
2 28.2 Carya illinoinensis 39 Juglandaceae Next to building 2 2000 
3 26 Pinus virginiana 52.5 Pinaceae Planted by xyz for opening celebration 3000

</code>
 </pre>
  
    表_ - “观察”（空）
  
   <pre>
 <code>
 create table table_b (
 f_id number references table_a( f_id ) 
, dbh number 
, species varchar2(64) 
, height number(6,2) 
, family varchar2(64) 
, notes varchar2(4000) 
, t_id number unique
) ;

</code>
 </pre>
  
    {1}允许我们向table_b添加记录的过程，{2}在适当的时候更新table_a。
  
  
    包装规格
  
   <pre>
 <code>
 create or replace package dendrology
is
 procedure add_record(
 f_id_ number
 , dbh_ number
 , species_ varchar2
 , height_ number
 , family_ varchar2
 , notes_ varchar2
 , t_id_ number
 );
end;
/

</code>
 </pre>
  
    包体
  
   <pre>
 <code>
 create or replace package body dendrology
is
 procedure add_record(
 f_id_ number
 , dbh_ number
 , species_ varchar2
 , height_ number
 , family_ varchar2
 , notes_ varchar2
 , t_id_ number
 ) is
 begin
 -- insert into table_b first
 insert into table_b ( f_id, dbh, species, height, family, notes, t_id )
 values ( f_id_, dbh_, species_, height_, family_, notes_, t_id_ );
 commit ;
 -- update table_A
 update table_a
 set dbh = dbh_
 , species = species_
 , height = height_
 , family = family_
 , notes = notes_
 , t_id = t_id_
 where f_id = f_id_ ;
 commit ;
 end add_record ;
end dendrology;
/

</code>
 </pre>
  
    测试程序：添加一些“观察”。
  
   <pre>
 <code>
 begin
 dendrology.add_record( 
 1, 10.5, 'Acer negundo', 25, 'Sapindaceae', 'Gifted by person xyz: misidentified', 1000 
 ) ;
 dendrology.add_record( 
 2, 31, 'Carya illinoinensis', 42, 'Juglandaceae', 'Next to building 2', 2000
 ) ;
 dendrology.add_record( 
 3, 26, 'Pinus virginiana', 52.5, 'Pinaceae', 'Planted by xyz for opening celebration', 3000
 ) ;
 dendrology.add_record( 
 2, 3, 'Carya ovata', 15, 'Juglandaceae', 'Replaced the pecan tree', 4000
 );
end;
/

</code>
 </pre>
  
    检查：TABLE_B
  
   <pre>
 <code>
 SQL> select * from table_b order by t_id ;
F_ID DBH SPECIES HEIGHT FAMILY NOTES T_ID 
1 10.5 Acer negundo 25 Sapindaceae Gifted by person xyz: misidentified 1000 
2 31 Carya illinoinensis 42 Juglandaceae Next to building 2 2000 
3 26 Pinus virginiana 52.5 Pinaceae Planted by xyz for opening celebration 3000 
2 3 Carya ovata 15 Juglandaceae Replaced the pecan tree 4000

</code>
 </pre>
  
    检查：TABLE_A
  
   <pre>
 <code>
 SQL> select * from table_a ;
F_ID DBH SPECIES HEIGHT FAMILY NOTES T_ID 
1 10.5 Acer negundo 25 Sapindaceae Gifted by person xyz: misidentified 1000 
2 3 Carya ovata 15 Juglandaceae Replaced the pecan tree 4000 
3 26 Pinus virginiana 52.5 Pinaceae Planted by xyz for opening celebration 3000

</code>
 </pre>
  
    Dbfiddle
    <a href="http://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=cbdcfb1dae18d1e1a9ad6bd27ce4a57a" rel="nofollow noreferrer">
      这里
    </A>
    。注意：我们假设在调用过程时已填充TABLE_A。
  
</DIV>