• 主页

sqlserver存储过程谁能帮忙改为oracle

lipt110 2014-01-07 01:33:54
USE [etcom_v9]
GO
/****** Object: StoredProcedure [dbo].[Web_Login] Script Date: 01/07/2014 09:44:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***
* 网上营业厅登陆
***/
ALTER procedure [dbo].[Web_Login]
@vcUserName varchar(50), ---用户名
@vcPassword varchar(20), ---密码
@vcCustType varchar(50) = '普通用户' --登陆的用户类型
AS
Begin
Declare @vcPasswordCheck varchar(20) --用户密码
Declare @vcCustNO varchar(20) --客户编码

if @vcCustType = '普通用户'
begin
select @vcCustNO = vcCustNO,@vcPasswordCheck = vcPsw from Boss_Custom a,BOSS_Associate b where a.vcCustNO=b.vcCustomNO and b.vcLink=@vcUserName
if @vcCustNO is null
begin
select @vcCustNO = vcTel,@vcPasswordCheck = vc170Psw from Jfgl_UserInfo where vcTel=@vcUserName
if @vcCustNO is null
select @vcCustNO = vcUserID,@vcPasswordCheck = vcPsw from BBN_UserInfo where vcUserID=@vcUserName
if @vcCustNO is null
select @vcCustNO = vcDoorNo,@vcPasswordCheck = vcPsw from DTV_UserInfo where vcDoorNo=@vcUserName
end
end
else if @vcCustType = '单位用户'
select @vcCustNO = a.vcCustNO,@vcPasswordCheck = a.vcPsw from BOSS_Custom a,jfgl_custinfo b where a.vcCustNO = b.vcAccNo and a.vcCustType = '单位用户' and b.vcDeptNo = @vcUserName

--验证密码是否正确
if @vcUserName is not null and @vcPassword is not null and @vcPassword=@vcPasswordCheck
Begin
select '1' as nRes,@vcCustNO as vcCustomNO
End
Else
Begin
select '-1' as nRes,'' as vcCustomNO
End
End
...全文
113 点赞 收藏 14
写回复
14 条回复
阳泉酒家小当家 2014年01月07日
引用 13 楼 lipt110 的回复:
[quote=引用 12 楼 yupeigu 的回复:] [quote=引用 11 楼 lipt110 的回复:] [quote=引用 10 楼 yupeigu 的回复:] [quote=引用 9 楼 lipt110 的回复:] [quote=引用 7 楼 ap0405140 的回复:] [quote=引用 3 楼 lipt110 的回复:] 还是有错误，你试过了吗
在Oracle 10g环境测试正常.[/quote] 肯定有错误啊，有的变量还带有@没加；什么的 PROCEDURE RADIUS.WEB_LOGIN 编译错误 错误：PLS-00103: Encountered the symbol ";" when expecting one of the following: if 行：54 文本：end ; 这就是报的错误[/quote] 试试这个： 
create procedure Web_Login
(vcUserName varchar2(50),
 vcPassword varchar2(20),
 vcCustType varchar2(50)='普通用户')
is
begin
vcPasswordCheck varchar2(20);
vcCustNO varchar2(20);

if vcCustType='普通用户' then
begin
	 select vcCustNO into vcCustNO,
		   vcPsw into vcPasswordCheck
	 from Boss_Custom a
	 inner join BOSS_Associate b on a.vcCustNO=b.vcCustomNO and b.vcLink=vcUserName
 
	if vcCustNO is null then
	begin
	  select vcTel into vcCustNO,
			vc170Psw into vcPasswordCheck
	  from Jfgl_UserInfo 
	  where vcTel=vcUserName

	 if vcCustNO is null then
	   select vcUserID into vcCustNO,
			  vcPsw into vcPasswordCheck
		from BBN_UserInfo 
		where vcUserID=vcUserName
	 end if;
	    
	 if vcCustNO is null then
	   select vcDoorNo into vcCustNO,
			  vcPsw into vcPasswordCheck
		from DTV_UserInfo 
		where vcDoorNo=vcUserName
	 end if;
   end if;
end;
else
begin
	if vcCustType='单位用户' then
	begin
		 select a.vcCustNO into vcCustNO,
			   a.vcPsw into vcPasswordCheck
		 from BOSS_Custom a
		 inner join jfgl_custinfo b on a.vcCustNO=b.vcAccNo and a.vcCustType='单位用户' and b.vcDeptNo=vcUserName
	end;
	end if;

	if vcUserName is not null and vcPassword is not null and vcPassword=vcPasswordCheck then
	Begin
		select '1' "nRes",vcCustNO "vcCustomNO" from dual
	End;
	Else
	Begin
		select '-1' "nRes",'' "vcCustomNO" from dual
	End;
	end if;
