Append result from generator function to a table

    
SQL>
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>
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(p_num_rows in number)
  2     RETURN gift_table
  3  AS
  4      v_gift_table     gift_TABLE := gift_table();
  5  BEGIN
  6
  7      for i in 1..p_num_rows loop
  8          v_gift_table.EXTEND;
  9          v_gift_table(i) := ( gift_type(i,i,sysdate,0,sysdate,null,'CA',1,null,null ));
 10
 11      end loop;
 12
 13      return v_gift_table;
 14
 15  END;
 16  /

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(100) ) );
  7      v_run1 := dbms_utility.get_time - v_start;
  8      dbms_output.put_line('no pipe '||v_run1);
  9  end;
 10  /
no pipe 5

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> drop table gift;

Table dropped.

SQL>

Related examples in the same category

1.	Return varchar2 value from function
2.	Function return Integer
3.	Return value from a function
4.	Use function return value in select statement
5.	Save the returning value from a procedure to a variable
6.	RETURN statement.
7.	Multiple RETURN Statements
8.	Return a type
9.	Concatenates two strings into one:
10.	Return a varray from a function
11.	We use user function in DML statements
12.	This script demonstrates using a record type as a function return value
13.	Use function to check passwords
14.	Use user-defined function to combine and format columns

Append result from generator function to a table : Function Return « Stored Procedure Function « Oracle PL / SQL

Related examples in the same category