Oracle Oracale过程执行出现错误

我在oracle中有一个存储过程。每当我尝试执行它时，它都会给我一个下面提到的错误: 命令中从第5行开始的错误: exec usp_eventmgmt_get_events(TO_DATE('2018-07-18 '，'yyyy-MM-DD')，'District'，1) * 错误报告: ORA-06550: 第1行，第7列: PLS-00306: 调用 “USP_EVENTMGMT_GET_EVENTS” 时参数的数量或类型错误ORA-06550: 第1行，第7列: PL/SQL: 语句已忽略06550。00000-“行 % s，列 % s:\ n % s” * 原因: 通常是PL/SQL编译错误。行动:

CREATE OR REPLACE PROCEDURE "EGISINSPTEST"."USP_EVENTMGMT_GET_EVENTS"
(
    end_date date,
    heirarchy_type varchar2,
    heirarchy_key int,
  eventCursor OUT SYS_REFCURSOR
)
AS
BEGIN
IF heirarchy_type = 'abc' THEN
  OPEN eventCursor FOR
    SELECT rownum AS "id", es.* FROM
      (SELECT 

        DueDateUnformatted as DueDate, 
        Priority,
        ComplianceDate, 
        AssignmentName,
        count(EventKey) as Total
      FROM  view_viename1
      WHERE DueDateUnformatted < (end_date + 1) and parentEventKey is null and inspectiondate is null and DistrictKey=heirarchy_key
      Group By  DueDateUnformatted, Priority, ComplianceDate, AssignmentName) es;
      --dbms_output.put_line('Output 1');
ELSE IF heirarchy_type = 'def' THEN
  OPEN eventCursor FOR
    SELECT rownum AS "id", es.* FROM
      (SELECT 

      DueDateUnformatted as DueDate, 
      Priority,
      ComplianceDate, 
      AssignmentName,
      count(EventKey) as Total
      FROM  view_viename1
      WHERE DueDateUnformatted < (end_date + 1) and parentEventKey is null and inspectiondate is null and DivisionKey=heirarchy_key
      Group By  DueDateUnformatted, Priority, ComplianceDate, AssignmentName) es;
      --dbms_output.put_line('Output 2');
ELSE IF heirarchy_type = 'hij' THEN
  OPEN eventCursor FOR
    SELECT rownum AS "id", es.* FROM
      (SELECT 

      DueDateUnformatted as DueDate, 
      Priority,
      ComplianceDate, 
      AssignmentName,
      count(EventKey) as Total
      FROM  view_viename1
      WHERE DueDateUnformatted < (end_date + 1) and parentEventKey is null and inspectiondate is null and CompanyKey=heirarchy_key
      Group By  DueDateUnformatted, Priority, ComplianceDate, AssignmentName) es;    
      --dbms_output.put_line('Output 3');
END IF;
END IF;
END IF;
END;
复制

正如错误所暗示的那样

“调用中参数的错误数量或类型”

该例程期望3个输入参数，再加上1个输出参数。

SQL> CREATE OR REPLACE PROCEDURE USP_EVENTMGMT_GET_EVENTS
  2  (
  3      end_date date,
  4      heirarchy_type varchar2,
  5      heirarchy_key int,
  6    eventCursor OUT SYS_REFCURSOR
  7  ) is
  8  begin
  9    open eventCursor for
 10      select count(*) c
 11      from  all_objects
 12      where object_type = heirarchy_type
 13      and  created > end_date;
 14  end;
 15  /

Procedure created.

SQL>
SQL> exec USP_EVENTMGMT_GET_EVENTS(sysdate,'TABLE',1);
BEGIN USP_EVENTMGMT_GET_EVENTS(sysdate,'TABLE',1); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'USP_EVENTMGMT_GET_EVENTS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>
SQL> variable rc refcursor
SQL> exec USP_EVENTMGMT_GET_EVENTS(sysdate,'TABLE', 1, :rc);

PL/SQL procedure successfully completed.

SQL> print rc

         C
----------
         0

1 row selected.
复制

所以你需要一个 “接收” 变量来保持ref光标输出。