在Oracle中插入基于字段值的记录数

SQL> with test (quoteid, tariff_length, cost) as
  2    (select 310, 4, 12 from dual union
  3     select 311, 6, 16 from dual
  4    )
  5  select rownum as commid, quoteid, cost
  6  from test,
  7       table(cast(multiset(select level from dual
  8                           connect by level <= tariff_length
  9                          ) as sys.odcinumberlist));

    COMMID    QUOTEID       COST
---------- ---------- ----------
         1        310         12
         2        310         12
         3        310         12
         4        310         12
         5        311         16
         6        311         16
         7        311         16
         8        311         16
         9        311         16
        10        311         16

10 rows selected.

SQL>

with tblquotesnew (quoteid, tariff_length, cost) as (
            select 310, 4, 12 from dual
  union all select 311, 6, 16 from dual
)
select rownum as commid, quoteid, cost
from tblquotesnew
cross join xmltable ('1 to xs:integer($n)' passing tariff_length as "n");

    COMMID    QUOTEID       COST
---------- ---------- ----------
         1        310         12
         2        310         12
         3        310         12
         4        310         12
         5        311         16
         6        311         16
         7        311         16
         8        311         16
         9        311         16
        10        311         16

insert into tblcommnew (commid, quoteid, cost)
select rownum, quoteid, cost
from tblquotesnew
cross join xmltable ('1 to xs:integer($n)' passing tariff_length as "n");

10 rows inserted.