1、字符函数
- 字符函数的输入为字符类型，其返回值是字符类型或者是数字类型。
- 字符函数可以分为大小写控制函数（UPPER、LOWER和INITCAP）和字符控制函数（CONCAT、SUBSTR、LENGTH、INSERT、LPAD、RPAD、TRIM、REPLACE）两类。
a、大小写控制函数
- 这类函数用于改变字符的大小写。（需要注意的是，当输入的字符为NULL（null）时，返回值也为NULL）
- UPPER（char）：该函数用于将字符串转换成大写格式。
- LOWER（char）：该函数用于将字符串转换成小写格式。
- INITCAP（char）：该函数用于将字符串中的每个单词的首字母大写，其他字符小写。
- 示例：
sql> conn scott/02000059 已连接。 sql> select * from emp where job = upper('salesman'); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 ```
sql> select * from emp where lower(job) = 'clerk'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 G_EASON CLERK 7902 17-12月-80 800 20 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7934 MILLER CLERK 7782 23-1月 -82 1300 10 ```
sql> select empno,initcap(ename) ename from emp; EMPNO ENAME 7369 G_Eason 7499 Allen 7521 Ward 7566 Jones 7654 Martin 7698 Blake 7782 Clark 7788 Scott 7839 King 7844 Turner 7876 Adams EMPNO ENAME 7900 James 7902 Ford 7934 Miller 已选择14行。
b、字符控制函数
- CONCAT(str1,str2)：该函数用于字符串的连接，str1和str2用于指定被连接的字符串。
- 例如：concat('lello','oracle') 结果：hellooracle
sql> select concat(concat(ename,':'),sal) from emp; CONCAT(CONCAT(ENAME,SAL) G_EASON:800 ALLEN:1600 WARD:1250 JONES:2975 MARTIN:1250 BLAKE:2850 CLARK:2450 SCOTT:3000 KING:5000 TURNER:1500 ADAMS:1100 JAMES:950 FORD:3000 MILLER:1300 已选择14行。 ``` ----- - SUBSTR(char,m[,n])：该函数用于截取字符串，char指定源自符串，m用于指定从哪个位置开始截取，n用于指定截取字符串的长度。如果m为0，则从首字母开始，如果m为负数，则从尾部开始。 - 例如：substr('hello'1,3)，结果为：ell；substr('hello',3)，结果为hel；substr('hello',-1,1)，结果为：o。
sql> select * from emp where substr(job,1,4) = 'SALE'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
----- - LENGTH(char)：该函数用于返回字符串的长度，字符串中的后缀空格也计作字符串的长度。例如length('oracle ')，结果为：7。
sql> select * from emp where length(ename) = 6; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7934 MILLER CLERK 7782 23-1月 -82 1300 10
----- - INSTR(char1,char2,[,n[,m]])：该函数用于取得子串在字符串中的位置，char1用于指定源字符串，char2用于指定子串，n用于指定起始搜索位置（默认值：1），m用于指定子串的第m次出现的次数（默认值：1）。 - 例如：instr('hello oracle','oracle')，结果为：7；instr('hello oracle hello oracle','oracle',2)，结果为：20。
sql> select instr('hello oracle','oracle') from dual; INSTR('HELLOORACLE','ORACLE') 7
sql> select instr('hello oracle hello oracle',5,2) from dual; INSTR('HELLOORACLEHELLOORACLE','ORACLE',2) 20
- 注意，dual为一张虚拟的表。 ----- - LPAD(char1,n char2)：该函数用于在字符串的左端填充字符，char1用于指定源字符串，char2用于指定被填充的字符，n用于指定填充后的char1的总长度。 - 例如：lpad('hello',10,'#')，结果是：#####hello。
sql> select lpad(job,9,'*') from emp; LPAD(JOB,'*') ****CLERK *SALESMAN *SALESMAN **MANAGER *SALESMAN **MANAGER **MANAGER **ANALYST PRESIDENT *SALESMAN ****CLERK ****CLERK **ANALYST ****CLERK 已选择14行。 ```
- RPAD(char1,n char2)：该函数用于在字符串的右端填充字符，char1用于指定源字符串，char2用于指定被填充的字符，n用于指定填充后的char1的总长度。
- 例如：rpad('hello','#')，结果是：hello#####。
- REPLACE(char,search_string[,replacement_string])：该函数用于替换字符串的子串内容。Char用于指定源字符串，search_string用于指定被替换子串，repalcement_string用于指定替换子串。
- 例如：replace('hello oracle','world')，结果为：hello world。
sql> select replace('hello oracle','world') replace_fun from dual; REPLACE_FUN hello world
2、数值函数
- ROUND(n,m])：四舍五入，说明：该函数用于返回四舍五入的结果，其中n可以使任意数字，m必须是整数。
- 例如：ROUND(25.328)，结果为：25；ROUND(25.328,2)，结果为：25.33；ROUND(25.328,-1)，结果为：30。
sql> select sal/30,round(sal/30,2) from emp; SAL/30 ROUND(SAL/30,2) 26.6666667 26.67 53.3333333 53.33 41.6666667 41.67 99.1666667 99.17 41.6666667 41.67 95 95 81.6666667 81.67 100 100 166.666667 166.67 50 50 36.6666667 36.67 31.6666667 31.67 100 100 43.3333333 43.33 已选择14行。
- TRANC：截取数字，TRUNC(n,[m])，该函数用于截取数字，其中n可以是任意数字，m必须是整数。
- 例如：trunc(25,328)，结果为：25；trunc(25.328,2)，结果为：25.32；trunc(25.328,-1)，结果为：20。
sql> select sal/30,trunc(sal/30,2) from emp; SAL/30 TRUNC(SAL/30,2) 26.6666667 26.66 53.3333333 53.33 41.6666667 41.66 99.1666667 99.16 41.6666667 41.66 95 95 81.6666667 81.66 100 100 166.666667 166.66 50 50 36.6666667 36.66 31.6666667 31.66 100 100 43.3333333 43.33 已选择14行。
- MOD：求模（求余数），MOD(m,n)，该函数用于取得两个数字相除后的余数。如果数字n为0，则返回结果为m。
- 例如：mod(25,6)，结果为：1；mod(25,0)，结果为：25。
sql> select * from emp where mod(empno,2) = 0; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 DEPTNO 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10 已选择10行。 ``` # 3、日期和时间函数 - 用于处理日期时间类型的函数。 - 其常用的日期时间函数有：SYSDATE、MONTHS_BETWEEN、ADD_MONTHS、NEXT_DAY、LAST_DAY、ROUND、TRUNC。 ----- - SYSDATE：该函数用于返回当前系统日期。 - 举例：select sysdate-1 昨天,sysdate 今天，sysdate + 1明天 from dual;。
sql> select sysdate-1 昨天,sysdate 今天,sysdate + 1 明天 from dual; 昨天 今天 明天 02-12月-17 03-12月-17 04-12月-17
----- - MONTHS_BETWEEN(d1,d2)：改函数用于返回日期d1和d2之间相差的月数。d1大于d2结果为正数，否则为负数。 - 举例：计算员工入职多长时间，select months_between(sysdate,hiredate) from emp;。
sql> select round(months_between(sysdate,hiredate)/12) hiredate_year from emp; HIREDATE_YEAR 37 37 37 37 36 37 36 31 36 36 31 36 36 36 已选择14行。 ```
- ADD_MONTHS：add_months(d,n)该函数用于返回特定日期时间之后或者之前的月份所对应的日期时间。（求出若干月之后的日期）d用于指定日期时间，n可以是任意整数。
- 举例：查询每位员工入职30年后的年份，select ename,add_months(hiredate,30* 12) from emp;。
sql> select ename,30* 12) from emp; ENAME ADD_MONTHS(HIR G_EASON 17-12月-10 ALLEN 20-2月 -11 WARD 22-2月 -11 JONES 02-4月 -11 MARTIN 28-9月 -11 BLAKE 01-5月 -11 CLARK 09-6月 -11 SCOTT 19-4月 -17 KING 17-11月-11 TURNER 08-9月 -11 ADAMS 23-5月 -17 JAMES 03-12月-11 FORD 03-12月-11 MILLER 23-1月 -12 已选择14行。
- NEXT_DAY(d,char)：该函数用于返回特定日期之后的第一个工作日所对应的日期。d用于指定日期时间值，char用于指定工作日。
- 注意：当使用该函数时，工作日必须与日期语言匹配，假如日期语言为AMERICAN，那么周一对应于MONDAY；假如日期语言为简体中文，那么周一对应于“星期一”。
- 例如：查询周一的日期，select sysdate,nextday(sysdate,'星期一') from dual;。
sql> select sysdate,next_day(sysdate,'星期一') 下一个工作日 from dual; SYSDATE 下一个工作日 03-12月-17 04-12月-17
- LAST_DAY：该函数用于返回特定日期所在月份的最后一天。
- 例如：select sysdate,last_day(sysdate) from dual;。
sql> select sysdate,last_day(sysdate) from dual; SYSDATE LAST_DAY(SYSDA 03-12月-17 31-12月-17
- ROUND(d[,fmt])：该函数用于返回日期时间的四舍五入的结果。d用于指定日期的时间值，fmt用于指定四舍五入的方式。
- 如果设置fmt为YEAR，则7月1日为分界线，如果设置fmt为MONTH，则16日为分界线。
- 例如：如果系统时间是“20-7月-17”，round(sysdate,'YEAR')，结果为：01-1月-18；round(sysdate,'MONTH')，结果为：01-8月-17。
sql> select round(sysdate,'YEAR') as 时间的四舍五入 from dual; 时间的四舍五入 01-1月 -18 sql> select round(sysdate,'MONTH') as 时间的四舍五入 from dual; 时间的四舍五入 01-12月-17
- 当前的系统时间为03-12月-17。
- TRUNC：TRUNC(d[,fmt])：该函数用于截断日期时间数据。D用于指定日期的时间值，fmt用于指定截断日期时间数据的方法，如果设置fmt为YEAR，则结果为本年度的1月1日，如果设置fmt为MONTH，则结果为本月的1日。
- 例如：trunc(sysdate,'YEAR')；trunc(sysdate,'MONTH');。
sql> select trunc(sysdate,'MONTH') as 截断的时间 from dual; 截断的时间 01-12月-17 sql> select trunc(sysdate,'YEAR') as 截断的时间 from dual; 截断的时间 01-1月 -17
- 当前的系统时间为03-12月-17。
4、转换函数
- 该函数用于将数据从一种数据类型转换成另外一种数据类型。
a、隐式数据类型转换
- Oracle可以隐式的（自动的）进行数据类型转换
| 源数据类型 | 目标数据类型 |
|---|---|
| VARCHAR2或者CHAR | NUMBER |
| VARCHAR2或者CHAR | DATE |
| NUMBER | VARCHAR2 |
| DATE | VARCHAR2 |
- 如下示例：
sql> select * from emp where sal > '3000'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7839 KING PRESIDENT 17-11月-81 5000 10
sql> select * from emp where hiredate = '17-11月-81'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7839 KING PRESIDENT 17-11月-81 5000 10
b、显式地数据类型转换
- 日期类型 -> 字符类型（使用TO_CHAR()）、字符类型 -> 日期类型（使用TO_DATE()）。
- 字符类型 -> 数值类型（使用TO_NUMBER()）、数值类型 -> 字符类型（使用TO_CHAR()）。
- TO_CHAR：将日期类型转换为字符类型。函数格式为：TO_CHAR(d,fmt [,'nlsparams']])。
- d用于知道你跟日起值，fmt用于指定日期格式模型，'nlsparams'用于指定日期显示语言（格式：'NLS_DATE_LANGUAGE=language'）
- 默认日期显示格式为DD-MON-RR。select to_char(hiredate,'DD-MON-RR','NLS_DATE_LANGUAGE=AMERICAN') from emp;。
sql> select to_char(hiredate,'NLS_DATE_LANGUAGE=AMERICAN') to_char from emp; TO_CHAR 17-DEC-80 20-FEB-81 22-FEB-81 02-APR-81 28-SEP-81 01-MAY-81 09-JUN-81 19-APR-87 17-NOV-81 08-SEP-81 23-MAY-87 03-DEC-81 03-DEC-81 23-JAN-82 已选择14行。
- 需要注意，当在格式模型中增加字符值时，必须用双引号引住字符值。如：select to_char(hiredate,'YYYY"年"MM"月"DD"日"') to_char from emp;。
sql> select to_char(hiredate,'YYYY"年"MM"月"DD"日"') to_char from emp; TO_CHAR 1980年12月17日 1981年02月20日 1981年02月22日 1981年04月02日 1981年09月28日 1981年05月01日 1981年06月09日 1987年04月19日 1981年11月17日 1981年09月08日 1987年05月23日 1981年12月03日 1981年12月03日 1982年01月23日 已选择14行。
- TO_CHAR：将数值类型转换成字符类型。函数格式为：TO_CHAR(n[,fmt])。
- n用于指定数值，fmt用于指定数字格式的模型。
- 格式模型，常用的元素如下：
| 常用元素 | 说明 |
|---|---|
| 9 | 显示数字，并且会忽略前导0 |
| 0 | 显示数字，如果位数不足，则用0补齐 |
| . | 在指定位置显示小数点 |
| , | 在指定位置显示逗号 |
| $ | 在数字前加上美元符号 |
| L | 在数字前加上本地货币符号 |
sql> SELECT sal,to_char(sal,'L00,000,000.00') to_char FROM emp; SAL TO_CHAR 800 ￥00,800.00 1600 ￥00,001,600.00 1250 ￥00,250.00 2975 ￥00,002,975.00 1250 ￥00,250.00 2850 ￥00,850.00 2450 ￥00,450.00 3000 ￥00,003,000.00 5000 ￥00,005,000.00 1500 ￥00,500.00 1100 ￥00,100.00 950 ￥00,950.00 3000 ￥00,000.00 1300 ￥00,300.00 已选择14行。 sql> SELECT sal,'L99,999,999.99') to_char FROM emp; SAL TO_CHAR 800 ￥800.00 1600 ￥1,600.00 1250 ￥1,250.00 2975 ￥2,975.00 1250 ￥1,250.00 2850 ￥2,850.00 2450 ￥2,450.00 3000 ￥3,000.00 5000 ￥5,000.00 1500 ￥1,500.00 1100 ￥1,100.00 950 ￥950.00 3000 ￥3,000.00 1300 ￥1,300.00 已选择14行。
- TO_DATE(char[,fmt[,'nlsparams']])：该函数用于将字符串转换成日期类型的数据。char用于匹配日期类型的字符串，fmt用于指定日期格式模型，'nlsparams'用于指定日期语言。
- 举例：查看1982之后入职的员工信息：select ename,hiredate from emp where hiredate > to_date('1981-12-31','YYYY-MM-DD');
sql> select ename,hiredate from emp where hiredate > to_date('1981-12-31','YYYY-MM-DD'); ENAME HIREDATE SCOTT 19-4月 -87 ADAMS 23-5月 -87 MILLER 23-1月 -82
- TO_NUMBER(n[,fmt])：该函数可以将包含数字的字符串转换成数值类型。n是包含数字的字符串，fmt用于指定数字格式模型。
- 显示工资大于3000的所有员工的姓名和工资：
- select ename,sal from emp where sal > to_number('￥3000','L99999');
sql> select ename,sal from emp where sal > to_number('￥3000','L99999'); ENAME SAL KING 5000
5、通用函数
- 此类函数适用于任何数据类型，同时也适用于空值。如：NVL(expr1,expr2)、NVL2(expr1,expr2,expr3)、NULLIF(expr1,expr2)、COALESCE(expr1 [,expr2 ] [,... ])。
处理NULL
- 例如：select ename,sal,comm,sal + comm from emp;
- 在Oracle9i之前，处理null只能使用函数NVL。
- 但是从Oracle9i之后，NVL或者NVL2都可以处理NULL。
- NVL(expr1,expr2)：该函数用于处理NULL。如果expr是null，则返回expr2，如果expr1不是null，则返回expr1。
- 例如：select ename,sal + nvl(comm,0) from emp;
sql> select ename,0) from emp; ENAME SAL COMM SAL+NVL(COMM,0) G_EASON 800 800 ALLEN 1600 300 1900 WARD 1250 500 1750 JONES 2975 2975 MARTIN 1250 1400 2650 BLAKE 2850 2850 CLARK 2450 2450 SCOTT 3000 3000 KING 5000 5000 TURNER 1500 0 1500 ADAMS 1100 1100 JAMES 950 950 FORD 3000 3000 MILLER 1300 1300 已选择14行。
- NVL2(expr1,expr3)：该函数用于处理NULL，如果expr1不是null，则返回expr2，如果expr1是null，则返回expr3。例如：select ename,nvl2(comm,sal+ comm,sal) nvl2 from emp;。
sql> select ename,sal) nvl2 from emp; ENAME SAL COMM NVL2 G_EASON 800 800 ALLEN 1600 300 1900 WARD 1250 500 1750 JONES 2975 2975 MARTIN 1250 1400 2650 BLAKE 2850 2850 CLARK 2450 2450 SCOTT 3000 3000 KING 5000 5000 TURNER 1500 0 1500 ADAMS 1100 1100 JAMES 950 950 FORD 3000 3000 MILLER 1300 1300 已选择14行。 ``` ----- - NULLIF(expr1,expr2)：该函数用于比较表达式expr1和expr2，如果二者相等，则返回NULL，否则返回expr1。 ----- - COALESCE：COALESCE(expr1[,expr2][,...]。该函数用于返回表达式列表中第一个NOT NULL表达式的结果。例如：select ename,coalesce(sal + comm,sal) coalesce from emp;
sql> select ename,sal) coalesce from emp; ENAME SAL COMM COALESCE EASON 3000 3000 G_EASON 800 800 ALLEN 1600 300 1900 WARD 1250 500 1750 JONES 2975 2975 MARTIN 1250 1400 2650 BLAKE 2850 2850 CLARK 2450 2450 SCOTT 3000 3000 KING 5000 5000 TURNER 1500 0 1500 ADAMS 1100 1100 JAMES 950 950 FORD 3000 3000 MILLER 1300 1300 已选择15行。 ```
6、条件表达式
- 条件表达式中主要有CASE表达式和Decode()函数。
a、CASE表达式
- 语法格式为：CASE expr WEHRE comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 ...... WHEN comparison_exprn THEN return_exprn ELSE else_expr] END。
- 示例：想显示全部雇员的职位，但是这些职位要求替换为中文显示。
sql> select empno,ename,case job when 'CLERK' then '办事员' when 'SALESMAE' then '销售' when 'MANAGER' then '经理' when 'ANALYST' then '分析员' else '总裁' end case from emp; EMPNO ENAME CASE 7951 EASON 分析员 7369 G_EASON 办事员 7499 ALLEN 总裁 7521 WARD 总裁 7566 JONES 经理 7654 MARTIN 总裁 7698 BLAKE 经理 7782 CLARK 经理 7788 SCOTT 分析员 7839 KING 总裁 7844 TURNER 总裁 7876 ADAMS 办事员 7900 JAMES 办事员 7902 FORD 分析员 7934 MILLER 办事员 已选择15行。 sql> ``` ## b、Decode函数 - 语法格式：DECODE(col | expression,search1,result1,search2,result2,....,] [,default]。Decode函数和case表达式都可用于IF-THEN-ELSE逻辑判断的。 - 示例：想显示全部雇员的职位，但是这些职位要求替换为中文显示。
sql> select empno,job,decode(job,'CLERK','办事员','SALESMAN','销售','MANAGER','经理','ANALYST','分析员','总裁') from emp; EMPNO ENAME JOB DECODE 7951 EASON ANALYST 分析员 7369 G_EASON CLERK 办事员 7499 ALLEN SALESMAN 销售 7521 WARD SALESMAN 销售 7566 JONES MANAGER 经理 7654 MARTIN SALESMAN 销售 7698 BLAKE MANAGER 经理 7782 CLARK MANAGER 经理 7788 SCOTT ANALYST 分析员 7839 KING PRESIDENT 总裁 7844 TURNER SALESMAN 销售 7876 ADAMS CLERK 办事员 7900 JAMES CLERK 办事员 7902 FORD ANALYST 分析员 7934 MILLER CLERK 办事员 已选择15行。
# 7、嵌套函数 - 嵌套函数的执行熟顺序是由内到外。 - 语法格式：F3(F2(col,arg1),arg2),arg3)。 - 参照雇员信息表，想显示距聘用日期3个月后的下一个星期一的日期，且日期格式如：2017-01-06。 - 分析：距聘用日期3个月后：add_months(hiredate,3)，下一个星期一：next_day(add_months(hiredate,3),'星期一')，且日期格式如2017-01-06： to_char(next_day(add_months(hiredate,'星期一'),'YYYY-MM-DD')。
sql> select to_char(next_day(add_months(hiredate,'YYYY-MM-DD') new_date from emp; NEW_DATE 2018-03-05 1981-03-23 1981-05-25 1981-05-25 1981-07-06 1982-01-04 1981-08-03 1981-09-14 1987-07-20 1982-02-22 1981-12-14 1987-08-24 1982-03-08 1982-03-08 1982-04-26 已选择15行。 ```
- 参照雇员信息表，显示雇员日薪并四舍五入到2位小数的结果，然后对薪资格式以：￥1,182.19'这样的例子形式进行格式化。
- 分析：雇员日薪并四舍五入到2位小数的结果：雇员日薪并四舍五入到2位小数的结果：round(sal/30,2)；对薪资格式以‘￥1,182.19’这样的例子形式进行格式化：to_char(round(sal/30,2),'L9,999.99')。
sql> select to_char(round(sal/30,999.99') from emp; TO_CHAR(ROUND(SAL/3 ￥100.00 ￥26.67 ￥53.33 ￥41.67 ￥99.17 ￥41.67 ￥95.00 ￥81.67 ￥100.00 ￥166.67 ￥50.00 ￥36.67 ￥31.67 ￥100.00 ￥43.33 已选择15行。
