--测试数据
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();
}
}
plsql 树形展示,及java 调用 ( 参考)http://blog.chinaunix.net/u/26573/showart.php?id=494292)
最新推荐文章于 2024-01-08 15:19:51 发布