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;

1. EXECUTE是SQL*Plus命令
2. 您没有将所需的参数传递到过程。您已经为过程声明了两个参数：

p_ projectname IN VARCHAR2，p_description OUT VARCHAR2

因此，您需要声明所需的参数，然后将其传递给过程：

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

---

更新工作演示：

在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