Oracle数据库内置函数

--ORACLE内置函数:单⾏函数，集合函数--1.绝对值，取余，判断数据正负函数，

SELECT ABS(100),ABS(-100),ABS('100') FROM DUAL;

SELECT MOD(5,2) MOD(8/3,5),MOD('10',5),MOD(-10,6),MOD(1,0) FROM DUAL;SELECT SIGN('9'),SIGN(-9),SIGN(0.00),SIGN(-2*'9') FROM DUAL;--三⾓函数

SELECT COS(3.1415926),COS('3.1415926') FROM DUAL;ACOS(N)返回N的反余弦值COSH(N)返回N的双曲余弦值SIN(N)返回N的正炫值

SINH(N)返回N的双曲正炫值ASIN(N) 返回N的反正炫值TAN(N)返回N的正切值

TANH(N)返回N的双曲正切值ATAN(N)返回N的正切值

--返回以指定数值为准整数的函数

SELECT CELL(10),CELL('10.5'),CELL(-10.2) FROM DUAL;

SELECT FLOOR(10),FLOOR('10.5'),FLOOR(-10.2) FROM DUAL;--指数对数

SELECT SQRT(100),SQRT('53.9') FROM DUAL;

SELECT POWER(5,2),POWER('5',2),POWER(5.5,2.5), FROM DUAL;SELECT LOG(10,100),LOG(10.5,'100'),POWER(10,2) FROM DUAL;

--四舍五⼊

SELECT ROUND(100.23456,4),ROUND(100.23456,2.56),ROUND(155.23456,-2) FROM DUAL;--ASCII码与字符转换函数

SELECT CHR(65)||CHR(66)||CHR(67) ABC,CHR(54678) FROM DUAL;SELECT ASCII('明') ,ASCII('ADB'),ASCII('ABC') FROM DUAL;

--带排序参数的字母⼤⼩写转换函数

--NLS_INITCAP(PARAM) 将指定参数的第⼀个字母转换成⼤写--NLS_UPPER(PARAM) 将指定参数变成⼤写--NLS_LOWER(PARAM) 将指定参数变成⼩写

SELECT NLS_INITCAP('a test'),NLS_INITCAP('my test','nls_sort=schinese_stroke_m') from dual;

SELECT NLS_INITCAP('this is a test'),NLS_INITCAP('my test','nls_sort=schinese_pinyin_m') from dual;SELECT NLS_INITCAP('A TEST'),NLS_INITCAP('MY TEST','NLS_SORT=schinese_stroke_m') from dual;--为指定参数排序函数

SELECT * FROM PRODUCTINFO ORDER BY NLSSORT(PROCEDURE,'NLS_SORT=SCHINESE_PINYIN_M')select trim(trailing 't' from 'test'),trim(' test ') from dual;select rtrim('test*ffs','fs*t') from dual;

select ltrim(' ftest',' f'),length(ltrim(' ftest',' f')) from dual;--字符集名称和ID转换函数

select nls_charset_id('US7ASCII') from dual;select nls_charset_name(1) from dual;

--⽇期函数

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

select dbtimezone from dual; --得到数据库时区函数

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')one,to_char(new_time(sysdate,'pdt','est'),'yyyy-mm-dd hh24:mi:ss')two from dual;--时区时间转换函数--字符串转ASCII类型字符串函数

select ASCIISTR('这是测试！') from dual;--⼆进制转换为⼗进制

select bin_to_num(1),bin_to_num(1,0,0),bin_to_num(1,1,1) from dual;--数据类型转换函数cast()

--字符串转换rowid函数

select chartorowid('AAAABBBCCC') from dual;select rowidtochar(rowid) from dual;

--字符串在字符集间转换函数

select convert('测试','US7ASCII','ZHS16GBK') from dual;--字符串转化为数字函数

select to_number('2456.304','9999.999') from dual;--排除指定条件函数

select * from productinfo where lnnvl(quantity >= 70);提取姓名的⼤写⾸字母缩写

select replace(replace(translate('Lily James','abcdefghijklmnopqrstuvwxyz',rpad('#', 26, '#')),'#',''),' ','') from dual;