end;


end;
[/quote] 虽然还是不行，但是非常感谢[/quote] 还是报哪个错吗[/quote] 反正有不少的错误，我改改的话就好点，不过还是那错误[/quote] 试试这个： 


create procedure Web_Login
(vcUserName varchar2(50),
 vcPassword varchar2(20),
 vcCustType varchar2(50)='普通用户')
is
begin
vcPasswordCheck varchar2(20);
vcCustNO varchar2(20);

if vcCustType='普通用户' then

	 select vcCustNO into vcCustNO,
		   vcPsw into vcPasswordCheck
	 from Boss_Custom a
	 inner join BOSS_Associate b on a.vcCustNO=b.vcCustomNO and b.vcLink=vcUserName
 
	 if vcCustNO is null then
	
		  select vcTel into vcCustNO,
				vc170Psw into vcPasswordCheck
		  from Jfgl_UserInfo 
		  where vcTel=vcUserName

		  if vcCustNO is null then
			select vcUserID into vcCustNO,
				  vcPsw into vcPasswordCheck
			from BBN_UserInfo 
			where vcUserID=vcUserName
		  end if;
		    
		  if vcCustNO is null then
			select vcDoorNo into vcCustNO,
				  vcPsw into vcPasswordCheck
			from DTV_UserInfo 
			where vcDoorNo=vcUserName
	      end if;
     end if;
else
	if vcCustType='单位用户' then
		 select a.vcCustNO into vcCustNO,
			   a.vcPsw into vcPasswordCheck
		 from BOSS_Custom a
		 inner join jfgl_custinfo b on a.vcCustNO=b.vcAccNo and a.vcCustType='单位用户' and b.vcDeptNo=vcUserName
	end if;

	if vcUserName is not null and vcPassword is not null and vcPassword=vcPasswordCheck then
		select '1' "nRes",vcCustNO "vcCustomNO" from dual
	Else
		select '-1' "nRes",'' "vcCustomNO" from dual
	end if;
end if;


end;
 回复 点赞
lipt110 2014年01月07日
引用 12 楼 yupeigu 的回复:
[quote=引用 11 楼 lipt110 的回复:] [quote=引用 10 楼 yupeigu 的回复:] [quote=引用 9 楼 lipt110 的回复:] [quote=引用 7 楼 ap0405140 的回复:] [quote=引用 3 楼 lipt110 的回复:] 还是有错误，你试过了吗
在Oracle 10g环境测试正常.[/quote] 肯定有错误啊，有的变量还带有@没加；什么的 PROCEDURE RADIUS.WEB_LOGIN 编译错误 错误：PLS-00103: Encountered the symbol ";" when expecting one of the following: if 行：54 文本：end ; 这就是报的错误[/quote] 试试这个： 
create procedure Web_Login
(vcUserName varchar2(50),
 vcPassword varchar2(20),
 vcCustType varchar2(50)='普通用户')
is
begin
vcPasswordCheck varchar2(20);
vcCustNO varchar2(20);

if vcCustType='普通用户' then
begin
	 select vcCustNO into vcCustNO,
		   vcPsw into vcPasswordCheck
	 from Boss_Custom a
	 inner join BOSS_Associate b on a.vcCustNO=b.vcCustomNO and b.vcLink=vcUserName
 
	if vcCustNO is null then
	begin
	  select vcTel into vcCustNO,
			vc170Psw into vcPasswordCheck
	  from Jfgl_UserInfo 
	  where vcTel=vcUserName

	 if vcCustNO is null then
	   select vcUserID into vcCustNO,
			  vcPsw into vcPasswordCheck
		from BBN_UserInfo 
		where vcUserID=vcUserName
	 end if;
	    
	 if vcCustNO is null then
	   select vcDoorNo into vcCustNO,
			  vcPsw into vcPasswordCheck
		from DTV_UserInfo 
		where vcDoorNo=vcUserName
	 end if;
   end if;
end;
else
begin
	if vcCustType='单位用户' then
	begin
		 select a.vcCustNO into vcCustNO,
			   a.vcPsw into vcPasswordCheck
		 from BOSS_Custom a
		 inner join jfgl_custinfo b on a.vcCustNO=b.vcAccNo and a.vcCustType='单位用户' and b.vcDeptNo=vcUserName
	end;
	end if;

	if vcUserName is not null and vcPassword is not null and vcPassword=vcPasswordCheck then
	Begin
		select '1' "nRes",vcCustNO "vcCustomNO" from dual
	End;
	Else
	Begin
		select '-1' "nRes",'' "vcCustomNO" from dual
	End;
	end if;
