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;BEGINSELECT 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;高手帮看看,随便给优化一下。谢谢
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;BEGINSELECT 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;高手帮看看,随便给优化一下。谢谢
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;
我在 IF 里只写 一条update 就能编译,加上 其他的语句 就不行。
2 (APROVIDERID IN VARCHAR,
3 ACARDID IN VARCHAR,
4 ABOOKCOUNT IN NUMBER,
5 ABOOKPRICE IN NUMBER,
6 ATOTALMONEY IN NUMBER,
7 AOPERATION IN VARCHAR,
8 AREMARK IN VARCHAR)
9 AS
10 V_COUNT NUMBER;
11 V_BALANCE NUMBER;
12 BEGIN
13 SELECT COUNT(*) INTO V_COUNT
14 FROM "ACCOUNT"
15 WHERE PROVIDERID=APROVIDERID;
16 IF V_COUNT=0 THEN
17 INSERT INTO "ACCOUNT"(PROVIDERID,BALANCE) VALUES(APROVIDERID,0);
18 COMMIT; -------APPENDED
19 END IF;
20 SELECT BALANCE + ATOTALMONEY INTO V_BALANCE
21 FROM "ACCOUNT"
22 WHERE PROVIDERID = APROVIDERID;
23 IF V_BALANCE > 0 THEN
24 UPDATE "ACCOUNT" SET BALANCE = BALANCE + ATOTALMONEY WHERE PROVIDERID = APROVIDERID;
25 INSERT INTO WASTEBOOK(PROVIDERID,CARDID,BUYDATE,BOOKCOUNT,BOOKPRICE,TOTALMONEY,BALANCE,OPERATIO
26 VALUES(APROVIDERID,ACARDID,SYSDATE,ABOOKCOUNT,ABOOKPRICE,ATOTALMONEY,V_BALANCE,AOPERATION,AMREM
27 COMMIT; -------APPENDED
28 END IF;
29* END DEPOSIT;
30 /? 创建的过程带有编译错误。
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;
COMMIT; -------APPENDED
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;
CREATE TABLE "BSFF"."WASTEBOOK"("PROVIDERID" VARCHAR2(10) NOT
NULL, "CARDID" VARCHAR2(10) NOT NULL, "BUYDATE" DATE NOT NULL,
"BOOKCOUNT" NUMBER NOT NULL, "BOOKPRICE" NUMBER(18, 2) NOT NULL,
"TOTALMONEY" NUMBER(18, 2) NOT NULL, "BALANCE" NUMBER(18, 2) NOT
NULL, "OPERATION" VARCHAR2(20) NOT NULL, "REMARK" VARCHAR2(1000)
NOT NULL)
CREATE OR REPLACE PROCEDURE "BSFF"."DEPOSIT" (
APROVIDERID IN VARCHAR2,
ACARDID IN VARCHAR2,
ABOOKCOUNT IN NUMBER,
ABOOKPRICE IN NUMBER,
ATOTALMONEY IN NUMBER,
AOPERATION IN VARCHAR2,
AREMARK IN VARCHAR2)
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 "ACCOUNT"(PROVIDERID,BALANCE) VALUES(APROVIDERID,0);
--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;
INSERT INTO "WASTEBOOK"(PROVIDERID,CARDID,BUYDATE,BOOKCOUNT,BOOKPRICE,TOTALMONEY,BALANCE,OPERATION,REMARK)
VALUES('1','1',SYSDATE,1,1,1,1,'1','1');
不使用参数的值就可以。
(APROVIDERID IN VARCHAR2, --modify
ACARDID IN VARCHAR2, --modify
ABOOKCOUNT IN NUMBER,
ABOOKPRICE IN NUMBER,
ATOTALMONEY IN NUMBER,
AOPERATION IN VARCHAR2, --modify
AREMARK IN VARCHAR2) --modify
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;
COMMIT; -------APPENDED
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;
CREATE TABLE "BSFF"."WASTEBOOK"
("PROVIDERID" VARCHAR2(10) NOT NULL,
"CARDID" VARCHAR2(10) NOT NULL,
"BUYDATE" DATE NOT NULL,
"BOOKCOUNT" NUMBER NOT NULL,
"BOOKPRICE" NUMBER(18, 2) NOT NULL,
"TOTALMONEY" NUMBER(18, 2) NOT NULL,
"BALANCE" NUMBER(18, 2) NOT NULL,
"OPERATION" VARCHAR2(20) NOT NULL,
"REMARK" VARCHAR2(1000) NOT NULL)
参数定义
(APROVIDERID IN VARCHAR,
ACARDID IN VARCHAR,
ABOOKCOUNT IN NUMBER,
ABOOKPRICE IN NUMBER,
ATOTALMONEY IN NUMBER,
AOPERATION IN VARCHAR,
AREMARK IN VARCHAR)
是不是 定义的类型不一样 ?
(APROVIDERID IN VARCHAR2, --modify
ACARDID IN VARCHAR2, --modify
ABOOKCOUNT IN NUMBER,
ABOOKPRICE IN NUMBER,
ATOTALMONEY IN NUMBER,
AOPERATION IN VARCHAR2, --modify
AREMARK IN VARCHAR2) --modify
AS
V_COUNT NUMBER;
V_BALANCE NUMBER;BEGIN
--input checking
IF APROVIDERID IS NULL OR
ACARDID IS NULL OR
ABOOKCOUNT IS NULL OR
ABOOKPRICE IS NULL OR
ATOTALMONEY IS NULL OR
AOPERATION IS NULL OR
AREMARK IS NULL
THEN
RETURN;
END IF;
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;
COMMIT; -------APPENDED
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;
谢谢你哦。以后还要多多请教