第五章++++常用函数

1、第五章第五章 常用函数常用函数计算机科学与工程学院计算机科学与工程学院 2013年年3月月Dual Dual 表表：是一个虚拟表，用于查询那些不属于实际表里的内容。：是一个虚拟表，用于查询那些不属于实际表里的内容。例例： select 3+3 from dual ;select 3+3 from dual ;例例：查看当前用户：查看当前用户 select user from dual;select user from dual;例例：查看当前日期、时间：查看当前日期、时间 select sysdate from dual;select sysdate from dual; OracleOrac

2、le内置函数（内置函数（1 1）OracleOracle内置函数（内置函数（2 2） 字符函数字符函数1 LOWER（char） 返回字符串返回字符串,并将所有的字符小写。并将所有的字符小写。 例例：select lower(AaBbCcDd)AaBbCcDd from dual; 例例：将：将emp表的员工姓名全部转换为小写表的员工姓名全部转换为小写 select lower(eNAME) FROM scott.EMP; 例例：查询员工姓名和工资，将：查询员工姓名和工资，将emp表的员工姓名全部小写显示表的员工姓名全部小写显示 select lower(ENAME) , sal from s

3、cott.emp;2 UPPER( char) 返回字符串返回字符串,并将所有的字符大写。并将所有的字符大写。 例例： select upper(AaBbCcDd) from dual; 例：例：将员工名字按大写方式显示将员工名字按大写方式显示 select UPPER(ENAME) FROM scott.EMP;OracleOracle内置函数（内置函数（3 3）字符函数字符函数3 LENGTH ： 返回字符串的长度返回字符串的长度; 例例：显示正好有：显示正好有5个字符的员工姓名个字符的员工姓名 select * from scott.emp where length(ename) =5;

4、注意注意：ename字段为字段为varchar（10）类型，）类型，length(ename) 是实际长度而不是定是实际长度而不是定义时的长度，所以可以查询到实际长度为义时的长度，所以可以查询到实际长度为5的名字。的名字。 例例：显示正好有：显示正好有10个字符的员工姓名个字符的员工姓名 select * from scott.emp where length(ename) =10;4 SUBSTR(string,start,count) ：取子字符串：取子字符串,从从start开始开始,取取count个个 例例： select substr3,8) from dua

