oracle - 我是 PL/SQL 的新手。有人可以帮助我了解这个 PL/SQL 过程吗？

Question

编写一个 PL/SQL 过程，将员工编号和薪水作为输入参数，并从经理为 'BLAKE' 且薪水在 1000 到 2000 之间的员工表中删除。

我写了下面的代码:-

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;

获得以下错误:-

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

Answer 1

输入参数不能在函数或过程中赋值，并且您的输入参数也不用作输入，因此将它们声明为局部变量并使用它们。

 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;

将该过程称为

begin
 processing;
end;

编辑 1 :- 如果您需要删除 emp_no 和工资的输出
在 emp1 中使用 out 参数。

 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;

称他们为

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;