表选择算法

Question

下面我使用简单的示例，真实的表更大，我无法将设备存储在一个表中，我无法合并 device1 和 device2 表。

我有三个表：device1、device2、computer(device3)、device1 和 device2 具有相同序列的 id，device3 是计算机。表device3可以与device1和device2连接（多对多），所以我有两个有关系的表：rel1with3和rel2with3。

CREATE SEQUENCE device_1or2;
CREATE SEQUENCE device_3;

CREATE TABLE device1 
  ( d_id NUMBER, d_data VARCHAR2(20)
  , CONSTRAINT device1_pk PRIMARY KEY (d_id));
CREATE TABLE device2 
  ( d_id NUMBER, d_data VARCHAR2(20)
  , CONSTRAINT device2_pk PRIMARY KEY (d_id));
CREATE TABLE computer
  ( d_id NUMBER, d_data VARCHAR2(20)
  , CONSTRAINT device3_pk PRIMARY KEY (d_id));

CREATE TABLE rel1with3 
  ( d_id_1 NUMBER, d_id_3 NUMBER
  , CONSTRAINT rel13_fk_1 FOREIGN KEY (d_id_1) REFERENCES device1 (d_id)
  , CONSTRAINT rel13_fk_3 FOREIGN KEY (d_id_3) REFERENCES computer (d_id)
);
CREATE UNIQUE INDEX I_REL_13 ON rel1with3 (d_id_1, d_id_3);

CREATE TABLE rel2with3 
( d_id_2 NUMBER, d_id_3 NUMBER
, CONSTRAINT rel23_fk_2 FOREIGN KEY (d_id_2) REFERENCES device2 (d_id)
, CONSTRAINT rel23_fk_3 FOREIGN KEY (d_id_3) REFERENCES computer (d_id)
);
CREATE UNIQUE INDEX I_REL_23 ON rel2with3 (d_id_2, d_id_3);

我想创建有两个参数的存储过程（device1 或 device2 作为第一个参数，计算机作为第二个参数）并添加/删除关系（在关系表中插入/删除行）。

CREATE OR REPLACE PROCEDURE Add_Relation
( COMPUTER NUMBER
, DEVICE   NUMBER
, ERR      OUT NUMBER
) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    device_data VARCHAR2(20);
BEGIN
  -- detect which device it is, insert record
  -- stored procedure, the same for both device type, BUT uses device data from additional table field
  WRITE_LOG(device, device_data, computer);
  COMMIT;
  EXCEPTION
    WHEN OTHERS THEN ERR := TO_CHAR(SQLCODE);
END;

问题是：如何更好地组织这个？使用另一个函数？使用 IF-ELSE？执行速度等如何更快？

Answer 1

我只是尝试插入 rel1with3，如果失败尝试使用 rel2with3：

CREATE OR REPLACE PROCEDURE Add_Relation(computer NUMBER, device NUMBER) IS
   device_data VARCHAR2(20);
   parent_key_not_found EXCEPTION;
   PRAGMA EXCEPTION_INIT(parent_key_not_found, -2291);
BEGIN
   BEGIN
      INSERT INTO rel1with3 (d_id_1, d_id_3) VALUES (computer, device);
      device_data := 'device1';
   EXCEPTION
      WHEN parent_key_not_found THEN
         INSERT INTO rel2with3 (d_id_2, d_id_3) VALUES (computer, device);
         device_data := 'device2';
   END;
   WRITE_LOG(device, device_data, computer);
END;

一些建议：

1. 使用 PL/SQL 异常而不是返回参数（您很容易忘记检查参数，导致难以诊断错误）
2. 不要使用自主事务，除非您需要使用它们（在这种情况下，它们会破坏您的事务流程）。
3. 仅在顶部调用过程/代码（通常是调用应用程序，而不是 PL/SQL）中使用事务处理（提交/回滚）
4. 不要忘记在 rel1with3 和 rel2with3 上对两个引用列进行索引（通常您将在 (d_id_1, d_id_3) 上进行 PK，因此您需要在 d_id_3 上添加一个索引）

Answer 2

我只使用一个序列对象并拥有一个设备表。在设备表中，我会这样设置：

CREATE TABLE device
(
   ID NUMBER NOT NULL PRIMARY KEY
 , device_name VARCHAR2(100) NOT NULL
 , device_type VARCHAR2(100) NOT NULL
 , parent_device_id NUMBER NULL
 , child_device_id NUMBER NULL
);

alter table device
  add constraint FK1 foreign key (parent_device_id)
  references device (ID);

alter table device
  add constraint FK2 foreign key (child_device_id)
  references device (ID);

对于类型 3（多对多）的设备，您将拥有parent_device_id 和 child_device_id 的值（这些将是设备 3 连接到的设备）。如果没有任何与此设备相关的设备，则对于给定设备，这些列将为空。

如果有比父/子更好的名称，您可以使用任何合适的名称。这个想法是，您只需将设备存储在一张表中，并相应地映射关系。

Parent_device_id 和 child_device_id 会将外键返回到 id 列上的设备表，这样可以确保您永远不会“孤立”任何设备记录。

您可以在插入设备记录时使用相同的序列对象生成新的 ID。