ORACLE常用的方法总结 纯纯干货

方法总结

1、银行卡脱敏处理

select REPLACE(account, substr(account, 6, 10), '******') from dual

2、获取特定字符串在字符串中出现的次数

select regexp_count(',abc,,,Ad,,ef,', ',') from dual; --结果：7
select length(',abc,,,d,,ef,') - length(replace(',abc,,,d,,ef,', ',','')) from dual; --结果：7

3、instr函数 字符查找函数

其功能是查找一个字符串在另一个字符串中第几次出现的位置。

SELECT instr('syranmo','s') FROM dual; --结果：1
select instr('A01,A02,A03,A04,A05',',',1,3) from dual；--结果：12

(解读：从字符串A01,A02,A03,A04,A05第一位开始，第三个，的位置)

4、substr函数 截取字符串的“内容”

select substr('abcdefg',3,4) from dual; --结果：cdef
select substr('A01,A02,A03,A04,A05',1,instr('A01,A02,A03,A04,A05',',',1,3)-1) from dual;  --结果: A01,A02,A03

(解读：从字符串A01,A02,A03,A04,A05第一位开始，截取11位(详看上方instr)的字符串)

5. uf_split函数 --查看文件类型

select trim(uf_split('1.doc','.',2)) from dual  --结果：.doc

CREATE OR REPLACE FUNCTION UF_SPLIT(
  P_STR VARCHAR2,
  P_SPLIT VARCHAR2,
  P_NTH NUMBER
) RETURN CHAR
IS
  V_STR VARCHAR2(4000);
  V_NTH NUMBER;
  V_P NUMBER;
  V_RESULT VARCHAR2(4000);
BEGIN
  IF P_SPLIT IS NULL THEN
    RETURN(NULL);
  END IF;
  IF P_NTH < 1 THEN
    RETURN(NULL);
  END IF;
  V_STR := P_STR;
  V_NTH := P_NTH;
  WHILE V_NTH > 0 LOOP
    V_P := INSTR(V_STR, P_SPLIT);
    IF V_P = 0 THEN
      V_RESULT := V_STR;
      V_STR := NULL;
    ELSE
      V_RESULT := SUBSTR(V_STR, 1, V_P - 1);
      V_STR := SUBSTR(V_STR, V_P + LENGTH(P_SPLIT));
    END IF;
    V_NTH := V_NTH - 1;
  END LOOP;
  RETURN(V_RESULT);
END UF_SPLIT;

6、去除字符串最后一个字符

select TRIM(',' from '2,3,')  from dual;  --结果：2，3

7. 牛逼切割（拆分字符串）

select uf_split(uf_split('123456&20;6754321&30', ';', level), '&', 1) account_no,
       uf_split(uf_split('123456&20;6754321&30', ';', level), '&', 2) plan_amt
  from dual
connect by level <= length(trim('123456&20;6754321&30')) -
           length(regexp_replace(trim('123456&20;6754321&30'), ';', '')) + 1

仅供参考 这个需要拆分字符串的时候可能用得到，建议再多查查资料(有一阵子没用到了 有点忘记了 但是它确实很牛)

8. regexp_replace函数

SELECT regexp_replace('01234abcde56789','[0-9]','#') AS new_str FROM dual;  --结果：#####abcde#####
--[^abc]-`匹配除了 a、b 或 c 中的其他字母
select REGEXP_REPLACE ('school123', '[^0-9]+', '#') from dual --结果：#123

9. replace函数

select REPLACE(String,from_str,to_str)  from dual

即：将String中所有出现的from_str替换为to_str。

10. REGEXP_SUBSTR 通过搜索正则表达式模式返回从字符串中提取的字符

select regexp_substr(‘aaa,bbb,ccc,ddd,eee’,’[^,]+’,1,3) from dual  --结果: ‘ccc’,即“aaa,bbb,ccc,ddd,eee”被”,”截取后的第三个子字符串

用法(分割去重)：

SELECT DISTINCT REGEXP_SUBSTR ('A,B,B,C,D,E','[^,]+',1,LEVEL) as "分割"
FROM DUAL
CONNECT BY REGEXP_SUBSTR ('A,B,B,C,D,E','[^,]+',1,LEVEL) IS NOT NULL
order by 1` --结果：分割成5行 数据分别为A,B,C,D,E

11、rpad函数从右边对字符串使用指定的字符进行填充

select rpad('tech', 8, '0') from dual --结果：'tech0000'

12、lpad函数从左边对字符串使用指定的字符进行填充

select lpad('abcde',10,'x') from dual; --结果：xxxxxabcde

13、decode函数(好用 类似if else end if)

select decode(expression,value,result1,result2) from dual

14、Oracle中wm_concat与listagg函数的用法及区别

前者返回类型为clob，拼接后的字符用逗号拼接,将多行数据显示为一行
后者为字符串,可定义拼接字符,将多行数据显示为一行

listagg函数

用法: listagg() WITHIN GROUP () 将多行合并成一行

select listagg(trim(title), ',') within group(order by title) title from table;

