Table variable join equivalent in Oracle-CodePudding

I'm given quite a huge table My_Table and a user-defined collection Picture_Arr as an input usually having 5-10 rows declared as:

TYPE Picture_Rec IS RECORD (
     seq_no                         NUMBER,
     task_seq                       NUMBER); 
TYPE Picture_Arr IS TABLE OF Picture_Rec;

In MS SQL I would normally write something like:

DECLARE @Picture_Arr TABLE (seq_no INT, task_seq INT)
SELECT M.*
FROM My_Table M
INNER JOIN @Picture_Arr A
  ON M.seq_no = A.seq_no AND M.task_seq = A.task_seq

But I can't get my head around how to re-write the same code in Oracle as Picture_Arr is not a table. As some tutorials state that I could've looped through My_Table and compare keys, but is it efficient in Oracle or is there another way of doing that?

CodePudding user response：

Perhaps this is what you are looking for. It is a bit complicated to understand what is the desired output, and whether the data of the record is stored somewhere or not

create type Picture_Rec as object(
seq_no                         NUMBER,
task_seq                       NUMBER); 
)
/

create type Picture_Tab as table of Picture_Rec
/

create or replace function get_picture_list
return Picture_Tab
is
  l_pic Picture_Tab;
begin
   select Picture_Rec ( seqno, taskseq )
   bulk collect into l_pic
   from your_table; -- the table you have these records
   return l_pic;
end;
/

Then you run

SELECT M.*
FROM My_Table M
JOIN TABLE ( get_picture_list() ) p 
  ON M.seq_no = p.seq_no AND M.task_seq = p.task_seq