首页 » ORACLE » oracle in (,,)最多多少个枚举项 ORA-01795

oracle in (,,)最多多少个枚举项 ORA-01795

今天有人问题到这个问题,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

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Leroy Rizer | #1
    2011-12-21 at 07:40

    I and my buddies ended up following the nice secrets found on the website and then all of a sudden came up with a terrible suspicion I never expressed respect to the site owner for those secrets. Those young boys were definitely consequently warmed to study all of them and now have absolutely been enjoying these things. We appreciate you actually being indeed helpful and also for getting some tremendous guides millions of individuals are really needing to be aware of. My personal sincere apologies for not saying thanks to sooner.