Oracle、MySQL、SqlServer、PG数据库mergeinto语法实现总结
概述
多表关联查询的时候会⽤到临时表插⼊数据，然后再⽤select查⾏查询，在往临时表⾥插⼊数据的时候，我们经常会⽤到判断如果临时表⾥有了这部分数据我们就要更新数据，如果临时表⾥没有这部分数据我们就要插⼊，这个时候可以怎么去实现呢？
java有几个框架下⾯介绍各类型数据库实现的⼤致⽅式。
⼀、存储过程实现
各类型数据库都可以通过存储过程实现，因为是共性问题，所以就放前⾯了，这⾥以mysql数据库的存储过程为例。
1、环境准备
--建表
create table t1(
id bigint(10),
name varchar(16),
sale bigint(10),
operatime datetime);
create table t2(
id bigint(10),
name varchar(16),
sale bigint(20));
-- 插⼊数据
INSERT into t1 values(1,"xiaohong",1000,now());
INSERT into t1 values(2,"xiaoming",500,now());
INSERT into t2 values(1,"xiaohong",300);
INSERT into t2 values(2,"xiaoming",400);
INSERT into t2 values(3,"xiaoxiao",900);
2、存储过程实现
delimiter $
CREATE PROCEDURE merge_t2_to_t1 () BEGIN
-- 定义需要插⼊从a表插⼊b表的过程变量
DECLARE _ID bigint(10);
DECLARE _NAME VARCHAR (16);
DECLARE _SALE VARCHAR (16);
-- 游标遍历数据结束标志
DECLARE done INT DEFAULT FALSE;
-- 游标指向a表结果集第⼀条-1位置
DECLARE cur_account CURSOR FOR SELECT ID, NAME,SALE FROM t2;
-- 游标指向a表结果集最后⼀条加1位置设置结束标志
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done= TRUE;
-- 打开游标
OPEN cur_account;
-
- 遍历游标
read_loop :
异步程序LOOP
-- 取值a表当前位置数据到临时变量
FETCH NEXT FROM cur_account INTO _ID,_NAME,_SALE;
-- 如果取值结束跳出循环
IF done THEN LEAVE read_loop;
END IF;儿童教育机构网页设计素材
-- 当前数据做对⽐，如果b表存在则更新时间不存在则插⼊
IF NOT EXISTS ( SELECT 1 FROM t1 WHERE ID = _ID AND NAME=_NAME )
THEN
INSERT INTO t1 (ID, NAME,sale,operatime) VALUES (_ID,_NAME,_sale,now()); ELSE
UPDATE t1 set sale = _sale WHERE ID = _ID AND NAME=_NAME;
END IF;
END LOOP;
CLOSE cur_account;
END $
3、调⽤存储过程
call merge_t2_to_t1();
⼆、sqlserver的merge into语法
在SQL Server 2008的时候微软增加了⼀个强⼤的语句Merge。
1、语法mysql创建存储过程例子
MERGE 语句是⾸先对源表和⽬标表都进⾏完全表扫描，然后拿源表和⽬标表检查，匹配条件，若成⽴则执⾏SQL语句1，不成⽴则执⾏SQL语句2，最执⾏SQL语句3。
语法：
MERGE
[ TOP ( expression )[ PERCENT ]]
[ INTO ]<；操作表> --即将做插⼊、更新、删除的表
USING <；源表或者数据集或者⼦查询> --⽤户提供匹配条件来源的集合或者表
ON <；匹配条件> --可以是任意有效的条件组合
[ WHEN MATCHED [ AND <clause_search_condition>]--匹配条件成⽴
THEN <SQL语句1>]
[ WHEN NOT MATCHED [ BY TARGET ][ AND <clause_search_condition>]--匹配条件不成⽴
THEN <SQL语句2>]
[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition>]--⽬标变不存在⽽源表存在的数据
THEN <SQL语句3>]
; -- 不要忘记分号
注意：
1）Merge操作的只是“操作表”，源表不会有任何变化
2）Merge的最后结尾必须是以分号结束的，不能忘了分号
3）语法严格要求关键字之间只能有⼀个英⽂空格，不能有多余的空格
4）不⼀定要把三个操作都写全，可以根据实际情况
2、实验
1)环境准备
--建表
create table t1(
id int,
name VARCHAR(16),
sale int,
Operatime date);
create table t2(
id int,
name VARCHAR(16),
sale int);
-- 插⼊数据
INSERT into t1 values(1,'xiaohong',1000,sysdate);
INSERT into t1 values(2,'xiaoming',500,sysdate);
INSERT into t2 values(1,'xiaohong',300);
INSERT into t2 values(2,'xiaoming',400);
INSERT into t2 values(3,'xiaoxiao',900);
commit;
2）merge into实现
MERGE INTO t1 USING t2
ON t1.id-t2.id
WHEN MATCHED Then UpDate set t1.sale=t2.sale
When Not Matched
mysql查看所有存储过程When Not Matched then insert values(t2.id,t2.name,t2.sale,sysdate);
--Merge的最后结尾必须是以分号结束的，不能忘了分号谨记：语法严格要求关键字之间只能有⼀个英⽂空格，不能有多余的空格
三、Oracle的merge into语法
1、语法
merge语法是根据源表对⽬标表进⾏匹配查询，匹配成功时更新，不成功时插⼊。
MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
WHEN MATCHED THEN
solr版本查询[UPDATE sql]
WHEN NOT MATCHED THEN
[INSERT sql]
2、实验
1)环境准备
--建表
create table t1(
id number,
name VARCHAR(16),
sale number,
Operatime date);
create table t2(
id number,
name VARCHAR(16),
sale number);
-- 插⼊数据
INSERT into t1 values(1,'xiaohong',1000,sysdate);
INSERT into t1 values(2,'xiaoming',500,sysdate);
INSERT into t2 values(1,'xiaohong',300);
INSERT into t2 values(2,'xiaoming',400);
INSERT into t2 values(3,'xiaoxiao',900);
commit;
2）Oracle merge into实现
merge into t1 using t2 on (t1.id=t2.id) WHEN MATCHED THEN
update set t1.sale=t2.sale
WHEN NOT MATCHED THEN
insert values(t2.id,t2.name,t2.sale,sysdate);
四、mysql的on duplicate key update语法
mysql并没有oracle、mssql的merge into语法，但是有个on duplicate key update语法（不是标准的sql语法）可以实现merge into语法。
ON DUPLICATE KEY UPDATE为Mysql特有语法，使⽤时应多注意主键和插⼊值是否是我们想要插⼊或修改的key、Value。
创建表，注意要有⼀个唯⼀索引 new_code_index， 插⼊或者更新时，以此为标准。另外在⾼并发环
境下，禁⽤insert into …on duplicate key update…，因为会造成mysql主从不⼀致。
1、相关语法
--单条记录下使⽤
--假如t1表的主键或者UNIQUE 索引是a，如果数据库⾥⾯已经存在a=1的记录则更新这条记录的c字段的值为原来值+1，然后返回值为2。
--如果不存在则插⼊a=1,b=2,c=3到数据库，然后返回值为1。
INSERT INTO t1(a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
--多记录下使⽤
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(c);
2、实验
1)环境准备
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论