Oracle plsql use value from type in query-CodePudding

I have type

create or replace TYPE days_nt FORCE IS TABLE OF VARCHAR2(10);

In plsql procedure I have:

PROCEDURE test_proc (
      p_header_id   IN   mumber
    , p_days_ids      days_nt
)
AS
Begin

FOR d IN 1..p_days_ids.count LOOP    
     insert into demo(test) values(p_days_ids(d));
    --Here I have eg.  1 and 7
END LOOP;

Select * from my_table where id in ( 1 and 7 from above example);

How use value 1 and 7 from p_days_ids in query?

CodePudding user response：

You can use the MEMBER OF operator with nested table collection data types (but not with VARRAY data types, but that does not apply here);
You need to SELECT ... [BULK COLLECT] INTO ... FROM ... in PL/SQL (or use a cursor); and
The INSERT can be simplified to use INSERT ... SELECT ... , which will be more efficient as you only perform a single insert rather than multiple inserts and will handle missing indexes if the array is sparse.

CREATE PROCEDURE test_proc (
  p_header_id IN number
, p_days_ids  IN days_nt
)
AS
  TYPE my_table_arr IS TABLE OF my_table%ROWTYPE;
  v_arr my_table_arr;
BEGIN
  INSERT INTO demo(test)
  SELECT column_value FROM TABLE(p_days_ids);

  Select *
  BULK COLLECT INTO v_arr
  from   my_table
  where  id MEMBER OF p_days_ids;

  -- Do something with the v_arr collection.
END;
/

Then you can call the procedure using:

BEGIN
  test_proc(42, days_nt('1', '7'));
END;
/

Then you can use:

Select *
from   demo
where  test in (1, 7);

Which outputs:

TEST
1
7

fiddle

CodePudding user response：

This is how I understood it.

Type:

SQL> CREATE OR REPLACE TYPE days_nt FORCE IS TABLE OF VARCHAR2 (10);
  2  /

Type created.

Sample table (description taken from procedure's INSERT statement):

SQL> CREATE TABLE demo
  2  (
  3     test   VARCHAR2 (10)
  4  );

Table created.

Procedure:

SQL> CREATE OR REPLACE PROCEDURE test_proc (p_header_id  IN NUMBER,
  2                                         p_days_ids      days_nt)
  3  AS
  4  BEGIN
  5     FOR d IN 1 .. p_days_ids.COUNT
  6     LOOP
  7        INSERT INTO demo (test)
  8             VALUES (p_days_ids (d));
  9     --Here I have eg.  1 and 7
 10     END LOOP;
 11  END;
 12  /

Procedure created.

Testing: calling the procedure with 1 and 7 as values:

SQL> BEGIN
  2     test_proc (1, days_nt (1, 7));
  3  END;
  4  /

PL/SQL procedure successfully completed.

Result:

SQL> SELECT *
  2    FROM demo
  3   WHERE test IN (SELECT * FROM TABLE (days_nt (1, 7)));

TEST
----------
1
7

SQL>