将存储在PL/SQL变量中的值传递给IN子句 [英] Pass value stored in a PL/SQL variable into an IN clause

问题描述

我的代码如下:

DECLARE
vr_arr_list VARCHAR2(100) := `7,3,4';
BEGIN
FOR CX IN (Select ID, NAME from TBL_DEMO where ID IN (vr_arr_list))
LOOP
DBMS_OUTPUT.PUT_LINE(CX.ID || '-' || CX.NAME);
END LOOP;
/

我也尝试过使用

DBMS_UTILITY.comma_to_table (list => REGEXP_REPLACE (vr_arr_list, '(^|,)','\1x'), tablen => l_ID_count, tab => l_ID_array);

但是传递数组l_ID_array也不起作用.

But passing the array l_ID_array also didn't work.

请协助.

推荐答案

另一种方法是结合使用嵌套表和TABLE运算符

Another way is to make use of Nested tables in conjunction with TABLE operator

create type nt_vr_arr_list is table of number;

DECLARE
  vr_arr_list  nt_vr_arr_list := nt_vr_arr_list(100, 200, 330);
BEGIN
  FOR cx IN (SELECT id, name
               FROM tbl_demo
              WHERE id IN (SELECT COLUMN_VALUE FROM TABLE(vr_arr_list))) LOOP
    DBMS_OUTPUT.put_line('ID: ' || cx.id || ' Name: ' || cx.name);
  END LOOP;
END;

这篇关于将存储在PL/SQL变量中的值传递给IN子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！