存储过程的创建

最新推荐文章于 2022-08-04 18:13:06 发布

柚柚柚子、

最新推荐文章于 2022-08-04 18:13:06 发布

阅读量478

点赞数

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/mdaefsc/article/details/119237567

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

—包头
create or replace package scott.dwr_order_pkg as

–京东数据
procedure dwr_dd_order_JD_P(start_date date,
end_date date,
store_id number);

–天猫数据
procedure dwr_dd_order_TM_P(start_date date,
end_date date,
store_id number);

–苏宁数据
procedure dwr_dd_order_SN_P(start_date date,
end_date date,
store_id number);

end dwr_order_pkg;

----包体内容
create or replace package body scott.dwr_order_pkg as
V_OBJECT_NAME VARCHAR2(100);
V_START_DATE NUMBER;
V_END_DATE NUMBER;

/*========================================================================================
EFFECT:京东数据集成
TARGET_TABLE:DWR_DD_ORDER_JD_TMP
AUTHOR:Gorkor
DATE:2021-07-22
========================================================================================*/
procedure dwr_dd_order_JD_P(START_DATE date,
END_DATE date,
STORE_ID NUMBER) is
/V_OBJECT_NAME VARCHAR2(100);
V_START_DATE NUMBER;
V_END_DATE NUMBER;/
V_STORE_ID NUMBER;

begin

V_OBJECT_NAME := 'scott.dwr_dd_order_pkg.dwr_dd_order_JD_P';
V_START_DATE  := TO_NUMBER(TO_CHAR(START_DATE, 'YYYYMMDD'));
V_END_DATE    := TO_NUMBER(TO_CHAR(END_DATE, 'YYYYMMDD'));
V_STORE_ID    := STORE_ID;

–日志信息（插入参数，程序开始）
insert into scott.parameter_table_log
(SP_NAME,
PARAMETER_VALUES,
START_TIME,
USER_NAME,
log_id,
DESCRIBE,
step_id)
VALUES
(V_OBJECT_NAME,
‘参数：’ || ‘START_DATE:’ || V_START_DATE || ‘,’ || ‘END_DATE:’ ||
V_END_DATE || ‘,’ || ‘STORE_ID:’ || V_STORE_ID,
SYSDATE,
USER,
1,
‘参数输入注入完成,程序开始执行’,
1);
commit;

–这个位置临时表的删数操作（truncate）
–truncate table scott.dwr_dd_order_jd_tmp;

–日志信息（删除事实表数据，数据抽取的中间表）

insert into SCOTT.DWR_DD_ORDER_JD_TMP

  (order_id,
   crder_date,
   pay_id,
   quantity,
   order_atm,
   discounts_atm,
   pay_number,
   postage,
   expressage_company_id,
   tracking_number,
   pay_date,
   pay_way,
   delivery_date,
   receive_date,
   delivery_warehouse_id,
   order_status,
   receive_address,
   platform_id,
   STORE_ID)
  select T.order_id,
         T.crder_date,
         T.pay_id,
         T.quantity,
         T.order_atm,
         T.discounts_atm,
         T.pay_number,
         T.postage,
         T.expressage_company_id,
         T.tracking_number,
         T.pay_date,
         T.pay_way,
         T.delivery_date,
         T.receive_date,
         T.delivery_warehouse_id,
         T.order_status,
         T.receive_address,
         1                       AS platform_id,
         V_STORE_ID              AS STORE_ID
    from (SELECT s.order_id,
                 s.crder_date,
                 s.pay_id,
                 s.quantity,
                 s.order_atm,
                 s.discounts_atm,
                 s.pay_number,
                 s.postage,
                 s.expressage_company_id,
                 s.tracking_number,
                 s.pay_date,
                 s.pay_way,
                 s.delivery_date,
                 s.receive_date,
                 s.delivery_warehouse_id,
                 s.order_status,
                 s.receive_address
            from scott.dwr_dd_order s, scott.dwr_dim_platform B
           where s.platform_id = b.platform_id(+)
             AND to_number(to_char(s.crder_date, 'YYYYMMDD')) >=
                 V_START_DATE
             AND to_number(to_char(s.crder_date, 'YYYYMMDD')) <=
                 V_END_DATE
             AND s.platform_id = 3) T;
