Oracle # 创建存储过程+Job 详细步骤

淡淡的烟草味﹌ 2023-05-29 03:06 41阅读 0赞

## 第一步：创建表 ##

本例子中使用的语句为：

CREATE TABLE "PES_IFS_TJBI"."BI_PS01_INPROJECT_NEW" 
       (	"PROBLEM_ADD_NUM" NUMBER, 
    	"V1_ADD_NUM" NUMBER, 
    	"REALTIME_NUM" NUMBER, 
    	"STATION_NAME" VARCHAR2(200 BYTE), 
    	"CARTYPE" VARCHAR2(20 BYTE), 
    	"PRODUCT_DATE" VARCHAR2(20 BYTE)
       ) SEGMENT CREATION DEFERRED 
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
     NOCOMPRESS LOGGING
      TABLESPACE "USERS" ;
    
       COMMENT ON COLUMN "PES_IFS_TJBI"."BI_PS01_INPROJECT_NEW"."PROBLEM_ADD_NUM" IS '问题数和';
       COMMENT ON COLUMN "PES_IFS_TJBI"."BI_PS01_INPROJECT_NEW"."V1_ADD_NUM" IS 'V1问题数和';
       COMMENT ON COLUMN "PES_IFS_TJBI"."BI_PS01_INPROJECT_NEW"."REALTIME_NUM" IS '车辆数';
       COMMENT ON COLUMN "PES_IFS_TJBI"."BI_PS01_INPROJECT_NEW"."STATION_NAME" IS '工位';
       COMMENT ON COLUMN "PES_IFS_TJBI"."BI_PS01_INPROJECT_NEW"."CARTYPE" IS '车型';
       COMMENT ON COLUMN "PES_IFS_TJBI"."BI_PS01_INPROJECT_NEW"."PRODUCT_DATE" IS '生产日期';

## 第二步：创建存储过程 ##

基本语句示例：

create or replace PROCEDURE 存储过程名称
    IS
    BEGIN  
    --执行的语句代码编写位置
    COMMIT;
    exception
    WHEN others THEN
      ROLLBACK;
    END;

本示例代码为：

create or replace PROCEDURE PRO_Add_BI_PS01_INPROJECT_NEW
    --同步BI_PS01_INPROJECT_HISTORY表中的数据根据特殊的规则至BI_PS01_INPROJECT_NEW表中
    IS
    BEGIN 
    
    --将查询的数据结果新增至BI_PS01_INPROJECT_NEW表中 Start
    insert into BI_PS01_INPROJECT_NEW(
    PROBLEM_ADD_NUM,
    V1_ADD_NUM,
    REALTIME_NUM,
    STATION_NAME,
    CARTYPE,
    PRODUCT_DATE
    )
    --根据规则查询数据 Start
    select
    sum(PROBLEM_NUM)as PROBLEM_ADD_NUM,
    sum(V1_PROBLEM_NUM)as V1_ADD_NUM,
    REALTIME_NUM,STATION_NAME,CARTYPE,PRODUCT_DATE
    from 
    BI_PS01_INPROJECT_HISTORY
    group by 
    STATION_NAME,REALTIME_NUM,PRODUCT_DATE,CARTYPE
    ORDER BY PRODUCT_DATE desc,CARTYPE;
    --根据规则查询数据 End
    
    --将查询的数据结果新增至BI_PS01_INPROJECT_NEW表中 End
    
    COMMIT;
    exception
    WHEN others THEN
      ROLLBACK;
    END;

编写完成，点击执行——> 提交按钮。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MzEzNTc1_size_16_color_FFFFFF_t_70][]

刷新过程，显示新建立的存储过程：

![20191102110052403.png][]

特殊说明，如何区分存储过程有没有建立成功

![2019110211042364.png][]

![20191102110432239.png][]

## 第三步：创建Job ##

--创建Job--禁用 Start
     declare jobno number;
     begin
     dbms_job.submit(jobno,'PRO_ADD_BI_PS01_INPROJECT_NEW;',to_date('2019-11-01 11:20:00', 'yyyy-mm-dd hh24:mi:ss'),'SYSDATE + 1/24');
     end;
     --创建Job--禁用 End（每小时运行一次）
    
    --创建Job Start
    declare jobno number;
    begin
         dbms_job.submit(jobno,'PRO_ADD_BI_PS01_INPROJECT_NEW;',to_date('2019-11-02 13:20:00', 'yyyy-mm-dd hh24:mi:ss'),'sysdate+1/60');--每天86400秒钟，即一秒钟运行prc_name过程一次
        commit;
    end;
     --创建Job End（
     
     --查询Job Start
     select job, what, next_date, next_sec, sysdate, failures, broken,interval
    from user_jobs a;
     --查询Job End
     
     --查询Job汉化解释 Start
     select job, what as 存储过程名称, next_date as 下一次执行日期, next_sec as 下一次执行时间, sysdate as 当前日期, failures as 失败次数, broken,interval
    from user_jobs a;
     --查询Job汉化解释 End
     
     --删除Job Start
    begin
     dbms_job.remove(63);
    end;
    --删除Job End
     
    --修改Job Start
    begin
      dbms_job.change(63 ,'PRO_ADD_BI_PS01_INPROJECT_NEW;', sysdate,'sysdate+2/(24*60)' );
      commit;
    --修改Job End

目前已经完成存储过程以及job的建立，请耐心等到验证结果吧！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MzEzNTc1_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191102110013168.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2MzEzNTc1,size_16,color_FFFFFF,t_70
[20191102110052403.png]: https://img-blog.csdnimg.cn/20191102110052403.png
[2019110211042364.png]: https://img-blog.csdnimg.cn/2019110211042364.png
[20191102110432239.png]: https://img-blog.csdnimg.cn/20191102110432239.png