MySQL执行存储过程中出现错误1305 - FUNCTION cr_debug.ENTER_MODULE2 does not exist

Question

存储过程中并没有 cr_debug.ENTER_MODULE2这个方法,而且在另外一个环境上可以执行。这个是什么情况

Answer 1

[code=sql]BEGIN
DECLARE tRiskAmnt DECIMAL;
  DECLARE t_sql           VARCHAR(4000);
  DECLARE t_count         decimal;
  DECLARE t_ReceiveFlag   VARCHAR(20);
  DECLARE t_Amnt          decimal(16,2); 
  DECLARE t_Riskcode      varchar(20) ; 
  DECLARE t_Mult          decimal(20,5);
  DECLARE t_InsuYear      int; 
  DECLARE t_Interval      integer ;
  DECLARE t_command       varchar(200);
  DECLARE t_DutyCode      varchar(10) ;
  DECLARE t_AppAge        decimal ;
  DECLARE t_Sex           varchar(1);
  DECLARE t_PayEndYear    int ;
  DECLARE t_PayIntv       int;
  DECLARE t_temp_SumAmnt  decimal ;
  DECLARE t_Interval_temp date ;
  DECLARE t_Exflag        decimal; 
  DECLARE t_Exlcsflag     decimal;
  DECLARE t_Exlcflag      decimal;
  DECLARE t_Prem          decimal(16,2);
  DECLARE t_SumPrem       decimal(16,2);
  DECLARE t_birthday      varchar(20);
  DECLARE t_PayYears      int;
  DECLARE t_MainPolNo     varchar(20);
DECLARE Amnt1   decimal(16,2);
  DECLARE InsuredNo1  varchar(24);
  DECLARE Riskcode1   varchar(20);
  DECLARE Mult1 decimal(20,5);
  DECLARE Cvalidate1 date;
  DECLARE PayEndYear1 int;
  DECLARE insuyear1  int;
  DECLARE PayIntv1  int;
  DECLARE polno1  varchar(20);
  DECLARE appflag1 varchar(1);
  DECLARE contno1   varchar(20);
  DECLARE Prem1 decimal(16,2);
  DECLARE SumPrem1 decimal(16,2);
  DECLARE Birthday1  VARCHAR(10);
  DECLARE PayYears1 int;
  DECLARE Mainpolno1 varchar(20);
