PL/SQL • Create EmpPackage and the spSelectEmployeeById Stored Procedure

Listing 1. The stored procedure receives the employee number and displays the employee's name, job, and hire date based on the key columns of the referenced parent tables.

Create or replace package EmpPackage is

procedure spSelectEmployeeById 
	(v_empno in emp.empno%type, v_ename out 
	emp.ename%type, v_job out emp.job%type, 
	v_hiredate out emp.hiredate%type);

end EmpPackage;
/

create or replace package body EmpPackage is

procedure spSelectEmployeeById 
	(v_empno in emp.empno%type, v_ename out 
	emp.ename%type, v_job out emp.job%type, 
	v_hiredate out emp.hiredate%type)
is
begin
	select ename, job, hiredate
	into v_ename, v_job, v_hiredate
	from emp
	where empno=v_empno;
end;

end EmpPackage;
/