Oracle之存储过程和MERGE INTO语句

栏目: 数据库 · 发布时间: 6年前

内容简介:1、merge into语句的功能:我们操作数据库的时候,有时候会遇到使用场景:判断B表和A表是否满足ON中条件,如果满足则用B表去更新A表,如果不满足,则将B表数据插入A表或者更多的操作。2、具体SQL:下边sql是我在工作中最常使用的,功能是对接口表(表B)中通过批次ID查到的合同进行对正式表(表A)插入和更新。除此之外,还可以根据你的想实现功能进行各种条件更新和插入。只update或者只insert,带条件的update或带条件的insert,全插入insert实现,带delete的update(觉

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


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

查看所有标签

猜你喜欢:

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

创业的艺术2.0

创业的艺术2.0

〔美〕盖伊·川崎 / 刘悦、段歆玥 / 译言·东西文库/电子工业出版社 / 2016-9 / 68

“创业者导师”——盖伊•川崎的《创业的艺术2.0》被阿丽亚娜•赫芬顿评为“终极的创业手册”。无论您是企业家、小企业主、企业开拓者还是非盈利组织的领导人,都可以让你的产品、服务或理念获得成功。 盖伊选取了不用角度,探索前十年商界的巨大变化,并寻求解决之道。曾经所向披靡的市场巨头深陷水深火热之中,社交媒体也取代了人际关系和广告,成为营销推广的主要渠道。众筹也成为广大投资者的可行之举。“云”更是每......一起来看看 《创业的艺术2.0》 这本书的介绍吧!

Base64 编码/解码
Base64 编码/解码

Base64 编码/解码

html转js在线工具
html转js在线工具

html转js在线工具

UNIX 时间戳转换
UNIX 时间戳转换

UNIX 时间戳转换