昨天遇到一个在不同版本下,带参数游标打不开问题。代码如下
create or replace function F_Func(anCraftno in number,anGoodsid in number,anCRAFTOTAL IN NUMBER,anOLDCraftno in number,anOLDGoodsid in number) return number AS
v_newGOODSID Rule_Craft_Bom.Goodsid%TYPE;
v_oldGOODSID Rule_Craft_Bom.Goodsid%TYPE;
v_newCraftno Rule_Craft_Bom.Craftno%TYPE;
v_oldCraftno Rule_Craft_Bom.Craftno%TYPE;
v_newCraftotal Rule_Craft_Bom.CRAFTOTAL%TYPE;
v_oldCraftotal Rule_Craft_Bom.CRAFTOTAL%TYPE;
v_count number(10);
v_value number(10);
v_ishalfgoods number(10);
CURSOR C(ANCraftno IN NUMBER,ANGOODSID IN NUMBER) IS SELECT * FROM Rule_Craft_Bom WHERE Nvl(Craftno,0) = ANCraftno AND Goodsid = ANGOODSID;
BEGIN
v_newGOODSID := anGoodsid;
v_newCraftno := anCraftno;
v_newCraftotal := anCRAFTOTAL;
v_oldGOODSID := anOLDGoodsid;
v_oldCraftno := anOLDCraftno;
v_oldCraftotal := anCRAFTOTAL;
FOR T IN C(v_newCraftno,v_newGOODSID) LOOP
v_newGOODSID := t.sourartid;
v_newCraftno := 0;
v_newCraftotal := v_oldCraftotal*t.craftotal;
--查看没有工艺
BEGIN
select count(1) into v_count from Rule_Craft_Bom where Nvl(Craftno,0) = v_newCraftno AND Goodsid = v_newGOODSID;
EXCEPTION WHEN No_Data_Found THEN
v_count := 0;
END;
IF v_count = 0 THEN --没有子节点
UPDATE RULE_CRAFT_TBOMS SET CRAFTOTAL = CRAFTOTAL + v_newCraftotal
WHERE FIRMID = t.firmid AND CRAFTNO=v_oldCraftno AND
GOODSID = v_oldGOODSID AND SOURARTID = v_newGOODSID;
If Sql%RowCount = 0 Then
INSERT INTO RULE_CRAFT_TBOMS(FIRMID,CRAFTNO,GOODSID,SOURARTID,CRAFTOTAL,FACTORYID)
VALUES(T.FIRMID,v_oldCraftno,v_oldGOODSID,v_newGOODSID,v_newCraftotal,T.FACTORYID);
END IF;
ELSE --有子节点,就遍历
--判断是否需要生产
BEGIN
SELECT COUNT(1) INTO v_ishalfgoods FROM t_article WHERE genitemid = v_newGOODSID AND nvl(ishalfgoods,0) = 1;
EXCEPTION WHEN No_Data_Found THEN
v_ishalfgoods := 0;
END;
IF v_ishalfgoods > 0 THEN --需要单独生产
UPDATE RULE_CRAFT_TBOMS SET CRAFTOTAL = CRAFTOTAL + v_newCraftotal
WHERE FIRMID = t.firmid AND CRAFTNO=v_oldCraftno AND
GOODSID = v_oldGOODSID AND SOURARTID = v_newGOODSID;
If Sql%RowCount = 0 Then
INSERT INTO RULE_CRAFT_TBOMS(FIRMID,CRAFTNO,GOODSID,SOURARTID,CRAFTOTAL,FACTORYID)
VALUES(T.FIRMID,v_oldCraftno,v_oldGOODSID,v_newGOODSID,v_newCraftotal,T.FACTORYID);
END IF;
ELSE --不需要
--不用变量接值,编译过不了
v_value := F_Func(v_newCraftno,v_newGOODSID,v_newCraftotal,v_oldCraftno,v_oldGOODSID);
END IF;
END IF;
NULL;
END LOOP;
RETURN 0;
end F_Func;
在ORACLE 版本为11.1.0.7下能正常的打开游标,但在版本11.2.0.4下传两个参数时打不开。
头痛了很近 最后把 anCraftno in number 改为 anCraftno in Integer 就能正常打开了。
不知道是不是oracle的版本问题还是BUG