[20250418]绑定变量太多的限制.txt

[20250418]绑定变量太多的限制.txt --//看看最多1条sql语句能有多少绑定变量。 1.环境： SCOTT@book01p> @ ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2.建立测试环境： create table t as select * from all_objects; create unique index i_t_object_id on t(object_id); --//分析略。 $ cat a.txt set pagesize 0 set head off set feedback off set verify off set timing off spool b1.txt select 'set termout off'  from dual; select 'variable b'||to_char(level)||' number;'  from dual connect by level<=&1; select 'begin'  from dual; select ':b'||to_char(level)||' :='|| to_char(level)||';'  from dual connect by level<=&1; select 'end;'  from dual; select '/' from dual ; select 'set termout on' txt from dual; spool off spool b2.txt --select 'set timing on' txt from dual; select 'select count(data_object_id) from t where (1,object_id) in (' txt from dual ; select '(1,:b'||to_char(level)||'),' txt from dual connect by level<=&&1 -1 ; select '(1,:b'||to_char(&&1)||'));' txt from dual ; --select 'set timing off' txt from dual; spool off set pagesize 9999 set head on set feedback on 3.测试1,begin与end之间只能赋值32767个变量： SCOTT@book01p> @ a.txt 32768 --//输出略。 --//编辑b1.txt，删除开头的set termout off， set termout on。 SCOTT@book01p> @ b1.txt :b829 :=829;         * ERROR at line 830: ORA-06550: line 32770, column 1: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PLS-00123: program too large (codegen operands) $ sed -n "32770p" b1.txt :b1 :=1; --//删除:b32768 :=32768;一行，再测试。 SCOTT@book01p> @ b1.txt PL/SQL procedure successfully completed. --//说明再begin..end之间可以定义32767个绑定变量。 4.测试2,一条sql语句能出现多少绑定变量： SCOTT@book01p> @ a.txt 65536 --//输出略。 --//编辑b1.txt，删除开头的set termout off， set termout on，并且适当编辑满足前面begin..end之间可以定义32767个绑定变量的 --//限制。 $ egrep -C 1 --no-group-separator "begin|end" b1.txt variable b65536 number; begin :b1 :=1; :b32767 :=32767; end; / begin :b32768 :=32768; :b65534 :=65534; end; / begin :b65535 :=65535; :b65536 :=65536; end; / SCOTT@book01p> @ b1.txt PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. SCOTT@book01p> @ b2.txt select count(data_object_id) from t where (1,object_id) in (                                                            * ERROR at line 1: ORA-00920: invalid relational operator $ oerr ora 00920 00920, 00000, "invalid relational operator" // *Cause: // *Action: --//不知道是否超过集合的限制. --//编辑b2.txt 注解其中一行,看看情况的变化： $ head -2 b2.txt ;tail -3 b2.txt select count(data_object_id) from t where (1,object_id) in ( (1,:b1), (1,:b65534), --(1,:b65535), (1,:b65536)); SCOTT@book01p> @ b2.txt COUNT(DATA_OBJECT_ID) ---------------------                  3970 --//可以发现sql语句里面存在65535个绑定变量，超过65536报错。 --//编辑b2.txt,再测试看看： $ head -2 b2.txt ;tail -5 b2.txt select count(data_object_id) from t where (1,object_id) in ( (1,:b1), (1,:b65534)) or (1,object_id) in ( (1,:b65535), (1,:b65536)); SCOTT@book01p> @ b2.txt select count(data_object_id) from t where (1,object_id) in ( * ERROR at line 1: ORA-00920: invalid relational operator --//说明不是超过集合的限制. --//可以看出一条sql语句里面存在65535个绑定变量。