编译的时候temp_detailAccount是不存在的 不报错误么 INSERT INTO temp_detailAccount... 写成一个动态的sql语句 EXECUTE IMMEDIATE ..来执行insert,应该没有错误了八
INSERT INTO temp_detailAccount(account_detailDate,account_billNo,account_abstract,in_amount,in_money) SELECT A.detailDate,A.abstract,A.billNo,SUM(A.amount),SUM(B.realPrice*A.amount) FROM detailAccount A,barCode B WHERE A.barCode=B.barCode; --;是全角的
oracle中的临时表可以先建好 用起来也方便啊
en 我只能先建一个表了,谢谢大家。另外icedut(冰)你说在同一个过程里,建临时表以后,动态插入就可以吗?我试了怎么不行?表还是不存在 我现在只能单独写一个建表过程,然后再调用了 ,难道就不能放在一个过程里吗?
CREATE OR REPLACE PROCEDURE ll04 is ss varchar2(200); BEGIN ss:='CREATE GLOBAL TEMPORARY TABLE temp_ll04 (s_no int,sname varchar2(100) ) ON COMMIT PRESERVE ROWS'; execute immediate ss; ss:='insert into temp_ll04 values(1,''aa'')'; execute immediate ss; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN -- Consider logging the error and then re-raise RAISE; END ll04;--test begin ll04 ; end; select * from temp_ll04;--结果正确阿,你肯定是没有写对
SELECT SUM(A.amount) AS sum_amount,SUM(B.realPrice*A.amount) AS sum_money FROM detailAccount A,barCode B WHERE A.barCode=B.barCode AND A.detailDate=d_nearbyaidDate AND A.materialID=d_InputMaterialID AND A.outin='结转'; 还有这句怎么说少INTO子句阿,这样写不对吗?望指教!
SELECT SUM(A.amount) AS sum_amount,SUM(B.realPrice*A.amount) AS sum_money FROM detailAccount A,barCode B WHERE A.barCode=B.barCode AND A.detailDate=d_nearbyaidDate AND A.materialID=d_InputMaterialID AND A.outin='结转';-- 如果在存储过程中 select后面要有into的,或者前面有insert into...,
我把as改为into有提示说 不能用分组函数整整1天没有找到原因
SELECT SUM(A.amount) AS sum_amount,SUM(B.realPrice*A.amount) AS sum_money FROM detailAccount A,barCode B WHERE A.barCode=B.barCode AND A.detailDate=d_nearbyaidDate AND A.materialID=d_InputMaterialID AND A.outin='结转';-- sum_amount sum_money 如果是你要的变量 可以用动态执行sql的方法得到变量
begin sum_amount number(20); sum_money number(20); v_sql varchar2(200); v_p varchar2(100); v_p:='结转'; v_sql:='SELECT SUM(A.amount) ,SUM(B.realPrice*A.amount) FROM detailAccount A,barCode B WHERE A.barCode=B.barCode AND A.detailDate=d_nearbyaidDate AND A.materialID=d_InputMaterialID AND A.outin=:p1 '; execute immediate v_sql into sum_amount,sum_money using v_p; end;--我没有测试,不知道是否可以执行
问题解决了 原来在存储过程中 不能select sum(a.amount) into sum_amount,sum(b.price*a.amount) into sum_money from t 应该select sum(a.amount),sum(b.price*a.amount) into sum_amount, sum_money from t 这样就可以 谢谢 icedut(冰)
不报错误么
INSERT INTO temp_detailAccount... 写成一个动态的sql语句
EXECUTE IMMEDIATE ..来执行insert,应该没有错误了八
SELECT A.detailDate,A.abstract,A.billNo,SUM(A.amount),SUM(B.realPrice*A.amount) FROM detailAccount A,barCode B WHERE A.barCode=B.barCode; --;是全角的
用起来也方便啊
我现在只能单独写一个建表过程,然后再调用了 ,难道就不能放在一个过程里吗?
ss varchar2(200);
BEGIN
ss:='CREATE GLOBAL TEMPORARY TABLE temp_ll04
(s_no int,sname varchar2(100)
) ON COMMIT PRESERVE ROWS';
execute immediate ss;
ss:='insert into temp_ll04 values(1,''aa'')';
execute immediate ss;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END ll04;--test
begin
ll04 ;
end;
select * from temp_ll04;--结果正确阿,你肯定是没有写对
WHERE A.barCode=B.barCode AND A.detailDate=d_nearbyaidDate AND A.materialID=d_InputMaterialID AND A.outin='结转';
还有这句怎么说少INTO子句阿,这样写不对吗?望指教!
WHERE A.barCode=B.barCode AND A.detailDate=d_nearbyaidDate AND A.materialID=d_InputMaterialID AND A.outin='结转';--
如果在存储过程中
select后面要有into的,或者前面有insert into...,
WHERE A.barCode=B.barCode AND A.detailDate=d_nearbyaidDate AND A.materialID=d_InputMaterialID AND A.outin='结转';--
sum_amount
sum_money
如果是你要的变量
可以用动态执行sql的方法得到变量
sum_amount number(20);
sum_money number(20);
v_sql varchar2(200);
v_p varchar2(100);
v_p:='结转';
v_sql:='SELECT SUM(A.amount) ,SUM(B.realPrice*A.amount) FROM detailAccount A,barCode B
WHERE A.barCode=B.barCode AND A.detailDate=d_nearbyaidDate AND A.materialID=d_InputMaterialID AND A.outin=:p1 ';
execute immediate v_sql into
sum_amount,sum_money
using v_p;
end;--我没有测试,不知道是否可以执行
应该select sum(a.amount),sum(b.price*a.amount) into sum_amount, sum_money from t
这样就可以
谢谢 icedut(冰)