sql - Table choosing algorithm -

Below I am using simple examples, real tables are large, I can not store devices in a table, I can not merge the device 1 and the device table 2.

I have three tables: ID is the same sequence in device 1, device 2, computer (device 3), device 1 and device 2, device 3 is computer table device 3 device 1 and device 2 (Multiple-to-many), so I have two tables with relationships: rel1with3 and rel2with3.

  sequence device device or two; Make SEQUENCE device_3; Create table device 1 (d_id number, d_data VARCHAR2 (20), contract device 1_pk primary key (d_id)); Create Table Device 2 (d_id number, d_data VARCHAR2 (20), contract device 2_pk primary key (d_id)); Create a table computer (d_id number, d_data VARCHAR2 (20), contract device 3_pk primary key (d_id)); Tablet Relay 1 with 3 (d_id_1 NUMBER, d_id_3 NUMBER, Contract Relay 13_fk_1 Foreign Key (d_id_1) Reference Device 1 (DIID), Contract Relay 13_ Key of the Foxy Force (D_ID3) reference computer (DIID)); Create Relay 1 with 3 (d_id_1, d_id_3) on the unique index I_REL_13; Tablet relay 2 with 3 (D_ID_2 number, d_rd number NUMBER, contract relay 23_fk_2 foreign key (d_id_2) reference device 2 (DIID), contract relay 23_fac forensy (D_ID3) reference computer (DIID)); Create Relay 2 with 3 (d_id_2, d_id_3) on unique index I_REL_23;  

I want to create an archived methodology that has two parameters (device 1 or device 2 as the first parameter, second in the form of a computer) and insert the line in the relation / relation table / Delete

  Add or Add Process Add_Relation (Computer Number, Device Number, Number Exit) Pargama Autonomos_rector; Device_DataVARCHAR2 (20); BEGIN - Detects which device is, enter a record - the stored procedure is the same for both device types, but the additional table field uses device data from the WRITE_LOG (device, device_data, computer); COMMIT; Exceptions while others error: = TO_CHAR (SQLCODE); End;  

The question is: how to organize it better? Using another function? Using IF-ELSE? How fast is executed etc?

I just rel1with3 , if this fails then Try with rel2with3 :

  or change process Add_Relation (computer number, device NUMBER) is device_data VARCHAR2 (20); Parent_key_not_found exception; Pragama EXCEPTION_INIT (parent_key_net_found, -2291); BEGIN BEGIN1 rel1with3 (d_id_1, d_id_3) include in prices (computers, devices); Device_data: = 'device1'; Exception when parent_key_not_found is re-rel2with3 (d_id_2, d_id_3) values ​​(computer, device); Device_data: = 'device2'; End; WRITE_LOG (device, device_data, computer); End;  

Some suggestions:

1. Use the PL / SQL exception instead of the return parameter (which you can easily forget to check the parameters Do not use an autonomous transaction unless you need to use them (in this case they will break your transaction flow).
2. Do not use an autonomous transaction.
3. Use transaction processes (commit / rollback) only in the top calling process / code (usually calling app, not PL / SQL)
4. rel1with3 And do not forget to index both referenced columns at rel2with3 (Typically you will have a pk at (d_id_1, d_id_3) so that you get to d_id_3 But an additional index will be needed)