将Oracle过程移植到PostgreSQL [英] Porting Oracle Procedure to PostgreSQL

问题描述

我正在将Oracle函数移植到Postgres PGPLSQL ..我一直在使用本指南： http://www.postgresql.org/docs/8.1/static/plpgsql.html

 创建或替换过程DATA_UPDATE 
（任务NUMBER，
任务NUMBER）
 AS 
 BEGIN 
如果任务不是NULL THEN 
 UPDATE MISSION_OBJECTIVE MO 
 SET（MO.MO_TKR_TOTAL_OFF_SCHEDULED，
 MO.MO_TKR_TOTAL_RECEIVERS）= 
（SELECT NVL（SUM（RR.TRQ_FUEL_OFFLOAD），0），
 NVL（SUM（RR.TRQ_NUMBER_RECEIVERS），0）
 FROM REFUELING_REQUEST RR，MISSION_REQUEST_PAIRING MRP 
 WHERE MO.MSN_INT_ID = MRP.MSN_INT_ID 
 AND MO.MO_INT_ID = MRP.MO_INT_ID 
 AND MRP.REQ_INT_ID = RR.REQ_INT_ID）
 WHERE MO.MSN_INT_ID = mission 
 AND MO.MO_INT_ID = task; 
 END IF; 
 COMMIT; 
 END; 
  

我有这么远：

  CREATE OR REPLACE FUNCTION DATA_UPDATE 
（NUMERIC，
 NUMERIC）
 RETURNS integer as'
 DECLARE 
任务ALIAS $ 1 ; 
任务ALIAS为$ 2; 
 BEGIN 
如果任务不是NULL THEN 
 UPDATE MISSION_OBJECTIVE MO 
 SET（MO.MO_TKR_TOTAL_OFF_SCHEDULED，
 MO.MO_TKR_TOTAL_RECEIVERS）= 
（SELECT COALESCE （RR.TRQ_FUEL_OFFLOAD），0），
 COALESCE（SUM（RR.TRQ_NUMBER_RECEIVERS），0）
 FROM REFUELING_REQUEST RR，MISSION_REQUEST_PAIRING MRP 
 WHERE MO.MSN_INT_ID = MRP.MSN_INT_ID 
 AND MO.MO_INT_ID = MRP.MO_INT_ID 
 AND MRP.REQ_INT_ID = RR.REQ_INT_ID）
 WHERE MO.MSN_INT_ID = mission 
 AND MO.MO_INT_ID = task; 
 END IF; 
 COMMIT; 
 END; 
'LANGUAGE plpgsql; 
  

这是我得到的错误：

 错误：SELECT或其附近出现语法错误
 LINE 1：... OTAL_OFF_SCHEDULED，MO.MO_TKR_TOTAL_RECEIVERS）=（SELECT COA ... 
  
 
 
 
我不知道为什么这不工作...任何想法？
  CREATE OR REPLACE FUNCTION DATA_UPDATE 
（任务NUMERIC，
任务NUMERIC）
 RETURNS void as'
 DECLARE 
 offScheduled int4; 
 totalReceivers int4; 
 BEGIN 
如果任务不是NULL THEN 
选择COALESCE（SUM（RR.TRQ_FUEL_OFFLOAD），0），
 COALESCE（SUM（RR.TRQ_NUMBER_RECEIVERS），0）into offScheduled，totalReceivers 
 FROM REFUELING_REQUEST RR，MISSION_REQUEST_PAIRING MRP 
 WHERE MO.MSN_INT_ID = MRP.MSN_INT_ID 
 AND MO.MO_INT_ID = MRP。 MO_INT_ID 
和MRP.REQ_INT_ID = RR.REQ_INT_ID; 
 
 UPDATE MISSION_OBJECTIVE MO 
 SET MO.MO_TKR_TOTAL_OFF_SCHEDULED = offScheduled，
 MO.MO_TKR_TOTAL_RECEIVERS = totalReceivers 
 WHERE MO.MSN_INT_ID = mission 
 AND MO.MO_INT_ID =任务; 
 END IF; 
 END; 
'LANGUAGE plpgsql; 
  
 ...假设我没有把逻辑弄得太糟糕了）
 
 
 
我自由地改变了参数的定义方式和返回类型（因为它看起来好像没有从函数返回任何东西）
 
 
 
