利用TYPE解决IN列表过长的问题

介绍一种使用TYPE方式，解决IN列表过程的问题。

Oracle的in操作，最多支持1000个对象，如果超过这个值，则会报错：

SQL> CREATE TABLE T AS SELECT ROWNUM ID, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS;

表已创建。

SQL> SELECT COUNT(*) FROM TITPUB个人空间/f8])O9kLZ q.a6Z
  2  WHERE ID INITPUB个人空间9M1T:e7{`z ](xTe
  3  (ITPUB个人空间 `^/q)g nc
  4  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,ITPUB个人空间YUPE{oh
  5  22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,
_,lB?R U
g9w*H0  6  40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,
I8}(Ft%aW1UY0  7  58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,ITPUB个人空间2OaC'J C
  8  76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,ITPUB个人空间Z*b:]#Kw
  9  94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,
"PO:R{p%Zr'g0V#N0 10  109,110,111,112,113,114,115,116,117,118,119,120,121,122,
a)lo8y'u\r X(p0 11  123,124,125,126,127,128,129,130,131,132,133,134,135,136,ITPUB个人空间6mh9M"l
_ UN(l
 12  137,138,139,140,141,142,143,144,145,146,147,148,149,150,ITPUB个人空间A V7o!Q3f~
 13  151,152,153,154,155,156,157,158,159,160,161,162,163,164,
,m6h"~|/k{ Wk0 14  165,166,167,168,169,170,171,172,173,174,175,176,177,178,
z"Pp'TU-H b'BP0 15  179,180,181,182,183,184,185,186,187,188,189,190,191,192,
$u!t't9}3{FB0 16  193,194,195,196,197,198,199,200,201,202,203,204,205,206,ITPUB个人空间 tx!oQO.p
 17  207,208,209,210,211,212,213,214,215,216,217,218,219,220,
TJ0^0ww\0 18  221,222,223,224,225,226,227,228,229,230,231,232,233,234,
m-n!`:rt)s!\G0 19  235,236,237,238,239,240,241,242,243,244,245,246,247,248,
5D/B(fz$N'GxGl0 20  249,250,251,252,253,254,255,256,257,258,259,260,261,262,
s!yBpB0 21  263,264,265,266,267,268,269,270,271,272,273,274,275,276,ITPUB个人空间D8L4u g/[rbv
 22  277,278,279,280,281,282,283,284,285,286,287,288,289,290,
r fy q w0q!f d0 23  291,292,293,294,295,296,297,298,299,300,301,302,303,304,ITPUB个人空间B`!U3C7Q*oK`
 24  305,306,307,308,309,310,311,312,313,314,315,316,317,318,ITPUB个人空间*I \2N)Npi2K
 25  319,320,321,322,323,324,325,326,327,328,329,330,331,332,ITPUB个人空间"Z;V4i Soq!HY
 26  333,334,335,336,337,338,339,340,341,342,343,344,345,346,
W]DhN-@2T0 27  347,348,349,350,351,352,353,354,355,356,357,358,359,360,
#|y.j]+P1vWR0 28  361,362,363,364,365,366,367,368,369,370,371,372,373,374,ITPUB个人空间j/d*v4X&v!}
 29  375,376,377,378,379,380,381,382,383,384,385,386,387,388,
3p#\6Y!dD$Qe0i$cM0 30  389,390,391,392,393,394,395,396,397,398,399,400,401,402,
(f}2@-wzfvVQ(WC0 31  403,404,405,406,407,408,409,410,411,412,413,414,415,416,ITPUB个人空间5a h"kd&UuV_6|2yNl8Z
 32  417,418,419,420,421,422,423,424,425,426,427,428,429,430,
Mrj"]#[
p+@%cQ0 33  431,432,433,434,435,436,437,438,439,440,441,442,443,444,
d\1lq,Gg0 34  445,446,447,448,449,450,451,452,453,454,455,456,457,458,
#f'_-ab]q0 35  459,460,461,462,463,464,465,466,467,468,469,470,471,472,ITPUB个人空间 B:j%O9W,O3tf.X
 36  473,474,475,476,477,478,479,480,481,482,483,484,485,486,
.CF0F&`v@0 37  487,488,489,490,491,492,493,494,495,496,497,498,499,500,
L/dJAP a3y3N@0 38  501,502,503,504,505,506,507,508,509,510,511,512,513,514,ITPUB个人空间+LCbR[!YW\?*v
 39  515,516,517,518,519,520,521,522,523,524,525,526,527,528,
n*a"o5O,ZU0mW,a0 40  529,530,531,532,533,534,535,536,537,538,539,540,541,542,ITPUB个人空间N8Z/q [ _G
 41  543,544,545,546,547,548,549,550,551,552,553,554,555,556,
9Z7cJ l]Tb/kN0 42  557,558,559,560,561,562,563,564,565,566,567,568,569,570,ITPUB个人空间h)^1`F ~(@|j VMx
 43  571,572,573,574,575,576,577,578,579,580,581,582,583,584,
F/} m qf{XrO0 44  585,586,587,588,589,590,591,592,593,594,595,596,597,598,ITPUB个人空间O&L+i5^UA/ci n
 45  599,600,601,602,603,604,605,606,607,608,609,610,611,612,ITPUB个人空间~v"Bd lr)E!B1^
 46  613,614,615,616,617,618,619,620,621,622,623,624,625,626,ITPUB个人空间-X&Q7fJst.MB \D
 47  627,628,629,630,631,632,633,634,635,636,637,638,639,640,
1_(n t s,Ph0 48  641,642,643,644,645,646,647,648,649,650,651,652,653,654,

myWY$s0 49  655,656,657,658,659,660,661,662,663,664,665,666,667,668,
Ya0I8MA*vM0 50  669,670,671,672,673,674,675,676,677,678,679,680,681,682,
#U'H,AG[$Q+Xu0 51  683,684,685,686,687,688,689,690,691,692,693,694,695,696,ITPUB个人空间&ddT5M#{"TI^rNV
 52  697,698,699,700,701,702,703,704,705,706,707,708,709,710,
#t Ao'Mfx3BEfy0 53  711,712,713,714,715,716,717,718,719,720,721,722,723,724,
'a!^@k;i`(X7A`0 54  725,726,727,728,729,730,731,732,733,734,735,736,737,738,ITPUB个人空间I;]%h;Y&hG
 55  739,740,741,742,743,744,745,746,747,748,749,750,751,752,
IX};CLV;n/WR0 56  753,754,755,756,757,758,759,760,761,762,763,764,765,766,
0uk8N,O0MxN0 57  767,768,769,770,771,772,773,774,775,776,777,778,779,780,
:_MlE+T2T9_ X0 58  781,782,783,784,785,786,787,788,789,790,791,792,793,794,
H&_/~:sRv ^#r0 59  795,796,797,798,799,800,801,802,803,804,805,806,807,808,
?Ul%y{_$p2^0 60  809,810,811,812,813,814,815,816,817,818,819,820,821,822,
%y} F^tS)e-RNv/E&V0 61  823,824,825,826,827,828,829,830,831,832,833,834,835,836,ITPUB个人空间k
d!m,fp;J1T
 62  837,838,839,840,841,842,843,844,845,846,847,848,849,850,
&a T/Wc#?exO
w0 63  851,852,853,854,855,856,857,858,859,860,861,862,863,864,
)KP#H!{/ZWfT%h0 64  865,866,867,868,869,870,871,872,873,874,875,876,877,878,
uxe6~ C;J0 65  879,880,881,882,883,884,885,886,887,888,889,890,891,892,
O{{E})kF0 66  893,894,895,896,897,898,899,900,901,902,903,904,905,906,ITPUB个人空间Jg%}2W.V
 67  907,908,909,910,911,912,913,914,915,916,917,918,919,920,
"Qb:\9}5[0 68  921,922,923,924,925,926,927,928,929,930,931,932,933,934,ITPUB个人空间L o3dCn2g
 69  935,936,937,938,939,940,941,942,943,944,945,946,947,948,ITPUB个人空间#D:Z F#f"U2?:IP
 70  949,950,951,952,953,954,955,956,957,958,959,960,961,962,
w MCq6~Pb+h0 71  963,964,965,966,967,968,969,970,971,972,973,974,975,976,ITPUB个人空间R9y)S|+fnO+r
 72  977,978,979,980,981,982,983,984,985,986,987,988,989,990,
~+{if/q0 73  991,992,993,994,995,996,997,998,999,1000,1001,1002,1003ITPUB个人空间;uc"X9km2T6r&dq
 74  );ITPUB个人空间vCf:f#dHv
991,992,993,994,995,996,997,998,999,1000,1001,1002,1003
#UO{J:N0                                         *ITPUB个人空间"AEp
Fk
第73行出现错误:
i-BC J$b#Z0ORA-01795:列表中的最大表达式数为1000

当然使用拆分表达式的方式可以实现：

SQL> SELECT COUNT(*) FROM TITPUB个人空间2AUML;xkH
  2  WHERE ID IN
"WF:t*Q|2]7E0  3  (ITPUB个人空间m0P8C{;d ^-mI9G
  4  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,
&YFE)E1xv0  5  22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,ITPUB个人空间o*~c1Y-KmPO{
  6  40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,ITPUB个人空间&m4ue+jk
  7  58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,ITPUB个人空间B8A,oKrIJy\
  8  76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,ITPUB个人空间z&N~eLO0Z3S:Z*[
  9  94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,ITPUB个人空间8i,Pr,L;{3}
 10  109,110,111,112,113,114,115,116,117,118,119,120,121,122,ITPUB个人空间*A2K4hbUw"j
 11  123,124,125,126,127,128,129,130,131,132,133,134,135,136,
?4~"P4FF0 12  137,138,139,140,141,142,143,144,145,146,147,148,149,150,ITPUB个人空间3n:uD0h"s b
 13  151,152,153,154,155,156,157,158,159,160,161,162,163,164,
}L\!w&L_0 14  165,166,167,168,169,170,171,172,173,174,175,176,177,178,ITPUB个人空间$b&mSn fi C4n
 15  179,180,181,182,183,184,185,186,187,188,189,190,191,192,
|&iNR8P0 16  193,194,195,196,197,198,199,200,201,202,203,204,205,206,ITPUB个人空间/dv
q.Oih5D
 17  207,208,209,210,211,212,213,214,215,216,217,218,219,220,ITPUB个人空间Pti M&U_Z!~0g9b
 18  221,222,223,224,225,226,227,228,229,230,231,232,233,234,
&F5[tGBZx*D"u'w0 19  235,236,237,238,239,240,241,242,243,244,245,246,247,248,
'y?)cfSN0 20  249,250,251,252,253,254,255,256,257,258,259,260,261,262,
H,SF_1P0 21  263,264,265,266,267,268,269,270,271,272,273,274,275,276,
9_ M3}0@[K0 22  277,278,279,280,281,282,283,284,285,286,287,288,289,290,
5S G].} GhZt0 23  291,292,293,294,295,296,297,298,299,300,301,302,303,304,ITPUB个人空间,m;X;tS+T5VNWx
 24  305,306,307,308,309,310,311,312,313,314,315,316,317,318,
_`m'ha ^yJG/c0 25  319,320,321,322,323,324,325,326,327,328,329,330,331,332,
'f!h.Z&B `ue0 26  333,334,335,336,337,338,339,340,341,342,343,344,345,346,ITPUB个人空间$DU6oh0^8N${/F#I l
 27  347,348,349,350,351,352,353,354,355,356,357,358,359,360,ITPUB个人空间cu/L5S@/hY
 28  361,362,363,364,365,366,367,368,369,370,371,372,373,374,ITPUB个人空间2p+H5VYxv
 29  375,376,377,378,379,380,381,382,383,384,385,386,387,388,
8y"Y5R^(Q3R0 30  389,390,391,392,393,394,395,396,397,398,399,400,401,402,ITPUB个人空间 {M@RJ!Si4R
 31  403,404,405,406,407,408,409,410,411,412,413,414,415,416,
0K,z;qi!b m'w0SDc0 32  417,418,419,420,421,422,423,424,425,426,427,428,429,430,ITPUB个人空间] AjR&Y
 33  431,432,433,434,435,436,437,438,439,440,441,442,443,444,
{Z#OxBj%k)d0 34  445,446,447,448,449,450,451,452,453,454,455,456,457,458,
}:M,q*E$w3f0 35  459,460,461,462,463,464,465,466,467,468,469,470,471,472,ITPUB个人空间t:K'A'b-wE-y7eq
 36  473,474,475,476,477,478,479,480,481,482,483,484,485,486,ITPUB个人空间^-AZk*xw
q
{4S
 37  487,488,489,490,491,492,493,494,495,496,497,498,499,500,ITPUB个人空间8w%OY~3C3_3?3C'j3U8QD
 38  501,502,503,504,505,506,507,508,509,510,511,512,513,514,
WI1Dh^:U&fuQe+v po0 39  515,516,517,518,519,520,521,522,523,524,525,526,527,528,ITPUB个人空间*O;^0L9n.\y`Up6[
 40  529,530,531,532,533,534,535,536,537,538,539,540,541,542,
@%N.f_n0 41  543,544,545,546,547,548,549,550,551,552,553,554,555,556,ITPUB个人空间w~/rw\
 42  557,558,559,560,561,562,563,564,565,566,567,568,569,570,ITPUB个人空间R#S)P%tk w
 43  571,572,573,574,575,576,577,578,579,580,581,582,583,584,ITPUB个人空间:a8@ jee3t
 44  585,586,587,588,589,590,591,592,593,594,595,596,597,598,
s4R.I4\b7M0 45  599,600,601,602,603,604,605,606,607,608,609,610,611,612,
y%nH:x"Mu
yR0 46  613,614,615,616,617,618,619,620,621,622,623,624,625,626,ITPUB个人空间%u4J YNVB|
 47  627,628,629,630,631,632,633,634,635,636,637,638,639,640,ITPUB个人空间-VF4T1E(s{Xa Y
 48  641,642,643,644,645,646,647,648,649,650,651,652,653,654,ITPUB个人空间J
L w+v;Hcl
 49  655,656,657,658,659,660,661,662,663,664,665,666,667,668,
WMtJ:}-K9CI0 50  669,670,671,672,673,674,675,676,677,678,679,680,681,682,ITPUB个人空间yVa Jx%Ow
 51  683,684,685,686,687,688,689,690,691,692,693,694,695,696,
Yq\I/r%`,j;Z"M$siL0 52  697,698,699,700,701,702,703,704,705,706,707,708,709,710,ITPUB个人空间ZX;rI-^Mj
 53  711,712,713,714,715,716,717,718,719,720,721,722,723,724,
/Zn]l;]v\0 54  725,726,727,728,729,730,731,732,733,734,735,736,737,738,ITPUB个人空间!CZ-X)t'a"f'O
 55  739,740,741,742,743,744,745,746,747,748,749,750,751,752,ITPUB个人空间/|mssA&?
 56  753,754,755,756,757,758,759,760,761,762,763,764,765,766,ITPUB个人空间)R0D9q%ZA-k;o
 57  767,768,769,770,771,772,773,774,775,776,777,778,779,780,ITPUB个人空间Qy q|~g
 58  781,782,783,784,785,786,787,788,789,790,791,792,793,794,
