asked
Raise Salary SP
I am attempting to write a SP that will update all employee salaries that are working on a project. There are three tables involved. Employee table which has primary key SSN. Works_ON table which relates to EMPLOYEE table with ESSN=SSN. and the PROJECT table which relates to WORKS_ON table with PNO=PNUMBER I want to take two parameters. The name of the project and the raise amount. I am having an issue relating all the tables in a sp. I have just began to write more complicated stored procedures and need some guidance on how to proceed. Thanks in advance! CREATE OR REPLACE PROCEDURE RaiseSalary ( p_pname IN test_project.pname%type, p_EmpSalary IN test_employee.salary%type) AS BEGIN UPDATE test_employee set salary = salary + p_EmpSalary where ESSN = ( select w.ESSN from works_on w Join test_project p on p.pnumber=w.pno where p.pname = p.p_pname); Commit; END RaiseSalary; exec RaiseSalary('ProductZ',25);
If you're not getting a specific error, and just need help tying all the tables together, it looks like the `EMPLOYEE` table doesn't have a project name (`pname`) column. You'll have to use the project name in the filter for your subquery and use the result of the subquery (the `ESSN` column, I think) to limit the update. I don't have an instance handy to run this against, but I think something like this... UPDATE test_employee set salary = salary + p_EmpSalary where ESSN = ( select ESSN from test_project p Join works_on w on p.pnumber=w.pno where pname = p_pname); If your subquery can return more than 1 row, use `WHERE ESSN IN (select...);` instead of `WHERE ESSN = (select...)` **EDIT** Okay, I got hold of an oracle instance. Here is a working sample including table creation scripts and working stored procedure. /* create sample tables */ create table test_employee (essn int, salary int); create table test_project(pnumber int, pname varchar(25)); create table works_on (essn int, pno int); /* populate with sample data */ insert into test_employee(essn, salary) values(555005555, 10); insert into test_employee(essn, salary) values(444004444, 68); insert into test_project(pnumber, pname) values(1, 'ProductZ'); insert into test_project(pnumber, pname) values(2, 'ProductY'); insert into works_on(essn, pno) values(555005555, 1); insert into works_on(essn, pno) values(444004444, 2); /* select our employee data out to see our baseline salaries */ select essn, salary from test_employee; /* this returns the following */ /* ESSN SALARY */ /*---------- ------ */ /* 555005555 10 */ /* 444004444 68 */ /* create update stored procedure */ CREATE OR REPLACE PROCEDURE RaiseSalary ( p_pname IN test_project.pname%type, p_EmpSalary IN test_employee.salary%type) AS BEGIN UPDATE test_employee set salary = salary + p_EmpSalary where ESSN in ( select w.ESSN from works_on w Join test_project p on p.pnumber=w.pno where p.pname = p_pname); Commit; END RaiseSalary; / /* execute the procedure to increase the salary by 25 for employees on 'ProductZ' */ exec RaiseSalary('ProductZ',25); /* select our employee data out to see our updated salaries */ select essn, salary from test_employee; /* this returns the following */ /* ESSN SALARY */ /*---------- ------ */ /* 555005555 35 */ /* 444004444 68 */ The only meaningful change from the updated query in the question was to remove the `p.` from `p.p_pname` and put the / batch separator back in. The parameter variable can't be qualified with a table alias. I did use `where essn in (select...` rather than `where essn =` but the procedure works both ways as long as the subquery doesn't return more than a single row. **END EDIT**
When I add in that subquery I get the error: Invalid Identifier test_proj.pname must be declared Any ideas?
The columns are correct. The error is in line 2 of the stored procedure: p_pname in test_project.pname%type, error: Invalid Identifier test_proj.pname must be declared. It is yelling at me for not decalring the test_proj table
I updated the orginal query i wrote to show your subquery. ESSN comes from works_on table not the test_proj table. Wouldn't I still need to join works_on with test_employee table with ssn=essn? Because the employees salary that is getting updated is a column in the test_employee table
