一、IN

Oracle IN运算符可以用来确定值是否与列表或子查询中的任何值相匹配。查找在括号内的值。

expression [NOT] IN (v1,v2,...)

SELECT
    order_id,customer_id,status,salesman_id
FROM
    orders
WHERE
    salesman_id IN (54,55,56)
ORDER BY
    order_id;

二、REPLACE

replace(原字段，“原字段旧内容“,“原字段新内容“)。

SELECT REPLACE('accd','cd','ef') from dual; --> acef

replace：

语法：REPLACE(char,search_string[,replacement_string])

解释：replace中，每个search_string都被replacement_string所代替

select replace('acdd','cd','ef') from dual; --> acef

如果replacement_string为空或为null，那么所有的search_string都被移除

select replace('acdd','cd','') from dual; --> ad

如果search_string 为null，那么就返回原来的char

select replace('acdd','ef') from dual; -->acdd

select replace('acdd','','') from dual; -->acdd(也是两者都为空的情况)

select translate('acdd','cd','ef') from dual; -->aeff -->相当于 c=e; d=f

语法：TRANSLATE('char','from_string','to_string')

解释：translate中，每个from_string中的字符被to_string中

补充：TRANSLATE(string,from,to)转换的两个注意点
1、转换源字串(from)在目的字串(to)中不存在对应，则转换后被截除
2、转换目的字串(to)不能为''，''在oracle中被视为空值，因此无法匹配而返回为空值

注意：一个汉字作为一个字符还是两个字符进行转换与字符集的设置相关。

三、SIGN

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1 ,例如：

a=10，b=20
则sign(a-b)返回-1

四、DECODE

decode(expr1,expr2,expr3,[expr4])

作如下理解该表达式：
　　(1)，如果expr1 = expr2,decode函数返回expr3表达式的值;
　　(2)，如果expr1 != expr2,decode函数返回expr4表达式的值,如果expr4未指定，则返回null;

补充：

Decode函数的语法结构还包括如下:
decode (expression, search_1, result_1)
decode (expression, search_1, result_1, search_2, result_2)
decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n)

decode (expression, search_1, result_1, default)
decode (expression, search_1, result_1, search_2, result_2, default)
decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。

应用

1.使用decode函数分段

将成绩表中分数大于90分的分为优秀，80~90分为良好，70~80分为中等，60~70分为及格，60分以下为不及格。

（sign()函数的作用是，判断参数的值大于0则返回1，等于0则返回0，小于0则返回-1；

如图，当socre大于或等于90时，socre-90>=0，sign()函数返回1或0，则输出‘优秀’，

在score-90<0的情况下，再判断socre-80，socre-80>=0，sign()函数返回1或0，则输出‘良好’，

以此类推，用decode()的嵌套配合sign()函数来实现对分数的分段以及相应的输出，

最后60分以下的就default为‘不及格’就可以了，可以看到我们的Tony老师的分数就不及格了）

2.使用decode函数对表做行列转换

如图所示，有一张三个科目的成绩表，各科目名称和相应的分数呈现在行中，

现想将各科目名称转换为列名，每列的内容为对应科目的分数。

（如图，先用decode函数判断subject为Chinese时，输出score，不为Chinese时，输出为NULL，

因为共有三个科目，所以输出其中一个科目的分数时，其他科目的分数为NULL，这里只需要

用sum()函数来实现聚合的作用，将空值去除掉就可以了）

（group by先将name分组了，然后sum()函数分别将name为John的每个列的值相加，如Chinese列，

80+NULL+NULL=80，再将name为Will的每个列的值相加，最终达到如上图聚合的效果）

五、TO_CHAR

to_char函数的功能是将数值型或者日期型转化为字符型。

格式化函数

