CODEWITHSUNDEEP
plsql
Emils Legzdins
Emils Legzdins

Reputation: 37

Procedure created with compilation errors, no idea what is going on

create or replace procedure d_name_legzdins (id in dept.deptno%type) as d_name_legzdins varchar(20);

BEGIN

SELECT dname into d_count_legzdins FROM dept WHERE deptno=id;

if (SELECT count(empno) from emp where deptno=id)=0 THEN

raise_application_error(-20101, 'There are no employees currently working at this department!');

else

DBMS_OUTPUT.PUT_LINE('There are currently '|| (SELECT count(empno) from emp where deptno=id) ||' employees working at '|| d_count_legzdins ||'department');

END IF;

exception

  when no_data_found then

    DBMS_OUTPUT.PUT_LINE('There is no such department!');

end;'

i just started the subject in uni and cant get this to work no mater what i do

for reference

the table dept contains deptno, which is the id of the derpartment and dname which is the name

and emp contains the empno, whcih is the employee name and deptno is the same dept id and connected

Upvotes: 0

Views: 52

Answers (2)

Belayer
Belayer

Reputation: 14861

As pointed out you have some serious syntax errors. Cleaning those is obviously your first task. However IMHO the layout of your procedure is somewhat choppy. You have a structure as "Good Process then Error Process then Good process then Error process". I find a much cleaner process to be "Good Process then all Exception processing" (admittedly sometimes not possible). In this case both missing Dept and No Emp in the department are Exceptions. So process both in the EXCEPTION section. Oracle permits nested blocks and each block can have an exception section: So something like (see fiddle]1).

create or replace procedure d_name_legzdins (id in dept.deptno%type) 
as 
  v_name_legzdins varchar(20);
  v_emp_cnt number;
  
begin

   select d.dname, count(*) 
     into v_name_legzdins,v_emp_cnt 
     from dept d 
     join emp  e on (e.deptno = d.deptno)
    where d.deptno = id
    group by d.dname; 
 
   dbms_output.put_line('There are currently '|| v_emp_cnt || 
                        ' employees working at '|| v_name_legzdins ||' department'
                        ); 
                        
exception
  when no_data_found then 
    -- at this point we know that either the parameter for department (id) is invalid 
    -- or that it is valid but has no employees. Now discover which.
       begin 
           select dname
             into v_name_legzdins    
            from dept 
            where deptno =  id
              and rownum <= 1; 
           -- the department exists, so it does not have any employees.
           raise_application_error(-20101, 'There are no employees currently working at this department!');

    exception
      when no_data_found then
           dbms_output.put_line('There is no such department!');
    end ; -- inner exception    
end d_name_legzdins;

I do not understand why you just want a message for missing deportment but a user defined exception for no employees but that inconsistency is your choice.

Upvotes: 0

Vadim Artyushenko
Vadim Artyushenko

Reputation: 28

Emils, you have some serious syntax errors in your code. Quick reference guide to PL/SQL syntax and semantics can be viewed in Oracle Docs. Your code can be changed like this:

create or replace procedure d_name_legzdins (id in dept.deptno%type) 
as 
  v_name_legzdins varchar(20);
  v_emp_cnt number;
begin
 select dname 
 into v_name_legzdins 
 from dept 
 where deptno=id;
 
 begin
   select count(empno)
   into v_emp_cnt
   from  emp where 
   deptno=id;
 exception
   when no_data_found then
     raise_application_error(-20101, 'There are no employees currently working at this department!');
 end;
 
 DBMS_OUTPUT.PUT_LINE('There are currently '|| v_emp_cnt ||' employees working at '|| v_name_legzdins ||' department'); 

exception
  when no_data_found then
    DBMS_OUTPUT.PUT_LINE('There is no such department!');
end;

Upvotes: 1

Related Questions