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);