MERGE 用法

1、不带输出的
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[InsertShiGongJiao] 
@number varchar(100),
@companyindex int,
@lineid int,
@stationid int,
@direct int,
@stationname nvarchar(50),
@stationindex int,
@distance int,
@type int
asMERGE dbo.t_BusArrive_ShiGongJiao AStarget
    USING (SELECT @number n)  ASsource
    ON (target.number=source.n)
    WHEN MATCHED THEN
    UPDATE SET companyindex=@companyindex,
            lineid=@lineid,
            direct=@direct,
            stationname=@stationname,
            stationid=@stationid,
            stationindex=@stationindex,
            distance=@distance,
            type=@type,
            updatetime=GETDATE()
    WHEN NOT MATCHED THEN
    INSERT(number,lineid,companyindex,direct,stationid,stationname,distance,stationindex,updatetime,type)
VALUES(    @number,@lineid,@companyindex,@direct,@stationid,@stationname,@distance,@stationindex,GETDATE(),@type);
2、带输出的

ALTER PROCEDURE [dbo].[FG_BM_MakeLSH_test](
@TYPE VARCHAR(20),
@mc    VARCHAR(12), 
@lsh    INTOUTPUT) 
ASMERGE FGHIS5_ZY.dbo.住院_编码流水号 AST 
USING(SELECT @mc n,@TYPE t) ASsource
ON (T.名称=source.n AND T.分类=source.t)
WHEN MATCHED THEN 
UPDATE SET 流水号 = 流水号 + @lsh,更新时间=GETDATE() 
WHEN NOT MATCHED THEN 
INSERT(分类,名称,流水号,创建时间)VALUES (@TYPE,@mc,0,GETDATE())
OUTPUT Inserted.流水号 as 流水号;

Oracle使用：

create or replace PROCEDURE FG_BM_MakeLSH
(
v_type VARCHAR2 ,
v_mc VARCHAR2,
v_num number,
cur_OUT out sys_refcursor
)
AS
begin
--2015-12-02 CPOE_ly创建仅在测试时使用，正式环境请自行编写
MERGE into C##FGHIS5_ZY.住院_编码流水号 T
USING ( SELECT v_mc n ,v_type m FROM dual ) source
ON ( T.名称 = source.n
AND T.分类 = source.m
)
WHEN MATCHED THEN
UPDATE SET
T.流水号 = T.流水号 + v_num ,
T.更新时间 = sysdate
WHEN NOT MATCHED THEN
INSERT ( 分类, 名称, 流水号, 创建时间 )
VALUES ( v_type, v_mc, v_num, sysdate );
open cur_OUT for select 流水号 from C##FGHIS5_ZY.住院_编码流水号 where 名称=v_mc and 分类=v_type;
end;