更新数据的方式及日志与异常处理

更新数据的方法

更新的方式
离线（每几个小时更新一次，或者每天更新一次，日更居多，一般拿两天的数据，但是这样的话数据会重复（前天的））
实时

全量更新
增量更新
一般增量删加粗样式除再更新（目的是防止终端补录前天数据）

维度表：基本是全量更新，因为数据比较少 ，最多才几万条
事实表，一般是增量更新
结论在前：merge into更新法最好
对比：merge into更新DM_STORE_DATAIL表三天的酒店数据仅需1.2s，而游标法要7分钟，效率差的不是一星半点。

方法一：现将目标表的记录在增量范围之内删除，然后在将增量插入

--增量更新的4种方法
--方法一：先将目标表的记录在增量范围之内的删除，然后再将增量插入
CREATE OR REPLACE PROCEDURE SP_EMPA(P_START_DATE VARCHAR2,
                                    P_END_DATE VARCHAR2) IS
  V_START_DATE DATE := TO_DATE(P_START_DATE, 'YYYY-MM-DD HH24:MI:SS');
  V_END_DATE   DATE := TO_DATE(P_END_DATE, 'YYYY-MM-DD HH24:MI:SS');
BEGIN
  DELETE FROM EMP_TOTAL2
   WHERE UPDATE_TIME between v_start_date and V_END_DATE;
insert into emp_total2
  (empno, ename, job, mgr, hiredate, sal, comm, deptno, dname, loc, grade, update_time)
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno, dname, loc, grade, SYSDATE
FROM EMP_TOTAL
WHERE UPDATE_TIME  between v_start_date and v_end_date;
  COMMIT;
END;

--调用存储过程
BEGIN
  SP_EMPA('2021-09-08 23:00:00','2021-09-09 20:00:00');
END;

SELECT * FROM EMP_TOTAL2;

方法二，使用merge into更新（性能最好）

CREATE OR REPLACE PROCEDURE SP_dw_room_detail2(v_start_time date,v_end_time date) IS **这里用date格式会方便很多，不需要声明，因为下面的datekey是date格式的**
BEGIN
merge into dw_room_detail M
USING(
  SELECT A.datekey,A.YEARSS date_year,A.MONTHSS date_month,

             B.CHAINID chainid,
             SUM(C.statusrmng) status_rmng,
             SUM(C.statusrmng+c.roomday+c.roomhalfday+c.roomhourday) status_rm,
             SUM(B.AMOUNTRMNG) AmountRmNg,
             SUM(NVL(AmountRmNg,0)+NVL(AmountRmAllDay,0)+NVL(AmountRmOther,0)+NVL(AmountRmDiscount,0)) RoomAmount,
             SUM(NVL(AmountRmNg,0)+NVL(AmountRmAllDay,0)+NVL(AmountRmOther,0)+NVL(AmountRmDiscount,0)  +NVL(B.AmountOther,0))  revenue


  FROM ODS.ODS_T_DATE A
  JOIN ODS.ods_roomammount_detail B ON A.datekey = to_date(B.datekey,'yyyy/mm/dd')

  JOIN ODS.ods_roomcount_detaiL C ON  B.datekey = C.datekey
    AND B.chainid = C.chainid
   AND B.roomtypeid = C.roomtypeid
WHERE A.DATEKEY BETWEEN v_start_time AND v_end_time --这里需要使用条件，查找日期在参数范围内的数据，

  GROUP BY  A.datekey,A.YEARSS,A.MONTHSS ,
             B.CHAINID) N
ON( M.DATEKEY = N.DATEKEY AND M.CHAINID = N.CHAINID)--匹配字段，与原表数据进行关联

WHEN MATCHED THEN UPDATE SET --如果匹配得上，就更新该数据，--在这里不需要添加datekey和chainid的 因为在关联的时候已经匹配过了
   M.date_year = N.date_year,
   M.date_month = N.date_month,
   M.status_rmng = N.status_rmng,
   M.AmountRmNg = N.AmountRmNg,
   M.RoomAmount = N.RoomAmount,
   M.revenue = N.revenue
WHEN NOT MATCHED THEN INSERT--如果匹配不上，就增加数据。因为匹配不到的意思就是原来的M表里没有这行数据。

 (M.datekey,
  M.date_year,
  M.date_month,
  M.chainid,
  M.status_rmng,
  M.status_rm,
  M.amountrmng,
  M.roomamount,
  M.revenue
 )
