CREATE OR REPLACE PROCEDURE "BSFF ". "DEPOSIT " (APROVIDERID IN
VARCHAR,ACARDID IN VARCHAR,ABOOKCOUNT IN NUMBER,ABOOKPRICE IN
NUMBER,ATOTALMONEY IN NUMBER,AOPERATION IN VARCHAR,AREMARK IN VARCHAR)
AS
V_COUNT NUMBER;
V_BALANCE NUMBER;
BEGIN
SELECT COUNT(*) INTO V_COUNT FROM "ACCOUNT " WHERE PROVIDERID=APROVIDERID;
IF V_COUNT=0 THEN
INSERT INTO "ACCOUNT "(PROVIDERID,BALANCE) VALUES(APROVIDERID,0);
END IF;
SELECT BALANCE + ATOTALMONEY INTO V_BALANCE FROM "ACCOUNT " WHERE PROVIDERID = APROVIDERID;
IF V_BALANCE > 0 THEN
UPDATE "ACCOUNT " SET BALANCE = BALANCE + ATOTALMONEY WHERE PROVIDERID = APROVIDERID;
INSERT INTO WASTEBOOK(PROVIDERID,CARDID,BUYDATE,BOOKCOUNT,BOOKPRICE,TOTALMONEY,BALANCE,OPERATION,REMARK)
VALUES(APROVIDERID,ACARDID,SYSDATE,ABOOKCOUNT,ABOOKPRICE,ATOTALMONEY,V_BALANCE,AOPERATION,AMREMARK);
END IF;
END DEPOSIT;
高手帮看看,随便给优化一下。谢谢
------解决方案--------------------
未commit
CREATE OR REPLACE PROCEDURE "BSFF ". "DEPOSIT "
(APROVIDERID IN VARCHAR,
ACARDID IN VARCHAR,
ABOOKCOUNT IN NUMBER,
ABOOKPRICE IN NUMBER,
ATOTALMONEY IN NUMBER,
AOPERATION IN VARCHAR,
AREMARK IN VARCHAR)
AS
V_COUNT NUMBER;
V_BALANCE NUMBER;
BEGIN
SELECT COUNT(*) INTO V_COUNT
FROM "ACCOUNT "
WHERE PROVIDERID=APROVIDERID;
IF V_COUNT=0 THEN
INSERT INTO "ACCOUNT "(PROVIDERID,BALANCE) VALUES(APROVIDERID,0);
COMMIT; -------APPENDED
END IF;
SELECT BALANCE + ATOTALMONEY INTO V_BALANCE
FROM "ACCOUNT "
WHERE PROVIDERID = APROVIDERID;
IF V_BALANCE > 0 THEN
UPDATE "ACCOUNT " SET BALANCE = BALANCE + ATOTALMONEY WHERE PROVIDERID = APROVIDERID;
INSERT INTO WASTEBOOK(PROVIDERID,CARDID,BUYDATE,BOOKCOUNT,BOOKPRICE,TOTALMONEY,BALANCE,OPERATION,REMARK)
VALUES(APROVIDERID,ACARDID,SYSDATE,ABOOKCOUNT,ABOOKPRICE,ATOTALMONEY,V_BALANCE,AOPERATION,AMREMARK);
COMMIT; -------APPENDED
END IF;
END DEPOSIT;