Oracle 基本函数-数值：字符、 Instr()、日期、转换、trunc 截断、MD5、SQL 操作/运算符，注释

蚩尤后裔-汪茂雄

已于 2024-08-23 16:13:40 修改

阅读量3.9k

点赞数 3

分类专栏： Oracle 文章标签： oracle sql 数据库

于 2019-05-29 13:48:16 首次发布

本文链接：https://blog.csdn.net/wangmx1993328/article/details/90633831

版权

Oracle 专栏收录该内容

22 篇文章 3 订阅

订阅专栏

Oracle SQL 注释

单行注释：-- 注释文字

多行注释：/* 注释文字 */

SQL 操作/运算符与单双引号

1、SQL 操作符的优先级从高到低顺序：算术操作符->连接操作符->比较操作符->NOT->AND->OR。可以使用括号改变优先级。

算术操作符：加(+)、减(-)、乘(*)、除(/)，其中除号(/)的结果是浮点数。求余运算只能借助函数：MOD(x,y)：返回x除以y的余数。乘除的优先级高于加减。优先级相同时，按照从左至右运算。可以使用括号改变优先级。	＋、－、×、/
比较操作符注意等于是 = 不是 ==。不等于可以是 != 或者 <>。	=、!=、<、>、<=、>=、BETWEEN…AND、IN、LIKE 和 IS NULL等
区间查询	between …… and ……
关键字集合查询	in
模糊查询	like
判断是否为空	is null 或 is not null
逻辑操作符 select * from emp t where t.sal is not null and t.comm is not null;	与(AND)、或(OR)、非(NOT)
连接操作符号。用于将两个或多个字符串合并成一个字符串，或将一个字符串与一个数值合并在一起 select t.empno,t.ename \|\| '_' \|\| t.sal from emp t ;	两根竖杠 "\|\|"，用于将多个字符串或数据值合并成一个字符串

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 操作。

空值、设置默认值、特殊字符下划线_

1、空值是无效的，未指定的，未知的或不可预知的值，不是空格或者0。

2、包含空值的数学表达式的值都为空值。

--sal 或者 comm 存在空值时，则年薪1结果恒为空，年薪2则如果为空值，则默认为0，结果是正常的
SELECT T.*, 12 * sal + comm 年薪1, 12 * nvl(sal, 0) + nvl(comm, 0) 年薪2 FROM emp T;

3、当没有查询的数据行时设置默认值(生产数据迁移示范)。

--  错误示范 - 没有查询到数据行时设置默认值
SELECT nvl(t.dname, '-') as dname FROM dept t where t.deptno='0';
-- 错误示范 - 没有查询到数据行时设置默认值
SELECT (case when t.dname is null then '-' else t.dname end) as dname FROM dept t where t.deptno='0';

-- 正确示范 - 没有查询到数据行时设置默认值 - 使用子查询
SELECT nvl((SELECT t.dname FROM dept t where t.deptno = '0'), '-') dname FROM dual d;
-- 正确示范 - 没有查询到数据行时设置默认值 - 使用子查询
SELECT (case
         when (SELECT t.dname FROM dept t where t.deptno = '0') is null then  '-'
         else (SELECT t.dname FROM dept t where t.deptno = '0')
       end) as dname
  FROM dual d;

4、下划线'_'属于特殊字符，比如模糊查询姓名中含有下划线的员工。

SELECT T.*, ROWID FROM emp t where instr(t.ename, '_') > 0;
SELECT T.*, ROWID FROM emp t where t.ename like '%\_%' escape '\';

特殊字符'&'—自定义变量

1、SQL语句中如果某个内容中携带了 & 字符，且它后面还有内容，那么它会被当做变量处理，后面紧挨的内容会作为变量的名字。&后面的变量名字在遇到空格，英文标点符号等时会自动放弃。

--变量交互演示：拿到用户数输入的变量值
select decode('&退出请按0，继续等待请按1', '0', '退出', '1', '等待', '输入错误') as status from dual;

