惯用oracle sql语句汇总

       1.   oracle 递归查询随机数

                      select to_char(sysdate,'yyyy')+1-level aae100  from dual connect by level <20

       2.   oracle  删除表分区

                 可以用 ALTER TABLE DROP PARTITION 来删除分区，元数据和数据将被一并删除，

                  全删除ALTER TABLE yourTable DROP PARTITION partionName1;

                  清数据ALTER TABLE yourTable TRUNCATE PARTITION partionName1;

     3.    oracle 为分区表创建新的分区

                   alter table table_name add partition 分区名称 values 分区条件 TABLESPACE 分区所在表空间;

     4.    oracle 查询上一月，下一月

                   select bz,ny,to_char(add_months(to_date(ny,'YYYYMM'),1),'YYYYMM') nextny from cs

     5.    oracle 递归查询树形结构

                 使用递归查询层次  level =2 的数据

                  语法使用    select * from tablename start with cond1 connect by cond2 where cond3;

                  层次筛选关键字   level 

                  with t as ( select bz,ny,to_char(add_months(to_date(ny,'YYYYMM'),1),'YYYYMM') nextny from cs)
                                select * from
                   (select  bz,ny,nextny,level x from t start with ny>=201401  connect by prior  ny = nextny)
                                 where x = 2
 

                             查询结果：