Oracle常见函数汇总

柔情只为你懂 2023-07-12 14:43 142阅读 0赞

1 INITCAP()

假设c1为一字符串.函数INITCAP()是将每个单词的第一个字母大写,其它字母变为小写返回.

单词由空格,控制字符,标点符号等非字母符号限制.

select initcap('hello world') from dual;

结果 Hello World

2 Concat

连接字符串

```select` `concat(``'CSDN'``,``'_yeeXun'``)``from` `dual;`

结果 `CSDN_yeeXun`

3 ||

连接字符串

`select` `'CSDN'` `||` `'_yeeXun'` `from` `dual; 结果 CSDN_yeeXun contact 与 || 区别 CONCAT只能连接两个字符串，||可以连接多个字符串 例如`

`select` `concat(` `'CSDN'` `,` `'_yeeXun'` `,` `'china'` `)` `from` `dual`

`ORA-00909: invalid number` `of` `arguments`

`select` `'CSDN'` `||` `'_yeeXun'` `||` `'china'` `from` `dual; 结果 CSDN_yeeXunchina 当然 contact可以通过在sql语句里边通过多次调用该函数来实现连接多个字符串 例如 select concat(concat('aa','bb'),'cc') from dual;`

4 substr 取得字符串中指定起始位置和长度的字符串 substr( string, start\_position,  \[  length  \]  )  
如:  
![None.gif][] substr( ' This is a test ' ,  **6** ,  **2** ) would  return  ' is '   
![None.gif][] substr( ' This is a test ' ,  **6** ) would  return  ' is a test '   
![None.gif][] substr( ' TechOnTheNet ' ,  \- **3** ,  **3** ) would  return  ' Net '   
![None.gif][] substr( ' TechOnTheNet ' ,  \- **6** ,  **3** ) would  return  ' The '

select substr('Thisisatest', -4, 2) value from dual 结果是  te

select substr('emros',-3,1) value from dual 结果是  r

5 length 函数 函数本身挺简单，在这介绍两个疑点  
问题1 oracle中的函数length(char),为什么可以写成length(123),length('abc')，但是length(123asd)和length(asd)就不行了？  
解答：length(123)=3,是计算123的长度  
length('abc')=3,是计算'abc'的长度  
length(123asd)和length(asd)中,函数将其当做是变量,因为它既不是有效的数字,也不是有效的字符串(如'aaaaaa',带引号的),所以就会出现"标识符无效"等错误。  
问题2 在oracle里出现的,取数据库中一个字符类型列的长度,是个类别字段用的是char(10) bigcata  
如 大类都是两位数 01,02等 类别不多 小类就是0101,0102,0201,0202  
本来我想 select xxx from xxx where length(bigcata ) = 2 没结果  
select bigcata , length(bigcata )from xxx 结果全是10  
01 10  
02 10  
测试了下varchar2类型的 却是好的(bigcata 的类型改成 varchar2(10))  
123aa 5  
aa2 3  
是不是在oracle中 length()函数对char类型无效呢  
解答：先trim（）再取length（）  
select xxx from xxx where length(trim(bigcata) ) = 2  
因为char是固定长度的如果长度不够后面要补空格的  
  
6 instr函数  
**instr( string1, string2, start\_position,nth\_appearance )**

<table> 
 <tbody> 
  <tr> 
   <td align="left"> 
    <div>
      string1 
    </div> </td> 
   <td align="left"> 
    <div>
      源字符串，要在此字符串中查找。 
    </div> </td> 
  </tr> 
  <tr> 
   <td align="left"> 
    <div>
      string2 
    </div> </td> 
   <td align="left"> 
    <div>
      要在string1中查找的字符串 。 
    </div> </td> 
  </tr> 
  <tr> 
   <td align="left"> 
    <div>
      start_position 
    </div> </td> 
   <td align="left"> 
    <div>
      代表string1 的哪个位置开始查找。此参数可选，如果省略默认为1. 字符串索引从1开始。如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。 
    </div> </td> 
  </tr> 
  <tr> 
   <td align="left"> 
    <div>
      nth_appearance 
    </div> </td> 
   <td align="left"> 
    <div>
      代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1.如果为负数系统会报错。 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

注意：  
　　位置索引号从1开始。  
　　如果String2在String1中没有找到，instr函数返回0。  
　　示例：  
　　SELECT instr('syranmo','s') FROM dual; -- 返回 1  
　　SELECT instr('syranmo','ra') FROM dual; -- 返回 3  
　　SELECT instr('syran mo','a',1,2) FROM dual; -- 返回 0  
  
7 lpad函数

**语法格式如下：**

lpad( string, padded\_length, \[ pad\_string \] )

