create table ORG_info(
ORGID varchar(10), --组织编号
ORGNAME VARCHAR(30), --组织名称
superORG varchar(30)) --上级组织insert into ORG_info
select 'SS','公司总部','-' UNION ALL
select 'IT','IT部','SS' UNION ALL
select 'CW','财务部','SS'UNION ALL
SELECT 'IT01','IT网络','IT'UNION ALL
SELECT 'IT02','IT软件','IT'
--过程的功能:传入部门编号,获取其下属部门编号及名称
ALTER PROC [get_org]
@OrgID varchar(50)--组织编号
AS
declare @Org TABLE
(
TMP001 [varchar] (50), --上级组织
TMP002 [varchar] (50), --下级组织
TMP003 [varchar] (50), --组织名称
TMP004 [varchar] (1) --标志level
)declare @LoopCounter INT
select @LoopCounter=0insert into @Org(TMP001,TMP002,TMP003,TMP004)
select @OrgID,ORGID,ORGNAME,0 from ORG_info where superORG=@OrgID --判断是否有记录插入
while @@rowcount>0
begin
select @LoopCounter=@LoopCounter+1
insert into @Org(TMP001,TMP002,TMP003,TMP004)
select @OrgID,ORGID,ORGNAME,@LoopCounter from ORG_info join @Org on TMP002=superORG
where TMP004=@LoopCounter-1
end
select *from @Org
--调用过程
exec get_org 'IT'--删除测试表
drop table ORG_info
谢谢!!!
ORGID varchar(10), --组织编号
ORGNAME VARCHAR(30), --组织名称
superORG varchar(30)) --上级组织insert into ORG_info
select 'SS','公司总部','-' UNION ALL
select 'IT','IT部','SS' UNION ALL
select 'CW','财务部','SS'UNION ALL
SELECT 'IT01','IT网络','IT'UNION ALL
SELECT 'IT02','IT软件','IT'
--过程的功能:传入部门编号,获取其下属部门编号及名称
ALTER PROC [get_org]
@OrgID varchar(50)--组织编号
AS
declare @Org TABLE
(
TMP001 [varchar] (50), --上级组织
TMP002 [varchar] (50), --下级组织
TMP003 [varchar] (50), --组织名称
TMP004 [varchar] (1) --标志level
)declare @LoopCounter INT
select @LoopCounter=0insert into @Org(TMP001,TMP002,TMP003,TMP004)
select @OrgID,ORGID,ORGNAME,0 from ORG_info where superORG=@OrgID --判断是否有记录插入
while @@rowcount>0
begin
select @LoopCounter=@LoopCounter+1
insert into @Org(TMP001,TMP002,TMP003,TMP004)
select @OrgID,ORGID,ORGNAME,@LoopCounter from ORG_info join @Org on TMP002=superORG
where TMP004=@LoopCounter-1
end
select *from @Org
--调用过程
exec get_org 'IT'--删除测试表
drop table ORG_info
谢谢!!!
ORGID VARCHAR2(10), --组织编号
ORGNAME VARCHAR2(30), --组织名称
superORG varchar2(30) --上级组织
);INSERT INTO org_info VALUES ('SS', '公司总部', '-');
INSERT INTO org_info VALUES ('IT', 'IT部', 'SS');
INSERT INTO org_info VALUES ('CW', '财务部', 'SS');
INSERT INTO org_info VALUES ('IT01', 'IT网络', 'IT');
INSERT INTO org_info VALUES ('IT02', 'IT软件', 'IT');--过程的功能:传入部门编号,获取其下属部门编号及名称
select a.*,level
from ORG_info a
start with a.superORG = 'SS'
connect by prior a.ORGID = a.superORG
order by level;--删除测试表
drop table ORG_info
这样行不
--过程的功能:传入部门编号,获取其下属部门编号及名称 CREATE OR REPLACE PROCEDURE pro_test(pi_org VARCHAR2,
po_result OUT SYS_REFCURSOR) IS
BEGIN
OPEN po_result FOR
SELECT a.orgid, a.orgname, a.superorg, LEVEL
FROM org_info a
START WITH a.superorg = pi_org /*'SS'*/
CONNECT BY PRIOR a.orgid = a.superorg
ORDER BY LEVEL;
END;--调用过程
DECLARE
TYPE t_item_str IS RECORD(
orgid VARCHAR2(10),
orgname VARCHAR2(30),
superorg VARCHAR2(30),
slevel NUMBER); v_result SYS_REFCURSOR;
v_item_str t_item_str;
BEGIN
pro_test('SS', v_result);
LOOP
FETCH v_result
INTO v_item_str;
EXIT WHEN v_result%NOTFOUND;
dbms_output.put_line(v_item_str.orgid || ' ' || v_item_str.orgname || ' ' ||
v_item_str.superorg || ' ' || v_item_str.slevel);
END LOOP;
END;
那就这样了