what's wrong with this code? 2

[slok]

CREATE OR REPLACE PACKAGE employee_pkg AS

PROCEDURE New_Employee (
p_empname IN employee.emp_name%type,
p_salary IN employee.emp_salary%type,
p_sdate IN employee.emp_sdate%type,
p_deptno IN department.dept_no%type);


PROCEDURE Del_Employee (
p_empname IN employee.emp_name%type);


END employee_pkg;
/
show errors;

CREATE OR REPLACE PACKAGE BODY employee_pkg
AS
PROCEDURE New_Employee (
p_empname IN employee.emp_name%type,
p_salary IN employee.emp_salary%type,
p_sdate IN employee.emp_sdate%type,
p_deptno IN department.dept_no%type)
IS

Dept_Count number;
INVALID_DEPARTMENT exception;

BEGIN

select count(*) into Dept_Count
from department where dept_no = p_deptno;

if Dept_Count = 1 then

insert into EMPLOYEE values
( employeeNo.NextVal,
p_empname,
p_salary,
p_sdate,
p_deptno);

update department set dept_empcnt = dept_empcnt + 1 where dept_no = p_deptno;

commit;

else
raise INVALID_DEPARTMENT;
end if;

exception
when INVALID_DEPARTMENT then
raise_application_error(-20001, 'Invalid Department - INSERT UNSUCCESSFUL');



END New_Employee;


PROCEDURE DEL_EMPLOYEE(p_empname IN employee.emp_name%type)
IS
INVALID_DEPARTMENT exception;
INVALID_EMPLOYEE exception;
EMPCOUNT number;
v_deptNo department.dept_no%type;
BEGIN
select count(empname) INTO EMPCOUNT, dept_no INTO v_deptNo from employee
where empname = p_empname;

if EMPCOUNT > 0 then
delete from employee where empname = p_empname;
update department set dept_empcnt = dept_empcnt -1 where dept_no = v_deptNo;
commit;
else
raise INVALID_EMPLOYEE;
end if;

EXCEPTION
when INVALID_EMPLOYEE then
raise_application_error(-20001, 'Invalid Employee - DELETE UNSUCCESSFUL');

End Del_Employee;

END employee_pkg;
/

show errors;

 

[lewisp]

It would help if you told us what error(s) you are getting.

 

[slok]

I got the following message when I run it in sqlPlus.

================================

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY EMPLOYEE_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
51/47 PLS-00103: Encountered the symbol "INTO" when expecting one of
the following:
. ( , % from
The symbol ". was inserted before "INTO" to continue.

 

[Hehenka]

I think that this is wrong:

select count(empname) INTO EMPCOUNT, dept_no INTO v_deptNo from employee where empname = p_empname;

It would be:

select count(empname),dept_no
INTO EMPCOUNT, v_deptNo
from employee where empname = p_empname;

Helena

 

[EdWall]

This will not quite work because the syntax is not exactly correct. You don't really want a COUNT here, you want the department no and to know if the employee cannot be found.

I suggest you do it like this:

BEGIN
select dept_no
INTO v_deptNo
from employee where empname = p_empname
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE INVALID_EMPLOYEE
END

Ed