如何修复标识符必须在存储过程中声明？

I'm creating a procedure and then execute it in SQL plus. The procedure is correctly created / updated, as you can see from the following code.

我正在创建一个过程,然后在SQL plus中执行它。正如您可以从以下代码中看到的那样,正确创建/更新了该过程。

SQL> create or replace procedure add_sal(i_empno number, addsal out number)
 2  is
 3  begin
 4  select sal+1000
 5  into addsal
 6  from emp
 7  where empno=i_empno;
 8  end;
 9  /

Procedure created.

When I try to execute the procedure I'm getting an error that I don't understand.

当我尝试执行该过程时,我收到一个我不理解的错误。

SQL> exec add_sal(i_empno,addsal);
BEGIN add_sal(i_empno,addsal); END;
              *
ERROR at line 1:
ORA-06550: line 1, column 15:
PLS-00201: identifier 'I_EMPNO' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Where am I going wrong?

我哪里错了?

2 个解决方案

#1

SQL> exec add_sal(i_empno,addsal);

SQL> exec add_sal(i_empno,addsal);

You need to pass the values, not the parameter names itself.

您需要传递值,而不是参数名称本身。

Also, addsal is an OUT parameter, you need to first declare it.

另外,addsal是一个OUT参数,您需要先声明它。

In SQL*Plus:

SQL> CREATE OR REPLACE PROCEDURE add_sal(
  2      i_empno NUMBER,
  3      addsal OUT NUMBER)
  4  IS
  5  BEGIN
  6    SELECT sal+1000 INTO addsal FROM emp WHERE empno=i_empno;
  7  END;
  8  /

Procedure created.

SQL>
SQL> SHO ERR
No errors.
SQL>
SQL> variable addsal NUMBER
SQL>
SQL> EXEC add_sal(7369, :addsal);

PL/SQL procedure successfully completed.

SQL>
SQL> PRINT addsal

    ADDSAL
----------
      1800

SQL>

Alternatively, independent of SQL*Plus, you could execute it as an anonymous block:

或者,独立于SQL * Plus,您可以将其作为匿名块执行:

SQL> set serveroutput on
SQL> DECLARE
  2     o_addsal NUMBER;
  3  BEGIN
  4     add_sal(7369, o_addsal);
  5     DBMS_OUTPUT.PUT_LINE('The output is : '||o_addsal);
  6  END;
  7  /
The output is : 1800

PL/SQL procedure successfully completed.

SQL>

#2

To use i_empno in exec statement you must define it before:

要在exec语句中使用i_empno,必须在以下之前定义它:

VARIABLE i_empno NUMBER
VARIABLE addsal NUMBER
BEGIN
 :i_empno:=4;
END;
/
exec add_sal(:i_empno,:addsal);

Or you need to pass some value:

或者你需要传递一些价值:

VARIABLE addsal NUMBER
exec add_sal(1,:addsal);

#1