Mega Code Archive
Append result from generator function to a table
<![CDATA[
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>
]]>