DECLARE done int DEFAULT 0;
DECLARE cur_ybth CURSOR FOR select LCPol.Amnt * GETDomesticRate(LCPol.currency, '') Amnt,
                         LCPol.InsuredNo,
                         LCPol.Riskcode,
                         LCPol.Mult,
                         LCPol.Cvalidate,
                         LCPol.PayEndYear,
                         LCPol.insuyear,
                         LCPol.PayIntv,
                         LCPol.polno,
                         lcpol.appflag,
                         LCPol.contno,
                         LCPol.Prem * GETDomesticRate(LCPol.currency, '') Prem,
                         LCPol.SumPrem * GETDomesticRate(LCPol.currency, '') SumPrem,
                         DATE_FORMAT(LCPol.Insuredbirthday, '%Y-%m-%d') Birthday,
                         LCPol.PayYears,
                         LCPol.Mainpolno
                    from LCPol, lcinsuredrelated
                   where LCPol.Polno = lcinsuredrelated.polno
                     and lcinsuredrelated.customerno = '0000052605'
                     and LCPol.Appflag in ('0', '1', '2', '9')
                     and (LCPol.Uwflag <> '1' and LCPol.Uwflag <> '2' and
                         LCPol.Uwflag <> 'a')
                     and not exists
                   (SELECT 1
                            FROM lmriskapp
                           WHERE LCPol.Riskcode = lmriskapp.riskcode
                             and lmriskapp.risktype7 in ('1'))
                     and not exists
                   (select 1
                            from lccont
                           where prtno = lcpol.prtno
                             and Appflag in ('0', '1', '2', '9')
                             and uwflag in ('1', '2', 'a'))
                  union
                  select Amnt * GETDomesticRate(currency, '') Amnt,
                         InsuredNo,
                         Riskcode,
                         Mult,
                         Cvalidate,
                         PayEndYear,
                         insuyear,
                         PayIntv,
                         polno,
                         appflag,
                         contno,
                         Prem * GETDomesticRate(LCPol.currency, '') Prem,
                         LCPol.SumPrem * GETDomesticRate(LCPol.currency, '') SumPrem,
                         DATE_FORMAT(LCPol.Insuredbirthday, '%Y-%m-%d') Birthday,
                         LCPol.PayYears,
                         LCPol.Mainpolno
                    from LCPol
                   where InsuredNo = '0000052605'
                     and LCPol.Appflag in ('0', '1', '2', '9')
                     and (LCPol.Uwflag <> '1' and LCPol.Uwflag <> '2' and
                         LCPol.Uwflag <> 'a')
                     and not exists
                   (SELECT 1
                            FROM lmriskapp
                           WHERE LCPol.Riskcode = lmriskapp.riskcode
                             and lmriskapp.risktype7 in ('1'))
                     and not exists
                   (select 1
                            from lccont
                           where prtno = lcpol.prtno
                             and Appflag in ('0', '1', '2', '9')
                             and uwflag in ('1', '2', 'a'))
                  union
                  SELECT LCPol.Amnt * GETDomesticRate(LCPol.currency, '') Amnt,
                         LCPol.InsuredNo,
                         LCPol.Riskcode,
                         LCPol.Mult,
                         LCPol.Cvalidate,
                         LCPol.PayEndYear,
                         LCPol.insuyear,
                         LCPol.PayIntv,
                         LCPol.polno,
                         lcpol.appflag,
                         LCPol.contno,
                         LCPol.Prem * GETDomesticRate(LCPol.currency, '') Prem,
                         LCPol.SumPrem * GETDomesticRate(LCPol.currency, '') SumPrem,
                         DATE_FORMAT(LCPol.Insuredbirthday, '%Y-%m-%d') Birthday,
                         LCPol.PayYears,
                         LCPol.Mainpolno
                    FROM lcpol, lmriskapp
                   WHERE lcpol.riskcode = lmriskapp.riskcode
                     and lcpol.appntno = '0000052605'
                     and lmriskapp.risktype7 in ('1')
                     and LCPol.Appflag in ('0', '1', '2', '9')
                     and (LCPol.Uwflag <> '1' and LCPol.Uwflag <> '2' and
                         LCPol.Uwflag <> 'a')
                     and not exists
                   (select 1
                            from lccont
                           where prtno = lcpol.prtno
                             and Appflag in ('0', '1', '2', '9')
                             and uwflag in ('1', '2', 'a'));
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
set tRiskAmnt     = 0;
set t_temp_SumAmnt  = 0;
set t_sql           = '';
set t_ReceiveFlag   = 0;
                set t_Amnt   = 0;
set t_Riskcode      = 0;
set t_Mult          = 0;
set t_Interval      = 0;
set t_Interval_temp = NULL;
set t_DutyCode      = '';
set t_AppAge        = 0;
set t_Sex           = '0';
set t_PayEndYear    = '0';
set t_PayIntv       = '0';
set t_InsuYear      = 0;
set t_Prem          = 0;
set t_SumPrem       = 0;
set t_MainPolNo     = '';
OPEN cur_ybth;
    ybt_loop : LOOP
    FETCH  cur_ybth INTO  Amnt1,InsuredNo1,Riskcode1,Mult1,Cvalidate1,PayEndYear1,insuyear1,PayIntv1,polno1,appflag1,contno1 ,Prem1 ,SumPrem1,Birthday1,PayYears1,Mainpolno1;
    IF done=1 THEN