函数	返回	描述	例子
to_char(timestamp, text)	text	把 timestamp 转换成 string	to_char(timestamp 'now','HH12:MI:SS')
to_char(int, text)	text	把 int4/int8 转换成 string	to_char(125, '999')
to_char(float, text)	text	把 float4/float8 转换成 string	to_char(125.8, '999D9')
to_char(numeric, text)	text	把 numeric 转换成 string	to_char(numeric '-125.8', '999D99S')
to_date(text, text)	date	把 string 转换成 date	to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text)	date	把 string 转换成 timestamp	to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)	numeric	把 string 转换成 numeric	to_number('12,454.8-', '99G999D9S')

例子

输入	输出
to_char(now(),'Day, HH12:MI:SS')	`'Tuesday , 05:39:18'`
to_char(now(),'FMDay, HH12:MI:SS')	`'Tuesday, 05:39:18'`
to_char(-0.1,'99.99')	`' -.10'`
to_char(-0.1,'FM9.99')	`'-.1'`
to_char(0.1,'0.9')	`' 0.1'`
to_char(12,'9990999.9')	`' 0012.0'`
to_char(12,'FM9990999.9')	`'0012'`
to_char(485,'999')	`' 485'`
to_char(-485,'999')	`'-485'`
to_char(485,'9 9 9')	`' 4 8 5'`
to_char(1485,'9,999')	`' 1,485'`
to_char(1485,'9G999')	`' 1 485'`
to_char(148.5,'999.999')	`' 148.500'`
to_char(148.5,'999D999')	`' 148,500'`
to_char(3148.5,'9G999D999')	`' 3 148,500'`
to_char(-485,'999S')	`'485-'`
to_char(-485,'999MI')	`'485-'`
to_char(485,'999MI')	`'485'`
to_char(485,'PL999')	`'+485'`
to_char(485,'SG999')	`'+485'`
to_char(-485,'SG999')	`'-485'`
to_char(-485,'9SG99')	`'4-85'`
to_char(-485,'999PR')	`'<485>'`
to_char(485,'L999')	`'DM 485`
to_char(485,'RN')	`' CDLXXXV'`
to_char(485,'FMRN')	`'CDLXXXV'`
to_char(5.2,'FMRN')	`V`
to_char(482,'999th')	`' 482nd'`
to_char(485, '"Good number:"999')	`'Good number: 485'`
to_char(485.8,'"Pre-decimal:"999" Post-decimal:" .999')	`'Pre-decimal: 485 Post-decimal: .800'`
to_char(12,'99V999')	`' 12000'`
to_char(12.4,'99V999')	`' 12400'`
to_char(12.45, '99V9')	`' 125'`

简单应用

-- 1.0123
Select TO_CHAR(1.0123) FROM DUAL
-- 123
Select TO_CHAR(123) FROM DUAL
-- .123
Select TO_CHAR(0.123) FROM DUAL

函数用法

TO_CHAR ( n [, fmt [, 'nlsparam']] )

将NUMBER类型的n按数值格式fmt转换成VARCHAR2类型的值。'nlsparams'指定由数值格式的元素返回的字符,包括:

．小数点字符
．组分隔符
．本地钱币符号
．国际钱币符号

变元的形式为：

'NLS_NUMERIC_CHARACTERS="dg" NLS_CURRENCY="tcxt" NLS_ISO_CURRENCY=territory'

其中d为小数点字符,g为组分隔符。

例 :TO_CHAR (17145,'L099G999','NLS_NUMERIC_CHARACTERS=".," NLS_CURRENCY="NUD"')=NUD017,145
通过上面的了解，再查看fmt的一些格式，我们可以用以下表达式得到'0.123'的值：

-- 0.123
Select TO_CHAR(0.123,'0.999') FROM DUAL
-- ######
Select TO_CHAR(100.12,'0.999') FROM DUAL
-- 空格1.120
Select TO_CHAR(1.12,'0.999') FROM DUAL

1、去空格
2、小数点最多4位，最少保留2位。

