Oracle 常用函数以及小例子

          Oracle常用函数

1.Substr(字符串，截取位置，截取长度)(截取函数)

  --截取函数Substr

①select  substr('Hello World',0,1) value from dual; --0是从第一个字符开始截取长度为1的字符

②select  substr('Hello World',1,3) value from dual;--1是从第一个字符开始截取长度为1的字符

③select  substr('Hello World',2,4) value from dual;--2是从第一第二个字符开始截取长度为4的字符

④select  substr('Hello World',-3,3) value from dual;--负数(-i)表示截取的开始位置为字符串右端向左数第i个字符

2.Distinct去除重复函数

  

 执行select DISTINCT(stuname) from student;后为

 

3.Order By 排序

  

执行 select * from student order by stuno desc;

Order by 默认asc（从小到大排序）desc （从大到小排序）

 

4.Null 查询时不能使用”=” 而用is null或者is not null

  

执行 select * from student where stuage is null ;查询年龄为null的数据

 

5.Group By

  

执行select Count(*) from emp group by hiredate;后可以发现有2个一样的

 

6.Having 一般与 Group by 公用的限制语句 他不与单个值有关而是与组有关 执行select Count(*) from emp group by hiredate having count(*)>1;后查询按日期分组并且日期一样的

 

7.inner join on

执行select * from student,result where STUDENT.stuno=result.stuno 其实他就等同于

select * from student  INNER JOIN result on STUDENT.stuno=result.stuno

 

8.ABS 取绝对值的函数

执行select abs（-100） from dual；

 

9.mod 取余函数

  select mod(9,2) from dual;执行后为：

 

当2换为0时，则为9

10.sign 返回数的符号 正数为1 负数为-1，0为0

select sign(-2),sign(0),sign(2) from dual; 执行后为

 

11.cell（n） 返回>= n 的最小整数 要求n必须是十进制的数

Floor（n） 返回<=n的最大整数 要求n 必须是十进制的数

 select ceil(14.1),floor(14.1) from dual;执行后为

 

12.Power(n1，n2)  返回 n1的n2次幂     Exp(n)  返回e的n次幂

执行 select power(3,3),Exp(2) from dual;后为

 

13. nlssort函数的作用是对字段排序，字符串类型的

   按拼音排序：

          Select * from student t order by NLSSORT(t.stuname,'NLS_SORT=SCHINESE_PINYIN_M')

 

 按笔画排序

Select * from student t order by NLSSORT(t.stuname,'NLS_SORT = SCHINESE_STROKE_M')

       

按部首排序：

       Select * from student t order by NLSSORT(t.stuname,'NLS_SORT = SCHINESE_RADICAL_M')

 

14.ASCII（CHAR）返回参数首字母的ASCII值

select ascii('qwer') from dual;

 

15.length(char)返回char类型的长度

select length('qwer') from dual;

 

16.Cast转换类型

select cast('123'as Integer) from dual;

 

17.ROWIDTOCHAR  将ROWID类型的数值rowid转换为其外部的18字符的字符串表示,在oracle7和oracle8之间有些不一样的地方. ROWIDTOCHAR和CHARTOROWID是两个相反的函数. 

   CHARTOROWID  把包含外部格式的ROWID的CHAR或VARCHAR2数值转换为内部的二进制格式.参数string必须是包含外部格式的ROWID的18字符的字符串.oracle7和 oracle8中的外部格式是不同的.CHARTOROWID是ROWIDTOCHAR的反函数. 

select rowid,rowidtochar(rowid) from emp;  

   

select rowid,chartorowid('ssssffffgggghhhhrr'),ename from emp;  

  

18.to_date("要转换的字符串","转换的格式")   两个参数的格式必须匹配，否则会报错。

即按照第二个参数的格式解释第一个参数。

to_char(日期,"转换格式" ) 即把给定的日期按照“转换格式”转换。

select to_char(sysdate,'yy-mm-dd') from dual ;

 

Select to_date('2005-12-25,13:25:59','yyyy-mm-dd,hh24:mi:ss') from dual

   

19.Lvvnl   用于某个语句的where子句中的条件，如果条件为true就返回false；如果条件为UNKNOWN或者false就返回true。该函数不能用于复合条件如AND, OR, or BETWEEN中。

select * from emp where lnnvl(sal<=3000)

 

20.nvl (expr1,expr2)   如果expr1为null 则返回expr2  否则返回expr1  替换时常用 Select  productname，nvl(quantity,0) from productinfo     表示若quantity为NULL返回0否则返回quantity的值

 

select nvl(stuname,'xxxxxx') from student

 

Nvl2(expr1,expr2，expr3)  意思一样当expr1为空返回expr3，不为空返回expr2

 

 select nvl2(stuname,'xxxxxx','yyyyyyyyy') from student