内置函数

数值型函数

abs(x)

• 功能 返回x的绝对值
• 参数 x数值型
• 返回 数字

select abs(100),abs(-100) from dual;

sign(x)

• 功能 返回x的正负值
• 参数 x,数值型
• 返回 数字.若正值返回1,负值返回-1,0返回0

select sign(100),sign(-100),sign(0) from dual;

ceil(x)

• 功能 返回大于等于x的最小整数值
• 参数 x,数值型
• 返回 数字

select ceil(3.1),ceil(2.8+1.3),ceil(0),ceil(-3.6)
from dual;

floor(x)

-功能 返回小于等于x的最大整数值
-参数 x,数值型
-返回 数字

select floor(3.1),floor(2.8+1.3),floor(0),ceil(-3.6)
from dual;

power(x,y)

• 功能 返回x的y次幂
• 参数 x,y 数字型
• 返回 数字

select power(2.5,2),power(2.5,-2),power(2.5,0),power(10,2)
from dual;

log(x,y)

• 功能 返回以x为底的y的对数,即log(底,数值)
• 参数 x,y 数值型
• 条件 x,y 都必须大于0
• 返回:数字

select log(2.5,6.25),log(2.5,0.16),log(2.5,1),log(10,100)
from dual;

exp(y)

• 功能 返回e的y次幂(e为数字常量)
• 参数 y,数值型
• 返回 数字

select exp(3),exp(0),exp(-3) from dual;

ln(y)

• 功能 返回以e为底的y的对数(e为数字常量)
• 参数 y,数值型
• 返回 数字

select ln(20.0855369231877),ln(0.0497870683678639
) from dual;

mod(x,y)

• 功能 返回x除以y的余数(取余)
• 参数 x,y 数值型
• 返回 数字

select mod(23,8),mod(82,3) from dual;

round(x[,y])

• 功能 返回四舍五入后的值

如果y不为整数则截取y整数部分,
如果y>0则四舍五入为y位小数
如果y<0则四舍五入到小数点向左y为

• 参数 x,y 数值型

select round(5555.6666,2.6),
       round(5555.6666,-2.6),
       round(5555.6666,3),
       round(5555.6666)
from dual;

trunc(x,[,y])

• 功能 返回x 按精度y截取后的值
• 参数 x,y 数字型表达式,

如果y不为整数则截取y整数部分
如果y>0,x则截取到y位小数
如果y<0,x则截取到小数点向左第y位

• 返回 数字

select trunc(5555.6666,2.6),
       trunc(5555.6666,-2.6),
       trunc(5555.6666,-3),
       trunc(5555.6666)
from dual;

sqrt(x)

• 功能 返回x的平方根
• 参数 x,数值型
• 返回 数字

select sqrt(18),sqrt(10) from dual;

字符型函数

ascii(x1)

• 功能 返回字符表达式最左端字符的ASCII码值
• 参数 x1,字符表达式
• 返回 数值型

select ascii('A'),ascii('a'),ascii('1')
from dual;

chr(n1)

• 功能 将ascii码转换为字符
• 参数 n1,为0~255,整数
• 返回 字符串

select ascii('我'),ascii('爱'),ascii('你') from dual;

select chr(15108241),chr(15173809),chr(14990752) from dual;

concet(c1,c2)

• 功能 拼接两个字符串
• 参数 c1,c2字符型表达式
• 返回 字符型

select concat('010','88231657')||'转23' 孙先生电话
from dual;

initcap(c1)

• 功能 返回字符串并将字符串的第一个字母变为大写,其他字母变为小写
• 参数 c1,字符型
• 返回 字符型

select initcap('smIIH abc ABC') text 
from dual;

lower(c1)

• 功能 将字符串全部转为小写
• 参数 c1,字符型
• 返回 字符型

select lower('smIIH abc ABC') text
from dual;

upper(c1)

• 功能 将字符串全部转为大写
• 参数 c1,字符型
• 返回 字符型

select upper('smIIH abc ABC') text
from dual;

instr(C1,C2[,I[,J]])

• 功能 在一个字符串中搜索指定的字符,返回发现指定的字符的位置
• 说明 多字节符(汉字,全角符等),按一个字符计算
• 参数
  • C1 , 被搜索的字符串
  • C2 , 希望搜索的字符串
  • I ,  搜索的开始位置,默认为1
  • J ,  第J次出现的位置,默认为1
• 返回 数值

select instr('oracle rraning','ra',1,2) instr
from dual;

select instr('青岛大学,山东大学,山东信息职业技术学院','山东',1,2) 学校
from dual;

查看emp表中wname字段中以S开头的员工姓名

select ename from emp where instr(ename,'S')=1;

另一个instrb函数将 多字节符(汉字,全角符等),按2个字符计算

lemgth(C1)

• 功能 返回字符串的长度
• 说明 多字节符(汉字,全角符等),按一个字符计算
• 参数 C1,字符串
• 返回 数值型

select length('哈哈哈'),
       length('hhh'),
       length('22 33')
from dual;

