我这样定义了一个游标:
CURSOR in_cursor IS
SELECT A.vcStoreCode,B.vcStoreroomName,A.vcGoodsCode,
(TO_CHAR(A.dPrePostDate,'MM')||'月份') as vcMonth,A.fInNumber,A.fTotal
FROM V_BUY_INCOMEBILL_LIST A LEFT OUTER JOIN TB_BASE_STOREROOM B
ON A.vcStoreCode=B.vcStoreroomCode AND A.vcAspID=B.vcAspID
WHERE A.vcStatus IN ('正式启用','结清状态','未结清状态')
AND A.vcAspID=v_vcAspID AND A.fInNumber IS NOT NULL
AND B.vcStoreroomName LIKE v_vcStoreName+'%'
AND TO_CHAR(A.dPrePostDate,'YYYY')=v_vcYear;
在过程体中执行这个游标时:
OPEN in_cursor;
FETCH in_cursor INTO v_storeCode,v_storeName,v_goodsCode, v_month,v_fNum,v_fCost ;
执行到fetch时提示ora-01722 :无效数字
请问是不是定义游标时,select语句里不能包含视图呀?
CURSOR in_cursor IS
SELECT A.vcStoreCode,B.vcStoreroomName,A.vcGoodsCode,
(TO_CHAR(A.dPrePostDate,'MM')||'月份') as vcMonth,A.fInNumber,A.fTotal
FROM V_BUY_INCOMEBILL_LIST A LEFT OUTER JOIN TB_BASE_STOREROOM B
ON A.vcStoreCode=B.vcStoreroomCode AND A.vcAspID=B.vcAspID
WHERE A.vcStatus IN ('正式启用','结清状态','未结清状态')
AND A.vcAspID=v_vcAspID AND A.fInNumber IS NOT NULL
AND B.vcStoreroomName LIKE v_vcStoreName+'%'
AND TO_CHAR(A.dPrePostDate,'YYYY')=v_vcYear;
在过程体中执行这个游标时:
OPEN in_cursor;
FETCH in_cursor INTO v_storeCode,v_storeName,v_goodsCode, v_month,v_fNum,v_fCost ;
执行到fetch时提示ora-01722 :无效数字
请问是不是定义游标时,select语句里不能包含视图呀?
请检查一下游标返回的字段的类型是否跟fetch into 的一一匹配,特别int型
v_storeCode V_BUY_INCOMEBILL_LIST.v_storeCode%type; --不能用表别名如果你是上面这样定义的还有一个可能是,你得出的数值型结果超出了你定义的长度,
那就应该改成
v_fNum number;