[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资源有点多。
–//也许还有其他的方法，我不知道。
来源链接：https://www.cnblogs.com/lfree/p/18637977
如有侵犯您的版权，请及时联系3500663466#qq.com(#换@)，我们将第一时间删除本站数据。
暂无评论内容