--从N表内获取数据
 VALUES(
 N.datekey,
 N.date_year,
 N.date_month,
 N.chainid,
 N.status_rmng,
 N.status_rm,
 N.amountrmng,
 N.roomamount,
 N.revenue);
COMMIT;
END;

DM层的更新抽取方式

create or replace procedure sp_dm_store_detail2(v_start_time date,v_end_time date) is begin

merge into dm_store_detail M
using(
SELECT S.store_id,
       S.project_id,
       S.store_name,
       S.brand_name,
       S.group_name,
       S.brand_type_name,
       S.category_name,
       S.ship_name,
       S.area_name,
       S.province,
       S.city_name,
       S.district_name,
       S.room_open,
       S.open_date,
       S.signed_time,
       S.closed_time,
       S.status_name,
       D.datekey,
       D.date_year,
       D.date_month,
       D.status_rmng,
       D.status_rm,
       D.amountrmng,
       D.roomamount,
       D.revenue
FROM DW.DW_ROOM_DETAIL D
JOIN DW.DW_STORE S ON D.CHAINID =S.STORE_ID
WHERE D.DATEKEY BETWEEN v_start_time AND v_end_time
) N
on( M.DATEKEY = N.DATEKEY AND M.store_id = N.store_id --关联换成了store id和datekey
)

WHEN MATCHED THEN UPDATE SET ---这里不需要写store_id和datekey,在关联条件中已经是相等了
   M.project_id=N.project_id,
   M.store_name=N.store_name,
   M.brand_name=N.brand_name,
   M.group_name=N.group_name,
   M.brand_type_name=N.brand_type_name,
   M.category_name=N.category_name,
   M.ship_name=N.ship_name,
   M.area_name=N.area_name,
   M.province=N.province,
   M.city_name=N.city_name,
   M.district_name=N.district_name,
   M.room_open=N.room_open,
   M.open_date=N.open_date,
   M.signed_time=N.signed_time,
   M.closed_time=N.closed_time,
   M.status_name=N.status_name,
   M.date_year=N.date_year,
   M.date_month=N.date_month,
   M.status_rmng=N.status_rmng,
   M.status_rm=N.status_rm,
   M.amountrmng=N.amountrmng,
   M.roomamount=N.roomamount,
   M.revenue = N.revenue

WHEN NOT MATCHED THEN--但是这里的store_id和datekey要写，因为这是插入数据，不是更新

insert
  (M.store_id,
   M.project_id,
   M.store_name,
   M.brand_name,
   M.group_name,
   M.brand_type_name,
   M.category_name,
   M.ship_name,
   M.area_name,
   M.province,
   M.city_name,
   M.district_name,
   M.room_open,
   M.open_date,
   M.signed_time,
   M.closed_time,
   M.status_name,
   M.datekey,
   M.date_year,
   M.date_month,
   M.status_rmng,
   M.status_rm,
   M.amountrmng,
   M.roomamount,
   M.revenue)
values
  (N.store_id,
   N.project_id,
   N.store_name,
   N.brand_name,
   N.group_name,
   N.brand_type_name,
   N.category_name,
   N.ship_name,
   N.area_name,
   N.province,
   N.city_name,
   N.district_name,
   N.room_open,
   N.open_date,
   N.signed_time,
   N.closed_time,
   N.status_name,
   N.datekey,
   N.date_year,
   N.date_month,
   N.status_rmng,
   N.status_rm,
   N.amountrmng,
   N.roomamount,
   N.revenue);

commit;
end;

第三种方法：游标
自己用游标法写的代码（dw_room_detail 表）

create or replace procedure sp_youbiaofa(v_start_time date, v_end_time DATE) is

cursor c_dw_room_detail is--使用游标查询数据，范围限定在时间参数范围内
SELECT A.datekey,A.YEARSS date_year,A.MONTHSS date_month,

           B.CHAINID chainid,
           SUM(C.statusrmng) status_rmng,
           SUM(C.statusrmng+c.roomday+c.roomhalfday+c.roomhourday) status_rm,
           SUM(B.AMOUNTRMNG) AmountRmNg,
           SUM(NVL(AmountRmNg,0)+NVL(AmountRmAllDay,0)+NVL(AmountRmOther,0)+NVL(AmountRmDiscount,0)) RoomAmount,
           SUM(NVL(AmountRmNg,0)+NVL(AmountRmAllDay,0)+NVL(AmountRmOther,0)+NVL(AmountRmDiscount,0)  +NVL(B.AmountOther,0))  revenue


