Oracle – I am new to PL/SQL.Can some one help me in this PL/SQL procedure

oracleplsqlstored-procedures

Write a PL/SQL procedure which takes employee number and salary as input parameters and delete from employees table whose manager is 'BLAKE' and salary between 1000 and 2000.

I have written below code :-

create  or replace procedure processing(v_emp_no in emp1.empno%type,v_salary in emp1.sal%type)
is 
begin

select empno,sal into v_emp_no,v_salary
from emp where ename = 'BLAKE' and sal between 1000 and 2000;
delete from emp1
where empno  = v_emp_no
and sal = v_salary; 
end;

Getting below error :-

Error at line 5: PLS-00403: expression 'V_EMP_NO' cannot be used as an INTO-target of a SELECT/FETCH statement

Best Solution

The input parameters cannot be assigned value in function or procedure and also your input parameters are not used as input hence declare them as local variables and use them.

 create  or replace procedure processing
 is 
  l_emp_no emp1.empno%type;
  l_salary  emp1.sal%type;
 begin
  select empno,sal into l_emp_no,l_salary
  from emp where ename = 'BLAKE' and sal between 1000 and 2000;
  delete from emp1
  where empno  = l_emp_no
  and sal = l_salary; 
 end;

call the procedure as

begin
 processing;
end;

Edit1:- if you need to get output of emp_no and salary deleted in emp1 use the out parameter.

 create  or replace procedure processing
 (v_emp_no OUT emp1.empno%type,v_salary OUT emp1.sal%type)
 is 
  l_emp_no emp1.empno%type;
  l_salary  emp1.sal%type;
 begin
  select empno,sal into l_emp_no,l_salary
  from emp where ename = 'BLAKE' and sal between 1000 and 2000;
  delete from emp1
  where empno  = l_emp_no
  and sal = l_salary; 

  v_emp_no:=l_emp_no;
  v_salary:=l_salary;
 end;

Call them as

declare
p_emp_no emp1.empno%type;
p_salary emp1.sal%type;
begin
 processing(p_emp_no,p_salary);
 dbms_output.put_line('the employee deleted in emp1 is '||p_emp_no);
 dbms_output.put_line('the employee salary is '||p_salary);
end;
Related Question