[20241227]字符串转换成列表问题.txt
[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 | | | |
----------------------------------------
--//开发经常会写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 | | | |
----------------------------------------