CREATE OR REPLACE PROCEDURE NHKDATA.GETSYAININFO_(
i_cid IN SYAIN.KIGYO_CODE%TYPE,
i_uid IN SYAIN.IDCODE%TYPE,
o_username OUT VARCHAR2,
outStr OUT VARCHAR2
)IS
row_syain SYAIN%rowtype;
v_A KINMU_MONTHLY.NENGETSU%TYPE;
flag NUMBER := 0;
/*一覧*/
CURSOR cur_syainRireki(
ic_cid SYAIN.KIGYO_CODE%TYPE,
ic_uid SYAIN.IDCODE%TYPE
) IS
SELECT KINMU_MONTHLY.NENGETSU AS V_NENGETSU
FROM KINMU_MONTHLY LEFT JOIN KINMU_GOKEI
ON KINMU_MONTHLY.IDCODE = KINMU_GOKEI.IDCODE AND
KINMU_MONTHLY.KIGYO_CODE = KINMU_GOKEI.KIGYO_CODE AND
KINMU_MONTHLY.NENGETSU = KINMU_GOKEI.NENGETSU
WHERE KINMU_MONTHLY.IDCODE = ic_uid AND
KINMU_MONTHLY.KIGYO_CODE = ic_cid;
BEGIN
OPEN cur_syainRireki(i_cid,i_uid);
LOOP
FETCH cur_syainRireki.NENGETSU INTO v_A;
EXIT WHEN cur_syainRireki%NOTFOUND;
outStr := outStr || '&&NENGETSU' || flag || '::' ||v_A;
flag := flag + 1;
END LOOP;
outStr := outStr || '&&COUNT::' || to_char(flag); CLOSE cur_syainRireki; --RETURN;
EXCEPTION
WHEN OTHERS THEN
RAISE;END GETSYAININFO_;
/
为什么以上代码老是报PLS-002255错误???
厚着脸皮请教浪哥和诸位大侠,嘿嘿
i_cid IN SYAIN.KIGYO_CODE%TYPE,
i_uid IN SYAIN.IDCODE%TYPE,
o_username OUT VARCHAR2,
outStr OUT VARCHAR2
)IS
row_syain SYAIN%rowtype;
v_A KINMU_MONTHLY.NENGETSU%TYPE;
flag NUMBER := 0;
/*一覧*/
CURSOR cur_syainRireki(
ic_cid SYAIN.KIGYO_CODE%TYPE,
ic_uid SYAIN.IDCODE%TYPE
) IS
SELECT KINMU_MONTHLY.NENGETSU AS V_NENGETSU
FROM KINMU_MONTHLY LEFT JOIN KINMU_GOKEI
ON KINMU_MONTHLY.IDCODE = KINMU_GOKEI.IDCODE AND
KINMU_MONTHLY.KIGYO_CODE = KINMU_GOKEI.KIGYO_CODE AND
KINMU_MONTHLY.NENGETSU = KINMU_GOKEI.NENGETSU
WHERE KINMU_MONTHLY.IDCODE = ic_uid AND
KINMU_MONTHLY.KIGYO_CODE = ic_cid;
BEGIN
OPEN cur_syainRireki(i_cid,i_uid);
LOOP
FETCH cur_syainRireki.NENGETSU INTO v_A;
EXIT WHEN cur_syainRireki%NOTFOUND;
outStr := outStr || '&&NENGETSU' || flag || '::' ||v_A;
flag := flag + 1;
END LOOP;
outStr := outStr || '&&COUNT::' || to_char(flag); CLOSE cur_syainRireki; --RETURN;
EXCEPTION
WHEN OTHERS THEN
RAISE;END GETSYAININFO_;
/
为什么以上代码老是报PLS-002255错误???
厚着脸皮请教浪哥和诸位大侠,嘿嘿
还有CURSOR cur_syainRireki(
ic_cid SYAIN.KIGYO_CODE%TYPE,
ic_uid SYAIN.IDCODE%TYPE
)
从代码上看应该和KINMU_MONTHLY匹配,两者类型是否相符
SYAIN表却没有出现
SELECT KINMU_MONTHLY.NENGETSU AS V_NENGETSU
FROM KINMU_MONTHLY LEFT JOIN KINMU_GOKEI
既然只取KINMU_MONTHLY.NENGETSU 的值,为什么要left join,看起来多此一举
用for cur1 in cursor loop 来写试试
CREATE OR REPLACE PROCEDURE NHKDATA.GETSYAININFO_(
i_cid IN SYAIN.KIGYO_CODE%TYPE,
i_uid IN SYAIN.IDCODE%TYPE,
o_username OUT VARCHAR2,
outStr OUT VARCHAR2
)IS
row_syain SYAIN%rowtype;
v_A KINMU_MONTHLY.NENGETSU%TYPE;
flag NUMBER := 0;
/*一覧*/
CURSOR cur_syainRireki(
ic_cid SYAIN.KIGYO_CODE%TYPE,
ic_uid SYAIN.IDCODE%TYPE
) IS
SELECT KINMU_MONTHLY.NENGETSU AS V_NENGETSU
FROM KINMU_MONTHLY LEFT JOIN KINMU_GOKEI
ON KINMU_MONTHLY.IDCODE = KINMU_GOKEI.IDCODE AND
KINMU_MONTHLY.KIGYO_CODE = KINMU_GOKEI.KIGYO_CODE AND
KINMU_MONTHLY.NENGETSU = KINMU_GOKEI.NENGETSU
WHERE KINMU_MONTHLY.IDCODE = ic_uid AND
KINMU_MONTHLY.KIGYO_CODE = ic_cid;
BEGIN
for cur1 in cur_syainRireki(i_cid,i_uid)
LOOP
v_A:=cur1.V_NENGETSU;
outStr := outStr || '&&NENGETSU' || flag || '::' ||v_A;
flag := flag + 1;
END LOOP;
outStr := outStr || '&&COUNT::' || to_char(flag);
--RETURN;
EXCEPTION
WHEN OTHERS THEN
RAISE; END GETSYAININFO_;
你是不是忘了去掉close cur_syainRireki了
如果不是这个问题的话,看看游标定义的查询是否正确
为什么这个东西会报错!?!?!?!?!?!!?
本身已经隐含了open cursor、fetch..和close
所以你多加了close会出错的