--某同事说报表sql相关的存储过程的输入参数如传入一个大字符串报错,真是如些吗 

---创建业务表(订单表)所引用的订单类型数据
SQL> create table zidian(order_type varchar2(10));
                                                 
Table created                                    
                                                 
SQL> insert into zidian values('100000');        
                                                 
1 row inserted                                   
                                                 
SQL> insert into zidian values('100001');        
                                                 
1 row inserted                                   
                                                 
SQL> insert into zidian values('100002');        
                                                 
1 row inserted                                   
                                                 
SQL> insert into zidian values('100003');        
                                                 
1 row inserted                                   
                                                 
SQL> insert into zidian values('100004');        
                                                 
1 row inserted                                   
                                                 
SQL> commit;                                     
                                                 
Commit complete      

--创建业务表并插入数据
SQL> create table t_order(order_id int,order_type varchar2(10));                           
                                                               
Table created  

SQL> insert into t_order values(1,'100000');                                               
                                           
1 row inserted                             
                                           
SQL> insert into t_order values(2,'100001');
                                           
1 row inserted                             
                                           
SQL> insert into t_order values(3,'100002');
                                           
1 row inserted                             
                                           
SQL> commit;                               
                                           
Commit complete 

 

SQL> select * from zidian;                                                                                     
                                                                                    
ORDER_TYPE                                                                          
----------                                                                          
100000                                                                              
100001                                                                              
100002                                                                              
100003                                                                              
100004                                                                              
                                                                                    
SQL> select * from t_order;                                                         
                                                                                    
                               ORDER_ID ORDER_TYPE                                  
--------------------------------------- ----------                                  
                                      1 100000                                      
                                      2 100001                                      
                                      3 100002                                      
                                                                                    
SQL> ---实现filter掉order_type为100000的订单数据                                    
SQL> select * from t_order where order_type in ('100001','100002','100003','100004');
                                                                                    
                               ORDER_ID ORDER_TYPE                                  
--------------------------------------- ----------                                  
                                      2 100001                                      
                                      3 100002   
                                     

---如果用存储过程又如何实现上述功能呢

SQL> create or replace procedure proc_filter(in_filter varchar2)                                                                         
  2  as                                                                                                                                              
  3  v_sql varchar2(4000);                                                          
  4  v_cnt pls_integer;                                                             
  5  begin                                                                          
  6    v_sql:='select count(*) from t_order where order_type in'||in_filter;        
  7    dbms_output.put_line(v_sql);                                                 
  8    execute immediate v_sql into v_cnt;                                          
  9    dbms_output.put_line(v_cnt);                                                 
 10  end;                                                                           
 11  /                                                                              
                                                                                    
Procedure created                                                                   
                                                                                                                           
SQL> exec proc_filter('(''100001'',''100002'',''100003'',''100004'')');             
                                                                                    
select count(*) from t_order where order_type in('100001','100002','100003','100004')
2                                                                                   
                                                                                    
PL/SQL procedure successfully completed                                             

 

小结;1,还是转义字符的使用