end;


end;
[/quote] 虽然还是不行，但是非常感谢[/quote] 还是报哪个错吗[/quote] 反正有不少的错误，我改改的话就好点，不过还是那错误
 回复 点赞
阳泉酒家小当家 2014年01月07日
引用 11 楼 lipt110 的回复:
[quote=引用 10 楼 yupeigu 的回复:] [quote=引用 9 楼 lipt110 的回复:] [quote=引用 7 楼 ap0405140 的回复:] [quote=引用 3 楼 lipt110 的回复:] 还是有错误，你试过了吗
在Oracle 10g环境测试正常.[/quote] 肯定有错误啊，有的变量还带有@没加；什么的 PROCEDURE RADIUS.WEB_LOGIN 编译错误 错误：PLS-00103: Encountered the symbol ";" when expecting one of the following: if 行：54 文本：end ; 这就是报的错误[/quote] 试试这个： 
create procedure Web_Login
(vcUserName varchar2(50),
 vcPassword varchar2(20),
 vcCustType varchar2(50)='普通用户')
is
begin
vcPasswordCheck varchar2(20);
vcCustNO varchar2(20);

if vcCustType='普通用户' then
begin
	 select vcCustNO into vcCustNO,
		   vcPsw into vcPasswordCheck
	 from Boss_Custom a
	 inner join BOSS_Associate b on a.vcCustNO=b.vcCustomNO and b.vcLink=vcUserName
 
	if vcCustNO is null then
	begin
	  select vcTel into vcCustNO,
			vc170Psw into vcPasswordCheck
	  from Jfgl_UserInfo 
	  where vcTel=vcUserName

	 if vcCustNO is null then
	   select vcUserID into vcCustNO,
			  vcPsw into vcPasswordCheck
		from BBN_UserInfo 
		where vcUserID=vcUserName
	 end if;
	    
	 if vcCustNO is null then
	   select vcDoorNo into vcCustNO,
			  vcPsw into vcPasswordCheck
		from DTV_UserInfo 
		where vcDoorNo=vcUserName
	 end if;
   end if;
end;
else
begin
	if vcCustType='单位用户' then
	begin
		 select a.vcCustNO into vcCustNO,
			   a.vcPsw into vcPasswordCheck
		 from BOSS_Custom a
		 inner join jfgl_custinfo b on a.vcCustNO=b.vcAccNo and a.vcCustType='单位用户' and b.vcDeptNo=vcUserName
	end;
	end if;

	if vcUserName is not null and vcPassword is not null and vcPassword=vcPasswordCheck then
	Begin
		select '1' "nRes",vcCustNO "vcCustomNO" from dual
	End;
	Else
	Begin
		select '-1' "nRes",'' "vcCustomNO" from dual
	End;
	end if;
end;


end;
[/quote] 虽然还是不行，但是非常感谢[/quote] 还是报哪个错吗
 回复 点赞
lipt110 2014年01月07日
引用 10 楼 yupeigu 的回复:
[quote=引用 9 楼 lipt110 的回复:] [quote=引用 7 楼 ap0405140 的回复:] [quote=引用 3 楼 lipt110 的回复:] 还是有错误，你试过了吗
在Oracle 10g环境测试正常.[/quote] 肯定有错误啊，有的变量还带有@没加；什么的 PROCEDURE RADIUS.WEB_LOGIN 编译错误 错误：PLS-00103: Encountered the symbol ";" when expecting one of the following: if 行：54 文本：end ; 这就是报的错误[/quote] 试试这个： 
create procedure Web_Login
(vcUserName varchar2(50),
 vcPassword varchar2(20),
 vcCustType varchar2(50)='普通用户')
is
begin
vcPasswordCheck varchar2(20);
vcCustNO varchar2(20);

if vcCustType='普通用户' then
begin
	 select vcCustNO into vcCustNO,
		   vcPsw into vcPasswordCheck
	 from Boss_Custom a
	 inner join BOSS_Associate b on a.vcCustNO=b.vcCustomNO and b.vcLink=vcUserName
 
	if vcCustNO is null then
	begin
	  select vcTel into vcCustNO,
			vc170Psw into vcPasswordCheck
	  from Jfgl_UserInfo 
	  where vcTel=vcUserName

	 if vcCustNO is null then
	   select vcUserID into vcCustNO,
			  vcPsw into vcPasswordCheck
		from BBN_UserInfo 
		where vcUserID=vcUserName
	 end if;
	    
	 if vcCustNO is null then
	   select vcDoorNo into vcCustNO,
			  vcPsw into vcPasswordCheck
		from DTV_UserInfo 
		where vcDoorNo=vcUserName
	 end if;
   end if;
