救急！！！ 那位高手研究过Oracle9i中实现邮件程序自动转发

Question

我做的一個例子，表和存儲過程，大家一起看看
CREATE TABLE SALES_TOTAL_AUTO(EXEC_DAY DATE,
ORDER_BEFORE3QTY NUMBER(10),
ORDER_BEFORE2QTY NUMBER(10),
ORDER_BEFOREQTY NUMBER(10),
ORDER_CURRENTQTY NUMBER(10),
ORDER_NEXTQTY NUMBER(10),
ORDER_BEFORE3SUM NUMBER(10),
ORDER_BEFORE2SUM NUMBER(10),
ORDER_BEFORESUM NUMBER(10),
ORDER_CURRENTSUM NUMBER(10),
ORDER_NEXTSUM NUMBER(10),
SHIP_BEFORE3QTY NUMBER(10),
SHIP_BEFORE2QTY NUMBER(10),
SHIP_BEFOREQTY NUMBER(10),
SHIP_CURRENTQTY NUMBER(10),
SHIP_NEXTQTY NUMBER(10),
SHIP_BEFORE3SUM NUMBER(10),
SHIP_BEFORE2SUM NUMBER(10),
SHIP_BEFORESUM NUMBER(10),
SHIP_CURRENTSUM NUMBER(10),
UNSHIP_BEFORE3QTY NUMBER(10),
UNSHIP_BEFORE2QTY NUMBER(10),
UNSHIP_BEFOREQTY NUMBER(10),
UNSHIP_CURRENTQTY NUMBER(10),
UNSHIP_NEXTQTY NUMBER(10),
UNSHIP_BEFORE3SUM NUMBER(10),
UNSHIP_BEFORE2SUM NUMBER(10),
UNSHIP_BEFORESUM NUMBER(10),
UNSHIP_CURRENTSUM NUMBER(10),
CONSTRAINT PK_SALES_TOTAL_AUTO PRIMARY KEY(EXEC_DAY));
*************************************************************************************
  
CREATE OR REPLACE PROCEDURE PRO_SALES_TOTAL_AUTO(TIMEPOINT IN VARCHAR2)
AS
  TIME_POINT DATE;
  O_BEFORE3_QTY         NUMBER(10);
  O_BEFORE3_SUM         NUMBER(10);
  O_BEFORE2_QTY         NUMBER(10);
  O_BEFORE2_SUM         NUMBER(10);
  O_BEFORE_QTY          NUMBER(10);
  O_BEFORE_SUM          NUMBER(10);
  O_CUR_QTY          NUMBER(10);
  O_CUR_SUM          NUMBER(10);
  O_NEXT_QTY         NUMBER(10);
  O_NEXT_SUM         NUMBER(10);
  S_BEFORE3_QTY              NUMBER(10);
  S_BEFORE3_SUM              NUMBER(10);
  S_BEFORE2_QTY              NUMBER(10);
  S_BEFORE2_SUM              NUMBER(10);
  S_BEFORE_QTY               NUMBER(10);
  S_BEFORE_SUM               NUMBER(10);
  S_CUR_QTY          NUMBER(10);
  S_CUR_SUM          NUMBER(10);
  S_NEXT_QTY         NUMBER(10);
  S_NEXT_SUM         NUMBER(10);
  U_BEFORE3_QTY              NUMBER(10);
  U_BEFORE2_QTY              NUMBER(10);
  U_BEFORE_QTY               NUMBER(10);
  U_CUR_QTY          NUMBER(10);
  U_NEXT_QTY         NUMBER(10);
