SQLでINSERT文を生成

SQLでinsert文を生成してみた！おまけでcreate文(中途半端)も。
テストデータはinsert文じゃないと嫌な人は、これに手を加えて再利用できるかも？

クエリ

with
  param as (
    select
      'SAKURAGI' as owner,
      'EMP' as table_name
    from
      dual
  ),
  seq as (
    select
      rownum as value
    from
      dual
    connect by
      rownum <= 5
  ),
  data as (
    select
      s.value,
      tc.table_name,
      tc.column_id,
      tc.column_name,
      tc.data_type,
      tc.nullable,
      case
        when tc.data_type = any('CHAR', 'VARCHAR2') then
          tc.data_type || '(' || tc.char_length || ')'
        when tc.data_type = 'NUMBER' then
          tc.data_type || '(' || tc.data_length || ',' || nvl(tc.data_scale, 0) || ')'
        else
          tc.data_type
      end as data_type_text,
      case
        when tc.data_type = any('CHAR', 'VARCHAR2') then
          lpad(s.value, tc.char_length, '0')
        when tc.data_type = 'DATE' then
          'to_date(''' || to_char(sysdate+s.value, 'yyyy-mm-dd HH24:mi:ss') || ''', ''yyyy-mm-dd HH24:mi:ss'')'
        else
          rtrim(to_char(s.value) || '.' || lpad(s.value, tc.data_scale, '0'), '.')
      end as sample_value
    from
      all_tab_cols tc,
      seq s,
      param
    where
      tc.owner = param.owner and
      tc.table_name = param.table_name
    order by
      tc.column_id
  ),
  formatted_data as (
    select
      d.*,
      case
        when d.data_type = any('CHAR', 'VARCHAR2') then
          '''' || d.sample_value || ''''
        else
          d.sample_value
      end as formatted_value
    from
      data d
    order by
      d.column_id
  ),
  idx_info as (
    select
      col.column_id,
      col.table_name,
      col.column_name,
      ind_col.index_name,
      cons.constraint_name,
      cons.constraint_type
    from
      user_tab_cols col
      inner join user_ind_columns ind_col on
        ind_col.table_name = col.table_name and
        ind_col.column_name = col.column_name
      inner join user_constraints cons on
        cons.table_name = ind_col.table_name and
        cons.index_name = ind_col.index_name,
      param
    where
      col.table_name = param.table_name
  ),
  create_parts as (
    select
      d.table_name,
      listagg(
        d.column_name || ' ' ||
          d.data_type_text ||
          (case when d.nullable = 'N' then ' not null' end),
        ', '
      ) within group (order by d.column_id) as column_list
    from
      data d
    where
      d.value = 1
    group by
      d.table_name
  ),
  insert_parts as (
    select
      d.value,
      d.table_name,
      ----- case 1 -----
      --rtrim(regexp_replace(xmlagg(xmlelement(TAG,d.column_name||',') order by d.column_id), '', ''), ',') as column_list,
      --replace(rtrim(regexp_replace(xmlagg(xmlelement(TAG,d.formatted_value||',') order by d.column_id), '', ''), ','), chr(38)||'apos;', '''') as value_list
      ----- case 2 -----
      listagg(d.column_name, ', ') within group (order by d.column_id) as column_list,
      listagg(d.formatted_value, ', ') within group (order by d.column_id) as value_list
    from
      formatted_data d
    group by
      d.value,
      d.table_name
  )
select
  'create table ' || p.table_name || ' (' || p.column_list ||
    (case when idx.column_list is not null then (', primary key(' || idx.column_list || ')') end) || ');' as query
from
  create_parts p
  left outer join (
    select
      idx.table_name,
      listagg(idx.column_name, ', ') within group (order by idx.column_id) as column_list
    from
      idx_info idx
    group by
      idx.table_name
  ) idx on idx.table_name = p.table_name
union all
select
  'insert into ' || p.table_name || ' (' || p.column_list || ')' || ' values (' || p.value_list || ');' as query
from
  insert_parts p
;

実行結果

create table EMP (EMPNO NUMBER(22,0) not null, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(22,0), HIREDATE DATE, SAL NUMBER(22,2), COMM NUMBER(22,2), DEPTNO NUMBER(22,0), primary key(EMPNO));
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (1, '0000000001', '000000001', 1, to_date('2012-08-26 16:07:22', 'yyyy-mm-dd HH24:mi:ss'), 1.01, 1.01, 1);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (2, '0000000002', '000000002', 2, to_date('2012-08-27 16:07:22', 'yyyy-mm-dd HH24:mi:ss'), 2.02, 2.02, 2);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (3, '0000000003', '000000003', 3, to_date('2012-08-28 16:07:22', 'yyyy-mm-dd HH24:mi:ss'), 3.03, 3.03, 3);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (4, '0000000004', '000000004', 4, to_date('2012-08-29 16:07:22', 'yyyy-mm-dd HH24:mi:ss'), 4.04, 4.04, 4);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) values (5, '0000000005', '000000005', 5, to_date('2012-08-30 16:07:22', 'yyyy-mm-dd HH24:mi:ss'), 5.05, 5.05, 5);