select listagg(uf_split(REGEXP_SUBSTR('f96b18476401c9e9&20.pdf&.pdf&WF_MSG|a5d66142aedaa3ca&9993_pdf.pdf&.pdf&WF_MSG', '[^|]+', 1, rownum),'&',2),',') within group(order by rownum) as newport 
    from dual connect by rownum <= REGEXP_COUNT('f96b18476401c9e9&20.pdf&.pdf&WF_MSG|a5d66142aedaa3ca&9993_pdf.pdf&.pdf&WF_MSG', '[^|]+');

wm_concat函数

select wm_concat(trim(title)) from table order by title;

15、nvl() 和nvl2()

nvl(apply_no,0) 类型必须相同，nvl2(Leader_File,'|','')
NVL（表达式1，表达式2）
NVL2(表达式1，表达式2，表达式3)

16.oracle 时间格式

select to_char(sysdate,'yyyy-mm-dd hh24:mm:ss') from dual

17. connect by level语句

connect by level是一种生成树形结构的函数，可以用于分析数据的层次关系和顺序

SELECT YEAR-LEVEL   code,  YEAR-LEVEL name
  FROM (SELECT NVL(SUBSTR(MAX(TABLE_NAME), 11, 4) + 1,TO_CHAR(SYSDATE, 'YYYY')) YEAR  FROM USER_TABLES  WHERE TABLE_NAME LIKE 'RSJBXX_HIS%') CONNECT BY  LEVEL <=5

(这里只给一个例子 详情多方查阅资料 好久没用有点忘记了 以后用到再补充)

18. connect by rownum语句

ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列 (强调：先要有结果集)。
简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的

select to_char(sysdate, 'yyyy')-rownum+1,to_char(sysdate, 'yyyy')-rownum+1 from dual connect by rownum<=5
--结果：
2022
2021
2020
2019
2018

19. trunc函数：用于对值进行截断

TRUNC（n1,n2），n1表示被截断的数字，n2表示要截断到那一位。n2可以是负数，表示截断小数点前。注意，TRUNC截断不是四舍五入

select TRUNC(15.79,0) from dual;  --结果：15
select TRUNC(15.79,1) from dual;  --结果：15.7
select trunc(sysdate) from dual;  --结果：2022-02-15

20.比较大小函数 SIGN

sign 判断语句，返回-1，0，1强制转换返回格式

select sign(a-b) from dual

a=10, b=20 结果：-1
a=10, b=10 结果：0

21. oracle 日期格式

to_date(sysdate,'yyyy-MM-dd') 【这句是错的，格式不正确】
与to_char(to_date(sysdate,'yyyy-MM-dd'),'yyyy-mm-dd') 【这句是错的，格式不正确】
与to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')的区别
与to_char(sysdate,'yyyy-mm-dd')的区别

–判断当前日期是周几

select to_char(to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd'),'d') from dual;

oracle 日期格式
（1）to_date(“要转换的字符串”,“转换的格式”) 两个参数的格式必须匹配，否则会报错。
是将字符串转化为日期（DATE）格式，而且转化之后的格式与orcal系统日期参数有关
（2）to_char(日期,“转换格式” ) 即把给定的日期按照“转换格式”转换。
是将日期格式转化为字符串格式

22.把换行符
换成换行效果

把换行符<br/>换成换行效果

select uf_split(replace((replace(trim(name),CHR(10),''##@left@##br/##@right@##'')),CHR(13),''##@left@##br/##@right@##''),CHR(13),1) from dual 

分割出换行符前的字符串

select uf_split(trim(a.zic_name),CHR(10),1) from dual

24.死锁的查询与解锁

select sess.sid,
       sess.serial#,
       lo.oracle_username,
       lo.os_user_name,
       ao.object_name,
       'alter system kill session '''||sess.sid||','||sess.serial#||'''; ' as a,
       lo.locked_mode
  -- gv$locked_object  gv$session  这两个表加上g，查询的是所有节点下的死锁
  from gv$locked_object lo, dba_objects ao, gv$session sess
 where ao.object_id = lo.object_id and lo.session_id = sess.sid ;
 
 alter system kill session '836,7521';

25.字符串拼接(rpad、cast)

CAST函数可以进行数据类型的转换。
CAST函数的参数有两部分，源值和目标数据类型，中间用AS关键字分隔

select cast('admin' as char(21)) from dual;  

26. 判断手机号是否合法

if length(v_phone) !=11 then
   resultStr:='手机号长度不合法！';
   return;
elsif replace(translate(v_phone, '0123456789', '0'), '0', '') IS not NULL THEN
   resultStr:=v_phone || '不是[0-9]的数字序列';
   return;
end if;

27 字符串中想要用逗号作为分割(jqury)

var str1="1,2,3";

var str=str1.split(",");

alert(str[0]);

得到的结果：str[0]==1;str[1]==2;str[3]==3。

结语：暂时只整理到这里 基本上都是比较常用的方法，如果内容有问题的话可以在下面留言 我及时修正。