--变量交互演示：新增、修改等操作时，如果内容中存在字符串'&'，则同样会被当成变量交互
update emp t set t.ename = '&新姓名' where t.ename='&旧姓名';

--变量交互演示：根据用户输入的表名查询它的列信息
select t.TABLE_NAME, t.COLUMN_NAME,  t.DATA_TYPE,  t.DATA_LENGTH, t.DATA_PRECISION, t.DATA_SCALE, t.NULLABLE,  t2.COMMENTS
  from user_tab_columns t, user_col_comments t2
 where t.TABLE_NAME = upper('&输入表名')
   and t.TABLE_NAME = t2.TABLE_NAME
   and t.COLUMN_NAME = t2.COLUMN_NAME
 order by COLUMN_NAME;

--变量交互演示：PLSQL中不再写死变量值，而是通过用户输入
declare
  v_type  varchar2(16) := '&生成的类型：1一数字，2一UUID';
  v_count number default '&生成的个数';
begin
  if v_count > 0 and trim(v_type) in ('1', '2') then
    for i in 1 .. v_count loop
      if trim(v_type) = '1' then
        dbms_output.put_line(i);
      elsif trim(v_type) = '2' then
        dbms_output.put_line(lower(sys_guid()));
      end if;
    end loop;
  else
    dbms_output.put_line('输入的参数值错误.');
  end if;
end;

2、实际应用中经常需要插入原始的 & 字符内容，而不希望是变量交互，此时可以使用下面的方法。

--方法1：单独拆分&
select t.*,  t.ename ||  '&'  ||  'nbsp;' html from emp t;
--方式2：使用 ‘&’的ASCII码 chr(38) 进行替换
select t.*,  t.ename ||   chr(38) ||  'nbsp;' html from emp t;

Oracle 函数概述

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

单行函数

对每一个函数应用在表的记录中时,只能输入一行结果,返回一个结果,
比如:MOD(x,y）返回 x 除以 y 的余数 (x和y)可以是两个整数,也可以是表中的整数列)

常用的单行函数有:
字符函数:对字符串操作。
数字函数:对数字进行计算，返回一个数字。
转换函数:将一种数据类型转换为另外一种数据类型。
日期函数:对日期和时间进行处理。

聚合函数

聚合函数同时可以对多行数据进行操作,并返回一个结果。比如SUM(x) 返回结果集中ⅹ列的总合。

多行函数

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

2、多行函数会自动忽略空值，比如 avg(comm)，一共三个人，但是其中一个人奖金为空，此时求的平均值是两个人的平均值，而不是三个人。主要看具体需求，有的时候需要忽略null，有的时候则不需要忽略。

--统计每个部门的人数，没有员工的部门，统计个数展示为0。count(empno) 忽略统计为 null 的行.
select t.deptno, t.dname, count(empno)
  from (select d.deptno, d.dname, e.empno, e.ename
          from dept d
          left join emp e
            on d.deptno = e.deptno) t
 group by t.deptno, t.dname;

select max(sal) from emp; --取最大值

select max(sal) as max_sal from scott.emp t where t.deptno = 100;

--查询最高薪资。sal 为 null 的值会自动忽略。

--当 select 没有查询到数据时，max_sal会等于null，而不是无记录返回。

select max(to_number(code)) as code from gbm_bs_person_type t

-- 如果字段是字符串类型的数字，则必须先转为数字，否则会出现 '9' 比 '888' 大.

--max、min 函数不只是用于数字类型，字符串、日期、rowid 等等都可以。

select min(sal) from emp; -取最小值

select min(sal) as min_sal from scott.emp t where t.deptno = 100;

--查询最低薪资。sal 为 null 的值会自动忽略

--当 select 没有查询到数据时，min_sal 会等于null，而不是无记录返回。

select sum(sal) from emp; --求和

