【数据库】存储过程及定时更新表

数据库宽表更新数据一般经过以下过程：

1、首先判断数据是否存在；
2、如果不存在，则插入；
3、如果存在，则更新。

下面对主流数据库Mysql、SQL Server、Oracle的数据更新做描述。

My sql

1. replace into tbl_name(col_name, ...) values(...)

2. replace into tbl_name(col_name, ...) select ...

3. replace into tbl_name set col_name=value, ...

第一种形式类似于insert into的用法。

第二种replace select的用法也类似于insert select，这种用法并不一定要求列名匹配，事实上，MYSQL甚至不关心select返回的列名，它需要的是列的位置。例如，replace into tb1( name, title, mood) select rname, rtitle, rmood from tb2;这个例子使用replace into从tb2中将所有数据导入tb1中。

第三种replace set用法类似于update set用法，使用一个例如“SET col_name = col_name + 1”的赋值，则对位于右侧的列名称的引用会被作为DEFAULT(col_name)处理。因此，该赋值相当于SET col_name = DEFAULT(col_name) + 1。

前两种形式用的多些。其中 “into” 关键字可以省略，不过最好加上 “into”，这样意思更加直观。另外，对于那些没有给予值的列，MySQL 将自动为这些列赋上默认值。

SQL Server

if  exists  (select 1 from T where T.a='1001' )

        update T set T.b=2 Where T.a='1001'

else

        insert into T(a,b) values('1001',2);

有一个表T，有两个字段a、b，我们想在表T中做Insert/Update，如果条件满足，则更新T中b的值，否则在T中插入一条记录。在Microsoft的SQL语法中，很简单的一句判断就可以了。

Oracle\SQL Server

MERGE INTO table_name alias1          -- 别名表1

USING (table|view|sub_query)  alias2   -- 别名表2

ON  (join condition)    -- 连接条件

WHEN MATCHED THEN

     UPDATE table_name SET col1 = col_val1, col2 = col_val2

WHEN NOT MATCHED THEN

     INSERT (column_list)  VALUES (column_values);

更新表应用实例（Oracle）

一、删除表中数据→插入数据

create or replace PROCEDURE PROC_LZW_APP    -- 创建存储过程

is
    rn number;       -- 定义变量

begin

-- 立即执行 ‘删除表中内容’
execute immediate 'truncate table risk.lzw_app';    

-- 向表中插入数据
insert into risk.lzw_app            
  (

  COL1,

  COL2,

  COL3,

  COL4

  )

    SELECT COL1,COL2,COL3,COL4

       FROM T1

    -- 记录修改的条数

    rn := sql%rowcount; 

    -- 日志表中插入修改记录

    insert into report_log select 'proc','proc_lzw_app','01-report_app'||to_char(rn),sysdate from dual;

    commit;

-- 结束存储过程
end PROC_LZW_APP;

二、不删除数据→更新 | 插入数据

create or replace PROCEDURE PROC_LZW_AUDIT     -- 创建存储过程

is

    rn number;    -- 定义变量

begin

    merge into risk_da.report_audit t1

    using (

          select COL1,COL2

            from talble a

          ) t2 on (t1.id=t2.id)

        when matched then

            update set t1.col1=t2.col1,

                        t1.col2=t2.col2  -- 主键id相等则更新

      when not matched then

            insert (col1,col2)

            values (t2.col1,t2.col2);    -- 原表没有新id则插入数据

    rn := sql%rowcount;

    insert into risk_da.report_log select 'proc','proc_audit','02-report_audit'||to_char(rn),sysdate from dual;

    commit;

end；

三、删除部分数据→插入数据

create or replace PROCEDURE PROC_LZW_APP     -- 创建存储过程

is

    rn number;           -- 定义变量

begin

 -- 删除日期为当天的数据

     delete from risk_da.report_audit30_summary where statdate=trunc(sysdate); 

 -- 插入昨天产生的数据，不更新历史数据

insert into risk_da.report_audit                   

    select sum(case when cdate=trunc(sysdate-1) then 1 else 0 end) lastday_sum,

          col2

      from risk.report_fact

    where cdate>trunc(sysdate-10);                                               



    rn := sql%rowcount;

    insert into risk_da.report_log select 'proc','proc_audit','1-summary'||to_char(rn),sysdate from dual;

    commit;

end;

定时任务（Oracle）

一、创建定时任务

-- 定时任务的程序

-- 宽表每周一早上8点更新更新

variable jobno number;        -- 生成job号

begin

dbms_job.submit(:jobno,       -- job号

                'proc_lzw_dpd;',     -- 执行的存储过程, ';'不能省略

                sysdate,             -- 下次执行时间

--每次间隔时间，--例如：每天的凌晨2点执行Interval => TRUNC(sysdate) + 1 +2 /24

                'TRUNC(next_day(sysdate,''星期一''))+8/24' 

);

commit;

end;

二、查看JOB任务明细

-- 查看JOB号码
select * from user_jobs order by job;

三、删除定时JOB

-- 删除一个定时器（不能包含这些文字），JOB号码在查看JOB的第一列数字
exec dbms_job.remove(7588);

结束

定时任务可以在数据库闲余时间自动跑数，节省查询计算时间。