Oracle常用函数2查询

 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;