将Sql Server存储过程迁移到Oracle PL/ Sql

Question

下面的存储过程适用于SQL Server(在我对oracle进行一些特定更改之前)。现在这个存储过程也必须适用于Oracle。

我使用的Oracle Sql Developer在"OPEN CURSOR_ FOR"之后的行抱怨

Oracle不喜欢我对select语句和union语句进行分组，并对整个结果进行排序。

我做错了什么?关于oracle存储过程我只知道一点点…

create or replace
PROCEDURE GetWorkflowStatusForMatrix(
    p_ApplicationId IN varchar2,    
    CURSOR_ OUT sys_refcursor
)
AS
BEGIN   
    OPEN CURSOR_ FOR
    (
  select ApplicationId || ModuleId || UNIT_ID as StatusKey, UNIT_ID, ApplicationId, ModuleId, Owner, "Level", Action, "Comment", LastModifiedUser, LastModifiedDate 
    from WorkflowStatus where ApplicationId = p_ApplicationId
    union
    select distinct e.ApplicationId + WorkflowId + UnitId as StatusKey, UnitId, e.ApplicationId, WorkflowId, w.Owner, 'Level1', 'Working', EventType, UserId, EventDateTime as LastModifiedDate
    from EventLog e
    join WorkflowStatus w on w.ApplicationId = e.ApplicationId and w.ModuleId = e.WorkflowId and w.UNIT_ID = e.UnitId
    where e.ApplicationId = p_ApplicationId and w.Owner <> '' and w.Action = 'Created'
    )
    order by LastModifiedDate DESC;
END;

Answer 1

您需要阅读如何在Oracle中定义游标-这将帮助您…

http://www.techonthenet.com/oracle/cursors/declare.php

Answer 2

create or replace
PROCEDURE GetWorkflowStatusForMatrix(
    p_ApplicationId IN varchar2,    
    CURSOR_ OUT sys_refcursor
)
AS
BEGIN   
  OPEN CURSOR_ FOR
    select ApplicationId || ModuleId || UNIT_ID as StatusKey, UNIT_ID, ApplicationId, ModuleId, Owner, "Level", Action, "Comment", LastModifiedUser, LastModifiedDate 
  from WorkflowStatus where ApplicationId = p_ApplicationId
  union
  select distinct e.ApplicationId + WorkflowId + UnitId as StatusKey, UnitId, e.ApplicationId, WorkflowId, w.Owner, 'Level1', 'Working', EventType, UserId, EventDateTime as LastModifiedDate
from EventLog e
join WorkflowStatus w on w.ApplicationId = e.ApplicationId and w.ModuleId = e.WorkflowId and w.UNIT_ID = e.UnitId
where e.ApplicationId = p_ApplicationId and w.Owner <> '' and w.Action = 'Created'
order by LastModifiedDate DESC;
END;