[20241227]字符串转换成列表问题.txt

来源：这里教程网时间：2026-03-03 21:11:38 作者：

[20241227]字符串转换成列表问题.txt --//开发经常会写sql语句，经常会出现in ('111122','1111113'..,'2222111')之类的情况，一般语句in里面内容经常变化，导致无法使 --//用绑定变量。 --//实际上以前例子，通过建立type，然后建立函数将拼接的字符串转换为数字或者字符串列表。 --//前几天有人问及这方面问题，当时建议到网上检索str2numlist,str2varlist就能找到相关例子，照抄就可以了，然后写代码先拼接 --//字符串，这样就可以实现绑定变量。 --//实际上网上的例子没有使用pipe row，感觉这样能节约一些内存，自己修改看看，顺便解答一些问题： 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.修改为pipe row： CREATE OR REPLACE TYPE numtabletype AS TABLE OF NUMBER / CREATE OR REPLACE FUNCTION str2numlist (p_string IN VARCHAR2) RETURN numtabletype PIPELINED AS v_str LONG := p_string || ','; v_n PLS_INTEGER; v_index PLS_INTEGER := 1; BEGIN LOOP v_n := TO_NUMBER (INSTR (v_str, ',',v_index)); EXIT WHEN (NVL (v_n, 0) = 0); PIPE ROW ( LTRIM (RTRIM (SUBSTR(p_string, v_index, v_n - v_index)))); v_index := v_n + 1; END LOOP; RETURN; END; / CREATE OR REPLACE TYPE vartabletype AS TABLE OF VARCHAR2 (4000) / --//感觉不需要定义4000长度，100就可以了。 CREATE OR REPLACE FUNCTION str2varlist (p_string IN VARCHAR2) RETURN vartabletype PIPELINED AS v_str LONG := p_string || ','; v_n PLS_INTEGER; v_index PLS_INTEGER := 1; BEGIN LOOP v_n := TO_NUMBER (INSTR (v_str, ',',v_index)); EXIT WHEN (NVL (v_n, 0) = 0); PIPE ROW ( LTRIM (RTRIM (SUBSTR(p_string, v_index, v_n - v_index)))); v_index := v_n + 1; END LOOP; RETURN; END; / 3.测试看看： SCOTT@book01p> @ sl all alter session set statistics_level = all; Session altered. SCOTT@book01p> select * from STR2numlist('10,30') ; COLUMN_VALUE ------------ 10 30 SCOTT@book01p> select column_value c10 from STR2varlist('10,30') ; C10 ---------- 10 30 --//OK. --//执行计划如下： SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID ffk198p3px8qx, child number 0 ------------------------------------- select column_value c10 from STR2varlist('10,30') Plan hash value: 3286354863 --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | --------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 29 (100)| | 2 |00:00:00.01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | 1 | 8168 | 16336 | 29 (0)| 00:00:01 | 2 |00:00:00.01 | --------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$F5BB74E1 / "KOKBF$0"@"SEL$2" --//注意一些细节，这样返回行数，oracle缺省返回行数是8168，这样可能导致采用这种方式的sql语句趋向不使用索引甚至连接顺序发 --//生了改变。 SCOTT@book01p> variable s varchar2(4000); SCOTT@book01p> exec :s := '10,30'; PL/SQL procedure successfully completed. SCOTT@book01p> select * from dept where deptno in (select * from STR2numlist(:s) ); DEPTNO DNAME LOC ---------- ------------------------------ ------------- 10 ACCOUNTING NEW YORK 30 SALES CHICAGO SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 6pw75tbqncf8q, child number 0 ------------------------------------- select * from dept where deptno in (select * from STR2numlist(:s) ) Plan hash value: 1925696018 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 32 (100)| | 2 |00:00:00.01 | 6 | | | | |* 1 | HASH JOIN SEMI | | 1 | 1 | 22 | 32 (0)| 00:00:01 | 2 |00:00:00.01 | 6 | 1399K| 1399K| 1045K (0)| | 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 6 | | | | | 3 | COLLECTION ITERATOR PICKLER FETCH| STR2NUMLIST | 1 | 8168 | 16336 | 29 (0)| 00:00:01 | 2 |00:00:00.01 | 0 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$09D7319C 2 - SEL$09D7319C / "DEPT"@"SEL$1" 3 - SEL$09D7319C / "KOKBF$0"@"SEL$3" Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("DEPTNO"=VALUE(KOKBF$)) 27 rows selected. --//执行计划选择全部扫描，连接顺序先dept，然后是STR2NUMLIST。 --//当然对于大表也许问题不大，再看下面的例子： SCOTT@book01p> create table t as select * from all_objects; Table created. SCOTT@book01p> create index i_t_objecy_name on t(object_name); Index created. SCOTT@book01p> exec :s := 'DEPT,EMP, OBJ$'; SCOTT@book01p> select object_id,object_name from t where object_name in (select * from STR2varlist(:s) ); OBJECT_ID OBJECT_NAME ---------- ------------------------------ 18 OBJ$ 76193 EMP 76191 DEPT SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 2ndn1kd52kg01, child number 0 ------------------------------------- select object_id,object_name from t where object_name in (select * from STR2varlist(:s) ) Plan hash value: 3517368828 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 442 (100)| | 3 |00:00:00.05 | 1483 | | | | |* 1 | HASH JOIN RIGHT SEMI | | 1 | 1 | 43 | 442 (1)| 00:00:01 | 3 |00:00:00.05 | 1483 | 2171K| 2171K| 786K (0)| | 2 | COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | 1 | 8168 | 16336 | 29 (0)| 00:00:01 | 3 |00:00:00.01 | 0 | | | | | 3 | TABLE ACCESS FULL | T | 1 | 70025 | 2803K| 412 (1)| 00:00:01 | 70025 |00:00:00.02 | 1483 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$09D7319C 2 - SEL$09D7319C / "KOKBF$0"@"SEL$3" 3 - SEL$09D7319C / "T"@"SEL$1" Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJECT_NAME"=VALUE(KOKBF$)) --//还发现一个细节，看不见绑定变量值。连接顺序先STR2VARLIST，然后是T表，但是执行计划没有使用索引。 SCOTT@book01p> @ modtab t numblks 40000 input argument list : owner.table_name table_stat_attribute value table_stat_attribute = numrows numblks avgrlen @desc_proc sys dbms_stats set_table_stats exec dbms_stats.set_table_stats('SCOTT','T',numblks=>40000,NO_INVALIDATE=>false,force=>true); press enter to continue ... PL/SQL procedure successfully completed. --//修改表t数据块40000。 SCOTT@book01p> select object_id,object_name from t where object_name in (select * from STR2varlist(:s) ); OBJECT_ID OBJECT_NAME ---------- ------------------------------ 76191 DEPT 76193 EMP 18 OBJ$ SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 2ndn1kd52kg01, child number 1 ------------------------------------- select object_id,object_name from t where object_name in (select * from STR2varlist(:s) ) Plan hash value: 2618245243 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 796 (100)| | 3 |00:00:00.01 | 12 | | | | | 1 | NESTED LOOPS | | 1 | 306 | 13158 | 796 (1)| 00:00:01 | 3 |00:00:00.01 | 12 | | | | | 2 | NESTED LOOPS | | 1 | 306 | 13158 | 796 (1)| 00:00:01 | 3 |00:00:00.01 | 9 | | | | | 3 | SORT UNIQUE | | 1 | 8168 | 16336 | 29 (0)| 00:00:01 | 3 |00:00:00.01 | 0 | 2048 | 2048 | 2048 (0)| | 4 | COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | 1 | 8168 | 16336 | 29 (0)| 00:00:01 | 3 |00:00:00.01 | 0 | | | | |* 5 | INDEX RANGE SCAN | I_T_OBJECY_NAME | 3 | 1 | | 2 (0)| 00:00:01 | 3 |00:00:00.01 | 9 | | | | | 6 | TABLE ACCESS BY INDEX ROWID | T | 3 | 1 | 41 | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 3 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$09D7319C 4 - SEL$09D7319C / "KOKBF$0"@"SEL$3" 5 - SEL$09D7319C / "T"@"SEL$1" 6 - SEL$09D7319C / "T"@"SEL$1" Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("OBJECT_NAME"=VALUE(KOKBF$)) 32 rows selected. --//当然还有许多方法改变执行计划，这样至少不再出现大量文字变量相关语句，当然不是没有缺点，传入的字符串最大长度4000，如果 --//大于4000，这样方式也不行。 --//还原表统计信息： SCOTT@book01p> exec dbms_stats.gather_table_stats('SCOTT', 'T', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false) PL/SQL procedure successfully completed. SCOTT@book01p> select /*+ CARDINALITY("KOKBF$0"@"SEL$3" 10) */ object_id,object_name from t where object_name in (select * from STR2varlist(:s) ); OBJECT_ID OBJECT_NAME ---------- ------------------------------ 76191 DEPT 76193 EMP 18 OBJ$ SCOTT@book01p> @ dpc '' '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 60d5pcpx8qr9h, child number 0 ------------------------------------- select /*+ CARDINALITY("KOKBF$0"@"SEL$3" 10) */ object_id,object_name from t where object_name in (select * from STR2varlist(:s) ) Plan hash value: 2618245243 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 45 (100)| | 3 |00:00:00.01 | 12 | | | | | 1 | NESTED LOOPS | | 1 | 12 | 516 | 45 (3)| 00:00:01 | 3 |00:00:00.01 | 12 | | | | | 2 | NESTED LOOPS | | 1 | 12 | 516 | 45 (3)| 00:00:01 | 3 |00:00:00.01 | 9 | | | | | 3 | SORT UNIQUE | | 1 | 10 | 20 | 29 (0)| 00:00:01 | 3 |00:00:00.01 | 0 | 2048 | 2048 | 2048 (0)| | 4 | COLLECTION ITERATOR PICKLER FETCH| STR2VARLIST | 1 | 10 | 20 | 29 (0)| 00:00:01 | 3 |00:00:00.01 | 0 | | | | |* 5 | INDEX RANGE SCAN | I_T_OBJECY_NAME | 3 | 1 | | 2 (0)| 00:00:01 | 3 |00:00:00.01 | 9 | | | | | 6 | TABLE ACCESS BY INDEX ROWID | T | 3 | 1 | 41 | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 3 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$09D7319C 4 - SEL$09D7319C / "KOKBF$0"@"SEL$3" 5 - SEL$09D7319C / "T"@"SEL$1" 6 - SEL$09D7319C / "T"@"SEL$1" Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("OBJECT_NAME"=VALUE(KOKBF$)) 32 rows selected. --//通过CARDINALITY提示，设定返回10行，也可以控制执行计划。 4.其他： --//前面没有抓到绑定变量的值，开始以为是隐含参数_cursor_bind_capture_area_size值，缺省400,测试还是无法抓取到绑定变量值。 SYS@book> @ hidez _cursor_bind_capture_area_size SYS@book> @ pr ============================== NUM : 4287 N_HEX : 10BF NAME : _cursor_bind_capture_area_size DESCRIPTION : maximum size of the cursor bind capture area DEFAULT_VALUE : TRUE SESSION_VALUE : 400 SYSTEM_VALUE : 400 ISSES_MODIFIABLE : FALSE ISSYS_MODIFIABLE : IMMEDIATE PL/SQL procedure successfully completed. --//除了前面介绍的方法，实际上还有其他的例子，收集整理如下： --//使用xmltable。 SCOTT@book01p> variable s varchar2(2000); SCOTT@book01p> exec :s := '10,20'; PL/SQL procedure successfully completed. SCOTT@book01p> select * from dept where deptno in (select (column_value).getnumberval() from xmltable(:s)); DEPTNO DNAME LOC ---------- ------------------------------ ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS SCOTT@book01p> exec :s := '"SALES","RESEARCH"'; PL/SQL procedure successfully completed. --//注意这种方式，字符串里面有双引号 SCOTT@book01p> select * from dept where dname in (select (column_value).getstringval() from xmltable(:s)); DEPTNO DNAME LOC ---------- ------------------------------ ------------- 30 SALES CHICAGO 20 RESEARCH DALLAS --//换成单引号。 SCOTT@book01p> exec :s := '''SALES'',''RESEARCH'''; PL/SQL procedure successfully completed. SCOTT@book01p> select * from dept where dname in (select (column_value).getstringval() from xmltable(:s)); DEPTNO DNAME LOC ---------- ------------------------------ ------------- 30 SALES CHICAGO 20 RESEARCH DALLAS SCOTT@book01p> exec :s := q'['SALES','RESEARCH']'; PL/SQL procedure successfully completed. SCOTT@book01p> select * from dept where dname in (select (column_value).getstringval() from xmltable(:s)); DEPTNO DNAME LOC ---------- ------------------------------ ------------- 30 SALES CHICAGO 20 RESEARCH DALLAS SCOTT@book01p> exec :s := 'SALES,RESEARCH'; PL/SQL procedure successfully completed. SCOTT@book01p> select * from dept where dname in (select (column_value).getstringval() from xmltable(:s)); select * from dept where dname in (select (column_value).getstringval() from xmltable(:s)) * ERROR at line 1: ORA-19112: error raised during evaluation: XVM-01002: [XPDY0002] Dynamic context component 'context item' has no value $ oerr ora 19112 19112, 00000, "error raised during evaluation: %s" // *Cause: The error function was called during evaluation of the XQuery expression. // *Action: Check the detailed error message for the possible causes. --//没有单双引号的字符串报错。 SCOTT@book01p> exec :s := 'SALES,RESEARCH'; PL/SQL procedure successfully completed. SCOTT@book01p> exec :s := '"'||replace(:s,',','","')||'"' PL/SQL procedure successfully completed. SCOTT@book01p> select * from dept where dname in (select (column_value).getstringval() from xmltable(:s)); DEPTNO DNAME LOC ---------- ------------------------------ ------------- 30 SALES CHICAGO 20 RESEARCH DALLAS --//奇怪改写如下不行。 SCOTT@book01p> exec :s := 'SALES,RESEARCH'; PL/SQL procedure successfully completed. SCOTT@book01p> select * from dept where dname in (select (column_value).getstringval() from xmltable( '"'||replace(:s,',','","')||'"')); select * from dept where dname in (select (column_value).getstringval() from xmltable( '"'||replace(:s,',','","')||'"')) * ERROR at line 1: ORA-02000: missing COLUMNS keyword --//使用正则表达式REGEXP_SUBSTR+CONNECT BY： SCOTT@book01p> exec :s := '10,20'; PL/SQL procedure successfully completed. SCOTT@book01p> SELECT * FROM dept WHERE deptno IN ( SELECT TO_number (REGEXP_SUBSTR ( :s ,'[^,]+' ,1 ,LEVEL)) FROM DUAL CONNECT BY REGEXP_SUBSTR ( :s ,'[^,]+',1 ,LEVEL) IS NOT NULL); DEPTNO DNAME LOC ---------- ------------------------------ ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS SCOTT@book01p> exec :s := 'SALES,RESEARCH'; PL/SQL procedure successfully completed. SCOTT@book01p> SELECT * FROM dept WHERE dname IN ( SELECT TO_char (REGEXP_SUBSTR ( :s ,'[^,]+' ,1 ,LEVEL)) FROM DUAL CONNECT BY REGEXP_SUBSTR ( :s ,'[^,]+',1 ,LEVEL) IS NOT NULL); DEPTNO DNAME LOC ---------- ------------------------------ ------------- 20 RESEARCH DALLAS 30 SALES CHICAGO --//注意使用正则表达式的方式，如果变量很多，该方式消耗CPU资源有点多。 --//也许还有其他的方法，我不知道。

编辑推荐：

返回列表

[20241227]字符串转换成列表问题.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推