Oracle 插入过程和重复项的问题

亲爱的问汤姆团队!

你们帮我解决了我以前的sql问题。
现在我有一个新问题，我自己找不到解决方案。
在我的数据库中，我有以下情况:
我有一个或多个联系人的公司。对于每个联系人，它都存储在应联系的事项中 (属性 “类型”)，例如用于运输，特殊问题，营销，...。每年我们都会收到有关当前联系人的信息。这些人可以与前一年相同，也可以是一个或多个不同的人。如果数据库中的信息是特定类型的新人员，则应更新，例如，应为当前条目设置act_dat，并创建新条目。如果联系人 (例如营销) 保持不变，则数据库中不应发生任何变化。
进一步的限制是:
a) 对于运输，仅允许一个当前联系人，而对于所有其他问题/类型，可能有多个当前联系人
b) 人 “a” 可以是2012年运输的联系人，而2013年是 “b” 人。在2014，它可能再次是人，A'

我的表联系人看起来像这样:

CREATE TABLE contact (
    id            NUMBER
        CONSTRAINT nnc_con_id NOT NULL,
    firm_id      NUMBER
        CONSTRAINT nnc_con_firm_id NOT NULL,
    surname       VARCHAR2(150),
    lastname      VARCHAR2(150),
    division     VARCHAR2(150),
    phone       VARCHAR2(35),
    email         VARCHAR2(150),
    type           VARCHAR2(150)
        CONSTRAINT nnc_con_type NOT NULL,
    creat_id    NUMBER
        CONSTRAINT nnc_con_creat_id NOT NULL,
    creat_dat   TIMESTAMP WITH LOCAL TIME ZONE DEFAULT systimestamp
        CONSTRAINT nnc_con_creat_dat NOT NULL,
    act_id        NUMBER DEFAULT NULL,
    act_dat      TIMESTAMP WITH LOCAL TIME ZONE DEFAULT NULL
);

CREATE UNIQUE INDEX pk_con_ix ON
    contact (
        id
    ASC );

CREATE UNIQUE INDEX con_ix_ship ON
    contact ( decode(
        type, 'Shipping',
        firm_id, NULL),
    decode(
        type, 'Shipping', EXTRACT(YEAR FROM
        creat_dat), NULL),
   decode(
 type, 'Shipping',
 act_dat, null));

    
ALTER TABLE contact ADD CONSTRAINT pk_con PRIMARY KEY ( id );

CREATE SEQUENCE con_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER con_id_trg BEFORE
    INSERT ON contact
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := con_id_seq.nextval;
END;
/

我试图用一个程序来建模，但到目前为止没有成功。这是我最好的尝试:

