/*CREATE TABLE f725a(
A number
,B number);
INSERT INTO f725a VALUES(1,2);
INSERT INTO f725a VALUES(4,6);
CREATE TABLE f725b(
C number
,D number
,E number);
INSERT INTO f725b VALUES(7,4,3);
INSERT INTO f725b VALUES(9,5,8);
INSERT INTO f725b VALUES(11,15,18);*/
select t1.A,t1.B,t2.C,t2.D,t2.E from
(select a.*,rownum r from f725a a ) t1
right join
(select b.*,rownum r from f725b b ) t2
on t1.r = t2.r;
/*CREATE TABLE F07(
A VARCHAR2(10),
B number,
C VARCHAR2(10)
);
INSERT INTO F07 VALUES('a',1,'X');
INSERT INTO F07 VALUES('a',2,'Y');
INSERT INTO F07 VALUES('b',3,'X');
INSERT INTO F07 VALUES('b',4,'X');
INSERT INTO F07 VALUES('c',5,'Y');
INSERT INTO F07 VALUES('c',6,'Y');*/
select * from f07;
select a,b,decode(to_char(sum(c)),'1','1','0','X','2','Y') c from
(select f.A,sum(f.b)over(partition by f.a) b,decode(f.c,'X',0,'Y',1) c
from f07 f ) t group by a,b
-- 根据执行顺序，先执行group by 分组 然后执行聚合函数，select
-- 所以 case when max(C)=min(C) then max(c) else '1' end
-- 这一句是对分组后的c进行比较
select A,sum(B) b,
(case when max(C)=min(C) then max(c)
else '1' end) c
from f07
group by A
select
instr(r.extwh_detail,',',
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"马庄\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":')
)
from RPT_COMMODITIES_LEDGER_DAILY r;
------------------------------------------------
select
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"马庄\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":')
from RPT_COMMODITIES_LEDGER_DAILY r;
---------------------------------------------------------
select r.id,
case when r.extwh_detail like '%马庄%' then '马庄' else null end A列,
case when r.extwh_detail like '%马庄%' then
substr(r.extwh_detail,
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"马庄\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":'),-- 从‘\"quantity\":’的‘：’后面开始截取,
--
(
instr(r.extwh_detail,',',
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"马庄\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":')-- 从‘\"quantity\":’的‘后面的第一个','的位置
)
)-(
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"马庄\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":')
)
) else null end B列,
---------------------------------------------------------
case when r.extwh_detail like '%定陶%' then '定陶' else null end C列,
case when r.extwh_detail like '%定陶%' then
substr(r.extwh_detail,
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"定陶\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":'),-- 从‘\"quantity\":’的‘：’后面开始截取,
--
(
instr(r.extwh_detail,',',
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"定陶\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":')-- 从‘\"quantity\":’的‘后面的第一个','的位置
)
)-(
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"定陶\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":')
)
) else null end D列,
--------------------------------------------------------------
case when r.extwh_detail like '%万吨%' then '万吨' else null end E列,
case when r.extwh_detail like '%万吨%' then
substr(r.extwh_detail,
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"万吨\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":'),-- 从‘\"quantity\":’的‘：’后面开始截取,
--
(
instr(r.extwh_detail,',',
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"万吨\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":')-- 从‘\"quantity\":’的‘后面的第一个','的位置
)
)-(
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"万吨\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":')
)
) else null end F列,
--------------------------------------------------------------------
case when r.extwh_detail like '%冰城%' then '冰城' else null end G列,
case when r.extwh_detail like '%冰城%' then
substr(r.extwh_detail,
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"冰城\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":'),-- 从‘\"quantity\":’的‘：’后面开始截取,
--
(
instr(r.extwh_detail,',',
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"冰城\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":')-- 从‘\"quantity\":’的‘后面的第一个','的位置
)
)-(
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"冰城\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":')
)
) else null end H列,
----------------------------------------------------
case when r.extwh_detail like '%山地%' then '山地' else null end I列,
case when r.extwh_detail like '%山地%' then
substr(r.extwh_detail,
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"山地\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":'),-- 从‘\"quantity\":’的‘：’后面开始截取,
--
(
instr(r.extwh_detail,',',
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"山地\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":')-- 从‘\"quantity\":’的‘后面的第一个','的位置
)
)-(
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"山地\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":')
)
) else null end J列,
-------------------------------------------------------------------
case when r.extwh_detail like '%寿光%' then '寿光' else null end K列,
case when r.extwh_detail like '%寿光%' then
substr(r.extwh_detail,
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"寿光\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":'),-- 从‘\"quantity\":’的‘：’后面开始截取,
--
(
instr(r.extwh_detail,',',
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"寿光\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":')-- 从‘\"quantity\":’的‘后面的第一个','的位置
)
)-(
instr(r.extwh_detail,'\"quantity\":',
-(length(r.extwh_detail)-instr(r.extwh_detail,'\"extwh_name\": \"寿光\"')+1),-- 找到’\"quantity\":xxxx‘的位置
1)
+
length('\"quantity\":')
)
) else null end L列
from RPT_COMMODITIES_LEDGER_DAILY r;
---------------------------------------
-------------------------------------------------QTingLJQ---------------------------------------------------------
select id,'马庄' a,sum(case when extwh_name='马庄' then to_number(quantity) else 0 end) b,
'定陶' c,sum(case when extwh_name='定陶' then to_number(quantity) else 0 end) d,
'万吨' e,sum(case when extwh_name='万吨' then to_number(quantity) else 0 end) f,
'冰城' g,sum(case when extwh_name='冰城' then to_number(quantity) else 0 end) h,
'山地' i,sum(case when extwh_name='山地' then to_number(quantity) else 0 end) j,
'寿光' k,sum(case when extwh_name='寿光' then to_number(quantity) else 0 end) l
from
(--
select id,
substr(extwh_detail,(instr(extwh_detail,'extwh_name'，1)+length('extwh_name')+6),2) extwh_name,
rtrim(replace(substr(extwh_detail,(instr(extwh_detail,'quantity'，1)+length('quantity')+3),5),',',''),'\') quantity
from rpt_commodities_ledger_daily
where instr(extwh_detail,'{',1,2) = 0
union all
--
select id,
substr(extwh_detail,(instr(extwh_detail,'extwh_name'，1)+length('extwh_name')+6),2) ,
rtrim(replace(substr(extwh_detail,(instr(extwh_detail,'quantity'，1)+length('quantity')+3),5),',',''),'\')
from rpt_commodities_ledger_daily
where instr(extwh_detail,'{',1,2) != 0 and instr(extwh_detail,'{',1,3) = 0
union all
select id,
substr(extwh_detail,(instr(extwh_detail,'extwh_name'，1,2)+length('extwh_name')+6),2) ,
rtrim(replace(substr(extwh_detail,(instr(extwh_detail,'quantity'，1,2)+length('quantity')+3),5),',',''),'\')
from rpt_commodities_ledger_daily
where instr(extwh_detail,'{',1,2) != 0 and instr(extwh_detail,'{',1,3) = 0
union all
--
select id,
substr(extwh_detail,(instr(extwh_detail,'extwh_name'，1)+length('extwh_name')+6),2) ,
rtrim(replace(substr(extwh_detail,(instr(extwh_detail,'quantity'，1)+length('quantity')+3),5),',',''),'\')
from rpt_commodities_ledger_daily
where instr(extwh_detail,'{',1,3) != 0
union all
select id,
substr(extwh_detail,(instr(extwh_detail,'extwh_name'，1,2)+length('extwh_name')+6),2) ,
rtrim(replace(substr(extwh_detail,(instr(extwh_detail,'quantity'，1,2)+length('quantity')+3),5),',',''),'\')
from rpt_commodities_ledger_daily
where instr(extwh_detail,'{',1,3) != 0
union all
select id,
substr(extwh_detail,(instr(extwh_detail,'extwh_name'，1,3)+length('extwh_name')+6),2) ,
rtrim(replace(substr(extwh_detail,(instr(extwh_detail,'quantity'，1,3)+length('quantity')+3),5),',',''),'\')
from rpt_commodities_ledger_daily
where instr(extwh_detail,'{',1,3) != 0) e
group by id;
-------------------------------------------------QTingXLJ------------------------------------------------------
with a1 as(
--一组的
select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily where id not in
((select id from
(select id,substr(extwh_detail,(instr(extwh_detail,'extwh_name',1,2))+16,2) 二
from rpt_commodities_ledger_daily)
where 二='马庄')
union all
(select id from
(select id,substr(extwh_detail,(instr(extwh_detail,'extwh_name',1,3))+16,2) 三
from rpt_commodities_ledger_daily)
where 三='马庄'))
union all
--两组的
select id,substr(extwh_detail,1,instr(extwh_detail,'}')) from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily where id in
(select id from
(select id,substr(extwh_detail,(instr(extwh_detail,'extwh_name',1,2))+16,2) 二
from rpt_commodities_ledger_daily)
where 二='马庄'))
union all
select id,substr(extwh_detail,instr(extwh_detail,'{',1,2),
instr(extwh_detail,'}',1,2)-instr(extwh_detail,'{',1,2)+1) from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily where id in
(select id from
(select id,substr(extwh_detail,(instr(extwh_detail,'extwh_name',1,2))+16,2) 二
from rpt_commodities_ledger_daily)
where 二='马庄'))
union all
--三组的
select id,substr(extwh_detail,1,instr(extwh_detail,'}')) from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily where id in
(select id from
(select id,substr(extwh_detail,(instr(extwh_detail,'extwh_name',1,3))+16,2) 三
from rpt_commodities_ledger_daily)
where 三='马庄'))
union all
select id,substr(extwh_detail,instr(extwh_detail,'{',1,2),
instr(extwh_detail,'}',1,2)-instr(extwh_detail,'{',1,2)+1) from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily where id in
(select id from
(select id,substr(extwh_detail,(instr(extwh_detail,'extwh_name',1,3))+16,2) 三
from rpt_commodities_ledger_daily)
where 三='马庄'))
union all
select id,substr(extwh_detail,instr(extwh_detail,'{',1,3),
instr(extwh_detail,'}',1,3)-instr(extwh_detail,'{',1,3)+1) from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily where id in
(select id from
(select id,substr(extwh_detail,(instr(extwh_detail,'extwh_name',1,3))+16,2) 三
from rpt_commodities_ledger_daily)
where 三='马庄'))
),
a2 as(
select id,substr(extwh_detail,instr(extwh_detail,':',1,4)+4,2) 地点,
substr(extwh_detail,instr(extwh_detail,':',1,2)+2,instr(extwh_detail,',',1,2)-instr(extwh_detail,':',1,2)-2) quantity
from a1)
select id,
'马庄' a,
sum(case when 地点='马庄' then to_number(quantity) else 0 end) b,
'寿光' c,
sum(case when 地点='寿光' then to_number(quantity) else 0 end) d,
'万吨' e,
sum(case when 地点='万吨' then to_number(quantity) else 0 end) f,
'定陶' e,
sum(case when 地点='定陶' then to_number(quantity) else 0 end) f,
'山地' e,
sum(case when 地点='山地' then to_number(quantity) else 0 end) f,
'冰城' e,
sum(case when 地点='冰城' then to_number(quantity) else 0 end) f
from a2
group by id
order by id;
-------------------------------------------QTingWGD------------------------------------------------
select a.id,mz,
(case when mz='马庄' then
substr((
substr(substr(extwh_detail,-(instr(extwh_detail,'马庄')),39),(instr(extwh_detail,'ity\": ')+1),8)),
0,(instr(
substr(
substr(extwh_detail,-(instr(extwh_detail,'马庄')),39),(instr(extwh_detail,'ity\": ')+1),8),','))-1) end)
马庄,sg,
(case when sg='寿光' then
substr((substr(substr(extwh_detail,-(instr(extwh_detail,'寿光')),39),(instr(extwh_detail,'ity\": ')+1),8)),
0,(instr(
substr(substr(extwh_detail,-(instr(extwh_detail,'寿光')),39),(instr(extwh_detail,'ity\": ')+1),8),','))-1)
end) 寿光,bc,(case when bc='冰城' then
substr((substr(substr(extwh_detail,-(instr(extwh_detail,'冰城')),39),(instr(extwh_detail,'ity\": ')+1),8)),
0,(instr(
substr(substr(extwh_detail,-(instr(extwh_detail,'冰城')),39),(instr(extwh_detail,'ity\": ')+1),8),','))-1)
end) 冰城，dt，(case when dt='定陶' then
substr((substr(substr(extwh_detail,-(instr(extwh_detail,'定陶')),39),(instr(extwh_detail,'ity\": ')+1),8)),
0,(instr(
substr(substr(extwh_detail,-(instr(extwh_detail,'定陶')),39),(instr(extwh_detail,'ity\": ')+1),8),','))-1)
end) 定陶,wd,(case when wd='万吨' then
substr((substr(substr(extwh_detail,-(instr(extwh_detail,'万吨')),39),(instr(extwh_detail,'ity\": ')+1),8)),
0,(instr(
substr(substr(extwh_detail,-(instr(extwh_detail,'万吨')),39),(instr(extwh_detail,'ity\": ')+1),8),','))-1)
end) 万吨,sd,
(case when sd='山地' then
substr((substr(substr(extwh_detail,-(instr(extwh_detail,'山地')),39),(instr(extwh_detail,'ity\": ')+1),8)),
0,(instr(
substr(substr(extwh_detail,-(instr(extwh_detail,'山地')),39),(instr(extwh_detail,'ity\": ')+1),8),','))-1)
end) 山地
from (
select id,(case when sj like '%马庄%' then '马庄' else null end) mz，
(case when sj like '%寿光%' then '寿光' else null end) sg，
(case when sj like '%冰城%' then '冰城' else null end) bc，
(case when sj like '%定陶%' then '定陶' else null end) dt，
(case when sj like '%万吨%' then '万吨' else null end) wd，
(case when sj like '%山地%' then '山地'else null end) sd
from (select id,extwh_detail sj from rpt_commodities_ledger_daily)) a,rpt_commodities_ledger_daily b
where a.id=b.id
-----------------------------------------------------------------------------------------------------------------
--只有一组公司的数据：
select rpt.*
from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily) rpt
where instr(rpt.extwh_detail,'{',1,2) = 0
union all
--有两组公司的数据：
select rpt.id,substr(rpt.extwh_detail,1,instr(rpt.extwh_detail,'}')) extwh_detail
from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily) rpt
where instr(rpt.extwh_detail,'{',1,2) != 0 and instr(rpt.extwh_detail,'{',1,3) = 0
union all
select rpt.id,substr(rpt.extwh_detail,instr(rpt.extwh_detail,'{',1,2)) extwh_detail
from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily) rpt
where instr(rpt.extwh_detail,'{',1,2) != 0 and instr(rpt.extwh_detail,'{',1,3) = 0
union all
--有三组公司的数据：
select rpt.id,substr(rpt.extwh_detail,1,instr(rpt.extwh_detail,'}')) extwh_detail
from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily) rpt
where instr(rpt.extwh_detail,'{',1,3) != 0
union all
select rpt.id,substr(rpt.extwh_detail,instr(rpt.extwh_detail
,'{',1,2),instr(rpt.extwh_detail,'}',1,2)-instr(rpt.extwh_detail,'{',1,2)+1) extwh_detail
from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily) rpt
where instr(rpt.extwh_detail,'{',1,3) != 0
union all
select rpt.id,substr(rpt.extwh_detail,instr(rpt.extwh_detail,'{',1,3)) extwh_detail
from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily) rpt
where instr(rpt.extwh_detail,'{',1,3) != 0
----------------------------------------------------------------------------------------------------------------
--select count(*) from rpt_commodities_ledger_daily;
--select * from rpt_commodities_ledger_daily where instr(EXTWH_DETAIL,'马庄') > 0;
----------------------------------------------------------------------------------------------------------------
--只有一组公司的数据：
with rpt_commodities as(
select rpt.*
from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily) rpt
where instr(rpt.extwh_detail,'{',1,2) = 0
union all
--有两组公司的数据：
select rpt.id,substr(rpt.extwh_detail,1,instr(rpt.extwh_detail,'}')) extwh_detail
from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily) rpt
where instr(rpt.extwh_detail,'{',1,2) != 0 and instr(rpt.extwh_detail,'{',1,3) = 0
union all
select rpt.id,substr(rpt.extwh_detail,instr(rpt.extwh_detail,'{',1,2)) extwh_detail
from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily) rpt
where instr(rpt.extwh_detail,'{',1,2) != 0 and instr(rpt.extwh_detail,'{',1,3) = 0
union all
--有三组公司的数据：
select rpt.id,substr(rpt.extwh_detail,1,instr(rpt.extwh_detail,'}')) extwh_detail
from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily) rpt
where instr(rpt.extwh_detail,'{',1,3) != 0
union all
select rpt.id,substr(rpt.extwh_detail,instr(rpt.extwh_detail
,'{',1,2),instr(rpt.extwh_detail,'}',1,2)-instr(rpt.extwh_detail,'{',1,2)+1) extwh_detail
from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily) rpt
where instr(rpt.extwh_detail,'{',1,3) != 0
union all
select rpt.id,substr(rpt.extwh_detail,instr(rpt.extwh_detail,'{',1,3)) extwh_detail
from
(select id,replace(replace(extwh_detail,'[',''),']','') extwh_detail from rpt_commodities_ledger_daily) rpt
where instr(rpt.extwh_detail,'{',1,3) != 0
)
,rpt_commodities_ledger as(
select id,extwh_detail
,substr(extwh_detail,instr(extwh_detail,':',1,2)+1,instr(extwh_detail,',',1,2)-(instr(extwh_detail,':',1,2)+1)) quantity
from rpt_commodities
)
select * from rpt_commodities_ledger;
---------------------------------------------------------------------------------------------------------------------------------
-- Oracle->MySQL
-- 使用时改一下where条件的owner和table_name
-- 字段数据类型映射时会将Oracle中的浮点NUMBER转换为decimal(65,8)定点数
-- 可以识别主键约束、非空约束，但无法识别外键约束、唯一约束、自定义check
-- 对于Oracle字符串长度为4000的，MySQL中类型会使用text，否则会使用varchar
SELECT
concat(concat(t2.owner,'.'),t2.table_name) src_table_name,
CASE
WHEN t1.c=1 THEN concat(concat(concat(concat('create table ',lower(t2.owner)),'.'),lower(t2.table_name)),'(')
WHEN t1.c=2 THEN
concat(
concat(
concat(lower(t2.column_name),' '),
concat(
concat(
CASE
WHEN t2.data_type IN ('VARCHAR','VARCHAR2','CHAR','NCHAR','NVARCHAR2') THEN (case when t2.data_length=4000 then 'text' else concat(concat(concat('varchar','('),t2.data_length),')') end)
WHEN regexp_replace(t2.data_type,'[(].+[)]','') IN ('TIMESTAMP','DATE') THEN 'datetime'
WHEN t2.data_type IN ('CLOB','NCLOB','LONG') THEN 'text'
WHEN t2.data_type IN ('BLOB','LONG RAW') THEN 'blob'
WHEN t2.data_type IN ('BINARY_FLOAT') THEN 'float'
WHEN t2.data_type IN ('BINARY_DOUBLE') THEN 'double'
WHEN t2.data_type IN ('FLOAT') THEN concat(concat('float(',CASE WHEN t2.data_precision>53 THEN 53 ELSE t2.data_precision END),')')
WHEN t2.data_type IN ('NUMBER') THEN
CASE WHEN t2.data_precision IS NOT NULL AND t2.data_scale IS NOT NULL THEN concat(concat(concat(concat('decimal(',t2.data_precision),','),t2.data_scale),')')
WHEN t2.data_precision IS NULL AND t2.data_scale IS NULL THEN 'decimal(65,8)'
end
end
,' '),
(CASE WHEN t2.nullable='N' THEN 'not null ' ELSE '' END)
)
),
concat(concat(concat('comment ''',REPLACE(t2.column_comments,'''','''''')),''''),(CASE WHEN t2.column_id=t2.max_column_id AND t2.key_column_name IS null THEN '' ELSE ',' end))
)
WHEN t1.c=4 THEN concat(concat(') comment ''',REPLACE(t2.table_comments,'''','''''')),concat('''',chr(59)))
WHEN t1.c=3 THEN concat(concat('primary key(',lower(t2.key_column_name)),')')
END sql_line,
CASE WHEN t1.c=1 THEN 0 WHEN t1.c=2 THEN t2.column_id WHEN t1.c=3 THEN t2.column_id+1 WHEN t1.c=4 THEN t2.column_id+2 WHEN t1.c=5 THEN t2.column_id+3 end ord,
t2.owner,
t2.table_name,
t2.table_comments,
CASE WHEN t1.c=2 THEN t2.data_type END data_type,
CASE WHEN t1.c=2 THEN t2.data_length END data_length,
CASE WHEN t1.c=2 THEN t2.data_precision END data_precision,
CASE WHEN t1.c=2 THEN t2.data_scale END data_scale
FROM
(SELECT 1 c FROM dual
UNION ALL
SELECT 2 c FROM dual
UNION ALL
SELECT 3 c FROM dual
UNION ALL
SELECT 4 c FROM dual
UNION ALL
SELECT 5 c FROM dual
) t1
JOIN (
SELECT t1.owner,
t1.table_name,
t1.comments table_comments,
t2.COLUMN_name column_name,
t2.data_type,
t3.comments column_comments,
t2.data_length,
t2.data_PRECISION,
t2.data_scale,
t2.column_id,
max(t2.column_id) over(PARTITION BY t1.owner,t2.table_name) max_column_id,
t4.key_column_name,
t2.nullable
FROM all_tab_comments t1
JOIN ALL_TAB_COLS t2 ON t2.owner=t1.owner AND t2.table_name=t1.table_name
JOIN all_col_comments t3 ON t3.owner=t1.owner AND t3.table_name=t1.table_name AND t3.column_name=t2.column_name
LEFT JOIN (SELECT t1.owner,t1.constraint_name,t1.table_name,to_char(wmsys.wm_concat(t2.column_name)) key_column_name FROM ALL_CONSTRAINTS t1
JOIN ALL_CONS_COLUMNS t2 ON t2.owner=t1.owner AND t2.constraint_name=t1.constraint_name AND t2.table_name=t1.table_name AND t1.constraint_type='P'
GROUP BY t1.owner,t1.constraint_name,t1.table_name) t4 ON t4.owner=t1.owner AND t4.table_name=t1.table_name
WHERE t1.owner IN ('SCOTT') AND t1.table_name IN ('EMP') -- 在此处限制owner和table_name
) t2 ON t1.c=1 AND t2.column_id=1 OR t1.c=2 OR t1.c=3 AND t2.key_column_name IS NOT NULL AND t2.column_id=t2.max_column_id OR t1.c=4 AND t2.column_id=t2.max_column_id OR t1.c=5 AND t2.column_id=t2.max_column_id
ORDER BY src_table_name,ord