oracle打开游标报904,打开游标报 ORA-00972: identifier is too long

本帖最后由 youzhagui2006 于 2013-9-30 12:17 编辑

bell6248 发表于 2013-9-30 12:06

sql_return是如何的贴一下

procedure getDispatchAssessScoreData(p_begin_date varchar2,

p_end_date   varchar2,

p_type       varchar2,

p_cursor     in out refCursor) is

sql_avg    varchar2(4000);

sql_col    cust_jtcrm_assess_data.dispatch_content%type;

sql_return long;

sql_all    long;

sql_aync   varchar2(2000) := '';

v_cur          sys_refcursor;

col_cont       varchar2(4000);

col_cont_union varchar2(4000);

v_avg_value    varchar2(30);

v_num          number := 0;

v_maxdecode    varchar2(4000);

v_req_str          varchar2(4000);

col_req        number;

begin

sql_avg := ' select round( sum((decode(sign(100 - 20 - 2 * sum(delay_days)),-1,0,(100 - 20 - 2 * sum(delay_days))))) / 31,2) from cust_jtcrm_assess_score b,cust_jtcrm_assess_data c where b.rela_request_id = c.request_id and c.dispatch_norm_type =:1

and c.dispatch_finish_date >= to_date(:2,''yyyy-mm-dd'') and c.dispatch_finish_date < to_date(:3,''yyyy-mm-dd'') + 1 group by b.region_id ';

execute immediate (sql_avg)

into v_avg_value

using p_type, p_begin_date, p_end_date;

if v_avg_value is null then

v_avg_value := '''''';

end if;

sql_all := 'select to_char(rela_request_id) REQUEST_ID, region_id';

sql_col := 'select distinct c.dispatch_content, c.request_id from cust_jtcrm_assess_score b,cust_jtcrm_assess_data c where b.rela_request_id = c.request_id and c.dispatch_norm_type = ' ||

p_type || ' and c.dispatch_finish_date >= to_date(''' ||

p_begin_date ||

''',''yyyy-mm-dd'') and c.dispatch_finish_date < to_date(''' ||

p_end_date ||

''',''yyyy-mm-dd'') + 1 ';

-- dbms_output.put_line(sql_col);

begin

open v_cur for sql_col;

loop

fetch v_cur

into col_cont, col_req;

v_num := v_num + 1;

exit when v_cur%notfound;

sql_aync       := sql_aync || ',decode(to_char(dispatch_content),''' ||

col_cont || ''',b.delay_days)' || ' "' || col_cont || '" ';

v_maxdecode    := v_maxdecode || ',MAX(DECODE(RN, ' || v_num ||

', "' || col_cont || '")) "' || col_cont || '"';

col_cont_union := col_cont_union || '"' || col_cont || '",';

v_req_str := v_req_str || ',' || col_req  ||' "RQ' || v_num || '"';

end loop;

if col_cont_union is not null then

col_cont_union := substr(col_cont_union,

0,

length(col_cont_union) - 1);

end if;

close v_cur;

EXCEPTION

WHEN NO_DATA_FOUND THEN

null;

end;

if v_num = 0 then

sql_return := 'select 0 REQUEST_ID, '''' "省份", '''' "得分", '''' "平均分" from dual';

else

sql_all := sql_all || sql_aync ;

sql_all := sql_all || ',' || v_avg_value || ' "平均分"';

sql_all := sql_all ||

' from cust_jtcrm_assess_score b,cust_jtcrm_assess_data c where b.rela_request_id = c.request_id and c.dispatch_norm_type = ' ||

p_type || ' and c.dispatch_finish_date >= to_date(''' ||

p_begin_date ||

''',''yyyy-mm-dd'') and c.dispatch_finish_date < to_date(''' ||

p_end_date ||

''',''yyyy-mm-dd'') + 1 group by b.rela_request_id,delay_days,dispatch_content,region_id ';

-- dbms_output.put_line(sql_all);

if col_cont_union is null then

sql_return := 'with tcm AS (' || sql_all || ') select (select region_name from manage_region where region_id = tbl.region_id) "省份", df.grade "得分", "平均分" '||

v_req_str ||' from (SELECT region_id,  "平均分" FROM  tcm GROUP BY region_id, "平均分") tbl , (select region_id, sum(delay_days) grade

from (select b.delay_days, region_id from cust_jtcrm_assess_score b, cust_jtcrm_assess_data c

where b.rela_request_id = c.request_id and c.dispatch_norm_type = 1

and c.dispatch_finish_date >= to_date(''' || p_begin_date || ''', ''yyyy-mm-dd'')

and c.dispatch_finish_date < to_date(''' ||  p_end_date || ''', ''yyyy-mm-dd'') + 1

group by b.delay_days, region_id order by b.rela_request_id)

group by region_id) df where df.region_id = tbl.region_id';

else

sql_return := 'with tcm AS (' || sql_all || ') select (select region_name from manage_region where region_id = tbl.region_id) "省份", ' ||

col_cont_union || ',  df.grade "得分", "平均分" '|| v_req_str ||' from (SELECT region_id,  "平均分"

' || v_maxdecode ||' FROM (SELECT tcm.*, ROW_NUMBER() OVER(PARTITION BY region_id ORDER BY ' ||

col_cont_union || ') RN FROM tcm) T GROUP BY region_id, "平均分") tbl , (select region_id, sum(delay_days) grade

from (select b.delay_days, region_id from cust_jtcrm_assess_score b, cust_jtcrm_assess_data c

where b.rela_request_id = c.request_id and c.dispatch_norm_type = 1

and c.dispatch_finish_date >= to_date(''' || p_begin_date || ''', ''yyyy-mm-dd'')

and c.dispatch_finish_date < to_date(''' ||  p_end_date || ''', ''yyyy-mm-dd'') + 1

group by b.delay_days, region_id order by b.rela_request_id)

group by region_id) df where df.region_id = tbl.region_id order by df.region_id ';

end if;

end if;

--dbms_output.put_line(sql_return);

open p_cursor for sql_return;

end getDispatchAssessScoreData;