1--->'1.00'；1.1--->'1.00'；1.12-->'1.12'；1.1234--->'1.1234'；

1.12345--->'1.1235'

/*
FM ：除空格
9999999.0099：允许小数点左边最大正数为7位，小数点右边最少2位，最多4位，且在第5位进行四舍五入
*/

Select TO_CHAR(123.0233,'FM9999999.0099') FROM DUAL

六、TO_NUMBER

to_number()函数是将一些处理过的按一定格式编排过的字符串变回数值型的格式。

1、to_number()函数可以将char或varchar2类型的string转换为一个number类型的数值。

2、需要注意的是，被转换的字符串必须符合数值类型格式,如果被转换的字符串不符合数值型格式，Oracle将抛出错误提示。

3、to_number和to_char恰好是两个相反的函数。

（1）to_number(varchar2 or char,'格式'）

-- 12134
select to_number('000012134') from dual;  
-- 88877
select to_number('88877') from dual;

（2）如果数字在格式范围内的话，就是正确的，否则就是错误的；如：

-- 无效的数字
select to_number('$12345.678', '$999999.99') from dual;  
-- 12345.678
select to_number('$12345.678', '$999999.999') from dual;

（3）可以用来实现进制转换；16进制转换为10进制：

-- 415
select to_number('19f','xxx') from dual;  
-- 15
select to_number('f','xx') from dual;

七、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 ，便于查看，也便于运算。

八、FIELD+

字段+号在左边，就是左关联，在右边就是右关联。

左连接就是左边的表全有值，右边表的值可以为空（+）。

右连接是左边表值可以为空（+），右边表的值全有。

SELECT
	* 
FROM
	BATCH_JOB_EXECUTION a,
	BATCH_JOB_EXECUTION_PARAMS b 
WHERE
	a.JOB_EXECUTION_ID ( + ) = 102 
	AND a.JOB_EXECUTION_ID = b.JOB_EXECUTION_ID ( + )

九、ROW_NUMBER

row_number的用途非常广泛，排序最好用它，它会为查询出来的每一行记录生成一个序号，依次排序且不会重复，注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。

-- 顺序排序
SELECT * FROM ACCOUNT;

SELECT NAME,BALANCE,ROW_NUMBER() OVER(PARTITION BY POSITION ORDER BY BALANCE DESC) AS RANK FROM "ACCOUNT"

十、RANK

rank函数用于返回结果集的分区内每行的排名，行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名，与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，如果使用rank函数来生成序号，over子句中排序字段值相同的序号是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个，也就是相关行之前的排名数加一，可以理解为根据当前的记录数生成序号，后面的记录依此类推。

-- rank()跳跃排序，如果有两个第一级别时，接下来是第三级别。
SELECT * FROM ACCOUNT;

SELECT NAME,BALANCE,POSITION,RANK() OVER(PARTITION BY POSITION ORDER BY BALANCE DESC) AS RANK FROM "ACCOUNT";

十一、DENSE_RANK

dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃排序，有两个第一名时接下来就是第三名，dense_rank()是连续排序，有两个第一名时仍然跟着第二名。

-- DENSE_RANK()连续排序，如果有两个第一级别时，接下来是第二级别
SELECT * FROM ACCOUNT;

SELECT NAME,BALANCE,POSITION,DENSE_RANK() OVER(PARTITION BY POSITION ORDER BY BALANCE DESC) AS RANK FROM "ACCOUNT";

十二、SUBSTR

字符串截取方法：

格式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个字符开始截取后面所有的字符串。

十三、SYS_CONTEXT

是什么

sys_context函数是Oracle提供的一个获取环境上下文信息的预定义函数。该函数用来返回一个指定namespace下的parameter值。该函数可以在sql和PL/SQL语言中使用。sys_context实际上就是一个Oracle存储和传递参数的容器访问函数。我们登入Oracle服务器，是带有会话信息和其他一些属性信息的。其中，有一些是Oracle预定义的，登录系统的时候自动填入到指定的变量中。另一个函数userenv()保留与之兼容。

使用

select sys_context('USERENV', 'TERMINAL') terminal,
       sys_context('USERENV', 'LANGUAGE') language,
       sys_context('USERENV', 'SESSIONID') sessionid,
       sys_context('USERENV', 'INSTANCE') instance,
       sys_context('USERENV', 'ENTRYID') entryid,
       sys_context('USERENV', 'ISDBA') isdba,
       sys_context('USERENV', 'NLS_TERRITORY') nls_territory,
       sys_context('USERENV', 'NLS_CURRENCY') nls_currency,
       sys_context('USERENV', 'NLS_CALENDAR') nls_calendar,
       sys_context('USERENV', 'NLS_DATE_FORMAT') nls_date_format,
       sys_context('USERENV', 'NLS_DATE_LANGUAGE') nls_date_language,
       sys_context('USERENV', 'NLS_SORT') nls_sort,
       sys_context('USERENV', 'CURRENT_USER') current_user,
       sys_context('USERENV', 'CURRENT_USERID') current_userid,
       sys_context('USERENV', 'SESSION_USER') session_user,
       sys_context('USERENV', 'SESSION_USERID') session_userid,
       sys_context('USERENV', 'PROXY_USER') proxy_user,
       sys_context('USERENV', 'PROXY_USERID') proxy_userid,
       sys_context('USERENV', 'DB_DOMAIN') db_domain,
       sys_context('USERENV', 'DB_NAME') db_name,
       sys_context('USERENV', 'HOST') host,
       sys_context('USERENV', 'OS_USER') os_user,
       sys_context('USERENV', 'EXTERNAL_NAME') external_name,
       sys_context('USERENV', 'IP_ADDRESS') ip_address,
       sys_context('USERENV', 'MODULE') module,
       sys_context('USERENV', 'NETWORK_PROTOCOL') network_protocol,
       sys_context('USERENV', 'BG_JOB_ID') bg_job_id,
       sys_context('USERENV', 'FG_JOB_ID') fg_job_id,
       sys_context('USERENV', 'AUTHENTICATION_TYPE') authentication_type,
       sys_context('USERENV', 'AUTHENTICATION_DATA') authentication_data,
       sys_context('userenv', 'SID') 
from dual;

自定义

-- 创建存储过程，Test为命名空间，如果权限不足，则查看当前用户是否有权限操作该命名空间
CREATE 
	OR REPLACE PROCEDURE set_test_context ( TestValue IN VARCHAR2 ) IS BEGIN
		dbms_session.set_context ( 'Test', 'TestName', TestValue );
END set_test_context;

-- 调用储过过程
BEGIN
	set_test_context ( '小强崽' );
END;

-- 查询存储过程设置的参数
SELECT
	sys_context ( 'Test', 'TestName' )
FROM
	dual;

十四、INSTR

INSTR()字符查找函数。

格式

-- instr(源字符串, 目标字符串)
格式一：instr( string1, string2 )    

-- instr(源字符串, 目标字符串, 起始位置, 匹配序号)
格式二：instr( string1, string2 start_position, nth_appearance ) 

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。

实例

-- 返回结果：3 默认第一次出现“l”的位置。
select instr('helloworld','l') from dual;

-- 返回结果：4 即“lo”同时(连续)出现，“l”的位置。
select instr('helloworld','lo') from dual;

-- 返回结果：6 即“w”开始出现的位置。
select instr('helloworld','wo') from dual;

-- 返回结果：4 也就是说：在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置。
select instr('helloworld','l',2,2) from dual;

-- 返回结果：4 也就是说：在"helloworld"的第3(l)号位置开始，查找第二次出现的“l”的位置。
select instr('helloworld','l',3,2) from dual;

-- 返回结果：9 也就是说：在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置。
select instr('helloworld','l',4,2) from dual;

-- 返回结果：9 也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置。
select instr('helloworld','l',-1,1) from dual;

-- 返回结果：4 也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第二次出现的“l”的位置。
select instr('helloworld','l',-2,2) from dual;

-- 返回结果：9 也就是说：在"helloworld"的第2(e)号位置开始，查找第三次出现的“l”的位置。
select instr('helloworld','l',2,3) from dual;

--返回结果：3 也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第三次出现的“l”的位置。
select instr('helloworld','l',-2,3) from dual;

十五、ROUND

ROUND：对某个值进行四舍五入

格式：ROUND（number[,decimals]）

number 待做截取处理的数值，decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分，并四舍五入。如果为负数则表示从小数点开始左边的位数，相应整数数字用0填充，小数被去掉。需要注意的是，和trunc函数不同，对截取的数字要四舍五入。

-- 1234.5678
SELECT ROUND( 1234.5678, 4 ) FROM dual;
-- 1234.567
SELECT ROUND( 1234.5678, 3 ) FROM dual;
-- 1000
SELECT ROUND( 1234.5678, -3 ) FROM dual;
-- 50
SELECT ROUND( 45.923,-1 ) FROM dual;

十六、TRIM

用法一：去除指定符号的左右两边空格

-- (AA BB CC)左右两边没有空格 
select trim(' AA BB CC ') from dual;

用法二：去除指定字符，只能去除单个字符

-- ORA-30001: trim set should have only one character 要截取集仅能有一个字符
select trim('AA' from 'AA BB CC AA') from dual;

leading：去除左边

-- ( BB CC AA)左边还有个空格
select trim(leading 'A' from 'AA BB CC AA') from dual;
-- ( ABB CC AA)左边还有个空格
select trim(leading 'A' from 'AA ABB CC AA') from dual;

trailing：去除右边

-- (AA BB CC )右边还有个空格
select trim(trailing 'A' from 'AA BB CC AA') from dual;
-- (AA BB CCA )右边还有个空格
select trim(trailing 'A' from 'AA BB CCA AA') from dual;

both：去除左右两边，默认方式

-- ( BB CC )左右两边还有个空格
select trim('A' from 'AA BB CC AA') from dual;
-- ( BB CC )左右两边还有个空格
select trim(both 'A' from 'AA BB CC AA') from dual;

十七、LTRIM

用法一：去除指定符号的左边空格

-- (AA BB CC )
select ltrim(' AA BB CC ') from dual;

用法二：去除左边指定字符，可以去除多个字符

-- 表示字符串string1去除前面与string2字符集匹配的，若无匹配则结束返回
select ltrim(string1,string2) from dual;

-- ( BB CC)
select ltrim('AA BB CC','A') from dual;
-- ( BB CC)
select ltrim('AA BB CC','AA') from dual;
-- ( BB CC)
select ltrim('AA BB CC','AAA') from dual;
-- ( BB CC)
select ltrim('AA BB CC','AAA...') from dual;
-- ( BB CC)
select ltrim('AA BB CC','AB') from dual;
-- ( BB CC)
select ltrim('AA BB CC','ABC') from dual;
-- ( BB CC)
select ltrim('AA BB CC','BA') from dual;
-- (AA BB CC)
select ltrim('AA BB CC','C') from dual;
-- (NULL)
SELECT ltrim('AAAABCDE','BACDE') FROM dual;
-- (AAAABCDEF)
SELECT ltrim('AAAABCDEF','BCDEF') FROM dual;

十八、RTRIM

用法一：去除指定符号的右边空格

-- ( AA BB CC)
select rtrim(' AA BB CC ') from dual;

用法二：去除右边指定字符，可以去除多个字符

-- (AA BB )
select rtrim('AA BB CC','C') from dual;
-- (AA BB )
select rtrim('AA BB CC','CC') from dual;
-- (NULL)
select rtrim('AAAABCDE','BACDE') from dual;
-- (AAAA)
select rtrim('AAAABCDEF','BCDEF') from dual;
-- (xiaoqiang)
select rtrim('xiaoqiangzai','zai') from dual;
-- (xiaoqiang)
select rtrim('xiaoqiangzaizai','zai') from dual;
-- (xiaoqiang)
select rtrim('xiaoqiangzaizaaaai','zai') from dual;
-- (xiaoqiang)
select rtrim('xiaoqiangzaizaaaai','zaaaai') from dual;

九十九、附录

99.1 常用的字符串函数

函数	描述
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测试 https://www.wuduoqiang.com/
select LOWER('HTTPS://WWW.WUDUOQIANG.COM/') from dual;

-- UPPER测试 HTTPS://WWW.WUDUOQIANG.COM/
select UPPER('https://www.wuduoqiang.com/') from dual;

-- INITCAP测试 Xiao Qiang Zai
select INITCAP('xiao qiang zai') from dual;

-- SUBSTR测试 wuduoqiang 截取从第13开始截取10
select SUBSTR('https://www.wuduoqiang.com/', 13, 10) from dual;

-- LENGTH测试 9
select LENGTH('xiaoqiang') from dual;

-- ASCII测试 65
select ASCII('A') from dual;

-- CHR测试 A
select CHR(65) from dual;

-- REPLACE测试 qiang is good
select REPLACE('qiangthink', 'think', ' is good') from dual;

-- INSTR测试 1
select INSTR('QiangThink', 'Qiang', 1) from dual;

-- LPAD测试 **QiangThink
select LPAD('QiangThink', 12, '*') from dual;

-- RPAD测试 QiangThink**
select RPAD('QiangThink', 12, '*') from dual;

-- LTRIM测试 QiangThink
select LTRIM('**QiangThink', '*') from dual;

-- RTRIM测试 QiangThink
select RTRIM('QiangThink**', '*') from dual;

-- TRIM测试 QiangThink
select TRIM('*' from '**QiangThink**') from dual;
-- TRIM测试 QiangThink 自动去掉空格
select TRIM('  QiangThink  ') from dual;

-- CONCAT测试 QiangThink
select CONCAT('Qiang', 'Think') from dual;

99.2 常用的日期函数

函数	描述
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测试 2020-12-16 00:38:45
select TO_CHAR(SYSDATE,'yyyy-MM-dd hh24:mi:ss') from dual;

-- NEXT_DAY测试 2020-12-21 00:43:00
SELECT NEXT_DAY( SYSDATE, 'MONDAY ' ) FROM DUAL;

-- LAST_DAY测试 2020-12-31 00:39:41
select LAST_DAY(SYSDATE) from dual;

-- ADD_MONTHS测试 2021-02-16 00:39:59
select ADD_MONTHS(SYSDATE, 2) from dual;

-- MONTHS_BETWEEN测试 0
select MONTHS_BETWEEN(SYSDATE, SYSDATE) from dual;
-- MONTHS_BETWEEN测试 -4
select MONTHS_BETWEEN(ADD_MONTHS(SYSDATE, -2), ADD_MONTHS(SYSDATE, 2)) from dual;

-- ROUND测试 2020-12-16 00:00:00
select ROUND(SYSDATE) from dual;
-- ROUND测试 2021-01-01 00:00:00
select ROUND(SYSDATE, 'YEAR') from dual;
-- ROUND测试 2021-01-01 00:00:00
select ROUND(SYSDATE, 'MONTH') from dual;

-- TRUNC测试 2020-12-16 00:00:00
select TRUNC(SYSDATE) from dual;
-- TRUNC测试 2020-01-01 00:00:00
select TRUNC(SYSDATE, 'YEAR') from dual;
-- TRUNC测试 2020-12-01 00:00:00
select TRUNC(SYSDATE, 'MONTH') from dual;

-- CURRENT_DATE测试 2020-12-16 00:45:10
select CURRENT_DATE from dual;

-- EXTRACT测试 2020
select EXTRACT(YEAR from SYSDATE) from dual;
-- EXTRACT测试 12
select EXTRACT(MONTH from SYSDATE) from dual;
-- EXTRACT测试 16
select EXTRACT(DAY from SYSDATE) from dual;

99.3 常用的类型转换函数

函数	描述
TO_CHAR	将一个数字或日期转换成字符串
TO_NUMBER	将字符型数据转换成数字型数据
TO_DATE	将字符型数据转换为日期型数据
CAST	将一种built-in类型转换成另一种built-in类型

-- TO_CHAR测试 100
select TO_CHAR(100) from dual;
-- TO_CHAR测试 2020-12-16 00:46:41
select TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') from dual;

-- TO_NUMBER测试 10
select TO_NUMBER('10') from dual;
-- TO_NUMBER测试 invalid number
select TO_NUMBER('QiangThink') from dual;

-- TO_DATE测试 2021-09-29 00:00:00
select TO_DATE('2021-9-29', 'YYYY-MM-DD') from dual;

-- CAST测试 100
select CAST('100' as NUMBER) from dual;
-- CAST测试 2
select CAST(2 as char) from dual;

99.4 常用的集合函数

函数	描述
AVG	计算一列值的平均值
COUNT	统计一列中值的个数
MAX	求一列值中的最大值
MIN	求一列值中的最小值
SUM	计算一列值的总和

99.5 常用的其它函数

函数	描述
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

-- DECODE测试 30
select DECODE(20, 10, 5, 200, 10, 20, 30) from dual;

-- SIGN测试 1
select SIGN(20) from dual;
-- SIGN测试 -1
select SIGN(-30) from dual;
-- SIGN测试 0
select SIGN(0) from dual;

-- TRUNC测试 20.21
select TRUNC(20.2183, 2) from dual;
-- TRUNC测试 20.1
select TRUNC(20.1, 4) from dual;

-- GREATEST测试 400
select GREATEST(20, 100, 30, 20, 40, 400) from dual;

-- LEAST测试 20
select LEAST(20, 100, 30, 20, 40, 400) from dual;

-- NULLIF测试 NULL
select NULLIF(20, 20) from dual;
-- NULLIF测试 20
select NULLIF(20, 10) from dual;

-- NVL测试 20
select NVL(20, 30) from dual;
-- NVL测试 30
select NVL(NULL, 30) from dual;

-- NVL2测试 30
select NVL2(NULL, 20, 30) from dual;
-- NVL2测试 20
select NVL2('QiangThink', 20, 30) from dual;

小强崽

Oracle常用的函数

一、IN

二、REPLACE

三、SIGN

四、DECODE

五、TO_CHAR

六、TO_NUMBER

七、NVL

八、FIELD+

九、ROW_NUMBER

十、RANK

十一、DENSE_RANK

十二、SUBSTR

十三、SYS_CONTEXT

十四、INSTR

十五、ROUND

十六、TRIM

十七、LTRIM

十八、RTRIM

九十九、附录

99.1 常用的字符串函数

99.2 常用的日期函数

99.3 常用的类型转换函数

99.4 常用的集合函数

99.5 常用的其它函数

Administrator

一、IN

二、REPLACE

三、SIGN

四、DECODE

五、TO_CHAR

六、TO_NUMBER

七、NVL

八、FIELD+

九、ROW_NUMBER

十、RANK

十一、DENSE_RANK

十二、SUBSTR

十三、SYS_CONTEXT

十四、INSTR

十五、ROUND

十六、TRIM

十七、LTRIM

十八、RTRIM

九十九、附录

99.1 常用的字符串函数

99.2 常用的日期函数

99.3 常用的类型转换函数

99.4 常用的集合函数

99.5 常用的其它函数

Oracle常见问题

Oracle常用关键字

Administrator