最近整理了gbase8s数据库中常见的元数据的查询，包括表、视图、序列、包、类型、触发器、plsql等等，仅供参考。
set environment sqlmode 'oracle';
drop package DBMS_METADATA;
create or replace package DBMS_METADATA is function GET_DDL(objtype varchar2,objname varchar2,users varchar2) return varchar2;function GET_INDEX_DDL(objname varchar2,users varchar2) return varchar2;function GET_TABLE_DDL(objname varchar2,users varchar2) return varchar2;function GET_TABLE_DDL_2_GBASE(objname varchar2,users varchar2) return varchar2;function GET_TABLE_DDL_2_ORACLE(objname varchar2,users varchar2) return varchar2;function GET_VIEW_DDL(objname varchar2,users varchar2) return varchar2;function GET_PROCEDURE_DDL(objname varchar2,users varchar2) return varchar2;function GET_FUNCTION_DDL(objname varchar2,users varchar2) return varchar2;function GET_PACKAGE_DDL(objname varchar2,users varchar2) return varchar2;function GET_PACKAGE_BODY_DDL(objname varchar2,users varchar2) return varchar2;function GET_TRIGGER_DDL(objname varchar2,users varchar2) return varchar2;function GET_TYPE_DDL(objname varchar2,users varchar2) return varchar2;function GET_TYPE_BODY_DDL(objname varchar2,users varchar2) return varchar2;function GET_SEQUENCE_DDL(objname varchar2,users varchar2) return varchar2;function GET_SYNONYM_DDL(objname varchar2,users varchar2) return varchar2;end ;
/CREATE OR REPLACE PACKAGE BODY DBMS_METADATA ISfunction GET_DDL(objtype varchar2,objname varchar2,users varchar2) return varchar2 is
ret varchar2;
begin if objtype='TABLE' or objtype='table' then
ret := GET_TABLE_DDL(objname ,users);
elsif objtype='INDEX' or objtype='index' then
ret:=GET_INDEX_DDL(objname ,users );
elsif objtype='VIEW' or objtype='view' then
ret := GET_VIEW_DDL(objname ,users);
elsif objtype='PROCEDURE' or objtype='procedure' then
ret := GET_PROCEDURE_DDL(objname ,users);
elsif objtype='FUNCTION' or objtype='function' then
ret := GET_FUNCTION_DDL(objname ,users);
elsif objtype='PACKAGE' or objtype='package' then
ret := GET_PACKAGE_DDL(objname ,users);
elsif objtype='PACKAGE_BODY' or objtype='package_body' then
ret := GET_PACKAGE_BODY_DDL(objname ,users);
elsif objtype='TRIGGER' or objtype='trigger' then
ret := GET_TRIGGER_DDL(objname ,users);
elsif objtype='TYPE' or objtype='type' then
ret := GET_TYPE_DDL(objname ,users);
elsif objtype='TYPE_BODY' or objtype='type_body' then
ret := GET_TYPE_BODY_DDL(objname ,users);
elsif objtype='SEQUENCE' or objtype='sequence' then
ret := GET_SEQUENCE_DDL(objname ,users);
elsif objtype='SYNONYM' or objtype='synonym' then
ret := GET_SYNONYM_DDL(objname ,users);
else
RAISE_APPLICATION_ERROR(-746, 'object type is not support in gbase database!');
end if;
return ret;
end GET_DDL;function GET_INDEX_DDL(objname varchar2,users varchar2) return varchar2 is
cnt int;
ret varchar2;
begin
select count(1) into cnt from sysindexes where idxname=objname and owner=users;if cnt=0 then
return null;
end if;select 'create '||(case when idxtype ='D' then null when idxtype ='U' then 'unique ' else null end)||'index '||idxname||' on '||tabname
||'('||colname||') using btree ;' as idxddl into ret
from (
select idxname,owner,tabname,idxtype, wm_concat(colname) as colname from (
select s1.idxname,s1.owner,s3.tabname,s2.colname,s1.idxtype,
CASE WHEN s1.part1 = s2.colno THEN 1WHEN s1.part2 = s2.colno THEN 2WHEN s1.part3 = s2.colno THEN 3WHEN s1.part4 = s2.colno THEN 4WHEN s1.part5 = s2.colno THEN 5WHEN s1.part6 = s2.colno THEN 6WHEN s1.part7 = s2.colno THEN 7WHEN s1.part8 = s2.colno THEN 8WHEN s1.part9 = s2.colno THEN 9WHEN s1.part10 = s2.colno THEN 10WHEN s1.part11 = s2.colno THEN 11WHEN s1.part12 = s2.colno THEN 12WHEN s1.part13 = s2.colno THEN 13WHEN s1.part14 = s2.colno THEN 14WHEN s1.part15 = s2.colno THEN 15WHEN s1.part16 = s2.colno THEN 16ELSE 99
END AS sx
from sysindexes s1
left join syscolumns s2
on s1.tabid=s2.tabid
join systables s3 on s1.tabid=s3.tabid
and (s1.part1 = s2.colno OR s1.part2 = s2.colno OR s1.part3 = s2.colno)
where s1.idxname=objname and s1.owner=users
order by sx
)
group by idxname,owner,tabname,idxtype
);return ret;
end GET_INDEX_DDL;function GET_TABLE_DDL(objname varchar2,users varchar2) return varchar2 is
tabflag int;
tid int;
ret varchar2;begin
select tabid,flags into tid,tabflag from systables where tabname=objname and tabtype='T' and owner=users;
if tid >0 and tabflag = 16384 then
ret := GET_TABLE_DDL_2_ORACLE(objname,users);
elsif tid >0 and tabflag = 0 then
ret := GET_TABLE_DDL_2_GBASE(objname,users);
else
return null;
end if;
return ret;
end GET_TABLE_DDL;function GET_TABLE_DDL_2_GBASE(objname varchar2,users varchar2) return varchar2 is
sql_stmt varchar2;
tableid int;
flag int;
begin select flags,tabid into flag,tableid from systables where tabname=objname and tabtype='T' and tabid >999;
if flag=0 then
sql_stmt :='CREATE TABLE '||objname||' ('||CHR(10);
elsif flag=16 then
sql_stmt :='CREATE RAW TABLE '||objname||' ('||CHR(10);
else
return null;
end if;for name in (
select colname||' '||typename||' '||defaultvalue||' '||vmvalue as vddl from (
select colname ,
case
when c.extended_id =0 and c.coltype=0 and colattr <> 256 then 'CHAR('||c.collength||')'
when c.extended_id =0 and c.coltype=1 and colattr <> 256 then 'SMALLINT'
when c.extended_id =0 and c.coltype=2 and colattr <> 256 then 'INTEGER'
when c.extended_id =0 and c.coltype=3 and colattr <> 256 then 'FLOAT'
when c.extended_id =0 and c.coltype=4 and colattr <> 256 then 'SMALLFLOAT'
when c.extended_id =0 and c.coltype=5 and colattr <> 256 then 'DECIMAL'
when c.extended_id =0 and c.coltype=6 and colattr <> 256 then 'SERIAL'
when c.extended_id =0 and c.coltype=7 and colattr <> 256 then 'DATE'
when c.extended_id =0 and c.coltype=8 and colattr <> 256 then 'MONEY'
when c.extended_id =0 and c.coltype=9 and colattr <> 256 then 'NULL'
when c.extended_id =0 and c.coltype=10 and colattr <> 256 then
'DATETIME '||(select trim(case
when bitand(c.collength, 240) / 16 =0 then 'YEAR'
when bitand(c.collength, 240) / 16 =2 then 'MONTH'
when bitand(c.collength, 240) / 16 =4 then 'DAY'
when bitand(c.collength, 240) / 16 =6 then 'HOUR'
when bitand(c.collength, 240) / 16 =8 then 'MINUTE'
when bitand(c.collength, 240) / 16 =10 then 'SECOND'
when bitand(c.collength, 240) / 16 =11 then 'FRACTION(1)'
when bitand(c.collength, 240) / 16 =12 then 'FRACTION(2)'
when bitand(c.collength, 240) / 16 =13 then 'FRACTION(3)'
when bitand(c.collength, 240) / 16 =14 then 'FRACTION(4)'
when bitand(c.collength, 240) / 16 =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)||' TO '||(
select trim(case
when bitand(collength, 15) =0 then 'YEAR'
when bitand(collength, 15) =2 then 'MONTH'
when bitand(collength, 15) =4 then 'DAY'
when bitand(collength, 15) =6 then 'HOUR'
when bitand(collength, 15) =8 then 'MINUTE'
when bitand(collength, 15) =10 then 'SECOND'
when bitand(collength, 15) =11 then 'FRACTION(1)'
when bitand(collength, 15) =12 then 'FRACTION(2)'
when bitand(collength, 15) =13 then 'FRACTION(3)'
when bitand(collength, 15) =14 then 'FRACTION(4)'
when bitand(collength, 15) =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)when c.extended_id =0 and c.coltype=11 and colattr <> 256 then 'BYTE'
when c.extended_id =0 and c.coltype=12 and colattr <> 256 then 'TEXT'
when c.extended_id =0 and c.coltype=13 and colattr <> 256 then 'VARCHAR('||c.collength||')'
when c.extended_id =0 and c.coltype=14 and colattr <> 256 then
'INTERVAL '||(select trim(case
when bitand(c.collength, 240) / 16 =0 then 'YEAR'
when bitand(c.collength, 240) / 16 =2 then 'MONTH'
when bitand(c.collength, 240) / 16 =4 then 'DAY'
when bitand(c.collength, 240) / 16 =6 then 'HOUR'
when bitand(c.collength, 240) / 16 =8 then 'MINUTE'
when bitand(c.collength, 240) / 16 =10 then 'SECOND'
when bitand(c.collength, 240) / 16 =11 then 'FRACTION(1)'
when bitand(c.collength, 240) / 16 =12 then 'FRACTION(2)'
when bitand(c.collength, 240) / 16 =13 then 'FRACTION(3)'
when bitand(c.collength, 240) / 16 =14 then 'FRACTION(4)'
when bitand(c.collength, 240) / 16 =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)||' TO '||(
select trim(case
when bitand(collength, 15) =0 then 'YEAR'
when bitand(collength, 15) =2 then 'MONTH'
when bitand(collength, 15) =4 then 'DAY'
when bitand(collength, 15) =6 then 'HOUR'
when bitand(collength, 15) =8 then 'MINUTE'
when bitand(collength, 15) =10 then 'SECOND'
when bitand(collength, 15) =11 then 'FRACTION(1)'
when bitand(collength, 15) =12 then 'FRACTION(2)'
when bitand(collength, 15) =13 then 'FRACTION(3)'
when bitand(collength, 15) =14 then 'FRACTION(4)'
when bitand(collength, 15) =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)
when c.extended_id =0 and c.coltype=15 and colattr <> 256 then 'NCHAR('||c.collength||')'
when c.extended_id =0 and c.coltype=16 and colattr <> 256 then 'NVARCHAR('||c.collength||')'
when c.extended_id =0 and c.coltype=17 and colattr <> 256 then 'INT8'
when c.extended_id =0 and c.coltype=18 and colattr <> 256 then 'SERIAL8'
when c.extended_id =0 and c.coltype=19 and colattr <> 256 then 'SET'
when c.extended_id =0 and c.coltype=20 and colattr <> 256 then 'MULTISET'
when c.extended_id =0 and c.coltype=21 and colattr <> 256 then 'LIST'
when c.extended_id =0 and c.coltype=52 and colattr <> 256 then 'BIGINT'
when c.extended_id =0 and c.coltype=53 and colattr <> 256 then 'BIGSERIAL'
when c.extended_id =0 and c.coltype=63 and colattr <> 256 then 'VARCHAR2('||c.collength||')'
when c.extended_id =0 and c.coltype=64 and colattr <> 256 then 'NVARCHAR2('||c.collength||')'
when c.extended_id =0 and c.coltype=256 and colattr <> 256 then 'CHAR('||c.collength||') NOT NULL'
when c.extended_id =0 and c.coltype=257 and colattr <> 256 then 'SMALLINT'
when c.extended_id =0 and c.coltype=258 and colattr <> 256 then 'INTEGER'
when c.extended_id =0 and c.coltype=259 and colattr <> 256 then 'FLOAT'
when c.extended_id =0 and c.coltype=260 and colattr <> 256 then 'SMALLFLOAT'
when c.extended_id =0 and c.coltype=261 and colattr <> 256 then 'DECIMAL'
when c.extended_id =0 and c.coltype=262 and colattr <> 256 then 'SERIAL'
when c.extended_id =0 and c.coltype=263 and colattr <> 256 then 'DATE'
when c.extended_id =0 and c.coltype=264 and colattr <> 256 then 'MONEY'
when c.extended_id =0 and c.coltype=265 and colattr <> 256 then 'NULL'
when c.extended_id =0 and c.coltype=266 and colattr <> 256 then
'DATETIME '||(select trim(case
when bitand(c.collength, 240) / 16 =0 then 'YEAR'
when bitand(c.collength, 240) / 16 =2 then 'MONTH'
when bitand(c.collength, 240) / 16 =4 then 'DAY'
when bitand(c.collength, 240) / 16 =6 then 'HOUR'
when bitand(c.collength, 240) / 16 =8 then 'MINUTE'
when bitand(c.collength, 240) / 16 =10 then 'SECOND'
when bitand(c.collength, 240) / 16 =11 then 'FRACTION(1)'
when bitand(c.collength, 240) / 16 =12 then 'FRACTION(2)'
when bitand(c.collength, 240) / 16 =13 then 'FRACTION(3)'
when bitand(c.collength, 240) / 16 =14 then 'FRACTION(4)'
when bitand(c.collength, 240) / 16 =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)||' TO '||(
select trim(case
when bitand(collength, 15) =0 then 'YEAR'
when bitand(collength, 15) =2 then 'MONTH'
when bitand(collength, 15) =4 then 'DAY'
when bitand(collength, 15) =6 then 'HOUR'
when bitand(collength, 15) =8 then 'MINUTE'
when bitand(collength, 15) =10 then 'SECOND'
when bitand(collength, 15) =11 then 'FRACTION(1)'
when bitand(collength, 15) =12 then 'FRACTION(2)'
when bitand(collength, 15) =13 then 'FRACTION(3)'
when bitand(collength, 15) =14 then 'FRACTION(4)'
when bitand(collength, 15) =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)
when c.extended_id =0 and c.coltype=267 and colattr <> 256 then 'BYTE'
when c.extended_id =0 and c.coltype=268 and colattr <> 256 then 'TEXT'
when c.extended_id =0 and c.coltype=269 and colattr <> 256 then 'VARCHAR('||c.collength||') NOT NULL'
when c.extended_id =0 and c.coltype=270 and colattr <> 256 then
'INTERVAL '||(select trim(case
when bitand(c.collength, 240) / 16 =0 then 'YEAR'
when bitand(c.collength, 240) / 16 =2 then 'MONTH'
when bitand(c.collength, 240) / 16 =4 then 'DAY'
when bitand(c.collength, 240) / 16 =6 then 'HOUR'
when bitand(c.collength, 240) / 16 =8 then 'MINUTE'
when bitand(c.collength, 240) / 16 =10 then 'SECOND'
when bitand(c.collength, 240) / 16 =11 then 'FRACTION(1)'
when bitand(c.collength, 240) / 16 =12 then 'FRACTION(2)'
when bitand(c.collength, 240) / 16 =13 then 'FRACTION(3)'
when bitand(c.collength, 240) / 16 =14 then 'FRACTION(4)'
when bitand(c.collength, 240) / 16 =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)||' TO '||(
select trim(case
when bitand(collength, 15) =0 then 'YEAR'
when bitand(collength, 15) =2 then 'MONTH'
when bitand(collength, 15) =4 then 'DAY'
when bitand(collength, 15) =6 then 'HOUR'
when bitand(collength, 15) =8 then 'MINUTE'
when bitand(collength, 15) =10 then 'SECOND'
when bitand(collength, 15) =11 then 'FRACTION(1)'
when bitand(collength, 15) =12 then 'FRACTION(2)'
when bitand(collength, 15) =13 then 'FRACTION(3)'
when bitand(collength, 15) =14 then 'FRACTION(4)'
when bitand(collength, 15) =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)
when c.extended_id =0 and c.coltype=271 and colattr <> 256 then 'NCHAR('||c.collength||') NOT NULL'
when c.extended_id =0 and c.coltype=272 and colattr <> 256 then 'NVARCHAR('||c.collength||') NOT NULL'
when c.extended_id =0 and c.coltype=273 and colattr <> 256 then 'INT8'
when c.extended_id =0 and c.coltype=275 and colattr <> 256 then 'SET'
when c.extended_id =0 and c.coltype=276 and colattr <> 256 then 'MULTISET'
when c.extended_id =0 and c.coltype=277 and colattr <> 256 then 'LIST'
when c.extended_id =0 and c.coltype=308 and colattr <> 256 then 'BIGINT'
when c.extended_id =0 and c.coltype=319 and colattr <> 256 then 'VARCHAR2('||c.collength||') NOT NULL'
when c.extended_id =0 and c.coltype=320 and colattr <> 256 then 'NVARCHAR2('||c.collength||') NOT NULL'
when colattr = 256 then 'generated always as '
when c.extended_id <> 0 then
(select name from sysxtdtypes sx where sx.extended_id=c.extended_id)
end typename,
case when type is null then null
when type ='E' then 'default '||default2
when type ='L' and c.coltype in (0,13,15,16,63,64,256,269,271,319,320) then 'default '||"'"||read_defaultstr (c.tabid,c.colno,c.coltype,c.collength,c.extended_id)::varchar(255)||"'"
when type ='C' then 'default sysdate '||(select trim(case
when bitand(c.collength, 240) / 16 =0 then 'YEAR'
when bitand(c.collength, 240) / 16 =2 then 'MONTH'
when bitand(c.collength, 240) / 16 =4 then 'DAY'
when bitand(c.collength, 240) / 16 =6 then 'HOUR'
when bitand(c.collength, 240) / 16 =8 then 'MINUTE'
when bitand(c.collength, 240) / 16 =10 then 'SECOND'
when bitand(c.collength, 240) / 16 =11 then 'FRACTION(1)'
when bitand(c.collength, 240) / 16 =12 then 'FRACTION(2)'
when bitand(c.collength, 240) / 16 =13 then 'FRACTION(3)'
when bitand(c.collength, 240) / 16 =14 then 'FRACTION(4)'
when bitand(c.collength, 240) / 16 =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)||' TO '||(
select trim(case
when bitand(collength, 15) =0 then 'YEAR'
when bitand(collength, 15) =2 then 'MONTH'
when bitand(collength, 15) =4 then 'DAY'
when bitand(collength, 15) =6 then 'HOUR'
when bitand(collength, 15) =8 then 'MINUTE'
when bitand(collength, 15) =10 then 'SECOND'
when bitand(collength, 15) =11 then 'FRACTION(1)'
when bitand(collength, 15) =12 then 'FRACTION(2)'
when bitand(collength, 15) =13 then 'FRACTION(3)'
when bitand(collength, 15) =14 then 'FRACTION(4)'
when bitand(collength, 15) =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)
when type ='L' and c.coltype in (14,270) then ' default interval('||read_defaultstr (c.tabid,c.colno,c.coltype,c.collength,c.extended_id)::varchar(255)||') '||(select trim(case
when bitand(c.collength, 240) / 16 =0 then 'YEAR'
when bitand(c.collength, 240) / 16 =2 then 'MONTH'
when bitand(c.collength, 240) / 16 =4 then 'DAY'
when bitand(c.collength, 240) / 16 =6 then 'HOUR'
when bitand(c.collength, 240) / 16 =8 then 'MINUTE'
when bitand(c.collength, 240) / 16 =10 then 'SECOND'
when bitand(c.collength, 240) / 16 =11 then 'FRACTION(1)'
when bitand(c.collength, 240) / 16 =12 then 'FRACTION(2)'
when bitand(c.collength, 240) / 16 =13 then 'FRACTION(3)'
when bitand(c.collength, 240) / 16 =14 then 'FRACTION(4)'
when bitand(c.collength, 240) / 16 =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)||' TO '||(
select trim(case
when bitand(collength, 15) =0 then 'YEAR'
when bitand(collength, 15) =2 then 'MONTH'
when bitand(collength, 15) =4 then 'DAY'
when bitand(collength, 15) =6 then 'HOUR'
when bitand(collength, 15) =8 then 'MINUTE'
when bitand(collength, 15) =10 then 'SECOND'
when bitand(collength, 15) =11 then 'FRACTION(1)'
when bitand(collength, 15) =12 then 'FRACTION(2)'
when bitand(collength, 15) =13 then 'FRACTION(3)'
when bitand(collength, 15) =14 then 'FRACTION(4)'
when bitand(collength, 15) =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)
else 'default '||read_defaultstr (c.tabid,c.colno,c.coltype,c.collength,c.extended_id)::varchar(255)end as defaultvalue ,
case when colattr=256 then '('||default2 ||') virtual' end vmvalue
from (
select s1.tabid,s1.colname,s1.colno,s1.coltype,s1.extended_id,s1.collength,s1.colattr,s2.type,s2.default default1,replace(wm_concat(s3.default),',','') default2 from syscolumns s1
left join (select tabid,default,colno,type from sysdefaults ) s2 on
s1.tabid=s2.tabid and s1.colno=s2.colno
left join (select tabid,default,colno from sysdefaultsexpr where type='T') s3 on
s1.tabid=s3.tabid and s1.colno=s3.colno
where s1.tabid=tableid
group by s1.tabid,s1.colname,s1.colno,s1.coltype,s1.extended_id,s1.collength,s1.colattr,s2.type,s2.default
order by s1.colno
)c
)
)
loop sql_stmt :=sql_stmt||name.vddl||','||CHR(10);
end loop;--查询check约束for checkname in(
select 'check ' ||checktext||'constraint '||constrname as checkexpr from (
select s1.constrname ,s2.checktext from sysconstraints s1,syschecks s2 where s1.constrid=s2.constrid
and s2.type='T' and s1.constrtype='C' and s1.tabid=tableid
)
)
loop
sql_stmt :=sql_stmt|| checkname.checkexpr||','||CHR(10);
end loop;--查询唯一约束for unique_constrsaints in(
select constrname,constrained_columns from (select constrname,wm_concat(colname) AS constrained_columns from (
SELECT c.constrname AS constrname,
CASE WHEN si.part1 = sc.colno THEN 1WHEN si.part2 = sc.colno THEN 2WHEN si.part3 = sc.colno THEN 3WHEN si.part4 = sc.colno THEN 4WHEN si.part5 = sc.colno THEN 5WHEN si.part6 = sc.colno THEN 6WHEN si.part7 = sc.colno THEN 7WHEN si.part8 = sc.colno THEN 8WHEN si.part9 = sc.colno THEN 9WHEN si.part10 = sc.colno THEN 10WHEN si.part11 = sc.colno THEN 11WHEN si.part12 = sc.colno THEN 12WHEN si.part13 = sc.colno THEN 13WHEN si.part14 = sc.colno THEN 14WHEN si.part15 = sc.colno THEN 15WHEN si.part16 = sc.colno THEN 16ELSE 99
END AS sx,
sc.colname
FROM sysconstraints cJOIN sysindexes si ON c.idxname = si.idxnameJOIN syscolumns sc ON c.tabid = sc.tabid
WHERE c.tabid = tableidAND c.constrtype = 'U'AND (si.part1 = sc.colno OR si.part2 = sc.colno OR si.part3 = sc.colnoOR si.part4 = sc.colno OR si.part5 = sc.colnoOR si.part6 = sc.colno OR si.part7 = sc.colnoOR si.part8 = sc.colno OR si.part9 = sc.colnoOR si.part10 = sc.colno OR si.part11 = sc.colnoOR si.part12 = sc.colno OR si.part13 = sc.colnoOR si.part14 = sc.colno OR si.part15 = sc.colnoOR si.part16 = sc.colno)order by sx
)
GROUP BY constrname
ORDER BY constrname)
)loop
--sql_stmt :=sql_stmt|| 'constraint '||unique_constrsaints.constrname || 'unique('||unique_constrsaints.constrained_columns||'),'||CHR(10) ;
sql_stmt :=sql_stmt||'unique('||unique_constrsaints.constrained_columns||')'||'constraint '||unique_constrsaints.constrname ||','||CHR(10) ;
end loop;--查询主键约束for primary_constrsaints in(
select constrname,constrained_columns from (
select constrname,wm_concat(colname) AS constrained_columns from (
SELECT c.constrname AS constrname,
CASE WHEN si.part1 = sc.colno THEN 1WHEN si.part2 = sc.colno THEN 2WHEN si.part3 = sc.colno THEN 3WHEN si.part4 = sc.colno THEN 4WHEN si.part5 = sc.colno THEN 5WHEN si.part6 = sc.colno THEN 6WHEN si.part7 = sc.colno THEN 7WHEN si.part8 = sc.colno THEN 8WHEN si.part9 = sc.colno THEN 9WHEN si.part10 = sc.colno THEN 10WHEN si.part11 = sc.colno THEN 11WHEN si.part12 = sc.colno THEN 12WHEN si.part13 = sc.colno THEN 13WHEN si.part14 = sc.colno THEN 14WHEN si.part15 = sc.colno THEN 15WHEN si.part16 = sc.colno THEN 16ELSE 99
END AS sx,
sc.colname
FROM sysconstraints cJOIN sysindexes si ON c.idxname = si.idxnameJOIN syscolumns sc ON c.tabid = sc.tabid
WHERE c.tabid = tableidAND c.constrtype = 'P'AND (si.part1 = sc.colno OR si.part2 = sc.colno OR si.part3 = sc.colnoOR si.part4 = sc.colno OR si.part5 = sc.colnoOR si.part6 = sc.colno OR si.part7 = sc.colnoOR si.part8 = sc.colno OR si.part9 = sc.colnoOR si.part10 = sc.colno OR si.part11 = sc.colnoOR si.part12 = sc.colno OR si.part13 = sc.colnoOR si.part14 = sc.colno OR si.part15 = sc.colnoOR si.part16 = sc.colno )order by sx
)
GROUP BY constrname
ORDER BY constrname
)
)loop
sql_stmt :=sql_stmt||' primary key('||primary_constrsaints.constrained_columns||') '||'constraint '||primary_constrsaints.constrname ||','||CHR(10) ;
end loop;--查询外键约束for foreign_constrsaints in(
SELECT c.constrname AS foreign_key_name,(SELECT wm_concat(DISTINCT colname) FROM (SELECT sc.colname,CASE WHEN si.part1 = sc.colno THEN 1WHEN si.part2 = sc.colno THEN 2WHEN si.part3 = sc.colno THEN 3WHEN si.part4 = sc.colno THEN 4WHEN si.part5 = sc.colno THEN 5WHEN si.part6 = sc.colno THEN 6WHEN si.part7 = sc.colno THEN 7WHEN si.part8 = sc.colno THEN 8WHEN si.part9 = sc.colno THEN 9WHEN si.part10 = sc.colno THEN 10WHEN si.part11 = sc.colno THEN 11WHEN si.part12 = sc.colno THEN 12WHEN si.part13 = sc.colno THEN 13WHEN si.part14 = sc.colno THEN 14WHEN si.part15 = sc.colno THEN 15WHEN si.part16 = sc.colno THEN 16ELSE 99END AS col_orderFROM sysindexes siJOIN syscolumns sc ON c.tabid = sc.tabidWHERE si.idxname = c.idxnameAND (si.part1 = sc.colno OR si.part2 = sc.colno OR si.part3 = sc.colnoOR si.part4 = sc.colno OR si.part5 = sc.colno OR si.part6 = sc.colnoOR si.part7 = sc.colno OR si.part8 = sc.colno OR si.part9 = sc.colnoOR si.part10 = sc.colno OR si.part11 = sc.colno OR si.part12 = sc.colnoOR si.part13 = sc.colno OR si.part14 = sc.colno OR si.part15 = sc.colnoOR si.part16 = sc.colno)ORDER BY col_order) ordered_cols) AS child_columns,pc.constrname AS parent_constraint_name,(SELECT wm_concat(DISTINCT colname)FROM (SELECT sc.colname,CASE WHEN si.part1 = sc.colno THEN 1WHEN si.part2 = sc.colno THEN 2WHEN si.part3 = sc.colno THEN 3WHEN si.part4 = sc.colno THEN 4WHEN si.part5 = sc.colno THEN 5WHEN si.part6 = sc.colno THEN 6WHEN si.part7 = sc.colno THEN 7WHEN si.part8 = sc.colno THEN 8WHEN si.part9 = sc.colno THEN 9WHEN si.part10 = sc.colno THEN 10WHEN si.part11 = sc.colno THEN 11WHEN si.part12 = sc.colno THEN 12WHEN si.part13 = sc.colno THEN 13WHEN si.part14 = sc.colno THEN 14WHEN si.part15 = sc.colno THEN 15WHEN si.part16 = sc.colno THEN 16ELSE 99END AS col_orderFROM sysindexes siJOIN syscolumns sc ON pc.tabid = sc.tabidWHERE si.idxname = pc.idxnameAND (si.part1 = sc.colno OR si.part2 = sc.colno OR si.part3 = sc.colnoOR si.part4 = sc.colno OR si.part5 = sc.colno OR si.part6 = sc.colnoOR si.part7 = sc.colno OR si.part8 = sc.colno OR si.part9 = sc.colnoOR si.part10 = sc.colno OR si.part11 = sc.colno OR si.part12 = sc.colnoOR si.part13 = sc.colno OR si.part14 = sc.colno OR si.part15 = sc.colnoOR si.part16 = sc.colno)ORDER BY col_order) ordered_cols) AS parent_columns,pt.tabname AS parent_table
FROM sysconstraints cJOIN sysreferences sr ON c.constrid = sr.constridJOIN sysconstraints pc ON pc.constrid = sr.primaryJOIN systables pt ON pc.tabid = pt.tabid
WHERE c.tabid = tableidAND c.constrtype = 'R'
ORDER BY c.constrname
)loop
sql_stmt :=sql_stmt||' foreign key('||foreign_constrsaints.child_columns||') references '||foreign_constrsaints.parent_table||'('||foreign_constrsaints.parent_columns||')'||'constraint '||foreign_constrsaints.foreign_key_name || ','||CHR(10) ;
end loop;sql_stmt := sql_stmt||');';
--去除最后的逗号
sql_stmt := REGEXP_REPLACE(sql_stmt, ',\s*\)\s*;\s*$', ');');
--sql_stmt := REGEXP_REPLACE(sql_stmt, '\),\s*\);$', '));');--dbms_output.put_line(sql_stmt);
return sql_stmt;
end GET_TABLE_DDL_2_GBASE;function GET_TABLE_DDL_2_ORACLE(objname varchar2,users varchar2) return varchar2 is
sql_stmt varchar2;
tableid int;
flag int;
begin
select flags,tabid into flag,tableid from systables where tabname=objname and tabtype='T' and tabid >999;
if flag=16384 then
sql_stmt :='CREATE TABLE '||objname||' ('||CHR(10);
elsif flag=16400 then
sql_stmt :='CREATE RAW TABLE '||objname||' ('||CHR(10);
end if;for name in (
select colname||' '||typename||' '||defaultvalue||' '||vmvalue as vddl from (
select colname ,
case
when c.extended_id =0 and c.coltype=0 and colattr <> 256 then 'CHAR('||c.collength||')'
when c.extended_id =0 and c.coltype=1 and colattr <> 256 then 'SMALLINT'
when c.extended_id =0 and c.coltype=2 and colattr <> 256 then 'INTEGER'
when c.extended_id =0 and c.coltype=3 and colattr <> 256 then 'FLOAT'
when c.extended_id =0 and c.coltype=4 and colattr <> 256 then 'SMALLFLOAT'
when c.extended_id =0 and c.coltype=5 and colattr <> 256 then 'DECIMAL'
when c.extended_id =0 and c.coltype=6 and colattr <> 256 then 'SERIAL'
when c.extended_id =0 and c.coltype=7 and colattr <> 256 then 'DATE'
when c.extended_id =0 and c.coltype=8 and colattr <> 256 then 'MONEY'
when c.extended_id =0 and c.coltype=9 and colattr <> 256 then 'NULL'
when c.extended_id =0 and c.coltype=10 and colattr <> 256 then
'DATETIME '||(select trim(case
when bitand(c.collength, 240) / 16 =0 then 'YEAR'
when bitand(c.collength, 240) / 16 =2 then 'MONTH'
when bitand(c.collength, 240) / 16 =4 then 'DAY'
when bitand(c.collength, 240) / 16 =6 then 'HOUR'
when bitand(c.collength, 240) / 16 =8 then 'MINUTE'
when bitand(c.collength, 240) / 16 =10 then 'SECOND'
when bitand(c.collength, 240) / 16 =11 then 'FRACTION(1)'
when bitand(c.collength, 240) / 16 =12 then 'FRACTION(2)'
when bitand(c.collength, 240) / 16 =13 then 'FRACTION(3)'
when bitand(c.collength, 240) / 16 =14 then 'FRACTION(4)'
when bitand(c.collength, 240) / 16 =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)||' TO '||(
select trim(case
when bitand(collength, 15) =0 then 'YEAR'
when bitand(collength, 15) =2 then 'MONTH'
when bitand(collength, 15) =4 then 'DAY'
when bitand(collength, 15) =6 then 'HOUR'
when bitand(collength, 15) =8 then 'MINUTE'
when bitand(collength, 15) =10 then 'SECOND'
when bitand(collength, 15) =11 then 'FRACTION(1)'
when bitand(collength, 15) =12 then 'FRACTION(2)'
when bitand(collength, 15) =13 then 'FRACTION(3)'
when bitand(collength, 15) =14 then 'FRACTION(4)'
when bitand(collength, 15) =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)when c.extended_id =0 and c.coltype=11 and colattr <> 256 then 'BYTE'
when c.extended_id =0 and c.coltype=12 and colattr <> 256 then 'TEXT'
when c.extended_id =0 and c.coltype=13 and colattr <> 256 then 'VARCHAR('||c.collength||')'
when c.extended_id =0 and c.coltype=14 and colattr <> 256 then
'INTERVAL '||(select trim(case
when bitand(c.collength, 240) / 16 =0 then 'YEAR'
when bitand(c.collength, 240) / 16 =2 then 'MONTH'
when bitand(c.collength, 240) / 16 =4 then 'DAY'
when bitand(c.collength, 240) / 16 =6 then 'HOUR'
when bitand(c.collength, 240) / 16 =8 then 'MINUTE'
when bitand(c.collength, 240) / 16 =10 then 'SECOND'
when bitand(c.collength, 240) / 16 =11 then 'FRACTION(1)'
when bitand(c.collength, 240) / 16 =12 then 'FRACTION(2)'
when bitand(c.collength, 240) / 16 =13 then 'FRACTION(3)'
when bitand(c.collength, 240) / 16 =14 then 'FRACTION(4)'
when bitand(c.collength, 240) / 16 =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)||' TO '||(
select trim(case
when bitand(collength, 15) =0 then 'YEAR'
when bitand(collength, 15) =2 then 'MONTH'
when bitand(collength, 15) =4 then 'DAY'
when bitand(collength, 15) =6 then 'HOUR'
when bitand(collength, 15) =8 then 'MINUTE'
when bitand(collength, 15) =10 then 'SECOND'
when bitand(collength, 15) =11 then 'FRACTION(1)'
when bitand(collength, 15) =12 then 'FRACTION(2)'
when bitand(collength, 15) =13 then 'FRACTION(3)'
when bitand(collength, 15) =14 then 'FRACTION(4)'
when bitand(collength, 15) =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)
when c.extended_id =0 and c.coltype=15 and colattr <> 256 then 'NCHAR('||c.collength||')'
when c.extended_id =0 and c.coltype=16 and colattr <> 256 then 'NVARCHAR('||c.collength||')'
when c.extended_id =0 and c.coltype=17 and colattr <> 256 then 'INT8'
when c.extended_id =0 and c.coltype=18 and colattr <> 256 then 'SERIAL8'
when c.extended_id =0 and c.coltype=19 and colattr <> 256 then 'SET'
when c.extended_id =0 and c.coltype=20 and colattr <> 256 then 'MULTISET'
when c.extended_id =0 and c.coltype=21 and colattr <> 256 then 'LIST'
when c.extended_id =0 and c.coltype=52 and colattr <> 256 then 'BIGINT'
when c.extended_id =0 and c.coltype=53 and colattr <> 256 then 'BIGSERIAL'
when c.extended_id =0 and c.coltype=63 and colattr <> 256 then 'VARCHAR2('||c.collength||')'
when c.extended_id =0 and c.coltype=64 and colattr <> 256 then 'NVARCHAR2('||c.collength||')'
when c.extended_id =0 and c.coltype=256 and colattr <> 256 then 'CHAR('||c.collength||') NOT NULL'
when c.extended_id =0 and c.coltype=257 and colattr <> 256 then 'SMALLINT'
when c.extended_id =0 and c.coltype=258 and colattr <> 256 then 'INTEGER'
when c.extended_id =0 and c.coltype=259 and colattr <> 256 then 'FLOAT'
when c.extended_id =0 and c.coltype=260 and colattr <> 256 then 'SMALLFLOAT'
when c.extended_id =0 and c.coltype=261 and colattr <> 256 then 'DECIMAL'
when c.extended_id =0 and c.coltype=262 and colattr <> 256 then 'SERIAL'
when c.extended_id =0 and c.coltype=263 and colattr <> 256 then 'DATE'
when c.extended_id =0 and c.coltype=264 and colattr <> 256 then 'MONEY'
when c.extended_id =0 and c.coltype=265 and colattr <> 256 then 'NULL'
when c.extended_id =0 and c.coltype=266 and colattr <> 256 then
'DATETIME '||(select trim(case
when bitand(c.collength, 240) / 16 =0 then 'YEAR'
when bitand(c.collength, 240) / 16 =2 then 'MONTH'
when bitand(c.collength, 240) / 16 =4 then 'DAY'
when bitand(c.collength, 240) / 16 =6 then 'HOUR'
when bitand(c.collength, 240) / 16 =8 then 'MINUTE'
when bitand(c.collength, 240) / 16 =10 then 'SECOND'
when bitand(c.collength, 240) / 16 =11 then 'FRACTION(1)'
when bitand(c.collength, 240) / 16 =12 then 'FRACTION(2)'
when bitand(c.collength, 240) / 16 =13 then 'FRACTION(3)'
when bitand(c.collength, 240) / 16 =14 then 'FRACTION(4)'
when bitand(c.collength, 240) / 16 =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)||' TO '||(
select trim(case
when bitand(collength, 15) =0 then 'YEAR'
when bitand(collength, 15) =2 then 'MONTH'
when bitand(collength, 15) =4 then 'DAY'
when bitand(collength, 15) =6 then 'HOUR'
when bitand(collength, 15) =8 then 'MINUTE'
when bitand(collength, 15) =10 then 'SECOND'
when bitand(collength, 15) =11 then 'FRACTION(1)'
when bitand(collength, 15) =12 then 'FRACTION(2)'
when bitand(collength, 15) =13 then 'FRACTION(3)'
when bitand(collength, 15) =14 then 'FRACTION(4)'
when bitand(collength, 15) =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)
when c.extended_id =0 and c.coltype=267 and colattr <> 256 then 'BYTE'
when c.extended_id =0 and c.coltype=268 and colattr <> 256 then 'TEXT'
when c.extended_id =0 and c.coltype=269 and colattr <> 256 then 'VARCHAR('||c.collength||') NOT NULL'
when c.extended_id =0 and c.coltype=270 and colattr <> 256 then
'INTERVAL '||(select trim(case
when bitand(c.collength, 240) / 16 =0 then 'YEAR'
when bitand(c.collength, 240) / 16 =2 then 'MONTH'
when bitand(c.collength, 240) / 16 =4 then 'DAY'
when bitand(c.collength, 240) / 16 =6 then 'HOUR'
when bitand(c.collength, 240) / 16 =8 then 'MINUTE'
when bitand(c.collength, 240) / 16 =10 then 'SECOND'
when bitand(c.collength, 240) / 16 =11 then 'FRACTION(1)'
when bitand(c.collength, 240) / 16 =12 then 'FRACTION(2)'
when bitand(c.collength, 240) / 16 =13 then 'FRACTION(3)'
when bitand(c.collength, 240) / 16 =14 then 'FRACTION(4)'
when bitand(c.collength, 240) / 16 =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)||' TO '||(
select trim(case
when bitand(collength, 15) =0 then 'YEAR'
when bitand(collength, 15) =2 then 'MONTH'
when bitand(collength, 15) =4 then 'DAY'
when bitand(collength, 15) =6 then 'HOUR'
when bitand(collength, 15) =8 then 'MINUTE'
when bitand(collength, 15) =10 then 'SECOND'
when bitand(collength, 15) =11 then 'FRACTION(1)'
when bitand(collength, 15) =12 then 'FRACTION(2)'
when bitand(collength, 15) =13 then 'FRACTION(3)'
when bitand(collength, 15) =14 then 'FRACTION(4)'
when bitand(collength, 15) =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)
when c.extended_id =0 and c.coltype=271 and colattr <> 256 then 'NCHAR('||c.collength||') NOT NULL'
when c.extended_id =0 and c.coltype=272 and colattr <> 256 then 'NVARCHAR('||c.collength||') NOT NULL'
when c.extended_id =0 and c.coltype=273 and colattr <> 256 then 'INT8'
when c.extended_id =0 and c.coltype=275 and colattr <> 256 then 'SET'
when c.extended_id =0 and c.coltype=276 and colattr <> 256 then 'MULTISET'
when c.extended_id =0 and c.coltype=277 and colattr <> 256 then 'LIST'
when c.extended_id =0 and c.coltype=308 and colattr <> 256 then 'BIGINT'
when c.extended_id =0 and c.coltype=319 and colattr <> 256 then 'VARCHAR2('||c.collength||') NOT NULL'
when c.extended_id =0 and c.coltype=320 and colattr <> 256 then 'NVARCHAR2('||c.collength||') NOT NULL'
when colattr = 256 then 'generated always as '
when c.extended_id <> 0 then
(select name from sysxtdtypes sx where sx.extended_id=c.extended_id)
end typename,
case when type is null then null
when type ='E' then 'default '||default2
when type ='L' and c.coltype in (0,13,15,16,63,64,256,269,271,319,320) then 'default '||"'"||read_defaultstr (c.tabid,c.colno,c.coltype,c.collength,c.extended_id)::varchar(255)||"'"
when type ='C' then 'default sysdate '||(select trim(case
when bitand(c.collength, 240) / 16 =0 then 'YEAR'
when bitand(c.collength, 240) / 16 =2 then 'MONTH'
when bitand(c.collength, 240) / 16 =4 then 'DAY'
when bitand(c.collength, 240) / 16 =6 then 'HOUR'
when bitand(c.collength, 240) / 16 =8 then 'MINUTE'
when bitand(c.collength, 240) / 16 =10 then 'SECOND'
when bitand(c.collength, 240) / 16 =11 then 'FRACTION(1)'
when bitand(c.collength, 240) / 16 =12 then 'FRACTION(2)'
when bitand(c.collength, 240) / 16 =13 then 'FRACTION(3)'
when bitand(c.collength, 240) / 16 =14 then 'FRACTION(4)'
when bitand(c.collength, 240) / 16 =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)||' TO '||(
select trim(case
when bitand(collength, 15) =0 then 'YEAR'
when bitand(collength, 15) =2 then 'MONTH'
when bitand(collength, 15) =4 then 'DAY'
when bitand(collength, 15) =6 then 'HOUR'
when bitand(collength, 15) =8 then 'MINUTE'
when bitand(collength, 15) =10 then 'SECOND'
when bitand(collength, 15) =11 then 'FRACTION(1)'
when bitand(collength, 15) =12 then 'FRACTION(2)'
when bitand(collength, 15) =13 then 'FRACTION(3)'
when bitand(collength, 15) =14 then 'FRACTION(4)'
when bitand(collength, 15) =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)
when type ='L' and c.coltype in (14,270) then ' default interval('||read_defaultstr (c.tabid,c.colno,c.coltype,c.collength,c.extended_id)::varchar(255)||') '||(select trim(case
when bitand(c.collength, 240) / 16 =0 then 'YEAR'
when bitand(c.collength, 240) / 16 =2 then 'MONTH'
when bitand(c.collength, 240) / 16 =4 then 'DAY'
when bitand(c.collength, 240) / 16 =6 then 'HOUR'
when bitand(c.collength, 240) / 16 =8 then 'MINUTE'
when bitand(c.collength, 240) / 16 =10 then 'SECOND'
when bitand(c.collength, 240) / 16 =11 then 'FRACTION(1)'
when bitand(c.collength, 240) / 16 =12 then 'FRACTION(2)'
when bitand(c.collength, 240) / 16 =13 then 'FRACTION(3)'
when bitand(c.collength, 240) / 16 =14 then 'FRACTION(4)'
when bitand(c.collength, 240) / 16 =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)||' TO '||(
select trim(case
when bitand(collength, 15) =0 then 'YEAR'
when bitand(collength, 15) =2 then 'MONTH'
when bitand(collength, 15) =4 then 'DAY'
when bitand(collength, 15) =6 then 'HOUR'
when bitand(collength, 15) =8 then 'MINUTE'
when bitand(collength, 15) =10 then 'SECOND'
when bitand(collength, 15) =11 then 'FRACTION(1)'
when bitand(collength, 15) =12 then 'FRACTION(2)'
when bitand(collength, 15) =13 then 'FRACTION(3)'
when bitand(collength, 15) =14 then 'FRACTION(4)'
when bitand(collength, 15) =15 then 'FRACTION(5)'
end )
from dual
)::varchar(11)
else 'default '||read_defaultstr (c.tabid,c.colno,c.coltype,c.collength,c.extended_id)::varchar(255)end as defaultvalue ,
case when colattr=256 then '('||default2 ||') virtual' end vmvalue
from (
select s1.tabid,s1.colname,s1.colno,s1.coltype,s1.extended_id,s1.collength,s1.colattr,s2.type,s2.default default1,replace(wm_concat(s3.default),',','') default2 from syscolumns s1
left join (select tabid,default,colno,type from sysdefaults ) s2 on
s1.tabid=s2.tabid and s1.colno=s2.colno
left join (select tabid,default,colno from sysdefaultsexpr where type='T') s3 on
s1.tabid=s3.tabid and s1.colno=s3.colno
where s1.tabid=tableid
group by s1.tabid,s1.colname,s1.colno,s1.coltype,s1.extended_id,s1.collength,s1.colattr,s2.type,s2.default
order by s1.colno
)c
)
)
loop
sql_stmt :=sql_stmt||name.vddl||','||CHR(10);
end loop;--查询check约束for checkname in(
select 'constraint '||constrname ||'check ' ||checktext as checkexpr from (
select s1.constrname ,s2.checktext from sysconstraints s1,syschecks s2 where s1.constrid=s2.constrid
and s2.type='T' and s1.constrtype='C' and s1.tabid=tableid
)
)
loop
sql_stmt :=sql_stmt|| checkname.checkexpr||','||CHR(10);
end loop;--查询唯一约束for unique_constrsaints in(
select constrname,constrained_columns from (select constrname,wm_concat(colname) AS constrained_columns from (
SELECT c.constrname AS constrname,
CASE WHEN si.part1 = sc.colno THEN 1WHEN si.part2 = sc.colno THEN 2WHEN si.part3 = sc.colno THEN 3WHEN si.part4 = sc.colno THEN 4WHEN si.part5 = sc.colno THEN 5WHEN si.part6 = sc.colno THEN 6WHEN si.part7 = sc.colno THEN 7WHEN si.part8 = sc.colno THEN 8WHEN si.part9 = sc.colno THEN 9WHEN si.part10 = sc.colno THEN 10WHEN si.part11 = sc.colno THEN 11WHEN si.part12 = sc.colno THEN 12WHEN si.part13 = sc.colno THEN 13WHEN si.part14 = sc.colno THEN 14WHEN si.part15 = sc.colno THEN 15WHEN si.part16 = sc.colno THEN 16ELSE 99
END AS sx,
sc.colname
FROM sysconstraints cJOIN sysindexes si ON c.idxname = si.idxnameJOIN syscolumns sc ON c.tabid = sc.tabid
WHERE c.tabid = tableidAND c.constrtype = 'U'AND (si.part1 = sc.colno OR si.part2 = sc.colno OR si.part3 = sc.colnoOR si.part4 = sc.colno OR si.part5 = sc.colnoOR si.part6 = sc.colno OR si.part7 = sc.colnoOR si.part8 = sc.colno OR si.part9 = sc.colnoOR si.part10 = sc.colno OR si.part11 = sc.colnoOR si.part12 = sc.colno OR si.part13 = sc.colnoOR si.part14 = sc.colno OR si.part15 = sc.colnoOR si.part16 = sc.colno)order by sx
)
GROUP BY constrname
ORDER BY constrname)
)loop
sql_stmt :=sql_stmt|| 'constraint '||unique_constrsaints.constrname || 'unique('||unique_constrsaints.constrained_columns||'),'||CHR(10) ;
end loop;--查询主键约束for primary_constrsaints in(
select constrname,constrained_columns from (
select constrname,wm_concat(colname) AS constrained_columns from (
SELECT c.constrname AS constrname,CASE WHEN si.part1 = sc.colno THEN 1WHEN si.part2 = sc.colno THEN 2WHEN si.part3 = sc.colno THEN 3WHEN si.part4 = sc.colno THEN 4WHEN si.part5 = sc.colno THEN 5WHEN si.part6 = sc.colno THEN 6WHEN si.part7 = sc.colno THEN 7WHEN si.part8 = sc.colno THEN 8WHEN si.part9 = sc.colno THEN 9WHEN si.part10 = sc.colno THEN 10WHEN si.part11 = sc.colno THEN 11WHEN si.part12 = sc.colno THEN 12WHEN si.part13 = sc.colno THEN 13WHEN si.part14 = sc.colno THEN 14WHEN si.part15 = sc.colno THEN 15WHEN si.part16 = sc.colno THEN 16ELSE 99END AS sx,
sc.colname
FROM sysconstraints cJOIN sysindexes si ON c.idxname = si.idxnameJOIN syscolumns sc ON c.tabid = sc.tabid
WHERE c.tabid = tableidAND c.constrtype = 'P'AND (si.part1 = sc.colno OR si.part2 = sc.colno OR si.part3 = sc.colnoOR si.part4 = sc.colno OR si.part5 = sc.colnoOR si.part6 = sc.colno OR si.part7 = sc.colnoOR si.part8 = sc.colno OR si.part9 = sc.colnoOR si.part10 = sc.colno OR si.part11 = sc.colnoOR si.part12 = sc.colno OR si.part13 = sc.colnoOR si.part14 = sc.colno OR si.part15 = sc.colnoOR si.part16 = sc.colno)order by sx
)
GROUP BY constrname
ORDER BY constrname
)
)loop
sql_stmt :=sql_stmt|| 'constraint '||primary_constrsaints.constrname || ' primary key('||primary_constrsaints.constrained_columns||'),'||CHR(10) ;
end loop;--查询外键约束for foreign_constrsaints in(
SELECT c.constrname AS foreign_key_name,(SELECT wm_concat(DISTINCT colname) FROM (SELECT sc.colname,CASE WHEN si.part1 = sc.colno THEN 1WHEN si.part2 = sc.colno THEN 2WHEN si.part3 = sc.colno THEN 3WHEN si.part4 = sc.colno THEN 4WHEN si.part5 = sc.colno THEN 5WHEN si.part6 = sc.colno THEN 6WHEN si.part7 = sc.colno THEN 7WHEN si.part8 = sc.colno THEN 8WHEN si.part9 = sc.colno THEN 9WHEN si.part10 = sc.colno THEN 10WHEN si.part11 = sc.colno THEN 11WHEN si.part12 = sc.colno THEN 12WHEN si.part13 = sc.colno THEN 13WHEN si.part14 = sc.colno THEN 14WHEN si.part15 = sc.colno THEN 15WHEN si.part16 = sc.colno THEN 16ELSE 99END AS col_orderFROM sysindexes siJOIN syscolumns sc ON c.tabid = sc.tabidWHERE si.idxname = c.idxnameAND (si.part1 = sc.colno OR si.part2 = sc.colno OR si.part3 = sc.colnoOR si.part4 = sc.colno OR si.part5 = sc.colno OR si.part6 = sc.colnoOR si.part7 = sc.colno OR si.part8 = sc.colno OR si.part9 = sc.colnoOR si.part10 = sc.colno OR si.part11 = sc.colno OR si.part12 = sc.colnoOR si.part13 = sc.colno OR si.part14 = sc.colno OR si.part15 = sc.colnoOR si.part16 = sc.colno)ORDER BY col_order) ordered_cols) AS child_columns,pc.constrname AS parent_constraint_name,(SELECT wm_concat(DISTINCT colname)FROM (SELECT sc.colname,CASE WHEN si.part1 = sc.colno THEN 1WHEN si.part2 = sc.colno THEN 2WHEN si.part3 = sc.colno THEN 3WHEN si.part4 = sc.colno THEN 4WHEN si.part5 = sc.colno THEN 5WHEN si.part6 = sc.colno THEN 6WHEN si.part7 = sc.colno THEN 7WHEN si.part8 = sc.colno THEN 8WHEN si.part9 = sc.colno THEN 9WHEN si.part10 = sc.colno THEN 10WHEN si.part11 = sc.colno THEN 11WHEN si.part12 = sc.colno THEN 12WHEN si.part13 = sc.colno THEN 13WHEN si.part14 = sc.colno THEN 14WHEN si.part15 = sc.colno THEN 15WHEN si.part16 = sc.colno THEN 16ELSE 99END AS col_orderFROM sysindexes siJOIN syscolumns sc ON pc.tabid = sc.tabidWHERE si.idxname = pc.idxnameAND (si.part1 = sc.colno OR si.part2 = sc.colno OR si.part3 = sc.colnoOR si.part4 = sc.colno OR si.part5 = sc.colno OR si.part6 = sc.colnoOR si.part7 = sc.colno OR si.part8 = sc.colno OR si.part9 = sc.colnoOR si.part10 = sc.colno OR si.part11 = sc.colno OR si.part12 = sc.colnoOR si.part13 = sc.colno OR si.part14 = sc.colno OR si.part15 = sc.colnoOR si.part16 = sc.colno)ORDER BY col_order) ordered_cols) AS parent_columns,pt.tabname AS parent_table
FROM sysconstraints cJOIN sysreferences sr ON c.constrid = sr.constridJOIN sysconstraints pc ON pc.constrid = sr.primaryJOIN systables pt ON pc.tabid = pt.tabid
WHERE c.tabid = tableidAND c.constrtype = 'R'
ORDER BY c.constrname
)loop
sql_stmt :=sql_stmt|| 'constraint '||foreign_constrsaints.foreign_key_name || ' foreign key('||foreign_constrsaints.child_columns||') references '||foreign_constrsaints.parent_table||'('||foreign_constrsaints.parent_columns||'),'||CHR(10) ;
end loop;sql_stmt := sql_stmt||');';--去除最后的逗号
sql_stmt := REGEXP_REPLACE(sql_stmt, ',\s*\)\s*;\s*$', ');');--dbms_output.put_line(sql_stmt);
--sql_stmt := REGEXP_REPLACE(sql_stmt, '\),\s*\);$', '));');--dbms_output.put_line(sql_stmt);
return sql_stmt;end GET_TABLE_DDL_2_ORACLE;function GET_VIEW_DDL(objname varchar2,users varchar2) return varchar2 is
tid int;
flag int;
sql_stmt varchar2;
viewtext varchar2;
ret varchar2;
begin
select tabid,flags into tid,flag from systables where tabname=objname and tabtype='V' and owner=users;if tid <=0 THEN
return null;
end if;if flag=16384 then
sql_stmt :='set environment sqlmode "oracle";';
elsif flag=0 then
sql_stmt :='set environment sqlmode "gbase";';
else
return null;
end if;select replace(viewtext,'@pinjiefengefu','') into viewtext from (
select replace(wm_concat(viewtextall),'@pinjiefengefu,','') viewtext from (
select seqno,viewtext||'@pinjiefengefu' as viewtextall from sysviews where tabid=tid order by seqno asc
));ret :=sql_stmt|| CHR(10) ||viewtext;
dbms_output.put_line(ret);
return ret;
end GET_VIEW_DDL;function GET_PROCEDURE_DDL(objname varchar2,users varchar2) return varchar2 is
tid int;
flag int;
sql_stmt varchar2;
viewtext varchar2;
cnt int;
begin select count(1) into cnt from sysprocedures where procname=objname and owner=users;if cnt =0 then
return null;
end if;for procddl in(
select procid,procflags from sysprocedures where procname=objname and mode='O' and isproc='t' and type=0 and owner=users
)
loop
if procddl.procflags=4 then
sql_stmt :='set environment sqlmode "oracle";';
elsif procddl.procflags=0 then
sql_stmt :='set environment sqlmode "gbase";';
end if;select replace(viewtext,'@pinjiefengefu','') into viewtext from (
select replace(wm_concat(proctext),'@pinjiefengefu,','') viewtext from (
select seqno,data::varchar(256) proctext from sysprocbody where procid=procddl.procid and datakey='T' order by seqno asc
));sql_stmt :=sql_stmt|| CHR(10) ||viewtext;
end loop;dbms_output.put_line(sql_stmt);
return sql_stmt;end GET_PROCEDURE_DDL;function GET_FUNCTION_DDL(objname varchar2,users varchar2) return varchar2 is
tid int;
flag int;
sql_stmt varchar2;
viewtext varchar2;
cnt int;
begin select count(1) into cnt from sysprocedures where procname=objname and owner=users;if cnt =0 then
return null;
end if;for procddl in(
select procid,procflags from sysprocedures where procname=objname and mode='O' and isproc='f' and type=0 and owner=users
)
loop
if procddl.procflags=4 then
sql_stmt :='set environment sqlmode "oracle";';
elsif procddl.procflags=0 then
sql_stmt :='set environment sqlmode "gbase";';
end if;select replace(viewtext,'@pinjiefengefu','') into viewtext from (
select replace(wm_concat(proctext),'@pinjiefengefu,','') viewtext from (
select seqno,data::varchar(256) proctext from sysprocbody where procid=procddl.procid and datakey='T' order by seqno asc
));sql_stmt :=sql_stmt|| CHR(10) ||viewtext;
end loop;--dbms_output.put_line(sql_stmt);
return sql_stmt;
end GET_FUNCTION_DDL;function GET_PACKAGE_DDL(objname varchar2,users varchar2) return varchar2 is
tid int;
flag int;
sql_stmt varchar2;
viewtext varchar2;
cnt int;
begin
select procid into tid from sysprocedures where mode='O' and isproc='p' and type = 1 and procname=objname and owner=users;
if tid <=0 or cnt is null then
return null;
end if;sql_stmt :='set environment sqllmode "oracle";';select replace(viewtext,'@pinjiefengefu','') into viewtext from (
select replace(wm_concat(proctext),'@pinjiefengefu,','') viewtext from (
select seqno,data::varchar(256) proctext from sysprocbody where procid=tid and datakey='T' order by seqno asc
));sql_stmt :=sql_stmt|| CHR(10) ||viewtext;
return sql_stmt;
end GET_PACKAGE_DDL;function GET_PACKAGE_BODY_DDL(objname varchar2,users varchar2) return varchar2 is
tid int;
flag int;
sql_stmt varchar2;
viewtext varchar2;
cnt int;
begin
select procid into tid from sysprocedures where mode='O' and isproc='p' and type = 2 and procname=objname and owner=users;
if tid <=0 or cnt is null then
return null;
end if;sql_stmt :='set environment sqllmode "oracle";';select replace(viewtext,'@pinjiefengefu','') into viewtext from (
select replace(wm_concat(proctext),'@pinjiefengefu,','') viewtext from (
select seqno,data::varchar(256) proctext from sysprocbody where procid=tid and datakey='T' order by seqno asc
));sql_stmt :=sql_stmt|| CHR(10) ||viewtext;
return sql_stmt;
end GET_PACKAGE_BODY_DDL;function GET_TRIGGER_DDL(objname varchar2,users varchar2) return varchar2 is
tid int;
flag int;
sql_stmt varchar2;
viewtext varchar2;
cnt int;
begin
select count(1) into cnt from systriggers where trigname=objname and owner=users;
if cnt=0 then
return null;
end if;select trigid,mode into tid,flag from systriggers where owner=users;if mode='G' then
sql_stmt :='set environment sqllmode "gbase";';
select replace(tritext,'@pinjiefengefu','') into viewtext from (
select replace(wm_concat(tritext),'@pinjiefengefu,','') tritext from (
select seqno,data::varchar(256) tritext from systrigbody where trigid=tid and datakey='D' order by seqno asc
));
sql_stmt :=sql_stmt||chr(10)||viewtext;select replace(tritext,'@pinjiefengefu','') into viewtext from (
select replace(wm_concat(tritext),'@pinjiefengefu,','') tritext from (
select seqno,data::varchar(256) tritext from systrigbody where trigid=tid and datakey='A' order by seqno asc
));
sql_stmt :=sql_stmt||chr(10)||viewtext;
return sql_stmt;elsif mode='O' then
sql_stmt :='set environment sqllmode "oracle";';
select replace(tritext,'@pinjiefengefu','') into viewtext from (
select replace(wm_concat(tritext),'@pinjiefengefu,','') tritext from (
select seqno,data::varchar(256) tritext from systrigbody where trigid=tid and datakey='P' order by seqno asc
));
end if;sql_stmt :=sql_stmt||chr(10)||viewtext;
return sql_stmt;end GET_TRIGGER_DDL;function GET_TYPE_DDL(objname varchar2,users varchar2) return varchar2 is
tid int;
flag int;
sql_stmt varchar2;
viewtext varchar2;
cnt int;
begin
select extended_id into tid from sysxtdtypes where name=objname and owner=users;
if extended_id <=0 or extended_id is null then
return null;
end if;sql_stmt :='set environment sqllmode "oracle";';select replace(tritext,'@pinjiefengefu','') into viewtext from (
select replace(wm_concat(tritext),'@pinjiefengefu,','') tritext from (
select seqno,source::varchar(256) tritext from source$ where objid=tid and datakey=13 order by seqno asc
));
sql_stmt :=sql_stmt||chr(10)||viewtext;return sql_stmt;end GET_TYPE_DDL;function GET_TYPE_BODY_DDL(objname varchar2,users varchar2) return varchar2 is
begin
return null;
end GET_TYPE_BODY_DDL;function GET_SEQUENCE_DDL(objname varchar2,users varchar2) return varchar2 is
tid int;
flag int;
sql_stmt varchar2;
viewtext varchar2;
cnt int;
begin
select tabid into tid from systables where tabtype='Q' and owner=users;if tid <=0 or tid is null then
return null;
end if;select 'create sequence '||'objname'|| ' increment by '||inc_val|| ' start with '||start_val
||' maxvalue '||(case when max_val is null then 9223372036854775807 else max_val end) ||' minvalue '
||min_val||(case when cycle=0 then ' nocycle' else ' cycle' end)||(case when cache <> 0 then ' cache '|| cache else ' nocache ' end)
||' order;' as seqddl into sql_stmt from syssequences where seqid=tid;return sql_stmt;end GET_SEQUENCE_DDL;function GET_SYNONYM_DDL(objname varchar2,users varchar2) return varchar2 is
tid int;
flag int;
sql_stmt varchar2;
viewtext varchar2;
cnt int;begin
select tabid into tid from systables where tabname=objname and tabtype='S' and owner=users;if tid <=0 or tid is null then
return null;
end if;sql_stmt :='set environment sqllmode "gbase";';
--select servername,dbname,owner,tabname,btabid from syssyntable where tabid=tid;
--export locale database
select 'create public synonym for '||'"'||owner||'".'||tname into viewtext from (
select s1.owner,s1.tabname sname,s2.tabname tname,s2.owner owner from syssyntable s1,systables s2
where s1.btabid=s2.tabid and s1.btabid is not null and s1.tabid=tid
);sql_stmt :=sql_stmt||chr(10)||viewtext;
--export other database or server
select 'create public synonym for '||dbname||'@'||servername||':"'||owner||'".'||tname into viewtext from (
select s1.servername,s1.dbname,s1.owner,s1.tabname from syssyntable s1
where s1.tabid=tid and btabid is null
);
sql_stmt :=sql_stmt||chr(10)||viewtext;
return sql_stmt;
end GET_SYNONYM_DDL;end;
/
