大概如下:(login_stat='Y'登录成功) create or replace procedure usr_test(usr_id varchar2, login_stat out varchar2) is l_usr_id varchar2(1000); l_stat varchar2(5); l_last_date date; l_current_date date; begin select active into l_stat from users where u_id = usr_id; if l_stat = 0 then login_stat := 'N'; dbms_output.put_line('User status is inactive,can not login'); else select max(l_time) into l_last_date from login where l_id = usr_id; select sysdate into l_current_date from dual; if l_current_date - l_last_date > 60 then update users set active = 0 where u_id = usr_id; login_stat := 'N'; dbms_output.put_line('User status is inactive,can not login'); else login_stat := 'Y'; dbms_output.put_line('login successfully'); end if;
end if;end usr_test;
初步想法,定期遍历user表的数据。 create or replace procedure XX as CURSOR cur_l is select l_id,l_time from login ; begin for r_cur_l in cur_l loop exit when cur_l%notfound or cur_l%notfound is null; if sysdate -to_date( r_cur_l.l_time,'yyyymmdd')>60 then update users set active=0 where u_id=r_cur_l.l_id; end if; end loop; end;如果可以直接获取l_id,也可以直接update了。
update users t set active=0 where active=1 and not exists(select 1 from login where i_id=t.u_id and l_time>sysdate-60); commit; 需要用存储过程的话就加上begin end;
弄个SQL作业一周执行一次就可以了
看看这个能满足你的要求吗?create or replace procedure my_update_active is v_active varchar2(3) := '0'; --状态位 v_interval number; --最长登录间隔时间 v_id number; --用户id usersrecord users%rowtype; cursor c_1 is --定义一个游标循环所有用户 select * from users; begin open c_1; loop fetch c_1 into usersrecord; v_id:=usersrecord.u_id; select max(l_time) into v_interval from (select l_id, l_time - lag(l_time, 1, null) over(order by l_time) as l_time from (select l_id, l_time from login where l_id = v_id order by l_time)) group by l_id; if v_interval > 60 then update users set active = v_active where u_id = v_id; end if; exit when c_1%notfound; --计算用户相邻两次的最大登录时间,并赋值给v_insterval end loop; close c_1; end;
create or replace procedure usr_test(usr_id varchar2, login_stat out varchar2) is
l_usr_id varchar2(1000);
l_stat varchar2(5);
l_last_date date;
l_current_date date;
begin
select active into l_stat from users where u_id = usr_id;
if l_stat = 0 then
login_stat := 'N';
dbms_output.put_line('User status is inactive,can not login');
else
select max(l_time) into l_last_date from login where l_id = usr_id;
select sysdate into l_current_date from dual;
if l_current_date - l_last_date > 60 then
update users set active = 0 where u_id = usr_id;
login_stat := 'N';
dbms_output.put_line('User status is inactive,can not login');
else
login_stat := 'Y';
dbms_output.put_line('login successfully');
end if;
end if;end usr_test;
create or replace procedure XX
as
CURSOR cur_l is
select l_id,l_time from login ;
begin
for r_cur_l in cur_l loop
exit when cur_l%notfound or cur_l%notfound is null;
if sysdate -to_date( r_cur_l.l_time,'yyyymmdd')>60 then
update users set active=0 where u_id=r_cur_l.l_id;
end if;
end loop;
end;如果可以直接获取l_id,也可以直接update了。
where active=1
and not exists(select 1 from login where i_id=t.u_id and l_time>sysdate-60);
commit;
需要用存储过程的话就加上begin end;
v_active varchar2(3) := '0'; --状态位
v_interval number; --最长登录间隔时间
v_id number; --用户id
usersrecord users%rowtype;
cursor c_1 is --定义一个游标循环所有用户
select * from users;
begin
open c_1;
loop
fetch c_1
into usersrecord;
v_id:=usersrecord.u_id;
select max(l_time)
into v_interval
from (select l_id,
l_time - lag(l_time, 1, null) over(order by l_time) as l_time
from (select l_id, l_time
from login
where l_id = v_id
order by l_time))
group by l_id;
if v_interval > 60 then
update users set active = v_active where u_id = v_id;
end if;
exit when c_1%notfound;
--计算用户相邻两次的最大登录时间,并赋值给v_insterval
end loop;
close c_1;
end;