执行job和停止job

## 创建job

```sql
declare
    v_job number;
    v_what varchar2(1000);
begin
    v_what := '
begin
    execute immediate ''truncate table table_name'';
    commit;
exception
    when others then
        rollback;
end;
';

dbms_job.submit(
        job => v_job,
        what => v_what,
        next_date => trunc(sysdate)+1+6/24,
        interval => 'trunc(sysdate)+1+6/24');
    commit;
    dbms_output.put_line('job_id: '||v_job);
end;
```

## 执行job

```sql
declare
    v_job number := 11;
begin
    dbms_job.broken(v_job,false);
    dbms_job.next_date(v_job,sysdate+1/24/60);  --1分钟后执行job
    commit;
    for rec in (select * from user_jobs where job = v_job) loop
        dbms_output.put_line('job: '||rec.job||chr(10)||
                             'sql: '||rec.what||CHR(10)||
                             'this date: '||to_char(rec.this_date,'yyyy-mm-dd hh24:mi:ss')||chr(10)||
                             'next date: '||to_char(rec.next_date,'yyyy-mm-dd hh24:mi:ss')||CHR(10)
                        );
    end loop;
end;
```

## 停止job
```sql
declare
    v_job number := 11;
    v_cnt number;
    v_sid number;
    v_ser number;
begin
    select max(sid) into v_sid  from dba_jobs_running where job = v_job;
    if nvl(v_sid,0) = 0 then
       return;
    end if;
    dbms_job.broken(v_job,true);
    commit;
    select serial#  into v_ser from v$session where sid = v_sid;
    execute immediate 'alter system kill session '''||v_sid||','||v_ser||''' immediate';
end;
```

## 更改频率

```sql
declare
    v_job number := 11;
begin
    dbms_job.interval(v_job,'trunc(sysdate+1)+1/24');
    dbms_job.next_date(v_job,trunc(sysdate+1)+1/24);
    commit;
    for rec in (select * from user_jobs where job = v_job) loop
        dbms_output.put_line('job: '||rec.job||chr(10)||
                             'sql: '||rec.what||CHR(10)||
                             'this date: '||to_char(rec.this_date,'yyyy-mm-dd hh24:mi:ss')||chr(10)||
                             'next date: '||to_char(rec.next_date,'yyyy-mm-dd hh24:mi:ss')||CHR(10)
                        );
    end loop;
end;
```

## 更改内容

```sql
declare
    v_job number := 11;
begin
    dbms_job.what(v_job,'truncate table table_name');
    commit;
    for rec in (select * from user_jobs where job = v_job) loop
        dbms_output.put_line('job: '||rec.job||chr(10)||
                             'sql: '||rec.what||CHR(10)||
                             'this date: '||to_char(rec.this_date,'yyyy-mm-dd hh24:mi:ss')||chr(10)||
                             'next date: '||to_char(rec.next_date,'yyyy-mm-dd hh24:mi:ss')||CHR(10)
                        );
    end loop;
end;
```