Summary of common oracle SQL statements and oraclesql statements

Summary of common oracle SQL statements and oraclesql statements

1. oracle recursive query of random numbers

 

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

 

2. oracle deletes table partitions

 

You can use alter table drop partition to delete partitions. metadata and data will be deleted together,

Delete all alter table yourTable drop partition partionName1;

Clear Data alter table yourTable truncate partition partionName1;

 

3. oracle creates a new partition for the partition table

 

Alter table table_name add partition name values partition condition the TABLESPACE in which the TABLESPACE partition is located;

 

4. oracle query: Last month, next month

Select bz, ny, to_char (add_months (to_date (ny, 'yyyymm'), 1), 'yyyymm') nextny from cs

 

5. oracle recursive query Tree Structure

 

Use recursion to query data with level = 2

The syntax uses select * from tablename start with cond1 connect by cond2 where cond3;

Hierarchical filter keyword 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
 

Query results: