Oracle存储过程开发 博客分类： SQL技术栈 Oracle数据库存储过程Job定时任务
程序员文章站
2024-03-01 20:20:28
...
Oracle数据库存储过程开发：
一个完整的存储过程开发包含如下流程：
1.存储过程包头Package定义：一组相关过程、函数、变量、常量#SinaEditor_Temp_FontName、类型和游标等PL/SQL程序设计元素的组合
包头定义：
create or replace package package_namae is --package_namae 包头名称
procedure procedure_name; --procedure_name 存储过程名称
procedure sp_write_log( --sp_write_log 存储过程日志记录
job_name varchar, --job_name Job名称
extraction_timepoint TIMESTAMP,--extraction_timepoint 存储抽取时间
ins_start_timestamp TIMESTAMP,--ins_start_timestamp Job执行开始时间
ins_finish_timestamp TIMESTAMP, --ins_finish_timestamp Job执行结束时间
record_count number, --record_count记录统计数
job_type char, --job_type Job类型
status char, --status Job运行状态
error varchar2); --error Job运行失败信息
end package_namae; --package_namae包头名称
2.存储过程包体PackageBody定义：是包定义部分的具体实现，它负责为包头中所声明子程序提供具体的实现，在包主体中还可以声明包的私有元素
create or replace package body package_namae is --package_namae 包头名称
procedure procedure_name as --procedure_name 存储过程名称
job_name varchar, --job_name Job名称
extraction_timepoint TIMESTAMP,--extraction_timepoint 存储抽取时间
ins_start_timestamp TIMESTAMP,--ins_start_timestamp Job执行开始时间
ins_finish_timestamp TIMESTAMP, --ins_finish_timestamp Job执行结束时间
record_count number, --record_count记录统计数
job_type char, --job_type Job类型
status char, --status Job运行状态
error varchar2); --error Job运行失败信息
begin
job_name := 'job_name'; --job_name Job名称
extraction_timepoint := null; --ins_start_timestamp Job执行开始时间
ins_start_timestamp := sysdate; ---ins_finish_timestamp Job执行结束时间
job_type := 'D'; --定义Job运行类型：D-daily H-Hourly 等等
--具体SQL脚本内容开始
-----------------------------SQL具体逻辑--------------------------
--具体SQL脚本内容结束
commit;
status := 'S';
-------------write log---------------------------------------------------------------------------
sp_write_log(job_name,
extraction_timepoint,
ins_start_timestamp,
ins_finish_timestamp,
record_count,
job_type,
status,
error);
---------------EXCEPTION-----------------------------------------------------------------------------
exception
when others then
begin
rollback;
sp_write_log(job_name,
extraction_timepoint,
ins_start_timestamp,
ins_finish_timestamp,
record_count,
job_type,
'F',
sqlcode || ' ' || sqlerrm);
end;
end procedure_name;
--------------------------------------------------------------------------
----------------------LOG日志存过-----------------------------
------------------------------------------------------------------------
procedure sp_write_log(job_name varchar,
extraction_timepoint timestamp,
ins_start_timestamp timestamp,
ins_finish_timestamp timestamp,
record_count number,
type char,
status char,
error varchar2) as
begin
insert into vom_stats_log --vom_stats_log日志记录表
values
(job_name,
extraction_timepoint,
ins_start_timestamp,
ins_finish_timestamp,
record_count,
type,
status,
error);
commit;
end;
end;
3.存储过程定时程序Program定义：
定义program程序：
begin
sys.dbms_scheduler.create_program(
program_name => ' scheme .program_name',
program_type => 'stored_procedure',
program_action => 'package_namae.procedure_name',
number_of_arguments => 1,
enabled => false,
comments => 'Program备注描述');
sys.dbms_scheduler.define_program_argument(
program_name => 'scheme .program_name',
argument_position => 1,
argument_name => 'job_type',
argument_type => 'char',
default_value => 'D');
sys.dbms_scheduler.enable(name => 'scheme .program_name');
end;
/
4.存储过程定时调度Schedule定义：
定义schedule调度：
begin
sys.dbms_scheduler.create_schedule(
schedule_name => 'scheme.schedule_name',
start_date => to_date('start_date', 'dd-mm-yyyy hh24:mi:ss'),
repeat_interval => 'Freq=Daily;Interval=1',
end_date => to_date(null),
comments => 'Schedule备注描述');
end;
/
5.存储过程定时任务Job定义：
定义Job任务：
begin
sys.dbms_scheduler.create_job(
job_name => 'scheme.job_name',
program_name => 'scheme.program_name',
schedule_name => 'scheme.schedule_name',
job_class => 'DEFAULT_JOB_CLASS',
enabled => false,
auto_drop => false,
comments => 'Job备注描述');
sys.dbms_scheduler.enable(name => 'scheme.job_name');
end;
/
PS：包头和包主体分开编译，并作为两个分开的对象分别存放在数据库字典中