database - Using PL/SQL to develop relationship among two tables? -

i new pl/sql language , working on problem , looking advice right direction go. appreciated!

i have employee table , building table.
employee table consists of employee_id, last_name, first_name, , job(f/p) full or part time. building table consists of employee_id , building_location.(this location employee works from) either home or office.

i want user type in last_name user input. result looking information employee: employee name, job (full or part employee) , building_location. example, want output if user typed in johnson:

employee name     job position     building location ==================================================== andre johnson      part time        home office nick johnson       full time        downtown office 

if user types in string want list employees work company. if name on employee table 'johnson' displays johnson. need add in select clause?

at point put code checks if employee works company or not. looking build this.

accept p_1 prompt 'please enter last name of employee:'  declare v_name       employee.last_name%type := '&p_1'; v_lname      employee.last_name%type; v_fname      employee.first_name%type; v_jobpos    employee.job%type; v_buildtype  building.building_type%type; v_count      number;  begin   select    count(*)    v_count    employee   upper(last_name) = upper(v_name);    if v_count = 0         dbms_output.put_line('employee name        job position        building location');         dbms_output.put_line('----------------------------------------------------'  );         in (select * employee order last_name) loop     dbms_output.put_line(i.last_name || ', ' || i.first_name)  end loop;  else     in (select * employee order last_name) loop         dbms_output.put_line(i.last_name || ', ' || i.first_name);      end loop;   end if; end; 

you can this. simplest way might this:

    ...     if v_count = 0         dbms_output.put_line(v_name || ' not work here.');         /* new code starts here! */         dbms_output.put_line('employees are:');         r in (select * employees) loop             dbms_output.put_line(r.last_name||', '||r.first_name);         end loop;         /* new code ends here! */     else       ...     end if; end;