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里的内容动态产生...求教...谢谢
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里的内容动态产生...求教...谢谢
解决方案 »
- 求教大神,这种情况该怎么解决
- oracle 中的临时变量& 是什么类型?举例说明
- oracle 9i 在linux系统下,创建数据库实例的时候停在0%的进度,这个会是什么问题啊?
- 如何在oracle中定义和执行字符串
- 查询效率
- oracle10中如何配置恢复设置?
- 着急的一个问题,在线等。我用CREATE 建的表,在用ALTER或DELETE操作时提示我说表名不存在,怎么办?
- oracle 左连接和右连接问题,请帮小菜一下啊,搞清不为什么!!
- 请指教:如何用SQL/PLUS或者WorkSheet执行一个.SQL 文件?
- 急,小弟求助项目中一个问题:Oracle怎么自动把某一dbf文件中数据导入?(见内)
- 哪些部门的平均工资比30部门的平均工资要低
- 【求助】关于Oracle这种需求的查询语句不清楚怎么写
'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';
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';
应该是这样...多谢2楼...
非字符型变量最好不要这样拼接,改成
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' using v_start_date,v_end_date;