String 准备被填充的字符串；  
padded\_length 填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;  
pad\_string 填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个参数未写，lpad函数将会在string的左边粘贴空格。  
示例一：  
SQL> select lpad('abcde',10,'x') from dual;  
输出 xxxxxabcde  
示例二：  
SQL> select lpad('abcde',10,'oq') from dual;  
输出 oqoqoabcde  
示例三：  
SQL> select lpad('abcde',2) from dual;  
输出 ab  
  
8 rpad函数  
与lpad函数对应，rpad函数从右边对字符 串使用指定的字符进行填充，语法格式与lpad格式相同：  
rpad( string, padded\_length, \[ pad\_string \] )

例如：  
rpad('tech', 7); 将返回'tech '  
rpad('tech', 2); 将返回'te'  
rpad('tech', 8, '0'); 将返回'tech0000'  
rpad('tech on the net', 15, 'z'); 将返回 'tech on the net'  
rpad('tech on the net', 16, 'z'); 将返回 'tech on the netz'  
  
9 round函数  
语法格式：  
ROUND(number, num\_digits)

**number** 必需。要四舍五入的数字。  
**num\_digits** 必需。位数，按此位数对 number 参数进行四舍五入。  
ROUND函数中：

如果 **num\_digits** 大于 0（零），则将数字四舍五入到指定的小数位。

如果 **num\_digits** 等于 0，则将数字四舍五入到最接近的整数。

如果 **num\_digits** 小于 0，则在小数点左侧进行四舍五入。

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      =ROUND(2.15, 1) 
    </div> </td> 
   <td> 
    <div>
      将 2.15 四舍五入到一个小数位 
    </div> </td> 
   <td> 
    <div>
      2.2 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      =ROUND(2.149, 1) 
    </div> </td> 
   <td> 
    <div>
      将 2.149 四舍五入到一个小数位 
    </div> </td> 
   <td> 
    <div>
      2.1 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      =ROUND(-1.475, 2) 
    </div> </td> 
   <td> 
    <div>
      将 -1.475 四舍五入到两个小数位 
    </div> </td> 
   <td> 
    <div>
      -1.48 
    </div> </td> 
  </tr> 
  <tr> 
   <td> 
    <div>
      =ROUND(21.5, -1) 
    </div> </td> 
   <td> 
    <div>
      将 21.5 四舍五入到小数点左侧一位 
    </div> </td> 
   <td> 
    <div>
      20 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

10 trunc 函数  
TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。  
语法格式：  
TRUNC（number\[,decimals\]）

其中：

number 待做截取处理的数值

decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分

下面是该函数的使用情况：

TRUNC（89.985，2）=89.98

TRUNC（89.985）=89

TRUNC（89.985，-1）=80

11 Months\_between函数

MONTHS\_BETWEEN (x, y)用于计算x和y之间有几个月。如果x在日历中比y早，那么MONTHS\_BETWEEN()就返回一个负数。

当x 和 y 之间的月份之差不是整月的时候，可以采用小数表示，例如

SELECT MONTHS\_BETWEEN('25-MAY-2008', '15-JAN-2008')

FROM dual;

MONTHS\_BETWEEN('25-MAY-2008','15-JAN-2008')

4.32258065

months\_between(date1, date2),必须注意的是，date1与date2都为Date类型，不然会出现错误。须用to\_date(‘’,'') 来转换为日期格式，才能参加计算。

例： select months\_between(to\_date('25-05-2009','dd-MM-yyyy'),to\_date('25-01-2008','dd-MM-yyyy')) from dual 输出 16  
  
12 Add\_months 函数  
select add\_months(sysdate,1) from dual  
  
to\_char ,to\_number,to\_date函数在此不再介绍  
举例如下：  
select to\_char(sysdate,'yyyy-mm-dd') from dual;  
select to\_number('13')+to\_number('14') from dual  
Select to\_date(‘20090210’,‘yyyyMMdd’) from dual  
  
13 NVL函数

格式为：

NVL( string1, replace\_with)

功能：如果string1为NULL，则NVL函数返回replace\_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。

注意事项：string1和replace\_with必须为同一数据类型，除非显式的使用TO\_CHAR函数进行类型转换。

14 NVL2()函数

NVL2(expr1,expr2,expr3)

Oracle在NVL()的功能上扩展，提供了NVL2函数。

功能：oracle中常用函数，如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值。

即：NVL2(表达式,不为空设值,为空设值)

15 COALESCE()函数  
依次考察各参数表达式，遇到非null 值即停止并返回该值。  
select empno, ename, sal, comm, coalesce(sal+comm, sal, 0)总收入from emp;

[None.gif]: http://www.cnblogs.com/Images/OutliningIndicators/None.gif