Как обновить данные таблицы, если данные из другой таблицы больше, чем значение

Question

У меня есть три таблицы:

• СОТРУДНИК (ССН, зарплата).
• ЧАС_РАБОТЫ (emp_ssn, pid, hours).
• ПРОЕКТ (идентификатор, имя_проекта).

Пример данных для СОТРУДНИКА:

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

Пример данных за WORKS_HOUR

      insert into WORKS_HOUR values('101',80, 60)
insert into WORKS_HOUR values('102',90, 40).

Примеры данных для ПРОЕКТА

      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;

Error:
17/1     PL/SQL: SQL Statement ignored
19/13    PL/SQL: ORA-00904: "ESSN": invalid identifier

Мой ожидаемый результат должен быть

      Employee_ssn : 1011
Employee_sal : 1100
Work_hours : 60

БЛАГОДАРЮ ВАС!

Answer 1

У вас есть 3 структурные проблемы, мешающие работе вашего кода:

1. Оператор select имеет предикат essn = ssn, но описание вашей таблицы для work_hours содержит имя столбца emp_ssn . Значит, столбца essn не существует. Это прямая причина вашей ошибки: неверный идентификатор в основном означает, что столбец не существует.
2. По-прежнему в вашем выборе вы используете предикат employee natural join works_on . Это приведет к тому, что запрос никогда не будет возвращать строки. NATURAL JOIN соответствует всем столбцам с одинаковыми именами в ссылочных таблицах. Однако в ссылочных таблицах нет общих имен столбцов.
3. Оператор обновления использует предикат ssn=essn . Это приведет к той же ошибке, которую вы получаете в настоящее время, поскольку столбец / переменная essn не существует.

Кроме того, даже исправление НАТУРАЛЬНОГО СОЕДИНЕНИЯ до необходимого ВНУТРЕННЕГО СОЕДИНЕНИЯ (или переименование 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; 
/

ПРИМЕЧАНИЕ. Исключение too_many_rows возникает только при дублировании файла employee.ssn. Никогда не произойдет, если для этого столбца существует правильное ограничение уникальности (или PK). Подпрограмма не будет увеличивать too_many_rows для нескольких подходящих work_hours, а обновит зарплату сотрудника только один раз (но обратите внимание на предупреждение @astentx).

См. Демонстрацию здесь . Демо включает тестовый драйвер, который генерирует DBMS_OUTPUT. DBMS_OUTPUT подходит для тестирования / отладки, но не в производственной среде.
Это подводит нас к заключительному пункту. Если какое-либо исключение возникает и обрабатывается так, как написано в данный момент, оно напишет сообщение, но вызывающая процедура никогда не узнает об этом и подумает, что все прошло успешно. Я предлагаю вам потратить некоторое время, чтобы понять, что на самом деле означает раздел ИСКЛЮЧЕНИЕ.

Answer 2

Написанная вами процедура использует таблицы и столбцы, которых не существует (в соответствии с тем, что вы опубликовали).

• Таблица works_on должно быть works_hour
• ты использовал essn; что это?
• несоответствие данных выборки; если SSN = 1011, то вы не можете ожидать, что он присоединится к 101, поэтому он не вызывает ORA-01422 (тег, который вы использовали) ( too_many_rows) но no_data_found.

Я попытался исправить то, что, по моему мнению, должно быть исправлено. Затем процедура компилируется.

      SQL> CREATE OR REPLACE PROCEDURE employee_details(
  2    p_ssn IN CHAR
  3  )AS
  4
  5    v_ssn    employee.ssn%TYPE;
  6    v_sal    employee.salary%TYPE;
  7    w_hours  works_hour.hours%TYPE;  -- works_hour, not works_on
  8  BEGIN
  9    SELECT ssn,
 10           salary,
 11           hours
 12    INTO
 13      v_ssn,
 14      v_sal,
 15      w_hours
 16    FROM employee
 17     JOIN works_hour on emp_ssn = ssn
 18    WHERE ssn = p_ssn;
 19    --AND ssn = v_ssn; -- essn;
 20
 21    dbms_output.put_line('Employee_ssn :' || v_ssn);
 22    dbms_output.put_line('Employee_sal :' || v_sal);
 23    dbms_output.put_line('Work_hours :' || w_hours);
 24    IF w_hours > 60.0 THEN
 25      v_sal := v_sal +(v_sal *.1);
 26    END IF;
 27
 28    UPDATE employee
 29    SET
 30      salary = v_sal
 31    WHERE ssn = v_ssn -- essn
 32          AND ssn = p_ssn;
 33
 34  EXCEPTION
 35    WHEN no_data_found THEN
 36      dbms_output.put_line('No data found.');
 37    WHEN too_many_rows THEN
 38      dbms_output.put_line('Many rows fetched.');
 39  END;
 40  /

Procedure created.

SQL>

Это даже работает сейчас:

      SQL> set serveroutput on
SQL>
SQL> exec employee_details('1011');
Employee_ssn :1011
Employee_sal :1000
Work_hours :60

PL/SQL procedure successfully completed.

SQL> select * From employee;

SSN      SALARY
---- ----------
1011       1000
1012       1200

SQL>

Но я не знаю, чего вы хотели.