end;
else
begin
	if vcCustType='单位用户' then
	begin
		 select a.vcCustNO into vcCustNO,
			   a.vcPsw into vcPasswordCheck
		 from BOSS_Custom a
		 inner join jfgl_custinfo b on a.vcCustNO=b.vcAccNo and a.vcCustType='单位用户' and b.vcDeptNo=vcUserName
	end;
	end if;

	if vcUserName is not null and vcPassword is not null and vcPassword=vcPasswordCheck then
	Begin
		select '1' "nRes",vcCustNO "vcCustomNO" from dual
	End;
	Else
	Begin
		select '-1' "nRes",'' "vcCustomNO" from dual
	End;
	end if;
end;


end;
[/quote] 虽然还是不行，但是非常感谢
 回复 点赞
阳泉酒家小当家 2014年01月07日
引用 9 楼 lipt110 的回复:
[quote=引用 7 楼 ap0405140 的回复:] [quote=引用 3 楼 lipt110 的回复:] 还是有错误，你试过了吗
在Oracle 10g环境测试正常.[/quote] 肯定有错误啊，有的变量还带有@没加；什么的 PROCEDURE RADIUS.WEB_LOGIN 编译错误 错误：PLS-00103: Encountered the symbol ";" when expecting one of the following: if 行：54 文本：end ; 这就是报的错误[/quote] 试试这个： 
create procedure Web_Login
(vcUserName varchar2(50),
 vcPassword varchar2(20),
 vcCustType varchar2(50)='普通用户')
is
begin
vcPasswordCheck varchar2(20);
vcCustNO varchar2(20);

if vcCustType='普通用户' then
begin
	 select vcCustNO into vcCustNO,
		   vcPsw into vcPasswordCheck
	 from Boss_Custom a
	 inner join BOSS_Associate b on a.vcCustNO=b.vcCustomNO and b.vcLink=vcUserName
 
	if vcCustNO is null then
	begin
	  select vcTel into vcCustNO,
			vc170Psw into vcPasswordCheck
	  from Jfgl_UserInfo 
	  where vcTel=vcUserName

	 if vcCustNO is null then
	   select vcUserID into vcCustNO,
			  vcPsw into vcPasswordCheck
		from BBN_UserInfo 
		where vcUserID=vcUserName
	 end if;
	    
	 if vcCustNO is null then
	   select vcDoorNo into vcCustNO,
			  vcPsw into vcPasswordCheck
		from DTV_UserInfo 
		where vcDoorNo=vcUserName
	 end if;
   end if;
end;
else
begin
	if vcCustType='单位用户' then
	begin
		 select a.vcCustNO into vcCustNO,
			   a.vcPsw into vcPasswordCheck
		 from BOSS_Custom a
		 inner join jfgl_custinfo b on a.vcCustNO=b.vcAccNo and a.vcCustType='单位用户' and b.vcDeptNo=vcUserName
	end;
	end if;

	if vcUserName is not null and vcPassword is not null and vcPassword=vcPasswordCheck then
	Begin
		select '1' "nRes",vcCustNO "vcCustomNO" from dual
	End;
	Else
	Begin
		select '-1' "nRes",'' "vcCustomNO" from dual
	End;
	end if;
end;


end;
 回复 点赞
lipt110 2014年01月07日
引用 7 楼 ap0405140 的回复:
[quote=引用 3 楼 lipt110 的回复:] 还是有错误，你试过了吗
在Oracle 10g环境测试正常.[/quote] 肯定有错误啊，有的变量还带有@没加；什么的 PROCEDURE RADIUS.WEB_LOGIN 编译错误 错误：PLS-00103: Encountered the symbol ";" when expecting one of the following: if 行：54 文本：end ; 这就是报的错误
 回复 点赞
阳泉酒家小当家 2014年01月07日
试试这个： 
create procedure Web_Login
(vcUserName varchar2(50),
 vcPassword varchar2(20),
 vcCustType varchar2(50)='普通用户')
is
begin
vcPasswordCheck varchar2(20);
vcCustNO varchar2(20);