select sum(sal) as sum_sal from scott.emp t where t.deptno = 100;

--统计员工薪资总和

--当 select 没有查询到数据时，sum_sal 会等于null，而不是无记录返回。

select sum(comm) from emp;

--统计员工奖金总和。为 null 的值会自动忽略

--count 统计个数。当没有数据时，返回 0。

--*：表示一行中只要有一列的值不为空，就会统计。
--常数：每一行都统计。建议使用常数代替 "*"。

--count(empno)：主键列不会为空，且有索引，相当于每一行都统计。

--count(nvl(comm, 0))：comm 奖金列值可能为空，为空的数据默认不会被统计，所以必须借助 nvl 函数。

--count(distinct t.deptno) ：返回deptno非空且不重复的记录总数
select count(*) t1, count(1) t2, count(empno) t3, count(nvl(comm, 0)) t4, count(distinct t.deptno) t6 from emp t;

--统计平均奖金。多行函数会自动忽略空值，此时可以借助 nvl 函数。

--当 select 没有查询到数据时，avg_sal 会等于null，而不是无记录返回。

select avg(nvl(comm, 0)) from emp; --求平均值

select sum(comm)/count(1) from emp;

--统计平均奖金。当 comm 有 null 值时，可以使用此种方式

数值函数

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

select abs(-100) as val from dual; --求绝对值	--输出 100
select ceil(3.14159) from dual;	--向上取整
select ename,job,ceil(sal) from emp;	--对所有用户的工资向上取整
select floor(3.14159) from dual;	--向下取整
select ename,job,floor(sal) from emp;	--对所有用户的工资向下取整
select round(2.14159,3) from dual;	--四舍五入。round(x,[y])，y缺省时默认为0
select ename,job,round(sal) from emp;	--对所有用户的工资四舍五入

trunc(number,number ) 用于截断浮点型数据,. 第二个参数表示截断小数点后面的第几位，不会进行四舍五入，number 默认为0。如果第一个参数是字符串，则报错。
select trunc(3.14159,3) from dual;	--截断小数点后面第3位，输出 3.141
select trunc(3.14159,1) from dual;	--截断小数点后面第1位，输出 3.1
select trunc(3.14159,0) from dual;	--截断小数点后面第0位，输出 3
select ename,job,trunc(sal,0) from emp;	--对薪资进行取整.截断是没有四舍五入的
select mod(10,3) from dual; --mod 求余函数	--输出 1.
select mod(10,4) from dual;	--输出 2.
select mod(10,5) from dual;	--输出 0.
select ename ,job, mod(sal,30) from emp;	--薪资除以30后的余数
Select power(2,10) 二的十次方 from dual; --求次方	-- power(a,b) ：获取的 a 的 b 次方
select Sqrt(16) 十六的开方 from dual; --求平方	-- sqrt(x) ：获取 x 的二分之一次方
--给出反余弦的值 select acos(-1) from dual;	--给出反正弦的值 select asin(0.5) from dual;
--返回一个数字的正弦值 select sin(1.57079) from dual;	--返回双曲正弦的值 select sin(20),sinh(20) from dual;
--返回一个数字的反正切值 select atan(1) from dual;	--返回数字的正切值 select tan(20),tan(10) from dual;
--返回数字 n 的双曲正切值 select tanh(20),tan(20) from dual;	--返回一个给定数字的余弦 select cos(-3.1415927) from dual;
--返回一个数字反余弦值 select cosh(20) from dual;	--返回一个数字 e 的 n 次方根 select exp(2), exp(1) from dual;
--返回一个数字的对数值 select ln(1),ln(2),ln(2.7182818) from dual;	--返回一个以 n1 为底 n2 的对数 select log(2,1),log(2,4) from dual;
--取数字 n 的符号,大于 0 返回 1,小于 0 返回-1,等于 0 返回 0 select sign(123),sign(-100),sign(0) from dual;
--返回一组表达式中的最大值 select greatest(10,20,55) from dual; SELECT t.sal,t.comm, greatest(t.sal,t.comm) FROM emp T;
--返回一组表达式中的最小值 select LEAST(10,20,55) from dual; SELECT t.sal,t.comm, LEAST(t.sal,t.comm) FROM emp T;

