--프로시저
create or REPLACE PROCEDURE leavemember
(
mu_idx in number,
mnickname in NVARCHAR2,
mleave_date in date,
mgithub_url NVARCHAR2,
mprofilephoto NVARCHAR2
)
as
begin
if mleave_date = sysdate
then
delete from member where u_idx =mu_idx;
delete from user_detail where u_idx =mu_idx;
end if;
commit;
end
-스케줄링
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'sleavemember',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN leavemember; END;',
start_date => systimestamp ,
repeat_interval => 'FREQ=secondly; interval=1'
--repeat_interval => 'sysdate+7+1/86400'
);
end;
EXECUTE DBMS_SCHEDULER.ENABLE('sleavemember');
EXECUTE DBMS_SCHEDULER.DISABLE('sleavemember');
이렇게 만들어봤는데 모드 생성은 되는데 왜 데이터가 1초마다 사라지지는 않을까요 ㅠㅠ
답변감사합니다 그런데 프로시저를 이런식으로 수정하면
create or REPLACE PROCEDURE leavemember
(
mu_idx number,
mleave_date date
)
as
begin
if mleave_date = to_char(sysdate,'YY/MM/DD')
then
delete from member where u_idx = mu_idx;
delete from user_detail where u_idx = mu_idx;
end if;
commit;
end;
입력값을 받는 걸까요 ?? 그리고 u_idx 가 해당하는 값만 삭제하고 싶은데 (
mu_idx number,
mleave_date date
) 이런식을 선언하는 것이 아닌ㄴ가요 ㅜㅜㅜ
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'sleavemember',
job_type => 'STORED_PROCEDURE',
job_action => 'ADMIN.LEAVEMEMBER',
start_date => systimestamp,
-- repeat_interval => 'TRUNC(SYSDATE+1)'
repeat_interval => 'FREQ=secondly; INTERVAL=1'
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is : ' || to_char(10));
end;
select * from member;
EXECUTE DBMS_SCHEDULER.ENABLE('sleavemember');
EXECUTE DBMS_SCHEDULER.DISABLE('sleavemember');
스케줄링에서 이런식으로 실행하였습니다. ㅜㅜ