Oracle PL/SQL存储过程动态赋值问题
create or replace procedure record_inp_all
(
areacode varchar2,
beginTime varchar2,
endTime varchar2
)
as
v_areacode varchar2(20);
v_stat varchar2(20);
v_areacode_count number;
v_table type_split;
v_beginTime date;
v_endTime date;
v_sqlWhere varchar2(100) default '';
v_sql varchar2(1000);
begin
if nvl(beginTime,'') is not null then
v_beginTime := to_date(beginTime,'yyyy-mm-dd hh24:mi:ss');
else
v_beginTime := to_date('2014-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
end if;
if nvl(endTime,'') is not null then
v_endTime := to_date(endTime,'yyyy-mm-dd hh24:mi:ss');
else
v_endTime := sysdate;
end if;
select strsplit(areacode,',') into v_table from dual;
dbms_output.put_line(v_table.count);
if v_table.count = 1 then
if v_table(1) is null then
v_sqlWhere := v_sqlWhere||'';
else
v_sqlWhere := v_sqlWhere||' area_code_fund ='||v_table(1);
end if;
end if;
if v_table.count > 1 and v_table.count <9 then
--v_sqlWhere :=
for i in 1.. v_table.count loop
v_sqlWhere := v_sqlWhere||chr(39)||v_table(i)||chr(39)||',';
end loop;
v_sqlWhere := '('||substr(v_sqlWhere,1,length(v_sqlWhere)-1)||')';
dbms_output.put_line('v_sqlWhere:'||v_sqlWhere);
end if;
if v_table.count = 9 then
v_sqlWhere := v_sqlWhere||'';
dbms_output.put_line('v_sqlWhere:'||v_sqlWhere);
end if;
v_sqlWhere:='(''111'',''222'')';
dbms_output.put_line('v_sqlWhere:'||v_sqlWhere);
for c_cur in
(select t.area_code_fund,to_char(t.registe_date,'yyyy-mm') c_regtime,count(*) c_count from nc_inp_register t where
t.registe_date>=v_beginTime and t.registe_date<=v_endTime and t.data_source<>'2'
and t.area_code_fund inv_sqlWhere
group by t.area_code_fund,to_char(t.registe_date,'yyyy-mm')
order by t.area_code_fund,to_char(t.registe_date,'yyyy-mm'))
loop
dbms_output.put_line(c_cur.area_code_fund||','||c_cur.c_regtime||','||c_cur.c_count);
end loop;
end;
v_sqlWhere为什么不能在正确赋值?而别的参数反而可以的
------解决方案--------------------
open cursor for 'select t.area_code_fund,
to_char(t.registe_date, ''yyyy-mm'') c_regtime,
count(*) c_count
from nc_inp_register t
where t.registe_date >= v_beginTime
and t.registe_date <= v_endTime
and t.data_source <> '2'
and t.area_code_fund in '
------解决方案--------------------
v_sqlWhere
------解决方案--------------------
'
group by t.area_code_fund, to_char(t.registe_date, ''yyyy-mm'')
order by t.area_code_fund, to_char(t.registe_date, ''yyyy-mm'')'