CREATE or replace function bustemp.funGetAllModuleFunctions(id varchar2(30))
returns AllModuleFunctions table(ModuleFunctionID varchar2(30) primary key not null,
SuperModuleFunctionID varchar2(30) ,Depth numeric(2,0),
ModuleFunctionName varchar2(30),SNInNode numeric(2,0) )
as
begin
declare ModuleFunctions table(ModuleFunctionID varchar2(30) primary key not null,
SuperModuleFunctionID varchar2(30) ,Depth numeric(2,0),
ModuleFunctionName varchar2(30),SNInNode numeric(2,0) )
declare Depth numeric(2,0)
declare ModuleFunctionID varchar2(30)
--初始化ModuleFunctionID,Depth
select Depth:=1
select ModuleFunctionID:=id
if len(ltrim(ModuleFunctionID))=0
select ModuleFunctionID=ModuleFunctionID from LibModuleFunction where len(ltrim(SuperModuleFunctionID))=0 or SuperModuleFunctionID is null
--初始化第一个节点
insert into ModuleFunctions
select ModuleFunctionID,SuperModuleFunctionID,Depth,ModuleFunctionName,SNInNode from LibModuleFunction where ModuleFunctionID=ModuleFunctionID
--展开所有子节点
while exists(select * from LibModuleFunction where
SuperModuleFunctionID in(select ModuleFunctionID from ModuleFunctions where Depth=Depth))
begin
insert into ModuleFunctions
select ModuleFunctionID,SuperModuleFunctionID,Depth+1,ModuleFunctionName,SNInNode from LibModuleFunction
where SuperModuleFunctionID in(select ModuleFunctionID from ModuleFunctions where Depth=Depth)
select Depth:=Depth+1
end
insert into AllModuleFunctions
select * from ModuleFunctions
return
end
returns AllModuleFunctions table(ModuleFunctionID varchar2(30) primary key not null,
SuperModuleFunctionID varchar2(30) ,Depth numeric(2,0),
ModuleFunctionName varchar2(30),SNInNode numeric(2,0) )
as
begin
declare ModuleFunctions table(ModuleFunctionID varchar2(30) primary key not null,
SuperModuleFunctionID varchar2(30) ,Depth numeric(2,0),
ModuleFunctionName varchar2(30),SNInNode numeric(2,0) )
declare Depth numeric(2,0)
declare ModuleFunctionID varchar2(30)
--初始化ModuleFunctionID,Depth
select Depth:=1
select ModuleFunctionID:=id
if len(ltrim(ModuleFunctionID))=0
select ModuleFunctionID=ModuleFunctionID from LibModuleFunction where len(ltrim(SuperModuleFunctionID))=0 or SuperModuleFunctionID is null
--初始化第一个节点
insert into ModuleFunctions
select ModuleFunctionID,SuperModuleFunctionID,Depth,ModuleFunctionName,SNInNode from LibModuleFunction where ModuleFunctionID=ModuleFunctionID
--展开所有子节点
while exists(select * from LibModuleFunction where
SuperModuleFunctionID in(select ModuleFunctionID from ModuleFunctions where Depth=Depth))
begin
insert into ModuleFunctions
select ModuleFunctionID,SuperModuleFunctionID,Depth+1,ModuleFunctionName,SNInNode from LibModuleFunction
where SuperModuleFunctionID in(select ModuleFunctionID from ModuleFunctions where Depth=Depth)
select Depth:=Depth+1
end
insert into AllModuleFunctions
select * from ModuleFunctions
return
end
解决方案 »
- 求一个分组汇总语句写法,急呀!
- ORA-12514:TNS:无法解析指定的连接标识符 急求
- oracle分页,有些看不懂
- OleDbCommand Parameter 时 IN 关键字查询问题
- 关于ORACLE DB LINK 连接SQLSERVER2000的问题
- 如何在oracle中评估用不同模型建立的数据库的效率
- 视图问题:1,对于多表联接的视图,DML操作时,是怎么达到增删改的目的的?2,什么情况下会对视图DML,一般不是直接增删改表么?
- 提问!!!!!!请高手们赐教!!!!!!!!!!!!!!查询问题
- 请问谁能有关toad的帮助文档?
- sql语句构造,按条计分 (在线)
- 在Visual C++中如何使用oracle 的Pro*C编程!怎么设置?
- job调用的过程可以有参数么?~
Depth numeric(2,0);
ModuleFunctionID varchar2(30);
begin
--初始化ModuleFunctionID,Depth
......
pl/sql edit
pl/sql developer
返回一个表?能这样用么,没见过这样的用法
然后再定义这个表变量呢?
怎么样,可以吗?
ORACLE没有理由不支持表变量吧
oracle中可以支持pl/sql表,而没有“表变量”