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
...全文
164 14 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
LongRui888 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] 反正有不少的错误,我改改的话就好点,不过还是那错误
LongRui888 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] 虽然还是不行,但是非常感谢
LongRui888 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 ; 这就是报的错误
LongRui888 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 ;
LongRui888 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;
LongRui888 2014-01-07
  • 打赏
  • 举报
回复
呵呵,可以贴到 oracle板块去问问。

内容概要:本书《Deep Reinforcement Learning with Guaranteed Performance》探讨了基于李雅普诺夫方法的深度强化学习及其在非线性系统最优控制中的应用。书中提出了一种近似最优自适应控制方法,结合泰勒展开、神经网络、估计器设计及滑模控制思想,解决了不同场景下的跟踪控制问题。该方法不仅保证了性能指标的渐近收敛,还确保了跟踪误差的渐近收敛至零。此外,书中还涉及了执行器饱和、冗余解析等问题,并提出了新的冗余解析方法,验证了所提方法的有效性和优越性。 适合人群:研究生及以上学历的研究人员,特别是从事自适应/最优控制、机器人学和动态神经网络领域的学术界和工业界研究人员。 使用场景及目标:①研究非线性系统的最优控制问题,特别是在存在输入约束和系统动力学的情况下;②解决带有参数不确定性的线性和非线性系统的跟踪控制问题;③探索基于李雅普诺夫方法的深度强化学习在非线性系统控制中的应用;④设计和验证针对冗余机械臂的新型冗余解析方法。 其他说明:本书分为七章,每章内容相对独立,便于读者理解。书中不仅提供了理论分析,还通过实际应用(如欠驱动船舶、冗余机械臂)验证了所提方法的有效性。此外,作者鼓励读者通过仿真和实验进一步验证书中提出的理论和技术。

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