SQL> --入库表增加一列remain,入库时初始化与入库金额相等
SQL> select * from in_store;
PTY INDATE NO MONEY UNITPRICE BATCHNO REMAIN
--- ---------- ---- ---------- ---------- ---------- ----------
pvc 2011-05-20 6969 100000 2000 10 100000
pvc 2011-05-21 6969 50000 1000 10 50000
SQL> select * from out_store;
PTY OUTDATE NO MONEY
--- ---------- ---- ----------
pvc 2011-05-25 6969 50000
pvc 2011-05-26 6969 80000
SQL>
SQL> --创建临时表
SQL> create global temporary table OUT_STORE_TMP
2 (
3 ptype CHAR(3),
4 outdate DATE,
5 no CHAR(4),
6 money NUMBER,
7 unitprice NUMBER,
8 batchno NUMBER
9 )
10 on commit preserve rows;
表已创建。
SQL>
SQL> --创建存储过程
SQL> CREATE OR REPLACE PROCEDURE p_out_store(o OUT SYS_REFCURSOR) IS
2 v_money NUMBER;
3 CURSOR c1 IS
4 SELECT * FROM out_store o ORDER BY o.outdate;
5 CURSOR c2 IS
6 SELECT * FROM in_store i WHERE remain > 0 ORDER BY i.indate FOR UPDATE;
7 BEGIN
8 DELETE FROM out_store_tmp;
9 FOR i1 IN c1 LOOP
10 v_money := i1.money;
11 FOR i2 IN c2 LOOP
12 IF i1.ptype = i2.ptype AND i1.no = i2.no AND v_money > 0 THEN
13 IF v_money > i2.remain THEN
14 --插入出库临时表
15 INSERT INTO out_store_tmp
16 (ptype, outdate, no, money, unitprice, batchno)
17 VALUES
18 (i1.ptype, i1.outdate, i1.no, i2.remain, i2.unitprice,
19 i2.batchno);
20 --更新库存
21 UPDATE in_store a SET a.remain = 0 WHERE CURRENT OF c2;
22 v_money := v_money - i2.remain;
23 ELSE
24 INSERT INTO out_store_tmp
25 (ptype, outdate, no, money, unitprice, batchno)
26 VALUES
27 (i1.ptype, i1.outdate, i1.no, v_money, i2.unitprice, i2.batchno);
28 UPDATE in_store a
29 SET a.remain = a.remain - v_money
30 WHERE CURRENT OF c2;
31 v_money := 0;
32 EXIT;
33 END IF;
34 END IF;
35 END LOOP;
36 COMMIT;
37 END LOOP;
38 OPEN o FOR
39 SELECT * FROM out_store_tmp;
40 END;
41 /
过程已创建。
SQL>
SQL> var o refcursor
SQL>
SQL> --执行存储过程
SQL> exec p_out_store(:o);
PL/SQL 过程已成功完成。
SQL>
SQL> print o
PTY OUTDATE NO MONEY UNITPRICE BATCHNO
--- ---------- ---- ---------- ---------- ----------
pvc 2011-05-25 6969 50000 2000 10
pvc 2011-05-26 6969 50000 2000 10
pvc 2011-05-26 6969 30000 1000 10
