oracle带参数jop,【oracle】job带输入固定参数的存储过程

—创建表

https://www.cndba.cn/debimeng/article/3182

create table test_job(j_id int,j_name varchar2(30),j_phone int);

—查询表

https://www.cndba.cn/debimeng/article/3182

select * from test_job;

—创建存储过程https://www.cndba.cn/debimeng/article/3182https://www.cndba.cn/debimeng/article/3182

create or replace procedure p_test(v_id in test_job.j_id%type,

v_name in test_job.j_name%type,

v_phone in test_job.j_phone%type) as

begin

insert into test_job values (v_id, v_name, v_phone);

commit;

end;

—创建job任务

declare

job number;

BEGIN

DBMS_JOB.SUBMIT(

JOB => job, /*自动生成JOB_ID*/

WHAT => 'p_test(''1'',''Wangwu'',''123456'');', /*需要执行的存储过程名称或SQL语句,带参数的话使用四个单引号括起来*/

NEXT_DATE => sysdate+3/(24*60), /*初次执行时间-下一个3分钟*/

INTERVAL => 'trunc(sysdate,''mi'')+1/(24*60)' /*每隔1分钟执行一次*/

);

commit;

end;

—查询job

select * from user_job;

https://www.cndba.cn/debimeng/article/3182

select * from test_job;

—停止jobhttps://www.cndba.cn/debimeng/article/3182

https://www.cndba.cn/debimeng/article/3182

exec dbms_job.broken(1,true); /*1为job的id*/

版权声明：本文为博主原创文章，未经博主允许不得转载。

job输入参数 参数四个单引号