本文是我在前一篇文章提到的关于先进先出存储过程,针对Oracle 9i 的修改版,基本思路一致,只是修改了关键字使用方法。
有关数据库和存储过程的设计,请参阅使用存储过程实现进销存系统中的先进先出 算法(1)——数据库与存储过程设计
使用Sql Server? 前往。
点击下载Oracle 9i 存储过程 下载
-
-
-
-
-
CREATE OR REPLACE PROCEDURE pro_ProductSM(
-
SorM IN char,
-
pId IN productsm.pid%type,
-
-
marketPrice IN nvarchar2,
-
marketNum IN nvarchar2,
-
-
stockPrice IN nvarchar2,
-
stockNum IN nvarchar2
-
) IS
-
BEGIN
-
-
DECLARE
-
stockNo int;
-
marketNo int;
-
updateSQL nvarchar2(200);
-
numx nvarchar2(10);
-
costx nvarchar2(10);
-
-
num1 numeric(6);
-
num2 numeric(6);
-
num3 numeric(6);
-
num4 numeric(6);
-
num5 numeric(6);
-
num6 numeric(6);
-
totalNum numeric(7);
-
-
cost1 numeric(5, 2);
-
cost2 numeric(5, 2);
-
cost3 numeric(5, 2);
-
cost4 numeric(5, 2);
-
cost5 numeric(5, 2);
-
cost6 numeric(5, 2);
-
-
flag int;
-
-
-
thisWant numeric(6);
-
thisNum numeric(6);
-
thisCost numeric(5, 2);
-
-
money numeric(5, 2);
-
-
-
-
-
BEGIN
-
-
IF SorM = 'S' THEN
-
-
BEGIN
-
-
-
-
DBMS_OUTPUT.PUT_LINE('INFO:You will buy somethings-------price is' ||
-
stockPrice || '---nums is ' || stockNum ||
-
'---pid is ' || pId);
-
-
SELECT stockNo, marketNo
-
INTO stockNo, marketNo
-
FROM ProductSM
-
WHERE pId = pId;
-
-
IF stockNo >= 7 THEN
-
-
DBMS_OUTPUT.PUT_LINE('All storages have full, can not buy!');
-
-
ELSE
-
BEGIN
-
-
DBMS_OUTPUT.PUT_LINE('INFO:BEGIN UPDATE THE DB WHERE PID IS' || pId);
-
-
-
numx := 'num' || TO_CHAR(stockNo);
-
costx := 'cost' || stockNo;
-
-
updateSQL := 'UPDATE ProductSM SET ' || numx || '=' || stockNum || ', ' ||
-
costx || '=' || stockPrice ||
-
', stockNo=stockNo+1 WHERE pId=' || pId;
-
EXECUTE IMMEDIATE TO_CHAR(updateSQL);
-
-
-
IF marketNo = 0 THEN
-
-
BEGIN
-
updateSQL := 'UPDATE ProductSM SET marketNo = 1 WHERE pId =' || pId;
-
EXECUTE IMMEDIATE TO_CHAR(updateSQL);
-
-
END;
-
END IF;
-
-
DBMS_OUTPUT.PUT_LINE('INFO:You buy something successfully!!'
-
||'-------------stockPrice is' ||
-
stockPrice || ',---pid is ' || pId ||
-
'----stockNum is' || stockNum);
-
-
END;
-
-
END IF;
-
-
END;
-
-
-
-
-
ELSIF SorM = 'M' THEN
-
-
BEGIN
-
-
-
DBMS_OUTPUT.PUT_LINE('INFO:You will sell somethings-------price is' ||
-
marketPrice || '---nums is ' || marketNum ||
-
'---pid is ' || pId);
-
-
-
SELECT stockNo, marketNo
-
INTO stockNo, marketNo
-
FROM ProductSM
-
WHERE pId = pId;
-
-
IF marketNo > stockNo THEN
-
-
BEGIN
-
DBMS_OUTPUT.PUT_LINE('出售编号大于进货编号,怎么可能?我得去仓库看看了。');
-
END;
-
ELSE
-
BEGIN
-
-
-
DBMS_OUTPUT.PUT_LINE('Storages INFO:----stockNo is' ||
-
TO_CHAR(stockNo) || ' -------marketNo is' ||
- TO_CHAR(marketNo));
-
-
SELECT num1,
- cost1,
- num2,
- cost2,
- num3,
- cost3,
- num4,
- cost4,
- num5,
- cost5,
- num6,
- cost6
-
INTO num1,
- cost1,
- num2,
- cost2,
- num3,
- cost3,
- num4,
- cost4,
- num5,
- cost5,
- num6,
- cost6
-
FROM ProductSM
-
WHERE pId = pId;
-
- totalNum := num1 + num2 + num3 + num4 + num5 + num6;
-
-
-
IF totalNum < marketNum THEN
-
-
DBMS_OUTPUT.PUT_LINE('不好意思,你的需求过大,交易失败');
-
-
ELSE
-
BEGIN
-
-
-
SELECT marketNo
-
INTO marketNo
-
FROM ProductSM
-
WHERE pId = pId;
-
- flag := 0;
- thisWant := marketNum;
-
- WHILE flag = 0 LOOP
-
-
-
SELECT marketNo
-
INTO marketNo
-
FROM ProductSM
-
WHERE pId = pId;
-
-
IF marketNo = 1 THEN
-
-
BEGIN
- thisNum := num1;
- thisCost := cost1;
-
numx := 'num1';
-
costx := 'cost1';
-
END;
-
ELSIF marketNo = 2 THEN
-
BEGIN
- thisNum := num2;
- thisCost := cost2;
-
numx := 'num2';
-
costx := 'cost2';
-
END;
-
ELSIF marketNo = 3 THEN
-
BEGIN
- thisNum := num3;
- thisCost := cost3;
-
numx := 'num3';
-
costx := 'cost3';
-
END;
-
ELSIF marketNo = 4 THEN
-
BEGIN
- thisNum := num4;
- thisCost := cost4;
-
numx := 'num4';
-
costx := 'cost4';
-
END;
-
ELSIF marketNo = 5 THEN
-
BEGIN
- thisNum := num5;
- thisCost := cost5;
-
numx := 'num5';
-
costx := 'cost5';
-
END;
-
ELSIF marketNo = 6 THEN
-
BEGIN
- thisNum := num6;
- thisCost := cost6;
-
numx := 'num6';
-
costx := 'cost6';
-
END;
-
END IF;
-
-
-
-
IF thisWant < thisNum THEN
-
-
BEGIN
-
-
DBMS_OUTPUT.PUT_LINE('INFO:you sell something-------marketPrice is ' ||
-
marketPrice || ' ----cost is' ||
- TO_CHAR(thisCost) ||
-
'----pid is' || pId ||
-
'------num is' ||
- TO_CHAR(thisWant));
-
- money := (marketPrice - thisCost) * thisWant;
-
DBMS_OUTPUT.PUT_LINE('money you gain is:' ||
- TO_CHAR(money));
-
-
updateSQL := 'UPDATE ProductSM SET ' || numx || '=' ||
-
TO_CHAR(numx) || '-' ||
-
TO_CHAR(thisWant) || ' WHERE pId=' || pId;
-
EXECUTE IMMEDIATE TO_CHAR(updateSQL);
-
-
thisWant := 0;
-
flag := 1;
-
-
END;
-
ELSIF thisWant = thisNum THEN
-
-
BEGIN
-
-
-
DBMS_OUTPUT.PUT_LINE('INFO:you sell something-------marketPrice is ' ||
-
marketPrice || ' ----cost is' ||
- TO_CHAR(thisCost) ||
-
'----pid is' || pId ||
-
'------num is' ||
- TO_CHAR(thisWant));
-
- money := (marketPrice - thisCost) * thisWant;
-
DBMS_OUTPUT.PUT_LINE('money you gain is:' ||
- TO_CHAR(money));
-
-
updateSQL := 'UPDATE ProductSM SET marketNo = marketNo + 1,' || numx +
-
'=0,' || costx || '=0 WHERE pId=' || pId;
-
EXECUTE IMMEDIATE TO_CHAR(updateSQL);
-
-
thisWant := 0;
-
flag := 1;
-
-
END;
-
-
ELSE
-
-
BEGIN
-
-
-
DBMS_OUTPUT.PUT_LINE('INFO:you sell something-------marketPrice is ' ||
-
marketPrice || ' ----cost is' ||
- TO_CHAR(thisCost) ||
-
'----pid is' || pId ||
-
'------num is' ||
- TO_CHAR(thisNum));
-
- money := (marketPrice - thisCost) * thisNum;
-
DBMS_OUTPUT.PUT_LINE('money you gain is:' ||
- TO_CHAR(money));
-
-
updateSQL := 'UPDATE ProductSM SET marketNo = marketNo + 1,' || numx +
-
'=0,' || costx + '=0 WHERE pId=' || pId;
-
EXECUTE IMMEDIATE TO_CHAR(updateSQL);
-
-
- thisWant := thisWant - thisNum;
-
-
END;
-
-
END IF;
-
-
END LOOP;
-
-
END;
-
END IF;
-
-
END;
-
END IF;
-
-
END;
-
END IF;
-
-
END;
-
-
END;
本文转自 nileader 51CTO博客,原文链接:http://blog.51cto.com/nileader/304396,如需转载请自行联系原作者