Oracle 基本函数-数值、字符、 Instr()、日期、转换、SQL 操作符、trunc 截断

￡神魔★判官ぃ 2022-01-26 05:05 371阅读 0赞

**目录**

SQL 操作符 与 单双引号

Oracle 函数概述

多行函数

数值函数

字符函数

字符串查找函数 Instr()

日期函数

转换函数

通用函数

忽略大小写查询字符串

trunc 截断数字和日期

--------------------

[~~~ 准备员工表与部门表测试数据][Link 1]

# SQL 操作符 与 单双引号 #

1、SQL 操作符的优先级从高到低顺序：算术操作符->连接操作符->比较操作符->NOT->AND->OR

<table> 
 <tbody> 
  <tr> 
   <td>算术操作符：加(+)、减(-)、乘(*)、除(/)，其中除号(/)的结果是浮点数。求余运算只能借助函数：MOD(x,y)：返回x除以y的余数。</td> 
   <td>&nbsp; ＋、－、×、÷</td> 
  </tr> 
  <tr> 
   <td>比较操作符</td> 
   <td> <p>=、!=、&lt;、&gt;、&lt;=、&gt;=、BETWEEN…AND、IN、LIKE 和 IS NULL等</p> </td> 
  </tr> 
  <tr> 
   <td> <p>区间查询</p> </td> 
   <td>&nbsp; between …… and ……</td> 
  </tr> 
  <tr> 
   <td>关键字集合查询</td> 
   <td>&nbsp; in</td> 
  </tr> 
  <tr> 
   <td>模糊查询</td> 
   <td>&nbsp; like</td> 
  </tr> 
  <tr> 
   <td>判断是否为空</td> 
   <td>is null&nbsp; 或 is not null</td> 
  </tr> 
  <tr> 
   <td> <p>逻辑操作符</p> <p>//select * from emp t where t.sal is not null and t.comm is not null;</p> </td> 
   <td>与(AND)、或(OR)、非(NOT)</td> 
  </tr> 
  <tr> 
   <td> <p>连接操作符号。用于将两个或多个字符串合并成一个字符串，或将一个字符串与一个数值合并在一起</p> <p>select t.empno,t.ename || '_' || t.sal from emp t ;</p> </td> 
   <td>两根竖杠 "||"，用于将多个字符串或数据值合并成一个字符串</td> 
  </tr> 
 </tbody> 
</table>

2、**Oracle 单双引号**

1、Oracle 中字符串用单引号，别名用双引号。

