I\'m working within SQL, but I\'m having a problem with this one.......Here\'s w
ID: 3546218 • Letter: I
Question
I'm working within SQL, but I'm having a problem with this one.......Here's what I need to do:
I need to create the following procedures in SQL:
A procedure that accepts a job code as a parameter and lists the full name and hire date for all employees with that job code. Display a message if the job code is invalid.
A procedure that accepts a project number and a date as parameters, in that order, and displays the employee number, first name, and last name for all employees that worked on that project on that day. Print a message if no employees worked on that project on that date.
A procedure that accepts a project number as a parameter and displays the employee number and last name and number of hours worked on that project for all employees who worked on the project. Display a message if no employees worked on the project.
A procedure that accepts an employee number as a parameter and displays the names of all projects that this employee is assigned to at this time. Print a message if the employee is not assigned to any projects.
Explanation / Answer
1) create or replace procedure emp_job_code(p_job varchar2)
as
cursor c1 is
select FIRST_NAME ||' '||LAST_NAME full_name, hire_date,job_id from employees where job_id = upper(p_job);
lv_job_cnt number(9);
begin
select count(1) into lv_job_cnt from employees where job_id = upper(p_job);
if lv_job_cnt = 0 then
dbms_output.put_line('job code '||p_job|| ' is invalid');
end if;
if lv_job_cnt > 0 then
dbms_output.put_line('full_name'||','||'hire_date'||','||'job_id');
dbms_output.put_line('--------------------------------------------------------------');
end if;
for x1 in c1
loop
dbms_output.put_line(x1.full_name||','||x1.hire_date||','||x1.job_id);
end loop;
exception
when no_data_found then
dbms_output.put_line('job code is invalid');
end emp_job_code;
/
If you share the tables then i will share the remaining procedures.
And also please try to increase the points.