第一步:创建表
本例子中使用的语句为:
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;
编写完成,点击执行——> 提交按钮。
刷新过程,显示新建立的存储过程:
特殊说明,如何区分存储过程有没有建立成功
第三步:创建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的建立,请耐心等到验证结果吧!