将数组传递到 oracle sql 并使用该数组

发布于 2024-10-18 10:45:27 字数 539 浏览 2 评论 0原文

我遇到了以下问题，我将一个字符串数组传递到 Oracle SQL，并且我想检索其 id 位于列表中的所有数据...

这是我尝试过的...

OPEN O_default_values FOR 
SELECT  ID AS "Header",
        VALUE AS "DisplayValue", 
        VALUE_DESC AS "DisplayText"
FROM TBL_VALUES
WHERE ID IN I_id;

I_id 是一个数组描述如下 - TYPE gl_id IS TABLE OF VARCHAR2(15) INDEX BY PLS_INTEGER;

我收到“表达式类型错误”错误。 I_id 数组有时可能多达 600 条记录。我的问题是，有没有办法完成我刚才描述的操作，或者我是否需要创建某种游标并循环遍历数组？

已尝试过的方法 - 动态创建 SQL 字符串，然后将值连接到 SQL 字符串的末尾，然后执行它。这适用于少量数据，并且字符串的大小是静态的，这将导致一些其他错误（例如索引超出范围）。

原文

I am running into the following problem, I am passing an array of string into Oracle SQL, and I would like to retrieve all the data where its id is in the list ...

here's what i've tried ...

OPEN O_default_values FOR 
SELECT  ID AS "Header",
        VALUE AS "DisplayValue", 
        VALUE_DESC AS "DisplayText"
FROM TBL_VALUES
WHERE ID IN I_id;

I_id is an array described as follows - TYPE gl_id IS TABLE OF VARCHAR2(15) INDEX BY PLS_INTEGER;

I've been getting the "expression is of wrong type" error.
The I_id array can sometimes be as large as 600 records.
My question is, is there a way to do what i just describe, or do i need to create some sort of cursor and loop through the array?

What has been tried - creating the SQL string dynamically and then con-cat the values to the end of the SQL string and then execute it. This will work for small amount of data and the size of the string is static, which will caused some other errors (like index out of range).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蓝眸 2024-10-25 10:45:27

看看这个链接： http ://asktom.oracle.com/pls/asktom/f?p=100:11:620533477655526::::P11_QUESTION_ID:139812348065

实际上，您想要的是带有绑定变量的列表中的变量。

请注意这一点：

“the”已被弃用。不需要它
今天。
TABLE 是它的替代品
从表中选择*（函数）；

由于您已经拥有该类型，因此您需要做的就是类似于以下内容：

OPEN O_default_values FOR 
SELECT  ID AS "Header",
        VALUE AS "DisplayValue", 
        VALUE_DESC AS "DisplayText"
FROM TBL_VALUES
WHERE ID IN (select column_value form  table(I_id));

have a look at this link: http://asktom.oracle.com/pls/asktom/f?p=100:11:620533477655526::::P11_QUESTION_ID:139812348065

effectively what you want is a variable in-list with bind variables.

do note this:

"the" is deprecated. no need for it
today.
TABLE is it's replacement
select * from TABLE( function );

since you already have the type, all you need to do is something similar to below:

OPEN O_default_values FOR 
SELECT  ID AS "Header",
        VALUE AS "DisplayValue", 
        VALUE_DESC AS "DisplayText"
FROM TBL_VALUES
WHERE ID IN (select column_value form  table(I_id));

回复收藏 0 原文

~没有更多了~