Oracle 19C入门到精通之常用系统函数

SQL语言是一种脚本语言，它提供了大量内置函数，使用这些内置函数可以大大增强SQL语言的运算和判断功能。

1. 字符类函数

1.1. ASCII函数和CHR函数——ASCII码与字符互转

ASCII(c)函数用于返回一个字符的ASCII码，其中参数c表示一个字符；

CHR(i)函数用于返回给出ASCII码值所对应的字符，i表示一个ASCII码值。

ASCII(c)函数与CHR(i)函数两者之间是互逆关系；

--查询字符“Z、H、D和空格”的ASCII值

select ascii('Z') Z,ascii('H') H,ascii('D') D ,ascii(' ') space from dual;

--查询90、72、68、32对应的字符

select  chr(90),chr(72),chr(68),chr(32) from dual;

说明： dual是Oracle系统内部提供的一个用于实现临时数据计算的虚拟表，它只有一个列dummy，类型为VARCHAR2(1)，主要用来选择系统变量或求一个表达式的值；

1.2. CONCAT函数——字符串连接

CONCAT(s1,s2)函数将字符串s2连接到字符串s1的后面；CONCAT函数的功能等同于‘||’；

--将ello 与World!两个字符串拼接起来

select concat('Hello ','World!') 信息  from dual;

select 'Hello ' || 'World!' 信息 from dual;

1.3. INITCAP函数——字符串单词首字母转换为大写

INITCAP(s)函数将字符串s的每个单词的第一个字母大写，其他字母小写。单词之间用空格、控制字符、标点符号来区分。

--将hello word！it is a beautiful day！每个单词首字母转换为大写

select initcap('hello word！it is a beautiful day！') 信息 from dual;

1.4. INSTR函数——返回子字符串在源字符串中的位置

INSTR(s1,s2,i,j)函数用于返回字符s2在字符串s1中第j次出现时的位置，搜索从字符串s1的第i个字符开始。当没有发现要查找的字符时，该函数返回值为0；如果i为负数，那么搜索将从右到左进行，但函数的返回位置还是按从左到右来计算。其中，s1和s2均为字符串；i和j均为整数，默认值为1。不管从第几个字符开始搜索，如果发现字符，都是从左边第1个字符开始计算位置。

--在Hello Word！中从其第3个字符开始，查找字母o第2次出现的位置

select instr('Hello Word！','o',3,2) o第二次出现 from dual;

1.5. LENGTH函数——返回字符串长度

LENGTH(s)函数用于返回字符串s的长度，如果s为NULL，则返回值为NULL。

--返回字符串Hello Word！的长度

select length('Hello Word！') 字符串长度 from dual;

1.6. LOWER函数和UPPER函数——字母大小写转换

LOWER(s)函数用于返回字符串s的小写形式

UPPER(s)函数用于返回字符串s的大写形式

select LOWER('Hello') 转换为小写，UPPER('Hello') 转换为大写 from dual;

1.7. LTRIM函数、RTRIM(函数和TRIM函数——删除指定字符串

LTRIM(s1,s2)函数用来删除字符串s1左边的字符串s2

RTRIM(s1,s2)函数用来删除字符串s1右边的字符串s2

TRIM(s2 FROM s1)函数用来删除字符串s1左右两端的字符串s2

如果在这3个函数中不指定字符串s2，则表示去除相应方位的空格。

select LTRIM('***hello***','*'),RTRIM('***hello***','*'),TRIM('*' FROM '***hello***') from dual;

1.8. REPLACE函数——替换指定的字符串

REPLACE(s1,s2[,s3])函数使用s3字符串替换出现在s1字符串中的所有s2字符串，并返回替换后的新字符串，其中，s3的默认值为空字符串。

--将lilei替换成hanmeimei

select replace('my name is lilei','lilei','hanmeimei') from dual;

1.9. SUBSTR函数——截取字符串

SUBSTR(s,i,[j])函数表示从字符串s的第i个位置开始截取长度为j的子字符串。如果省略参数j，则直接截取到尾部。其中，i和j为整数。

--截取a beautiful day

select substr('it is a beautiful day!',7,15) from dual;

2. 数字类函数

数字类函数主要用于执行各种数据计算，所有的数字类函数都有数字参数并返回数字值。Oracle中常用的数字类函数如下表：

上表中三角函数的操作数和返回值都是弧度，而不是角度，这一点需要注意。下面举例说明几个常用的数字类函数；

2.1. CEIL(n)函数

CEIL(n)函数用于返回大于或等于数值n的最小整数；

select ceil(8.3),ceil(8),ceil(-8.3) from dual;

2.2. POWER(n1,n2)函数

POWER(n1,n2)函数用于返回n1的n2次方。其中n1和n2都为整数。

--返回2的3次方的值

select power(2,3) from dual;

2.3. ROUND(n1,n2)函数

ROUND(n1,n2)函数用于返回舍入小数点右边n2位的n1的值，n2的默认值为0，会返回小数点最接近的整数。如果n2为负数，就舍入到小数点左边相应的位上，n2必须是整数。

--四舍五入保留小数点后3位

select round(3.1415926,3) from dual;

3. 日期和时间类函数

常用的日期和时间类函数及其说明如下表所示：

日期类型的默认格式是“DD-MON-YY”，其中DD表示两位数字的“日”，MON表示3位数字的“月份”，YY表示两位数字的“年份”，下面列举一下常用的日期和时间类函数的具体应用；

3.1. SYSDATE函数——返回系统当前日期

--返回系统当前日期时间

select sysdate as 系统日期 from dual;

3.2. ADD_MONTHS(d,i)函数

ADD_MONTHS(d,i)函数用于返回日期d加上i个月之后的结果。其中，i为任意整数。

--显示当前日期加上6个月的日期时间

select ADD_MONTHS(sysdate,6) from dual;

4. 转换类函数

常用的转换类函数及其说明如下表所示：

4.1. TO_CHAR函数

TO_CHAR(str,format)函数用于将str表达式转换为字符串，format表示字符串格式。

--将当前日期转换为YYYY-MM-DD格式

select sysdate as 默认格式日期, to_char(sysdate,'YYYY-MM-DD') as 转换后日期 from dual;

4.2. TO_NUMBER函数

TO_NUMBER(s[,format])函数用于返回字符串s代表的数字，返回值按照format格式进行显示。

--将十六进制的20f转换为十进制数

select to_number('20f','xxx') as 十进制数 from dual;

5. 聚集函数

聚集函数可以针对一组数据进行计算，并得到相应的结果。常用的操作有计算平均值、统计记录数、计算最大值等。Oracle提供的主要聚集函数及其说明如下表所示：

--使用COUNT()函数计算员工总数，使用AVG()函数计算平均工资

select count(empno) as 员工总数,round(avg(sal),2) as 平均工资 from emp;