Oracle Insert All、merge into 批量新增与修改、regexp_like 、regexp_replace 正则、with as 虚拟视图

目录

Oracle Insert All 批量插入

oracle merge into 合并新增与更新

regexp_like 正则匹配

regexp_replace 正则替换

with as 虚拟视图

---

脚本：部门及其his与log表

Oracle 函数官网文档：https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm。

Oracle Insert All 批量插入

1、假如需要将 dept 的数据备份到 dept_his 和 dept_log 表，最简单的方式如下所示，然后它并不严谨，特别是在生产上的时候，因为在第一次 Insert 的时候，可能 dept 表中的数据已经发生了变化，从而导致 his 和 log 表并不一样。

insert into dept_his(deptno,dname,loc) select deptno,dname,loc from dept;
insert into dept_log(deptno,dname,loc) select deptno,dname,loc from dept;

2、Oracle 的 insert all 用于把同一批数据插入到不同的表中。

3、insert first 与 insert all 支持 when then 条件插入，都会对 select 的每一行结果判断是否满足条件。

insert first：对于每一行数据，只插入到第一个 when 条件成立的表，不继续检查其他条件。   
insert all：对于每一行数据，对每一个 when 条件都进行检查，如果满足条件就执行插入操作。 

4、insert all 多表插入限制条件：

 1. 只能对表执行多表插入语句，不能对视图或物化视图执行；
 2. 不能对远端表执行多表插入语句；
 3. 不能使用表集合表达式；
 4. 不能超过999个目标列；
 5. 在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时，多表插入语句不能并行执行；
 6. 多表插入语句不支持执行计划稳定性；
 7. 多表插入语句中的子查询不能使用序列。

示例1：将 dept 的数据备份新增到 his 和 log 表

-- 将 dept 的数据备份新增到 his 和 log 表
-- 对于 select 的每一行结果都会 into 到每张目标表中, into 后面没有写 values，此时 select 的列相当于 values
insert all
       into dept_his(deptno,dname,loc)
       into dept_log(deptno,dname,loc)
select deptno,dname,loc from dept;

示例2：insert first 与 insert all 的用法区别

-- 将部门编码小于30的数据备份新增到 his 表; 大于等于30且小于60的备份新增到log表
-- 对于 select 的每一行结果进行 when 条件匹配，匹配上，则进入子块，且不继续往后判断，直接进入下一条数据
-- 当 select 查询的列和 into 目标表的列一致时，则都可以省略，否则需要明确指定，values 获取的值来自 select 
insert first
  when deptno <30 then
    into dept_his
  when deptno < 60 then
    into dept_log
select * from dept;
 
-- 将部门编码小于30的数据备份新增到 his 表; 小于60的备份新增到log表
-- 对于 select 的每一行结果进行 when 条件匹配，匹配上，则进入子块，且继续往后判断剩余的 when 条件
-- 当 select 查询的列和 into 目标表的列一致时，则都可以省略，否则需要明确指定，values 获取的值来自 select 
insert all
  when deptno <30 then
    into dept_his(deptno,dname,loc) values(deptno,dname,loc)
  when deptno < 60 then
    into dept_log(deptno,dname) values (deptno,dname)
select deptno,dname,loc from dept;

示例3：批量新增插入(多用于程序中)

-- 批量新增插入
-- select 查询有几条结果，则全部 into 执行几次，此时 values 不再从 select 中取值
insert all
       into dept_his(deptno,dname,loc) values(1,'预算','北京')
       into dept_his(deptno,dname,loc) values(2,'支付','上海')
       into dept_his(deptno,dname,loc) values(3,'指标','深圳')
select 1 from dual;

merge into 合并新增与更新

1、merge into 合并语法如下：

MERGE INTO target_table A USING source_table B ON search_condition
WHEN MATCHED THEN
	UPDATE SET A.col1 = B.col1, A.col2 = B.col2,... WHERE xxx [DELETE WHERE]
WHEN NOT MATCHED THEN
	INSERT (A.col1,A.col2,...) values(B.value1,B.value2,...) WHERE xxx;

