oracle语句相关

相同ID有多条记录，取时间最近的一条

pk_psndoc是可能重复的id列，begindate是时间段

select *
  from (select a1.*,
               row_number() over(partition by a1.pk_psndoc order by a1.begindate desc) rn
          from hi_psnjob a1)
 where rn = 1

视图授权给指定用户

grant connect to oatest;
grant select on org_corp_view to oatest;

查下属公司

 

prior跟父节点列fathercorp放在一起，就是往父结点方向遍历；prior跟子结点列pk_corp放在一起，则往叶子结点方向遍历.

 

SELECT *
  FROM bd_corp pp
 START WITH pp.pk_corp = '1044'
CONNECT BY PRIOR pp.pk_corp = pp.fathercorp;

创建函数索引

查询必须使用to_char(hh.created_tm,'yyyy-mm-dd')='2018-06-24'

create index head_idx_cretaetm on TT_INTERFACE_GL_HEADERS (to_char(created_tm,'yyyy-mm-dd'));

批量删除表

create or replace procedure dropProcedure as
   sql1 varchar2(1000);
begin

  for cur1 in (select 'drop table ' || tt.TABLE_NAME || ' ' c1
                 from all_tables tt
                where tt.OWNER = 'NCADMIN1'
                  and tt.TABLE_NAME like 'TEMQ%') loop

    sql1 := cur1.c1;
    execute immediate sql1;
    commit;

  end loop;

end dropProcedure;