LEAVE ybt_loop;
END IF;
SET t_count         = 0;
    SET t_temp_SumAmnt  = 0;
    SET t_ReceiveFlag   = 0;
    SET t_Amnt          = 0;
    SET t_Mult          = 0;
    SET t_Interval      = 0;
    SET t_Interval_temp = NULL;
    SET t_command       = '';
    SET t_InsuYear      = 0;
    SET t_Exflag        = 0;
    SET t_Exlcsflag     = 0;
    SET t_Exlcflag      = 0;
    SET t_Prem          = 0;
    SET t_SumPrem       = 0;
    SET t_Riskcode      = trim(Riskcode1);
 if tCalType = '1' then
      select count(1)
        into t_Exflag
        from lmrisksort
       where riskcode = t_Riskcode
         and risksorttype = '3'
         and RiskSortValue = tRiskType;
    else
      if tCalType = '2' then
        select count(1)
          into t_Exflag
          from lmrisksort
         where riskcode = t_Riskcode
           and risksorttype = '8'
           and RiskSortValue = tRiskType;
      end if;
    end if;
    if t_Exflag = 1 then
      select floor(Months_between(cvalidate1, DATE_ADD(Birthday,INTERVAL 1 DAY)) / 12),
             Sex
        into t_AppAge, t_Sex
        from ldperson
       where CustomerNo = tInsuredNo;
      set t_PayEndYear = trim(PayEndYear1);
      set t_InsuYear   = trim(insuyear1);
      set t_PayIntv    = trim(PayIntv1);
      set t_command    = trim(Polno1);
      select dutycode
        into t_DutyCode
        from lcduty
       where polno = polNo1
         and rownum = 1;
      select case count(1)
               when 1 then
                'Y'
               when 0 then
                'N'
             end
        into t_ReceiveFlag
        from lcget
       where getstartdate <= sysdate()
         and livegettype = '0'
         and polNo = polNo1;
      set t_Amnt    = trim(Amnt1);
      set t_Prem    = trim(Prem1);
      set t_SumPrem = trim(SumPrem1);
      if (t_SumPrem = '0') then
        set t_SumPrem = t_Prem;
      end if;
      set t_Mult = trim(Mult1);
      set t_Interval_temp = trim(Cvalidate1);
      if DATE_FORMAT(Cvalidate1, '%Y-%m-%d') =
         DATE_FORMAT(sysdate(), '%Y-%m-%d') then
        set t_Interval = 1;
      elseif ceil(months_between(sysdate(), t_Interval_temp) / 12) = 0 then
        set t_Interval = 1;
      else
        set t_Interval = ceil(months_between(sysdate(), t_Interval_temp) / 12);
      end if;
      if tCalType = '1' then
        select calsql
          into t_sql
          from lmcalmode
         where calcode like 'FX%' 
           and riskcode = t_Riskcode
           and type = tRiskType;
      else
        if tCalType = '2' then
          select calsql
            into t_sql
            from lmcalmode
           where calcode like 'TJ%'
             and riskcode = t_Riskcode
             and type = tRiskType;
        end if;
      end if; 
 

Answer 2

     set t_sql := upper(t_sql);
      set t_sql := REPLACE(trim(t_sql), '?DUTYCODE?', trim(t_DutyCode));
      set t_sql := REPLACE(trim(t_sql), '?POLNO?', trim(t_command));
      set t_sql := REPLACE(trim(t_sql), '?INSUYEAR?', trim(t_InsuYear));
      set t_sql := REPLACE(trim(t_sql), '?APPAGE?', trim(t_AppAge));
      set t_sql := REPLACE(trim(t_sql), '?SEX?', trim(t_Sex));
      set t_sql := REPLACE(trim(t_sql), '?PAYENDYEAR?', trim(t_PayEndYear));
      set t_sql := REPLACE(trim(t_sql), '?PAYINTV?', trim(t_PayIntv));
      set t_sql := REPLACE(trim(t_sql), '?RECEIVEFLAG?', trim(t_ReceiveFlag));
      set t_sql := REPLACE(trim(t_sql), '?AMNT?', trim(t_Amnt));
      set t_sql := REPLACE(trim(t_sql), '?GET?', trim(t_Amnt));
      set t_sql := REPLACE(trim(t_sql), '?INSUREDNO?', trim(tInsuredNo));
      set t_sql := REPLACE(trim(t_sql), '?RISKCODE?', trim(t_Riskcode));
      set t_sql := REPLACE(trim(t_sql), '?MULT?', trim(t_Mult));
      set t_sql := REPLACE(trim(t_sql), '?INTERVAL?', trim(t_Interval));
      set t_sql := REPLACE(trim(t_sql), '?PREM?', trim(t_Prem));
      set t_sql := REPLACE(trim(t_sql), '?SUMPREM?', trim(t_SumPrem));
      set t_sql := replace(trim(t_sql), '?BIRTHDAY?', trim(t_birthday));
      set t_sql := replace(trim(t_sql), '?PAYYEARS?', trim(t_PayYears));
      set t_sql := replace(trim(t_sql), '?MAINPOLNO?', trim(t_MainPolNo));
call carr_out(t_sql,@SumAmnt);
SET  t_temp_SumAmnt=@SumAmnt;
IF t_temp_SumAmnt is null THEN
        set t_temp_SumAmnt = 0;
      end if;
      set tRiskAmnt = tRiskAmnt + t_temp_SumAmnt;
end if;
END LOOP;
      CLOSE cur_ybth;
if tRiskAmnt is not null then
    return(tRiskAmnt);
else
    return(0);
end if;[/code]

Answer 3

谁知道怎么回事啊，我的function中根本没这个方法啊

Answer 4

你这个是动态的，应该是拼出的 SQL 的问题，拼的 SQL 放到变量，或者 SELECT 出来看看

Answer 5

已经知道原因了原来是之前的一个方法用了dbfore调试结果就有了cr_debug这个方法来了