plsql 树形展示，及java 调用 （ 参考）http://blog.chinaunix.net/u/26573/showart.php?id=494292）

--测试数据

drop table region;
create table REGION  (
   ID                   CHAR(32)                        not null,
   PID                  CHAR(32),
   NAME                 VARCHAR2(30),
   LEVEL_               number(2),
   LEAF_                number(1),
   constraint PK_REGION primary key (ID)
);
insert into REGION (ID, PID, NAME, LEVEL_, LEAF_) values ('根',null , '根',0, 0);
insert into REGION (ID, PID, NAME, LEVEL_, LEAF_) values ('华北','根' , '华北',1, 0);
insert into REGION (ID, PID, NAME, LEVEL_, LEAF_) values ('北京','华北' , '北京',2, 1);
insert into REGION (ID, PID, NAME, LEVEL_, LEAF_) values ('沈阳','华北' , '沈阳',2, 1);
insert into REGION (ID, PID, NAME, LEVEL_, LEAF_) values ('华南','根' , '',1, 1);
insert into REGION (ID, PID, NAME, LEVEL_, LEAF_) values ('西北','根' , '',1, 1);


---不知为何好像一个char 类型的'a' 与一个varchar2类型的'a' 并不相等，所以此例只对char 类型的id pid 类型有效,
create or replace package p_cursor is
type cursorType is ref cursor;
--type IdType is table of  char(32) index by binary_integer;
end;
/
create  or replace type IdType as table of  char(32);
/

--判断 有没有子节点  返回1 或者0  (注意类型是char 而不是varchar2 ，好像 不可滥用)
create or replace function hasChild(p_pid char, table_name varchar2 ,pidColName varchar2  ) return number  is
childrenCount number:=0;
v_sql_stmt varchar2(100) :='select count(*) from '||table_name||' where '||pidColName||'=:p_pid';

begin
  EXECUTE IMMEDIATE v_sql_stmt  into childrenCount USING   p_pid  ;
    if childrenCount =0 then
        return 0;
    else
        return 1;
    end if ;

end;
/

--此过程，私用，private  通过此过程的递归调用 ，所子孙节点都 返回到ids中
create or replace procedure getAllPrivate(rootid char, table_name varchar2 ,pidColName varchar2 ,idColName varchar2, ids in out  IdType ) is
v_id char(32);
 id_cur  p_cursor.cursorType   ;--is select distinct id from region where pid=rootid;
v_sql varchar2(1000) ;
begin
    v_sql:= 'select distinct '||idColName||' from  '||table_name || ' where '||pidColName ||'= :rootid';
     open id_cur for v_sql using rootid ;
    --如果有子节点
    if hasChild(rootid,table_name ,pidColName)=1  then
            fetch id_cur into v_id;--先打开一个(不知为何)
        loop
                exit when id_cur%notfound;
                      ids.extend;
                    ids(ids.last):= v_id;    --先不管有无子节点，先把自身加入
                    getAllPrivate(v_id,table_name,pidColName,idColName,ids);--递归处理子节点
                    fetch id_cur into v_id;
        end loop;
        close id_cur;
        end if;
end;

    /


    -- 可以通过此函数的调用
create or replace procedure getAll(rootid char, table_name varchar2 ,pidColName varchar2 ,idColName varchar2,  id_cursor in  out p_cursor.cursorType) is
v_hasChild number(1);
v_allIds IdType:=IdType();
begin
    v_allIds.extend;
    v_allIds(1):=rootid;
 --  execute immediate sql_stmt;
    getAllPrivate(rootid,table_name ,pidColName ,idColName ,v_allIds);
open id_cursor for  'select * from  table(cast( :v_allIds as IdType) )' using v_allIds ;
--    open id_cursor for  select  *  from  table(cast( v_allIds as IdType) );
end;

/





--测试代码
set serveroutput on
    declare  cursor_instance p_cursor.cursorType;
    id char(32);
    begin
        --region 表名，pid region.pid ;id region.id cursor_instance 实际并未用到
        getAll('华北','region' ,'pid','id',cursor_instance);
        fetch cursor_instance into id ;
        loop
            exit when cursor_instance%notfound;
        fetch cursor_instance into id ;
        dbms_output.put_line(id);
        end loop;
       


    end;
    /

 
java 调用存储过程

//此过程 ，传过来一个id ，然后将此节点的子孙节点的id 返回到一个游标中，
public class OracleProcedureReturnCollectionByPlSqlType_Test {

    public static void main(String[] args) throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
        Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "scott");
        CallableStatement stmt = conn.prepareCall("{call getAll(?,?,?,?,?) }");
        stmt.setString(1, "华北");//根节点id值， ,此节点的所有子孙的id 将被返回
        stmt.setString(2, "region");//表名
        stmt.setString(3, "pid");//表region 中表示父节点的列名
        stmt.setString(4, "id");//表region 中表示子节点的列名
        stmt.registerOutParameter(5, oracle.jdbc.OracleTypes.CURSOR);
        stmt.execute();
        ResultSet rs = (ResultSet) stmt.getObject(5);
        while (rs.next()) {
            String id =rs.getString(1);
             System.out.println(id);

        }

        stmt.close();

    }
}