PL/SQL- Updating salary by using Procedure and Cursor

Question

I have to create a procedure that will raise employees salaries. First, it asks user to enter employee_id. If his country is United States of America and job title is Administration Assistant or Stock Manager , then raise salary by 20%. Otherwise rollback.

I have a problem with creating a procedure. I don't know what to do next. Now there is an ERROR: 25/5 PL/SQL: Statement ignored 25/112 PLS-00382: expression is of wrong type Could you help me?

This is my code:

CREATE OR REPLACE PROCEDURE raise_emp_salaries (
p_emp_id IN NUMBER) AS

CURSOR c_emp IS
SELECT
    countries.country_name,
    jobs.job_title,
    employees.employee_id,
    employees.salary
FROM
    countries,
    jobs,
    employees;

rec_emp   c_emp%rowtype;
new_sal   NUMBER(12, 2);

BEGIN OPEN c_emp; 
SAVEPOINT update_no;
LOOP
FETCH c_emp INTO rec_emp;
EXIT WHEN c_emp%notfound;

IF rec_emp.country_name = 'United States of America'  --25/5      PL/SQL: Statement ignored 25/112 PLS-00382: expression is of wrong type
AND rec_emp.job_title = 'Administration Assistant' 
OR 'Stock Manager' THEN
    new_sal := rec_emp.salary * 1.20;
END IF;

UPDATE employees
SET
    employees.salary = new_sal
WHERE
    employees.employee_id = p_emp_id;
COMMIT;

end loop;

 EXCEPTION
WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.PUT_LINE('NO RECORDS');
     ROLLBACK TO update_no;
WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('FOUND SOME KIND OF ERROR');
     ROLLBACK TO update_no;
 
close c_emp;
end;

Answer 1

your and/or is incorrect. change to this:

IF rec_emp.country_name = 'United States of America'
AND rec_emp.job_title IN ('Administration Assistant','Stock Manager') THEN

IN is basically an OR but easier to read. But you have a logic problem also. You SELECT statement is using three tables, countries,jobs, employees, and there is no JOIN between any of them. That can't be correct, as that will result in the CROSS PRODUCT of all the rows from all three tables, probably not what you want. Since you don't show the DLL for the three tables, IDK the relationship between them, so you will need to figure out the JOINS.

Also, it is normally a better practice to NOT do commit/rollback in the procedure, and let the caller of the procedure determine that. What if the caller was executing addtional DML in the session? Now your procedure has the side-effect of committing those changes also. You should either return an error code/message from the procedure or create your own exceptions and raise those.

More - it is also bad practice to swallow an OTHERS exception and not provide the details. Also, you will never encounter a NO_DATA_FOUND exception with the way your cursor is implemented.