PostgreSQL存储过程转换SQLServer的

发布网友发布时间：2022-04-23 16:13

共1个回答

热心网友时间：2022-05-02 03:35

因sql server2000中不允许调用当前时间的函数getdate(),也不允许非表变量之外的update、insert、delete操作，故这里修改为存储过程，其中原来的return值改用输出参数"@Result int output"来实现，具体如下：

CREATE PROCEDURE web_login(@web_username varchar(50), @web_pwd varchar(50), @web_ip varchar(50),@Result int output)
AS
BEGIN
declare
@len int, -- -2 fail -3 lockip
@u_locktime varchar(10),
@u_cleartime varchar(50),
@u_count int,
@user_id int,
@log_id int,
@cal int

set @len=0

select @u_count=config_value from as_all_config where config_item='U_AllowCount'
select @u_cleartime=config_value from as_all_config where config_item='U_LimitDay'
set @cal=@u_cleartime
select @u_locktime=config_value from as_all_config where config_item='U_Locktime'
--判断用户是否已经锁定
select top 1 @len=serialno from web_user_log where login_id =@web_username and last_ip=@web_ip and last_lock_time<getdate() and last_count= @u_count
if @len>0
begin
insert into as_log(log_fromtype, log_type,log_text, log_level) values(8,4,'用户 '+@web_username+'登录失败 ip锁定',1)
set @Result=-3
RETURN
end
--判断用户失败次数
select top 1 @len=last_count,@log_id=serialno from web_user_log where login_id =@web_username and last_ip=@web_ip and convert(varchar(10),last_fail_time,120)=convert(varchar(10),getdate(),120) order by last_fail_time desc
if @len>3
begin
update web_user_log set last_lock_time=getdate() where serialno=@log_id
insert into as_log(log_fromtype, log_type,log_text, log_level) values(8,4,'用户 '+@web_username+'登录失败 ip锁定',1)
set @Result=-3
RETURN
end
--正常操作
select top 1 @len=last_count, @log_id=serialno from web_user_log where login_id =@web_username and last_ip=@web_ip and convert(varchar(10),last_fail_time,120)=convert(varchar(10),getdate(),120) order by last_fail_time desc
select top 1 @user_id=serialno from web_user where login_id = @web_username and login_pwd=@web_pwd
delete from web_user_log where login_id =@web_username and last_lock_time<=convert(varchar(10),dateadd(day,-@cal,getdate()),120)
if @user_id>0
begin
insert into as_log( log_fromtype, log_type, log_text, log_level) values(8,4,'用户 '+@web_username+'登录成功',1)
set @Result=@user_id
RETURN
end
else if (@len is null or @len=0)
insert into web_user_log(last_count, last_ip, login_pwd, login_id) values(1,@web_ip,@web_pwd,@web_username)
else
update web_user_log set last_count=last_count+1 where serialno=@log_id

insert into as_log(log_fromtype, log_type, log_text, log_level)values(8,4,'用户 '+@web_username+'登录失败',1)
set @Result=-2
RETURN
END
go

--更改当前数据库中对象的所有者:
sp_changeobjectowner 'web_login','ema_user'
go