在数组中循环查找where条件pl/sql [英] looping through an array for the where condition pl/sql
问题描述
在pl/sql中是否有可能循环遍历需要在pl/sql语句的WHERE子句中使用的许多ID. sql语句本身非常简单,但是我需要遍历许多id:
Is it possible in pl/sql to loop through a number of id's that need to go in the WHERE clause of the pl/sql statement. The sql statement itself is pretty simple, but I need to iterate over a number of id's:
SELECT x_name
FROM table_x
WHERE x_id = {array of 90 id's};
如何在此处插入90个id,以便sql遍历它们?我尝试使用游标For Loop,但遇到问题.下面的代码是错误的,但是它可能表明我在这里想要实现的目标
How can I insert the 90 id's here so that sql iterates over them? I tried using the cursor For Loop, but I'm stuck. The code below is erroneous, but it might give an indication what Im trying to achieve here
DECLARE
TYPE x_id_array IS VARRAY(3) OF NUMBER;
CURSOR cur_x_id (x_ondz_id NUMBER) IS
SELECT x_name
FROM table_x
WHERE x_id = var_ondz_id;
loop_total integer;
x_id x_id_array;
name VARCHAR;
BEGIN
x_id_new := x_id_array(8779254, 8819930, 8819931); --3 for testing
loop_total := x_id_new.count;
FOR i in 1 .. loop_total LOOP
dbms_output.put_line('x_id: ' || x_id_new(i) || '= Name: ' || x_name );
END LOOP;
END;
/
预期的投放量为
x_id: 8779254= Name: Name_1
x_id: 8819930= Name: Name_2
x_id: 8819931= Name: Name_3
...
... etc for all 90 id's in the array
感谢您的帮助
推荐答案
我们可以在集合上使用TABLE
函数来获取数字/字符列表.
We can use TABLE
function on a collection to get a list of numbers / character.
SELECT *
FROM TABLE ( sys.odcinumberlist(8779254,8819930,8819931) );
8779254
8819930
8819931
这里我使用的是Oracle内部的VARRAY
,限制为32767.您可以使用自己的NESTED TABLE
类型.
Here I'm using Oracle's internal VARRAY
with a limit of 32767. You may use your own NESTED TABLE
type.
create OR REPLACE TYPE yourtype AS TABLE OF NUMBER;
然后选择它.
SELECT *
FROM TABLE ( yourtype(8779254,8819930,8819931) );
因此,您的查询可以简单地写为
So, your query can simply be written as
SELECT x_name
FROM table_x
WHERE x_id IN ( SELECT * FROM
TABLE ( yourtype(8779254,8819930,8819931) ) );
12.2及更高版本,您甚至无需指定TABLE
.
12.2 and above, you won't even need to specify TABLE
.
SELECT * FROM yourtype(8779254,8819930,8819931)
有效.
这篇关于在数组中循环查找where条件pl/sql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!