字符函数

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

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

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

select concat('长沙','星城') as citi from dual;	--输出长沙星城
select t.stuid,concat(t.stuname,'·'\|\|t.gender),t.age,t.classno from student t;	--拼接用户的姓名与性别，同时中间使用 '·' 符号隔开
select lower('AbcD') from dual;	--输出 abcd
select upper('AbcD') from dual;	--输出 ABCD
select t.stuid,lower(t.stuname) from student t;	--查询学生学号与姓名，姓名中的英文字母转小写输出。
substr(str,startIndex[,length])：str 被截取的字符串；startIndex 表示开始截取的索引位置，从1开始，0也是表示1；length 表示截取的长度。没有截取到时，返回 null。 pl/sql示例1。 plsql示例2：嵌套表集合自定义 split 函数。
select substr('ZhangSan',0,3) from dual;	--输出 Zha
select substr('ZhangSan',1,3) from dual;	--输出 Zha
select substr('ZhangSan',2,3) from dual;	--输出 han
select substr('ZhangSan',3,3) from dual;	--输出 ang
select substr(ename,1,3) ,job from emp;	--截取员工姓名的前3个字符
select substr('001,002,003', 1, 4 - 1) from dual;--001 select substr('001,002,003', 4 + 1) from dual;--002,003
-- length 获取内容的字符数，length 获取内容的字节数 --字符字节 -- 5 8 select length('Hi，你好') 字符, lengthb('Hi，你好') 字节 from dual;
select length('张无忌') from dual;	--输出3 length 函数用于求字符串长度
select length(' 张无忌 ') from dual;	--输出 7
select length(ename) from emp;	--输出所有员工姓名的长度
select length(trim(' 张无忌 ')) from dual;	--输出 3
--ltrim()：去左边空格，rtrim()：去右边空格，trim()：去两边空格 -- ltrim、rtrim 不仅可以分别用于去掉内容左右两边的空格，还可以用于去掉左右两边的指定内容
--输出去掉收尾空格后的 "张无忌". trim() 函数用于去掉字符串首尾的空格 select trim(' 张无忌 ') from dual;
select rtrim(' 中国人 ') r1, ltrim(' 中国人 ') r2, rtrim(' 中国人, ', ', ') r3, ltrim(' 。中国人 ', ' 。') r4 from dual;
-- 注意：它们都只能去掉空格，无法去掉 Tab 键产生的制表符;也无法去掉换行符、回车符等空白内容; -- 需要强行替换：制表符 chr(9)、换行符 chr(10)、回车符 chr(13) SELECT T.loc, REPLACE(REPLACE(REPLACE(TRIM(loc), chr( 9 ), '' ), chr( 10 ), '' ),chr( 13 ), '' ) "去除前后空白字符", REPLACE(REPLACE(REPLACE(REPLACE(loc,' ',''), chr( 9 ), '' ), chr( 10 ), '' ),chr( 13 ), '' ) "去除全部空白字符方式1", REGEXP_REPLACE(loc, '[[:space:]]', '') "去除全部空白字符方式2" FROM dept t;

