存储过程报错

zjl8008 2016-03-09 04:37:00

CREATE OR REPLACE PROCEDURE p_ip_bill_tf 

( pm_reg_no IN VARCHAR2) 

 IS

   v_cfh ipbill.pre_no%TYPE;

   v_cfrq ipbill.bill_date%TYPE;

   v_chrg_no ipbill.chrg_no%TYPE;

   v_reg_no ipbill.reg_no%TYPE;

   CURSOR cr(zyls VARCHAR2) IS select case when old_pre_no   is not  null then old_pre_no else pre_no end as cfh,

case when old_bill_date   is not  null then old_bill_date else bill_date end as cfrq,

chrg_no,reg_no  

from ip_bill f where reg_no=zyls and chrg_no=0  and nvl(yb_send_flag,'0')='0'

group by case when old_pre_no   is not  null then old_pre_no else pre_no end  ,

case when old_bill_date   is not  null then old_bill_date else bill_date end  ,

chrg_no,reg_no

having(sum(total)=0);

  BEGIN

    --打开游标

    OPEN cr(pm_reg_no);

    LOOP

--–-循环

  FETCH cr

        INTO V_cfh,v_cfrq,v_chrg_no,v_reg_no;

    EXIT WHEN cr%NOTFOUND; 

-- IF cr%NOTFOUND THEN

           --   EXIT;

      --  end if;

       --更新语句（根据查询出来的结果集合）

  update ip_bill b set yb_send_flag='1' where b.reg_no=v_reg_no and b.pre_no=v_cfh and b.bill_date=v_cfrq and b.chrg_no=v_chrg_no;

  update ip_bill b set yb_send_flag='1' where b.reg_no=v_reg_no and b.old_pre_no=v_cfh and b.old_bill_date=v_cfrq and b.chrg_no=v_chrg_no;



end loop; --结束循环

            CLOSE cr; 

END p_ip_bill_tf

错误提示：

Compilation errors for PROCEDURE XGRMYY.P_IP_BILL_TF



Error: PLS-00103: 出现符号 "end-of-file"在需要下列之一时：

        ;

       符号 ";" 被替换为 "end-of-file" 后继续。

Line: 33

Text: END p_ip_bill_tf

...全文

130 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

zjl8008 2016-03-09

打赏
举报

找到原因了，是变量的数据类型没取对！ v_cfh ipbill.pre_no%TYPE; v_cfrq ipbill.bill_date%TYPE; v_chrg_no ipbill.chrg_no%TYPE; v_reg_no ipbill.reg_no%TYPE; 有错！谢谢！

rick-he 2016-03-09

打赏
举报

引用 2 楼 zjl8008 的回复:

[quote=引用 1 楼 wmxcn2000 的回复:] END p_ip_bill_tf 最后面，加一个分号，试试。

加上错误更多了！ Compilation errors for PROCEDURE XGRMYY.P_IP_BILL_TF Error: PLS-00201: 必须声明标识符 'IPBILL.PRE_NO' Line: 4 Text: v_cfh ipbill.pre_no%TYPE; Error: PL/SQL: Item ignored Line: 4 Text: v_cfh ipbill.pre_no%TYPE; Error: PLS-00201: 必须声明标识符 'IPBILL.BILL_DATE' Line: 5 Text: v_cfrq ipbill.bill_date%TYPE; Error: PL/SQL: Item ignored Line: 5 Text: v_cfrq ipbill.bill_date%TYPE; Error: PLS-00201: 必须声明标识符 'IPBILL.CHRG_NO' Line: 6 Text: v_chrg_no ipbill.chrg_no%TYPE; Error: PL/SQL: Item ignored Line: 6 Text: v_chrg_no ipbill.chrg_no%TYPE; Error: PLS-00201: 必须声明标识符 'IPBILL.REG_NO' Line: 7 Text: v_reg_no ipbill.reg_no%TYPE; Error: PL/SQL: Item ignored Line: 7 Text: v_reg_no ipbill.reg_no%TYPE; Error: PLS-00320: 此表达式的类型声明不完整或格式不正确 Line: 22 Text: INTO V_cfh,v_cfrq,v_chrg_no,v_reg_no; Error: PL/SQL: SQL Statement ignored Line: 21 Text: FETCH cr Error: PLS-00320: 此表达式的类型声明不完整或格式不正确 Line: 28 Text: update ip_bill b set yb_send_flag='1' where b.reg_no=v_reg_no and b.pre_no=v_cfh and b.bill_date=v_cfrq and b.chrg_no=v_chrg_no; Error: PL/SQL: ORA-00904: "V_CHRG_NO": 标识符无效 Line: 28 Text: update ip_bill b set yb_send_flag='1' where b.reg_no=v_reg_no and b.pre_no=v_cfh and b.bill_date=v_cfrq and b.chrg_no=v_chrg_no; Error: PL/SQL: SQL Statement ignored Line: 28 Text: update ip_bill b set yb_send_flag='1' where b.reg_no=v_reg_no and b.pre_no=v_cfh and b.bill_date=v_cfrq and b.chrg_no=v_chrg_no; Error: PLS-00320: 此表达式的类型声明不完整或格式不正确 Line: 29 Text: update ip_bill b set yb_send_flag='1' where b.reg_no=v_reg_no and b.old_pre_no=v_cfh and b.old_bill_date=v_cfrq and b.chrg_no=v_chrg_no; Error: PL/SQL: ORA-00904: "V_CHRG_NO": 标识符无效 Line: 29 Text: update ip_bill b set yb_send_flag='1' where b.reg_no=v_reg_no and b.old_pre_no=v_cfh and b.old_bill_date=v_cfrq and b.chrg_no=v_chrg_no; Error: PL/SQL: SQL Statement ignored Line: 29 Text: update ip_bill b set yb_send_flag='1' where b.reg_no=v_reg_no and b.old_pre_no=v_cfh and b.old_bill_date=v_cfrq and b.chrg_no=v_chrg_no; [/quote] 没申明就定义，标识符无效就检查是不是写错了。为什么会错误更多？ oracle先检测语法

zjl8008 2016-03-09