我在 PL/Sql 过程中通过附加 USERNAME、单引号 (') 和逗号 (,) 的形式动态构造一个名为 user_data 的字符串
I am dynamically constructing a string with name user_data in PL/Sql procedure by appending USERNAMEs, single quotes(') and commas(,) of the form
'abc123','xyz456','pqr789'
但是当我将此字符串传递给 SELECT 语句的 WHERE IN 条件时
But when I pass this string to WHERE IN condition of SELECT statement
SELECT * FROM table_name WHERE USERNAME IN (user_data)
它正在抛出一个 NO_DATA_FOUND 异常.
It is throwing a NO_DATA_FOUND exception.
另一方面,如果我的字符串只包含一个没有引号的用户,它能够找到该用户并显示所需的输出.
On the other hand if my string contains only one user without the quotes, it is able to find that user and display desired output.
字符串user_data的数据类型是varchar2.
The datatype of string user_data is varchar2.
您可以使用集合来完成:
You can do it using a collection:
CREATE TYPE VARCHAR2s_Table IS TABLE OF VARCHAR2(100);
/
然后像这样输入您的数据:
Then enter your data like this:
SELECT *
FROM table_name
WHERE user_data MEMBER OF VARCHAR2s_Table( 'abc123','xyz456','pqr789' );
或者:
您可以创建一个函数来拆分数据并生成集合:
You can create a function to split the data and generate the collection:
CREATE TYPE VARCHAR2_TABLE AS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE FUNCTION split_String(
i_str IN VARCHAR2,
i_delim IN VARCHAR2 DEFAULT ','
) RETURN VARCHAR2_TABLE DETERMINISTIC
AS
p_result VARCHAR2_TABLE := VARCHAR2_TABLE();
p_start NUMBER(5) := 1;
p_end NUMBER(5);
c_len CONSTANT NUMBER(5) := LENGTH( i_str );
c_ld CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
IF c_len > 0 THEN
p_end := INSTR( i_str, i_delim, p_start );
WHILE p_end > 0 LOOP
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
p_start := p_end + c_ld;
p_end := INSTR( i_str, i_delim, p_start );
END LOOP;
IF p_start <= c_len + 1 THEN
p_result.EXTEND;
p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
END IF;
END IF;
RETURN p_result;
END;
/
然后你可以这样做:
SELECT *
FROM table_name
WHERE user_data MEMBER OF split_String( 'abc123,xyz456,pqr789', ',' );
或:
SELECT *
FROM table_name
WHERE user_data MEMBER OF split_String( TRIM( '''' FROM '''abc123'',''xyz456'',''pqr789''' ), ''',''' );
这篇关于WHERE IN 条件不接受字符串值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!