sql - Oracle - 插入存储过程外键

Question

说明:

创建两个表，分别命名为employees 和departments。在表格名称前加上您的姓名首字母。通过名为 dept 的列链接两个表(外键)。为每个表编几个列名。

员工表:

create table bsemployees(
dept        number primary key,
empName     varchar2(20),
salary      number
);

部门表:

create table bsdepartments(
dept        number references bsemployees(dept),
deptName    varchar2(20)
);

编写如下存储过程:

• 在employees 表中插入一行。如果部门不存在。将其插入到departments 表中。

create or replace procedure sp_employees(
    a_dept IN number, 
    a_empName IN varchar2, 
    a_salary IN number
)
as
    vCount  number;
BEGIN   
    sp_check_dept(a_dept,vCount);

    insert into bsemployees values(a_dept, a_empName, a_salary);

    if vCount = 0 then
        dbms_output.put_line('**DEPT DOES NOT EXIST**');
        insert into bsdepartments (dept, deptName) values(a_dept, NULL);
    end if;
END;
/

create or replace procedure sp_check_dept(
    a_dept IN number,
    vCount OUT number
)
as
BEGIN
    select count(*)
    into vCount
    from bsdepartments
    where dept = a_dept;
end;
/

• 在部门表中插入一行。

create or replace procedure sp_departments(
    a_dept IN number, 
    a_deptName IN varchar2
)
as
BEGIN
    insert into bsdepartments values(a_dept, a_deptName);
END;
/

除了当我尝试向 departments 表中插入一行时，我得到了一个完整性约束 - 找不到父键错误。

如果我执行 execute sp_employees(5, 'John Doe', 90000); 它将显示 ***DEPT DOES NOT EXIST*** 并继续并将数据插入 bsemployees 并将 dept# 插入 bsdepartments 并且 deptName 将留空基于我的 if-then 语句。执行 select(*) 会向我展示这一点。

但是，如果我继续执行 execute sp_departments(1, 'human resources'); 将行放入 departments 我得到父键错误。我知道我正在尝试插入没有父键的内容，但我不知道如何修复它。

Answer 1

您的表设计不太正确 - dept 主键需要作为外键添加到 employee(而不是主键)，而 employee 应该有自己的主键:

create table bsdepartments(
  dept        number primary key,
  deptName    varchar2(20)
);


create table bsemployees(
  empName     varchar2(20) primary key,
  dept        number references bsdepartments(dept),
  salary      number
);

然后您可以在 check_dept 过程中执行“如果不存在则添加”逻辑:

create or replace procedure sp_check_dept(
    a_dept IN number
)
as
   vCount number
BEGIN
    select count(*)
        into vCount
        from bsdepartments
        where dept = a_dept;

    if (vCount = 0) then
        dbms_output.put_line('**DEPT DOES NOT EXIST**');
        insert into bsdepartments (dept, deptName) values(a_dept, NULL);
    end if;
end;

然后简化了员工插入过程，因为它应该由一个部门保证:

create or replace procedure sp_insertEmployee(
    a_dept IN number, 
    a_empName IN varchar2, 
    a_salary IN number
)
as
BEGIN   
    sp_check_dept(a_dept);

    insert into bsemployees values(a_dept, a_empName, a_salary);
END

注释

• 建议您根据其用途命名这些过程，例如insertEmployee 与 employees
• 正如您所指出的，“如果不存在则添加”方法的问题是您没有足够的数据来完全填充 department 表，因此空列(但这是你的讲师要求什么)