--1.ceil:返回大于等于数字N的最小整数
SQL> select ceil(15),ceil(15.1) from dual;
CEIL(15) CEIL(15.1)
---------- ----------
15 16
--2.floor:返回小于等于数字N的最大整数
SQL> select floor(15),floor(15.1) from dual;
FLOOR(15) FLOOR(15.1)
---------- -----------
15 15
--3.mod(m,n):取得两个数字相除后的余数
SQL> select mod(null,100),mod(100,33) from dual;
MOD(NULL,100) MOD(100,33)
------------- -----------
1
--4.round(n,[m]):返回四舍五入结果，m必须是整数
SQL> select round(10.23,null),round(10.26,1) from dual;
ROUND(10.23,NULL) ROUND(10.26,1)
----------------- --------------
10.3
--5.power(m,n):返回数字m的n次幂
SQL> select power(2,3),power(-2,3) from dual;
POWER(2,3) POWER(-2,3)
---------- -----------
8 -8
--6.trunc(n,[m]):截取数字
SQL> select trunc(45.926),trunc(45.926,1),trunc(45.926,-1) from dual;
TRUNC(45.926) TRUNC(45.926,1) TRUNC(45.926,-1)
------------- --------------- ----------------
45 45.9 40
--7.abs(n):返回数字n的绝对值
SQL> select abs(null),abs(15),abs(-15) from dual;
ABS(NULL) ABS(15) ABS(-15)
---------- ---------- ----------
15 15
--8.concat(str1,str2):连接字符串
SQL> select concat('Good',' Morning') from dual;
CONCAT('GOOD
------------
Good Morning
--9.initcap(char):将字符串每个单词的首字符大写
SQL> select initcap(null),initcap('world') from dual;
I INITC
- -----
World
SQL> select initcap('welcome to beijing') from dual;
INITCAP('WELCOMETO
------------------
Welcome To Beijing
--10.instr(char1,char2[,n[,m]]):取得子串在字符串中的位置
SQL> select instr('morning','n'),instr('abcdefg','c') from dual;
INSTR('MORNING','N') INSTR('ABCDEFG','C')
-------------------- --------------------
4 3
--11.length(char):返回字符串的长度
SQL> select length(null),length('morning') from dual;
LENGTH(NULL) LENGTH('MORNING')
------------ -----------------
7
--12.vsize(expr):返回oracle内部存储expr的实际字节数
SQL> select vsize('ab') from dual;
VSIZE('AB')
-----------
2
--13.lower(char):将字符串转换为小写
SQL> select lower('ABCD') from dual;
LOWE
----
abcd
--14.substr(char,m[,n]):取得字符串的子串
SQL> select substr('morning',2,4) from dual;
SUBS
----
orni
--15.upper(char):将字符串转换为大写
SQL> select upper('abcd') from dual;
UPPE
----
ABCD
--16.ascii(char):返回字符串首字符的ascii码值
SQL> select ascii('a'),ascii('b') from dual;
ASCII('A') ASCII('B')
---------- ----------
97 98
--17.chr(n):将ascii码值转变为字符
SQL> select chr(null),chr(97),chr(98) from dual;
C C C
- - -
a b
--18.lpad(char1,n,char2):在char1左端填充char2，直到总长度为n
SQL> select lpad(1000,6,'*') from dual;
LPAD(1
------
**1000
SQL> select lpad(1000,8,'*') from dual;
LPAD(100
--------
****1000
--19.ltrim(char1[,set]):去掉字符串char1左端所包含的set中的任何字符
SQL> select ltrim('morning','m'),ltrim('morning','or') from dual;
LTRIM( LTRIM('
------ -------
orning morning
--20.replace(char,search_string[,replacement_string]):将字符串子串替换为其他子串
SQL> select replace('缺省值为10','缺省','默认') from dual;
REPLACE('
----------
默认值为10
--21.rpad(char1,n,char2):在字符串char1的右端填充char2,直到长度达到n
SQL> select rpad('hello',10,'-') from dual;
RPAD('HELL
----------
hello-----
--22.rtrim(char[,set]):用于去掉字符串char右端所包含的Set中的任何字符
SQL> select rtrim('morning','ing') from dual;
RTR
---
mor
--23.last_day(d):返回特定日期所在月份的最后一天
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDA
--------------
30-11月-13
--24.to_char
SQL> select to_char(sysdate) "当前日期" from dual;
当前日期
--------------
01-11月-13
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI') from dual;
TO_CHAR(SYSDATE,
----------------
2013-11-01 16:21
--25.asciistr(string):将字符串转变为ascii字串值
SQL> select asciistr('中国') from dual;
ASCIISTR('
----------
\4E2D\56FD
--26.cast(expr as type_name)
SQL> select cast('01-11月-13' as date) from dual;
CAST('01-11月-
--------------
01-11月-13
--27.decode(expr,search1,result[,search2,result2,...][,default])
SQL> select deptno,ename,sal,
2 decode(deptno,10,sal*1.2,20,sal*1.1,sal) "New Salary"
3 from emp
4 order by deptno;
DEPTNO 姓名 工资 New Salary
---------- --------------- --------------- ----------
10 CLARK ￥2450 2940
10 KING ￥5000 6000
10 MILLER ￥1300 1560
20 SMITH ￥800 880
20 ADAMS ￥1100 1210
20 FORD ￥3000 3300
20 SCOTT ￥3000 3300
20 JONES ￥2975 3272.5
30 ALLEN ￥1600 1600
30 BLAKE ￥2850 2850
30 MARTIN ￥1250 1250
30 JAMES ￥950 950
30 TURNER ￥1500 1500
30 WARD ￥1250 1250
14 rows selected.
--28.nullif(expr1,expr2):如果二者相等，则返回NULL，否则返回expr1
SQL> select ename,sal,nullif(sal,5000) from emp;
姓名 工资 NULLIF(SAL,5000)
--------------- --------------- ----------------
SMITH ￥800 800
ALLEN ￥1600 1600
WARD ￥1250 1250
JONES ￥2975 2975
MARTIN ￥1250 1250
BLAKE ￥2850 2850
CLARK ￥2450 2450
SCOTT ￥3000 3000
KING ￥5000
TURNER ￥1500 1500
ADAMS ￥1100 1100
JAMES ￥950 950
FORD ￥3000 3000
MILLER ￥1300 1300
14 rows selected.
--29.user:返回当前会话对应数据库用户名
SQL> select user from dual;
USER
------------------------------
SCOTT
--30.userenv(parameter):返回当前会话上下文的属性信息
parameter:isdba、language、terminal、client_info
SQL> select userenv('isdba') from dual;
USEREN
------
FALSE
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
SQL> select userenv('terminal') from dual;
USERENV('TERMINA
----------------
LINGLONG-MES
SQL> select userenv('client_info') from dual;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
--31.coalesce(expr1[,expr2]...):返回表达式中第一个not null表达式的结果
SQL> select coalesce(comm,sal),comm,sal from emp
2 where deptno=30;
COALESCE(COMM,SAL) COMM 工资
------------------ ---------- ---------------
300 300 ￥1600
500 500 ￥1250
1400 1400 ￥1250
2850 ￥2850
0 0 ￥1500
950 ￥950
6 rows selected.
--32.sys_guid:生成类型raw的16字节的唯一标示符
SQL> select sys_guid() from dual;
SYS_GUID()
--------------------------------
4D3156A8C83A4EEC8A89343705007B26
扫一扫
01-06
1856
1856
06-06
2867
2867
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
