How to update a table data if a data from another table is more than a value

Question

I have three tables:

• EMPLOYEE (ssn, salary).
• WORKS_ON (essn, pid, hours).
• PROJECT (pid, pname).

Example data for EMPLOYEE:

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

Example data for WORKS_ON

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

Example data for PROJECT

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

I need to create a stored procedure and if the works hour exceeds 50 then set his/her salary by 10%.

This is what I have done, I had a problem with UPDATE(maybe my whole code is wrong) and I have tried many times but still getting issues, please help me

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

My expected result should be

Employee_ssn : 1011
Employee_sal : 1100
Work_hours : 60

THANK YOU!

Answer 1

You have 3 structural issues preventing with your code from functioning:

1. The select statement has a predicate essn=ssn but your table description for work_hours contains column name emp_ssn. So the column essn does not exist. This is the direct cause of you error: invalid identifier basically means a column does not exist.
2. Still on your select, you use the predicate employee natural join works_on. This will cause the query to never return rows. NATURAL JOIN matches on all columns with the same name in the referenced tables. However, there are no common columns names in the referenced tables.
3. The update statement uses the predicate ssn=essn. This would result in the same error you current get as the column/variable essn does not exist.

Furthere, even correcting NATURAL JOIN to the needed INNER JOIN (or renaming a emp_ssn to essn) your sample data would not return any rows as you have no matching data value in the referenced tables.
Other than using dbms_output to display values(for debug, purposes I presume or part of the homework) there is no reason for the "select, test and set, update" sequence you have. As it stands you will update the row even if salary is NOT recalculates. But the entire thing is unnecessary. It can be accomplished with a single update statement. If necessary do that the calling routine.
You also have a consistency problem between code and expected results. Your code looks for w_hours > 60.0 but your expenced results and sample data indicate w_hours >= 60.0. Even where your description says 50.
So reduce the procedure to it bare requirements. (Note: to provide the Exception Message tested the number of rows processed and raised the appropriate error to be handled by the calling routine.
Finally the procedure name itself says nothing about what your procedure actually does. This in (IMHO) a very poor practice.
So try:

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; 
/

NOTE: The exception too_many_rows will only be raised when the employee.ssn is duplicated. Will never happen if there is a proper unique (or PK) constraint on that column. The routine will not raise too_many_rows for multiple qualifying work_hours but only update the employee salary once (but heed the warning by @astentx ).

See Demo here. Demo includes a test driver that generates the DBMS_OUTPUT. DBMS_OUTPUT is fine for test/debug but not in a production environment.
Which brings up a final point. If either exception is raised and handled as currently written, it will write the message, but the calling routine would never know about it and think everything is successful. I suggest you take some time to understand whet the EXCEPTION section actually does and means.

Answer 2

Procedure you wrote uses tables and columns that don't exist (according to what you posted).

• table works_on should be works_hour
• you used essn; what is it?
• sample data mismatch; if SSN = 1011, then you can't expect it to be joined to 101, so it doesn't raise ORA-01422 (tag you used) (too_many_rows) but no_data_found.

I tried to fix what I thought that should be fixed. Then the procedure compiles.

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>

It even works now:

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>

I don't know whether that's what you wanted, though.