Oracle之存储过程和MERGE INTO语句

共计 2473 个字符，预计需要花费 7 分钟才能阅读完成。

一、MERGE INTO 语句

1、merge into 语句的功能：我们操作数据库的时候，有时候会遇到 insert 或者 Update 这种需求。我们操纵代码时至少需要写一个插入语句和更新语句并且还得单独写方法效验数据是否存在，这种操作完全可以用 merge into 语句代替，不仅省时省力而且条理更清晰，一个 SQL 语句直接完成插入，如果有相同主键进行更新操作。

使用场景：判断B表和A表是否满足 ON 中条件，如果满足则用 B 表去更新 A 表，如果不满足，则将 B 表数据插入 A 表或者更多的操作。

2、具体 SQL：下边 sql 是我在工作中最常使用的，功能是对接口表（表 B）中通过批次 ID 查到的合同进行对正式表（表 A）插入和更新。除此之外，还可以根据你的想实现功能进行各种条件更新和插入。只 update 或者只 insert，带条件的 update 或带条件的 insert，全插入 insert 实现，带 delete 的 update(觉得可以用 3 来实现)

MERGE INTO 后是更新的表，USING 是对接口表进行筛选，（如果有重复数据，仅选取一行插入，用 ORDER BY 控制）。ON 中是具体的条件（表中标识字段，字段编码）满足执行 WHEN MATCHED THEN 下的语句 
 不满足则执行 WHEN NOT MATCHED THEN 后语句:

MERGE INTO TableA A 
USING (
    (SELECT L.*,
                  ROW_NUMBER() OVER(PARTITION BY T.FLEX_VALUE ORDER BY 1) AS RN
    FROM TABLEB L
    WHERE T.BATCH_ID = #{batchId} ) L
    AND L.RN = 1 )  B
ON (A.FLEX_VALUE = B.FLEX_VALUE)
 WHEN MATCHED THEN
  UPDATE

A.FLEX_VALUE_SET_NAME = B.FLEX_VALUE_SET_NAME,
A.VALIDATION_TYPE = B.VALIDATION_TYPE,

WHEN NOT MATCHED THEN
  INSERT (

A.FLEX_VALUE_SET_NAME = B.FLEX_VALUE_SET_NAME,
A.VALIDATION_TYPE = B.VALIDATION_TYPE)

二、Oracle 的存储过程

1、定义：存储过程(Stored Procedure): 就是一组用于完成特定数据库功能的 SQL 语句集，该 SQL 语句集经过, 编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数, 来调用并执行它，从而完成一个或一系列的数据库操作。

2、创建：Oracle 存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

我在工作中常用的一个存储过程结构如下：

– 存储过程校验信息，三个入参，一个输入批次。输出分别是错误编码，和错误信息。

PROCEDURE VALIDATE_ARCHIVE_ITF(P_BATCHID IN VARCHAR2,
P_FLAG OUT NUMBER,
P_MSG OUT VARCHAR2) IS
CURSOR CMS_ARCHIVE_ITF(BATCHID VARCHAR2) IS
SELECT ROWID,
CONTRACT_NO,
ARCHIVE_STUTAS,
ERROR_INFO,
ARC_TIME
FROM CMS_ARCHIVE_IFT CAI
WHERE CAI.BATCH_ID = BATCHID;
L_ERROR_MSG VARCHAR2(255); – 定义变量错误信息
L_TENANT_ID VARCHAR2(255);– 定义变量租户 ID
L_CONTRACT_SERIAL_NO VARCHAR2(255);– 定义变量
BEGIN
FOR RET IN CMS_ARCHIVE_ITF(P_BATCHID) LOOP
L_ERROR_MSG := NULL;– 给传入三个参数赋默认值
P_FLAG := 1;
P_MSG := NULL;

– 对输入字段非空效验
IF (RET.ARC_TIME IS NULL OR RET.ARC_TIME = ”) THEN
L_ERROR_MSG := L_ERROR_MSG || ‘LAST_UPDATE_DATE 不能为空;’;
END IF;
– 判断非空校验是否成功，不成功继续继续下一个。如果有错误更新接口表
IF L_ERROR_MSG IS NOT NULL THEN
P_FLAG := -99;
UPDATE CMS_ARCHIVE_IFT
SET ERROR_CODE = ’01’, ERROR_MSG = L_ERROR_MSG
WHERE ROWID = RET.ROWID;
CONTINUE;
END IF;
– 如果成功通过条件效验
IF P_FLAG = 1 THEN
BEGIN
INSERT INTO CMS_ARCHIVE_INFO
(TENANT_ID,
CONTRACT_ID,
ARCHIVE_ID)
VALUES
(RET.TENANT_ID,
(SELECT CONTRACT_ID
FROM CMS_CONTRACT_INFO
WHERE CONTRACT_NO = RET.CONTRACT_NO),
SYS_GUID())

EXCEPTION– 异常信息
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 200));
P_FLAG := -99;
P_MSG := SUBSTR(SQLERRM, 1, 200);
END;
END IF;
END LOOP;
END;

：