BEGIN
  IF TIMEPOINT IS NULL THEN TIME_POINT:=SYSDATE;
  ELSE TIME_POINT:=TO_DATE(TIMEPOINT,'YYYY/MM/DD');
  END IF;
  
  ---接單數據(開始)
  
  SELECT ROUND(SUM(A.TOTAL_AMT_ORDERED*FUN_GET_RATE(B.CUSTOMER_ID))) INTO O_BEFORE3_QTY
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=ADD_MONTHS(TRUNC(TIME_POINT,'MONTH'),-3)
  AND A.PROMISE_DATE<ADD_MONTHS(TIME_POINT,-3)
  AND B.STATUS<>'X'
  AND A.CUST_ORDER_ID=B.ID
  AND A.CUST_ORDER_ID NOT IN (SELECT C.CUST_ORDER_ID FROM CUST_ORDER_CONFIRM C WHERE CONFIRM='N');
  ---接單金額(當月): 截止到查詢時止, 統計截止日期所在月的第一天到統計截止日期的訂單金額
  ---接單金額(其他): 依此類推, 各月同期的訂單金額
  
  SELECT ROUND(SUM(A.TOTAL_AMT_ORDERED*FUN_GET_RATE(B.CUSTOMER_ID))) INTO O_BEFORE3_SUM
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=ADD_MONTHS(TRUNC(TIME_POINT,'MONTH'),-3)
  AND A.PROMISE_DATE<TRUNC(ADD_MONTHS(TIME_POINT,-2),'MONTH')
  AND B.STATUS<>'X'
  AND A.CUST_ORDER_ID=B.ID
  AND A.CUST_ORDER_ID NOT IN (SELECT C.CUST_ORDER_ID FROM CUST_ORDER_CONFIRM C WHERE CONFIRM='N');
  
  ---接單總金額(當月): 截止到查詢時止, 統計截止日期所在月的訂單金額
  ---接單總金額(其他): 依此類推, 各月的訂單金額
  
  SELECT ROUND(SUM(A.TOTAL_AMT_ORDERED*FUN_GET_RATE(B.CUSTOMER_ID))) INTO O_BEFORE2_QTY
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=ADD_MONTHS(TRUNC(TIME_POINT,'MONTH'),-2)
  AND A.PROMISE_DATE<ADD_MONTHS(TIME_POINT,-2)
  AND B.STATUS<>'X'
  AND A.CUST_ORDER_ID=B.ID
  AND A.CUST_ORDER_ID NOT IN (SELECT C.CUST_ORDER_ID FROM CUST_ORDER_CONFIRM C WHERE CONFIRM='N');  SELECT ROUND(SUM(A.TOTAL_AMT_ORDERED*FUN_GET_RATE(B.CUSTOMER_ID))) INTO O_BEFORE2_SUM
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=ADD_MONTHS(TRUNC(TIME_POINT,'MONTH'),-2)
  AND A.PROMISE_DATE<TRUNC(ADD_MONTHS(TIME_POINT,-1),'MONTH')
  AND B.STATUS<>'X'
  AND A.CUST_ORDER_ID=B.ID
  AND A.CUST_ORDER_ID NOT IN (SELECT C.CUST_ORDER_ID FROM CUST_ORDER_CONFIRM C WHERE CONFIRM='N');
  
  SELECT ROUND(SUM(A.TOTAL_AMT_ORDERED*FUN_GET_RATE(B.CUSTOMER_ID))) INTO O_BEFORE_QTY
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=ADD_MONTHS(TRUNC(TIME_POINT,'MONTH'),-1)
  AND A.PROMISE_DATE<ADD_MONTHS(TIME_POINT,-1)
  AND B.STATUS<>'X'
  AND A.CUST_ORDER_ID=B.ID
  AND A.CUST_ORDER_ID NOT IN (SELECT C.CUST_ORDER_ID FROM CUST_ORDER_CONFIRM C WHERE CONFIRM='N');
  
  SELECT ROUND(SUM(A.TOTAL_AMT_ORDERED*FUN_GET_RATE(B.CUSTOMER_ID))) INTO O_BEFORE_SUM
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=ADD_MONTHS(TRUNC(TIME_POINT,'MONTH'),-1)
  AND A.PROMISE_DATE<TRUNC(TIME_POINT,'MONTH')
  AND B.STATUS<>'X'
  AND A.CUST_ORDER_ID=B.ID
  AND A.CUST_ORDER_ID NOT IN (SELECT C.CUST_ORDER_ID FROM CUST_ORDER_CONFIRM C WHERE CONFIRM='N');  SELECT ROUND(SUM(A.TOTAL_AMT_ORDERED*FUN_GET_RATE(B.CUSTOMER_ID))) INTO O_CUR_QTY
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=TRUNC(TIME_POINT,'MONTH')
  AND A.PROMISE_DATE<TIME_POINT
  AND B.STATUS<>'X'
  AND A.CUST_ORDER_ID=B.ID
  AND A.CUST_ORDER_ID NOT IN (SELECT C.CUST_ORDER_ID FROM CUST_ORDER_CONFIRM C WHERE CONFIRM='N');
  
  SELECT ROUND(SUM(A.TOTAL_AMT_ORDERED*FUN_GET_RATE(B.CUSTOMER_ID))) INTO O_CUR_SUM
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=TRUNC(TIME_POINT,'MONTH')
  AND A.PROMISE_DATE<LAST_DAY(TIME_POINT)+1
  AND B.STATUS<>'X'
  AND A.CUST_ORDER_ID=B.ID
  AND A.CUST_ORDER_ID NOT IN (SELECT C.CUST_ORDER_ID FROM CUST_ORDER_CONFIRM C WHERE CONFIRM='N');
  
  SELECT ROUND(SUM(A.TOTAL_AMT_ORDERED*FUN_GET_RATE(B.CUSTOMER_ID))) INTO O_NEXT_QTY
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=ADD_MONTHS(TRUNC(TIME_POINT,'MONTH'),1)
  AND A.PROMISE_DATE<ADD_MONTHS(TIME_POINT,1)
  AND B.STATUS<>'X'
  AND A.CUST_ORDER_ID=B.ID
  AND A.CUST_ORDER_ID NOT IN (SELECT C.CUST_ORDER_ID FROM CUST_ORDER_CONFIRM C WHERE CONFIRM='N');  SELECT ROUND(SUM(A.TOTAL_AMT_ORDERED*FUN_GET_RATE(B.CUSTOMER_ID))) INTO O_NEXT_SUM
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=LAST_DAY(TIME_POINT)+1
  AND A.PROMISE_DATE<TRUNC(ADD_MONTHS(TIME_POINT,2),'MONTH')
  AND B.STATUS<>'X'
  AND A.CUST_ORDER_ID=B.ID
  AND A.CUST_ORDER_ID NOT IN (SELECT C.CUST_ORDER_ID FROM CUST_ORDER_CONFIRM C WHERE CONFIRM='N');  -----接單數據(結束), 出貨數據(開始)
  
  SELECT ROUND(SUM(A.QTY*C.UNIT_PRICE*FUN_GET_RATE(D.CUSTOMER_ID))) INTO S_BEFORE3_QTY 
  FROM INVENTORY_TRANS A,CUST_ORDER_LINE C,CUSTOMER_ORDER D
  WHERE A.TYPE='O'
  AND A.CLASS='I'
  AND (A.WAREHOUSE_ID='FINAL_GOODS' OR A.WAREHOUSE_ID IS NULL)
  AND A.TRANSACTION_DATE>=TRUNC(TIME_POINT,'MONTH')
  AND A.TRANSACTION_DATE<TIME_POINT
  AND C.CUST_ORDER_ID=A.CUST_ORDER_ID
  AND C.LINE_NO=A.CUST_ORDER_LINE_NO
  AND C.PROMISE_DATE<=TRUNC(ADD_MONTHS(TIME_POINT,-2),'MONTH')
  AND C.CUST_ORDER_ID=D.ID;  ---出貨金額(當月): 截止到查詢時止, 統計截止日期所在月的第一天到統計截止日期的出貨金額(出交期在當月的訂單)
  ---出貨金額(其他): 依此類推, 各月同期的出貨金額  SELECT ROUND(SUM(A.QTY*C.UNIT_PRICE*FUN_GET_RATE(D.CUSTOMER_ID))) INTO S_BEFORE3_SUM 
  FROM INVENTORY_TRANS A,CUST_ORDER_LINE C,CUSTOMER_ORDER D
  WHERE A.TYPE='O'
  AND A.CLASS='I'
  AND (A.WAREHOUSE_ID='FINAL_GOODS' OR A.WAREHOUSE_ID IS NULL)
  AND A.TRANSACTION_DATE>=TRUNC(ADD_MONTHS(TIME_POINT,-3),'MONTH')
  AND A.TRANSACTION_DATE<TRUNC(ADD_MONTHS(TIME_POINT,-2),'MONTH')
  AND C.CUST_ORDER_ID=A.CUST_ORDER_ID
  AND C.LINE_NO=A.CUST_ORDER_LINE_NO
  AND C.CUST_ORDER_ID=D.ID;
  

