一、数值型函数
函 数 | 功 能 | 实 例 | 结 果 |
abs | 求绝对值函数 | abs(−5) | 5 |
sqrt | 求平方根函数 | sqrt(2) | 1.41421356 |
power | 求幂函数 | power(2,3) | 8 |
cos | 求余弦三角函数(用弧度) | cos(3.14159) | −1 |
mod | 求除法余数 | mod(1600, 300) | 100 |
ceil | 求大于等于某数的最小整数 | ceil(2.35) | 3 |
floor | 求小于等于某数的最大整数 | floor(2.35) | 2 |
round | 按指定精度对十进制数四舍五入 | round(45.923, 1) round(45.923, 0) round(45.923, −1) | 45.9 46 50 |
trunc | 按指定精度截断十进制数 | trunc(45.923, 1) trunc(45.923) trunc(45.923, −1) | 45.9 45 40 |
二、oracle中的dual表
2.1 理解
1、DUAL是 SYS用户的一个TABLE.
2、Dual是Oracle中的一个实际存在的表,任何用户均可读取,常用在没有目标表的Select语句块中,用来构成select的语法规则,oracle保证dual里面永远只有一条记录。
3、DUAL就是个一行一列的表,该表和系统中的其他表一样,一样可以执行插入、更新、删除操作,还可以执行drop操作。但是不要去执行drop表的操作,否则会使系统不能用,数据库起不了,会报Database startup crashes with ORA-1092错误。
2.2 DUAL 能做什么
1、查找当天日期
SQL> select sysdate from dual;
2、查找当前日期的当月第一天
SQL> select trunc(sysdate,’MONTH’) from dual;
3、查找当前日期的当月最后一天
SQL> select trunc(last_day(sysdate)) from dual;
4、查看当前用户,可以在SQL Plus中执行下面语句
SQL> select user from dual;
5、获得当前系统时间
SQL> select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual;
6、获得主机名
SQL>select SYS_CONTEXT(‘USERENV’,'TERMINAL’) from dual;
7、获得当前locale
SQL>select SYS_CONTEXT(‘USERENV’,'language’) from dual;
8、获得一个随机数
SQL>select dbms_random.random from dual;
9、查看当前日期、时间
SQL> select sysdate from dual;
10、获得序列your_sequence的下一个值
SQL>select your_sequence.nextval from dual;
11、获得序列your_sequence的当前值
SQL>select your_sequence.currval from dual;
12、可以用做计算器
参考网址:http://yesican.blog.51cto.com/700694/269814
http://www.itfarmer.com.cn/plsql/1093.html
三、字符型函数
函数名称 | 功 能 | 实 例 | 结 果 |
ascii | 获得字符的ASCII 码 | Ascii('A') | 65 |
chr | 返回与ASCII 码相应的字符 | Chr(65) | A |
lower | 将字符串转换成小写 | lower ('SQL Course') | sql course |
upper | 将字符串转换成大写 | upper('SQL Course') | SQL COURSE |
initcap | 将字符串转换成每个单词以大写开头 | initcap('SQL course') | Sql Course |
concat | 连接两个字符串 | concat('SQL', ' Course') | SQL Course |
substr | 给出起始位置和长度,返回子字符串 | substr('String',1,3) | Str |
length | 求字符串的长度 | length('Wellcom') | 7 |
instr | 给出起始位置和出现的次数,求子字符串在字符串中出现的位置 | instr('String', 'r',1,1) | 3 |
lpad | 用字符填充字符串左侧到指定长度 | lpad('Hi',10,'-') | --------Hi |
rpad | 用字符填充字符串右侧到指定长度 | rpad('Hi',10,'-') | Hi-------- |
trim | 在一个字符串中去除另一个字符串 | trim('S' FROM 'SSMITH') | MITH |
replace | 用一个字符串替换另一个字符串中的子字符串 | replace('ABC', 'B', 'D') | ADC |
四、日期型函数
1、Oracle使用内部数字格式来保存时间和日期,包括世纪、年、月、日、小时、分、秒。缺省日期格式为DD-MON-YY,如“08-05月-03”代表2003年5月8日。2、SYSDATE是返回系统日期和时间的虚列函数。
函 数 | 功 能 | 实 例 | 结 果 |
months_between | 返回两个日期间的月份 | months_between ('04-11 月-05','11-1 月 -01') | 57.7741935 |
add_months | 返回把月份数加到日期上的新日期 | add_months('06-2 月-03',1) add_months('06-2 月-03',-1) | 06-3 月-03 06-1 月-03 |
next_day | 返回指定日期后的星期对应的新日期 | next_day('06-2 月-03','星期一') | 10-2 月-03 |
last_day | 返回指定日期所在的月的最后一天 | last_day('06-2 月-03') | 28-2月-03 |
round | 按指定格式对日期进行四舍五入 | round(to_date('13-2 月-03'),'YEAR') round(to_date('13-2 月-03'),'MONTH') round(to_date('13-2 月-03'),'DAY') | 01-1 月-03 01-2 月-03 16-2 月-03 (按周四舍五入) |
trunc | 对日期按指定方式进行截断 | trunc(to_date('06-2 月-03'),'YEAR') trunc(to_date('06-2 月-03'),'MONTH') trunc(to_date('06-2 月-03'),'DAY') | 01-1 月-03 01-2 月-03 02-2 月-03 (按周截断 |
五、日期转换格式字符
代 码 | 代表的格式 | 例 子 |
AM、PM | 上午、下午 | 08 AM |
D | 数字表示的星期(1~7) | 1,2,3,4,5,6,7 |
DD | 数字表示月中的日期(1~31) | 1,2,3,…,31 |
MM | 两位数的月份 | 01,02,…,12 |
Y、YY、YYY、YYYY | 年份的后几位 | 3,03,003,2003 |
RR | 解决Y2K 问题的年度转换 | |
DY | 简写的星期名 | MON,TUE,FRI,… |
DAY | 全拼的星期名 | MONDAY,TUESDAY,… |
MON | 简写的月份名 | JAN,FEB,MAR,… |
MONTH | 全拼的月份名 | JANUARY,FEBRUARY,… |
HH、HH12 | 12小时制的小时(1~12) | 1,2,3,…,12 |
HH24 | 24 小时制的小时(0~23) | 0,1,2,…,23 |
MI | 分(0~59) | 0,1,2,…,59 |
SS | 秒(0~59) | 0,1,2,…,59 |
,./-;: | 原样显示的标点符号 | |
'TEXT' | 引号中的文本原样显示 | TE |
sql: SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS AM DY') FROM dual;
sql:SELECT TO_CHAR(sysdate,'YYYY" 年"MM" 月"DD" 日"')FROM dual;
六、转换函数
函 数 | 功 能 | 实 例 | 结 果 |
To_char | 转换成字符串类型 | To_char(1234.5, '$9999.9') | $1234.5 |
To_date | 转换成日期类型 | To_date('1980-01-01', 'yyyy-mm-dd') | 01-1 月-80 |
To_number | 转换成数值类型 | To_number('1234.5') | 1234.5 |
(1) 字符串到数值。(2) 字符串到日期。(3) 数值到字符串。(4)日期到字符串。
sql:SELECT SYSDATE,to_char(SYSDATE,'YYyysp') FROM dual;
![](https://img-my.csdn.net/uploads/201208/22/1345613840_9521.jpg)
七、数值转换
代 码 | 代表的格式 | 例 子 |
9 | 代表一位数字,如果是正数,前面是空格,如果是负数,前面是-号 | 9999 |
0 | 代表一位数字,在相应的位置上如果没有数字则出现0 | 0000 |
, | 逗号,用作组分隔符 | 99,999 |
. | 小数点,分隔整数和小数 | 999.9 |
$ | $货币符号 | $999.9 |
L | 本地货币符号 | L999.99 |
FM | 去掉前后的空格 | FM999.99 |
EEEE | 科学计数法 | 9.9EEEE |
S | 负数符号−放在开头 | S999.9 |
![](https://img-my.csdn.net/uploads/201208/22/1345615285_4519.jpg)
说明:如果实际位数超过指定位数,则会填充为#号
八、其他常用函数
函 数 | 功 能 | 实 例 | 结 果 |
nvl | 空值转换函数 | nvl(null, '空') | 空 |
decode | 实现分支功能 | decode(1,1, '男', 2, '女') | 男 |
userenv | 返回环境信息 | userenv('LANGUAGE') | SIMPLIFIEDCHINESE_CHINA.ZHS16GBK |
greatest | 返回参数的最大值 | greatest(20,35,18,9) | 35 |
least | 返回参数的最小值 | least(20,35,18,9) | 9 |
说明:字符串的比较原则是,依次转换为ASCII码,先比较第一位,如果相同,则继续比较第二位,依此类推,直到出现大小关系
nvl例:
nvl(comm, 0):用0代替空的Comm值。nvl(hiredate, '01-1月-97'):用1997年1月1日代替空的雇佣日期。
nvl(job, '无'):用“无”代替空的职务。
decode例:
sql:SELECT name,age,decode(age, '26', '年龄26','12','年龄12','年龄不大于10') FROM userinfor;
说明:如果age字段的内容为26”则返回“年龄26”,如果是“12”则返回“年龄12”。如果不是“ 26” 、“ 12” 之一, 则返回“年龄不大于10”
userenv例:
函数userenv返回用户环境信息字符串,该函数只有一个字符串类型的参数,参数的内容为如下之一的字符串,可以不区分大小写:* ISDBA:判断会话用户的角色是否为SYSDBA,是则返回TRUE。
* INSTANCE:返回会话连接的INSTANCE标识符。
* LANGUAGE:返回语言、地区、数据库字符集信息。
* LANG:返回会话语言的ISO简称。
* TERMINAL:返回正在会话的终端或计算机的标识符。