存储过程test不过,求教

Question

create or replace procedure PRC_RP_CC_SCORE_WEEKLY(l_data_date in varchar2) is
 
  l_Plan_ID         varchar2(40);
  l_Plan_Name       varchar2(100);
  l_Plan_Type       varchar2(8);
  l_start_time      VARCHAR2(19);
  l_end_time        VARCHAR2(19);
  l_Paper_Count     integer;
  l_Average_Score   number(6, 2);
  l_Scoring_Rate    number(5, 2);
  l_Template_Amount number(6, 2);
  l_load_rate       number(5,2);
  l_load_rate_count number(5,2);
  l_Paper_total     number(6,0);
  l_Plan_end_time   VARCHAR2(19);
  
  l_during_date      VARCHAR2(19);
  l_cur_Paper_Result int;
  l_cur_Paper_Result_1 int;
  no_week           number;
  l_date            varchar2(19);
  l_query_Sql        varchar2(1500);
  l_query_sql_1      varchar2(1000);
  l_Insert_Result    boolean;
  l_Precessed        int;--查询执行结果，无意义
  l_precessed_1     int;  --定义常量
  c_DateFormat   CONSTANT VARCHAR2(20) := 'yyyy-mm-dd';
  --c_TimeFormat   CONSTANT VARCHAR2(30) := 'yyyy-mm-dd hh24:mi:ss';
  c_Paper_Status CONSTANT VARCHAR2(20) := '3'; --"已完成"状态
  c_plan_status   CONSTANT VARCHAR2(20) := '1' ;begin  l_query_sql_1 := 'select t.id,
                           t.start_time,
                           t.end_date,
                           to_char(to_date(t.end_date,:v_dateformat)-to_date(t.start_time,:v_dateformat)) as during_date
          from tbl_qc_plan t
         where t.status = :v_Status or
        t.end_date=:v_date ';
          --Open cursor
  l_cur_Paper_Result_1 := dbms_sql.open_cursor;
  --parse SQL
  dbms_sql.parse(l_cur_Paper_Result_1, l_query_sql_1, dbms_sql.native);
  --bind variable by column name
  dbms_sql.bind_variable(l_cur_Paper_Result_1, 'v_date', l_data_date);
  dbms_sql.bind_variable(l_cur_Paper_Result_1, 'v_Status', c_plan_status);
  dbms_sql.bind_variable(l_cur_Paper_Result_1, 'v_dateformat', c_DateFormat);
  --describe defines
  dbms_sql.define_column(l_cur_Paper_Result_1, 1, l_Plan_ID, 40);
  dbms_sql.define_column(l_cur_Paper_Result_1, 2, l_start_time, 19);
  dbms_sql.define_column(l_cur_Paper_Result_1, 3, l_Plan_end_time, 19);
  dbms_sql.define_column(l_cur_Paper_Result_1, 4, l_during_date, 19);
  -- execute Query
  l_precessed_1 := dbms_sql.execute(l_cur_Paper_Result_1);  LOOP
    -- fetch a row
    IF dbms_sql.fetch_rows(l_cur_Paper_Result_1) > 0 then      -- fetch columns from the row
     
      dbms_sql.column_value(l_cur_Paper_Result_1, 1, l_Plan_ID);
      dbms_sql.column_value(l_cur_Paper_Result_1, 2, l_start_time);
      dbms_sql.column_value(l_cur_Paper_Result_1, 3, l_Plan_end_time);
      dbms_sql.column_value(l_cur_Paper_Result_1, 4, l_during_date);
         no_week:= ((to_date(l_data_date,'yyyy-mm-dd')-to_date(l_start_time,'yyyy-mm-dd'))+1)/7;
         
         if no_week=1 or no_week=2 or no_week=3 or no_week=4 or no_week=5 or l_Plan_end_time = l_data_date then 
          
         if l_Plan_end_time != l_data_date then
             l_date :=to_char((to_date( l_data_date,c_DateFormat) - 6),c_DateFormat);
         else  
             l_date :=to_char((to_date( l_data_date,c_DateFormat) - MOD(l_during_date,7) +1),c_DateFormat); 
         end if;
             
         l_query_Sql := 'select pl.id,
               pl.plan_name,
               pl.type,
               pl.start_time,
               pl.end_date,
               count(dis_res.rusult_id),
               AVG(dis_res.TEMPLATE_SCORE) avg_score,
               dis_res.template_amount  amount      
          from (select dis.plan_id, res.template_score, res.template_amount,res.template_name,res.template_id,
                       res.row_id as rusult_id
                  from TBL_QC_PAPER_DISPOSE dis, TBL_QC_DISPOSE_RESULT res
                 where dis.id = res.dispose_id
                   and dis.end_time between :v_date and :v_end_date
                   and dis.status = :v_Status
                   and dis.plan_id =:v_plan_id) dis_res,
               tbl_qc_plan pl
         where pl.id = dis_res.plan_id
         group by pl.id,
                  pl.plan_name,
                  pl.type,
                  pl.start_time,
                  pl.end_date,
                  dis_res.plan_id,
                  dis_res.template_amount';          --Open cursor
          l_cur_Paper_Result := dbms_sql.open_cursor;
          --parse SQL
          dbms_sql.parse(l_cur_Paper_Result, l_query_Sql, dbms_sql.native);
          --bind variable by column name
          dbms_sql.bind_variable(l_cur_Paper_Result, 'v_date', l_date);
          dbms_sql.bind_variable(l_cur_Paper_Result, 'v_end_date', l_data_date);
          dbms_sql.bind_variable(l_cur_Paper_Result, 'v_Status', c_Paper_Status);
          dbms_sql.bind_variable(l_cur_Paper_Result, 'v_plan_id', l_plan_id);
          --describe defines
          dbms_sql.define_column(l_cur_Paper_Result, 1, l_Plan_ID, 40);
          dbms_sql.define_column(l_cur_Paper_Result, 2, l_Plan_Name, 100);
          dbms_sql.define_column(l_cur_Paper_Result, 3, l_Plan_Type, 8);
          dbms_sql.define_column(l_cur_Paper_Result, 4, l_start_time,19);
          dbms_sql.define_column(l_cur_Paper_Result, 5, l_end_time,19);
          dbms_sql.define_column(l_cur_Paper_Result, 6, l_Paper_Count);
          dbms_sql.define_column(l_cur_Paper_Result, 7, l_Average_Score);
          dbms_sql.define_column(l_cur_Paper_Result, 8, l_Template_Amount);            
          -- execute Quer
          l_Precessed := dbms_sql.execute(l_cur_Paper_Result);          LOOP
            -- fetch a row
              IF dbms_sql.fetch_rows(l_cur_Paper_Result) > 0 then              -- fetch columns from the row
             
              dbms_sql.column_value(l_cur_Paper_Result, 1, l_Plan_ID);
              dbms_sql.column_value(l_cur_Paper_Result, 2, l_Plan_Name);
              dbms_sql.column_value(l_cur_Paper_Result, 3, l_Plan_Type);
              dbms_sql.column_value(l_cur_Paper_Result, 4, l_start_time);
              dbms_sql.column_value(l_cur_Paper_Result, 5, l_end_time);
              dbms_sql.column_value(l_cur_Paper_Result, 6, l_Paper_Count);
              dbms_sql.column_value(l_cur_Paper_Result, 7, l_Average_Score);
              dbms_sql.column_value(l_cur_Paper_Result, 8, l_Template_Amount);  
                
              
              l_Scoring_Rate := l_Average_Score / l_Template_Amount *100;
             
              
              select to_number (plan_info.value,999999) into l_Paper_total
                from tbl_qc_common_info plan_info
               where plan_info.object_id = l_Plan_ID
                 and plan_info. = '2';                
                
                if l_Plan_end_time != l_data_date then
                l_load_rate:=l_Paper_Count/(l_paper_total/l_during_date*7);
                else 
                l_load_rate:=l_paper_count/((l_paper_total/l_during_date)*MOD(l_during_date,7));
                
                end if;
              
              l_Insert_Result := FUN_INSERT_CC_SCORE_WEEK(                                 l_Plan_ID ,
                                 l_Plan_Name ,
                                 l_Plan_Type ,
                                 l_start_time ,
                                 l_end_time  ,
                                 no_week ,
                                 l_Paper_Count  ,
                                 l_Average_Score ,
                                 l_Scoring_Rate,
                                 l_load_rate,
                                 l_load_rate_count);
                                 
              ELSE
                exit;
            
            END IF; 
          END LOOP;
        else exit;
        end if;
        else exit;
        end if;
