单行函数
单行函数: 操作数据对象 接受参数返回一个结果 只对一行进行变换 每行返回一个结果 可以转换数据类型 可以嵌套 参数可以是一列或一个值
![\]()
*************************************************************************************************************************************************
字符函数
![\]()
*************************************************************************************************************************************************
SQL> --字符函数
大小写控制函数
![\]()
SQL> select lower("HELLO World') 转小写, upper('HELLO World') 转大写, initcap('hello world') 首字母大写
2 from dual;
转小写 转大写 首字母大写
----------- ----------- -----------
hello world HELLO WORLD Hello World
SQL> --substr:求子串
SQL> select substr('Hello World',4) from dual;
SUBSTR('
--------
lo World
SQL> select substr('Hello World',4,5) from dual;
SUBST
-----
lo Wo
字符控制函数
![\]()
字符数字节数 SQL> --length:字符数 lengthb：字节数
SQL> select length("Hello World') 字符数,lengthb('Hello World') 字节数
2 from dual;
字符数 字节数
---------- ----------
11 11
SQL> ed
已写入 file afiedt.buf
1 select length('北京') 字符数,lengthb('北京') 字节数
2* from dual
SQL> /
字符数 字节数
---------- ----------
2 4
SQL> host cls
查找子串 SQL> --instr:在母串中查找子串，如果找到返回下标（从1开始）；否则返回0
SQL> select instr('Hello World','ll') from dual;
INSTR('HELLOWORLD','LL')
------------------------
3
左右填充
SQL> --lpad:左填充 rpad 右填充
SQL> select lpad('abcd',10,'*') 左填充,rpad('abcd',10,'*') 右填充 from dual;
左填充 右填充
---------- ----------
******abcd abcd******
SQL> host cls
去掉前后指定的字符
SQL> --trim:去掉前后指定的字符 SQL> select trim('H' from 'Hello WorldH') from dual;
TRIM('H'FR
----------
ello World
replace:替换 SQL> --replace:替换
SQL> select replace('Hello World','l','*') from dual;
REPLACE('HE
-----------
He**o Wor*d
SQL> host cls
数字函数
SQL> --数值函数
![\]()
四舍五入round SQL> select round(45.926,2) 一,round(45.926,1) 二,round(45.926,0) 三, round(45.926,-1) 四,round(45.926,-2) 五
2 from dual;
一 二 三 四 五
---------- ---------- ---------- ---------- ----------
45.93 45.9 46 50 0
SQL> ed
已写入 file afiedt.buf
截断函数,TRUNC 1 select TRUNC(45.926,2) 一,TRUNC(45.926,1) 二,TRUNC(45.926,0) 三, TRUNC(45.926,-1) 四,TRUNC(45.926,-2) 五
2* from dual
SQL> /
一 二 三 四 五
---------- ---------- ---------- ---------- ----------
45.92 45.9 45 40 0
SQL> host cls
日期函数
SQL> --日期函数
<??http://www.2cto.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KPHA+ICAgICAgICAgICAgT3JhY2xlINbQtcTI1cba0M3K/b7dyrW8yrqs09DBvbj2JiMyMDU0MDs6IMjVxtq6zcqxvOShozwvcD4KPHA+ICAgICAgICAgICAgxKzIz7XEyNXG2iYjMjY2ODQ7yr3KxyBERC1NT04tUlI8YnI+CjwvcD4KCjxwPlNRTCZndDsgc2VsZWN0IHN5c2RhdGUgZnJvbSBkdWFsOzwvcD4KU1lTREFURSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgPGJyPgotLS0tLS0tLS0tLS0tLSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICA8YnI+CjI1LTPUwiAtMTIgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIDxicj4KPGJyPgo8YnI+ClNRTCZndDsgc2VsZWN0IHN5c2RhdGUtMSDX8szsLHN5c2RhdGUgvfHM7CxzeXNkYXRlJiM0MzsxIMP3zOwgZnJvbSBkdWFsOzxicj4KPGJyPgo8YnI+CtfyzOwgICAgICAgICAgIL3xzOwgICAgICAgICAgIMP3zOwgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIDxicj4KLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLS0tLS0gLS0tLS0tLS0tLS0tLS0gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgPGJyPgoyNC0z1MIgLTEyICAgICAyNS0z1MIgLTEyICAgICAyNi0z1MIgLTEyICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICA8YnI+Cjxicj4KPGJyPgpTUUwmZ3Q7IGhvc3QgY2xzPGJyPgo8YnI+CjxoND601sLUvMbL47Dst6jI1cbasunRrzwvaDQ+CqOoCiAgICAgICAgINTayNXG2snPvNPJz7vyvPXIpdK7uPbK/dfWveG5+8jUzqrI1cbaoaMKICAgICAgICAgwb249sjVxtrP4Lz1t7W72MjVxtrWrrzkz+Cy7rXEzOzK/aGjCiAgICAgICAgIL/J0tTTw8r919az/TI0wLTP8sjVxtrW0LzTyc+78rz1yKXQocqxoaMKo6kKCjxwPlNRTCZndDsgLS2y6dGv1LG5pLXEuaTB5DrM7CDQx8baINTCIMTqPC9wPgpTUUwmZ3Q7IHNlbGVjdCBlbmFtZSwoc3lzZGF0ZS1oaXJlZGF0ZSkgzOwsKHN5c2RhdGUtaGlyZWRhdGUpLzcg0MfG2iwoc3lzZGF0ZS1oaXJlZGF0ZSkvMzAg1MIsKHN5c2RhdGUtaGlyZWRhdGUpLzM2NSDE6jxicj4KICAyICBmcm9tIGVtcDs8YnI+Cjxicj4KPGJyPgpFTkFNRSAgICAgICAgICAgIMzsICAgICAgINDHxtogICAgICAgICDUwiAgICAgICAgIMTqICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICA8YnI+Ci0tLS0tLS0tIC0tLS0tLS0tLS0gLS0tLS0tLS0tLSAtLS0tLS0tLS0tIC0tLS0tLS0tLS0gICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIDxicj4KU01JVEggICAgMTE0MjEuNjAxNSAxNjMxLjY1NzM2ICAzODAuNzIwMDUgMzEuMjkyMDU4OSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgPGJyPgpBTExFTiAgICAxMTM1Ni42MDE1IDE2MjIuMzcxNjQgMzc4LjU1MzM4MyAzMS4xMTM5NzY3ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICA8YnI+CldBUkQgICAgIDExMzU0LjYwMTUgMTYyMi4wODU5MyAzNzguNDg2NzE3IDMxLjEwODQ5NzMgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIDxicj4KSk9ORVMgICAgMTEzMTUuNjAxNSAgMTYxNi41MTQ1IDM3Ny4xODY3MTcgIDMxLjAwMTY0OCAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgPGJyPgpNQVJUSU4gICAxMTEzNi42MDE1IDE1OTAuOTQzMDcgIDM3MS4yMjAwNSAgMzAuNTExMjM3ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICA8YnI+CkJMQUtFICAgIDExMjg2LjYwMTUgMTYxMi4zNzE2NCAgMzc2LjIyMDA1IDMwLjkyMjE5NTkgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIDxicj4KQ0xBUksgICAgMTEyNDcuNjAxNSAxNjA2LjgwMDIxICAzNzQuOTIwMDUgMzAuODE1MzQ2NiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgPGJyPgpTQ09UVCAgICAgOTAyMi42MDE1IDEyODguOTQzMDcgMzAwLjc1MzM4MyAyNC43MTk0NTYyICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICA8YnI+CktJTkcgICAgIDExMDg2LjYwMTUgMTU4My44MDAyMSAzNjkuNTUzMzgzIDMwLjM3NDI1MDcgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIDxicj4KVFVSTkVSICAgMTExNTYuNjAxNSAxNTkzLjgwMDIxIDM3MS44ODY3MTcgMzAuNTY2MDMxNSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgPGJyPgpBREFNUyAgICAgOTAyMi42MDE1IDEyODguOTQzMDcgMzAwLjc1MzM4MyAyNC43MTk0NTYyICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICA8YnI+CkpBTUVTICAgIDExMDcwLjYwMTUgIDE1ODEuNTE0NSAgMzY5LjAyMDA1IDMwLjMzMDQxNTEgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIDxicj4KRk9SRCAgICAgMTEwNzAuNjAxNSAgMTU4MS41MTQ1ICAzNjkuMDIwMDUgMzAuMzMwNDE1MSAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgPGJyPgpNSUxMRVIgICAxMTAxOS42MDE1IDE1NzQuMjI4NzkgIDM2Ny4zMjAwNSAzMC4xOTA2ODkxICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICA8YnI+Cjxicj4KPGJyPgrS0dGh1PExNNDQoaM8YnI+Cjxicj4KPGJyPgpTUUwmZ3Q7IHNlbGVjdCBzeXNkYXRlJiM0MztoaXJlZGF0ZSBmcm9tIGVtcDs8YnI+CnNlbGVjdCBzeXNkYXRlJiM0MztoaXJlZGF0ZSBmcm9tIGVtcDxicj4KICAgICAgICAgICAgICAqPGJyPgq12iAxINDQs/bP1rTtzvM6IDxicj4KT1JBLTAwOTc1OiCyu9TK0O3I1cbaICYjNDM7IMjVxtogPGJyPgo8YnI+Cjxicj4KCjxoND7A+9PDb3JhY2xluq/K/cC0vqvIt7Lp0a/I1cbaPC9oND4KPGltZyBzcmM9"" alt="\">
SQL> host cls
SQL> --使用months_between计算工龄
SQL> select ename,(sysdate-hiredate)/30 方式一,months_between(sysdate,hiredate) 方式二
2 from emp;
ENAME 方式一 方式二
-------- ---------- ----------
SMITH 380.720133 375.277548
ALLEN 378.553466 373.180774
WARD 378.4868 373.116258
JONES 377.1868 371.761419
MARTIN 371.220133 365.922709
BLAKE 376.220133 370.793677
CLARK 374.920133 369.535613
SCOTT 300.753466 296.40658
KING 369.553466 364.277548
TURNER 371.8868 366.567871
ADAMS 300.753466 296.40658
JAMES 369.020133 363.729161
FORD 369.020133 363.729161
MILLER 367.320133 362.084
已选择14行。
SQL> --add_months:加上若干个月
SQL> select add_months(sysdate,123) from dual;
ADD_MONTHS(SYS
--------------
25-6月 -22
SQL> select last_day(sysdate) from dual; 某月最后一天
LAST_DAY(SYSDA
--------------
31-3月 -12
SQL> select next_day(sysdate,"星期日') from dual; 下一个星期日
NEXT_DAY(SYSDA
--------------
01-4月 -12
SQL> select next_day(sysdate,'礼拜天') from dual;
select next_day(sysdate,'礼拜天') from dual
*
第 1 行出现错误:
ORA-01846: 周中的日无效
SQL> host cls
SQL> --对日期进行四舍五入
SQL> select round(sysdate,'MONTH'), round(sysdate,'YEAR') from dual;
ROUND(SYSDATE, ROUND(SYSDATE,
-------------- --------------
01-4月 -12 01-1月 -12
转换函数
![\]()
隐式转换
SQL> --隐式数据类型转换前提：被转换对象是可以转换的
![\]()
显式转换：
![\]()
to_char对日期的转换
![\]()
*****************************************************************************************************************************
![\]()
*****************************************************************************************************************************
![\]()
SQL> --2012-3月-25今天是星期日
SQL> --2012-3月-25 14:44:00 今天是星期日
SQL> select to_char(sysdate,"yyyy-mon-dd hh24:mi:mm"今天是" day') from dual;
TO_CHAR(SYSDATE,'YYYY-MON-DDHH24:MI:MM"今
-----------------------------------------
2012-3月 -25 14:46:03今天是 星期日
SQL> host cls
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------- --------- ---------- |