commit;

–日志记录信息
insert into scott.parameter_table_log
(SP_NAME,
PARAMETER_VALUES,
START_TIME,
USER_NAME,
log_id,
DESCRIBE,
step_id)
VALUES
(V_OBJECT_NAME,
NULL,
SYSDATE,
USER,
2,
‘数据插入SCOTT.DWR_DD_ORDER_JD_TMP表完成’,
2);
commit;

END dwr_dd_order_JD_P;

/*========================================================================================
EFFECT:苏宁数据集成
TARGET_TABLE:DWR_DD_ORDER_SN_TMP
AUTHOR:Gorkor
DATE:2021-07-22
========================================================================================*/
procedure dwr_dd_order_SN_P(START_DATE date,
END_DATE date,
STORE_ID number) is
V_STORE_ID NUMBER;

begin
V_OBJECT_NAME := ‘scott.dwr_dd_order_pkg.dwr_dd_order_SN_P’;
V_START_DATE := TO_NUMBER(TO_CHAR(START_DATE, ‘YYYYMMDD’));
V_END_DATE := TO_NUMBER(TO_CHAR(END_DATE, ‘YYYYMMDD’));
V_STORE_ID := STORE_ID;

—参数注入日志
insert into scott.parameter_table_log
(SP_NAME,
PARAMETER_VALUES,
START_TIME,
USER_NAME,
log_id,
DESCRIBE,
step_id)
VALUES
(V_OBJECT_NAME,
‘参数：’ || ‘START_DATE:’ || V_START_DATE || ‘,’ || ‘END_DATE:’ ||
V_END_DATE || ‘,’ || ‘STORE_ID:’ || V_STORE_ID,
SYSDATE,
USER,
1,
‘参数输入注入完成,程序开始执行’,
1);
commit;

insert into SCOTT.DWR_DD_ORDER_SN_TMP

  (order_id,
   crder_date,
   pay_id,
   quantity,
   order_atm,
   discounts_atm,
   pay_number,
   postage,
   expressage_company_id,
   tracking_number,
   pay_date,
   pay_way,
   delivery_date,
   receive_date,
   delivery_warehouse_id,
   order_status,
   receive_address,
   platform_id,
   STORE_ID)
  select T.order_id,
         T.crder_date,
         T.pay_id,
         T.quantity,
         T.order_atm,
         T.discounts_atm,
         T.pay_number,
         T.postage,
         T.expressage_company_id,
         T.tracking_number,
         T.pay_date,
         T.pay_way,
         T.delivery_date,
         T.receive_date,
         T.delivery_warehouse_id,
         T.order_status,
         T.receive_address,
         1  AS platform_id,
         V_STORE_ID AS STORE_ID
    from (SELECT s.order_id,
                 s.crder_date,
                 s.pay_id,
                 s.quantity,
                 s.order_atm,
                 s.discounts_atm,
                 s.pay_number,
                 s.postage,
                 s.expressage_company_id,
                 s.tracking_number,
                 s.pay_date,
                 s.pay_way,
                 s.delivery_date,
                 s.receive_date,
                 s.delivery_warehouse_id,
                 s.order_status,
                 s.receive_address
            from scott.dwr_dd_order s, scott.dwr_dim_platform B
           where s.platform_id = b.platform_id(+)
             AND to_number(to_char(s.crder_date, 'YYYYMMDD')) >=
                 V_START_DATE
             AND to_number(to_char(s.crder_date, 'YYYYMMDD')) <=
                 V_END_DATE
             AND s.platform_id = 2) T;
commit;

—SP完成日志
insert into scott.parameter_table_log
(SP_NAME,
PARAMETER_VALUES,
START_TIME,
USER_NAME,
log_id,
DESCRIBE,
step_id)
VALUES
(V_OBJECT_NAME,
NULL,
SYSDATE,
USER,
2,
‘数据插入SCOTT.DWR_DD_ORDER_SN_TMP表完成’,
2);
commit;

