ANBOB™

今天有人问题到这个问题，in（）里可以有多少个，以前从没试过那么多，刚做了个试验，如下

SQL> select count(*) from icme_noproject_score;                                 
 
  COUNT(*)
----------
  16659919
 
SQL> create table test (id int);                                                
 
Table created.
 
SQL>                                                                            
SQL> select * from test where id in(select id from icme_noproject_score);       
 
no rows selected


SQL> run                                                                                                                                                    
  1  create or replace procedure ptestin(pnum int)
  2    is
  3    v_sql varchar2(10000);
  4    v_p varchar2(4000);
  5    v_c number;
  6    begin
  7    v_sql := 'select count(*) from test where id in(0';
  8       for i in 1..pnum loop
  9         v_p:=v_p||','||i;
 10       end loop;
 11       v_sql :=v_sql||v_p||')';
 12     execute immediate v_sql into v_c;
 13        dbms_output.put_line(pnum ||'args in !'||v_c);
 14*   end;
 
Procedure created.
 
SQL> exec ptestin(10);                                                                                                                                      
10args in !0
 
PL/SQL procedure successfully completed.
 
SQL> exec ptestin(100);                                                                                                                                     
100args in !0
 
PL/SQL procedure successfully completed.
 
SQL> exec ptestin(1000);                                                                                                                                    
BEGIN ptestin(1000); END;
 
*
ERROR at line 1:
ORA-01795: maximum number of expressions in a list is 1000
ORA-06512: at "ICME.PTESTIN", line 12
ORA-06512: at line 1
 
 
SQL> exec ptestin(999);                                                                                                                                     
999args in !0
 
PL/SQL procedure successfully completed.

突破这个限制可以用分为多个in or

打赏

微信扫一扫，打赏作者吧～