Oracle存储过程开发

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：包头和包主体分开编译，并作为两个分开的对象分别存放在数据库字典中