if vcCustType='普通用户' then
begin
	 select vcCustNO into vcCustNO,
		   vcPsw into vcPasswordCheck
	 from Boss_Custom a
	 inner join BOSS_Associate b on a.vcCustNO=b.vcCustomNO and b.vcLink=vcUserName
 
	if vcCustNO is null then
	begin
	  select vcTel into vcCustNO,
			vc170Psw into vcPasswordCheck
	  from Jfgl_UserInfo 
	  where vcTel=vcUserName

	 if vcCustNO is null then
	   select vcUserID into vcCustNO,
			  vcPsw into vcPasswordCheck
		from BBN_UserInfo 
		where vcUserID=vcUserName
	 end if;
	    
	 if vcCustNO is null then
	   select vcDoorNo into vcCustNO,
			  vcPsw into vcPasswordCheck
		from DTV_UserInfo 
		where vcDoorNo=@vcUserName
	 end if;
   end if;
end;
else
begin
	if vcCustType='单位用户' then
	begin
		 select a.vcCustNO into vcCustNO,
			   a.vcPsw into vcPasswordCheck
		 from BOSS_Custom a
		 inner join jfgl_custinfo b on a.vcCustNO=b.vcAccNo and a.vcCustType='单位用户' and b.vcDeptNo=vcUserName
	end;
	end if;

	if vcUserName is not null and vcPassword is not null and vcPassword=vcPasswordCheck then
	Begin
		select '1' "nRes",vcCustNO "vcCustomNO" from dual
	End;
	Else
	Begin
		select '-1' "nRes",'' "vcCustomNO" from dual
	End;
	end if;
end;


end;
 回复 点赞
唐诗三百首 2014年01月07日
引用 3 楼 lipt110 的回复:
还是有错误，你试过了吗
在Oracle 10g环境测试正常.
 回复 点赞
lipt110 2014年01月07日
引用 4 楼 yupeigu 的回复:
[quote=引用 3 楼 lipt110 的回复:] 还是有错误，你试过了吗
报的什么错误[/quote]就是最后一行报错
 回复 点赞
lipt110 2014年01月07日
我改了改，报错 PROCEDURE ETCOM.WEB_LOGIN 编译错误 错误：PLS-00103: 出现符号 ";"在需要下列之一时： if 行：54 文本：end ;
回复 点赞
阳泉酒家小当家 2014年01月07日
引用 3 楼 lipt110 的回复:
还是有错误，你试过了吗
报的什么错误
 回复 点赞
lipt110 2014年01月07日
还是有错误，你试过了吗
 回复 点赞
唐诗三百首 2014年01月07日
try this, 

create procedure Web_Login
(vcUserName varchar2(50),
 vcPassword varchar2(20),
 vcCustType varchar2(50)='普通用户')
is
begin
vcPasswordCheck varchar2(20);
vcCustNO varchar2(20);

if vcCustType='普通用户' then
begin
select vcCustNO into vcCustNO,
       vcPsw into vcPasswordCheck
 from Boss_Custom a
 inner join BOSS_Associate b on a.vcCustNO=b.vcCustomNO and b.vcLink=vcUserName
 
if vcCustNO is null then
begin
 select vcTel into vcCustNO,
        vc170Psw into vcPasswordCheck
  from Jfgl_UserInfo 
  where vcTel=vcUserName

 if vcCustNO is null then
   select vcUserID into vcCustNO,
          vcPsw into vcPasswordCheck
    from BBN_UserInfo 
    where vcUserID=vcUserName
 end if;
    
 if vcCustNO is null then
   select vcDoorNo into vcCustNO,
          vcPsw into vcPasswordCheck
    from DTV_UserInfo 
    where vcDoorNo=@vcUserName
 end if;
end;
end	if;
end;
else
if vcCustType='单位用户' then
select a.vcCustNO into vcCustNO,
       a.vcPsw into vcPasswordCheck
 from BOSS_Custom a
 inner join jfgl_custinfo b on a.vcCustNO=b.vcAccNo and a.vcCustType='单位用户' and b.vcDeptNo=vcUserName
end if;

if vcUserName is not null and vcPassword is not null and vcPassword=vcPasswordCheck then
Begin
select '1' "nRes",vcCustNO "vcCustomNO" from dual
End
Else
Begin
select '-1' "nRes",'' "vcCustomNO" from dual
End
end if;

end;
回复 点赞
阳泉酒家小当家 2014年01月07日
呵呵，可以贴到 oracle板块去问问。

 回复 点赞
发动态
发帖子
疑难问题
创建于2007-09-28

9308

社区成员

12.1w+

社区内容

MS-SQL Server 疑难问题
社区公告
暂无公告