sql - Oracle存储过程中的UNION

Question

我试图在Oracle中做最基本的存储过程。我只是想返回由联合加入的2个查询...代码如下:

CREATE OR REPLACE PROCEDURE GETATMCONTRACTSBYMANAGERID  (
   MANAGERID IN NUMBER
) AS 
BEGIN
  select t.ID, t."NUMBER", t.TITLE, t.NOTES, t.CONTRACT_TYPE_ID as CONTRACT_TYPE_ID,   t.CONTRACT_STATUS_ID as CONTRACT_STATUS_ID, t.TASK_NUMBER as TASK_NUMBER, c.CONSULTANT_ID,t.TASK_MANAGER_ID, t.IS_COMPREHENSIVE, t."NUMBER", TO_DATE(SUBSTR(t."NUMBER", 8, 2), 'rr')  as val, SUBSTR(t."NUMBER", 11, 3) as val2, t.REF_ID from CONTRACT t INNER JOIN CONTRACT c on c.ID = t.REF_ID
  where t.TASK_MANAGER_ID = MANAGERID;
  UNION 
  select t.ID, t."NUMBER", t.TITLE, t.NOTES, t.CONTRACT_TYPE_ID as CONTRACT_TYPE_ID, t.CONTRACT_STATUS_ID as CONTRACT_STATUS_ID, t.TASK_NUMBER as TASK_NUMBER, t.CONSULTANT_ID, t.TASK_MANAGER_ID, t.IS_COMPREHENSIVE, t."NUMBER", TO_DATE(SUBSTR(t."NUMBER", 8, 2), 'rr')  as val, SUBSTR(t."NUMBER", 11, 3) as val2, t.REF_ID from CONTRACT t
  where t."NUMBER" in (select "NUMBER" from Contract t1 where t1.TASK_MANAGER_ID = MANAGERID)
    and t.TASK_NUMBER = ' '
  order by val desc, val2 desc, TASK_NUMBER asc;
END GETATMCONTRACTSBYMANAGERID;

我将第一个承认我对SQL Server的理解要比对oracle的适应度高，但对我来说似乎应该可以。

它返回的错误是:

Error(7,1): PLS-00103: Encountered the symbol "UNION" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

任何与此问题的帮助将不胜感激:)

Answer 1

您在;运算符之前有一个多余的分号(union)-只需将其删除:

select t.ID, t."NUMBER", t.TITLE, t.NOTES, t.CONTRACT_TYPE_ID as CONTRACT_TYPE_ID,   t.CONTRACT_STATUS_ID as CONTRACT_STATUS_ID, t.TASK_NUMBER as TASK_NUMBER, c.CONSULTANT_ID,t.TASK_MANAGER_ID, t.IS_COMPREHENSIVE, t."NUMBER", TO_DATE(SUBSTR(t."NUMBER", 8, 2), 'rr')  as val, SUBSTR(t."NUMBER", 11, 3) as val2, t.REF_ID from CONTRACT t INNER JOIN CONTRACT c on c.ID = t.REF_ID
  where t.TASK_MANAGER_ID = MANAGERID -- Semicolon removed here
  UNION 
  select t.ID, t."NUMBER", t.TITLE, t.NOTES, t.CONTRACT_TYPE_ID as CONTRACT_TYPE_ID, t.CONTRACT_STATUS_ID as CONTRACT_STATUS_ID, t.TASK_NUMBER as TASK_NUMBER, t.CONSULTANT_ID, t.TASK_MANAGER_ID, t.IS_COMPREHENSIVE, t."NUMBER", TO_DATE(SUBSTR(t."NUMBER", 8, 2), 'rr')  as val, SUBSTR(t."NUMBER", 11, 3) as val2, t.REF_ID from CONTRACT t
  where t."NUMBER" in (select "NUMBER" from Contract t1 where t1.TASK_MANAGER_ID = MANAGERID)
    and t.TASK_NUMBER = ' '
  order by val desc, val2 desc, TASK_NUMBER asc;