end loop;
  
end PRC_RP_CC_SCORE_WEEKLY;
以上为小弟编写的一个存储过程~可test的时候在红色标记处报错~我看了一下午也不知道错在何处，求高手指点。

Answer 1

plan_info.value 有小数或者太大了

Answer 2

select *     from tbl_qc_common_info plan_info 
              where plan_info.object_id = l_Plan_ID 
                and plan_info. = '2'; 有数据没？

Answer 3

报什么错？
你这边出错，有可能是to_number出错，也有可能是into的出错。

Answer 4

要是你的 to_number有问题你可以换成
select trunc(plan_info.value) into l_Paper_total 
                from tbl_qc_common_info plan_info 
              where plan_info.object_id = l_Plan_ID 
                and plan_info. = '2'; 
要是你是NO_DATE_FOUND 你可以在下面处理异常
exception
   when no_data_found then
     <statements>
   when others then
     <statements>
还有好像你的 l_Paper_total 变量没定义吧！ 请在上面定义

Answer 5

你的to_number 用错了！ 换成trunc（数）或round（to_number（数），0）

Answer 6

plan_info.value
我感觉是这个参数有问题，你不要先赋值单独检查这个sql看一下有没有问题
 declare 
 l_Paper_total number;
 cursor cur_test 
 is 
 select value from tbl_qc_common_info where  = '2';