create or replace PROCEDURE INSERT_CONTACT 
(
  IN_FIRM_ID IN contact.firm_id%TYPE
, IN_SURNAME IN contact.surname%TYPE
, IN_LASTNAME IN contact.lastname%TYPE 
, IN_DIVISION IN contact.division%TYPE
, IN_PHONE IN contact.phone%TYPE
, IN_EMAIL IN contact.email%TYPE 
, IN_TYPE IN contact.type%TYPE 
, IN_CREAT_ID IN contact.creat_id%TYPE 
, IN_CREAT_DAT IN contact.creat_dat%TYPE DEFAULT SYSTIMESTAMP
, IN_ACT_ID IN contact.act_id%TYPE DEFAULT NULL
, IN_ACT_DAT IN contact.act_dat%TYPE DEFAULT NULL
) AS 
BEGIN
    UPDATE contact
        SET act_id = in_creat_id, act_dat = in_creat_dat
            WHERE firm_id = IN_FIRM_ID
     AND act_dat IS NULL
            AND IN_type = 'Shipping'
            AND type = 'Shipping'
            AND ( type <> in_type
               OR surname <> in_surname
               OR lastname <> in_lastname
               OR division <> in_division
               OR phone <> in_phone
               OR email <> in_email);
    INSERT INTO contact 
            (firm_id, surname, lastname, division, phone, email, type, creat_id, creat_dat) 
            VALUES(in_firm_id, in_surname,in_lastname,in_division,in_phone,in_email,in_type,in_creat_id, in_creat_dat);
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                dbms_output.put_line('<<
所以
EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Shipping',1,to_date('10.01.2013','DD.MM.YYYY'));

应该插入数据。
EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Shipping',1,to_date('10.01.2013','DD.MM.YYYY'));

什么都不应该改变。
EXECUTE insert_contact(12,'Tom','Cat',null,null,null,'Shipping',1,to_date('11.01.2013','DD.MM.YYYY'));

应该更新当前条目 (设置act_dat) 并插入一个新条目 (因为当前只允许一个运输人员)。
EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Shipping',1,to_date('10.01.2014','DD.MM.YYYY'));

应该更新当前条目 (设置act_dat) 并插入新条目。

EXECUTE insert_contact(12,'Tom','Cat',null,null,null,'Marketing',1,to_date('10.01.2013','DD.MM.YYYY'));

应该插入一个新条目。
EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Marketing',1,to_date('10.01.2013','DD.MM.YYYY'));

应该插入一个新条目 (没有更新，因为允许有一个以上的营销当前人员)。

EXECUTE insert_contact(12,'Tina','Bird',null,null,null,'Marketing',1,to_date('11.01.2014','DD.MM.YYYY'));

应该更新当前的两个条目 (设置act_dat) 并插入一个新条目 (因为有一个新的营销人员)。



关于错误处理，我计划将其存储在表中，而不仅仅是使用dbms_output。
如果你们能给我一个如何解决我的问题的提示，我真的很感激。
非常感谢
芭芭拉
 

您的更新语句 * 始终 * 具有:

和IN_type = '装运'
和type = 'Shipping'

因此，当你通过 “营销” 时，它永远不会做任何事情。这不是这里的核心问题吗？

另外，请注意我对WHERE子句进行的更改以处理null，因为 “a <> b” 不适用于null

SQL>
SQL> CREATE TABLE contact (
  2      id            NUMBER
  3          CONSTRAINT nnc_con_id NOT NULL,
  4      firm_id      NUMBER
  5          CONSTRAINT nnc_con_firm_id NOT NULL,
  6      surname       VARCHAR2(150),
  7      lastname      VARCHAR2(150),
  8      division     VARCHAR2(150),
  9      phone       VARCHAR2(35),
 10      email         VARCHAR2(150),
 11      type           VARCHAR2(150)
 12          CONSTRAINT nnc_con_type NOT NULL,
 13      creat_id    NUMBER
 14          CONSTRAINT nnc_con_creat_id NOT NULL,
 15      creat_dat   TIMESTAMP WITH LOCAL TIME ZONE DEFAULT systimestamp
 16          CONSTRAINT nnc_con_creat_dat NOT NULL,
 17      act_id        NUMBER DEFAULT NULL,
 18      act_dat      TIMESTAMP WITH LOCAL TIME ZONE DEFAULT NULL
 19  );

Table created.

SQL>
SQL> CREATE UNIQUE INDEX pk_con_ix ON
  2      contact (
  3          id
  4      ASC );

Index created.

SQL>
SQL> CREATE UNIQUE INDEX con_ix_ship ON
  2      contact ( decode(
  3          type, 'Shipping',
  4          firm_id, NULL),
  5      decode(
  6          type, 'Shipping', EXTRACT(YEAR FROM
  7          creat_dat), NULL),
  8     decode(
  9   type, 'Shipping',
 10   act_dat, null));

Index created.

SQL>
SQL>
SQL> ALTER TABLE contact ADD CONSTRAINT pk_con PRIMARY KEY ( id );

Table altered.

SQL>
SQL> CREATE SEQUENCE con_id_seq START WITH 1 NOCACHE ORDER;

Sequence created.

SQL>
SQL> CREATE OR REPLACE TRIGGER con_id_trg BEFORE
  2      INSERT ON contact
  3      FOR EACH ROW
  4      WHEN ( new.id IS NULL )
  5  BEGIN
  6      :new.id := con_id_seq.nextval;
  7  END;
  8  /

Trigger created.

SQL>
SQL>
SQL> create or replace PROCEDURE INSERT_CONTACT
  2  (
  3    IN_FIRM_ID IN contact.firm_id%TYPE
  4  , IN_SURNAME IN contact.surname%TYPE
  5  , IN_LASTNAME IN contact.lastname%TYPE
  6  , IN_DIVISION IN contact.division%TYPE
  7  , IN_PHONE IN contact.phone%TYPE
  8  , IN_EMAIL IN contact.email%TYPE
  9  , IN_TYPE IN contact.type%TYPE
 10  , IN_CREAT_ID IN contact.creat_id%TYPE
 11  , IN_CREAT_DAT IN contact.creat_dat%TYPE DEFAULT SYSTIMESTAMP
 12  , IN_ACT_ID IN contact.act_id%TYPE DEFAULT NULL
 13  , IN_ACT_DAT IN contact.act_dat%TYPE DEFAULT NULL
 14  ) AS
 15  BEGIN
 16      UPDATE contact
 17          SET act_id = in_creat_id, act_dat = in_creat_dat
 18              WHERE firm_id = IN_FIRM_ID
 19       AND act_dat IS NULL
 20  和IN_type = '装运'
 21  和type = 'Shipping'
 22              AND ( decode(type,in_type,0,1) = 1
 23                 OR decode(surname,in_surname,0,1) = 1
 24                 OR decode(lastname,in_lastname,0,1) = 1
 25                 OR decode(division,in_division,0,1) = 1
 26                 OR decode(phone,in_phone,0,1) = 1
 27                 OR decode(email,in_email,0,1) = 1
 28                 );
 29         dbms_output.put_line('updated: '||sql%rowcount);
 30      INSERT INTO contact
 31              (firm_id, surname, lastname, division, phone, email, type, creat_id, creat_dat)
 32              VALUES(in_firm_id, in_surname,in_lastname,in_division,in_phone,in_email,in_type,in_creat_id, in_creat_dat);
 33              dbms_output.put_line('inserted 1');
 34          EXCEPTION
 35              WHEN DUP_VAL_ON_INDEX THEN
 36                  dbms_output.put_line('insert not done');
 37  END INSERT_CONTACT;
 38  /

Procedure created.

SQL> set serverout on
SQL>
SQL> --should insert the data.
SQL> EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Shipping',1,to_date('10.01.2013','DD.MM.YYYY'));
updated: 0
inserted 1

PL/SQL procedure successfully completed.

SQL>
SQL> --should change nothing.
SQL> EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Shipping',1,to_date('10.01.2013','DD.MM.YYYY'));
updated: 0
insert not done

PL/SQL procedure successfully completed.

SQL>
SQL> --should update the current entry (setting the act_dat) and insert a new entry (since there is only one current shipping person allowed).
SQL> EXECUTE insert_contact(12,'Tom','Cat',null,null,null,'Shipping',1,to_date('11.01.2013','DD.MM.YYYY'));
updated: 1
inserted 1

PL/SQL procedure successfully completed.

SQL>
SQL> --should update the current entry (setting the act_dat) and insert a new entry.
SQL> EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Shipping',1,to_date('10.01.2014','DD.MM.YYYY'));
updated: 1
inserted 1

PL/SQL procedure successfully completed.

SQL>
SQL> --should insert a new entry.
SQL> EXECUTE insert_contact(12,'Tom','Cat',null,null,null,'Marketing',1,to_date('10.01.2013','DD.MM.YYYY'));
updated: 0
inserted 1

PL/SQL procedure successfully completed.

SQL>
SQL> --should insert a new entry (no update, since there is more than one marketing current person allowed).
SQL> EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Marketing',1,to_date('10.01.2013','DD.MM.YYYY'));
updated: 0
inserted 1

PL/SQL procedure successfully completed.

SQL>
SQL> select * from contact
  2  @pr
==============================
ID                            : 1
FIRM_ID                       : 12
SURNAME                       : Maria
LASTNAME                      : Mouse
DIVISION                      :
PHONE                         :
EMAIL                         :
TYPE                          : Shipping
CREAT_ID                      : 1
CREAT_DAT                     : 10-JAN-13 12.00.00.000000 AM
ACT_ID                        : 1
ACT_DAT                       : 11-JAN-13 12.00.00.000000 AM
==============================
ID                            : 3
FIRM_ID                       : 12
SURNAME                       : Tom
LASTNAME                      : Cat
DIVISION                      :
PHONE                         :
EMAIL                         :
TYPE                          : Shipping
CREAT_ID                      : 1
CREAT_DAT                     : 11-JAN-13 12.00.00.000000 AM
ACT_ID                        : 1
ACT_DAT                       : 10-JAN-14 12.00.00.000000 AM
==============================
ID                            : 4
FIRM_ID                       : 12
SURNAME                       : Maria
LASTNAME                      : Mouse
DIVISION                      :
PHONE                         :
EMAIL                         :
TYPE                          : Shipping
CREAT_ID                      : 1
CREAT_DAT                     : 10-JAN-14 12.00.00.000000 AM
ACT_ID                        :
ACT_DAT                       :
==============================
ID                            : 5
FIRM_ID                       : 12
SURNAME                       : Tom
LASTNAME                      : Cat
DIVISION                      :
PHONE                         :
EMAIL                         :
TYPE                          : Marketing
CREAT_ID                      : 1
CREAT_DAT                     : 10-JAN-13 12.00.00.000000 AM
ACT_ID                        :
ACT_DAT                       :
==============================
ID                            : 6
FIRM_ID                       : 12
SURNAME                       : Maria
LASTNAME                      : Mouse
DIVISION                      :
PHONE                         :
EMAIL                         :
TYPE                          : Marketing
CREAT_ID                      : 1
CREAT_DAT                     : 10-JAN-13 12.00.00.000000 AM
ACT_ID                        :
ACT_DAT                       :

PL/SQL procedure successfully completed.

SQL>
SQL> --should update the current two entries (setting the act_dat) and insert a new entry (since there is a new marketing person)
SQL> EXECUTE insert_contact(12,'Tina','Bird',null,null,null,'Marketing',1,to_date('11.01.2014','DD.MM.YYYY'));
updated: 0
inserted 1

PL/SQL procedure successfully completed.

SQL>
SQL>