1.insert 常用函数
insert into a(1,2,3.....)
select x1,x2,count(1),
sum(2+3),
avg(6),
min(7),
max(8),
sum(case when xxx then xx else xx end)
from b where xx group by x1,x2;
insert into a(1,2,3.....)
with
tmp1 as ()
,tmp2 as ()
select t1.xx,
t2.xx,
..
from tmp1 t1,tmp2 t2 where t1.x1=t2.x1
left join tb1 on ()
union all
select xx,
...
from tmp2;
merge into tb f
using (
with t as ()
select xx,
.....
from m,t where
)s
on(f.x1=s.x1)
when matched then update set
f.xx=s.xx
when not matched then insert(xx,xx)
values(xx,xx);
查看表名称
select a.table_name,b.comments,count(1) from user_tab_cols a
inner join user_tab_comments b
on a.table_name=b.table_name
where a.table_name like ‘T%’
group by a.table_name,b.comments;
查看表结构
select a.table_name,c.comments,a.column_name,a.comments,
case when b.data_type in (‘CHAR’,’VARCHAR2’) then b.data_type||’(‘||b.data_length||’)’
when b.data_type in(‘NUMBER’) then case when b.data_scale> 0 then b.data_type||’(’||’(‘||b.data_precision||’,’||b.data_scale||’)’
else b.data_type||’(‘||b.data_precision||’)’ end
else b.data_type end data_type,
b.column_id
from user_tab_cols b,user_col_comments a,user_tab_comments c
where a.table_name=b.table_name
and a.table_name=c.table_name
and a.column_name=b.column_name
order by a.table_name,b.column_id;