mynotes/oracle/Oracle函数.md

自定义函数

CREATE [OR REPLACE] FUNCTION '定义的函数名称'('参数名1' '参数类型','参数名2' '参数类型', ...) RETURN '返回值类型'
AS/IS
返回值形参 形参类型实例化;
变量1  变量类型;
BEGIN
方法体
（其中用到if判断的话,每一个if对应一个end if,出现几次if就会有几个end if;)
RETURN (接收过实参的)返回值形参
[EXCEPTION '异常处理部分']
END;

-- 创建表
create table city(
    id integer,
    code varchar2(50),
    name varchar2(50)
);
--插入数据
insert into city values (1,'qingdao', '');
insert into city values (2,'beijing', '');
insert into city values (3,'shanghai', '');
insert into city values (4,'linyi', '');

-- drop table city;

多条件 decode 函数

语法：

decode(条件, 值1, 返回值1, 值2, 返回值2, … 值n, 返回值n, 缺省值)

该函数逻辑如下：

IF 条件=值1 THEN
　　　　RETURN(返回值1)
ELSIF 条件=值2 THEN
　　　　RETURN(返回值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(返回值n)
ELSE
　　　　RETURN(缺省值)
END IF

例子

select t.id, t.code, 
    decode(t.code, 'qingdao', '青岛', 'beijing', '北京', 'shanghai', '上海', '不知名城市') as name 
from city t;

-- 等同于
select t.id, t.code,
    case t.code
        when 'qingdao' then '青岛'
        when 'beijing' then '北京'
        when 'shanghai' then '上海'
        else '不知名城市'
    end as name
from city t;

日期函数

--本月第一天
SELECT TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE), -1) + 1) AS 本月第一天 FROM DUAL;
--下个月第一天
SELECT TRUNC(LAST_DAY(SYSDATE) + 1) AS 下个月第一天 FROM DUAL;

-- 本年第一天
SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL;
-- 本年最后一天
SELECT LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'Y'),11)) FROM DUAL

截取字符串 SUBSTR

-- 语法：
SUBSTR(string, start, [length])

查找字符串位置 INSTR

-- 语法：
INSTR(string,child_string,[start],[show_time])
-- start：可选项,开始位置,默认从1开始。如果为负数,则从右向左检索。
-- show_time：可选项,表示子字符串第几次出现在源字符串当中,默认第1次,负数则报错。

字符串长度 length

-- 查询字符串长度
select length(t.code) from city ;
-- 查询最大长度
select max(length(t.code)) from city;

替换函数 replace

-- 语法
replace(字段名,旧值, 新值)
--示例
update city set name = replace(name,'北京','首都') where name like '%北京%';

替换函数 translate

translate 函数是 replace 的超集

--语法：
translate(string, from_str, to_str);
-- from_str 查找字符
-- to_str 替换字符

-- 1. 字符一一对应,一一替换
select translate('13687657303', '123', 'abc') as n from dual;
-- ac687657c0c

-- 2. 查找字符没有对应的替换字符,将所有该查找字符去掉
select translate('13687657303', '123', 'ab') as n from dual;
-- a6876570

-- 3. 查找字符对应多个替换字符,以第一个对应替换字符为准
select translate('13687657303', '1233', 'abcd') as n from dual;
-- ac687657c0c

补位函数 lpad 和 rpad

oracle函数中有不足位数补空格的函数,LPAD和RPAD分别是左补位和右补位

-- 语法,填充字符默认空格
lpad(字段名,总位数, 填充字符)
-- 例子,结果是00056,即补足五位
select lpad('56',5,'0') from dual

拼接函数 wm_concat

wm_concat函数实现行转列功能,即将查询出的某一列值使用逗号进行隔开拼接,成为一条数据。

select wm_concat(t.ename) names from city t;

字符串拆分为多行

-- 语法模式：
SELECT REGEXP_SUBSTR(string,                     
         '[^特定字符]+',                     
         1,                     
         LEVEL,                     
         'i') as 起个别名 
FROM DUAL 
CONNECT BY LEVEL <= LENGTHB(TRANSLATE(string, '特定字符' || string, '特定字符')) + 1;

-- 示例：
SELECT REGEXP_SUBSTR('1,2,3',                     
         '[^,]+',                     
         1,                     
         LEVEL,                     
         'i') as f 
FROM DUAL 
CONNECT BY LEVEL <= LENGTHB(TRANSLATE('1,2,3', ',' || '1,2,3', ',')) + 1;

语法：REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

• string：需要进行正则处理的字符串
• pattern：进行匹配的正则表达式
• position：起始位置,从字符串的第几个字符开始正则表达式匹配（默认为1） 注意：字符串最初的位置是1而不是0
• occurrence：获取第几个分割出来的组（分割后最初的字符串会按分割的顺序排列成组）
• modifier：模式i不区分大小写进行检索；模式c 区分大小写进行检索。默认为c。针对的是正则表达式里字符大小写的匹配