Oracle之存储过程和MERGE INTO语句

栏目: 数据库 · 发布时间: 8个月前

来源: www.linuxidc.com

本文转载自:https://www.linuxidc.com/Linux/2018-12/155659.htm,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有。

一、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;

Linux公社的RSS地址https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址: https://www.linuxidc.com/Linux/2018-12/155659.htm


以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网

为你推荐:

相关软件推荐:

查看所有标签

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

大话存储Ⅱ

大话存储Ⅱ

张冬 / 清华大学出版社 / 2011-5 / 99.00元

《大话存储2:存储系统架构与底层原理极限剖析》内容简介:网络存储是一个涉及计算机硬件以及网络协议/技术、操作系统以及专业软件等各方面综合知识的领域。目前国内阐述网络存储的书籍少之又少,大部分是国外作品,对存储系统底层细节的描述不够深入,加之术语太多,初学者很难真正理解网络存储的精髓。《大话存储2:存储系统架构与底层原理极限剖析》以特立独行的行文风格向读者阐述了整个网络存储系统。从硬盘到应用程序,对......一起来看看 《大话存储Ⅱ》 这本书的介绍吧!

RGB转16进制工具
RGB转16进制工具

RGB HEX 互转工具

SHA 加密
SHA 加密

SHA 加密工具

RGB CMYK 转换工具
RGB CMYK 转换工具

RGB CMYK 互转工具