关于两个表的递归查询和删除

 

Oracle 的递归查询：

1.  从上级递归查下级

  

select  org_  from org_ org_  start  with org_.org_id = ? connect  by  prior  org_.org_id  = org_.org_parentid 

2.从下级递归查上级

select  org_  from org_ org_  start  with org_.org_id = ? connect  by  org_.org_id  = org_.org_parentid

 

3.  两表连接递归查询

 

     在这里假设有两张表，部门表（org_） 和员工表（user_）

     根据指定的部门ORG_ID，查询所有该部门的员工及子部门的员工

select user_.user_id from org_ org_, user_ user_ where user_.org_id = org_.org_id and ( user_.org_id = ? or user_.org_id in     ( select t.org_id from org_ t start with t.org_id = ? connect by prior t.org_id = t.org_parentid ) )

  

    查询部门所有的ORG_ID

  

select t.org_id,t.org_name from org_ t   
start with t.org_id = 3 connect by prior t.org_id = t.org_parentid

 4.过于删除操作的JAVA代码

StringBuilder del_sql_user=new StringBuilder();
StringBuilder del_sql_org=new StringBuilder();
// 递归查出起始部门下属的用户USRE_ID和改用户对应的部门ORG_ID
String select_userlist_id = "select user_.user_id from org_ org_, user_ user_  where user_.org_id = org_.org_id(+) and ( user_.org_id = "+ org_id+ " or user_.org_id in ( select t.org_id from org_ t start with t.org_id = "+ org_id+ " connect by prior t.org_id = t.org_parentid )  )";

String[] delsql_arr=new String[2];
del_sql_user.append("delete from user_ where user_id in("+select_userlist_id+")");
			
// 递归查出起始部门下属的部门ORG_ID
String select_orglist_id = "select t.org_id from org_ t start with t.org_id = "+ org_id + " connect by prior t.org_id = t.org_parentid";
del_sql_org.append("delete from org_ where org_id in("+select_orglist_id+")");
			
delsql_arr[0]=del_sql_user.toString();
delsql_arr[1]=del_sql_org.toString();
			
this.getJdbcTemplate().batchUpdate(delsql_arr);