l0vm V&p!~&C F ^)UcZ0 59  795,796,797,798,799,800,801,802,803,804,805,806,807,808,ITPUB个人空间X4Xs(_#kD'C%G,P
 60  809,810,811,812,813,814,815,816,817,818,819,820,821,822,ITPUB个人空间6GM4{/BV#r4cL`
 61  823,824,825,826,827,828,829,830,831,832,833,834,835,836,ITPUB个人空间2cKAuc gC @+kQ4F.Q
 62  837,838,839,840,841,842,843,844,845,846,847,848,849,850,ITPUB个人空间 z@ t5`nV/k\$M{
 63  851,852,853,854,855,856,857,858,859,860,861,862,863,864,ITPUB个人空间\x@0DiH!pE
 64  865,866,867,868,869,870,871,872,873,874,875,876,877,878,
)RJpz-YH0 65  879,880,881,882,883,884,885,886,887,888,889,890,891,892,ITPUB个人空间#I)cAZ h
 66  893,894,895,896,897,898,899,900,901,902,903,904,905,906,
W9g2u.g0G1}|0 67  907,908,909,910,911,912,913,914,915,916,917,918,919,920,ITPUB个人空间)F-r%~0tmxj
 68  921,922,923,924,925,926,927,928,929,930,931,932,933,934,ITPUB个人空间B%fh0Q dI[_
N
 69  935,936,937,938,939,940,941,942,943,944,945,946,947,948,
V0c9f ` Tz0 70  949,950,951,952,953,954,955,956,957,958,959,960,961,962,ITPUB个人空间dh*i1?8r Du
 71  963,964,965,966,967,968,969,970,971,972,973,974,975,976,ITPUB个人空间 `$e6p_4M
 72  977,978,979,980,981,982,983,984,985,986,987,988,989,990,ITPUB个人空间&A+my-GxAG
 73  991,992,993,994,995,996,997,998,999,1000)ITPUB个人空间)s;_#^,C7C!A"Q7{
 74  OR ID IN (1001,1002,1003);

  COUNT(*)ITPUB个人空间9~P QZg xd
----------
W^IG,ml0      1003

用UNION的方式也可以达到上面的效果，不过这种方式存在一个问题，如果IN列表的数据量很大，那么SQL将使用OR或UNION分成多个部分，而且必须确保每个部分的IN列表中的数量都不超过1000，这是很难通过程序来进行控制的。

通过使用TYPE，来构造一个嵌套表的方式，可以彻底解决上面的问题：

SQL> CREATE OR REPLACE TYPE T_IN IS TABLE OF NUMBER;ITPUB个人空间g [?MY
  2  /

类型已创建。

SQL> CREATE OR REPLACE FUNCTION F_TO_T_IN (P_IN VARCHAR2) RETURN T_IN ASITPUB个人空间7Q~8^7M TF
  2   V_RETURN T_IN DEFAULT T_IN();
.kC~"@'M+y*M4U&do)m0  3   V_IN VARCHAR2(32767);
+_*Xd4B%trvT'` Q0  4   V_COUNT NUMBER DEFAULT 0;ITPUB个人空间D3u'|KT
  5  BEGINITPUB个人空间2Mp2iNh
  6   V_IN := REPLACE(P_IN || ',', CHR(10), '');
"@oHqv os0@l0  7   WHILE(INSTR(V_IN, ',') > 0) LOOP
soE6l]'h0  8    V_RETURN.EXTEND;ITPUB个人空间2z;Wb3Cj
  9    V_COUNT := V_COUNT + 1;
q5a%[pdA/M7f)C0 10    V_RETURN(V_COUNT) := SUBSTR(V_IN, 1, INSTR(V_IN, ',') - 1);
r@}sc7O8oT0 11    V_IN := SUBSTR(V_IN, INSTR(V_IN, ',') + 1);
4X!U]$AaL0 12   END LOOP;ITPUB个人空间 d'i7|9}i ~~
 13   RETURN V_RETURN;ITPUB个人空间]GA,\/[ V
 14  END;ITPUB个人空间
SM(i mtCk(W cZ L/B
 15  /

函数已创建。

SQL> SELECT COUNT(*) FROM T A, TABLE(SELECT F_TO_T_IN(ITPUB个人空间-L{Z|a1kM%{5a!c8B
  2  '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,ITPUB个人空间5n@5}F3zzI@%`U'xDN
  3  22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,
Yz6Q2SuR0  4  40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,ITPUB个人空间1VzJ
r(?0r
G
  5  58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,ITPUB个人空间o3w8Yuc9LHt4fi#S
  6  76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,ITPUB个人空间6p~I5u m
  7  94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,
:i['qQ?$Zs0  8  109,110,111,112,113,114,115,116,117,118,119,120,121,122,
o)j$b!f7T'S&uF h0  9  123,124,125,126,127,128,129,130,131,132,133,134,135,136,ITPUB个人空间wWd9W;v:xU
 10  137,138,139,140,141,142,143,144,145,146,147,148,149,150,ITPUB个人空间{6U:N${V'Q+l$hH
 11  151,152,153,154,155,156,157,158,159,160,161,162,163,164,ITPUB个人空间I6?Mk'Q&`Vo
 12  165,166,167,168,169,170,171,172,173,174,175,176,177,178,
w1M)l5D
Fd0 13  179,180,181,182,183,184,185,186,187,188,189,190,191,192,ITPUB个人空间!w7v)F5q@x Y
 14  193,194,195,196,197,198,199,200,201,202,203,204,205,206,ITPUB个人空间(R,z0iAl'lba!N
 15  207,208,209,210,211,212,213,214,215,216,217,218,219,220,
.R2i_;|`,tU0 16  221,222,223,224,225,226,227,228,229,230,231,232,233,234,ITPUB个人空间P Wu,n)HO\d*l
 17  235,236,237,238,239,240,241,242,243,244,245,246,247,248,ITPUB个人空间"u1v`7B{:{%J
 18  249,250,251,252,253,254,255,256,257,258,259,260,261,262,
q)A0`@`q9{:n|0 19  263,264,265,266,267,268,269,270,271,272,273,274,275,276,
L7e nw8~A S0 20  277,278,279,280,281,282,283,284,285,286,287,288,289,290,ITPUB个人空间a8IS)XXCA
 21  291,292,293,294,295,296,297,298,299,300,301,302,303,304,
B(A dZ$r `I r.k3q0 22  305,306,307,308,309,310,311,312,313,314,315,316,317,318,
H
R7p7I4aHfd0 23  319,320,321,322,323,324,325,326,327,328,329,330,331,332,
G~5h)AJ%ZE6a)lF0 24  333,334,335,336,337,338,339,340,341,342,343,344,345,346,
8u8g xS6C%F0 25  347,348,349,350,351,352,353,354,355,356,357,358,359,360,ITPUB个人空间8|];oNs t2?-AD
 26  361,362,363,364,365,366,367,368,369,370,371,372,373,374,
(T]6HTs2[/A0 27  375,376,377,378,379,380,381,382,383,384,385,386,387,388,ITPUB个人空间N D0yUI.y ~
 28  389,390,391,392,393,394,395,396,397,398,399,400,401,402,ITPUB个人空间Y;c)c.{(PxpM
 29  403,404,405,406,407,408,409,410,411,412,413,414,415,416,ITPUB个人空间4W#DFf})I?},Y9vo;Q
 30  417,418,419,420,421,422,423,424,425,426,427,428,429,430,
I,Hp1nA
d[cx0 31  431,432,433,434,435,436,437,438,439,440,441,442,443,444,
"O0j*NV2_;H:|0a"F O6w%Q0 32  445,446,447,448,449,450,451,452,453,454,455,456,457,458,
/i8D"l$^6jGB-w4l0 33  459,460,461,462,463,464,465,466,467,468,469,470,471,472,ITPUB个人空间4m,dV;W [9Y
 34  473,474,475,476,477,478,479,480,481,482,483,484,485,486,ITPUB个人空间KOM[-A*Mdj
P$t-}G
 35  487,488,489,490,491,492,493,494,495,496,497,498,499,500,
D6`cr-KNhT0 36  501,502,503,504,505,506,507,508,509,510,511,512,513,514,ITPUB个人空间2\{y2~Z9Yw
 37  515,516,517,518,519,520,521,522,523,524,525,526,527,528,ITPUB个人空间9O:u(D4A#I^;f8ix
 38  529,530,531,532,533,534,535,536,537,538,539,540,541,542,ITPUB个人空间#Pf~fUq
 39  543,544,545,546,547,548,549,550,551,552,553,554,555,556,ITPUB个人空间2}"_$L+v8Y#whb
 40  557,558,559,560,561,562,563,564,565,566,567,568,569,570,ITPUB个人空间e3KMj4w7k/m
 41  571,572,573,574,575,576,577,578,579,580,581,582,583,584,
T,u2T+u g2T*O%lu}0 42  585,586,587,588,589,590,591,592,593,594,595,596,597,598,ITPUB个人空间yo.A/Y_0H8e5A
 43  599,600,601,602,603,604,605,606,607,608,609,610,611,612,
9v dAH\-g0 44  613,614,615,616,617,618,619,620,621,622,623,624,625,626,
6m5^K2Chj"ZD0 45  627,628,629,630,631,632,633,634,635,636,637,638,639,640,ITPUB个人空间_dFZ9s{`
K%bK
 46  641,642,643,644,645,646,647,648,649,650,651,652,653,654,
(SjLaB4`"m0 47  655,656,657,658,659,660,661,662,663,664,665,666,667,668,ITPUB个人空间ti7c6DX\#v/p-?
 48  669,670,671,672,673,674,675,676,677,678,679,680,681,682,ITPUB个人空间8\j4|,T.pb4ve3h
 49  683,684,685,686,687,688,689,690,691,692,693,694,695,696,
,pNt:{ u/Y[V0 50  697,698,699,700,701,702,703,704,705,706,707,708,709,710,
(h,d&H+G!cA T0 51  711,712,713,714,715,716,717,718,719,720,721,722,723,724,
d)SPp.P0 52  725,726,727,728,729,730,731,732,733,734,735,736,737,738,
"VWl R4P0 53  739,740,741,742,743,744,745,746,747,748,749,750,751,752,ITPUB个人空间Uu%`\&gu:v%y
 54  753,754,755,756,757,758,759,760,761,762,763,764,765,766,
/SB$a,c/v0 55  767,768,769,770,771,772,773,774,775,776,777,778,779,780,
0eF%`#d k7~}0 56  781,782,783,784,785,786,787,788,789,790,791,792,793,794,
;T"w$`2ZU0l$U9J/H\d0 57  795,796,797,798,799,800,801,802,803,804,805,806,807,808,ITPUB个人空间HGY?!KJ5N
 58  809,810,811,812,813,814,815,816,817,818,819,820,821,822,ITPUB个人空间(d0~:KVqG*Q
 59  823,824,825,826,827,828,829,830,831,832,833,834,835,836,
5N
f!Z(q/Y/D0 60  837,838,839,840,841,842,843,844,845,846,847,848,849,850,
/r:|[a$Y.mL}(H)e0 61  851,852,853,854,855,856,857,858,859,860,861,862,863,864,ITPUB个人空间j!V)C pQ-a/x!~9Ry
 62  865,866,867,868,869,870,871,872,873,874,875,876,877,878,ITPUB个人空间$DY.YCx:Di9T{
 63  879,880,881,882,883,884,885,886,887,888,889,890,891,892,ITPUB个人空间bEGx5U ?
~(G
 64  893,894,895,896,897,898,899,900,901,902,903,904,905,906,ITPUB个人空间8QbwnX;\
 65  907,908,909,910,911,912,913,914,915,916,917,918,919,920,ITPUB个人空间"OI#l6`O aYqq*jo*P
 66  921,922,923,924,925,926,927,928,929,930,931,932,933,934,
x+v
nDmWYd0 67  935,936,937,938,939,940,941,942,943,944,945,946,947,948,ITPUB个人空间-o6z4TW0ral
c,d
 68  949,950,951,952,953,954,955,956,957,958,959,960,961,962,
F1h#`(HrQ3ho`0 69  963,964,965,966,967,968,969,970,971,972,973,974,975,976,ITPUB个人空间$_lN"].q}&uR
 70  977,978,979,980,981,982,983,984,985,986,987,988,989,990,
!m&h`w1p
R-f7F8k0 71  991,992,993,994,995,996,997,998,999,1000,1001,1002,1003'ITPUB个人空间nu;M8TC)g'Y
 72  ) FROM DUAL) BITPUB个人空间,t*WF8[ })h&W
 73  WHERE A.ID = B.COLUMN_VALUEITPUB个人空间1VV'z}`@)Wl)I
 74  ;

  COUNT(*)ITPUB个人空间9x {v(~0?7sl)r6[cl x
----------
6T^4HX OB)ua$i \0      1003

这种方式对IN的列表数量没有限制，当然，当前给出的例子很简单，只是针对NUMBER类型的，而且使用VARCHAR2类型来传递列表的值，仍然存在长度的限制，只需要将过程稍微修改一下，改为CLOB类型作为参数，就不在存在长度的限制。至于其他数据类型的实现，方法与此大同小异，这里就不重复了。