编辑：oops，我使用了的错误构造select into  ... 
 
I am porting an Oracle function into Postgres PGPLSQL.. I have been using this guide:  http://www.postgresql.org/docs/8.1/static/plpgsql.html
CREATE OR REPLACE PROCEDURE DATA_UPDATE
  (mission  NUMBER,
   task     NUMBER)
AS
BEGIN
IF mission IS NOT NULL THEN
  UPDATE MISSION_OBJECTIVE MO
     SET (MO.MO_TKR_TOTAL_OFF_SCHEDULED,
          MO.MO_TKR_TOTAL_RECEIVERS) =
           (SELECT NVL(SUM(RR.TRQ_FUEL_OFFLOAD),0),
                   NVL(SUM(RR.TRQ_NUMBER_RECEIVERS),0)
              FROM REFUELING_REQUEST RR, MISSION_REQUEST_PAIRING MRP
             WHERE MO.MSN_INT_ID = MRP.MSN_INT_ID
               AND MO.MO_INT_ID = MRP.MO_INT_ID
               AND MRP.REQ_INT_ID = RR.REQ_INT_ID)
   WHERE MO.MSN_INT_ID = mission
     AND MO.MO_INT_ID = task ;
END IF ;
COMMIT ;
END ;
I've got it this far:
CREATE OR REPLACE FUNCTION DATA_UPDATE
  (NUMERIC,
   NUMERIC)
   RETURNS integer as '
   DECLARE
   mission ALIAS for $1;
   task ALIAS for $2;
BEGIN
IF mission IS NOT NULL THEN
  UPDATE MISSION_OBJECTIVE MO
     SET (MO.MO_TKR_TOTAL_OFF_SCHEDULED,
          MO.MO_TKR_TOTAL_RECEIVERS) =
           (SELECT COALESCE(SUM(RR.TRQ_FUEL_OFFLOAD),0),
                   COALESCE(SUM(RR.TRQ_NUMBER_RECEIVERS),0)
              FROM REFUELING_REQUEST RR, MISSION_REQUEST_PAIRING MRP
             WHERE MO.MSN_INT_ID = MRP.MSN_INT_ID
               AND MO.MO_INT_ID = MRP.MO_INT_ID
               AND MRP.REQ_INT_ID = RR.REQ_INT_ID)
   WHERE MO.MSN_INT_ID = mission
     AND MO.MO_INT_ID = task ;
END IF;
COMMIT;
END;
' LANGUAGE plpgsql;
This is the error I get:
ERROR:  syntax error at or near "SELECT"
LINE 1: ...OTAL_OFF_SCHEDULED, MO.MO_TKR_TOTAL_RECEIVERS) = (SELECT COA...
I do not know why this isn't working... any ideas?
解决方案
I don't think Postgres supports that update construct (the simple test I tried fails with the same error). You might have to do something like this:
CREATE OR REPLACE FUNCTION DATA_UPDATE
  (mission NUMERIC,
   task NUMERIC)
   RETURNS void as '
DECLARE
   offScheduled int4;
   totalReceivers int4; 
BEGIN
IF mission IS NOT NULL THEN
  select COALESCE(SUM(RR.TRQ_FUEL_OFFLOAD),0),
  COALESCE(SUM(RR.TRQ_NUMBER_RECEIVERS),0) into offScheduled, totalReceivers 
  FROM REFUELING_REQUEST RR, MISSION_REQUEST_PAIRING MRP
             WHERE MO.MSN_INT_ID = MRP.MSN_INT_ID
               AND MO.MO_INT_ID = MRP.MO_INT_ID
               AND MRP.REQ_INT_ID = RR.REQ_INT_ID;

 UPDATE MISSION_OBJECTIVE MO
     SET MO.MO_TKR_TOTAL_OFF_SCHEDULED = offScheduled,
          MO.MO_TKR_TOTAL_RECEIVERS = totalReceivers 
     WHERE MO.MSN_INT_ID = mission
     AND MO.MO_INT_ID = task ;
END IF;
END;
' LANGUAGE plpgsql;
... assuming I haven't mangled the logic too badly ;)

I took the liberty of changing the way the parameters are defined, and the return type (since it looks like you're not actually returning anything from the function?)

Edit: oops, I used the wrong construct for select into...

                        
这篇关于将Oracle过程移植到PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！