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里的内容动态产生...求教...谢谢

解决方案 »

  1.   

          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';
      

  2.   

    楼上的哥哥,不行哦...'||mgrantstr||'得到的是||mgrantstr||
      

  3.   

    楼上正解,open for 打开游标本身支持动态拼接语句
      

  4.   

          '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';
    应该是这样...多谢2楼...
      

  5.   

    ..刚才没看清楚
    非字符型变量最好不要这样拼接,改成
          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;