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语句打出来直接执行看看什么错误，现在这样看不清楚。
------解决方案--------------------
- SQL code
order by t.flow_node,t.user_no;';--改為,把分號去掉order by t.flow_node,t.user_no';