2、merge into 执行过程：对于 source_table(来源表) 中的每一条记录，结合 target_table(目标表) 判断该记录是否满足 search_condition 条件，如果满足：则执行 MATCHED 部分的 update 更新语句；如果不满足：则执行 NOT MATCHED 部分的 insert 新增语句；

3、merge into 注意事项：

3.1、on 条件必须唯一定位来源表中单条记录，即目标表的记录在来源表中最多只能有1条数据匹配，否则报错：ORA-30926：无法在源表中获得一组稳定的行。

目标表与来源表可以多对一，但是绝不能一对多。

3.2、update 不能更新 ON 条件子句中引用的列，即目标表在 on 条件中使用了的字段，都不能再进行更新操作。

4、update 语句可以指定一个可选的带 where 条件的 delete 语句，用来删除目标表中符合  search_condition，且在 update 操作之后，满足 delete where 条件的数据。

示例1：合并更新与插入

-- 将部门表 dept 中编号大于20的数据备份到 deot_his 历史表，已经存在时更新，不存在时新增.
-- MATCHED THEN 与 NOT MATCHED THEN 可以同时存在，也可以单独存在
MERGE INTO dept_his A
USING (SELECT * FROM dept t where t.DEPTNO > 20) B
ON (A.deptno = B.deptno)
WHEN MATCHED THEN
  UPDATE SET A.dname = B.dname, A.loc = B.loc
WHEN NOT MATCHED THEN
  INSERT (A.deptno, A.dname, A.loc) VALUES (B.deptno, B.dname, B.loc);

示例2：合并更新并删除

-- 将部门表 dept 的数据更新到 his 表，如果更新后的部门名称是以 'S' 开头，则删除 his 表中的此条数据
MERGE INTO dept_his A USING dept B ON (A.deptno = B.deptno)
WHEN MATCHED THEN
  UPDATE SET A.dname = B.dname, A.loc = B.loc DELETE WHERE A.Dname like 'S%';

示例3：无条件 insert

-- 将部门编码大于20的数据全部备份插入到 his 表，因为 1=0 条件不成立，所以来源表中的每条数据都将插入到 his 表
-- 如果 values 中的列与目标表一致，则 insert 后面的列可以省略不写
MERGE INTO dept_his A USING (SELECT T.* FROM dept t where t.deptno > 20) B ON (1=0)
WHEN NOT MATCHED THEN
  INSERT VALUES (B.deptno, B.dname, B.loc);

示例4： where 条件过滤说明，USING 左侧的目标表、右侧的来源表都可以在 on 过滤前先带条件进行过滤，然后 on 条件中可以继续带条件过滤，最后 UPDATE、INSERT 语句的后面也可以带条件过滤。原则就是尽量提前缩小结果集，为后续操作减去压力，能提起带条件过滤的，尽量提起过滤。

-- 将 emp 表中部门号大于 20，且薪水小于 3k的员工，奖金加上 100元.
-- 方式1-推荐
MERGE INTO (SELECT T.* FROM emp t where t.sal < 3000) E USING (SELECT d.deptno FROM dept d where d.deptno > 20) B
ON (e.deptno = b.deptno) WHEN MATCHED THEN UPDATE SET e.comm = nvl(e.comm, 0) + 100;
-- 方式2
MERGE INTO emp e USING dept B ON (e.sal < 3000 and B.deptno > 20 and e.deptno = b.deptno)
WHEN MATCHED THEN UPDATE SET e.comm = nvl(e.comm, 0) + 100;
-- 方式3
MERGE INTO emp e USING dept B ON (e.deptno = b.deptno)
WHEN MATCHED THEN UPDATE SET e.comm = nvl(e.comm, 0) + 100 where b.deptno > 20 and e.sal < 3000;

sql/oracle/merge into 同表不同条件更新.sql。

sql/oracle/merge into 多条件批量更新.sql。

regexp_like 正则匹配

1、REGEXP_LIKE 函数语法：REGEXP_LIKE (expression, pattern [, match_parameter])

2、参数 expression：字符表达式，例如列或字段。可以是 VARCHAR2，CHAR，NVARCHAR2，NCHAR，CLOB 或 NCLOB 数据类型。

3、参数 pattern：正则表达式匹配信息，这与前端 JS 还是后台 Java 的正则规则是一样的。Java SE 正则表达式 API Pattern 与 Matcher._蚩尤后裔的博客-CSDN博客