replase(src, 'xxx', 'yyy' )：用于替换src中的内容xxx为yyy，并返回替换后的值。 src 为 null 或者没有 xxx 时而导致没有替换成功时，原样返回 src。
select replace('赵敏','敏','敏敏特穆尔') from dual; -- 字符串替换函数	--输出 "赵敏敏特穆尔"
select replace(ename,'A','a') from emp;	--替换员工姓名中的大写字母"A" 为 'a'
--金额也可以替换，comm 为 1000.00 时，同样会被替换 SELECT t.ename, replace(t.ename, 'KING', 'xxxx'),t.comm, replace(t.comm, 1000, '***') FROM emp T;
select ascii('a') as a ,ascii('A') as A,t.dname, ascii(t.dname) as dname from DEPT t;	ascii：返回与指定的字符对应的十进制数;
select chr(65) as a ,chr(97) as A,t.deptno, chr(t.deptno) as deptno from DEPT t;	chr：给出整数,返回对应的字符;
select initcap('china') as a ,t.dname, initcap(t.dname) as dname from DEPT t;	initcap：返回字符串并将字符串的第一个字母变为大写，其余位置字母为小写;
lpad(目标内容, 目标长度，往左侧填充的内容)：在目标内容的左侧填充内容，直到达目标长度，未指定填充内容时，默认为空格。 rpad(目标内容, 目标长度，往右侧填充的内容)：在目标内容的右侧填充内容，直到达目标长度，未指定填充内容时，默认为空格。特别注意，如果目标内容的长度小于目标长度，则会对目标内容进行截取，后面多余会直接舍弃。 -- 09 09 0 select lpad('9',2,'0'), lpad('09',2,'x'), lpad('09',1,'x') from dual; -- Test select lpad('Test', 10) from dual; --生成2013年1到12月，如 201302、201310 select SUBSTR('2013xx', 1, 4) \|\| lpad(level, 2, 0) ymd from dual connect by level<=12; --查询编号为7839的员工，及其下属的员工，并标记级次(从1开始)，并在名字前标记#号，是几级就加几个前缀# --如 #KING、###SCOTT select level, empno, rpad('#', level, '#') \|\| ename ename from emp start with empno = 7839 connect by prior empno = mgr;
--目标长度是指的字节长度。目标内容以及填充内容，其中的汉字会使用字节长度进行算 --汉字的字节长度的计算与oracle数据库字符集有关，GBK和USASCII字符集下每个汉字字节长度为2，UTF8下每个汉字字节长度为3 SELECT '中国ABC123' "原始内容", rpad('中国ABC123', 20, '#十#') "右侧补充使总字节长度达到20位", length(rpad('中国ABC123', 20, '#十#')) "最终的字符长度16", lengthb(rpad('中国ABC123', 20, '#十#')) "最终的字节长度20" FROM dual;

字符串查找函数 Instr()

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

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

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;

--数字同样可以查找
select instr(1, 1),instr(1, 2) from dual;-- 输出 1，0
select instr(1, '1'),instr(1, '2') from dual;-- 输出 1，0

plsql示例2：嵌套表集合自定义 split 函数。

日期函数

1、在日期上加上或减去一个数字(天)结果仍为日期。

2、两个日期相减返回日期之间相差的天数。

3、可以用数字除24来向日期中加上或减去小时。

4、日期序列需要借助：level 级次（伪列）。