lengthb(C1) 多字节符(汉字,全角符等),按2个字符计算

lpad(c1,n[,c2])

• 功能 在字符串c1的左边用字符串c2填充,指导长度为n时为止
• 参数
  • c1 字符串
  • n 追加后字符总长度
  • c2 追加字符串,默认为空格
• 返回 字符型
• 说明
  • 如果c1长度大于n,则返回c1左边n个字符
  • 如果c1长度小于n,且c2和c1连接后大于n,则返回连接后边n个字符

  select lpad('sun',10,'*?'),
       lpad('sunsunsunsun',10,'*?'),
       lpad('sun',10,'!@#$%^&*()_+')
  from dual;

  

rpad(c1,n[,c2])

• 功能 在字符串c1的右边用字符串c2填充,指导长度为n时为止
• 参数
  • c1 字符串
  • n 追加后字符总长度
  • c2 追加字符串,默认为空格
• 返回 字符型
• 说明
  • 如果c1长度大于n,则返回c2左边n个字符
  • 如果c1长度小于n,c1和c2连接后大于n ,则返回连接后的左边n个字符
  • 如果c1长度小于n,c1和c2连接后大于n,则返回c1与多个重复的c2连接(总长度>=n)后的左边n个字符

在字符串’gao’的左边和右边分别加7个6

select lpad(rpad('gao',10,'6666666'),17,'6666666') text
from dual;
select rpad(lpad('gao',10,'6666666'),17,'6666666') text 
from dual;

截取字符

select rpad('sun6666',3)
from dual;

ltrim(c1[,c2])

• 功能 删除左边字符串
• 参数
  • c1,字符串
  • c2 追加字符串,默认为空格
• 返回 字符型

select ltrim('  qing dao shi'),
       ltrim('qing dao shi','qing dao ')
from dual;

rtrim(c1[,c2])

• 功能 删除右边出现的字符串
• 参数
  • c1 字符串
  • c2 追加字符串,默认为空格
• 返回 字符型

select rtrim(' qing dao shi',' shi')
from dual;

trim(c1 from c2)

• 功能 删除左边和右边出现的字符串
• 参数
  • c2 删除前的字符串
  • c1 要删除的字符串,默认为空格
• 返回 字符型

select trim('  qing dao shi  '),
       trim('*' from '***qing dao shi***')
from dual;

如果源数据中张三的前面或者后面有空字符,直接查找是找不到的

select * from student where trim(sname)='张三';

replace(c1,c2[,c3])

• 功能 将字符表达式值中,部分相同字符串,替换成新的字符串
• 参数
  • c1希望被替换的字符或变量
  • c2 被替换的字符串
  • c3 要替换的字符串,默认为空(即删除)

select replace('i love you','i','he'),
       replace('i love you','i ')
from dual;

substr(c1,n1[,n2])

• 功能 截取字符串
• 说明 多字节符(汉字,全角符等),按一个字符计算
• 参数
  • 在字符表达式c1里,从n1开始截取n2个字符;若不指定n2则截取从第y个字符指导结束的字符
• 返回 字符型

select substr('20191029',1,4),
       substr('20191029',5)
from dual;

substrb(c1,n1[,n2]) 多字节符(汉字,全角符等),按2个字符计算

translate(c1.c2,c3)

• 功能 将字符表达式中,指定字符替换为新字符
• 说明
  • 多字节符(汉字,全角符等),按1个字符计算
• 参数
  • c1 希望被替换的字符或变量
  • c2 查询原始的字符集
  • c3 替换新的字符集,将c2对应顺序字符,替换为c3对应顺序字符
• 注意
  • 如果c3长度大于c2,则c3后面长处的字符无效
  • 如果c3长度小于c2,则c2后面长处的字符均替换为空(删除)
  • 如果c3长度为0,则返回空字符串
  • 如果c2里字符重复,按首次位置为替换依据

select translate('he love you','he','i'),
       translate('he love you','o','i')
from dual;

日期函数

sysdate

• 功能 返回系统当前日期
• 返回 日期

select sysdate time
from dual;

add_months(d1,n1)

• 功能: 返回在日期d1基础上再加n1个月后的日期
• 参数:
  • d1,日期型
  • n1,数字型

select sysdate,
       add_months(sysdate,2),
       add_months(sysdate,-2)
from dual;

last_day(d1)

• 功能 返回日期d1所在月份最后一天的日期
• 参数 d1日期型
• 返回 日期

select sysdate,
       last_day(sysdate)
from dual;

months_between(d1,d2)

• 功能 返回日期d1到日期d2之间的月数
• 参数 d1,d2 日期型
• 返回 数字
  • 如果d1>d2,则返回正数
  • 如果d1<d2,则返回负数

select sysdate,
       months_between(sysdate,to_date('2019-01-01','YYYY-MM-DD')),
       months_between(sysdate,to_date('2020-01-01','YYYY-MM-DD'))
from dual;

new_time(dt1,c1,c2)

• 功能 给出时间dt1在c1时区对应c2时区的日期和时间
• 参数
• dt1 日期型