begin
 for rec_test in cur_test loop
 dbms_output.put_line(rec_test.value);
 end loop;
end;
看看有没有小数或者字符的。

Answer 7

谢谢各位~，我最后把l_Paper_total 定义为varchar2(8),
然后直接select plan_info.value into l_paper_total,奇怪的是开始几次test依然报错，但当我百无聊赖再test一次居然过了~ 真是百思不得其解

Answer 8

value倒是没有问题~ 不过问题解决到现在我也不明白什么原因~

Answer 9

SQL> select to_number('939393') from dual;TO_NUMBER('939393')
-------------------
             939393SQL> select to_char(933922,999999.99) from dual;TO_CHAR(93
----------
 933922.00参考一下

Answer 10

thanks,不过我l_paper_total 是number型，为啥说to_number用错了呢 请教！

Answer 11

增加异常处理，看看具体走到哪个异常处理里去了
begin
 ...
exception 
  when no_data_found then 
    dbms_output.put_line('there is no data found, the detail msg:' || sqlerrm);
  when others then
    dbms_output.put_line('others:' || sqlerrm);
end PRC_RP_CC_SCORE_WEEKLY; 执行这个存储过程的时候
set serverout on 
exec proc_name (...);或者把sqlerrm赋值给另一个变量，然后在pl/sql的调试窗口里面加上这个变量，看最后这个变量的值是啥(varchar类型的）

Answer 12

你直接运行这个SQL，得到什么结果？
select to_number (plan_info.value,999999)
                from tbl_qc_common_info plan_info 
              where plan_info.object_id = l_Plan_ID 
                and plan_info. = '2'; 

Answer 13

运行这个可以查到希望的数据~当然l_plan_id是用我所查的计划名称代替