select sysdate from dual;	--输出 Oracle 服务器系统的当前时间
select sysdate + 7 from dual;	--查询7天后的当前时间
select add_months(sysdate,3) from dual; select add_months(sysdate,-1) from dual; --负数表示减去，即上个月	--查询3个月后的当前时间 ADD_MONTHSI(d,n),在某一个日期d上,加上指定的月数n,返回计算后的新日期。 d表示日期,n表示要加的月数。
-- 2023/11/1 2023/12/1 2024/1/1 SELECT ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1) "上个月第一天", TRUNC(SYSDATE, 'MM') "本月第一天", ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1) "下个月第一天" FROM DUAL; -- 2023/7/1 2023/10/1 2024/1/1 SELECT TRUNC(ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -3), 'Q') AS "上季度的第一天", TRUNC(SYSDATE, 'Q') AS "本季度的第一天", TRUNC(ADD_MONTHS(TRUNC(SYSDATE, 'Q'), 3), 'Q') AS "下季度的第一天" FROM DUAL;
select sysdate - hiredate from emp;	--查询员工入职的天数。
select round(sysdate - hiredate) from emp;	--查询员工入职的天数。对结果进行四舍五入。
select ceil((sysdate - hiredate)/7) from emp;	--查询员工入职的周数。结果向上取整
select months_between(sysdate , hiredate) from emp;	--查询员工入职的月数
--1.96774193548387 SELECT months_between(to_date('2023-11-24','yyyy-mm-dd'), to_date('2023-09-25','yyyy-mm-dd')) m1 FROM dual T; --11.9677419354839 SELECT months_between(to_date('2024-09-24','yyyy-mm-dd'), to_date('2023-09-25','yyyy-mm-dd')) m1 FROM dual T; --12 SELECT months_between(to_date('2024-09-25','yyyy-mm-dd'), to_date('2023-09-25','yyyy-mm-dd')) m1 FROM dual T; --12.0322580645161 SELECT months_between(to_date('2024-09-26','yyyy-mm-dd'), to_date('2023-09-25','yyyy-mm-dd')) m1 FROM dual T; --12(忽略时分秒进行比较) SELECT months_between(to_date('2024-09-25 15:12:13', 'yyyy-mm-dd hh24:mi:ss'), to_date('2023-09-25', 'yyyy-mm-dd')) m1 FROM dual T;
--查询员工入职的年数/工龄 select sysdate, hiredate, TRUNC(months_between(sysdate, hiredate) / 12) 工龄向下取整计算, ceil(months_between(sysdate, hiredate) / 12) 工龄向上取整计算 from emp;
--LAST_DAY(d) select sysdate,last_day(sysdate) from dual;	返回指定日期当月的最后一天。
select next_day(sysdate,'星期二') from dual;	--查询紧邻指定日期最近的下一个星期几
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7854, '张三', 'salesman', 9598, sysdate, 1300.00, 320.00, 30);	--插入数据，日期使用 sysdate 设置
select sysdate,(sysdate) + 30 / (24 * 60 * 60) from dual t;--当前时间加 30秒：2021/9/5 17:13:02 2021/9/5 17:13:32 select sysdate,(sysdate) + 30 / (24 * 60) from dual t;--当前时间加 30分钟：2021/9/5 17:13:38 2021/9/5 17:43:38 select sysdate,(sysdate) + 3 / 24 from dual t;--当前时间加 3小时：2021/9/5 17:14:29 2021/9/5 20:14:29

转换函数

1、转换函数将值从一种数据类型转换为另外一种数据类型，通用的类型转换推荐使用 cast 函数数据类型转换。

