时间戳

首页 > 解决方案 > 如果另一个表中的数据超过一个值，如何更新表数据

sql - 如果另一个表中的数据超过一个值，如何更新表数据

问题描述

我有三张桌子：

EMPLOYEE 的示例数据：

insert into EMPLOYEE values('1011', 1000)
insert into EMPLOYEE values('1012', 1200).

WORKS_ON 的示例数据

insert into WORKS_ON values('1011',80, 60)
insert into WORKS_ON values('1012',90, 40).

PROJECT 的示例数据

insert into PROJECT values(80, A)
insert into PROJECT values(90, B).

我需要创建一个存储过程，如果工作时间超过 50 小时，则将他/她的工资设置为 10%。

这就是我所做的，我在更新时遇到了问题（也许我的整个代码都是错误的），我已经尝试了很多次但仍然遇到问题，请帮助我

CREATE OR REPLACE PROCEDURE employee_details(p_ssn IN CHAR) AS
   v_ssn employee.ssn%TYPE;
   v_sal employee.salary%TYPE;
   w_hours works_on.hours%TYPE;
BEGIN
   SELECT ssn, salary, hours
   INTO v_ssn, v_sal, w_hours
   FROM employee NATURAL JOIN works_on
   WHERE ssn = p_ssn
   AND ssn = essn;
   DBMS_OUTPUT.PUT_LINE('Employee_ssn :' || v_ssn);
   DBMS_OUTPUT.PUT_LINE('Employee_sal :' || v_sal);
   DBMS_OUTPUT.PUT_LINE('Work_hours :' || w_hours);
   IF w_hours > 60.0 THEN
   v_sal := v_sal + (v_sal * .1);
   END IF;
   UPDATE employee
   SET salary = v_sal
   WHERE ssn = essn
   AND ssn = p_ssn;
   EXCEPTION 
   WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('No data found.');
   WHEN TOO_MANY_ROWS THEN
   DBMS_OUTPUT.PUT_LINE('Many rows fetched.');
END;

错误：
17/1 PL/SQL：忽略 SQL 语句
19/13 PL/SQL：ORA-00904：“ESSN”：标识符无效

我的预期结果应该是

Employee_ssn : 1011
Employee_sal : 1100
Work_hours : 60

谢谢你！

标签： sqloracleplsqlora-01422

解决方案

您有 3 个结构性问题阻止您的代码运行：

  1. select 语句有一个谓词essn=ssn但您对 work_hours 的表描述包含列名emp_ssn。所以列essn不存在。这是您错误的直接原因：无效标识符基本上意味着列不存在。
  2. 仍然在您的选择中，您使用谓词employee natural join works_on。这将导致查询永远不会返回行。NATURAL JOIN 匹配引用表中具有相同名称的所有列。但是，引用的表中没有公共列名。
  3. 更新语句使用谓词ssn=essn。这将导致您当前遇到的相同错误，因为列/变量essn不存在。

此外，即使将 NATURAL JOIN 更正为所需的 INNER JOIN（或将 emp_ssn 重命名为 essn），您的示例数据也不会返回任何行，因为您在引用的表中没有匹配的数据值。
除了使用dbms_output显示值（用于调试、我假设的目的或部分作业）之外，没有理由使用“选择、测试和设置、更新”序列。就目前而言，即使没有重新计算薪水，您也会更新该行。但整个事情是不必要的。它可以通过单个更新语句来完成。如有必要，请执行调用例程。
您还存在代码和预期结果之间的一致性问题。您的代码查找w_hours > 60.0但您的结果和示例数据表明w_hours >= 60.0。即使你的描述说 50。
所以将程序减少到它的基本要求。（注意：提供异常消息测试了处理的行数并引发了由调用例程处理的适当错误。
最后，过程名称本身没有说明您的过程实际做什么。这在（恕我直言）中是一个非常糟糕的做法.
所以试试：

create or replace 
procedure increase_salary_for_excessive_hours(p_ssn in employee.ssn%type) as 
begin 
    update employee e 
       set salary = salary * 1.1
    where e.ssn = p_ssn
      and exists (select null
                    from works_hour w
                   where w.emp_ssn = e.ssn 
                     and w.hours >= 60
                 ); 
                 
    if sql%rowcount < 1 then 
       raise no_data_found;
    elsif sql%rowcount > 1 then 
       raise too_many_rows;
    end if;
end increase_salary_for_excessive_hours; 
/

注意：只有在employee.ssn 重复时才会引发异常too_many_rows。如果该列上有适当的唯一（或 PK）约束，则永远不会发生。该例程不会为多个符合条件的 work_hours 提高 too_many_rows ，而只会更新一次员工工资（但要注意 @astentx 的警告）。

在此处查看演示。演示包括一个生成 DBMS_OUTPUT 的测试驱动程序。DBMS_OUTPUT 适用于测试/调试，但不适用于生产环境。
这就引出了最后一点。如果任何一个异常被引发并按照当前编写的方式处理，它将编写消息，但调用例程永远不会知道它并认为一切都成功了。我建议您花一些时间来了解 EXCEPTION 部分的实际作用和含义。

推荐阅读