Oracle冷门却又好用的函数、脚本

一、获取层级数据，查询拥有下属的所有leader数据

where 1= 1 connect by prior emp_id = lead_id;

level 是伪列的使用，格式化层级

connect_by_isleaf 是否是叶子节点

connect_by_root 查找根节点

select lpad(' ',level*2,' ')||emp_name as name,emp_id,lead_id,salary,level
       from employee
       start with lead_id=0
       connect by prior emp_id=lead_id

select connect_by_root emp_name,emp_name,lead_id,salary
       from employee  
       where dept_no='002'
       start with lead_id=1
       connect by  prior emp_id = lead_id;

select emp_id,emp_name,lead_id,salary,connect_by_isleaf
       from employee
       start with lead_id=0
       connect by nocycle prior emp_id=lead_id;

二、查询数据库所有表结构

SELECT a.TABLE_NAME,b.comments ,a.COLUMN_NAME ,a.data_type||'('||a.DATA_LENGTH||')' ,c.comments FROM user_tab_columns a , user_tab_comments b,user_col_comments c  
WHERE a.TABLE_NAME = b.table_name 
AND a.COLUMN_NAME = c.COLUMN_NAME
AND a.TABLE_NAME  = c.table_name
AND a.TABLE_NAME IN (SELECT SEGMENT_NAME FROM dba_segments WHERE owner = 'USER' AND SEGMENT_TYPE = 'TABLE');

 

欢迎补充，持续更新 ing......