Return a varray from a function : Function Return « Stored Procedure Function « Oracle PL / SQL

1. Oracle PL / SQL
2. Stored Procedure Function
3. Function Return

Return a varray from a function

   


SQL>
SQL> create table gift(
  2   gift_id NUMBER,
  3   emp_id NUMBER,
  4   register_date DATE ,
  5   total_price NUMBER(7,2),
  6   DELIVER_DATE DATE,
  7   DELIVER_TIME VARCHAR2(7),
  8   payment VARCHAR2(2) ,
  9   EMP_NO NUMBER(3,0),
 10   DELIVER_NAME VARCHAR2(35),
 11   message VARCHAR2(100)
 12   )
 13  storage(initial 50m);

Table created.

SQL>
SQL> alter table gift
  2  add constraint gift_pk primary key(gift_id);

Table altered.

SQL>
SQL> analyze table gift compute statistics;

Table analyzed.

SQL>
SQL> create or replace type gift_type as object
  2   ( gift_id NUMBER,
  3   emp_id NUMBER,
  4   register_date DATE ,
  5   total_price NUMBER(7,2),
  6   DELIVER_DATE DATE,
  7   DELIVER_TIME VARCHAR2(7),
  8   payment VARCHAR2(2) ,
  9   EMP_NO NUMBER(3,0),
 10   DELIVER_NAME VARCHAR2(35),
 11   message VARCHAR2(100)
 12   );
 13  /



SQL>
SQL> create or replace type gift_table as table of gift_type;
  2  /

Type created.

SQL>
SQL> create or replace function gift_generator_piped
  2     (p_num_rows in number)
  3     RETURN gift_table
  4     PIPELINED
  5  AS
  6  BEGIN
  7
  8      for i in 1..p_num_rows loop
  9          PIPE ROW ( gift_type(i,i,sysdate,0,sysdate,null,'CA',1,null,null ));
 10      end loop;
 11
 12      return;
 13
 14  END;
 15  /

Function created.

SQL> show errors
No errors.
SQL>
SQL>
SQL> declare
  2      v_start  number;
  3      v_run1    number;
  4  begin
  5      v_start := dbms_utility.get_time;
  6      insert /*+APPEND */ into gift (select * from table(gift_generator_piped(1000) ) );
  7      v_run1 := dbms_utility.get_time - v_start;
  8      dbms_output.put_line('piped '||v_run1);
  9
 10  end;
 11  /
piped 2

PL/SQL procedure successfully completed.

SQL> drop table gift;

Table dropped.

SQL>

   
    
    
  

Related examples in the same category