CODEWITHSUNDEEP
oracle-databaseplsql
kamalesh
kamalesh

Reputation: 11

Count the no of records

Write a program that gives all employees in Mechanical department,

(i) 15% pay increase.

(ii) display a message displaying how many employees were awarded the increase. if no Employees found then print the message 'No Records found'.

Employee :

columnname  Data type     constraints

EMPID        NUMBER(5)      PK    
EMP_NAME     VARCHAR(25)    NOT NULL    
SALARY       NUMBER(10,2)      
DEPT         VARCHAR(25)

EMP_ID  EMP_NAME  SALARY  DEPT 
101     TOM       54000   MECH    
102     WILLIAM   43000   CSE    
103     JOHN      34560   MECH    
104     SMITH     56000   CSE    
105     STEVE     23450   IT

Sample Output : 2 Employee got increment.

and i did like this,

create or replace procedure empsal as 
emp employee%rowtype;
sal number ;

cursor cr is select * from employees where dept='mech';

begin
  open cr;
  loop
    fetch cr into emp;
    exit when ce%notfound;

    sal:=emp.salary+(emp.salary*15/100);

    update employee set salary=sal where dept='mech';
  end loop;
  close cr;

  if(sql%found) then
    dbms_output.put_line(sql%rowcount);
  else
    dbms_output.put_line('no records found');
  end;

but its shows the COMPILATION ERROR

Upvotes: 0

Views: 818

Answers (1)

Littlefoot
Littlefoot

Reputation: 143123

No need to do make it that complex.

SQL> set serveroutput on
SQL> create or replace procedure empsal as
  2  begin
  3    update employee set
  4      salary = salary * 1.15
  5      where dept = 'MECH';
  6
  7    dbms_output.put_line(case when sql%rowcount = 0 then 'No records found'
  8                              else sql%rowcount || ' employees got increment'
  9                         end);
 10  end;
 11  /

Procedure created.

SQL> exec empsal;
2 employees got increment

PL/SQL procedure successfully completed.

SQL>

As of your compilation errors:

  • if table name is employee, don't use employees (while declaring a cursor)
  • if cursor name is cr, don't use ce for it (exit statement)
  • if misses end if

As of logical errors:

  • if department name is MECH, don't reference it as mech (letter case matters)
  • doing it in a loop - and without where clause in update statement - you're increasing salary for everyone as many times as there are employees in the MECH department.
  • in if, you're referencing a cursor that is already closed so ... no use of it

Upvotes: 2

Related Questions