1.创建测试表
CREATE
TABLE
t_test
(
ID DATE
PRIMARY
KEY
,
VALUE
NUMBER
);
2.创建存储过程
create
or
replace
procedure
pro_test(v
IN
NUMBER
)
is
begin
insert
into
t_test(id,VALUE)
values
(sysdate,v);
end
;
/
3.创建JOB
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
JOB_NAME
=>
'
HUYVANPULL_TEST_JOB1
'
,
JOB_TYPE
=>
'
STORED_PROCEDURE
'
,
JOB_ACTION
=>
'
PRO_TEST
'
,
NUMBER_OF_ARGUMENTS
=>
1
,
REPEAT_INTERVAL
=>
'
FREQ=MINUTELY;INTERVAL=1
'
,
ENABLED
=>
FALSE,
START_DATE
=>
SYSDATE,
COMMENTS
=>
'
HUYVANPULL-Test
'
);
END
;
/
如果没有创建权限,赋权限
Grant
Create
Job
To
em_test;
4.设置存储JOB的存储过程的参数
BEGIN
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
JOB_NAME
=>
'
HUYVANPULL_TEST_JOB1
'
,
ARGUMENT_POSITION
=>
1
,
ARGUMENT_VALUE
=>
0
);
END
;
/
5.启动JOB
BEGIN
DBMS_SCHEDULER.enable(
'
HUYVANPULL_TEST_JOB1
'
);
END
;
/
CREATE
TABLE
t_test( ID DATE
PRIMARY
KEY
, VALUE
NUMBER
);
2.创建存储过程
create
or
replace
procedure
pro_test(v
IN
NUMBER
)
is
begin
insert
into
t_test(id,VALUE)
values
(sysdate,v);
end
;
/
3.创建JOB
BEGIN
DBMS_SCHEDULER.CREATE_JOB( JOB_NAME
=>
'
HUYVANPULL_TEST_JOB1
'
, JOB_TYPE
=>
'
STORED_PROCEDURE
'
, JOB_ACTION
=>
'
PRO_TEST
'
, NUMBER_OF_ARGUMENTS
=>
1
, REPEAT_INTERVAL
=>
'
FREQ=MINUTELY;INTERVAL=1
'
, ENABLED
=>
FALSE, START_DATE
=>
SYSDATE, COMMENTS
=>
'
HUYVANPULL-Test
'
);
END
;
/
Grant
Create
Job
To
em_test;
4.设置存储JOB的存储过程的参数
BEGIN
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE( JOB_NAME
=>
'
HUYVANPULL_TEST_JOB1
'
, ARGUMENT_POSITION
=>
1
, ARGUMENT_VALUE
=>
0
);
END
;
/
5.启动JOB
BEGIN
DBMS_SCHEDULER.enable(
'
HUYVANPULL_TEST_JOB1
'
);
END
;
/
6.查询JOB
SELECT
*
FROM
USER_SCHEDULER_JOBS;
7.停用JOB
BEGIN
DBMS_SCHEDULER.disable(
'
HUYVANPULL_TEST_JOB1
'
);
END
;
/
8.删除JOB
BEGIN
DBMS_SCHEDULER.drop_job(
'
HUYVANPULL_TEST_JOB1
'
);
END
;
/
