使用listagg函数拼接了动态建表语句:
SELECT listagg(n.create_tab, ' ') WITHIN GROUP(ORDER BY ROWNUM DESC) NAME,
i_id as id
FROM (Select 'create Table ' || Tab_Name || ' ( ' || Tab_Body || ');' ||
chr(10) As Create_Tab
From (Select Tab_Name,
tab_cn_name,
Listagg(Tab_Body) Within Group(Order By index_id) As Tab_Body
From (Select a.en_name As Tab_Name,
a.chn_name As tab_cn_name,
b.en_name || ' ' || b.data_type || Case
When b.index_id < A1.Max_Id Then
',' || chr(10)
Else
''
End As Tab_Body,
b.index_id,
'comment on Table ' || a.en_name || ' is ' ||
a.chn_name || ''';' as tab_comments,
'comment on Column ' || a.en_name || '.' ||
b.en_name || ' is ''' || b.chn_name ||
''';' as col_comments
From dmp.t_ds_model a --Table
Inner Join (Select count(*) As Max_Id, a.parent_id
From dmp.t_ds_model a
Where a.class_code = 'Column'
group by a.parent_id) A1
On a.id = a1.parent_id
Left Join dmp.t_ds_model b --Column
On a.id = b.parent_id
Where b.class_code = 'Column'
AND a.id = i_id) --更改
Group By Tab_Name, tab_cn_name)
union
select 'comment on Table ' || T.En_Name || ' is ''' ||
t.chn_name || '''; ' || chr(10)
from dmp.t_ds_model t
where t.class_code = 'Table'
AND t.id = i_id
union
select Listagg(col_comment) Within Group(Order By index_id) As com_body
from (select 'comment on Column ' || t1.en_name || '.' ||
t2.en_name || ' is ''' || t2.chn_name || ''';' ||
CHR(10) as col_comment,
t2.index_id
from dmp.t_ds_model t1 --Table
inner join dmp.t_ds_model t2 --Column
on t1.id = t2.parent_id
where t1.class_code = 'Table'
and t2.class_code = 'Column'
AND t1.id = i_id)) n;
之后使用execute immediate 运行建表语句:
select t.model_sql
into v_sql
from dmp.t_ds_model_sql t
where t.model_id = i_id;
execute immediate v_sql;