为存储过程定义FOREIGN KEY时出现错误，请求技术排查

核心错误点 #

• 存储过程中非法定义外键：FOREIGN KEY是数据表的约束，仅能在表定义时添加，不能放在存储过程的参数声明之后，这是直接触发语法错误的原因。
• 表定义语法错误：table10_prc表中NAME_ID列定义末尾用了分号;，但列定义未完成，应改为逗号,。
• INSERT语句字段映射错误：存储过程addnewmem4中，INSERT的目标字段是(Name, Family, NAME_ID)，但SELECT子句的顺序错误，且NAME_ID在临时表temp中不存在，导致插入逻辑失效。
• 参数类型不匹配：delete_member的入参par_id定义为VARCHAR2，但表中ID是INTEGER类型，易引发隐式转换问题。

修正后的完整代码 #

1. 修正表定义（添加外键约束到表级别） #

如果NAME_ID需要关联table10_prc的ID（自关联），直接在表定义时添加外键：

Create Table table10_prc (
    Family    VARCHAR2(200),
    Name      VARCHAR2(200),
    ID        INTEGER  NOT NULL PRIMARY KEY,
    NAME_ID    INTEGER,
    -- 添加自关联外键约束（如果需要）
    CONSTRAINT fk_name_id FOREIGN KEY (NAME_ID) REFERENCES table10_prc(ID)
);

2. 保留序列和触发器 #

CREATE SEQUENCE ID_seq3
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1;

Create or Replace trigger trg3 
  BEFORE insert on table10_prc 
  for each row 
BEGIN
  select ID_seq3.nextval INTO :new.ID from dual;
END;

3. 修正存储过程addnewmem4 #

移除非法的外键定义，调整INSERT字段映射逻辑（这里假设NAME_ID暂时赋值为NULL，可根据实际业务补充赋值规则）：

CREATE OR REPLACE PROCEDURE addnewmem4 (str IN VARCHAR2)
AS
BEGIN
  INSERT INTO table10_prc (Name, Family, NAME_ID)
  WITH temp AS (
    SELECT REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) val
    FROM DUAL
    CONNECT BY LEVEL <= REGEXP_COUNT(str, ',') + 1
  )
  SELECT 
    TRIM(SUBSTR(val, 1, INSTR(val, ';') - 1)), -- 对应Name字段
    TRIM(SUBSTR(val, INSTR(val, ';') + 1)),    -- 对应Family字段
    NULL -- 按业务需求赋值NAME_ID，比如关联已有ID或留空
  FROM temp;
  COMMIT;
END;

4. 修正delete_member参数类型 #

CREATE OR REPLACE PROCEDURE delete_member (par_id IN INTEGER)
IS
BEGIN
  DELETE FROM table10_prc
  WHERE id = par_id;
END;

5. 测试调用代码 #

BEGIN
  addnewmem4 ('faezeh;Ghanbarian,pari;izadi');
END;
/

BEGIN
  addnewmem4 ('Saeed;Izadi,Saman; Rostami');
END;
/

BEGIN
  delete_member (1);
END;
/

BEGIN
  delete_member (2);
END;
/

CALL delete_member(5);
/

CALL delete_member(6);
/

SELECT * FROM table10_prc;
/

关键说明 #

• 外键约束必须定义在数据表上，而非存储过程中。如果需要限制NAME_ID的取值范围，直接在表创建时添加FOREIGN KEY约束即可。
• 存储过程的核心是实现业务逻辑，字段的完整性约束由数据表负责维护。
• 确保INSERT语句的字段顺序与SELECT子句的返回值顺序严格匹配，避免数据插入错位。

内容的提问来源于stack exchange，提问作者Its_faezeh0308