求助...in里的内容如何动态...SQL codev_start_datedatev_end_datedategrantstrvarchar2(500)mgrantstr
求助...in里的内容如何动态...
- SQL code
v_start_date date; v_end_date date; grantstr varchar2(500); mgrantstr varchar2(500); begin v_start_date := iv_start_date; v_end_date := iv_end_date + 1; grantstr := igrantstr; mgrantstr:=replace(grantstr,',',''','''); mgrantstr:=''''||mgrantstr||''''; open ov_total for select dvn.station_name,dvn.domain_value_name,count(station_id) from (select ins.station_name,vli.vehicle_tag_no,vli.station_id, dvi.domain_value_name,vli.grant_date from vehicle_label_info vli, (select * from domain_value_info where code_domain = 'LABEL') dvi, (select * from INSPECTION_STATION) ins where vli.label_type = dvi.id_domain_value(+) and ins.station_id = vli.station_id(+) and vli.station_id in (mgrantstr) and vli.grant_date >= v_start_date and vli.grant_date < v_end_date) dvn group by dvn.station_name, dvn.domain_value_name order by dvn.station_name; end
上面是个存储过程...我想要in里的内容动态产生...求教...谢谢
[解决办法]
- SQL code
open ov_total for 'select dvn.station_name,dvn.domain_value_name,count(station_id) from (select ins.station_name,vli.vehicle_tag_no,vli.station_id, dvi.domain_value_name,vli.grant_date from vehicle_label_info vli, (select * from domain_value_info where code_domain = ''LABEL'') dvi, (select * from INSPECTION_STATION) ins where vli.label_type = dvi.id_domain_value(+) and ins.station_id = vli.station_id(+) and vli.station_id in ('||mgrantstr||') and vli.grant_date >= v_start_date and vli.grant_date < v_end_date) dvn group by dvn.station_name, dvn.domain_value_name order by dvn.station_name';
[解决办法]
楼上正解,open for 打开游标本身支持动态拼接语句