【数据库】存储过程及定时更新表
2019-02-18 本文已影响3人
MichalLiu
数据库宽表更新数据一般经过以下过程：
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);
结束
定时任务可以在数据库闲余时间自动跑数，节省查询计算时间。
