数据库
Oracle

系统函数篇

# Oracle

# 正则替换函数REGEXP_REPLACE（不规则日期修改）

01-1月 -19

-- 查看NLS_TIMESTAMP_FORMAT和NLS_DATE_LANGUAGE参数和SYSTIMESTAMP 数据格式
select * from v$nls_parameters; 
SELECT SYSTIMESTAMP FROM DUAL;
-- 确定NLS_TIMESTAMP_FORMAT格式
-- alter session set NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH.MI.SS.FF8 AM';  -- 如果参数NLS_TIMESTAMP_FORMAT格式不是DD-MON-RR HH.MI.SSXFF AM首先要调整该参数
-- 查看NLS_DATE_LANGUAGE是SIMPLIFIED CHINESE还是AMERICA，如果是AMERICA需要修改成SIMPLIFIED CHINESE，
ALTER SESSION SET nls_date_language='SIMPLIFIED CHINESE';

SELECT TO_DATE('01-1月 -19', 'DD-MON -RR') FROM DUAL;

2021-1-02

-- 1. 可以直接转日期 
SELECT to_date('2021-1-02', 'yyyy-MM-dd hh24:mi:ss') FROM dual;

-- 2. 当字符串处理
select CASE WHEN REGEXP_LIKE('2021-1-02', '^(\d{4})[-|/](\d{1})[-|/](\d{1})$') THEN REGEXP_REPLACE('2021-1-02', '^(\d{4})[-|/](\d{1})[-|/](\d{1})$', '\1-0\2-0\3') 
            WHEN REGEXP_LIKE('2021-1-02', '^(\d{4})[-|/](\d{1})[-|/](\d{2})$') THEN REGEXP_REPLACE('2021-1-02', '^(\d{4})[-|/](\d{1})[-|/](\d{2})$', '\1-0\2-\3')
            WHEN REGEXP_LIKE('2021-1-02', '^(\d{4})[-|/](\d{2})[-|/](\d{1})$') THEN REGEXP_REPLACE('2021-1-02', '^(\d{4})[-|/](\d{2})[-|/](\d{1})$', '\1-\2-0\3')
            ELSE '2021-1-02' END
from dual;

-- 转为日期格式，抽取出年、月、日，左补全 位数
select extract(YEAR from date'2022-8-11') || LPAD(extract(MONTH from date'2022-8-11'), 2, '0') || LPAD(extract(DAY from date'2022-8-11'), 2, '0') year from dual;

# 聚合函数lag/lead

lead(value_expr [,offset][,default]) over([query_partition_clause] order by Order_by_clause)

lag(exp_str,offset,defval) over()
exp_str 是要做对比的字段
offset 是exp_str字段的偏移量 比如说 offset 为2 则 拿exp_str的第一行和第三行对比，第二行和第四行，依次类推，offset的默认值为1！
defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。

# 聚合函数wm_concat

注：Oracle 19c中，wm_concat失效。wm_concat函数是oracle的非公开函数，在新版的oracle中不支持该函数。
解决方法：

创建 wm_concat 函数，参考连接：https://blog.csdn.net/sun2012930/article/details/111712882

使用 listagg(多行转单行的列,分隔符) within group(order by 排序字段) 写法替换（返回类型为字符串，长度最大为4000）

使用 xmlagg(xmlparse( content(多行转单行的列) ) order by 排序字段).getclobval() 写法替换

多行变一行

类似于 MySQL 中的 GROUP_CONCAT GROUP_CONCAT( [distinct] 要连接的字段 [ORDER BY 排序字段 ASC/DESC ] [separator '分隔符'] )
类似于 Hive 中的 concat_ws + collect_list（collect_set）效果 concat_ws('_', collect_set(id))

# 单行函数L[R]PAD

语法
LPAD(string,padded_length,[ pad_string]) :从左开始填充,L:left 左,PAD:pad 填充
RPAD(string,padded_length,[ pad_string]) :从右开始填充,R:right 右,PAD:pad 填充

解释
string：原数据，即要被填充的数据；
padded_length：填充后的长度；
pad_string：填充字符串 可选填，如果不填就粘贴空格

# 单行函数MD5加密

select utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => 'test')) from dual

# SYS.UTL_MATCH.edit_distance_similarity 文本相似度计算

点击查看

过程梳理：

由于有两个数据量过万的表数据进行对比，且编码不一致不能进行比对，只能通过同一区划，相似名称进行比对，确认一对一关系。

确定思路是把数据分成多部分，能确认一对一关系的先插入结果表中。

先能想到的是通过等值关联取能关联上的先插入到结果表。

后通过instr(str1, str2) > 0关联，将此部分数据插入到结果表。

后由于instr只能找出连续子串，非连续的简称一类并不能解决，采用自定义函数通过遍历短字符串的每个字符，到长字符串中查找如果有找不到的字符，返回-1；若全能找到返回短串的长度。

后由于有些名称有错别字或其他问题，上面的自定义函数并不能解决，故考虑判断字符串的相似度，Oracle自带判断文本相似度函数，故用其作为关联条件。

