oracle 在此查询中使用 SQL IN 的替代方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13068753/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alternatives to using SQL IN in this query
提问by hohenhiem
I have a string holding comma separated values = value1, value2, value3 .......
我有一个包含逗号分隔值的字符串 = value1, value2, value3 ......
I want to perform following operation:
我想执行以下操作：
SELECT col FROM table WHERE col IN :values
This works fine if values have less than 1000 entries. It gives error when values have more than 1000 entries. There is a limit on usage of IN.
如果值少于 1000 个条目，这可以正常工作。当值超过 1000 个条目时，它会出错。IN的使用有限制。
Is there any alternative way to perform this query?
有没有其他方法可以执行此查询？
EDIT: It is Oracle's Business Intelligence Publisher application. Customer/User can use any database underneath.
编辑：它是 Oracle 的商业智能发布应用程序。客户/用户可以使用下面的任何数据库。
I do not have control over database. So I cannot create a temp table or stored procedure. All I can do is select multiple values from UI screen (it forms comma separated string) and use it in a SQL query. Depending on which reports are generated.
我无法控制数据库。所以我无法创建临时表或存储过程。我所能做的就是从 UI 屏幕中选择多个值（它形成逗号分隔的字符串）并在 SQL 查询中使用它。取决于生成的报告。
- Cannot use EXISTS with static string values.
- Cannot use stored procedure or temp table.
- 不能对静态字符串值使用 EXISTS。
- 不能使用存储过程或临时表。
采纳答案by Wolf
If you cannotcreate a global temporary table, then you could convert your delimited list to rows using sys.dbms_debug_vc2coll()and join to this collection.
如果您无法创建全局临时表，那么您可以将分隔列表转换为使用sys.dbms_debug_vc2coll()并加入此集合的行。
SELECT t.col
FROM table t
JOIN TABLE(SELECT column_value
FROM sys.dbms_debug_vc2coll(:values)) c on t.col = c.column_value;
回答by 0xCAFEBABE
The most performance will take a bit of crafting. I would suggest building an in-memory temporary table that holds your values, each in one row, and then join your real table to this temporary table. This will make your query significantly faster and, as an added bonus, the number of rows in the temporary table is not limited (or rather, only limited by memory).
最好的性能需要一些技巧。我建议建立一个内存中的临时表来保存你的值，每个值都在一行中，然后将你的真实表加入到这个临时表中。这将使您的查询显着更快，并且作为额外的好处，临时表中的行数不受限制（或者更确切地说，仅受内存限制）。
回答by jalynn2
You could insert the values into a temp table and then do a join:
您可以将值插入到临时表中，然后进行连接：
SELECT col from table t1 JOIN #temp t2 WHERE table.col = t2.col;
回答by Ali Issa
Create a stored procedure to do this job for you like this:
创建一个存储过程来为您完成这项工作，如下所示：
Create PROCEDURE [dbo].[spGetData]
-- Add the parameters for the stored procedure here
@ids nvarchar(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #TempTable(col int)
while len(@ids) > 0
begin
insert into #TempTable values (left(@ids, charindex(',', @ids+',')-1))
set @ids = stuff(@ids, 1, charindex(',', @ids+','), '')
end
-- Insert statements for procedure here
select col from table where col in (select col from #TempTable)
END
Source is from my blog: http://alisissa.wordpress.com/2012/10/24/pass-a-comma-separated-list-to-a-stored-procedure/
来源来自我的博客：http: //alisissa.wordpress.com/2012/10/24/pass-a-comma-separated-list-to-a-stored-procedure/
回答by Ted
We've used the following function for years to split CSV strings passed into stored procedures. (Apologies if it isn't the most modern, elegant or efficient way of doing it, but it's worked well for our purposes)
多年来，我们一直使用以下函数来拆分传递给存储过程的 CSV 字符串。（抱歉，如果这不是最现代、最优雅或最有效的方式，但它对我们的目的很有效）
CREATE FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20) = ' ')
RETURNS @Strings TABLE
(
position int IDENTITY,
value varchar(8000)
)
AS
BEGIN
DECLARE @index int
SET @index = -1
SET @text = LTRIM(RTRIM(@text))
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter , @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @Strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
RETURN
END
In use it's:
在使用中是：
select value from dbo.fn_Split('string1,string2,string3',',')
(Oh, and this is for an MS SQL Server database btw)
（哦，顺便说一句，这是针对 MS SQL Server 数据库的）
