PL/SQL Union语句公共变量需要定义？

Question

  SELECT JOBNO,
         SUBJOBNO,
         SUM (IPCST) IPCST,
         SUM (VAD) VAD,
         SUM (WSTVAD) WSTVAD,
         SUM (PRQTY) PRQTY,
         SUM (ACC) ACC,
         SUM (SGVAD) SGVAD
    FROM (  SELECT jobno,
                   subjobno,
                   SUM (ipcost) IPCST,
                   0 VAD,
                   0 WSTVAD,
                   0 PRQTY,
                   0 ACC,
                   0 SGVAD
              FROM stt_prdtl
             WHERE jobno = 123 AND wktype = 100
          GROUP BY jobno, subjobno
          UNION
            SELECT jobno,
                   subjobno,
                   0 IPCST,
                   SUM (vadncost) VAD,
                   0 WSTVAD,
                   0 PRQTY,
                   0 ACC,
                   0 SGVAD
              FROM stt_prdtl
             WHERE jobno = 123
          GROUP BY jobno, subjobno
          UNION
            SELECT jobno,
                   subjobno,
                   0 IPCST,
                   0 VAD,
                   SUM (wstvadncost) WSTVAD,
                   0 PRQTY,
                   0 ACC,
                   0 SGVAD
              FROM stt_prdtl
             WHERE jobno = 123
          GROUP BY jobno, subjobno
          UNION
            SELECT jobno,
                   subjobno,
                   0 IPCST,
                   0 VAD,
                   0 WSTVAD,
                   SUM (prdqty) PRQTY,
                   0 ACC,
                   0 SGVAD
              FROM stt_prdtl
             WHERE jobno = 123 AND wktype = 500
          GROUP BY jobno, subjobno
          UNION
            SELECT jobno,
                   subjob,
                   0 IPCST,
                   0 VAD,
                   0 WSTVAD,
                   0 PRQTY,
                   SUM (acc_cost) ACC,
                   0 SGVAD
              FROM stt_prhdr
             WHERE jobno = 123 AND status = 'C'
          GROUP BY jobno, subjob
          UNION
            SELECT jobno,
                   subjobno,
                   0 IPCST,
                   0 VAD,
                   0 WSTVAD,
                   0 PRQTY,
                   0 ACC,
                   SUM (sgvadcost) SGVAD
              FROM stt_prdtl
             WHERE jobno = 123 AND wktype = 500
          GROUP BY jobno, subjobno)
GROUP BY jobno, subjobno
ORDER BY subjobno;

任何人，你可以帮助我如何定义一般的工作编号，而不是键入每行。我需要定义这个变量，通常在代码的开头，从那里脚本需要调用。

我期待任何一个人的结果？

Answer 1

DECLARE
   jobno_var number;  // or jobno_var table.column%type;
BEGIN
      // Now you can reference variable jobno_var anywhere in your code below
     SELECT JOBNO,
         SUBJOBNO,
         SUM (IPCST) IPCST,
        ...
         GROUP BY jobno, subjobno)
    GROUP BY jobno, subjobno
    ORDER BY subjobno;
END;