Answer 1

---出貨總金額(當月): 截止到查詢時止, 統計截止日期所在月的第一天到統計截止日期的出貨金額
  ---出貨總金額(其他): 依此類推, 各月的出貨金額  SELECT ROUND(SUM(A.QTY*C.UNIT_PRICE*FUN_GET_RATE(D.CUSTOMER_ID))) INTO S_BEFORE2_QTY 
  FROM INVENTORY_TRANS A,CUST_ORDER_LINE C,CUSTOMER_ORDER D
  WHERE A.TYPE='O'
  AND A.CLASS='I'
  AND (A.WAREHOUSE_ID='FINAL_GOODS' OR A.WAREHOUSE_ID IS NULL)
  AND A.TRANSACTION_DATE>=TRUNC(TIME_POINT,'MONTH')
  AND A.TRANSACTION_DATE<TIME_POINT
  AND C.CUST_ORDER_ID=A.CUST_ORDER_ID
  AND C.LINE_NO=A.CUST_ORDER_LINE_NO
  AND C.PROMISE_DATE>=TRUNC(ADD_MONTHS(TIME_POINT,-2),'MONTH')
  AND C.PROMISE_DATE<TRUNC(ADD_MONTHS(TIME_POINT,-1),'MONTH')
  AND C.CUST_ORDER_ID=D.ID;  SELECT ROUND(SUM(A.QTY*C.UNIT_PRICE*FUN_GET_RATE(D.CUSTOMER_ID))) INTO S_BEFORE2_SUM 
  FROM INVENTORY_TRANS A,CUST_ORDER_LINE C,CUSTOMER_ORDER D
  WHERE A.TYPE='O'
  AND A.CLASS='I'
  AND (A.WAREHOUSE_ID='FINAL_GOODS' OR A.WAREHOUSE_ID IS NULL)
  AND A.TRANSACTION_DATE>=TRUNC(ADD_MONTHS(TIME_POINT,-2),'MONTH')
  AND A.TRANSACTION_DATE<TRUNC(ADD_MONTHS(TIME_POINT,-1),'MONTH')
  AND C.CUST_ORDER_ID=A.CUST_ORDER_ID
  AND C.LINE_NO=A.CUST_ORDER_LINE_NO
  AND C.CUST_ORDER_ID=D.ID;
  
  SELECT ROUND(SUM(A.QTY*C.UNIT_PRICE*FUN_GET_RATE(D.CUSTOMER_ID))) INTO S_BEFORE_QTY 
  FROM INVENTORY_TRANS A,CUST_ORDER_LINE C,CUSTOMER_ORDER D
  WHERE A.TYPE='O'
  AND A.CLASS='I'
  AND (A.WAREHOUSE_ID='FINAL_GOODS' OR A.WAREHOUSE_ID IS NULL)
  AND A.TRANSACTION_DATE>=TRUNC(TIME_POINT,'MONTH')
  AND A.TRANSACTION_DATE<TIME_POINT
  AND C.CUST_ORDER_ID=A.CUST_ORDER_ID
  AND C.LINE_NO=A.CUST_ORDER_LINE_NO
  AND C.PROMISE_DATE>=TRUNC(ADD_MONTHS(TIME_POINT,-1),'MONTH')
  AND C.PROMISE_DATE<TRUNC(TIME_POINT,'MONTH')
  AND C.CUST_ORDER_ID=D.ID;  SELECT ROUND(SUM(A.QTY*C.UNIT_PRICE*FUN_GET_RATE(D.CUSTOMER_ID))) INTO S_BEFORE_SUM 
  FROM INVENTORY_TRANS A,CUST_ORDER_LINE C,CUSTOMER_ORDER D
  WHERE A.TYPE='O'
  AND A.CLASS='I'
  AND (A.WAREHOUSE_ID='FINAL_GOODS' OR A.WAREHOUSE_ID IS NULL)
  AND A.TRANSACTION_DATE>=TRUNC(ADD_MONTHS(TIME_POINT,-1),'MONTH')
  AND A.TRANSACTION_DATE<TRUNC(TIME_POINT,'MONTH')
  AND C.CUST_ORDER_ID=A.CUST_ORDER_ID
  AND C.LINE_NO=A.CUST_ORDER_LINE_NO
  AND C.CUST_ORDER_ID=D.ID;  SELECT ROUND(SUM(A.QTY*C.UNIT_PRICE*FUN_GET_RATE(D.CUSTOMER_ID))) INTO S_CUR_QTY 
  FROM INVENTORY_TRANS A,CUST_ORDER_LINE C,CUSTOMER_ORDER D
  WHERE A.TYPE='O'
  AND A.CLASS='I'
  AND (A.WAREHOUSE_ID='FINAL_GOODS' OR A.WAREHOUSE_ID IS NULL)
  AND A.TRANSACTION_DATE>=TRUNC(TIME_POINT,'MONTH') ---出貨時間段不變
  AND A.TRANSACTION_DATE<TIME_POINT
  AND C.CUST_ORDER_ID=A.CUST_ORDER_ID
  AND C.LINE_NO=A.CUST_ORDER_LINE_NO
  AND C.PROMISE_DATE>=TRUNC(TIME_POINT,'MONTH') ---交期
  AND C.PROMISE_DATE<LAST_DAY(TIME_POINT)+1
  AND C.CUST_ORDER_ID=D.ID;  SELECT ROUND(SUM(A.QTY*C.UNIT_PRICE*FUN_GET_RATE(D.CUSTOMER_ID))) INTO S_CUR_SUM 
  FROM INVENTORY_TRANS A,CUST_ORDER_LINE C,CUSTOMER_ORDER D
  WHERE A.TYPE='O'
  AND A.CLASS='I'
  AND (A.WAREHOUSE_ID='FINAL_GOODS' OR A.WAREHOUSE_ID IS NULL)
  AND A.TRANSACTION_DATE>=TRUNC(TIME_POINT,'MONTH')
  AND A.TRANSACTION_DATE<LAST_DAY(TIME_POINT)+1
  AND C.CUST_ORDER_ID=A.CUST_ORDER_ID
  AND C.LINE_NO=A.CUST_ORDER_LINE_NO
  AND C.CUST_ORDER_ID=D.ID;
  
  SELECT ROUND(SUM(A.QTY*C.UNIT_PRICE*FUN_GET_RATE(D.CUSTOMER_ID))) INTO S_NEXT_QTY 
  FROM INVENTORY_TRANS A,CUST_ORDER_LINE C,CUSTOMER_ORDER D
  WHERE A.TYPE='O'
  AND A.CLASS='I'
  AND (A.WAREHOUSE_ID='FINAL_GOODS' OR A.WAREHOUSE_ID IS NULL)
  AND A.TRANSACTION_DATE>=TRUNC(TIME_POINT,'MONTH')
  AND A.TRANSACTION_DATE<LAST_DAY(TIME_POINT)+1
  AND C.CUST_ORDER_ID=A.CUST_ORDER_ID
  AND C.LINE_NO=A.CUST_ORDER_LINE_NO
  AND C.PROMISE_DATE>=LAST_DAY(TIME_POINT)+1
  AND C.CUST_ORDER_ID=D.ID;
  
  -----出貨數據(結束),未出貨數據開始  SELECT ROUND(SUM((A.ORDER_QTY-A.TOTAL_SHIPPED_QTY)*A.UNIT_PRICE*FUN_GET_RATE(B.CUSTOMER_ID))) INTO U_BEFORE3_QTY
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=TRUNC(ADD_MONTHS(TIME_POINT,-3),'MONTH')
  AND A.PROMISE_DATE<TRUNC(ADD_MONTHS(TIME_POINT,-2),'MONTH')
  AND A.TOTAL_SHIPPED_QTY<A.ORDER_QTY
  AND A.CUST_ORDER_ID=B.ID
  AND B.STATUS<>'X';
  
  SELECT ROUND(SUM((A.ORDER_QTY-A.TOTAL_SHIPPED_QTY)*A.UNIT_PRICE*FUN_GET_RATE(B.CUSTOMER_ID))) INTO U_BEFORE2_QTY
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=TRUNC(ADD_MONTHS(TIME_POINT,-2),'MONTH')
  AND A.PROMISE_DATE<TRUNC(ADD_MONTHS(TIME_POINT,-1),'MONTH')
  AND A.TOTAL_SHIPPED_QTY<A.ORDER_QTY
  AND B.STATUS<>'X'
  AND A.CUST_ORDER_ID=B.ID;  SELECT ROUND(SUM((A.ORDER_QTY-A.TOTAL_SHIPPED_QTY)*A.UNIT_PRICE*FUN_GET_RATE(B.CUSTOMER_ID))) INTO U_BEFORE_QTY
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=TRUNC(ADD_MONTHS(TIME_POINT,-1),'MONTH')
  AND A.PROMISE_DATE<TRUNC(TIME_POINT,'MONTH')
  AND A.TOTAL_SHIPPED_QTY<A.ORDER_QTY
  AND B.STATUS<>'X'
  AND A.CUST_ORDER_ID=B.ID;  SELECT ROUND(SUM((A.ORDER_QTY-A.TOTAL_SHIPPED_QTY)*A.UNIT_PRICE*FUN_GET_RATE(B.CUSTOMER_ID))) INTO U_CUR_QTY
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=TRUNC(TIME_POINT,'MONTH')
  AND A.PROMISE_DATE<LAST_DAY(TIME_POINT)+1
  AND A.TOTAL_SHIPPED_QTY<A.ORDER_QTY
  AND B.STATUS<>'X'
  AND A.CUST_ORDER_ID=B.ID;
  
  ---未出貨金額(當月): 出貨總金額(當月): 截止到查詢時止, 統計截止日期所在月的未出貨金額
  ---未出貨金額(其他): 依此類推
  
  SELECT ROUND(SUM((A.ORDER_QTY-A.TOTAL_SHIPPED_QTY)*A.UNIT_PRICE*FUN_GET_RATE(B.CUSTOMER_ID))) INTO U_NEXT_QTY
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>=LAST_DAY(TIME_POINT)+1
  AND A.PROMISE_DATE<TRUNC(ADD_MONTHS(TIME_POINT,2),'MONTH')
  AND A.TOTAL_SHIPPED_QTY<A.ORDER_QTY
  AND B.STATUS<>'X'
  AND A.CUST_ORDER_ID=B.ID;  INSERT INTO SALES_TOTAL_AUTO(EXEC_DAY,
ORDER_BEFORE3QTY,
ORDER_BEFORE2QTY,
ORDER_BEFOREQTY,
ORDER_CURRENTQTY,
ORDER_NEXTQTY,
ORDER_BEFORE3SUM,
ORDER_BEFORE2SUM,
ORDER_BEFORESUM,
ORDER_CURRENTSUM,
ORDER_NEXTSUM,
SHIP_BEFORE3QTY,
SHIP_BEFORE2QTY,
SHIP_BEFOREQTY,
SHIP_CURRENTQTY,
SHIP_NEXTQTY,
SHIP_BEFORE3SUM,
SHIP_BEFORE2SUM,
SHIP_BEFORESUM,
SHIP_CURRENTSUM,
UNSHIP_BEFORE3QTY,
UNSHIP_BEFORE2QTY,
UNSHIP_BEFOREQTY,
UNSHIP_CURRENTQTY,
UNSHIP_NEXTQTY)
            VALUES(TRUNC(TIME_POINT),
   O_BEFORE3_QTY,
                   O_BEFORE2_QTY,
                   O_BEFORE_QTY,
                   O_CUR_QTY,
                   O_NEXT_QTY,
   O_BEFORE3_SUM,
                   O_BEFORE2_SUM,
                   O_BEFORE_SUM,
                   O_CUR_SUM,
                   O_NEXT_SUM,
   S_BEFORE3_QTY,
                   S_BEFORE2_QTY,
                   S_BEFORE_QTY,
                   S_CUR_QTY,
                   S_NEXT_QTY,
   S_BEFORE3_SUM,
                   S_BEFORE2_SUM,
                   S_BEFORE_SUM,
                   S_CUR_SUM,
   U_BEFORE3_QTY,
                   U_BEFORE2_QTY,
                   U_BEFORE_QTY,
                   U_CUR_QTY,
                   U_NEXT_QTY);   UPDATE SALES_TOTAL_AUTO SET UNSHIP_BEFORE3SUM=(SELECT ROUND((SUM(A.TOTAL_AMT_ORDERED*FUN_GET_RATE(B.CUSTOMER_ID))-SUM(A.TOTAL_AMT_SHIPPED*FUN_GET_RATE(B.CUSTOMER_ID))))
  FROM CUST_ORDER_LINE A,CUSTOMER_ORDER B
  WHERE A.PROMISE_DATE>TRUNC(TO_DATE('2002/06/01','YYYY/MM/DD'))
  AND A.PROMISE_DATE<TRUNC(ADD_MONTHS(TIME_POINT,-2),'MONTH')
  AND A.TOTAL_SHIPPED_QTY<A.ORDER_QTY
  AND A.CUST_ORDER_ID=B.ID
  AND B.STATUS<>'X')
  WHERE EXEC_DAY=TRUNC(TIME_POINT);  UPDATE SALES_TOTAL_AUTO SET UNSHIP_BEFORE2SUM=UNSHIP_BEFORE3SUM+UNSHIP_BEFORE2QTY WHERE EXEC_DAY=TRUNC(TIME_POINT);  UPDATE SALES_TOTAL_AUTO SET UNSHIP_BEFORESUM=UNSHIP_BEFORE2SUM+UNSHIP_BEFOREQTY WHERE EXEC_DAY=TRUNC(TIME_POINT);  UPDATE SALES_TOTAL_AUTO SET UNSHIP_CURRENTSUM=UNSHIP_BEFORESUM+UNSHIP_CURRENTQTY WHERE EXEC_DAY=TRUNC(TIME_POINT);
COMMIT;
END;

Answer 2

to 小包
现在ORACLE应用的好的么，向你学习！

Answer 3

大哥
oracle存储函数中
dyndql:='select var,a.aaa from aaa a'
变量var如何引用，谁知道，帮帮忙，谢谢了

Answer 4

大哥
oracle存储函数中
dyndql:='select var,a.aaa from aaa a'
变量var如何引用，谁知道，帮帮忙，谢谢了
Vdyndql:='select var,a.aaa from aaa a'
Execute immediate Vdynsql using var;
这样为什么不行？？？