oracle用到的函数

1、substr() --字符串截取

格式1： substr(string string, int a, int b);

格式2：substr(string string, int a) ;

解析：

    格式1：

        1、string 需要截取的字符串

        2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）

        3、b 要截取的字符串的长度

    格式2：

        1、string 需要截取的字符串

        2、a 可以理解为从第a个字符开始截取后面所有的字符串。

2、instr() --字符串查找

格式一：instr( string1, string2 )    // instr(源字符串, 目标字符串)

格式二：instr( string1, string2 , start_position, nth_appearance )   // instr(源字符串, 目标字符串, 起始位置, 匹配序号)

解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。

  注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。

3、decode()

该函数有两种形式：

第一种形式：

含义解释

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)

该函数的含义如下：

IF 条件=值1 THEN

　　　　RETURN(返回值1)

ELSIF 条件=值2 THEN

　　　　RETURN(返回值2)

　　　　......

ELSIF 条件=值n THEN

　　　　RETURN(返回值n)

ELSE

　　　　RETURN(缺省值)

END IF

第二种形式：

decode(字段或字段的运算，值1，值2，值3）

这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3

4、upper()

小写字符转化成大写

5、lower()

大写字符转化成小写

6、Union：

union内部的select 语句必需拥有相同数量的列，列也必须拥有相似的数据类型，同时每条select 语句中的列的顺序必须相同

对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

7、Union All：

对两个结果集进行并集操作，包括重复行，不进行排序；

8、Intersect：

对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

9、Minus：

对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

10、行转列 pivot

用法如下 pivot（聚合函数 for 列名 in（类型））如：

--行转列

select *

from SalesList pivot(

max(salesNum) for shangPin in ( --shangPin 即要转成列的字段

'上衣' as 上衣, --max(salesNum) 此处必须为聚合函数，

'裤子' as 裤子, --in () 对要转成列的每一个值指定一个列名

'袜子' as 袜子,

'帽子' as 帽子

  )

)

where 1 = 1; --这里可以写查询条件，没有可以直接不要where

11、列转行 unpivot

12、nvl

NVL函数的格式如下：NVL(expr1,expr2)

含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

13、nvl2

nvl2(value1,value2,value3)

这个函数的意思是如果value1的值为null 函数返回value3 否则函数返回value2 也就是说函数永远不会返回value1

注意的是参数value2 value3可以是除了LONG类型之外的任意数据类型。

14、NULLIF

NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

15、start with ..connect by prior..（表字段有层级关系）

基本语法--该语法其实是oracle的递归算法：

select 字段 from 表名 where 条件 start with 条件 connect by (条件)

1.START WITH

start with 子句为可选项，用来标识从哪一行开始查找(可以有多个条件)

若该子句被省略，则表示所有满足查询条件的行作为根节点

2.CONNECT BY

connect by后面的条件中包含prior，

条件形式多为prior 字段1=字段2 或 字段1=prior字段2

除了可以使用字段名外还可以使用表达式

特别的：prior在哪边表示从哪边开始查起，即若prior在父节点前从父节点开始向下查询，若在子节点前表示从子节点开始自下而上查询