--拼接的字符必须是单引号，别名没有空格时，可以不带引号，有空格时，必须使用引号，且必须是双引号
    select t.*,'0000' || 'A' as "编 码" from emp t where t.job = 'MANAGER';
    
    -- 两个单引号可以用于转义，如 is '''：第一个是普通的单引号，第二个是对第三个进行转义
    SELECT distinct
    'comment on column bas_person_log.' || t.COLUMN_NAME ||' is ''' || t.COMMENTS || ''';' as coms
    FROM user_col_comments t where t.TABLE_NAME in ('BAS_AGENCY_INFO','BAS_AGENCY_EXT');

[Oracle SQL 操作。][Oracle SQL]

# Oracle 函数概述 #

1、Oracle sqL 提供了用于执行特定操作的专用函数，这些函数大大增强了 SQL 语言的功能。函数可以接受零个或者多个输入参数，并返回一个输出结果。 Oracle数据库中主要使用两种类型的函数：

<table> 
 <tbody> 
  <tr> 
   <td> <p><strong>单行函数</strong></p> <p>对每一个函数应用在表的记录中时,只能输入一行结果,返回一个结果,<br> 比如:MOD(x,y）返回 x 除以 y 的余数 (x和y)可以是两个整数,也可以是表中的整数列)</p> </td> 
   <td>常用的单行函数有:<br> 字符函数:对字符串操作。<br> 数字函数:对数字进行计算，返回一个数字。<br> 转换函数:将一种数据类型转换为另外一种数据类型。<br> 日期函数:对日期和时间进行处理。</td> 
  </tr> 
  <tr> 
   <td>聚合函数</td> 
   <td>聚合函数同时可以对多行数据进行操作,并返回一个结果。比如SUM(x) 返回结果集中ⅹ列的总合。</td> 
  </tr> 
 </tbody> 
</table>

# 多行函数 #

1、对多行的某一列进行处理。常用的有 max()、min()、count()、avg()、sum() 等

<table> 
 <tbody> 
  <tr> 
   <td> <p>select <span style="color:#f33b45;">max</span>(sal) from emp;&nbsp; --取最大值</p> <p>select max(sal) as max_sal from scott.emp t where t.deptno = 100;</p> </td> 
   <td> <p>--查询最高薪资。sal 为 null 的值会自动忽略。</p> <p><span style="color:#f33b45;">--当 select 没有查询到数据时，max_sal会等于null，而不是无记录返回。</span></p> </td> 
  </tr> 
  <tr> 
   <td colspan="2"> <p>select max(to_number(code)) as code from gbm_bs_person_type t</p> <p><span style="color:#fe2c24;">-- 如果字段是字符串类型的数字，则必须先转为数字，否则会出现 '9' 比 '888' 大.</span></p> </td> 
  </tr> 
  <tr> 
   <td> <p>select <span style="color:#f33b45;">min</span>(sal) from emp; -取最小值</p> <p>select min(sal) as min_sal from scott.emp t where t.deptno = 100;</p> </td> 
   <td> <p>--查询最低薪资。sal 为 null 的值会自动忽略</p> <p><span style="color:#f33b45;">--当 select 没有查询到数据时，min_sal&nbsp;会等于null，而不是无记录返回。</span></p> </td> 
  </tr> 
  <tr> 
   <td> <p>select <span style="color:#f33b45;">sum</span>(sal) from emp; --求和</p> <p>select sum(sal) as sum_sal from scott.emp t where t.deptno = 100;</p> </td> 
   <td> <p>--统计员工薪资总和</p> <p><span style="color:#f33b45;">--当 select 没有查询到数据时，sum_sal&nbsp;会等于null，而不是无记录返回。</span></p> </td> 
  </tr> 
  <tr> 
   <td>select sum(comm) from emp;</td> 
   <td>--统计员工奖金总和。为 null 的值会自动忽略</td> 
  </tr> 
  <tr> 
   <td>select <span style="color:#f33b45;">count</span>(*) from emp; --获取个数</td> 
   <td>--统计员工总数.</td> 
  </tr> 
  <tr> 
   <td>select count(1) as c from scott.emp t where t.deptno = 100;</td> 
   <td>--统计员工总数。建议使用常数代替 "*"；当没有数据时，返回 0</td> 
  </tr> 
  <tr> 
   <td> <p>select <span style="color:#f33b45;">avg</span>(comm) from emp; --求平均值</p> <p>select avg(sal) as avg_sal from scott.emp t where t.deptno = 100;</p> </td> 
   <td> <p>--统计平均奖金。当 comm 有 null 值，则计算结果是错误的。</p> <p><span style="color:#f33b45;">--当 select 没有查询到数据时，avg_sal&nbsp;会等于null，而不是无记录返回。</span></p> </td> 
  </tr> 
  <tr> 
   <td>select <span style="color:#f33b45;">sum</span>(comm)/count(1) from emp;</td> 
   <td>--统计平均奖金。当 comm 有 null 值时，可以使用此种方式</td> 
  </tr> 
 </tbody> 
</table>

# 数值函数 #

数值函数接受数字参数,这些参数可以是表中的列,也可以是一个字符串表达式。

<table> 
 <tbody> 
  <tr> 
   <td>select <span style="color:#f33b45;">abs</span>(-100) as val from dual; --求绝对值</td> 
   <td>--输出 100</td> 
  </tr> 
  <tr> 
   <td>select <span style="color:#f33b45;">ceil</span>(3.14159) from dual;</td> 
   <td>--向上取整</td> 
  </tr> 
  <tr> 
   <td>select ename,job,ceil(sal) from emp;</td> 
   <td>--对所有用户的工资向上取整</td> 
  </tr> 
  <tr> 
   <td>select <span style="color:#f33b45;">floor</span>(3.14159) from dual;</td> 
   <td>--向下取整</td> 
  </tr> 
  <tr> 
   <td>select ename,job,floor(sal) from emp;</td> 
   <td>--对所有用户的工资向下取整</td> 
  </tr> 
  <tr> 
   <td>select <span style="color:#f33b45;">round</span>(2.14159,3) from dual;</td> 
   <td>--四舍五入。round(x,[y])，y缺省时默认为0</td> 
  </tr> 
  <tr> 
   <td>select ename,job,round(sal) from emp;</td> 
   <td>--对所有用户的工资四舍五入</td> 
  </tr> 
 </tbody> 
</table>

![20190528102258500.gif][]

<table> 
 <tbody> 
  <tr> 
   <td colspan="2"> <p><span style="color:#f33b45;">trunc(number,number ) 用于截断浮点型数据,.</span><br> 第二个参数表示截断小数点后面的第几位，不会进行四舍五入，number 默认为0。如果第一个参数是字符串，则报错。</p> </td> 
  </tr> 
  <tr> 
   <td>select trunc(3.14159,3) from dual;</td> 
   <td>--截断小数点后面第3位，输出 3.141</td> 
  </tr> 
  <tr> 
   <td>select trunc(3.14159,1) from dual;</td> 
   <td>--截断小数点后面第1位，输出 3.1</td> 
  </tr> 
  <tr> 
   <td>select trunc(3.14159,0) from dual;</td> 
   <td>--截断小数点后面第0位，输出 3</td> 
  </tr> 
  <tr> 
   <td>select ename,job,trunc(sal,0) from emp;</td> 
   <td>--对薪资进行取整.截断是没有四舍五入的</td> 
  </tr> 
  <tr> 
   <td>select <span style="color:#f33b45;">mod</span>(10,3) from dual; --mod 求余函数</td> 
   <td>--输出 1.&nbsp;</td> 
  </tr> 
  <tr> 
   <td>select mod(10,4) from dual;</td> 
   <td>--输出 2.</td> 
  </tr> 
  <tr> 
   <td>select mod(10,5) from dual;</td> 
   <td>--输出 0.</td> 
  </tr> 
  <tr> 
   <td>select ename ,job, mod(sal,30) from emp;</td> 
   <td>--薪资除以30后的余数</td> 
  </tr> 
  <tr> 
   <td> <p>Select <span style="color:#f33b45;">power</span>(2,10)&nbsp; 二的十次方 from dual; --求次方</p> </td> 
   <td>-- power(a,b) ：获取的 a 的 b 次方</td> 
  </tr> 
  <tr> 
   <td>select <span style="color:#f33b45;">Sqrt</span>(16) 十六的开方 from dual;&nbsp; --求平方</td> 
   <td>-- sqrt(x) ：获取 x 的 二分之一次方</td> 
  </tr> 
 </tbody> 
</table>

# 字符函数 #

字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式。

1、concat(字符串1,字符串2) —– 连接字符串，2 个参数

2、lower(str)：字符串转小写，upper(str)：字符串转大写

<table> 
 <tbody> 
  <tr> 
   <td style="width:565px;">select concat('长沙','星城') as citi from dual;</td> 
   <td style="width:361px;">--输出 长沙星城</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select t.stuid,concat(t.stuname,'·'||t.gender),t.age,t.classno from student t;</td> 
   <td style="width:361px;">--拼接用户的姓名与性别，同时中间使用 '·' 符号隔开</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select lower('AbcD') from dual;</td> 
   <td style="width:361px;">--输出 abcd</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select <span style="color:#f33b45;">upper</span>('AbcD') from dual;</td> 
   <td style="width:361px;">--输出 ABCD</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select t.stuid,lower(t.stuname) from student t;</td> 
   <td style="width:361px;">--查询学生学号与姓名，姓名中的英文字母转小写输出。</td> 
  </tr> 
  <tr> 
   <td colspan="2" style="text-align:center;width:565px;"><span style="color:#f33b45;">substr(str,startIndex,length)：</span>str 被截取的字符串，startIndex 从1开始，0也是表示1.</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select substr('ZhangSan',0,3) from dual;</td> 
   <td style="width:361px;">--输出 Zha</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select substr('ZhangSan',1,3) from dual;</td> 
   <td style="width:361px;">--输出 Zha</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select substr('ZhangSan',2,3) from dual;</td> 
   <td style="width:361px;">--输出 han</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select substr('ZhangSan',3,3) from dual;</td> 
   <td style="width:361px;">--输出 ang</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select substr(ename,1,3) ,job from emp;</td> 
   <td style="width:361px;">--截取员工姓名的前3个字符</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select <span style="color:#f33b45;">length</span>('张无忌') from dual;</td> 
   <td style="width:361px;">--输出3&nbsp; length 函数用于求字符串长度</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select length('&nbsp; 张无忌&nbsp; ') from dual;</td> 
   <td style="width:361px;">--输出 7</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select length(ename) from emp;</td> 
   <td style="width:361px;">--输出所有员工姓名的长度</td> 
  </tr> 
  <tr> 
   <td colspan="2" style="text-align:center;width:565px;">--<span style="color:#f33b45;">ltrim()：去左边空格，rtrim()：去右边空格，trim()：去两边空格</span></td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select trim('&nbsp; 张无忌&nbsp; ') from dual;</td> 
   <td style="width:361px;">--输出去掉收尾空格后的 "张无忌".&nbsp; trim() 函数用于去掉字符串首尾的空格</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select length(trim('&nbsp; 张无忌&nbsp; ')) from dual;</td> 
   <td style="width:361px;">--输出 3</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select <span style="color:#f33b45;">replace</span>('赵敏','敏','敏敏特穆尔') from dual; -- 字符串替换函数</td> 
   <td style="width:361px;">--输出 "赵敏敏特穆尔"&nbsp;&nbsp;&nbsp;&nbsp; replase函数用于替换字符串中的指定字符</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select replace(ename,'A','a') from emp;</td> 
   <td style="width:361px;">--替换员工姓名中的大写字母"A" 为 'a'</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select ascii<span style="color:#f33b45;">('</span>a') as a ,ascii('A') as A,t.dname, ascii(t.dname) as dname from DEPT t;</td> 
   <td style="width:361px;">ascii：返回与指定的字符对应的十进制数;</td> 
  </tr> 
  <tr> 
   <td style="width:565px;"> <p>select <span style="color:#f33b45;">chr</span>(65) as a ,chr(97) as A,t.deptno, chr(t.deptno) as deptno from DEPT t;</p> </td> 
   <td style="width:361px;">chr：给出整数,返回对应的字符;</td> 
  </tr> 
  <tr> 
   <td style="width:565px;">select <span style="color:#f33b45;">initcap</span>('china') as a ,t.dname, initcap(t.dname) as dname from DEPT t;</td> 
   <td style="width:361px;">initcap：返回字符串并将字符串的第一个字母变为大写，其余位置字母为小写;</td> 
  </tr> 
 </tbody> 
</table>

# 字符串查找函数 Instr() #

1、Oracle 字符串查找函数 Instr() 语法：instr(string, substring \[, start\_position \[, count \]\])

<table> 
 <tbody> 
  <tr> 
   <td>1、string ：要搜索的目标字符串，字符串可以是 char，varchar2，nchar，nvarchar2，clob 或 nclob 等类型。<br> 2、substring : 要在目标字符串(string)中搜索的子字符串，子字符串可以是 char，varchar2，nchar，nvarchar2，clob 或 nclob 等类型。<br> 3、start_position : 可选。开始检索的位置，目标字符串中第一个位置为 1，不写时默认为 1；如果 start_position 为负数，则表示从后往前检索，如 -1 表示倒数第 1 个字符。<br> 4、count : 可选。substring 子串第 count 次出现的位置，省略时，默认为 1<br> 5、返回值：返回子字符串在目标字符串中出现的位置，如果没有找到，则返回 0.</td> 
  </tr> 
 </tbody> 
</table>

![20191012095105418.gif][]

select empno,ename,
    case --职位(job) 以 'A' 开头的设置 html 红色，以 'B'开头的设置 html 蓝色
    when instr(job,'A') = 1  then '<span style="color:red">'|| job || '</span>' 
    when instr(job,'C') = 1  then '<span style="color:blue">'|| job || '</span>' 
    else job end as job,
    mgr,hiredate,sal,comm,deptno
    from emp;

# 日期函数 #

<table> 
 <tbody> 
  <tr> 
   <td>select <span style="color:#f33b45;">sysdate </span>from dual;</td> 
   <td>--输出 Oracle 服务器系统的当前时间</td> 
  </tr> 
  <tr> 
   <td>select sysdate + 7 from dual;</td> 
   <td>--查询7天后的当前时间</td> 
  </tr> 
  <tr> 
   <td> <p>select <span style="color:#f33b45;">add_months</span>(sysdate,3) from dual;</p> <p>select <span style="color:#f33b45;">add_months</span>(sysdate,-1) from dual;</p> <p>--负数表示减去，即上个月</p> </td> 
   <td> <p>--查询3个月后的当前时间</p> <p>ADD_MONTHSI(d,n),在某一个日期d上,加上指定的月数n,返回计算后的新日期。</p> <p>d表示日期,n表示要加的月数。</p> </td> 
  </tr> 
  <tr> 
   <td>select sysdate - hiredate from emp;</td> 
   <td>--查询员工入职的天数。</td> 
  </tr> 
  <tr> 
   <td>select round(sysdate - hiredate) from emp;</td> 
   <td>--查询员工入职的天数。对结果进行四舍五入。</td> 
  </tr> 
  <tr> 
   <td>select ceil((sysdate - hiredate)/7) from emp;</td> 
   <td>--查询员工入职的周数。结果向上取整</td> 
  </tr> 
  <tr> 
   <td>select <span style="color:#f33b45;">months_between</span>(sysdate , hiredate) from emp;</td> 
   <td>--查询员工入职的月数</td> 
  </tr> 
  <tr> 
   <td>select months_between(sysdate , hiredate)/12 from emp;</td> 
   <td>--查询员工入职的年数</td> 
  </tr> 
  <tr> 
   <td> <p>--LAST_DAY(d)</p> <p>select sysdate,<span style="color:#f33b45;">last_day</span>(sysdate) from dual;</p> </td> 
   <td>返回指定日期当月的最后一天。</td> 
  </tr> 
  <tr> 
   <td>select <span style="color:#f33b45;">next_day</span>(sysdate,'星期二') from dual;</td> 
   <td>--查询紧邻指定日期最近的下一个星期几</td> 
  </tr> 
  <tr> 
   <td>insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)<br> &nbsp;values (7854, '张三', 'salesman', 9598, sysdate, 1300.00, 320.00, 30);</td> 
   <td>--插入数据，日期使用 sysdate 设置</td> 
  </tr> 
  <tr> 
   <td colspan="2">select sysdate,(sysdate) + 30 / (24 * 60 * 60) from dual t;--当前时间加 30秒：2021/9/5 17:13:02&nbsp;&nbsp; 2021/9/5 17:13:32<br> select sysdate,(sysdate) + 30 / (24 * 60) from dual t;--当前时间加 30分钟：2021/9/5 17:13:38&nbsp; 2021/9/5 17:43:38<br> select sysdate,(sysdate) + 3 / 24 from dual t;--当前时间加 3小时：2021/9/5 17:14:29&nbsp;&nbsp; 2021/9/5 20:14:29</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

![20190528114835453.gif][]

# 转换函数 #

1、转换函数将值从一种数据类型转换为另外一种数据类型，通用的类型转换可以使用 [cast 函数数据类型转换][cast]

<table> 
 <tbody> 
  <tr> 
   <td style="width:488px;">select 100 + <span style="color:#f33b45;">to_number</span>('50') from dual; --字符串转数值</td> 
   <td style="width:413px;">--输出 150。to_number(str)：字符串转数值</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select 100 + '50' from dual;</td> 
   <td style="width:413px;">--输出150。默认也会自动转换的。</td> 
  </tr> 
  <tr> 
   <td colspan="2" style="text-align:center;">--<span style="color:#f33b45;">to_char(date,'pattern')：日期转字符串</span></td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select <span style="color:#f33b45;">to_char</span>(sysdate,'yyyy-mm-d hh:mi:ss') from dual; --日期转字符串</td> 
   <td style="width:413px;">--格式化日期为指定格式的字符串。默认为 12小时制</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;</td> 
   <td style="width:413px;">--格式化日期为指定格式的字符串。指定为 24小时制</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select to_char(sysdate,'yyyy') from dual;</td> 
   <td style="width:413px;">--输出年份，如 2019</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select to_char(sysdate,'mm') from dual;</td> 
   <td style="width:413px;">--输出月份，如 05</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select to_char(sysdate,'dd') from dual;</td> 
   <td style="width:413px;">--输出日期，如 28</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select to_char(sysdate,'hh24') from dual;</td> 
   <td style="width:413px;">--输出小时(24小时制)，如 15</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select to_char(sysdate,'mi') from dual;</td> 
   <td style="width:413px;">--输出分钟，如 04</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select to_char(sysdate,'ss') from dual;</td> 
   <td style="width:413px;">--输出秒钟，如 40</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select to_char(sysdate,'dd') from dual;</td> 
   <td style="width:413px;">--表示一个月中的第几天</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select to_char(sysdate,'ddd') from dual;</td> 
   <td style="width:413px;">--表示一年中的第几天</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select empno,ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;</td> 
   <td style="width:413px;">--格式化日期输出</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select to_char(sysdate,'d') from dual;</td> 
   <td style="width:413px;">--表示一个星期中的第几天，1表示星期天,2表示星期1,以此类推</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select to_char(sysdate,'day') from dual;</td> 
   <td style="width:413px;">--输出当前是星期几，比如：星期五</td> 
  </tr> 
  <tr> 
   <td colspan="2" style="text-align:center;">--<span style="color:#f33b45;">to_date('str','pattern')：字符串转日期</span></td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select to_date('2019-08-25 09:25:33','yyyy-mm-dd hh:mi:ss') from dual;</td> 
   <td style="width:413px;">--输出 2019/8/25 9:25:33。&nbsp;&nbsp; 12小时制时，小时数不能超过12，否则报错。</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select to_date('2019-08-25 19:25:33','yyyy-mm-dd hh24:mi:ss') from dual;</td> 
   <td style="width:413px;">--输出 2019/8/25 19:25:33。&nbsp; 24小时制</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select to_date('2019-08-25 09:25','yyyy-mm-dd hh:mi') from dual;</td> 
   <td style="width:413px;">--输出 2019/8/25 9:25:00</td> 
  </tr> 
  <tr> 
   <td style="width:488px;">select * from emp where hiredate between to_date('1981-06','yyyy-mm') and to_date('1982-07','yyyy-mm');</td> 
   <td style="width:413px;">--查询 1981年6月 到 1982年7月入职的员工</td> 
  </tr> 
 </tbody> 
</table>

![20190528151329773.gif][]

# 通用函数 #

1、nvl(param1,param2)：如果 param1 等于 null，则返回 param2，否则返回 param1 自身。

2、nvl2(param1,param2,param3)：如果 param1 等于 null，则返回 param3，否则返回 param2。

3、nullif(param1,param2)：如果 param1 等于 param2 ，则返回 null，否则返回 param1.

4、decode(表达式,值1,结果1,\[值2,结果2...\]\[,否则\]：select 中实现 if else 功能。

5、coalesce(expression1, expression2, ... `:`返回列表中第一个非null表达式的值，如果所有表达式求值为null，则返回 null，比如 expression1 结果不为 null 时，则返回它，否则判断expression2结果是否为null，不为 null 时，则返回expression2，依此类推。

千万注意：Oracle 中值为 null 的字段是不能做算术运算的，比如加减乘除、它的结果会恒为 null，此时可以借助 nvl 函数。

update temp set f8 = f1 + f2 + f3 where id = 2;--只要 f1,f2,f3 中有一个值为 null，则 f8 会恒为 null

<table> 
 <tbody> 
  <tr> 
   <td>select nvl(null,'is null') from dual;</td> 
   <td>--输出 is null</td> 
  </tr> 
  <tr> 
   <td>select nvl('Hi','is null') from dual;</td> 
   <td>--输出 Hi</td> 
  </tr> 
  <tr> 
   <td>select nvl2(null,100,200) from dual;</td> 
   <td>--输出 200</td> 
  </tr> 
  <tr> 
   <td>select nvl2('Hi',100,200) from dual;</td> 
   <td>--输出 100</td> 
  </tr> 
  <tr> 
   <td>select ename,job,nvl(comm,50) from emp;</td> 
   <td>--没有奖金的员工统一加上 50</td> 
  </tr> 
  <tr> 
   <td>select ename,job,nvl2(comm,comm+50,150) from emp;</td> 
   <td>--没有奖金的员工统一加上 150，有奖金的再加上 50</td> 
  </tr> 
  <tr> 
   <td>select nullif(100,100) from dual;</td> 
   <td>--输出 null</td> 
  </tr> 
  <tr> 
   <td>select nullif(100,120) from dual;</td> 
   <td>--输出 100</td> 
  </tr> 
  <tr> 
   <td colspan="2">select t.*,<span style="color:#f33b45;">decode</span>(t.job,'MANAGER','经理','CLERK','办事员','SALESMAN','推销员','普通员工') as personRank from emp t;</td> 
  </tr> 
  <tr> 
   <td colspan="2">select EMPNO,ENAME,JOB,MGR,HIREDATE,sal,<span style="color:#f33b45;">coalesce(COMM,0)</span> as COMM,DEPTNO from emp; -- 如果奖金为 null，则输出0</td> 
  </tr> 
  <tr> 
   <td colspan="2"></td> 
  </tr> 
 </tbody> 
</table>

# 忽略大小写查询字符串 #

1、当想要忽略大小写查询字符串时，可以通过如下方式进行查询

-- 方式1：参数与数据库值统一转为大写进行查询
    SELECT T.* FROM emp t where upper(t.ename) = upper('Scott');
    -- 方式2：参数与数据库值统一转为小写进行查询,
    SELECT T.* FROM emp t where lower(t.ename) = lower('Scott');
    -- 方式2 initcap 函数，将字符串的第一个字母变为大写，其余位置字母为小写
    SELECT T.* FROM emp t where initcap(t.ename) = initcap('Scott');

# trunc 截断数字和日期 #

1、TRUNC 函数用于对值进行截断，通常用于截断数字和日期

2、格式：TRUNC(suorce, length)，source 表示被截断的数字，length 表示保留小数位数，或者是日期的格式项 yyyy,mm,dd hh,hh24,mi,ss 。

3、trunc 截断小数时不会四舍五入。

4、length 为空时，默认截断所有小数位，为负数时表示截取整数，截断的整数用0替代。

5、length 为空时，默认截断日期的年月日，舍弃时分秒。

截断数字示例：

select trunc(3.14159) from dual;--3
    select trunc(3.14159,0) from dual;--3
    select trunc(3.14159,1) from dual;--3.1
    select trunc(3.14159,3) from dual;--3.141
    select trunc(314159.67767,-3) from dual;--截取整数 314000
    select ename,job,trunc(sal,0) from emp;--对薪资进行取整.截断是没有四舍五入的

截断日期示例：

select sysdate,trunc(sysdate,'yyyy') from dual;-- 截取到年：2021/9/5 16:48:05  2021/1/1
    select sysdate,trunc(sysdate,'mm') from dual;-- 截取到月：2021/9/5 16:48:05  2021/9/1
    select sysdate,trunc(sysdate,'dd') from dual;-- 截取到天：2021/9/5 16:48:05   2021/9/5
    select sysdate,trunc(sysdate,'hh24') from dual;-- 截取到时：2021/9/5 16:48:05   2021/9/5 16:00:00
    select sysdate,trunc(sysdate,'mi') from dual;-- 截取到分：2021/9/5 16:48:05   2021/9/5 16:48:00
    select sysdate,trunc(add_months(sysdate,-1),'mm') from dual;-- 获取上月第一天 2021/9/5 16:48:05   2021/8/1
    select sysdate,trunc(sysdate) from dual;-- 获取今天的日期：2021/9/5 16:48:05   2021/9/5
    select sysdate,trunc(sysdate,'d') from dual;-- 获取当前星期的第一天(星期天) 2021/9/5 16:48:05  2021/9/5

[Link 1]: https://wangmaoxiong.blog.csdn.net/article/details/90581383#%E5%87%86%E5%A4%87%E5%91%98%E5%B7%A5%E8%A1%A8%E4%B8%8E%E9%83%A8%E9%97%A8%E8%A1%A8%E6%B5%8B%E8%AF%95%E6%95%B0%E6%8D%AE
[Oracle SQL]: https://wangmaoxiong.blog.csdn.net/article/details/90581383
[20190528102258500.gif]: /images/20220126/864f3d0157ac46ee80c1fe8bd74ad9f7.png
[20191012095105418.gif]: /images/20220126/4ca29aadc66743a5914cf2ed2da0d0ac.png
[20190528114835453.gif]: /images/20220126/1c2057a987184ad1a6d2f40b67cfffe7.png
[cast]: https://wangmaoxiong.blog.csdn.net/article/details/108242444
[20190528151329773.gif]: /images/20220126/f01fcd0afaf44ea9a4e4b7ebd8ee9a18.png