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

[carrie820329]

一、课题的内容和要求：
1、 研究Oracle9i邮件程序与网站应用集成技术。
2、 在网站上实现基于Oracle9i邮件程序的自动转发。

二、设计的技术要求与数据（或论文主要内容）：
1、 论述Oracle9i邮件程序与网站应用集成技术及实际意义。
2、 论述Oracle9i邮件程序与网站应用集成技术的框架、模式原理、实现机制、开发方法等。
3、 论述Oracle9i邮件程序与网站应用集成的关键技术。
4、 基于Oracle9i邮件程序的自动转发应用程序实例

若有以上研究的，望发表见解或介绍相关资料及网站。谢谢！！！

[carrie820329]

to 包子
我看不懂上面的代码，可以介绍些基础的，或推荐些资料

[Petergepeter]

晕！

[baojianjun]

我做的一個例子，表和存儲過程，大家一起看看
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;

[baojianjun]

---出貨總金額(當月): 截止到查詢時止, 統計截止日期所在月的第一天到統計截止日期的出貨金額
---出貨總金額(其他): 依此類推, 各月的出貨金額

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;

[big_mouse]

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

[fieldsun]

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

[fieldsun]

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


Vdyndql:='select var,a.aaa from aaa a'
Execute immediate Vdynsql using var;
这样为什么不行？？？

[chentony]

對var加引號就行了