• c1,c2对应的 时区及其简写

  大西洋标准时间：AST或ADT
  阿拉斯加_夏威夷时间：HST或HDT
  英国夏令时：BST或BDT
  美国山区时间：MST或MDT
  美国中央时区：CST或CDT
  新大陆标准时间：NST
  美国东部时间：EST或EDT
  太平洋标准时间：PST或PDT
  格林威治标准时间：GMT
  Yukou标准时间：YST或YDT

select sysdate bj_time,
       new_time(sysdate,'PDT','GMT') los_angles
from dual;

round(d1[,c1])

• 功能
  • 给出日期d1 按期间(参数c1) 四舍五入后的期间的第一天日期(与数值四舍五入意思相近)
• 参数
  • d1日期型
  • c1字符型
  • c1默认为j(即最近0点日期)
• c1对应的参数表
  • 最近0点日期 取消参数c1或j
  • 最近的星期日 day或者dy或者d
  • 最近月初日期 month或者mon或者mm或者rm
  • 最近季日期 q
  • 最近年初日期 syear或者year或者yyyy或者yyy或者yy或者y(多个y表示精度)
  • 最近世纪初日期 cc或者scc

  select sysdate 当前日期,
       round(sysdate,'syear') 最近年初日期,
       round(sysdate,'q') 最近季度日期,
       round(sysdate,'month') 最近月初,
       round(sysdate) 最近0点 
  from dual;

  

trunc(d1[,c1])

• 功能
  • 返回日期d1所在期间(参数c1)的第一天日期(返回日期所在期间的第一天)
• 参数
  • d1日期型,
  • c1位字符型,默认为当前日期

select sysdate 当前日期,
       trunc(sysdate) 今天日期,
       trunc(sysdate,'day') 本周日,
       trunc(sysdate,'month') 本月初,
       trunc(sysdate,'q') 本季初,
       trunc(sysdate,'year') 本年初日期 
from dual;

next_day(d1[,c1])

• 功能
  • 从特定日期得到未来第一个星期几的日期
• 参数
  • d1日期型
  • c1字符型,默认为当前日期
• 参数表
  • 星期一,星期二,星期三,星期四,星期五,星期六,星期日

从当前日期得到之后的第一个星期几的日期

select sysdate 当前日期,
       next_day(sysdate,'星期一') 未来第一个星期一,
       next_day(sysdate,'星期二') 未来第一个星期二,
       next_day(sysdate,'星期三') 未来第一个星期三,
       next_day(sysdate,'星期四') 未来第一个星期四,
       next_day(sysdate,'星期五') 未来第一个星期五,
       next_day(sysdate,'星期六') 未来第一个星期六,
       next_day(sysdate,'星期日') 未来第一个星期日 
from dual;

extract(c1 from d1)

• 功能
  • 提取时间日期中的数据
• 参数
  • d1日期型(date)/日期时间型(timestamp)
  • c1为字符型(参数)

select 
     extract(hour from timestamp '2001-2-16 2:38:40 ' ) 小时,
     extract(minute from timestamp '2001-2-16 2:38:40 ' ) 分钟,
     extract(second from timestamp '2001-2-16 2:38:40 ' ) 秒,
     extract(DAY from timestamp '2001-2-16 2:38:40 ' ) 日,
     extract(MONTH from timestamp '2001-2-16 2:38:40 ' ) 月,
     extract(YEAR from timestamp '2001-2-16 2:38:40 ' ) 年
from dual;

select sysdate 当前日期,
       extract(DAY from sysdate ) 日,
       extract(MONTH from sysdate ) 月,
       extract(YEAR from sysdate ) 年
from dual;

dbtimezone

• 功能 返回数据库时区

select dbtimezone 
from dual;

sessiontimezone

• 功能 返回当前会话时区
• 参数 没有参数,没有括号
• 返回 字符型

select dbtimezone,
       sessiontimezone 
from dual;

interval c1 set1

• 功能 变动日期时间数值
• 参数
  • c1位数字字符串或日期时间字符串
  • set为日期参数
• 返回
  • 日期时间格式的数值,前面多个+号
  • 以天或天更小为单位时可用 数值表达式 借用
  • 如1表示1天,1/24表示1小时,1/24/60表示1分钟

select trunc(sysdate)+(interval '1' second) 加1秒,
       trunc(sysdate)+(interval '1' minute) 加1分钟,
       trunc(sysdate)+(interval '1' hour) 加1小时,
       trunc(sysdate)+(INTERVAL '1' DAY) 加1天,
       trunc(sysdate)+(INTERVAL '1' MONTH) 加1月,
       trunc(sysdate)+(INTERVAL '1' YEAR) 加1年,
       trunc(sysdate)+(interval '01:02:03' hour to second) 加指定小时到秒,
       trunc(sysdate)+(interval '01:02' minute to second) 加指定分钟到秒,
       trunc(sysdate)+(interval '01:02' hour to minute) 加指定小时到分钟,
       trunc(sysdate)+(interval '2 01:02' day to minute) 加指定天数到分钟                      
from dual;