在数组中循环查找where条件pl/sql

编程入门行业动态更新时间:2024-10-27 08:26:16

本文介绍了在数组中循环查找where条件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) );