MSSQL当中的下一段存储过程 怎么转换成ORALCE的存储过程
USE [GeologyArchives]
GO
/****** Object: StoredProcedure [dbo].[SP_DelTreeNodes] Script Date: 12/13/2010 08:58:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[SP_DelTreeNodes]
@deptid varchar(50),
@child int,
@userId varchar(50)--用于区分操作者对表T_Tmp的操作
AS
declare @parentid varchar(50),@ct int,@deptidnew varchar(50),@ct2 int
delete T_Tmp where userid=@userId
select @parentid=parentid from T_Tree where [treeid]=@deptid
select @ct=count(*) from T_Tree where [treeid]=@deptid
--set @deptidnew=@deptid
if @child=0 begin--删除包含子类型
insert into T_Tmp (deptid,userId)values(@deptid,@userId)--select deptid,parentdeptid from dept where parentdeptid=@deptidnew while @ct>0 begin ---写临时表,循环
declare t_cur cursor for select deptid from T_Tmp where userId=@userId
open t_cur fetch next from t_cur into @deptidnew while @@fetch_status=0 begin
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
delete T_Tmp where deptid=@deptidnew and userId=@userId
fetch next from t_cur into @deptidnew
end
select @ct=count(*) from T_Tmp where userId=@userId
close t_cur
deallocate t_cur
end
--delete dept where deptid=@deptid
end
else begin --不包含子类型
if @ct>0 begin
update T_Tree set parentid=@parentid where parentid=@deptid
end
--delete T_Tree where [treeid]=@deptid
update T_Tree set isdell=1 where [treeid]=@deptid
end
USE [GeologyArchives]
GO
/****** Object: StoredProcedure [dbo].[SP_DelTreeNodes] Script Date: 12/13/2010 08:58:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[SP_DelTreeNodes]
@deptid varchar(50),
@child int,
@userId varchar(50)--用于区分操作者对表T_Tmp的操作
AS
declare @parentid varchar(50),@ct int,@deptidnew varchar(50),@ct2 int
delete T_Tmp where userid=@userId
select @parentid=parentid from T_Tree where [treeid]=@deptid
select @ct=count(*) from T_Tree where [treeid]=@deptid
--set @deptidnew=@deptid
if @child=0 begin--删除包含子类型
insert into T_Tmp (deptid,userId)values(@deptid,@userId)--select deptid,parentdeptid from dept where parentdeptid=@deptidnew while @ct>0 begin ---写临时表,循环
declare t_cur cursor for select deptid from T_Tmp where userId=@userId
open t_cur fetch next from t_cur into @deptidnew while @@fetch_status=0 begin
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
delete T_Tmp where deptid=@deptidnew and userId=@userId
fetch next from t_cur into @deptidnew
end
select @ct=count(*) from T_Tmp where userId=@userId
close t_cur
deallocate t_cur
end
--delete dept where deptid=@deptid
end
else begin --不包含子类型
if @ct>0 begin
update T_Tree set parentid=@parentid where parentid=@deptid
end
--delete T_Tree where [treeid]=@deptid
update T_Tree set isdell=1 where [treeid]=@deptid
end
解决方案 »
- oracle中用shutdown immediate命令,无论在SQLPLUS还是在OEM中,关闭数据库没有反应
- 请教定时job问题??????
- oracle 11g怎么把pfile保存为spfile?(11g默认为spfile)
- oracle触发器中NEW_VALUE和OLD_VALUE的问题。。谢谢大家了
- jstl有函数可以处理blob类型数据吗?
- 一个包中有多个需要返回结果集的存储过程的输出参数是引用光标类型的问题
- ▲求“分组进行编序号”的SQL
- 如何登陆oracle manager server
- ORACLE 9I中建表的脚本都 执行了,我的表去哪里能看到啊,在(oem)中
- 用Oradim80创建的Oracle服务,如何在服务启动的时候自动启动Instance,加载数据库?
- ORACLE FORM中的CURSOR不能ORDER BY吗?
- MSSQL的函数 怎么转到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
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
child number, ------这个关键字最好换个名字
userId varchar2) AS
parentid varchar(50);
ct number;
deptidnew varchar(50);
ct2 number;
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;
if child = 0 then
insert into T_Tmp (deptid, userId) values (deptid, userId);
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;
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;
update T_Tree set isdell = 1 where treeid = deptid;
end