日期:2014-05-17  浏览次数:20989 次

oracle存储过程动态条件以及游标问题。
create or replace procedure stat_centeruserstatistic_sp(
  i_orgno_no in sunds.bp_centeruserstatistic_tb.organ_no%type, --处理中心
  i_flow_node in sunds.bp_centeruserstatistic_tb.flow_node%type, --节点名称
  i_user_no in sunds.bp_centeruserstatistic_tb.user_no%type, --柜员号
  i_trans_id in sunds.bp_centeruserstatistic_tb.trans_id%type, --业务种类
  i_reportFormType in varchar2, --报表类型;0:日,1:月,2:年,3:时段
  i_date in sunds.bp_centeruserstatistic_tb.stat_date%type, --日期
  i_startdate in sunds.bp_centeruserstatistic_tb.stat_date%type, -- 时段起始日期
  i_enddate in sunds.bp_centeruserstatistic_tb.stat_date%type, --时段终止日期
  I_PAGE_IDX in varchar2, --页码
  cur out sys_refcursor,
  O_RET out varchar2,
  O_MSG out varchar2) is
v_sql_where_str varchar(4000);  
v_sql varchar(4000);  
begin

  --日统计
  if(i_reportFormType='0') then
  v_sql_where_str := ' select t.flow_node as flow_node, 
  t.user_no as user_no,
  t.trans_id as trans_id,
  sum(t.count)as all_together,
  sum(decode(t.stat_flag,''01'',t.count,0))as suc_count,
  sum(decode(t.stat_flag,''00'',t.count,0))as non_count,
  round(sum(decode(t.stat_flag,00, t.count,0))/sum(t.count)*100,2) as non_proba,
  round(sum(t.con_time) / sum(t.count),2) as avg_times
  from sunds.bp_centeruserstatistic_tb t
  where t.organ_no= '||chr(39)||i_orgno_no||chr(39)
  ||'and t.stat_date='||chr(39)||i_date||CHR(39);
   
  if i_flow_node is not null then
  v_sql_where_str := v_sql_where_str ||'and t.flow_node ='||
  chr(39) || i_flow_node || chr(39);
  end if;
  if i_user_no is not null then
  v_sql_where_str := v_sql_where_str || 'and t.user_no ='||
  chr(39) || i_user_no ||chr(39);
  end if;
  if i_trans_id is not null then 
  v_sql_where_str := v_sql_where_str ||' and t.trans_id ='||
  chr(39) || i_trans_id ||chr(39);
  end if;
  v_sql_where_str := v_sql_where_str ||'group by t.user_no,t.flow_node,t.trans_id
  order by t.flow_node,t.user_no;';
  --dbms_output.put_line('v_sql_where_str:'|| v_sql_where_str);
  open cur for v_sql_where_str;
  O_RET := '0';
  O_MSG := '查询成功';
  end if;


exception
  when others then
  O_RET := 'SP01000';
  O_MSG := '查询失败';
end stat_centeruserstatistic_sp;
/
经调试,总是在open这块跑异常,不知道什么原因,求高手指点下,本人也才学的存储过程。。。谢谢

------解决方案--------------------
--dbms_output.put_line('v_sql_where_str:'|| v_sql_where_str);
这句不要注释掉,把sql语句打出来直接执行看看什么错误,现在这样看不清楚