今天有人问题到这个问题,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
打赏
微信扫一扫,打赏作者吧~