单行函数：只有一个参数输入，只有一个结果输出。
多行函数：有多个参数输入，只有一个结果输出。
上面的定义是老师说的，网上查的资料也大概都是这样定义的；
但是，像 concat()、substr() 这样的函数，明明有多个参数，却属于单行函数；
而下一节要讲的多行函数包括 count()、sum()、avg() 这样的函数，只有一个参数，却属于多行函数；
所以，我的理解是 单行函数 或者 多行函数 的定义，不是看参数的多少，而是看参与运算的数据是单行的，还是多行的；
比如 select concat('hello','你好') from dual; 将两个字符串连接到一起，参与运算的数据 'hello' 和 '你好'，是属于同一行数据；
而 select sum(sal) "总工资" from emp; 查询表中的总工资，虽然 sum() 函数只有一个参数，但是参与运算的数据却是多行的；
一：大小写控制函数：
1、lower()：将数据转换成小写；
SQL> select lower('www.BAIdu.COM') from dual;
LOWER('WWW.BAIDU.COM')
--------------------------
www.baidu.com2、upper()：将数据转换成大写；
SQL> select upper('www.BAIdu.COM') from dual;
UPPER('WWW.BAIDU.COM')
--------------------------
WWW.BAIDU.COM3、initcap()：将每个单词的首字母转换成大写；
SQL> select initcap('www.BAIdu.COM') from dual;
INITCAP('WWW.BAIDU.COM')
--------------------------
Www.Baidu.Com二、字符控制函数：
1、concat()：连接字符串；
SQL> select concat('hello','你好') from dual;
CONCAT('HELLO','你好')
----------------------
hello你好注意：concat() 函数只能连接两个字符串，如果想连接多个字符串，可以使用 ||，如下：
SQL> select 'hello' || '你好' || '世界' from dual;
'HELLO'||'你好'||'世界'
----------------------------------
hello你好世界2、substr()：截取字符串；下面语句表示从第 5 个字符开始，截取 3 个字符，中英文都是 1 个字符；
SQL> select substr('hello你好',5,3) from dual;
SUBSTR('HELLO
--------------
o你好3、length()：获取字符串的长度；（中英文都是 1 个字符）
SQL> select length('hello你好') from dual;
LENGTH('HELLO你好')
-------------------
74、lengthb()：获取字符串的字节长度；UTF8编码：中文占3个字节；GBK编码：中文占2个字节；英文都是1个字节；
SQL> select lengthb('hello你好') from dual;
LENGTHB('HELLO你好')
--------------------
115、instr()：查找字符串中 指定字符 第一次出现位置的索引；
SQL> select instr('helloworld','o') from dual;
INSTR('HELLOWORLD','O')
-----------------------
5注意：如果找不到，返回 0；该函数对大小写敏感；
6、lpad()：左补字符；如果参数1的字符串不足 10 个字符，则在左边补字符 #；
SQL> select LPAD('hello',10,'#') from dual;
LPAD('HELLO',10,'#')
--------------------
#####hello7、rpad()：右补字符；如果参数1的字符串不足 10 个字符，则在右边补字符 #；
SQL> select RPAD('hello',10,'#') from dual;
RPAD('HELLO',10,'#')
--------------------
hello#####8、trim()：去除字符串两边的 指定字符；（去除字符串 'ooohelloworldoooo' 两边的 'o'，中间的 'o' 不会去除）
SQL> select trim('o' from 'ooohelloworldoooo') from dual;
TRIM('O'FROM'OOOHELL
--------------------
helloworld9、replace()：替换字符串；（将 'hello' 中的 'l' 替换成 'L'）
SQL> select replace('hello','l','L') from dual;
REPLACE('H
----------
heLLo
三、数字函数：
1、round()：四舍五入；（下面语句 保留 3 位小数，第 4 位四舍五入）
SQL> select round(3.1415,3) from dual;
ROUND(3.1415,3)
---------------
3.142round() 函数还可以用于日期：oracle 中用 sysdate 表示当前日期；
下面的语句表示：当前日期按月份四舍五入（每月 1-15 号是上半月，舍去；16号以后是下半月，进一位到下个月）
SQL> select round(sysdate, 'month') from dual;
ROUND(SYSDATE,
--------------
01-10月-19下面的语句表示：当前日期按年份四舍五入（每年 1-6 月是上半年，舍去；7-12 月是下半年，进一位到下一年）
SQL> select round(sysdate, 'year') from dual;
ROUND(SYSDATE,
--------------
01-1月 -202、trunc()：截取；（下面语句 保留 3 位小数，第 4 位直接去掉）
SQL> select trunc(3.1415,3) from dual;
TRUNC(3.1415,3)
---------------
3.141trunc() 函数也可以用于日期：
下面语句表示：截取月份后面的日期，不管是几号，全部去掉，恢复到当月 1号；
SQL> select trunc(sysdate, 'month') from dual;
TRUNC(SYSDATE,
--------------
01-10月-19下面语句表示：截取年份后面的月份，不管是几月，全部去掉，恢复到当年 1月1号；
SQL> select trunc(sysdate, 'year') from dual;
TRUNC(SYSDATE,
--------------
01-1月 -193、mod()：取余；
SQL> select mod(10,3) from dual;
MOD(10,3)
----------
1
四、日期函数：
1、oracle 中使用关键字 sysdate 表示当前日期：（默认的日期格式是 DD-MON-RR）
SQL> select sysdate from dual;
SYSDATE
--------------
10-10月-192、日期的数学运算：在日期上 加上(+) 或 减去(-) 一个数字，结果仍是日期；
例如：显示 昨天、今天、明天 的日期；
SQL> select sysdate-1 "昨天",sysdate "今天",sysdate+1 "明天" from dual;
昨天 今天 明天
-------------- -------------- --------------
09-10月-19 10-10月-19 11-10月-193、日期的数学运算：两个日期相减返回 日期之间相差的 天数；
SQL> select ename "姓名",round(sysdate-hiredate,0) "入职天数" from emp;
姓名 入职天数
-------------------- ----------
SMITH 14176
ALLEN 14111
WARD 14109
JONES 14070
MARTIN 13891
BLAKE 14041
CLARK 14002
SCOTT 11862
KING 13841
TURNER 13911
ADAMS 11828
JAMES 13825
FORD 13825
MILLER 13774
已选择14行。4、months_between()：精确计算到 指定日期 还有多少个月：
参数1是指定的日期，参数2是当前日期；下面语句表示从当前日期到 “2020年10月10号” 还有几个月，结果为 12 个月；
SQL> select months_between('10-10月-20', sysdate) from dual;
MONTHS_BETWEEN('10-10月-20',SYSDATE)
------------------------------------
125、add_months()：在给定日期上 加上指定的月份；
下面语句表示：在当前日期上 加上一个月
SQL> select add_months(sysdate,1) from dual;
ADD_MONTHS(SYS
--------------
10-11月-19下面语句表示：在 "2018年3月10号" 的日期上 减去 2 个月；
SQL> select add_months('10-3月-18',-2) from dual;
ADD_MONTHS('10
--------------
10-1月 -186、next_day()：从指令日期开始算起，获取下一个星期几的日期；
下面语句表示：从当前日期算起，显示下一个 "星期三" 的日期；
SQL> select next_day(sysdate,'星期三') from dual;
NEXT_DAY(SYSDA
--------------
16-10月-19下面语句表示：从指定日期算起，获取下一个 "星期三" 的下一个 "星期日" 的日期；
SQL> select next_day(next_day('10-10月-19','星期三'),'星期日') from dual;
NEXT_DAY(NEXT_
--------------
20-10月-197、last_day()：获取指定日期所属月份最后一天的日期；
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDA
--------------
31-10月-19下面语句用于显示本月倒数第二天的日期：
SQL> select last_day(sysdate)-1 from dual;
LAST_DAY(SYSDA
--------------
30-10月-19下面语句用于显示下一个月的最后一天的日期：
SQL> select last_day(add_months(sysdate,1)) from dual;
LAST_DAY(ADD_M
--------------
30-11月-19
五、数据类型转换函数：
oracle 中有三大数据类型：varchar2、number、date；
数据类型的转换有两种方式：隐式转换 和 显式转换；
oracle 中能自动完成的 隐式转换 有：
varchar2类型 --> number类型；
varchar2类型 --> date类型；
number类型 --> varchar2类型
date类型 --> varchar2类型
例如：字符串类型隐式转换成日期类型；hiredate 字段为日期类型，而 '17-12月-80' 为字符串；
SQL> select * from emp where hiredate = '17-12月-80';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- ---------- ----- ------------ ----- ----- ------
7369 SMITH CLERK 7902 17-12月-80 800 20例如：字符串类型隐式转换成数字类型；将 + 号左边的字符串 '123' 转换成数字 123；
SQL> select '123' + 123 from dual;
'123'+123
----------
246例如：数字类型隐式转换成字符串类型：将 || 右边的数字 123 转换成字符串 '123'；
SQL> select '123' || 123 from dual;
'123'||123
------------
123123显式转换通过 函数 实现：to_char()、to_date()、to_number()；
1、使用 to_char() 函数将 日期类型的数据 转换成 字符串：
参数 2 为有效的日期格式，常用的日期格式如下：（注意：大小写一样）
yyyy：4位数字表示的年份，比如 2011；
year：年份的英文全称，比如 twenty eleven；
mm：两位数字表示的月份，比如 04；
month：月的全称，比如 4月；
dd：2位数字表示的日期，比如 21；
day：星期的全称，比如 星期四；
SQL> select to_char(sysdate,'yyyy-mm-dd day') "日期" from dual;
日期
----------------------------------------------
2019-10-10 星期四参数2 表示的格式中，还可以添加常量，比如 "年"、"月"；格式整体用单引号，常量用双引号；
SQL> select to_char(sysdate,'yyyy" 年 "mm" 月 "dd" 日 "day') "日期" from dual;
日期
----------------------------------------------------------------------
2019 年 10 月 10 日 星期四参数2 表示的格式，还可以是时间格式，比如 HH24:MI:SS，HH24 表示 24 进制的时间；大小写一样；
SQL> select to_char(sysdate,'yyyy-mm-dd day hh24:mi:ss') "日期" from dual;
日期
----------------------------------------------------------------
2019-10-10 星期四 14:28:45如果想显示 12 进制时间，时间格式要改为 HH12；
为了区分上午和下午，时间格式还需要一个 AM（没有 PM，AM 会根据时间自动判断是上午还是下午）
SQL> select to_char(sysdate,'yyyy-mm-dd day hh12:mi:ss am') "日期" from dual;
日期
------------------------------------------------------------------------------
2019-10-10 星期四 02:31:26 下午2、使用 to_char() 函数将 数字类型的数据 转换成 字符串；
参数2 表示转换的格式，常用的格式有：
9：表示数字；
0：就表示数字 0；
$：美元符号；
L：本地货币符号；
.：表示小数点；
,：表示千位符；
SQL> select to_char(1234,'$9,999') from dual;
TO_CHAR(1234,'
--------------
$1,234
SQL> select to_char(1234,'L9,999') from dual;
TO_CHAR(1234,'L9,999')
--------------------------------
￥1,2343、使用 to_date() 函数将 字符串格式 转换成 日期格式；
SQL> select * from emp where hiredate = to_date('1980-12-17','yyyy-mm-dd');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- ---------- ----- ------------ ----- ----- ------
7369 SMITH CLERK 7902 17-12月-80 800 20参数1 为字符串，参数2 为字符串表示的日期格式；
格式中还可以加常量，常量用双引号括起来，如下：
SQL> select * from emp where hiredate = to_date('1980年12月17日','yyyy"年"mm"月"dd"日"');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- ---------- ----- ------------ ----- ----- ------
7369 SMITH CLERK 7902 17-12月-80 800 204、使用 to_number() 函数将字符串转成数字：
SQL> select to_number('123') from dual;
TO_NUMBER('123')
----------------
123
六、通用函数：
1、使用 NVL(a,b) 通用函数，统计员工年收入，NVL() 参数可以是任何类型，即(number/varchar2/date)；
如果 a 为 NULL，就取 b 的值，否则取 a 的值；
SQL> select ename,sal*12+NVL(comm,0) from emp;
ENAME SAL*12+NVL(COMM,0)
---------- ------------------
SMITH 9600
ALLEN 19500
WARD 15500
JONES 35700
MARTIN 16400
BLAKE 34200
CLARK 29400
SCOTT 36000
KING 60000
TURNER 18000
ADAMS 13200
JAMES 11400
FORD 36000
MILLER 15600
已选择14行。2、使用 NVL2(a,b,c) 通用函数，统计员工年收入；如果 a 不为 NULL，取 b 值，否则取 c 值；
SQL> select ename,sal*12+NVL2(comm,comm,0) from emp;
ENAME SAL*12+NVL2(COMM,COMM,0)
---------- ------------------------
SMITH 9600
ALLEN 19500
WARD 15500
JONES 35700
MARTIN 16400
BLAKE 34200
CLARK 29400
SCOTT 36000
KING 60000
TURNER 18000
ADAMS 13200
JAMES 11400
FORD 36000
MILLER 15600
已选择14行。3、使用 NULLIF(a,b) 通用函数，在类型一致的情况下，如果 a 与 b 相同，返回 NULL，否则返回 a；
SQL> select NULLIF(10,10) from dual;
NULLIF(10,10)
-------------
SQL> select NULLIF(10,100) from dual;
NULLIF(10,100)
--------------
10
七、流程控制函数：
使用 SQL99 标准通用语法中的 case 表达式：
case 字段
when 条件1 then 表达式1
when 条件2 then 表达式2
else 表达式n
end
将职位是分析员的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400；
SQL> select ename "姓名",job "职位",sal "涨前工资",
2 case job
3 when 'ANALYST' then sal+1000
4 when 'MANAGER' then sal+800
5 else sal+400
6 end "涨后工资"
7 from emp;
姓名 职位 涨前工资 涨后工资
-------------------- ------------------ ---------- ----------
SMITH CLERK 800 1200
ALLEN SALESMAN 1600 2000
WARD SALESMAN 1250 1650
JONES MANAGER 2975 3775
MARTIN SALESMAN 1250 1650
BLAKE MANAGER 2850 3650
CLARK MANAGER 2450 3250
SCOTT ANALYST 3000 4000
KING PRESIDENT 5000 5400
TURNER SALESMAN 1500 1900
ADAMS CLERK 1100 1500
JAMES CLERK 950 1350
FORD ANALYST 3000 4000
MILLER CLERK 1300 1700
已选择14行。oracle 中对 case 表达式做了优化，用 decode() 函数替换：
decode(字段,条件1,表达式1,条件2,表达式2,...表达式n)
SQL> select ename "姓名",job "职位",sal "涨前工资",
2 decode(job,'ANALYST',sal+1000,'MANAGER',sal+800,sal+400) "涨后工资"
3 from emp;
姓名 职位 涨前工资 涨后工资
-------------------- ------------------ ---------- ----------
SMITH CLERK 800 1200
ALLEN SALESMAN 1600 2000
WARD SALESMAN 1250 1650
JONES MANAGER 2975 3775
MARTIN SALESMAN 1250 1650
BLAKE MANAGER 2850 3650
CLARK MANAGER 2450 3250
SCOTT ANALYST 3000 4000
KING PRESIDENT 5000 5400
TURNER SALESMAN 1500 1900
ADAMS CLERK 1100 1500
JAMES CLERK 950 1350
FORD ANALYST 3000 4000
MILLER CLERK 1300 1700
已选择14行。
681
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
