存储过程错在哪里？

[zengwujun]

下面的存储过程出现编译错误，请问错在哪里，谢谢。
CREATE PROCEDURE test_pro
(iBegin IN NUMBER,
iEnd IN NUMBER,
summoney OUT NUMBER)
AS BEGIN
SELECT summoney = SUM(DATAVALUE)
FROM BASICDATAAMR_TAB
WHERE rownum <= iEnd
minus
SELECT summoney = SUM(DATAVALUE)
FROM BASICDATAAMR_TAB
WHERE rownum <= iBegin
END

[bzszp]

try:

CREATE PROCEDURE test_pro
(iBegin IN NUMBER,
iEnd IN NUMBER,
summoney OUT NUMBER)
AS
num1 number;
num2 number;
BEGIN
SELECT SUM(DATAVALUE) into num1
FROM BASICDATAAMR_TAB
WHERE rownum <= iEnd;

SELECT SUM(DATAVALUE) into num2
FROM BASICDATAAMR_TAB
WHERE rownum <= iBegin;

summoney :=num1-num2;
END;

[zengwujun]

楼上的，你的编译通过了，那我的过程错在哪里，为什么要这样做？

谢谢回答

[chao778899]

你所用的语法是SQL SERVER里边的,在ORACLE里边给变量赋值只能用SELECT COL1 INTO BIANLIANG1
这种语句

[ORARichard]

SELECT summoney = SUM(DATAVALUE)
FROM BASICDATAAMR_TAB
WHERE rownum <= iEnd
minus
SELECT summoney = SUM(DATAVALUE)
FROM BASICDATAAMR_TAB
WHERE rownum <= iBegin
------------------------------
楼主这段代码想得到什么结果，我觉得在SQLPLUS中执行都不会得到你想要的

[zengwujun]

怎么执行并传递参数呢？

[CodeMagic]

CREATE PROCEDURE test_pro (iBegin IN NUMBER,iEnd IN NUMBER,summoney OUT NUMBER) is
i number;
j number;
BEGIN
SELECT SUM(DATAVALUE) into i FROM BASICDATAAMR_TAB WHERE rownum <= iEnd;
SELECT SUM(DATAVALUE) into j FROM BASICDATAAMR_TAB WHERE rownum <= iBegin;

summoney:=i-j;
END

[ORARichard]

minus的用法你知道吗？你是想用minus还是"-"减号

[zengwujun]

想得到的结果是：
iBegin到iEnd行之间DATAVALUE的总量

[zengwujun]

minus不就是集合减的意思么

[ORARichard]

select sum(DATAVALUE) into summoney from (SELECT DATAVALUEFROM BASICDATAAMR_TAB
WHERE rownum <= iEnd
minus
SELECT DATAVALUE FROM BASICDATAAMR_TAB
WHERE rownum <= iBegin)

[zengwujun]

to:CodeMagic(ErrorDetector)

你的过程有错误

[ORARichard]

那应该是这样的，先用minus取结果集，再用SUM取和

[zengwujun]

SELECT SUM(DATAVALUE) FROM BASICDATAAMR_TAB
WHERE rownum <= 20
MINUS
SELECT SUM(DATAVALUE) FROM BASICDATAAMR_TAB
WHERE rownum <= 11

同样可以得到结果的

[yown]

CREATE PROCEDURE test_pro
(iBegin IN NUMBER,
iEnd IN NUMBER,
summoney OUT NUMBER)
IS
i:number;
j:number;
BEGIN
....
END

你是两个变量相减,怎么用minus?

[zengwujun]

to:CodeMagic(ErrorDetector)

is 改 as可以了，多谢，呵呵

[zengwujun]

啊，我的minus 还真是错了呢，刚才验证了，只得到前iEnd之和.

[zengwujun]

要揭贴了，我的QQ：23607128，上海，等到11点半