USE [GeologyArchives]
GO
/****** Object: StoredProcedure [dbo].[SP_Tree_UpOrDown] Script Date: 12/13/2010 09:28:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[SP_Tree_UpOrDown]
(
@wjh varchar(50),-- 本节点的treeid
@pwjh varchar(50),--父节点的treeid
@sign int --上下移的标志
)
AS
declare @oldsh varchar(50),@newsh varchar(50),@newid varchar(50)
if @sign = 0 begin --0上移
select @oldsh=showpos from T_Tree where [treeid]=@wjh --保存老位置
select @newsh=max(showpos) from T_Tree where parentid=@pwjh and showpos<@oldsh --取得新位置
select @newid=[treeid] from T_Tree where showpos=@newsh
if @newsh is not null begin
update T_Tree set showpos=@newsh where [treeid]=@wjh --被移动节点赋予新位置
update T_Tree set showpos=@oldsh where [treeid]=@newid --上面节点与被移动节点互换即下移
end
end
else begin --下移
select @oldsh=showpos from T_Tree where [treeid]=@wjh
select @newsh=min(showpos) from T_Tree where parentid=@pwjh and showpos>@oldsh
select @newid=[treeid] from T_Tree where showpos=@newsh
if @newsh is not null begin
update T_Tree set showpos=@newsh where [treeid]=@wjh
update T_Tree set showpos=@oldsh where [treeid]=@newid
end
end
这个在ORACLE应该怎么写
GO
/****** Object: StoredProcedure [dbo].[SP_Tree_UpOrDown] Script Date: 12/13/2010 09:28:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[SP_Tree_UpOrDown]
(
@wjh varchar(50),-- 本节点的treeid
@pwjh varchar(50),--父节点的treeid
@sign int --上下移的标志
)
AS
declare @oldsh varchar(50),@newsh varchar(50),@newid varchar(50)
if @sign = 0 begin --0上移
select @oldsh=showpos from T_Tree where [treeid]=@wjh --保存老位置
select @newsh=max(showpos) from T_Tree where parentid=@pwjh and showpos<@oldsh --取得新位置
select @newid=[treeid] from T_Tree where showpos=@newsh
if @newsh is not null begin
update T_Tree set showpos=@newsh where [treeid]=@wjh --被移动节点赋予新位置
update T_Tree set showpos=@oldsh where [treeid]=@newid --上面节点与被移动节点互换即下移
end
end
else begin --下移
select @oldsh=showpos from T_Tree where [treeid]=@wjh
select @newsh=min(showpos) from T_Tree where parentid=@pwjh and showpos>@oldsh
select @newid=[treeid] from T_Tree where showpos=@newsh
if @newsh is not null begin
update T_Tree set showpos=@newsh where [treeid]=@wjh
update T_Tree set showpos=@oldsh where [treeid]=@newid
end
end
这个在ORACLE应该怎么写
---分都给我正好我需要呵呵。。
create or replace PROCEDURE dbo.SP_DelTreeNodes(deptid varchar(50),
child number,
userId varchar(50)) --用于区分操作者对表T_Tmp的操作
AS
parentid varchar(50);
ct int;
deptidnew varchar(50);
ct2 int;
begin
execute 'delete T_Tmp where userid=' || userId;
select parentid into parentid from T_Tree where treeid = deptid;
select count(*) into ct from T_Tree where treeid = deptid;
--set @deptidnew=@deptid if child = 0 then
--删除包含子类型
insert into T_Tmp (deptid, userId) values (deptid, userId); --select deptid,parentdeptid from dept where parentdeptid=@deptidnew
while ct > 0 loop
---写临时表,循环
for t_cur in (select deptid from T_Tmp where userId = userId) loop
deptidnew := t_cur.deptid;
insert into T_Tmp
select treeid, parentid, userId
from T_Tree
where isdell <> 1
and parentid = deptidnew;
--delete T_Tree where [treeid]=@deptidnew
update T_Tree set isdell = 1 where treeid = deptidnew;
execute 'delete T_Tmp where deptid=' ||deptidnew||' and userId='userId;
end loop;
select count(*) into ct from T_Tmp where userId = userId;
end loop;
else
--不包含子类型
if ct > 0 then
update T_Tree set parentid = parentid where parentid = deptid;
end if;
end if;
--delete T_Tree where [treeid]=@deptid
update T_Tree set isdell = 1 where treeid = deptid;
end
pwjh in varchar(50), --父节点的treeid
sign in number --上下移的标志
) AS
oldsh varchar(50);
newsh varchar(50);
newid varchar(50);
begin
if sign = 0 then
--0上移
select showpos into oldsh from T_Tree where treeid = wjh; --保存老位置
select max(showpos)
into newsh
from T_Tree
where parentid = pwjh
and showpos < oldsh; --取得新位置
select treeid into newid from T_Tree where showpos = newsh;
if newsh is not null then
update T_Tree set showpos = newsh where treeid = wjh; --被移动节点赋予新位置
update T_Tree set showpos = oldsh where treeid = newid; --上面节点与被移动节点互换即下移
end if;
else
--下移
select showpos into oldsh from T_Tree where treeid = wjh;
select min(showpos)
into newsh
from T_Tree
where parentid = pwjh
and showpos > oldsh;
select treeid into newid from T_Tree where showpos = newsh;
if newsh is not null then
update T_Tree set showpos = newsh where treeid = wjh;
update T_Tree set showpos = oldsh where treeid = newid;
end if;
end if;
end
(
c_wjh varchar2,-- 本节点的treeid
c_pwjh varchar2,--父节点的treeid
c_sign number --上下移的标志
)
as
c_oldsh varchar(50);
c_newsh varchar(50);
c_newid varchar(50);
begin
if c_sign=0 then
select showpos into c_oldsh from T_Tree where treeid=c_wjh ;--保存老位置
select max(showpos) into c_newsh from T_Tree where parentid=c_pwjh and showpos<c_oldsh; --取得新位置
select treeid into c_newid from T_Tree where showpos=c_newsh;
if c_newsh is not null then
update T_Tree set showpos=c_newsh where treeid=c_wjh; --被移动节点赋予新位置
update T_Tree set showpos=c_oldsh where treeid=c_newid ;--上面节点与被移动节点互换即下移
end if;
else
select showpos into c_oldsh from T_Tree where treeid=c_wjh;
select min(showpos) into c_newsh from T_Tree where parentid=c_pwjh and showpos>c_oldsh;
select treeid into c_newid from T_Tree where showpos=c_newsh;
if c_newsh is not null then
update T_Tree set showpos=c_newsh where treeid=c_wjh;
update T_Tree set showpos=c_oldsh where treeid=c_newid;
end if;
end if;
end;