求救，procedure

显示全部楼层 · 2007-8-6 15:19:01

以下这个程序总是编译不通过，急啊，请各位大侠指点
CREATE OR REPLACE PROCEDURE tb_income_expense_day2(SG_BALANCEDATE NUMBER)
IS
BEGIN
insert intosginfo.tb_income_expense_day(DEPTNAME,PURPOSE,BALANCEDATE,ACC,CASH,CASHORT)
SELECT a.DEPTNAME,b.PURPOSE,a.BALANCEDATE,sum(a.ACC),sum(a.CASH),a.CASHSORT
from sginfo.tb_income_expense a,sginfo.tb_purpose b
where a.BALANCEDATE=SG_BALANCEDATE,a.PURPOSEid=b.ID
GROUP BY a.CASHSORT,a.DEPTNAME,b.PURPOSE,a.ID,a.BALANCEDATE;

end tb_income_expense_day2;

千问 · 2007-8-6 15:19:01

报错信息？

千问 · 2007-8-6 15:19:01

报错是： where 条件出错

千问 · 2007-8-6 15:19:01

group和select不匹配.

千问 · 2007-8-6 15:19:01

我已经尝试了，还是不行，大家帮帮忙

千问 · 2007-8-6 15:19:01

a.BALANCEDATE=SG_BALANCEDATE,a.PURPOSEid=b.ID
-=================
a.BALANCEDATE=SG_BALANCEDATE anda.PURPOSEid=b.ID

千问 · 2007-8-6 15:19:01

我把AND加到where里了，但又出现表不存在，我经过检查表名没错啊，那有怎么回事啊？

千问 · 2007-8-6 15:19:01

把错误贴出来.

千问 · 2007-8-6 15:19:01

sql>CREATE OR REPLACE PROCEDURE tb_income_expense_day2(SG_BALANCEDATE NUMBER)
IS
BEGIN
insert into sginfo.tb_income_expense_day(DEPTNAME,PURPOSE,BALANCEDATE,ACC,CASH,CASHSORT)
SELECT a.DEPTNAME,b.PURPOSE,a.BALANCEDATE,sum(a.ACC),sum(a.CASH),a.CASHSORT
from sginfo.tb_income_expense a,sginfo.tb_purpose b
where a.BALANCEDATE=SG_BALANCEDATE and a.PURPOSEid=b.ID
GROUP BY a.CASHSORT,a.DEPTNAME,b.PURPOSE,a.ID,a.BALANCEDATE;
end tb_income_expense_day2;
错误提示：
6 pl/sql

ra--00942:table or view does not exist
4 pl/sql:SQL statement ignored

千问 · 2007-8-6 15:19:01

最初由 wto0228 发布
[B]sql>CREATE OR REPLACE PROCEDURE tb_income_expense_day2(SG_BALANCEDATE NUMBER)
IS
BEGIN
insert into sginfo.tb_income_expense_day(DEPTNAME,PURPOSE,BALANCEDATE,ACC,CASH,CASHSORT)
SELECT a.DEPTNAME,b.PURPOSE,a.BALANCEDATE,sum(a.ACC),sum(a.CASH),a.CASHSORT
from sginfo.tb_income_expense a,sginfo.tb_purpose b
where a.BALANCEDATE=SG_BALANCEDATE and a.PURPOSEid=b.ID
GROUP BY a.CASHSORT,a.DEPTNAME,b.PURPOSE,a.ID,a.BALANCEDATE;
end tb_income_expense_day2;
错误提示：
6 pl/sql

ra--00942:table or view does not exist
4 pl/sql:SQL statement ignored [/B]
显示将 sginfo.tb_income_expense,sginfo.tb_purpose的select 权限授给当前用户.