CREATE PROCEDURE sp_AddNewNodeProc
@Parent int,
@NodeId int
AS
declare @Proc_ID int
declare @ProcTpye int
declare @Value int
declare @cs cursor
set @cs=cursor for select Proc_ID,ProcTpye,[Value] from UDS_ProcRule where ClassId=@Parent
open @cs
fetch next from @cs into @Proc_ID,@ProcTpye,@Value
while @@FETCH_STATUS=0
begin
insert into UDS_ProcRule(Proc_ID,ProcTpye,[Value],ClassId)values(@Proc_ID,@ProcTpye,@Value,@NodeId)
fetch next from @cs into @Proc_ID,@ProcTpye,@Value
end
小弟不会Oracle,但是公司现在项目要求用Oracle 所以将sql数据库改为oracle 求高人帮小弟将此sql转换成oracle小弟不胜感激
@Parent int,
@NodeId int
AS
declare @Proc_ID int
declare @ProcTpye int
declare @Value int
declare @cs cursor
set @cs=cursor for select Proc_ID,ProcTpye,[Value] from UDS_ProcRule where ClassId=@Parent
open @cs
fetch next from @cs into @Proc_ID,@ProcTpye,@Value
while @@FETCH_STATUS=0
begin
insert into UDS_ProcRule(Proc_ID,ProcTpye,[Value],ClassId)values(@Proc_ID,@ProcTpye,@Value,@NodeId)
fetch next from @cs into @Proc_ID,@ProcTpye,@Value
end
小弟不会Oracle,但是公司现在项目要求用Oracle 所以将sql数据库改为oracle 求高人帮小弟将此sql转换成oracle小弟不胜感激
CREATE OR REPLACE PROCEDURE sp_AddNewNodeProc
(
@Parent int,
@NodeId int
)
AS
@Proc_ID int;
@ProcTpye int;
@Value int;
CURSOR @cs for select Proc_ID,ProcTpye,Value from UDS_ProcRule where ClassId=@Parent;
begin
open @cs;
loop
fetch @cs into @Proc_ID,@ProcTpye,@Value
exit when @cs%notfound; insert into UDS_ProcRule(Proc_ID,ProcTpye,Value,ClassId)values (@Proc_ID,@ProcTpye,@Value,@NodeId);
end loop;
commit;
end;
--少了close @cs
CREATE OR REPLACE PROCEDURE sp_AddNewNodeProc
(
@Parent int,
@NodeId int
)
AS
@Proc_ID int;
@ProcTpye int;
@Value int;
CURSOR @cs for select Proc_ID,ProcTpye,Value from UDS_ProcRule where ClassId=@Parent;
begin
open @cs;
loop
fetch @cs into @Proc_ID,@ProcTpye,@Value
exit when @cs%notfound; insert into UDS_ProcRule(Proc_ID,ProcTpye,Value,ClassId)values (@Proc_ID,@ProcTpye,@Value,@NodeId);
end loop;
close @cs;
commit;
end;
/--试着比较一下,特别是去掉那些@:CREATE OR REPLACE procedure tran_get_money(userid IN VARCHAR2,money IN number) as
cursor c1 is select fees from info_admin where user_id=userId;
cursor c2 is select money from info_admin where user_id=userId;
l_fees number:=0;
l_money number:=0;
begin
open c1;
fetch c1 into l_fees;
open c2;
fetch c2 into l_money;
money=money*fees;
update Account set fees_money=money where user_id = userId;
close c1;
close c2;
end;
求解释
谢谢
CREATE OR REPLACE PROCEDURE sp_AddNewNodeProc
(
Parent int,
NodeId int
)
AS
Proc_ID int;
ProcTpye int;
iValue int;
CURSOR cs for select Proc_ID,ProcTpye,Value from UDS_ProcRule where ClassId=Parent;
begin
open cs;
loop
fetch cs into Proc_ID,ProcTpye,iValue
exit when cs%notfound; insert into UDS_ProcRule(Proc_ID,ProcTpye,Value,ClassId)values (Proc_ID,ProcTpye,iValue,NodeId);
end loop;
close cs;
commit;
end;
CREATE OR REPLACE PROCEDURE sp_AddNewNodeProc(v_Parent number,NodeId number)--rename Parent column
is
declare Proc_ID number;
ProcTpye number;
v_Value number; --rename Value variable
cursor cs is select Proc_ID,ProcTpye,Value from UDS_ProcRule where ClassId=v_Parent;
begin
open cs;
loop
fetch cs into Proc_ID,ProcTpye,v_Value
exit when cs%notfound;
insert into UDS_ProcRule(Proc_ID,ProcTpye,Value,ClassId)
values(Proc_ID,ProcTpye,v_Value,NodeId)
end loop;
end;最好把表里面的Value字段名字改下,在oracle里面它是保留的关键字!
CREATE OR REPLACE PROCEDURE sp_AddNewNodeProc(v_Parent number,NodeId number)--rename Parent column
is
declare Proc_ID number;
ProcTpye number;
v_Value number; --rename Value variable
cursor cs is select Proc_ID,ProcTpye,Value from UDS_ProcRule where ClassId=v_Parent;
begin
open cs;
loop
fetch cs into Proc_ID,ProcTpye,v_Value;
exit when cs%notfound;
insert into UDS_ProcRule(Proc_ID,ProcTpye,Value,ClassId)
values(Proc_ID,ProcTpye,v_Value,NodeId);
end loop;
close cs; --close cursor, don't forget!
end;
--each pl/sql statement should end with ';'