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
...全文
166 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板块去问问。

SQLServer存储过程转为oracle存储过程的工具
可以将SQL Server存储过程转为oracle存储过程的工具
SqlServer2008访问Oracle数据库
包括链接服务器环境安装、链接服务器创建、远程建表、修改表，增删改查数据，创建、执行带参与无参存储过程的方法等，资料为作者参考网络资料整理后亲测。 附带ODAC112040Xcopy_64bit安装包，ODAC。
SQL操作基础教程
- MySQL, PostgreSQL, Oracle, Microsoft SQL Server等数据库的区别与特性。 - 如何在不同数据库系统间迁移SQL语句。 通过这个SQL操作基础教程，学习者将能够掌握SQL语言的基础技能，并逐步提升到高级查询和管理...
SQLserver存储过程转化为oracle存储过程
SQLserver SP --> oracle SP str >> TO_CHAR 定义、赋值 begin declare @aaa varchar(max) set @aaa='ssss' >>> declare aaa varchar2(4000) begin aaa := '...
sqlserver 存储过程 转oracle存储过程,SqlServer存储过程转换成Oracle储存过程语法常见问题...
1. top order by 转换成 rownum order by 的问题 (子查询实现)同级情况下的优先处理顺序：sqlserver: 先order by 再toporacle: 先rownum 再 order by2. 已有数据的字段类型不匹配，通过下列语句修改。alter table ...
疑难问题

22,300

社区成员

121,734

社区内容

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

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章