这里写自定义目录标题
substr函数格式 (俗称：字符截取函数)
转自：https://www.cnblogs.com/dshore123/p/7805050.html
格式1： substr(string string, int a, int b);
格式2：substr(string string, int a) ;
解释：
格式1：
1、string 需要截取的字符串
2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
3、b 要截取的字符串的长度
格式2：
1、string 需要截取的字符串
2、a 可以理解为从第a个字符开始截取后面所有的字符串。
实例
1、select substr('HelloWorld',0,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
2、select substr('HelloWorld',1,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
3、select substr('HelloWorld',2,3) value from dual; //返回结果：ell，截取从“e”开始3个字符
4、select substr('HelloWorld',0,100) value from dual; //返回结果：HelloWorld，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。
5、select substr('HelloWorld',5,3) value from dual; //返回结果：oWo
6、select substr('Hello World',5,3) value from dual; //返回结果：o W (中间的空格也算一个字符串，结果是：o空格W)
7、select substr('HelloWorld',-1,3) value from dual; //返回结果：d （从后面倒数第一位开始往后取1个字符，而不是3个。原因：下面红色 第三个注解）
8、select substr('HelloWorld',-2,3) value from dual; //返回结果：ld （从后面倒数第二位开始往后取2个字符，而不是3个。原因：下面红色 第三个注解）
9、select substr('HelloWorld',-3,3) value from dual; //返回结果：rld （从后面倒数第三位开始往后取3个字符）
10、select substr('HelloWorld',-4,3) value from dual; //返回结果：orl （从后面倒数第四位开始往后取3个字符）
（注：当a等于0或1时，都是从第一位开始截取(如：1和2)）
（注：假如HelloWorld之间有空格，那么空格也将算在里面(如：5和6)）
（注：虽然7、8、9、10截取的都是3个字符，结果却不是3 个字符； 只要 |a| ≤ b,取a的个数(如：7、8、9)；当 |a| ≥ b时，才取b的个数，由a决定截取位置(如：9和10)）
11、select substr('HelloWorld',0) value from dual; //返回结果：HelloWorld，截取所有字符
12、select substr('HelloWorld',1) value from dual; //返回结果：HelloWorld，截取所有字符
13、select substr('HelloWorld',2) value from dual; //返回结果：elloWorld，截取从“e”开始之后所有字符
14、select substr('HelloWorld',3) value from dual; //返回结果：lloWorld，截取从“l”开始之后所有字符
15、select substr('HelloWorld',-1) value from dual; //返回结果：d，从最后一个“d”开始 往回截取1个字符
16、select substr('HelloWorld',-2) value from dual; //返回结果：ld，从最后一个“d”开始 往回截取2个字符
17、select substr('HelloWorld',-3) value from dual; //返回结果：rld，从最后一个“d”开始 往回截取3个字符
（注：当只有两个参数时；不管是负几，都是从最后一个开始 往回截取(如：15、16、17)）
完整函数实例
create or replace function get_request_code return varchar2 AS
-- 函数的作用：自动生成单号
v_mca_no mcode_apply.mca_no%TYPE;
CURSOR get_max_mca_no IS
SELECT max(substr(mca_no, 11, 3)) -- 查出的最大单号，截取出最后三位，如：001、002...00n
FROM mcode_apply
WHERE substr(mca_no, 3, 8) = to_char(sysdate, 'YYYYMMDD'); -- 截取单号【如：20170422】，to_char()：把时间转换为字符型，即string类型。
v_requestcode VARCHAR2(3);
BEGIN
OPEN get_max_mca_no;
FETCH get_max_mca_no INTO v_requestcode;
CLOSE get_max_mca_no;
IF v_requestcode IS NULL THEN
v_requestcode := NVL(v_requestcode, 0); -- NVL()函数：当v_requestcode为NULL时，取0作为值
END IF;
v_requestcode:= lpad(v_requestcode+1,3,'0'); -- 将游标中截取到的值加1，然后向左填充0，生成 001,002...00n 三位数的 序号； lpad()函数：向左填充
v_mca_no:='MA'||to_char(sysdate,'YYYYMMDD')||v_requestcode; -- 最终生成的申请单号（如：MA20170422001；MA20170422002;...MA2017042200N ）
RETURN '0~,'||v_mca_no;
END ;
INSTR（string,subString,position,ocurrence）查找字符串位置
解释：string：源字符串
subString：要查找的子字符串
position：查找的开始位置
ocurrence：源字符串中第几次出现的子字符串
For example:
INSTR(‘CORPORATE FLOOR’,‘OR’, 3, 2)中，源字符串为’CORPORATE FLOOR’, 目标字符串为’OR’，起始位置为3，取第2个匹配项的位置；返回结果为 14 ’
语法
格式一：instr( string1, string2 ) / instr(源字符串, 目标字符串)
格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) / instr(源字符串, 目标字符串, 起始位置, 匹配序号)
解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。
注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。
实例
格式1
1 select instr('helloworld','l') from dual; --返回结果：3 默认第一次出现“l”的位置
2 select instr('helloworld','lo') from dual; --返回结果：4 即：在“lo”中，“l”开始出现的位置
3 select instr('helloworld','wo') from dual; --返回结果：6 即“w”开始出现的位置
格式2
select instr('helloworld','l',2,2) from dual; --返回结果：4 也就是说：在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置
select instr('helloworld','l',3,2) from dual; --返回结果：4 也就是说：在"helloworld"的第3(l)号位置开始，查找第二次出现的“l”的位置
select instr('helloworld','l',4,2) from dual; --返回结果：9 也就是说：在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置
select instr('helloworld','l',-1,1) from dual; --返回结果：9 也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置
select instr('helloworld','l',-2,2) from dual; --返回结果：4 也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第二次出现的“l”的位置
select instr('helloworld','l',2,3) from dual; --返回结果：9 也就是说：在"helloworld"的第2(e)号位置开始，查找第三次出现的“l”的位置
select instr('helloworld','l',-2,3) from dual; --返回结果：3 也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第三次出现的“l”的位置
注：MySQL中的模糊查询 like 和 Oracle中的 instr() 函数有同样的查询效果； 如下所示：
MySQL： select * from tableName where name like '%helloworld%';
Oracle：select * from tableName where instr(name,'helloworld')>0; --这两条语句的效果是一样的
NVL函数用法
从两个表达式返回一个非 null 值。
语法
NVL(eExpression1, eExpression2)
参数
eExpression1, eExpression2
如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回 .NULL.。
返回值类型
字符型、日期型、日期时间型、数值型、货币型、逻辑型或 null 值
说明
在不支持 null 值或 null 值无关紧要的情况下，可以使用 NVL( ) 来移去计算或操作中的 null 值。
select nvl(a.name,‘空得’) as name from student a join school b on a.ID=b.ID
注意：两个参数得类型要匹配
SELECT T.D_FDATE,
T.VC_ZHCODE,
NVL(SUM(T.F_FZQSZ), 0) f_price_b,
NVL(SUM(T.F_FZQCB), 0) f_cost_b,
NVL(SUM(T.F_FGZ_ZZ), 0) f_gz_b,
NVL(SUM(T.F_FYZQSZ), 0) f_price_Y,
NVL(SUM(T.F_FYZQCB), 0) f_cost_Y,
NVL(SUM(T.F_FYGZ_ZZ), 0) f_gz_Y,
T.VC_SOURCE,
SYSDATE d_updatetime
FROM GZ_FUND_GZB T
比如这样的判断就很重要啦，因为你不知道哪一行是 is not null 的，也不知道接下来是否要对这个单元格进行运算操作，因此，不能给列填 null，就给它一个 0 ，便于查看，也便于运算。
常用字符串函数
转自：https://www.jellythink.com/archives/351
| 函数 | 描述 |
|---|---|
| LOWER(char) | 将字符串表达式char中的所有大写字母转换为小写字母 |
| UPPER(char) | 将字符串表达式char中的所有小写字母转换为大写字母 |
| INITCAP(char) | 首字母转换成大写 |
| SUBSTR(char, start, length) | 返回字符串表达式char中从第start开始的length个字符 |
| LENGTH(char) | 返回字符串表达式char的长度 |
| ASCII(char) | 取char的ASCII值 |
| CHR(number) | 取number的ASCII值 |
| REPLACE(char,search_str[,replacement_str]) | 将字符串char中的子串search_str替换成replacement_str；如果search_str=null，返回char；如果replacement_str=null，则会去掉char中的search_str |
| INSTR(char1,char2[,n[,m]]) | 获取子串char2在字符串char1中的位置。n为其实搜索位置，m为子串出现的次数；n为负，则从尾部开始搜索；n\m默认为1 |
| LPAD(char1,n,char2) | 在字符串char1的左端填充字符串char2直到长度达到n;char2默认为空格，如果char1.length>n，则返回char1左端的n个字符 |
| RPAD(char1,n,char2) | 在字符串char1的右端填充字符串char2直到长度达到n;char2默认为空格，如果char1.length>n，则返回char1左端的n个字符 |
| LTRIM(char1[,set]) | 去掉字符串char1左端包含的set中的任意字符 |
| RTRIM(char1[,set]) | 去掉字符串char1右端包含的set中的任意字符 |
| TRIM(char | char From string) | 从字符串的头尾或者两端截断特定字符 |
| CONCAT(str1,str2) | 连接字符串，同连接符(||)的作用一样 |
-- LOWER测试
select LOWER('HTTP://WWW.JELLYTHINK.COM') from dual; -- http://www.jellythink.com
-- UPPER测试
select UPPER('http://www.jellythink.com') from dual; -- HTTP://WWW.JELLYTHINK.COM
-- INITCAP测试
select INITCAP('jelly think') from dual; -- Jelly Think
-- SUBSTR测试
select SUBSTR('http://www.jellythink.com', 12, 10) from dual; -- jellythink(注：下标从1开始)
-- LENGTH测试
select LENGTH('JellyThink') from dual; -- 10
-- ASCII测试
select ASCII('A') from dual; -- 65
-- CHR测试
select CHR(65) from dual; -- A
-- REPLACE测试
select REPLACE('jellythink', 'think', ' is good') from dual; -- jelly is good
-- INSTR测试
select INSTR('JellyThink', 'Jelly', 1) from dual; -- 1
-- LPAD测试
select LPAD('JellyThink', 12, '*') from dual; -- **JellyThink
-- RPAD测试
select RPAD('JellyThink', 12, '*') from dual; -- JellyThink**
-- LTRIM测试
select LTRIM('**JellyThink', '*') from dual; -- JellyThink
-- RTRIM测试
select RTRIM('JellyThink**', '*') from dual; -- JellyThink
-- TRIM测试
select TRIM('*' from '**JellyThink**') from dual; -- JellyThink
select TRIM(' JellyThink ') from dual; -- JellyThink(注：默认去掉空格)
-- CONCAT测试
select CONCAT('Jelly', 'Think') from dual; -- JellyThink
常用日期函数
| 函数 | 描述 |
|---|---|
| SYSDATE | 返回系统当前日期和时间 |
| NEXT_DAY(day,char) | 返回指定日期day后的第一个工作日char所对应的日期 |
| LAST_DAY(day) | 返回day日期所指定月份中最后一天所对应的日期 |
| ADD_MONTHS(day,n) | 返回day日期在n个月后(n为正数)或前(n为负数)的日期 |
| MONTHS_BETWEEN(day1,day2) | 返回day1日期和day2日期之间相差得月份 |
| ROUND(day[,fmt]) | 返回日期的四舍五入结果。如果fmt指定年度，则7月1日为分界线；如果fmt指定月，则16日为分界线；如果指定天，则中午12:00为分界线，默认舍入到日 |
| TRUNC(day,[,fmt]) | 日期截断函数。如果fmt指定年度，则结果为本年度的1月1日；如果为月，则将结果为本月1日，默认截断到日 |
| CURRENT_DATE | 返回当前会话时区所对应日期时间 |
| EXTRACT | 从日期中获取所需要的特定数据 |
-- SYSDATE测试
select TO_CHAR(SYSDATE,'yyyy-MM-dd hh24:mi:ss') from dual; -- 2015-09-29 15:14:44
-- NEXT_DAY测试
select NEXT_DAY(SYSDATE, '星期一') from dual; -- 2015/10/5 15:16:46
-- LAST_DAY测试
select LAST_DAY(SYSDATE) from dual; -- 2015/9/30 15:17:23
-- ADD_MONTHS测试
select ADD_MONTHS(SYSDATE, 2) from dual; -- 2015/11/29 15:18:39
-- MONTHS_BETWEEN测试
select MONTHS_BETWEEN(SYSDATE, SYSDATE) from dual; -- 0
select MONTHS_BETWEEN(ADD_MONTHS(SYSDATE, -2), ADD_MONTHS(SYSDATE, 2)) from dual; -- -4
-- ROUND测试
select ROUND(SYSDATE) from dual; -- 2015/9/30
select ROUND(SYSDATE, 'YEAR') from dual; -- 2016/1/1
select ROUND(SYSDATE, 'MONTH') from dual; -- 2015/10/1
-- TRUNC测试
select TRUNC(SYSDATE) from dual; -- 2015/9/29
select TRUNC(SYSDATE, 'YEAR') from dual; -- 2015/1/1
select TRUNC(SYSDATE, 'MONTH') from dual; -- 2015/9/1
-- CURRENT_DATE测试
select CURRENT_DATE from dual; -- 2015/9/29 15:22:44
-- EXTRACT测试
select EXTRACT(YEAR from SYSDATE) from dual; -- 2015
select EXTRACT(MONTH from SYSDATE) from dual; -- 9
select EXTRACT(DAY from SYSDATE) from dual; -- 29
补充日期数据处理函数
Round函数
原文链接：https://blog.csdn.net/tayanxunhua/article/details/9258029
截取数字
格式如下：ROUND（number[,decimals]）
其中：number 待做截取处理的数值
decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分，并四舍
五入。如果为负数则表示从小数点开始左边的位数，相应整数数字用0填充，小数被去掉。
**需要注意的是，和trunc函数不同**，对截取的数字要四舍五入。
举例如下:
Sql代码：
SQL> select round(1234.5678,4) from dual;
ROUND(1234.5678,4)
——————
1234.5678
SQL> select round(1234.5678,3) from dual;
ROUND(1234.5678,3)
——————
1234.568
SQL> select round(1234.5678,2) from dual;
ROUND(1234.5678,2)
——————
1234.57
SQL> select round(1234.5678,1) from dual;
ROUND(1234.5678,1)
——————
1234.6
SQL> select round(1234.5678,0) from dual;
ROUND(1234.5678,0)
——————
1235
SQL> select round(1234.5678,-1) from dual;
ROUND(1234.5678,-1)
——————-
1230
SQL> select round(1234.5678,-2) from dual;
ROUND(1234.5678,-2)
——————-
1200
SQL> select round(1234.5678,-3) from dual;
ROUND(1234.5678,-3)
——————-
1000
附加：
SQL> select round(45.923,-1) from dual;
ROUND(45.923,-1)
——————-
50
————————————————
trunc
转自：https://www.cnblogs.com/mingforyou/p/7644308.html
截取数字的时候没有四舍五入，和round函数不同
/**************日期********************/
1.select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06
2.select trunc(sysdate, 'mm') from dual --2013-01-01 返回当月第一天.
3.select trunc(sysdate,'yy') from dual --2013-01-01 返回当年第一天
4.select trunc(sysdate,'dd') from dual --2013-01-06 返回当前年月日
5.select trunc(sysdate,'yyyy') from dual --2013-01-01 返回当年第一天
6.select trunc(sysdate,'d') from dual --2013-01-06 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 当前时间为17:35
8.select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120
Oracle查询最近一年数据以及两个日期间隔天数
查询之前需要格式化日期，用to_char(日期，格式)函数进行转换
SELECT D.ORDER_NUM ,
D.EMP_NAME
FROM DAT_DOCUMENT d
WHERE D.FORM_NAME ='Form_SD01'
AND TO_CHAR(D.CREATE_TIME,'yyyy-MM-dd') BETWEEN '2019-01-01' AND '2020-03-17';
查询两个日期直接间隔天数：
SELECT TO_char(d.CREATE_TIME,'yyyy-MM-dd'),
TO_char(d.UPDATE_TIME,'yyyy-MM-dd'),
trunc(d.UPDATE_TIME) - trunc(d.CREATE_TIME) as days
FROM dat_document d
结果
常用类型转换函数
| 函数 | 描述 |
|---|---|
| TO_CHAR | 将一个数字或日期转换成字符串 |
| TO_NUMBER | 将字符型数据转换成数字型数据 |
| TO_DATE | 将字符型数据转换为日期型数据 |
| CAST | 将一种built-in类型转换成另一种built-in类型 |
-- TO_CHAR测试
select TO_CHAR(100) from dual; -- 100
select TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') from dual; -- 2015-09-29 15:36:24
-- TO_NUMBER测试
select TO_NUMBER('10') from dual; -- 10
select TO_NUMBER('JellyThink') from dual; -- 无效数字
-- TO_DATE测试
select TO_DATE('2015-9-29', 'YYYY-MM-DD') from dual; -- 2015/9/29
-- CAST测试
select CAST('100' as NUMBER) from dual; -- 100
select CAST(2 as char) from dual; -- 2
months_between
months_between函数返回两个日期之间的月份数。如果两个日期月份内天数相同，或者都是某个月的最后一天，返回一个整数，否则，返回数值带小数，以每天1/31月来计算月中剩余天数。
select months_between(date'2015-01-01',date'2014-01-01') MM from dual
结果
MM
12
select months_between(sysdate,date'2015-05-13') mon from dual;
结果
mon
60.08624066606929510155316606929510155317
其他实例可参考
months_between函数实例
其他函数
| 函数 | 描述 |
|---|---|
| decode(expression , search , result [, search , result]… [, default]) | IF语句的另一形式。将输入数值与参数列表比较，返回对应值。应用于将表的行转换成列以及IF语句无法应用的场合 |
| SIGN(number) | 如果number大于0，SIGN则返回1；如果number小于0，SIGN则返回-1；如果number等于0，SIGN则返回0 |
| TRUNC(number, [ decimal_places ]) | number是要截取的数字，decimal_places是要保留的小数位。这个参数必须是个整数。 如果此参数缺省，默认保留0位小数 |
| GREATEST(expr1[,expr2]…) | 返回表达式中值最大的一个 |
| LEAST(expr1[,expr2]…) | 返回表达式中值最小的一个 |
| NULLIF(expr1,expr2) | 如果expr1=expr2；则返回null，否则返回expr1 |
| NVL(expr1,expr2) | 如果expr1=null；则返回expr2，否则返回expr1 |
| NVL2(expr1,expr2,expr3) | 如果expr1!=null；则返回expr2；如果expr1=null；则返回expr3 |
| ceil(n) | 取大于等于数值n的最小整数； |
| floor(n) | 取小于等于数值n的最大整数 |
-- DECODE测试
select DECODE(20, 10, 5, 200, 10, 20, 30) from dual; -- 30
-- SIGN测试
select SIGN(20) from dual; -- 1
select SIGN(-30) from dual; -- -1
select SIGN(0) from dual; -- 0
-- TRUNC测试
select TRUNC(20.2183, 2) from dual; -- 20.21
select TRUNC(20.1, 4) from dual; -- 20.1
-- GREATEST测试
select GREATEST(20, 100, 30, 20, 40, 400) from dual; -- 400
-- LEAST测试
select LEAST(20, 100, 30, 20, 40, 400) from dual; -- 20
-- NULLIF测试
select NULLIF(20, 20) from dual; -- NULL
select NULLIF(20, 10) from dual; -- 20
-- NVL测试
select NVL(20, 30) from dual; -- 20
select NVL(NULL, 30) from dual; -- 30
-- NVL2测试
select NVL2(NULL, 20, 30) from dual; -- 30
select NVL2('JellyThink', 20, 30) from dual; -- 20
--ceil(n)
SQL> select ceil(9.5) from dual; ---------- 10
----floor(n)
SQL> select floor(9.5) from dual; ---------9
1、NVL(X,VALUE)
如果X为空，返回value，否则返回X
例：对工资是2000元以下的员工，如果没发奖金，每人奖金100元
代码演示：NVL函数
SQL> SELECT ENAME,JOB,SAL,NVL(COMM,100) FROM EMP WHERE SAL<2000;
-------------------------------------------------------------------
ENAME JOB SAL NVL(COMM,100)
SMITH CLERK 800 100
ALLEN SALESMAN 1600 300
WARD SALESMAN 1250 500
MARTIN SALESMAN 1250 1400
TURNER SALESMAN 1500 50
ADAMS CLERK 1100 100
JAMES CLERK 950 100
-------------------------------------------------------------------
7 rows selected
2、NVL2(x,value1,value2)
如果x非空，返回value1，否则返回value2
例：对EMP表中工资为2000元以下的员工，如果没有奖金，则奖金为200元，如果有奖金，则在原来的奖金基础上加100元
代码演示：NVL2函数
SQL> SELECT ENAME,JOB,SAL,NVL2(COMM,comm+100,200) "comm"
2 FROM EMP WHERE SAL<2000;
-------------------------------------------------------------------
ENAME JOB SAL comm
SMITH CLERK 800 200
ALLEN SALESMAN 1600 400
WARD SALESMAN 1250 600
MARTIN SALESMAN 1250 1500
TURNER SALESMAN 1500 150
ADAMS CLERK 1100 200
JAMES CLERK 950 200
MILLER CLERK 1300 200
-------------------------------------------------------------------------------------------------------
补充
decode
Oracle 中 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
decode(字段或字段的运算，值1，值2，值3）
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多
使用方法：
1、比较大小
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如：
变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。
2、此函数用在SQL语句中，功能介绍如下：
Decode函数与一系列嵌套的 IF-THEN-ELSE语句相似。base_exp与compare1,compare2
等等依次进行比较。如果base_exp和 第i 个compare项匹配，就返回第i 个对应的
value 。如果base_exp与任何的compare值都不匹配，则返回default。每个compare值
顺次求值，如果发现一个匹配，则剩下的compare值（如果还有的话）就都不再求值。
一个为NULL的base_exp被认为和NULL compare值等价。如果需要的话，每一个compare
值都被转换成和第一个compare 值相同的数据类型，这个数据类型也是返回值的类型。
Decode函数在实际开发中非常的有用
结合Lpad函数，如何使主键的值自动加1并在前面补0
select LPAD(
decode(
count(记录编号),0,1,max(to_number(记录编号)+1)
),
14,
'0'
) 记录编号 from tetdmis
eg:
select decode(dir,1,0,1) from a1_interval
dir 的值是1变为0，是0则变为1
比如我要查询某班男生和女生的数量分别是多少?
通常我们这么写:
select count(*) from 表 where 性别 ＝ 男；
select count(*) from 表 where 性别 ＝ 女；
要想显示到一起还要union一下，太麻烦了
用decode呢，只需要一句话
select decode(性别，男，1，0），decode(性别，女，1，0） from 表
3，order by对字符列进行特定的排序
大家还可以在Order by中使用Decode。
例：表table_subject，有subject_name列。要求按照：语、数、外的顺序进行排序。
这时，就可以非常轻松的使用Decode完成要求了。
select *
from table_subject
order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3)
INTERVAL 函数
https://blog.csdn.net/wkc168/article/details/5545633/
INTERVAL YEAR TO MONTH数据类型
Oracle语法:
INTERVAL ‘integer [- integer]’ {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}]
该数据类型常用来表示一段时间差, 注意时间差只精确到年和月. precision为年或月的精确域, 有效范围是0到9, 默认值为2.
eg:
INTERVAL ‘123-2’ YEAR(3) TO MONTH
表示: 123年2个月, “YEAR(3)” 表示年的精度为3, 可见"123"刚好为3为有效数值, 如果该处YEAR(n), n<3就会出错, 注意默认是2.
INTERVAL ‘123’ YEAR(3)
表示: 123年0个月
INTERVAL ‘300’ MONTH(3)
表示: 300个月, 注意该处MONTH的精度是3啊.
INTERVAL ‘4’ YEAR
表示: 4年, 同 INTERVAL ‘4-0’ YEAR TO MONTH 是一样的
INTERVAL ‘50’ MONTH
表示: 50个月, 同 INTERVAL ‘4-2’ YEAR TO MONTH 是一样
INTERVAL ‘123’ YEAR
表示: 该处表示有错误, 123精度是3了, 但系统默认是2, 所以该处应该写成 INTERVAL ‘123’ YEAR(3) 或"3"改成大于3小于等于9的数值都可以的
INTERVAL ‘5-3’ YEAR TO MONTH + INTERVAL ‘20’ MONTH =
INTERVAL ‘6-11’ YEAR TO MONTH
表示: 5年3个月 + 20个月 = 6年11个月
相关函数
https://www.cnblogs.com/xyz0601/p/4417165.html
numtodsinterval(,) ,x是一个数字,c是一个字符串,
表明x的单位,这个函数把x转为interval day to second数据类型
常用的单位有 (‘day’,‘hour’,‘minute’,‘second’)
example
SQL> select sysdate,sysdate+numtodsinterval(3,‘hour’) as res from dual;
SYSDATE RES ------------------- -------------------
2007-09-05 01:45:34 2007-09-05 04:45:34
numtoyminterval
与numtodsinterval函数类似,将x转为interval year to month数据类型
常用的单位有’year’,‘month’
example
SQL> select sysdate,sysdate+numtoyminterval(3,‘year’) as res from dual;
SYSDATE RES ------------------- -------------------
2007-09-05 01:54:53 2010-09-05 01:54:53
格式：NumToYMInterval(n, interval_unit);
n: 数值类型
interval_unit: ‘YEAR’, ‘MONTH’ ，或其他可以转换成这两个值之一的表达式
NumToYMInterval(1, ‘YEAR’) ：一年后的间隔
NumToYMInterval(-1, ‘MONTH’): 一个月前
小数会被计算成整数后，再做计算：
select sysdate + numtoyminterval(0.1, ‘MONTH’) as future from dual;\
FUTURE
11-OCT-13
该函数的结果是：”INTERVAL YEAR TO MONTH literal“。不能与数值做运算。
select 1 + NumToYMInterval(1, ‘MONTH’) from dual
Oracle会返回一个错误。
做日期运算时，这个函数非常有用。例如：取一个月后的日期：
select sysdate + NumToYMInterval(1, ‘MONTH’) from dual;
数字显示格式
oracle 函数to_char(数据,‘FM999,999,999,999,990.00’) 格式化数据
to_char(数据,‘FM999,999,999,999,990.00’)
例子：
select to_char(0.59/1.00*100,'FM999,999,999,999,990.00')||'%' from dual
结果为：59.00%
下面是详细介绍（copy的）
select to_char( 0.596 , 'FM999,999,999,990.00' ) from dual
它的执行结果为：0.60
怎样使查出来类似0.60的小数带0，答案是：使用如上的格式即
①其9代表：如果存在数字则显示数字，不存在则显示空格
②其0代表：如果存在数字则显示数字，不存在则显示0，即占位符。
③其FM代表：删除如果是因9带来的空格，则删除之
具体区别可以见
select to_char(date'2018-02-01','yyyy,mm,dd') from dual;
结果：2018,02,01
select to_char(date'2018-02-01','fmyyyy,mm,dd') from dual;
结果：2018,2,1
Selec length(to_char( 1.00 , ' 999,999,999,999,999,999,999,990.00 ' )), length(to_char( 1.00 , ' FM999,999,999,999,999,999,999,990.00 ' )) from dual
其结果是：
| length( to_char( 1.00, '999,999,999,999,999,999,999,990.00' ) ) | length( to_char( 1.00, 'FM999,999,999,999,999,999,999,990.00' ) ) |
|--|--|
|35|4|
④四舍五入：0.596变成了0.60不知道大家注意到了没有。如果不要四舍五入，则需要trunc下，具体如下：
select to_char(trunc( 0.596 , 2 ), ' FM999,999,999,999,990.00 ' ) from dual
其结果是0.59
⑤解决列表100，000，000的情况
select to_char( 10000000000 , 'FM999,999,999,990.00' ) from dual
结果为：10,000,000,000.00
上面的功能适用于固定小数点格式。而如果没有小数点了?
⑥解决整数后面多个点的情况
select to_char( 10000000000 , 'FM999,999,999,990.99' ) from dual
结果为：10,000,000,000.
此类的解决方案则需要进行先判断词数据是否包含.(点的情况)
如果有点则 instr()下
否则直接to_cahr()
此功能适用于：页面展示不出0(类的自定义的属性，A类不存在属性b,通过sqlMap查询赋值的情况下适合，正常情况下不需要使用)，前后台列表展示不显示0，前后台列表的10，000，000，000格式。
日起的显示
一些开发人员为了获取”2018年3月5日”这样的格式写了以下的实现：
select to_char(sysdate,'yyyy')||'年'||ltrim(to_char(sysdate,'mm'),'0')||'月'||to_char(sysdate,'dd')||'日'
from dual;
结果：2020年11月04日
而正确的写法是：
select to_char(sysdate,'fmyyyy"年"mm"月"dd"日"') from dual;
结果：2020年11月4日
这里有2个技术细节：
1.双引号
可以把任何字符都放进去，比如：
select to_char(sysdate,'"yyyy="yyyy" mm="mm" dd="dd') from dual;
TO_CHAR(SYSDATE,'"YYYY="YYYY"MM="MM"DD="DD")
------------------------------
yyyy=2018 mm=04 dd=10
2.FM修饰符（不区分大小写）
2.1.用于裁掉月和日的前导0，比如
SQL> select to_char(date'2018-02-01','yyyy,mm,dd') from dual;
TO_CHAR(DATE'2018-02-01','YYYY
------------------------------
2018,02,01
SQL> select to_char(date'2018-02-01','fmyyyy,mm,dd') from dual;
TO_CHAR(DATE'2018-02-01','FMYY
------------------------------
2018,2,1
2.2.fm修饰符同样可以用于去掉将数字to_char时产生的空格字符。比如：
SQL> select '*'||to_char(89,'9990.00') from dual;
'*'||TO_CHAR(89,'9990.00')
--------------------------
* 89.00
SQL> select '*'||to_char(89,'fm9990.00') from dual;
'*'||TO_CHAR(89,'FM9990.00')
----------------------------
*89.00
2543
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