SELECT  A.AGENCY_ID,A.AGENCY_CODE,A.AGENCY_NAME,A.MOF_DIV_CODE,A.MOF_DIV_NAME,B.GUID,B.NAME,B.CODE,PROVINCE 
 FROM  YTHDWXX A
 JOIN  DZXTDWXX B
   ON  SYS.UTL_MATCH.edit_distance_similarity(a.agency_name,b.name) > 80
  AND  MOF_DIV_CODE = rpad(PROVINCE, 9, 0)

# oracle实现数据炸裂效果

--oracle根据分隔符将一行拆分为多行
with tmp as --临时数据集
(select '1,2,3' val
    from dual
  union all
  select '4,5,6' val
    from dual)
select regexp_substr(t.val, '[^,]+', 1, t2.lv)--截取对应行数的数据
  from tmp t,
      (select level lv--生成行数序列数据 1到最大行数
          from (select max(regexp_count(a.val, '[^,]+', 1)) r_count--计算数据集中拆分后最大的行数
                  from tmp a) b
        connect by level <= b.r_count) t2
where regexp_substr(t.val, '[^,]+', 1, t2.lv) is not null-- 排除掉空的数据

# oracle行列转换

# 行转列

PIVOT

SELECT * FROM （数据查询集）
PIVOT
(
 SUM(Score/*行转列后 列的值*/) FOR 
 coursename/*需要行转列的列*/ IN (转换后列的值)
)

-- 通俗理解就是把一列拆成了多列，被拆的列不能再被select？

通过 group by + case when 判断实现

# 列转行

unpivot

select 字段 from 数据集
unpivot（自定义列名/*列的值*/ for 自定义列名 in（列名））

利用union all 进行拼接

# 自定义函数(存储过程)

用于匹配简称

-- 一个字符串包含另一个字符串中的所有字符
create or replace function checks(v_a varchar2,v_b varchar)
return number
as
  num number;
  cou number;
  j number;
  index1 number;
begin
  num := -1;
  cou:=0;
  j := 0;
  index1 := 1;
  for i in 1..length(v_b) loop
     j := instr( substr(v_a,index1,length(v_a)), substr(v_b,i,1) );
     if j > 0 then
      cou:=cou+1;
      index1 := index1 + j;
     else
      return num;
     end if;
  end loop;
  return cou;
  --dbms_output.put_line(cou||'    '||length(v_b));
end;

# MERGE INTO 语法

MERGE INTO是Oracle SQL中用于将一个表中的数据合并到另一个表中的命令。该命令可以根据指定的条件来确定在目标表中是插入还是更新记录。

MERGE INTO target_table
USING source_table
ON (condition)
WHEN MATCHED THEN
  UPDATE SET target_column = source_column
WHEN NOT MATCHED THEN
  INSERT (target_column1, target_column2, ...) VALUES (source_column1, source_column2, ...)

其中

target_table是要合并到的目标表；
source_table是要从中获取数据的源表；
condition是用于匹配目标表和源表的条件；
UPDATE SET语句用于更新目标表中已有的记录；
INSERT语句用于将源表中不存在的记录插入到目标表中。

# 不常用语法

# 触发器

-- 向另一个数据库同步表数据
CREATE OR REPLACE TRIGGER "触发器名"
  after insert or update or delete on 表名
    for each row
begin
  if inserting then
    insert into 表名@INNERDB
      (ID,
       CODE,
       NAME)
    values
      (:new.ID,
       :new.CODE,
       :new.NAME);
  elsif updating then
    delete 表名@INNERDB where ID = :old.ID;
insert into 表名@INNERDB
(ID,
 CODE,
 NAME)
values
    (:new.ID,
     :new.CODE,
     :new.NAME);
elsif deleting then
    delete 表名@INNERDB where ID = :old.ID;
end if;
end;

# 出包含 CLOB 字段的脚本

条件允许的话还是用 Kettle、DataX等迁移工具，没有这个麻烦事

-- 1. 使用 plsql 工具查询该脚本（适用于一定长度的）
	SELECT q_id,
         q_code,
         q_title,
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 1, 3000)) <> 0 THEN 'TO_CLOB(''' || SUBSTR(Q_TABLE, 1, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 3001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 3001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 6001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 6001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 9001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 9001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 12001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 12001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 15001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 15001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 18001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 18001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 21001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 21001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 24001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 24001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 27001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 27001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 30001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 30001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 33001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 33001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 36001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 36001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 39001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 39001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 42001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 42001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 45001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 45001, 3000) || ''')' ELSE NULL END ||
         CASE WHEN LENGTH(SUBSTR(Q_TABLE, 48001, 3000)) <> 0 THEN '||TO_CLOB(''' || SUBSTR(Q_TABLE, 48001, 3000) || ''')' ELSE NULL END
  FROM table_name t 
  order by sort_index;
-- 2. 使用导出功能导出SQL脚本
-- 3. 替换 SQL 脚本中的内容
-- 3.1 将'TO_CLOB(''替换为TO_CLOB('
-- 3.2 将'')||TO_CLOB(''替换为')||TO_CLOB('
-- 3.3 将'')',替换为'),
-- 4 替换特殊字符& 为 ' || chr(38) || '

编辑

上次更新: 2025/04/21, 15:52:05

← Oracle相关知识杂记与MySQL语法区别→