Oracle使用单行函数
1.1 【函数】
单行:输入一行,返回一行。
多行:输入多行,返回一行。
1.1.1 单行函数
字符函数、数字函数、日期函数、转换函数
1.1.2 【dual 表】
**DUAL **表是一个“伪表”(虚拟表),只包含一个列 DUMMY,数据类型为 VARCHAR2(1)。DUAL 表只包含一行,其 DUMMY 列的值为 X。**DUAL **表是为了语法上的完整性。
1.2 【字符函数】
1.2.1 【大小写转换函数】
lower 小写, upper 大写, initcap 首字母大写。
SCOTT@orcl>select empno,ename,job from emp where ename='scott';
SCOTT@orcl>select empno,ename,job from emp where ename=upper('scott');
SCOTT@orcl>select ename,lower(ename),upper(ename),initcap(ename) from emp;
1.2.1.1【initcap】分隔符
SCOTT@orcl>select initcap('beijing,enmoedu_ocp%ocm') from dual;
空格、下划线、 %、!、 **$**等都是有效的单词分隔符。(特殊符号 标点符号)
1.2.2 【字符操作函数】
1.2.2.1【concat】 连接字符串、列、表达式
SCOTT@orcl>select concat('good','dba') from dual;
SCOTT@orcl>select ename||' is '||job from emp;
SCOTT@orcl>select concat(concat(ename,' is '),job) from emp;
1.2.2.2【substr】截取子串
SCOTT@orcl>select substr('oracle',1,3) from dual;
SCOTT@orcl>select substr('oracle',-5) from dual;
1.2.2.3【length】获取字符串长度(字符数)
OCP 考点:单行函数可能发生数据类型改变,此处length显示的number
SCOTT@orcl>select length('oracle') from dual;
1.2.2.4【instr】后面子串在前面字符串第一次出现的位置。
SCOTT@orcl>select instr('oracle','a') from dual;
SCOTT@orcl>select instr('oracle','b') from dual;
=0 表示没有。
**instr **经常用来做判断,判断一个字符串是否在另外一个字符串中。
查询从第 **3 **个字符开始, *****第 **4 **次出现的位置
查询从倒数第 **3 **个字符开始向前, *****第 **2 **次出现的位置
SCOTT@orcl>select instr('1*2*3*4*5*','*',3,4) from dual;
SCOTT@orcl>select instr('1*2*3*4*5*','*',-3,2) from dual;
1.2.2.5【lpad,rpad】左补全,右不全。
SCOTT@orcl>select sal,lpad(sal,4,0),rpad(sal,4,0) from emp;
1.2.2.6【trim】去掉前后空格,或者 从后面串 首尾去掉前面字符。
SCOTT@orcl>select trim(' hello ') from dual;
SCOTT@orcl>select length(' hello '),length(trim(' hello ')) from dual;
SCOTT@orcl>select trim('*' from '***oracle***') from dual;
SCOTT@orcl>select trim('**' from '***oracle****') from dual;
要去除的串 **trim_character **只能是单个字符。
官方文档位置:
SQL Language Reference -> 5 Functions -> CTRL+F 搜索 trim
leading /trailing/both 开头、结尾、两头 删除字符
SCOTT@orcl>select trim(leading '*' from '***oracle***') from dual;
SCOTT@orcl>select trim(trailing '*' from '***oracle***') from dual;
SCOTT@orcl>select trim(both '*' from '***oracle***') from dual;
1.2.2.7【replace】替换
SCOTT@orcl>select replace('oracle','ora','ORA-') from dual;
SCOTT@orcl>select replace(hiredate,'-') from emp;
1.3 【数字函数】
**round **四舍五入, **trunc **截取, **mod **取模 取余数。
1.3.1 【round】四舍五入
SCOTT@orcl>select round(45.923,2),round(45.923,0),round(49.923,-1) from dual;
1.3.2 【trunc】截断 取整
SCOTT@orcl>select trunc(45.923,2),trunc(45.923),trunc(45.923,-1) from dual;
1.3.3 【mod】取余数
SCOTT@orcl>select mod(1600,300) from dual;
SCOTT@orcl>select 1600/300 from dual;
SCOTT@orcl>select trunc(1600/300,0) from dual;
SCOTT@orcl>select trunc(1600/300,0),mod(1600,300) from dual;
商: quotient
余数: remainder
1.4 【日期函数】
类型: DATE
还有一种类型,时间戳,精度更高。
1.4.1 【日期格式】
1.4.1.1【数据库默认日期格式】
注意用户
SYS@orcl>select property_name,property_value from database_properties
2 where property_name='NLS_DATE_FORMAT';
1.4.1.2【当前 session 日期格式】
SYS@orcl>select * from v$nls_parameters
2 where parameter='NLS_DATE_FORMAT';
1.4.1.3【sysdate】
返回 Oracle 数据库服务器所在操作系统的当前日期和时间。
时间信息包含在 SYSDATE 中,默认不显示。
可以通过 TO_CHAR 转换函数提取时间信息或者通过改变 NLS_DATE_FORMAT 会话参数来更改设置。
SYS@orcl>select sysdate from dual;
1.4.1.4【设置会话时间格式】
SYS@orcl>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss day';
SYS@orcl>select sysdate from dual;
1.4.1.5【对日期进行计算】
SYS@orcl>select sysdate,sysdate+1,sysdate+1/24,sysdate+1/24/60 from dual;
1.4.2 【months_between】
SCOTT@orcl>select empno,ename,sal,months_between(sysdate,hiredate) months from emp;
1.4.3 【add_months】 为日期增加月份
SCOTT@orcl>select sysdate,add_months(sysdate,1),add_months(sysdate,12),
2 add_months(sysdate,-12) from dual;
1.4.4 【next_day】 下一个
SCOTT@orcl>select next_day(sysdate,'MONDAY') from dual;
SCOTT@orcl>select next_day(sysdate,2) from dual;
-- 下周第二天(西方以周日为第一日)
SCOTT@orcl>select next_day(sysdate,'FRIDAY') from dual;
1.4.5 【last_day】某月最后一天 (当前时间)
SCOTT@orcl>select sysdate,last_day(sysdate) from dual;
1.4.6 【日期 round】对日期四舍五入
SCOTT@orcl>select sysdate,round(sysdate),sysdate,round(sysdate,'mi'),
2 round(sysdate,'DD') from dual;
1.4.7 【日期 trunc】 对日期取整
SCOTT@orcl>select sysdate,trunc(sysdate),trunc(sysdate,'dd'),trunc(sysdate,'mm'),
2 trunc(sysdate,'yyyy') from dual;
1.5 【函数的嵌套】
SCOTT@orcl>select ename,concat(lower(substr(ename,1,3)),'_US') from emp
2 where deptno=10;