oracle函数(一)——字符操作、数字操作、日期操作

 

一、函数                            (说明：本文中用到的表都是oracle提供的scott用户下的表)

    使用函数的目的是为了操作数据，将输入的变量处理，返回一个结果。变量可以有好多，传入的变量可以是列的值，也可以是表达式。

    函数可以嵌套。内层函数的结果是外层函数的变量。

    oracle函数可以分为当行函数和多行函数：

  单行函数：每一行都有一个返回值，但可以有多个变量。

  多行函数：多行有一个返回值。

 

二、单行函数的分类

  a.字符操作函数

  b.数字操作函数

  c.日期操作函数

  d.数据类型转换函数

  e.综合数据类型函数

 

三、字符操作函数

 

大小写操作函数：lower(小写), upper(大写), initcap(首字母大写)

字符串操作函数：concat, length, substr, instr, trim, replace, lpad, rpad

 

1.大小写操作函数：

select lower(ename), upper(ename),initcap(ename) from emp;
select lower('mf TR'),upper('mf TR'),initcap('mf TR') from dual;
//dual 是虚拟表

  输出：mf tr  MF TR  Mf Tr

2.concat函数：连接，将两个字符串连接到一起

select ename,job,concat(ename,job) from emp;

 

 注：oracle的concat()函数只支持两个参数，不支持两个以上字符串的拼接，如果要进行多个字符串的拼接的话，可以使用多个concat()函数嵌套使用。

例如：

select ename,job, concat(concat(ename,' _ '),job) from emp;

 

 除此之外，oracle中字符串的连接还可以使用连接符“||”，支持多个参数连接。

select '员工：' || ename || ',工作是：' || job from emp;

 3.length：字符串的长度

select length('张三') from dual;    //按照字
select lengthb('张三') from dual;    //按字节
select lengthc('张三') from dual;    //unicode的长度

 输出：2 ， 4，  2。

4.substr:截取字符串。

substr(字符串,m,n)，+m表示从左向右第m位开始，-m表示从右向左第m位开始(m大于0)，n表示取几位。如果n没有值就是一直到字符串的结尾。

select ename,substr(ename,1,3) "first",substr(ename,-2)"last" from emp;

 输出：SMITH  SMI  TH

5.instr:查找子字符串在父字符串中的位置

instr(父字符串,子字符串)：返回子字符在父字符串的位置。0表示没有在父串找到该子串。

select ename,instr(ename,'A') "A在第几位" from emp;

6.trim:截掉字符串

select trim(leading 'a' from 'aaabbabaa') from dual;
//截掉连续的前置的a
select trim(trailing 'a' from 'aaabbabaa') from dual;
//截掉连续的后置的a
select trim(both 'a' from 'aaabbabaa') from dual;
//截掉连续的前置和后置的a
select trim('a' from 'aaaabbbaa') from dual;
//如果没有说明是前置还是后置就是both全截掉

 注：trim函数是截掉头或尾连续的字符，一般我们的用途是去掉头和尾的空格。

7.lpad,rpad：左铺垫，右铺垫

select lpad(1232,6,'*'),rpad('aaccbbdd',6,'@') from dual;

 输出：**1232  aaccbb

6代表总共铺垫到多少位，* @ 是要铺垫的字符串。

注：lpad左铺垫，rpad右铺垫，一般用于美化输出结果。如果位数不足，按照截取后的结果显示，不报错。

8.replace:替换

replace(字符串,'A','B')将字符串中A字符替换为B

select replace(ename,'A','*') from emp;

 

四、数字操作函数

 

round(数字,n)：四舍五入,n表示小数点后几位，0可以不写，表示取整，负数表示小数点前几位，下同

trunc(数字,n)：全部舍弃

ceil(数字)：取整，上进位

abs(数字)：取绝对值

mod(数字A,数字B)：取A除以B的余数

 

select round(45.923,2),round(45.923),round(45.234,-1) from dual;

 输出：45.92  46 50

select trunc(45.92333,2),trunc(45.92666,2),trunc(45.9247,-2) from dual;

 输出：45.92  45.92  0

select ceil(45.0002),ceil(45.666) from dual;

 输出：46  46

select abs(-23.00) from dual;

 输出：23

select mod(5,2) from dual;

输出：1 

 

五、日期操作函数

sysdate:当前数据库的日期

select sysdate from dual;

 重新设置时间显示格式

alter session set NLS_DATE_FROMAT='yyyy/mm/dd hh24:mi:ss';

 截取时间的年月日

select hiredate,trunc(hiredate,'yyyy'),trunc(hiredate,'year') from emp;

 

 注：数字的进位和截取是以小数点为中心，我们取小数点前或后的值，而日期的进位和截取是以年、月、日、时、分、秒为中心。