4、参数 match_parameter - 可选：允许修改 REGEXP_LIKE 条件的匹配行为。可以是以下的组合：

5、常用内置规则：

6、常用操作：

-- 查询 emp 表中以 F开头后面跟着 1-3 位数字的列信息
select *  from user_tab_columns where table_name = 'EMP' AND REGEXP_LIKE(COLUMN_NAME, '^F\d{1,3}$');
 
-- 查询表名中含有数字的表
SELECT T.* FROM user_tables t where regexp_like(t.TABLE_NAME, '[[:digit:]]');
 
-- 查询员工姓名以'S'开头，'H'结束的记录并且长度是5位
select * from emp t where regexp_like(t.ename,'^S...H$');
-- 查询员工姓名以'K'开头，后面跟着的大写字母不超过1到3位
select * from emp t where regexp_like(t.ename,'^K[A-Z]{1,3}$');
 
-- 查询员工姓名以'J'开头，'S'结束、中间3位大写字母
select * from emp where regexp_like(ename,'^J[A-Z]{3}S$');
-- 使用字符集实现：  [[:upper:]]  任何大写字母
select * from emp where regexp_like(ename,'^J[[:upper:]]{3}S');
-- 查询value中不是纯数字的记录
select * from emp t where not regexp_like(t.ename,'^[[:alnum:]]+$');
-- 查询value中不包含任何数字的记录。
select * from fzq where regexp_like(value,'^[^[:digit:]]+$');
--查询以12或者1b开头的记录.不区分大小写。
select * from fzq where regexp_like(value,'^1[2b]','i');
--查询以12或者1b开头的记录.区分大小写。
select * from fzq where regexp_like(value,'^1[2B]');
-- 查询数据中包含空白的记录。
select * from fzq where regexp_like(value,'[[:space:]]');
--查询所有包含小写字母或者数字的记录。
select * from emp t where regexp_like(t.ename,'^([A-Z]+|[0-9]+)$');
--查询任何包含标点符号的记录。
select * from fzq where regexp_like(value,'[[:punct:]]');

示例1：如果新增的是暂存数据，且证件号码长度是 15、18位，则入库前自动进行解密。

regexp_replace 正则替换

1、regexp_replace 正则替换函数用于通过正则表达式来进行匹配替换，默认情况下，每次匹配到的正则，都替换为 replace_string，返回的字符串与 source_char 字符集相同。如果source_char为非LOB类型，则返回varchar2数据类型，如果为LOB类型，则返回CLOB类型，该函数符合POSIX正则和Unicode正则。

2、格式：regexp_replace(source_char,pattern,replace_string,position,occurrence,match_parameter);

-- 中国 替换为 Chinese：Chinese人民说Chinese话，会Chinese功夫
-- 此时和 replace 效果一样
select regexp_replace('中国人民说中国话，会中国功夫', '中国', 'Chinese')  from dual;
 
-- 将xxx.xxx.xxxx格式的号码重新格式化为（xxx）xxx-xxxx
-- 其中.是特殊字符，需要转义
-- 输出：(515) 123-4567
SELECT REGEXP_REPLACE('515.123.4567', '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') "美国座机"
  FROM dual;
  
-- 检查字符串，查找两个或多个空格。Oracle将每次出现的两个或多个空格替换为一个空格。
-- 输出：500 Oracle Parkway, Redwood Shores, CA
SELECT REGEXP_REPLACE('500   Oracle     Parkway,    Redwood  Shores, CA',  '( ){2,}', ' ') "REGEXP_REPLACE"  FROM DUAL;
 
 
-- 将全部的数字替换为 *：太宗*****abcde*****
select regexp_replace('太宗01234abcde56789', '[0-9]', '*') a,
       regexp_replace('太宗01234abcde56789', '[[:digit:]]', '*') b
  from dual;
 
-- 去掉其中的全部字母：高宗0123456789。
select regexp_replace('高宗01234abcde56789ABC。', '[a-zA-Z]') a,
       regexp_replace('高宗01234abcde56789ABC。', '[[:upper:]]|[[:lower:]]') b
  from dual;

