03、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;