使用单行函数
使用单行函数
1 函数
单行函数:输入一行,返回一行
多行函数:输入多行,返回一行
1.1 单行函数
单行函数:字符函数、 数字函数、 日期函数、 转换函数
1.2 dual表
DUAL表是一个“伪表”(虚拟表),只包含一个列 DUMMY,数据类型为 VARCHAR2(1)
DUAL表只包含一行,其DUMMY列的值为X
DUAL表是为了语法上的完整性
2 字符函数
2.1 大小写转换函数
lower()小写
upper()大写
initcap()首字母大写
select lower(last_name),upper(last_name),initcap(last_name) from emp;
2.2 initcap分隔符
select initcap('oracle_nineteenC.ocp%training') from dual;
空格、下划线、 %、!、$等都是有效的单词分隔符。(特殊符号 标点符号)
3 字符操作函数
3.1 concat()连接字符串、列、表达式
语法:concat(参数1,参数2)
select first_name,last_name,concat(first_name,last_name) full_name from emp;
3.2 substr()截取字串
语法:substr(string,int a,int b)
string:需要截取的字符串
int a:截取字符串的开始位置,数字a
int b:要截取的字符串的长度,数字b
select substr('oracle',2,4) from dual; select substr('oracle',-3,2) from dual; select substr('oracle',-2,2) from dual;
select substr('oracle',-2) from dual; select substr('oracle',2) from dual; select substr('oracle',4) from dual;
3.3 length()获取字符串长度(字符数)
select length('oracle19ctraining') from dual; select last_name,length(last_name) from emp where rownum<=10;
3.4 instr()后面子串在前面字符串第一次出现的位置
语法:instr(字符串,子串)
select instr('oracle','a') from dual; select instr('oracle','cle') from dual; select instr('oracle','b') from dual;
=0表示没有
instr 经常用来做判断,判断一个字符串是否在另外一个字符串中
3.5 lpad()左补全、rpad()右补全
语法:lpad(字符,补全的位数,补全符号)
3.6 trim()去掉前后字符、ltrim()去掉前字符、rtrim()去掉后字符
去掉空格:
select trim(' demo '),ltrim(' demo '),rtrim(' demo ') from dual;
去掉前后特殊符号:
select trim('#' from '###demo##') from dual;
定义特殊符号时,只能定义一个,即'#'不能'##'
3.7 replace()替换
语法:replace(string,strA,strB)
string:需要被替换的字符串
strA:需要被替换掉的子串
strB:替换后的子串
select replace('oracle','ora','ORA-') from dual; select hire_date,replace(hire_date,'-') from emp where rownum<=10;
4 数字函数
round()四舍五入
trunc()截取
mod()取余数
4.1 round()四舍五入
语法:round(数值,小数点位数)
select round(74.415,2),round(74.415,0),round(74.415,-1) from dual;
4.2 trunc()截断 取整
select trunc(74.415,2),trunc(74.415),trunc(74.415,-1) from dual;
4.3 mod()取余数
select mod(1600,300) from dual; select 1600/300 from dual; select trunc(1600/300,0) from dual; select trunc(1600/300,0),mod(1600,300) from dual;
5 日期函数
5.1 日期格式
5.1.1 数据库默认日期格式
col PROPERTY_NAME for a30 col PROPERTY_VALUE for a50 select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME='NLS_DATE_FORMAT';
5.1.2 当前session日期格式
col parameter for a30 col value for a50 HR@orcl> select * from v$nls_parameters where parameter='NLS_DATE_FORMAT';
5.1.3 在数据库中查看当前时间sysdate
select sysdate from dual;
5.1.4 修改当前session会话时间格式
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss day'; select sysdate from dual;
5.1.5 对日期进行计算
select sysdate+1 from dual; select sysdate,sysdate+1/24 from dual; select sysdate,sysdate+1/24/60 from dual;
5.2 months_between()计算月数
select employee_id,last_name,salary,months_between(sysdate,hire_date) months from emp;
5.3 add_months()为日期增加月份
select sysdate from dual; select sysdate,add_months(sysdate,1) from dual; select sysdate,add_months(sysdate,12) from dual; select sysdate,add_months(sysdate,-12) from dual;
5.4 next_day()下一个日期
select next_day(sysdate,'MONDAY') from dual; select next_day(sysdate,2) from dual;
5.5 last_day()某月最后一天 (当前时间)
select sysdate,last_day(sysdate) from dual; select sysdate from dual;
5.6 round()对日期四舍五入
select sysdate,round(sysdate) from dual; select sysdate,round(sysdate,'mi') from dual; select sysdate,round(sysdate,'DD') from dual;
5.7 trunc()对日期取整
select sysdate,trunc(sysdate) from dual; select sysdate,trunc(sysdate,'dd') from dual; select sysdate,trunc(sysdate,'mm') from dual; select sysdate,trunc(sysdate,'yyyy') from dual;
6 函数的嵌套
select last_name,concat(concat(lower(substr(last_name,1,3)),'''s salary is '),salary) from emp;