select 100 + to_number('50') from dual; --字符串转数值	--输出 150。to_number(str)：字符串转数值
select 100 + '50' from dual;	--输出150。默认也会自动转换的。
数字转换字符串，格式: to_char(number [,'格式']);
-- 注意事项1：'9' 表示如果存在数字则显示数字，不存在则显示空格。 -- 注意事项2：'0' 表示如果存在数字则显示数字，不存在则显示0，即占位符。 -- 注意事项3：'$' 表示金额符号 -- 注意事项4：格式中的位数不能小于 number 的位数，否则结果就是 ################ -- 注意事项5：'x' 表示十六进制. -- 注意事项6：'FM' 表示删除因为'9'带来的空格。
-- 纯数字转字符串：88877.2561 select to_char(88877.2561) from dual; -- 纯数字转字符串：.2561 select to_char(0.2561) from dual;
-- 如果小于1，必须加0：-0.860 select trim(to_char(-0.8596,'0.999')) from dual; -- 如果小于1，必须加0： 0.187 select trim(to_char(0.18654,'0.999')) from dual; -- -485.000 select trim(to_char(-485, '999999999.000')) from dual; -- 148.500 select trim(to_char(148.5,'999999999.000')) from dual;
-- 转为10位整数(小数部分舍弃)，不足的前面补0：0000088455 select trim(to_char(88455,'0999999999')) from dual; -- 转为10位整数,小数部分保留3位(四舍五入)，不足的前面补0：0000088455.256 select trim(to_char(88455.2556,'0999999999.000')) from dual; -- 每三位用逗号分隔(小数部分舍弃)：8,545,150,212 select trim(to_char(8545150212.2557,'999,999,999,999')) from dual; -- 每三位用逗号分隔，小数部分保留3位(四舍五入)：8,545,150,212.256 select to_char(8545150212.2557,'FM999,999,999,999.000') from dual;
-- 金额格式转换：$8,545,150,212.256 select trim(TO_CHAR(8545150212.2557,'$999,999,999,999.999')) from dual; -- 10进制转化为16进制(小数部分不会转)：b267 select trim(to_char(45671,'xxxxxxxxx')) from dual;
to_char(date,'pattern')：日期转字符串
select to_char(sysdate,'yyyy-mm-d hh:mi:ss') from dual; --日期转字符串	--格式化日期为指定格式的字符串。默认为 12小时制
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;	--格式化日期为指定格式的字符串。指定为 24小时制
select to_char(sysdate,'yyyy') from dual;	--输出年份，如 2019
select to_char(sysdate,'mm') from dual;	--输出月份，如 05
select to_char(sysdate,'dd') from dual;	--输出日期，如 28
select to_char(sysdate,'hh24') from dual;	--输出小时(24小时制)，如 15
select to_char(sysdate,'mi') from dual;	--输出分钟，如 04
select to_char(sysdate,'ss') from dual;	--输出秒钟，如 40
select to_char(sysdate,'dd') from dual;	--表示一个月中的第几天
select to_char(sysdate,'ddd') from dual;	--表示一年中的第几天
select empno,ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;	--格式化日期输出
select to_char(sysdate,'d') from dual;	--表示一个星期中的第几天，1表示星期天,2表示星期1,以此类推
select to_char(sysdate,'day') from dual;	--输出当前是星期几，比如：星期五
--使用双引号向日期中添加字符 --2023年10月05日 09:14:43 select to_char(sysdate, 'YYYY"年"MM"月"DD"日" HH:MI:SS') date_time from dual;
--当前时间 2023-09-30 16:15:08 星期六 select to_char(sysdate, '"当前时间" yyyy-mm-dd hh24:mi:ss day') from dual;
to_date('str','pattern')：字符串转日期
select to_date('2019-08-25 09:25:33','yyyy-mm-dd hh:mi:ss') from dual;	--输出 2019/8/25 9:25:33。 12小时制时，小时数不能超过12，否则报错。
select to_date('2019-08-25 19:25:33','yyyy-mm-dd hh24:mi:ss') from dual;	--输出 2019/8/25 19:25:33。 24小时制
select to_date('2019-08-25 09:25','yyyy-mm-dd hh:mi') from dual;	--输出 2019/8/25 9:25:00
select * from emp where hiredate between to_date('1981-06','yyyy-mm') and to_date('1982-07','yyyy-mm');	--查询 1981年6月到 1982年7月入职的员工
--对当前时间只取年-月-日 select to_date(to_char(sysdate,'YYYY-MM-DD'),'YYYY-MM-DD') as sydate from dual;

doc/oracle 中 to_char用法 - 普罗米修斯y - 博客园.pdf · 汪少棠/material - Gitee.com 。

通用函数

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