-- 使用括号可以取值
-- ’+'在正则里有定义，需转义
-- 输出：(+86)0738-786531    138-1111-2222
select regexp_replace('+86 0738786531',
                      '(\+[0-9]{2})( )([0-9]{4})([0-9]{1,9})',
                      '(\1)\3-\4') as "座机号",
       regexp_replace('13811112222',
                      '([0-9]{3})([0-9]{4})([0-9]{4})',
                      '\1-\2-\3') as "手机号"
  from dual;
 
-- 将字符用空格分隔
-- 输出：高 祖 0 1 2 3 4 a b c d e 
select regexp_replace('高祖01234abcde', '(.)', '\1 ') from dual;
 
--从第8个值开始（包含自己），将所有数字、大/小写字母替换为*
--输出：1867388****
select regexp_replace('18673886425','[0-9a-zA-Z]','*',8) as new_str from dual;
 
-- 通过i参数，不区分大小写进行匹配
-- 将全部字母替换为 *，输出：中国*******12345.
select regexp_replace('中国abcdefg12345.', '[A-Z]', '*', 1, 0, 'i') from dual;
 
-- 通过n参数使 ’ . ’ 可以匹配换行符，实现合并
select regexp_replace('a
  b
  c',
                      '([a-z])(.)', '\1', 1, 0,  'n')
  from dual;
 
-- 第一条，匹配的是 ‘a a，  第二条，匹配的是 ‘aa’
-- 输出：a0 a bb b b         0 a a bb b b
select regexp_replace('aa a a bb b b', 'a a', '0') a,
       regexp_replace('aa a a bb b b', 'a a', '0', 1, 0, 'x') b
  from dual;

-- 替换手机号码的中间4位，输出：186****6425
select regexp_replace(
          regexp_replace(
              regexp_replace(
                  regexp_replace('18687976425','.','*',4,1)
             ,'.','*',4,2)
           ,'.','*',4,3)
        ,'.','*',4,4) as new_str from dual;
 
 
-- -- 替换手机号码的中间4位，输出：186****6425
SELECT substr(regexp_replace('18687976425', '.', '*', 4), 1, 7) || substr('18687976425', 8, 11) FROM dual;

with as 虚拟视图

1、with table as 是Oracle 9i新增语法，用于将查询中公共部分提取出来为虚拟视图，访问虚拟视图减少对原始表的io操作，从而达到优化性能和语法清晰的作用，'一次解析，多次使用'。

-- 示例1：一次解析，多次使用
-- 注意1：with as 语句最后面不能加分号，否则报错：ORA-00928: 缺失 SELECT 关键字
-- 注意2：不能只定义with..as语句，定义了就必须要使用它，否则报错：ORA-00928: 缺失 SELECT 关键字
with temp as
         (select '10001' as province_code from dual)
select case
           when (select * from temp) = '10001' then 'equals'
           when (select * from temp) = '10002' then 'not equals'
           else 'unknown'
        end is_equals
from dual;
 
--示例2：with as 非常适合在 union 语句中
with temp1 as (select 2 sex, '张三' name from dual),
     temp2 as (select 1 sex, '李四' name from dual),
     temp3 as (select 2 sex, '王五' name from dual)
select * from temp1
union all
select * from temp2
union all
select * from temp3;
 
--示例3：前面定义的 with..as 语句可以在后面定义的with..as语句使用
with temp1 as (select 2 sex, '张无忌' name from dual),
     temp2 as (select 1 sex, '赵敏' name from dual),
     temp3 as (select * from temp2)
select * from temp1
union all
select * from temp2
union all
select * from temp3;
 
--示例4：使用with as语句来创建表
--使用虚拟字段时，新表字段的长度就是虚拟字段建表的时的长度
create table temp as
with temp1 as (select 2 sex, '张三' name from dual),
     temp2 as (select 1 sex, '李四' name from dual),
     temp3 as (select 2 sex, '敏敏特穆尔' name from dual)
select * from temp1
union all
select * from temp2
union all
select * from temp3;
 
--示例5：使用with as语句来插入数据
insert into temp
with temp1 as (select 2 sex, '张无忌' name from dual),
     temp2 as (select 1 sex, '赵敏' name from dual)
select * from temp1
union all
select * from temp2;