FROM ODS.ODS_T_DATE A
JOIN ODS.ods_roomammount_detail B ON A.datekey = to_date(B.datekey,'yyyy/mm/dd')

JOIN ODS.ods_roomcount_detaiL C ON  B.datekey = C.datekey
  AND B.chainid = C.chainid
  and B.roomtypeid = C.roomtypeid
WHERE A.datekey between v_start_time AND v_end_time--记得限定范围

GROUP BY  A.datekey,A.YEARSS,A.MONTHSS ,
           B.CHAINID;

v_cnt number;--声明一个变量


begin
  for x in c_dw_room_detail loop
    select count(*)
    into v_cnt 
    from dw_room_detail m where m.datekey = x.datekey and m.chainid = x.chainid;
    if v_cnt =1
     then update dw_room_detail m set
         m.date_year = x.date_year,
         m.date_month = x.date_month,
         m.status_rmng = x.status_rmng,
         m.status_rm = x.status_rm,
         m.amountrmng = x.amountrmng,
         m.roomamount = x.roomamount,
         m.revenue = x.revenue
         where m.datekey = x.datekey and m.chainid = x.chainid;--这里还是得限定范围，因为从if v_cnt =1开始，下半部分和上半部分是完全脱节的，如果不限制，会全量更新成x._的数据，游标内所有的x还都会跑一次，就炸了。所以这条语句很重要。
         --区别：merge的语法不完全按照update set的语法，只是类似，它是独立的一套写法，记住就好了；而游标这种方法，则是需要使用规范的update set 语法。update set语法参考(https://editor.csdn.net/md/?articleId=120304065)
     elsif  v_cnt =0 then
       insert into dw_room_detail m
         (m.datekey,
          m.date_year,
          m.date_month,
          m.chainid,
          m.status_rmng,
          m.status_rm,
          m.amountrmng,
          m.roomamount,
          m.revenue)
       values
         (X.datekey,
          X.date_year,
          X.date_month,
          X.chainid,
          X.status_rmng,
          X.status_rm,
          X.amountrmng,
          X.roomamount,
          X.revenue);
end if;
end loop;
commit;
end;

参考代码

CREATE OR REPLACE PROCEDURE SP_DW_ROOM_DETAIL3(V_START_TIME DATE,V_END_TIME DATE) IS
CURSOR C_TEP IS
  SELECT D.DATEKEY,
       D.YEARSS date_year,
       D.MONTHSS date_month,
       A.CHAINID,
       SUM(B.STATUSRMNG) status_rmng,
       SUM(B.STATUSRMNG+B.ROOMDAY+B.ROOMHALFDAY+B.ROOMHOURDAY) status_rm,
       SUM(NVL(A.AMOUNTRMNG,0)) AmountRmNg,
       SUM(NVL(A.AMOUNTRMNG,0)+NVL(A.AMOUNTRMALLDAY,0)+NVL(A.AMOUNTRMOTHER,0)+NVL(A.AMOUNTRMDISCOUNT,0)) RoomAmount,
       SUM(NVL(A.AMOUNTRMNG,0)+NVL(A.AMOUNTRMALLDAY,0)+NVL(A.AMOUNTRMOTHER,0)+NVL(A.AMOUNTRMDISCOUNT,0)+NVL(A.AMOUNTOTHER,0)) revenue
FROM ODS.ods_t_date D
  JOIN ODS.ods_roomammount_detail A
    ON D.DATEKEY = TO_DATE(A.DATEKEY,'YYYYMMDD')
  JOIN ODS.ods_roomcount_detail B
    ON A.DATEKEY = B.DATEKEY
    AND A.CHAINID = B.CHAINID
    AND A.ROOMTYPEID = B.ROOMTYPEID
WHERE D.DATEKEY BETWEEN V_START_TIME AND V_END_TIME 
GROUP BY   D.DATEKEY,
       D.YEARSS,
       D.MONTHSS,
       A.CHAINID;

 V_CNT NUMBER;--声明一个变量
BEGIN
  FOR X IN C_TEP LOOP
    SELECT COUNT(*) INTO V_CNT FROM DW_ROOM_DETAIL M WHERE M.DATEKEY = X.DATEKEY AND M.CHAINID = X.CHAINID;
    IF V_CNT = 1
      THEN UPDATE DW_ROOM_DETAIL M SET
        M.DATE_MONTH = X.DATE_MONTH,
        M.DATE_YEAR = X.DATE_YEAR,
        M.status_rmng = X.status_rmng,
        M.status_rm = X.status_rm,
        M.AmountRmNg = X.AmountRmNg,
        M.RoomAmount = X.RoomAmount,
        M.revenue = X.revenue
        WHERE M.DATEKEY = X.DATEKEY AND M.CHAINID = X.CHAINID;
        ELSIF V_CNT = 0 THEN
          INSERT INTO DW_ROOM_DETAIL M
          (M.DATEKEY,
              M.date_year,
              M.DATE_MONTH,
              M.CHAINID,
              M.status_rmng,
              M.status_rm,
              M.AmountRmNg,
              M.RoomAmount,
              M.revenue)
              VALUES
              (X.DATEKEY,
               X.date_year,
               X.DATE_MONTH,
               X.CHAINID,
               X.status_rmng,
               X.status_rm,
               X.AmountRmNg,
               X.RoomAmount,
               X.revenue);
    END IF;
  END LOOP;
  COMMIT;
END;

DM层
游标法增量更新dm_store_detail

create or replace procedure sp_youbiaofa(v_start_time date ,v_end_time date) is
cursor c_dm_r_d is
SELECT S.store_id,
       S.project_id,
       S.store_name,
       S.brand_name,
       S.group_name,
       S.brand_type_name,
       S.category_name,
       S.ship_name,
       S.area_name,
       S.province,
       S.city_name,
       S.district_name,
       S.room_open,
       S.open_date,
       S.signed_time,
       S.closed_time,
       S.status_name,
       D.datekey,
       D.date_year,
       D.date_month,
       D.status_rmng,
       D.status_rm,
       D.amountrmng,
       D.roomamount,
       D.revenue
FROM DW.DW_ROOM_DETAIL D
JOIN DW.DW_STORE S ON D.CHAINID =S.STORE_ID
where D.datekey between v_start_time and v_end_time;

v_cnt number;

begin
  for i in c_dm_r_d loop
    select count(*)
    into v_cnt
    from dm_store_detail m
    where m.datekey = i.datekey and m.store_id = i.store_id;
    if v_cnt =1 then
      update dm_store_detail m set
       m.project_id = i.project_id,
       m.store_name =i.store_name,
       m.brand_name =i.brand_name,
       m.group_name =i.group_name,
       m.brand_type_name =i.brand_type_name,
       m.category_name =i.category_name,
       m.ship_name =i.ship_name,
       m.area_name =i.area_name,
       m.province =i.province,
       m.city_name =i.city_name,
       m.district_name =i.district_name,
       m.district_name =i.district_name,
       m.open_date =i.open_date,
       m.signed_time =i.signed_time,
       m.closed_time =i.closed_time,
       m.status_name =i.status_name,
       m.date_year =i.date_year,
       m.date_month =i.date_month,
       m.status_rmng =i.status_rmng,
       m.status_rm=i.status_rm,
       m.amountrmng=i.amountrmng,
       m.roomamount=i.roomamount,
       m.revenue=i.revenue
       where m.datekey = i.datekey and m.store_id = i.store_id;
       elsif v_cnt =0 then
         insert into dm_store_detail m
           (m.store_id,
            m.project_id,
            m.store_name,
            m.brand_name,
            m.group_name,
            m.brand_type_name,
            m.category_name,
            m.ship_name,
            m.area_name,
            m.province,
            m.city_name,
            m.district_name,
            m.room_open,
            m.open_date,
            m.signed_time,
            m.closed_time,
            m.status_name,
            m.datekey,
            m.date_year,
            m.date_month,
            m.status_rmng,
            m.status_rm,
            m.amountrmng,
            m.roomamount,
            m.revenue)
         values
           (i.store_id ,
            i.project_id ,
            i.store_name ,
            i.brand_name ,
            i.group_name ,
            i.brand_type_name ,
            i.category_name ,
            i.ship_name ,
            i.area_name ,
            i.province ,
            i.city_name ,
            i.district_name ,
            i.room_open ,
            i.open_date ,
            i.signed_time ,
            i.closed_time ,
            i.status_name ,
            i.datekey ,
            i.date_year ,
            i.date_month ,
            i.status_rmng ,
            i.status_rm ,
            i.amountrmng ,
            i.roomamount ,
            i.revenue);
       end if ;

    end loop;
    end;

以上是事实表的更新方法，而维度表一般不用这么操作，全量抽取就好。

日志及异常处理

—1、创建一个日志表，用来记录存储过程的日志

create table log_record
(
  log_id      number,      ---日志id
  sp_name     varchar2(100),----记录的存储过程名称
  step        number,      ---步骤
  finish_time date,       ---完成时间
  remarks     varchar2(100) ---备注
)

—2、创建用于插入日志id 的序列

create sequence S_LOG

---序列使用示例：

SELECT s_log.nextval FROM dual;

----3、插入日志的语句

INSERT INTO log_record
  (log_id, sp_name, step, finish_time, remarks)
VALUES
  (s_log.nextval, 'SP_DIM_USER', 2, SYSDATE, '插入数据完成');
COMMIT;

—4、把插入日志这个操作，写成一个存储过程

CREATE OR REPLACE PROCEDURE SP_LOG(P_SP_NAME VARCHAR2,P_STEP NUMBER,P_REMARKS VARCHAR2) 
IS
/*
创建人：
创建时间：
修改人：
修改时间：
功能描述：用于向日志表中记录数据
*/
BEGIN
  INSERT INTO LOG_RECORD
    (LOG_ID, SP_NAME, STEP, FINISH_TIME, REMARKS)
  VALUES
    (s_log.NEXTVAL, P_SP_NAME, P_STEP, SYSDATE, P_REMARKS);
  COMMIT;
END;

—5、在需要写日志的地方加上

sp_log('SP_DIM_USER',1,'程序开始');

—6、测试
一定要记得，不这样执行，仅仅在sp封装内执行是没用的。

BEGIN
  sp_dim_user;
END;

—7、查看日志

select * from LOG_RECORD t

二、异常处理

，也叫作错误处理，通常是把异常记录在日志中
----在存储过程中加上异常处理

BEGIN（是额外加进去的begin）

---

—执行的语句
EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
sp_log(v_sp_name,2,‘异常:’||SQLCODE||sqlerrm);

WHEN OTHERS THEN
ROLLBACK;
sp_log(v_sp_name,2,‘异常:’||SQLCODE||sqlerrm);
END;

----执行

BEGIN
sp_dim_user;
END;

—查看监控日志

select * from LOG_RECORD t

特殊情况：

create or replace procedure sp_emp(v_deptno in number) is
v_sp_name varchar2(100) :='sp_emp';
cursor c_emp is
select empno,ename,hiredate
from emp
where deptno =v_deptno and sal =  (select max(sal)
                                                      from emp
                                                      where deptno = v_deptno)  ;
begin
sp_log(v_sp_name,1,'程序开始');
begin
  for i in c_emp loop
    dbms_output.put_line(i.empno||','||i.ename||','||to_char(i.hiredate,'yyyy/mm/dd'));
    end loop;
    sp_log(v_sp_name,2,'打印完成');
    

EXCEPTION 
  
   WHEN DUP_VAL_ON_INDEX THEN 
     ROLLBACK;
     sp_log(v_sp_name,2,'异常:'||SQLCODE||sqlerrm);
     
     WHEN OTHERS THEN 
     ROLLBACK;  
       sp_log(v_sp_name,2,'异常:'||SQLCODE||sqlerrm);

end;
       sp_log(v_sp_name,3,'程序结束');

end;

这种情况是不会报错的，因为显式游标是自定义量，内部无论是空值，一条数据，多条数据都能够打印。如果是隐式游标则在 into 数据的时候会判断 是否为一条数据或者空值，空值则报错。

SP_DW_STOREde 的完整版：

CREATE OR REPLACE PROCEDURE SP_DW_STORE IS
V_CNT NUMBER;
BEGIN
  SP_LOG('SP_DW_STORE',1,'程序开始');
  
  SELECT COUNT(*) --程序执行前，从这里开始进行判断数据量，如果数据量为零，那么可能出现库或前面的层内表格被删了的情况，这时候就不能对现有数据进行抽取覆盖，这样会丢失数据。
INTO V_CNT
    FROM ODS.ODS_T_STORE S
    LEFT JOIN ODS.ODS_COM_BRAND A
      ON S.BRAND_ID = A.BRAND_ID
    LEFT JOIN ODS.ODS_COM_BRAND_TYPE B
      ON S.BRAND_TYPE_ID = B.TYPE_ID
    LEFT JOIN (SELECT M.ID,
                      M.NAME CITY_NAME,
                      N.NAME province,
                      P.NAME area_name
                 FROM ODS.ODS_COM_AREA M ---城市表
                 JOIN ODS.ODS_COM_AREA N --省份表
                   ON M.PID = N.ID
                 JOIN ODS.ODS_COM_AREA P ---区域表
                   ON N.PID = P.ID) C
      ON S.CITY_ID = C.ID
    LEFT JOIN ODS.ODS_COM_AREA_district D
      ON S.DISTRICT_ID = D.ID
    LEFT JOIN ODS.ODS_COM_STORE_STATUS E
      ON S.FLAG = E.ID;
      IF V_CNT != 0 THEN 
  BEGIN --判断完成才开始执行程序
  DELETE FROM DW_STORE;

SP_LOG('SP_DW_STORE',2,'删除数据完成');
insert into dw_store
  (store_id,
   project_id,
   store_name,
   brand_name,
   group_name,
   brand_type_name,
   category_name,
   ship_name,
   area_name,
   province,
   city_name,
   district_name,
   room_open,
   open_date,
   signed_time,
   closed_time,
   status_name)
  SELECT S.ID store_id,
         S.PROJECT_ID,
         S.STORE_NAME,
         A.BRAND_NAME,
         A.GROUP_NAME,
         B.TYPE_NAME brand_type_name,
         B.CATEGORY_NAME,
         CASE
           WHEN S.SHIP_FLAG = 0 THEN
            '异常店'
           WHEN S.SHIP_FLAG = 1 THEN
            '普通店'
           WHEN S.SHIP_FLAG = 2 THEN
            '插旗店'
           ELSE
            '旗舰店'
         END ship_name,
         C.AREA_NAME,
         C.PROVINCE,
         C.CITY_NAME,
         D.NAME district_name,
         S.ROOM_OPEN,
         S.OPEN_DATE,
         S.SIGNED_TIME,
         S.CLOSED_TIME,
         E.NAME status_name
    FROM ODS.ODS_T_STORE S
    LEFT JOIN ODS.ODS_COM_BRAND A
      ON S.BRAND_ID = A.BRAND_ID
    LEFT JOIN ODS.ODS_COM_BRAND_TYPE B
      ON S.BRAND_TYPE_ID = B.TYPE_ID
    LEFT JOIN (SELECT M.ID,
                      M.NAME CITY_NAME,
                      N.NAME province,
                      P.NAME area_name
                 FROM ODS.ODS_COM_AREA M ---城市表
                 JOIN ODS.ODS_COM_AREA N --省份表
                   ON M.PID = N.ID
                 JOIN ODS.ODS_COM_AREA P ---区域表
                   ON N.PID = P.ID) C
      ON S.CITY_ID = C.ID
    LEFT JOIN ODS.ODS_COM_AREA_district D
      ON S.DISTRICT_ID = D.ID
    LEFT JOIN ODS.ODS_COM_STORE_STATUS E
      ON S.FLAG = E.ID;

SP_LOG('SP_DW_STORE',3,'插入数据完成');
EXCEPTION --异常情况的处理方式，回滚等
  
   WHEN DUP_VAL_ON_INDEX THEN 
     ROLLBACK;
     sp_log('SP_DW_STORE',2,'异常:'||SQLCODE||sqlerrm);
     
     WHEN OTHERS THEN 
     ROLLBACK;  
       sp_log('SP_DW_STORE',2,'异常:'||SQLCODE||sqlerrm);
END;
COMMIT; --提交一定记得，前面的要删光 
ELSE 
SP_LOG('SP_DW_STORE',4,'程序结束');
END IF;
END;

动态sql，专门封装DDL语言（数据定义语言）
EXECUTE IMMEDIATE ‘SQL语言’