时间：2021-07-01 10:21:17 帮助过：47人阅读
Sql 有两种函数，单行函数和多行函数 1. 单行函数 单行函数: 操作数据对象、接受参数返回一个结果、只对一行进行变换、每行返回一个结果、可以转换数据类型、可以嵌套、参数可以是一列或一个 DUAL 是一个‘ 伪表’，可以用来测试函数和表达式 2. 字符函数 大
Sql有两种函数，单行函数和多行函数
1.单行函数
单行函数:操作数据对象、接受参数返回一个结果、只对一行进行变换、每行返回一个结果、可以转换数据类型、可以嵌套、参数可以是一列或一个值
DUAL是一个‘伪表’，可以用来测试函数和表达式
2.字符函数
大小写控制函数：这类函数改变字符的大小写。
例子：
结果为2。
- <pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li>select lower(ename) from emp;</li><li>LOWER(ENAM </li><li>---------- </li><li>smith </li><li>allen </li><li>ward </li><li>select upper(ename) from emp;</li><li>UPPER(ENAM </li><li>---------- </li><li>SMITH </li><li>ALLEN </li><li>WARD </li><li>select initcap(ename) from emp;</li><li>INITCAP(EN </li><li>---------- </li><li>Smith </li><li>Allen </li><li>Ward </li><li></li></ol></pre><br>
- <pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li></li></ol></pre>
- <p><strong><span>字符控制函数：</span></strong></p>
- <p><img src="https://www.gxlcms.com/inc/test.jsp?url=http%3A%2F%2Fimg.my.csdn.net%2Fuploads%2F201304%2F05%2F1365123810_8726.png&refer=http%3A%2F%2Fblog.csdn.net%2Fyangkai_hudong%2Farticle%2Fdetails%2F8760441"></p>
- <pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li>select concat('hello','word')from dual;</li><li>CONCAT('H </li><li>--------- </li><li>helloword </li><li>select substr('helloword',1,3) from dual;</li><li>SUB </li><li>--- </li><li>hel </li><li>select length('helloword') from dual;</li><li>LENGTH('HELLOWORD') </li><li>------------------- </li><li> 9 </li><li>select instr('helloworld','w') from dual;</li><li>INSTR('HELLOWORLD','W') </li><li>----------------------- </li><li> 6 </li><li>select lpad('hello',10,'*')from dual;</li><li>LPAD('HELL </li><li>---------- </li><li>*****hello</li><li>select rpad('hello',10,'#')from dual;</li><li>RPAD('HELL </li><li>---------- </li><li>hello##### </li><li>select trim(' hello ') from dual;</li><li>TRIM( </li><li>----- </li><li>hello </li><li></li></ol></pre>
- <p><br>
- </p>
- <p><span><span><span>3.</span><span>数字函数</span></span></span></p>
- <p><strong><span><span><span>ROUND:</span><span>四舍五入</span></span></span></strong></p>
- <p><strong><span><span><span>TRUNC: </span><span>截断</span></span></span></strong></p>
- <p><strong><span><span><span>MOD:</span><span>求余</span></span></span></strong></p>
- <p><span>Round 函数 :语法为ROUND(number，num_digits)；<strong>其中Number是需要进行四舍五入的数字；Num_digits为指定的位数</strong>，按此位数进行四舍五入，如果 num_digits 大于 0，则四舍五入到指定的小数位; Num_digits值为多少就到相应的小数点位置四舍五入，<span>如果 num_digits</span>等于
- 0，则四舍五入到最接近的整数，如果 num_digits 小于 0，则在小数点左侧进行四舍五入；Num_digits值多少就到小数点左侧的整数相应的位置四舍五入。</span></p>
- <p><strong><span>例如：</span></strong></p>
- <p><span>ROUND(2.149， 0) 将 2.149 四舍五入到一个整数</span></p>
ROUND(2.15， 1) 将 2.15 四舍五入到一个小数位，结果为2.2。
ROUND(2.149， 1) 将 2.149 四舍五入到一个小数位结果为2.1。
ROUND(-1.475， 2) 将 -1.475 四舍五入到两小数位结果为-1.48)。
ROUND(21.5， -1) 将 21.5 四舍五入到小数点左侧一位结果为20。
例子：
- SQL> select round(45.926,2) from dual;
- ROUND(45.926,2)
- ---------------
- 45.93
- SQL> select round(45.926,-2) from dual;
- ROUND(45.926,-2)
- ----------------
- 0
- SQL> select round(55.926,-2) from dual;
- ROUND(55.926,-2)
- ----------------
- 100
- SQL> select round(50.926,-2) from dual;
- ROUND(50.926,-2)
- ----------------
- 100
- SQL> select round(150.926,-2) from dual;
- ROUND(150.926,-2)
- -----------------
- 200
- SQL> select round(50.326,-2) from dual;
- ROUND(50.326,-2)
- ----------------
- 100
- SQL> select round(550.326,-2) from dual;
- ROUND(550.326,-2)
- -----------------
- 600
- SQL> select trunc(45.926,2) from dual;
- TRUNC(45.926,2)
- ---------------
- 45.92
- SQL> select trunc(45.926,-2) from dual;
- TRUNC(45.926,-2)
- ----------------
- 0
- SQL> select trunc(55.926,-2) from dual;
- TRUNC(55.926,-2)
- ----------------
- 0
- SQL> select trunc(155.926,-2) from dual;
- TRUNC(155.926,-2)
- -----------------
- 100
- SQL> select mod(1600,300) from dual;
- MOD(1600,300)
- -------------
- 100
- SQL> select mod(13,3) from dual;
- MOD(13,3)
- ----------
- 1
- SQL> select round(45.926,-1) from dual;
- ROUND(45.926,-1)
- ----------------
- 50
4.日期
Oracle中的日期型数据实际含有两个值: 日期和时间。
默认的日期格式是 DD-MON-RR.函数SYSDATE 返回:日期、时间
在日期上加上或减去一个数字结果仍为日期。两个日期相减返回日期之间相差的天数。可以用数字除24来向日期中加上或减去小时。
日期函数
注：日期转换格式不支持转换中文格式的日期
例子：
- SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;
- TO_CHAR(SY
- ----------
- 2013-04-04
- SQL> select to_char(sysdate,'yyyy/mm/dd') from dual;
- TO_CHAR(SY
- ----------
- 2013/04/04
- select to_char(sysdate,'YEAR-MONTH-DAY') from dual;
- TO_CHAR(SYSDATE,'YEAR-MONTH-DAY')
- -----------------------------------------------------------
- TWENTY THIRTEEN-4月 -星期四
- select to_date('1212-12-12','yyyy/mm/dd') from dual;
- TO_DATE('1212-
- --------------
- 12-12月-12
- SQL> select to_date('1212-12-12','yyyy-mm-dd') from dual;
- TO_DATE('1212-
- --------------
- 12-12月-12
- SQL> select to_char(sysdate,'dd month year') from dual;
- TO_CHAR(SYSDATE,'DDMONTHYEAR')
- ----------------------------------------------------
- 04 4月 twenty thirteen
- SQL> select to_char(sysdate,'dd month yyyy') from dual;
- TO_CHAR(SYSDAT
- --------------
- 04 4月 2013
5.转换函数
隐式数据类型转换：Oracle自动完成下列转换：
TO_CHAR 函数对日期的转换
格式:必须包含在单引号中而且大小写敏感。可以包含任意的有效的日期格式。日期之间用逗号隔开。
例子：
- select ename,to_char(sal,'$999,999.00') from emp;
- ENAME TO_CHAR(SAL,
- ---------- ------------
- SMITH $800.00
- ALLEN $1,600.00
- WARD $1,250.00
- select ename,to_char(sal,'l999,999.00') from emp;
- ENAME TO_CHAR(SAL,'L999,999
- ---------- ---------------------
- SMITH ￥800.00
- ALLEN ￥1,600.00
- WARD ￥1,250.00
- <span><span><span>6. </span><span>通用函数</span></span></span>
这些函数适用于任何数据类型，同时也适用于空值：
NVL (expr1, expr2):将空值转换成一个已知的值：可以使用的数据类型有日期、字符、数字。
函数的一般形式:
NVL(commission_pct,0)
NVL(hire_date,'01-JAN-97')
NVL(job_id,'No Job Yet')
NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。相当于java中的三目运算符
NULLIF (expr1, expr2) : 相等返回NULL，不等返回expr1
COALESCE (expr1, expr2, ..., exprn):COALESCE 与 NVL 相比的优点在于 COALESCE 可以同时处理交替的多个值。如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE 。
例子：
- SQL> select ename,nvl(comm,0) from emp;
- ENAME NVL(COMM,0)
- ---------- -----------
- SMITH 0
- ALLEN 300
- WARD 500
- 错误写法，条件comm与0位置混乱
- SQL> select ename,nvl2(comm,0,comm) from emp;
- ENAME NVL2(COMM,0,COMM)
- ---------- -----------------
- SMITH
- ALLEN 0
- WARD 0
- JONES
- 正确写法：
- SQL> select ename,nvl2(comm,comm,0) from emp;
- ENAME NVL2(COMM,COMM,0)
- ---------- -----------------
- SMITH 0
- ALLEN 300
- WARD 500
- SQL> select nullif(2,2) from dual;
- NULLIF(2,2)
- -----------
- SQL> select nullif(2,1) from dual;
- NULLIF(2,1)
- -----------
- 2
- SQL> select nullif(1,2) from dual;
- NULLIF(1,2)
- -----------
- 1
- SQL> select ename,
- 2 coalesce(sal,comm) from emp;
- ENAME COALESCE(SAL,COMM)
- ---------- ------------------
- SMITH 800
- ALLEN 1600
- WARD 1250
- COALESCE(COMM,SAL)
- ------------------
- 800
- 300
- 500
- 2975
7条件表达式
在 SQL 语句中使用IF-THEN-ELSE 逻辑；使用两种方法:
CASE 表达式
DECODE 函数
例子:
- SQL> select ename,job,sal,case
- 2 job when 'SALESMAN' then sal*1.2
- 3 when 'MANAGER' then sal*1.8
- 4 else sal end "revised_sal" from emp;
- ENAME JOB SAL revised_sal
- ---------- --------- ----- -----------
- SMITH CLERK 800 800
- ALLEN SALESMAN 1600 1920
- WARD SALESMAN 1250 1500
- JONES MANAGER 2975 5355
- SQL> select ename,job,sal,decode(
- 2 job,'SALESMAN',1.2*sal,
- 3 'MANAGER',1.8*sal,sal)
- 4 revised_sal from emp;
- ENAME JOB SAL REVISED_SAL
- ---------- --------- ----- -----------
- SMITH CLERK 800 800
- ALLEN SALESMAN 1600 1920
- WARD SALESMAN 1250 1500
8.嵌套函数
单行函数可以嵌套。嵌套函数的执行顺序是由内到外。
例子:
- SQL> select ename,job,nvl2(to_char(job),'manager','NO MANAGER') from emp;
- ENAME JOB NVL2(TO_CH
- ---------- --------- ----------
- SMITH CLERK manager
- ALLEN SALESMAN manager
- WARD SALESMAN manager