CODEWITHSUNDEEP
oracle11g
PS078
PS078

Reputation: 461

PL SQL: How to populate a cursor in loop

I have a scenario to implement where in we will have an SP with two parameters 1) In parameter of array of Varchar2 2) Cursor -- Out parameter

We have populate the cursor only for each Value passed in array and this opened/populated cursor will be used by Java. But what i have found till now is, if we have to process an array then it should have to be done using loop.

For ex:

Open Cursor_Name
For 
For index In Arrar_Parameter.FIRST .. Arrar_Parameter.LAST
LOOP
    SELECT * FROM EMP WHERE EmpId = Arrar_Parameter[i] -------> This needs to be looped and not sure if this will work
END LOOP

Can we have some thing like this

Open Cursor_Name
For 
SELECT * FROM EMP WHERE EmpId IN (Arrar_Parameter values) ------> To fetch/put all the array values at once without loop.

Kindly suggest hot to populate cursor in this scenario

Upvotes: 0

Views: 1672

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

Assuming your array parameter is a schema-level varray or nested table, you can use a table collection expression:

The table_collection_expression lets you inform Oracle that the value of collection_expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value—that is, a value whose type is nested table or varray. This process of extracting the elements of a collection is called collection unnesting.

This uses a built-in varray type but you can substitute your own:

create procedure procedure_name (
  array_parameter sys.odcivarchar2list, cursor_name out sys_refcursor
) as
begin
  open cursor_name for
    select e.*
    from table (array_parameter) a
    join emp e on e.empid = a.column_value;
end;
/

You can also use in if you prefer:

create procedure procedure_name (
  array_parameter sys.odcivarchar2list, cursor_name out sys_refcursor
) as
begin
  open cursor_name for
    select *
    from emp
    where empid in (select column_value from table (array_parameter));
end;
/

If it is a nested table you can also use the member of syntax:

create type my_varchar2_table as table of varchar2(30);
/

create procedure procedure_name (
  array_parameter my_varchar2_table, cursor_name out sys_refcursor
) as
begin
  open cursor_name for
    select *
    from emp
    where empid member of array_parameter;
end;
/

Upvotes: 1

Related Questions