SQLÓï¾ä·­Òë³ÉORACLEÓï¾ä - Oracle / »ù´¡ºÍ¹ÜÀí

SQL code:
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=0

insert 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

 

лл£¡£¡£¡

---

SQL code:
create table ORG_info(
ORGID VARCHAR2(10), --×éÖ¯±àº