5、l; 例例：显示所有员工姓名的前：显示所有员工姓名的前3个字母个字母 select substr(ename ,1,3) from scott.emp; OracleOracle内置函数（内置函数（4 4）字符函数字符函数4 4 SUBSTR(string,start,count) SUBSTR(string,start,count) （续）（续）例例：以首字母大写方式显示员工的姓名以首字母大写方式显示员工的姓名 完成首字母大写完成首字母大写 select upper(substr(ename,1,1) from scott.emp ;select upper(substr(ename,1,1

6、) from scott.emp ; 完成后面字母小写完成后面字母小写 select lower(substr(ename,2,length(ename)-1) from select lower(substr(ename,2,length(ename)-1) from scott.emp ;scott.emp ; 合并合并select upper(substr(ename,1,1) select upper(substr(ename,1,1) | lower(substr(ename,2,length(ename)-1) from scott.emp ;lower(substr(ename,

7、2,length(ename)-1) from scott.emp ; OracleOracle内置函数（内置函数（5 5）字符函数字符函数4 SUBSTR(string,start,count) （续）（续）例：例：以首字母小写方式显示员工的姓名以首字母小写方式显示员工的姓名 完成首字母小写完成首字母小写 select lower (substr(ename,1,1) from scott.emp; 完成后面字母大写完成后面字母大写 select upper (substr(ename,2,length(ename)-1) from scott.emp; 合并合并select lower(s

8、ubstr(ename,1,1) | upper(substr(ename,2,length(ename)-1) from scott.emp;OracleOracle内置函数（内置函数（5 5）例：例：显示正好有显示正好有5 5个字符的员工的姓名个字符的员工的姓名 select ename from scott.emp where length(ename)=5;select ename from scott.emp where length(ename)=5;例：例：显示所有员工姓名前显示所有员工姓名前3 3个字母个字母 select substr(ename,1,3) from scot

9、t.emp;select substr(ename,1,3) from scott.emp;OracleOracle内置函数（内置函数（6 6）字符函数字符函数5 replace(char, search_string , replace_string) search_string:要查找的字符串；要查找的字符串； replace_string：用于替换用于替换search_string的字符串。的字符串。例：例： 显示所有员工姓名，用显示所有员工姓名，用“我是老鼠我是老鼠”替换所有替换所有A select replace( ename , A , 我是老鼠我是老鼠 ) from scott.

10、emp; OracleOracle内置函数（内置函数（7 7） 数学函数数学函数 数学函数的输入参数和返回值的数据类型都是数字类型。数学函数的输入参数和返回值的数据类型都是数字类型。1 round（n,m):用于执行四舍五入运算。如果省略用于执行四舍五入运算。如果省略m,则四舍五入则四舍五入至整数位，如果至整数位，如果m是负数，则四舍五入到小数点前是负数，则四舍五入到小数点前m位，如果位，如果m是是正数，则四舍五入到小数点后正数，则四舍五入到小数点后m位。位。例例：查询：查询ALLEN的年薪的年薪 select (round(sal)+round(comm)*13 FROM EMP WHERE

11、 NAME= ALLEN; 注意：注意：此时此时COMM为空的列，年薪也为空，需用为空的列，年薪也为空，需用NVL函数处理函数处理例例：select round(15.567,2) from dual;select scott.emp.*,(round(sal)+round(nvl(comm,0)*13 FROM scott.EMP ; OracleOracle内置函数（内置函数（8 8）数学函数数学函数2 trunc(n,m):用于截取数字，如果省略用于截取数字，如果省略m,则省去小数部分，如果则省去小数部分，如果m是正数，则截取到小数点后是正数，则截取到小数点后m位位;如果如果m是负数，则

12、截取到小数点是负数，则截取到小数点前前m位。位。 例例： select trunc(45.936) from dual; select trunc(45.936，2) from dual; select trunc(45.936,-1) from dual;3 MOD(n1,n2) 返回一个返回一个n1除以除以n2的余数的余数 例例：select mod(10,3), mod(3,3), mod(2,3) from dual;4 floor(n)：返回小于或等于返回小于或等于n的最大整数的最大整数 例例：select floor(12.56) from dual; OracleOracle内置

13、函数（内置函数（8 8）数学函数数学函数5 ceil（n) ：返回大于或等于返回大于或等于n的最小整数。的最小整数。 例例：select ceil(12.56) from dual; 例例: select ceil(comm) FROM EMP WHERE ename=ALLEN;OracleOracle内置函数（内置函数（8 8）日期函数日期函数 默认情况下，日期格式为默认情况下，日期格式为dd_mod_yy1 sysdate:该函数返回系统时间该函数返回系统时间 select sysdate from dual;2 add_months(d,n):用于在指定日期上增加月数，其中用于在指定日

14、期上增加月数，其中d用于指定日期用于指定日期数据，数据，n可以是任意整数可以是任意整数 例例：返回当前日期后：返回当前日期后5个月的日期个月的日期 select add_months(sysdate ,5) from dual; 例例：显示入职：显示入职300个月以上的员工个月以上的员工 select * from emp where sysdateadd_months(HIREDATE,300);OracleOracle内置函数（内置函数（9 9）2 add_months(d,n)（续）（续） 例：例：显示满显示满10年服务年限的员工的姓名和受雇佣日期年服务年限的员工的姓名和受雇佣日期 se

15、lect * from emp where sysdate=add_months(HIREDATE,10*12);例例：对于每个员工，显示其加入公司的天数：对于每个员工，显示其加入公司的天数 select sysdate-hiredate from emp;3 last_day（d):该函数用于返回指定日期所处月份的最后一天该函数用于返回指定日期所处月份的最后一天 例例：select last_day(sysdate) from dual; 例例：select last_day(4-9月月-11) from dual; 例例：找出各月倒数第三天受雇佣的员工：找出各月倒数第三天受雇佣的员工 se

16、lect hiredate , name from emp where last_day(hiredate)- 2=hiredate;OracleOracle内置函数（内置函数（9 9）4 next_day(d,char) :用于返回指定日期后第一个工作日（由用于返回指定日期后第一个工作日（由char指定指定)所所对应的星期。对应的星期。 例例： Select next_day(sysdate,星期一星期一) from dual;5 months_between (d1,d2)：返回：返回d1和和d2之间相差的月数，如果之间相差的月数，如果d1小于小于d2，则返回负数；如果，则返回负数；如果d

17、1和和d2的天数相同或都是月底，则返回为整数；的天数相同或都是月底，则返回为整数；否则否则oracle 以每月以每月31天为准，计算结果有小数部分天为准，计算结果有小数部分 例例：select months_between(sysdate,10_4月月_10) from dual;6 current_date: 返回会话时区的日期。返回会话时区的日期。 例例：select current_date from dual;7 current_timestamp :用于返回会话时区的日期时间用于返回会话时区的日期时间 例例： select current_timestamp from dual;Or

18、acleOracle内置函数（内置函数（1010）转换函数转换函数 将一种数据类型转换成另一种数据类型，可以是隐式转换，即将一种数据类型转换成另一种数据类型，可以是隐式转换，即由系统自动完成，显示转换则需要调用转换函数。由系统自动完成，显示转换则需要调用转换函数。隐式转换：隐式转换：例：例： create table t1 (id int); insert into t1 values(10) 系统自动将系统自动将10转换为数值转换为数值10例：例：create table t2 (id varchar2(10); insert into t2 values(1); 系统自动将数值系统自动将数

19、值1转换为转换为1OracleOracle内置函数（内置函数（1111）转换函数转换函数1 to_char(d,fmt,nlsparams)：用于将日期值和数字值转变为字符用于将日期值和数字值转变为字符 串，串，d用于指定日期值；用于指定日期值；fmt用于指定日期格式，如果省略，则使用默用于指定日期格式，如果省略，则使用默 认日期显示格式；认日期显示格式；nlsparams用于指定日期显示语言，指定方式为用于指定日期显示语言，指定方式为nls_date_language=language,如果省略，则使用默认的日期语如果省略，则使用默认的日期语言。言。 例：例：select to_char(s

20、ysdate,dd_mon_yy) from dual; 例例：select ename ,to_char(hiredate,yyyy_mm_dd:hh24:mi:ss) from scott.emp ; 例例：select ename ,to_char(hiredate,yyyy_mm_dd hh24:mi:ss), to_char(sal,l99999.99) from scott.emp ; 格式模型格式模型fmt有以下一些元素：有以下一些元素： yy :两位数字的年份，如两位数字的年份，如2003年年03 ； yyyy：4位数字的年份，如位数字的年份，如2003年年2003； year

21、：英文全拼的年份，如：英文全拼的年份，如2003年年two thousand threeOracleOracle内置函数（内置函数（1212）转换函数转换函数1 to_char(d,fmt,nlsparams)(续）续） mm：两位数字的月份，如两位数字的月份，如3月月03； mon: 简拼的月份，如简拼的月份，如3月月MAR(英文），英文），3月月3月（中文）；月（中文）； month:全拼的月份，例如全拼的月份，例如3月月MARCH(英文），英文），3月（中文）；月（中文）； DD： 2位数字的天，如位数字的天，如30号号30； DDSPTH：英文全拼的天，例如：英文全拼的天，例如30号号

22、THIRTIETH DY：工作日的缩写，例如：工作日的缩写，例如2003年年3月月30日日SUNDAY(英文），星期日英文），星期日(中文）；中文）； DAY：工作日的全拼，例如：工作日的全拼，例如2003年年3月月30日日SUNDAY(英文），星期英文），星期日日(中文）；中文）； Hh24: 按照按照24小时格式显示小时，如晚上小时格式显示小时，如晚上8点点20； Hh12:按照按照12小时格式显示小时，如晚上小时格式显示小时，如晚上8点点08； Mi:显示分钟显示分钟 Ss :显示秒；显示秒；OracleOracle内置函数（内置函数（1212）转换函数转换函数2 to_char(n,fmt) : 用于将数字值转换成用于将数字值转换成fmt格式的格式的varchar2类型的值。类型的值。如果省略如果省略fmt，则显示实际的数字值。格式模型的元素如下：，则显示实际的数字值。格式模型的元素如下： 9：显示数字，并且忽略前导：显示数字，并且忽略前导0； 0：显示数字，如果位数不足，则用：显示数字，如果位数不足，则用0补齐；补齐； .：在指定位置显示小数点；：在指定位置显示小数点； ，：在指定位置显示逗号；：在指定位置显示逗号； $ ：在数字前加美元符号；：在数字前加美元