select nvl(null,'is null') from dual;	--输出 is null
select nvl('Hi','is null') from dual;	--输出 Hi
select nvl2(null,100,200) from dual;	--输出 200
select nvl2('Hi',100,200) from dual;	--输出 100
select ename,job,nvl(comm,50) from emp;	--没有奖金的员工统一加上 50
select ename,job,nvl2(comm,comm+50,150) from emp;	--没有奖金的员工统一加上 150，有奖金的再加上 50
select nullif(100,100) from dual;	--输出 null
select nullif(100,120) from dual;	--输出 100
select t.*,decode(t.job,'MANAGER','经理','CLERK','办事员','SALESMAN','推销员','普通员工') as personRank from emp t;
select EMPNO,ENAME,JOB,MGR,HIREDATE,sal,coalesce(COMM,0) as COMM,DEPTNO from emp; -- 如果奖金为 null，则输出0
--统计每年入职的人数 select sum(decode(to_char(t.hiredate, 'yyyy'), '1980', 1, 0)) as "1980", sum(decode(to_char(t.hiredate, 'yyyy'), '1981', 1, 0)) as "1981", sum(decode(to_char(t.hiredate, 'yyyy'), '1982', 1, 0)) as "1982", sum(decode(to_char(t.hiredate, 'yyyy'), '1983', 1, 0)) as "1983" from emp t where hiredate is not null;

忽略大小写查询字符串

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
--2023/12/14 16:26:48	   2023/10/1
select sysdate,trunc(sysdate,'Q') "本季度第一天" from dual;

Oracle MD5 函数提取摘要

1、Oracle 中也提供了函数可以直接获取数据的 MD5 摘要，无论长短，结果都是 32 位，语法如下：Utl_Raw.Cast_To_Raw(sys.dbms_obfuscation_toolkit.md5(input_string => '被提取摘要内容'))

2、特别注意'被提取摘要内容'不能为null或者为空''，否则报错:ORA-28231: 没有数据传递到 obfuscation 工具箱。

3、使用示例如下：

SQL>  select sys.dbms_obfuscation_toolkit.md5(input_string => '蚩尤后裔') as md51,
     Utl_Raw.Cast_To_Raw(sys.dbms_obfuscation_toolkit.md5(input_string => '蚩尤后裔')) as md52 from dual;

MD51                                MD52
--------------------------------- ------------------------------ 
$躞z|1?粙9??                       24F5F37AF8F17C31C211BB8B39DF11E3
SQL>
SQL> select t.*,utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => t.DNAME)) as md5 from dept t;
    DEPTNO DNAME          LOC            MD5  
---------- -------------- ------------- ------------------------
        10 ACCOUNTING     NEW YORK     0CC1ED309BE6D32F09CEBEAF1D378C62
        20 RESEARCH       DALLAS       0B667B9480CB3A0FA45691C3B7B5EE1C
        30 SALES          CHICAGO      DA7C1B2519415D312411F058C3C72E86
SQL>

A、dbms_obfuscation_toolkit.md5：创建数据的 MD5 哈希值，接收RAW类型和VARCHAR2类型。

B、utl_raw.cast_to_raw：将md5哈希值转换成RAW类型，得到由大写字母和数字组成的32位的md5值。

ORA-00932:数据类型不一致:应为CHAR,但却获得 BINARY。可以使用cast函数统一结果的数据类型。

4、如果需要频繁使用 md5 提取摘要，每次写一长串还是不太方便，而且为空时还报错，我们可以自定义函数，然后直接调用函数即可。

CREATE OR REPLACE FUNCTION MD5(source IN VARCHAR2) RETURN VARCHAR2 IS
    -- 自定义函数(MD5)，获取传入源(source)的md5摘要，返回32位的摘要信息
    v_md5 VARCHAR2(32) := '';
BEGIN
    -- 如果源内容为空或者为null，或者为空白字符串，则不处理，返回空/null.
    IF source IS NOT NULL AND TRIM(source) IS NOT NULL THEN
        v_md5 := utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => source));
    END IF;
    RETURN v_md5;
END;

-- 调用自定义函数提取摘要
-- 43DC9F79656CF8170ECD0CDF0E8D95C4
select md5(440102199010230759) from dual;
-- 24F5F37AF8F17C31C211BB8B39DF11E3
select md5('蚩尤后裔') from dual;
-- 返回 null
select MD5(null),MD5('   ') from dual;
-- 对表中的 comm 值提取摘要
select t.*,MD5(t.COMM) from emp t;