oracle - Oracle过程编译成功但显示错误

Question

使用Oracle SQL Developer我创建了一个简单的过程。该过程成功编译，但是当我键入命令时:

execute CMPPROJECTPROCSELECT();

BEGIN CMPPROJECTPROCSELECT(); END;

我收到以下错误:

Error starting at line : 1 in command -
execute CMPPROJECTPROCSELECT()
Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CMPPROJECTPROCSELECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Error starting at line : 2 in command -
BEGIN CMPPROJECTPROCSELECT(); END;
Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CMPPROJECTPROCSELECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

请帮我解决这个问题。我知道这是一个小错误。另外，我已经正确指定了数据类型，名称声明。

我的程序代码是

CREATE OR REPLACE PROCEDURE CMPPROJECTPROCSELECT( 
  p_projectname IN VARCHAR2,
  p_description OUT VARCHAR2)
IS
BEGIN
  SELECT DESCRIPTION
    INTO p_description
    FROM CMPPROJECT
   WHERE PROJECTNAME = p_projectname;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_description:= NULL;
    COMMIT;
END CMPPROJECTPROCSELECT;

Answer 1

execute CMPPROJECTPROCSELECT();
BEGIN CMPPROJECTPROCSELECT();
 END;

EXECUTE 是 SQL * Plus 命令。

您没有将必需的参数传递给过程。您已经为过程声明了两个参数:

p_projectname IN VARCHAR2,p_description OUT VARCHAR2

So, you need to declare the required parameters and then pass it to the procedure:

DECLARE
  proj_desc VARCHAR2(2000);
BEGIN
  CMPPROJECTPROCSELECT('project_name', proj_desc);
  -- use the OUT value of proj_desc later
END;
/ 

附带说明，您根本不需要 COMMIT 。它是永久提交 DML 所必需的，与 SELECT ..INTO子句无关。

SELECT DESCRIPTION INTO p_description FROM CMPPROJECT WHERE PROJECTNAME = p_projectname;
EXCEPTION
      WHEN NO_DATA_FOUND THEN
        p_description:= NULL;
COMMIT;  -- You don't need COMMIT at all

UPDATE 一个有效的演示:

在PL/SQL中:

SQL> CREATE OR REPLACE PROCEDURE get_emp(
  2      p_ename IN VARCHAR2,
  3      p_job OUT VARCHAR2)
  4  IS
  5  BEGIN
  6    SELECT job INTO p_job FROM emp WHERE ename = p_ename;
  7  END;
  8  /

Procedure created.

SQL> sho err
No errors.
SQL> set serveroutput on
SQL> DECLARE
  2    job VARCHAR2(20);
  3  BEGIN
  4    get_emp('SCOTT',JOB);
  5    DBMS_OUTPUT.PUT_LINE('The output is '||job);
  6  END;
  7  /
The output is ANALYST

PL/SQL procedure successfully completed.

在SQL * Plus中:

SQL> VARIABLE JOB VARCHAR2(20);
SQL> EXECUTE get_emp('SCOTT', :JOB);

PL/SQL procedure successfully completed.

SQL> PRINT JOB;

JOB
--------------------------------
ANALYST