END dwr_dd_order_SN_P;

/*========================================================================================
EFFECT:天猫数据集成
TARGET_TABLE:DWR_DD_ORDER_JD_TMP
AUTHOR:Gorkor
DATE:2021-07-22
========================================================================================*/
procedure dwr_dd_order_TM_P(START_DATE date, END_DATE date,STORE_ID NUMBER) is
V_STORE_ID NUMBER;
begin
V_OBJECT_NAME := ‘scott.dwr_dd_order_pkg.dwr_dd_order_TM_P’;
V_START_DATE := TO_NUMBER(TO_CHAR(START_DATE, ‘YYYYMMDD’));
V_END_DATE := TO_NUMBER(TO_CHAR(END_DATE, ‘YYYYMMDD’));
V_STORE_ID := STORE_ID;
—参数注入日志
insert into scott.parameter_table_log
(SP_NAME,
PARAMETER_VALUES,
START_TIME,
USER_NAME,
log_id,
DESCRIBE,
step_id)
VALUES
(V_OBJECT_NAME,
‘参数：’ || ‘START_DATE:’ || V_START_DATE || ‘,’ || ‘END_DATE:’ ||
V_END_DATE||’,’||‘STORE_ID:’||V_STORE_ID,
SYSDATE,
USER,
1,
‘参数输入注入完成,程序开始执行’,
1);
commit;

—日志信息（删除临时表数据）

insert into SCOTT.DWR_DD_ORDER_TM_TMP

  (order_id,
   crder_date,
   pay_id,
   quantity,
   order_atm,
   discounts_atm,
   pay_number,
   postage,
   expressage_company_id,
   tracking_number,
   pay_date,
   pay_way,
   delivery_date,
   receive_date,
   delivery_warehouse_id,
   order_status,
   receive_address,
   platform_id,
   STORE_ID
  )
  select 
    T.order_id ,
    T.crder_date,
    T.pay_id,
    T.quantity,
    T.order_atm,
    T.discounts_atm,
    T.pay_number,
    T.postage,
    T.expressage_company_id,
    T.tracking_number,
    T.pay_date,
    T.pay_way,
    T.delivery_date,
    T.receive_date,
    T.delivery_warehouse_id,
    T.order_status,
    T.receive_address,
    1 AS platform_id,
    V_STORE_ID AS STORE_ID
  from
    (SELECT s.order_id,
         s.crder_date,
         s.pay_id,
         s.quantity,
         s.order_atm,
         s.discounts_atm,
         s.pay_number,
         s.postage,
         s.expressage_company_id,
         s.tracking_number,
         s.pay_date,
         s.pay_way,
         s.delivery_date,
         s.receive_date,
         s.delivery_warehouse_id,
         s.order_status,
         s.receive_address
    from scott.dwr_dd_order s, scott.dwr_dim_platform B
   where s.platform_id = b.platform_id(+)
     AND to_number(to_char(s.crder_date, 'YYYYMMDD')) >= V_START_DATE
     AND to_number(to_char(s.crder_date, 'YYYYMMDD')) <= V_END_DATE
     AND s.platform_id = 1)T;
commit;

-----SP完成日志
insert into scott.parameter_table_log
(SP_NAME,
PARAMETER_VALUES,
START_TIME,
USER_NAME,
log_id,
DESCRIBE,
step_id)
VALUES
(V_OBJECT_NAME,
NULL,
SYSDATE,
USER,
2,
‘数据插入SCOTT.DWR_DD_ORDER_TM_TMP表完成’,
2);
commit;

END dwr_dd_order_TM_P;

END dwr_order_pkg;

柚柚柚子、

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
存储过程的创建

—包头create or replace package scott.dwr_order_pkg as–京东数据procedure dwr_dd_order_JD_P(start_date date,end_date date,store_id number);–天猫数据procedure dwr_dd_order_TM_P(start_date date,end_date date,store_id number);–苏宁数据procedure dwr_dd_orde
复制链接

扫一扫