How can I give multiple input values in PLSQL using record type, object and constructor?

Question

Could you help me to write a PLSQL code to pass 'multiple input values' for a single variable? Please help me how can I do it by using Record type, object and constructors to write Anonymous PLSQL block.

Thank You,

Regards, Veeresh

Answer 1

Here is one way:

HR@XE> CREATE OR REPLACE TYPE NUM_ARRAY AS TABLE OF NUMBER;
  2  /

Type created.

HR@XE> create or replace procedure demo_sp (
  in_array in num_array,
  out_sum out number
  )
  is
  BEGIN
  out_sum := 0;
  for i in 1 ..in_array.count loop
  out_sum := out_sum + in_array(i);
  END LOOP;
 END;
 /

Procedure created.

HR@XE> DECLARE
total  NUMBER;
nos NUM_ARRAY;
BEGIN
SELECT ROWNUM BULK COLLECT  INTO nos  FROM dual CONNECT BY ROWNUM<=5;
FOR I IN 1 .. nos.COUNT LOOP
dbms_output.put_line(nos(i));
end loop;
demo_sp(nos,total);
dbms_output.put_line('Total is: '||total);
END;
/
1
2
3
4
5
Total is: 15

PL/SQL procedure successfully completed.

HR@XE>