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<span style="color: #FF0000;"> v_sqlWhere</span> 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')这里不能用for in。得用动态sql.r_emp emp%rowtype; type c_type is ref cursor; c1 c_type; begin open c1 for 'select * from emp where salary >:1' using p_salary; loop fetch c1 into r_emp; exit when c1%notfound; ... end loop; close c1; end;
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'')'
这样会报 错,sql太长了。报“sql命令未正确结束”这种问题怎么解决?
这样会报 错,sql太长了。报“sql命令未正确结束”这种问题怎么解决?sql里面 and t.data_source <> '2' 改成and t.data_source <> ''2''
这样会报 错,sql太长了。报“sql命令未正确结束”这种问题怎么解决?sql里面 and t.data_source <> '2' 改成and t.data_source <> ''2'' 已经改成这个了。v_sql := 'select t.area_code_fund,to_char(t.registe_date,''yyyy-mm'') ,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'')';
看一下v_table的赋值以及判断取值是否正确。
还有第54行是否只是测试用的?v_sqlWhere重新赋值了。
还有oracle中‘’与null是等效的。
t.registe_date>=v_beginTime and t.registe_date<=v_endTime and t.data_source<>'2'
and t.area_code_fund in<span style="color: #FF0000;"> v_sqlWhere</span>
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')这里不能用for in。得用动态sql.r_emp emp%rowtype;
type c_type is ref cursor;
c1 c_type;
begin
open c1 for 'select * from emp where salary >:1' using p_salary;
loop
fetch c1 into r_emp;
exit when c1%notfound;
...
end loop;
close c1;
end;
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'')'
已经改成这个了。v_sql := 'select t.area_code_fund,to_char(t.registe_date,''yyyy-mm'') ,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'')';