ORACLE数据库操作简便函数用法

来源：互联网发布：第四十一知乎编辑：程序博客网时间：2024/05/15 13:40

1、DISTINCT用法：

SELECT DISTINCT A.NAME , A.STUDENT_ID , A.SEX FROM TABLE STUDENT

这条SQL语句是从学生表里查询了姓名，学号，性别，但是DISTINCT的查询规则是只要有一个不同，就是符合条件的。

例：源数据查询结果

ID NAME SUTDENT_ID SEX NAME STUDENT_ID SEX

1 张明 101 男张明 101 男

2 张明 102 男张明 102 男

3 张明 101 男李倩 103 女

4 李倩 103 女

2、WMSYS.WM_CONCAT用法：用来连接字符，中间用 , 隔开。

SELECT WMSYS.WM_CONCAT(STUDENT_ID) AS STUDENT_ID FROM STUDENT GROUP BY (NAME)

查询结果：101,102

3、SUBSTR用法：SUBSTR(字符串，起始位置，截取长度)，默认查找顺序从左到右，当起始位置为负数的时候，从右边开始查找。

SELECT SUBSTR('slighthost.com' , 0 , 1) AS M FROM DUAL //返回结果是s ，从字符串的第一个位置开始截取长度为1的字符串。

SELECT SUBSTR('slighthost.com', 1, 1) AS M FROM DUAL //返回结果是s，0和1都表示截取的开始位置为第一个字符。

SELECT SUBSTR('slighthost.com' , 14, 1) AS M FROM DUAL //返回结果是m，从最后一个字符开始截取。

SELECT SUBSTR('slighthost.com' , -1, 1) AS M FROM DUAL //返回结果是m，-1表示从最后一个字符开始截取。

SELECT SUBSTR('slighthost.com' , -8, 3) AS M FROM DUAL //返回结果是hos，-8表示从右到左数的第8个字符开始截取。

4、INSTR用法：INSTR(源字符串，要查找的字符串，从第几个字符开始，要找到第几个匹配的序号)，查找顺序与SUBSTR相同。

SELECT INSTR('collaboration floor','or',1,2) AS M FROM DUAL // 返回结果是18

SELECT INSTR('collaboration floor','or',-1,2) AS M FROM DUAL // 返回结果是7

SELECT SUBSTR('collaboration floor' , INSTR('collaboration floor','or',-1,2) , 3 ) FROM DUAL //返回结果是ora

5、MERGE INTO用法：

在数据操作时，有时可能需要将一个数据表的某些字段添加或更新到另一张数据表里，这时就有一个高效率的SQL语句。

merge into a
using (select b.* from a, b where a.id = b.id and a.qty <> b.qty ) b
on (a.id = b.id) //这条语句是说明两个表拷贝的连接条件
when matched then //如果存在a.id = b.id，那么就更新a.qty = b.qty
update set a.qty = b.qty
when not matched then //如果不存在a.id=b.id，那么就将b中的id、qty添加到a表中
insert (a.id,a.qty)
values(b.id,b.qty)

6、DECODE用法：

主要作用：将查询结果翻译成其他值

SELECT DECODE(columnname , 值1 , 翻译值1 ,值2 , 翻译值2.......值n , 翻译值n, 缺省值) FROM tablename

if(条件 == 值1)

then

return 翻译值1

else if(条件 == 值2)

then

return 翻译值2

..................

else if(条件 == 值n)

then

return 翻译值n

else

return 缺省值

7、NVL函数：

NVL(EXPR1 , EXPR2) 若EXPR1为空值，则返回EXPR2，否则返回EXPR1本身

select monthid ,decode(nal(sale,6000),6000,'yes','no') from table1

8、SIGN函数：

sign(n) 去数字n的符号，大于0返回1，小于0返回-1，等于0返回0

select decode(sign(变量1 - 变量2)，-1，变量1，变量2) from dual -----取较小值。

9、rank() over( partition by xx order by xx ) 的用法：

partition by 用于给结果集分组，如果没有指定那么他把整个结果集作为一个分组。

rank 是在每个分组内部进行排名的。

有的时候会遇到这样的问题，我们需要查询一张表，而且要按照业务排序，需要看到每一个业务的收费前三名是哪些客户。这个时候用rank() over(partition)是一个很不错的选择。比如我需要如下的结果：

地区日期费用产品编号用户编号

290 201202 258 1 s1
290 201202 200 1 s5
290 201202 100 1 s100
290 201202 90 2 s7
290 201202 88 2 s9
290 201202 10 2 s12

原始数据是这样的：

执行了一条SQL语句，就会得到如上结果：

SELECT A.AREA_ID, A.ACCT_MONTH, A.FEE, A.ITEM_ID, A.USER_ID  FROM (SELECT T.AREA_ID,               T.ACCT_MONTH,               T.FEE,               T.ITEM_ID,               T.USER_ID,               RANK() OVER(PARTITION BY T.ITEM_ID ORDER BY T.FEE DESC) RK          FROM TEST T) A WHERE RK < 4;

10、CONVERT / LENGTHB 函数：

ORACLE中查询某一个字段值是否包含中文字符

select * from tmbb t where w <> convert(w , 'US7ASCII' , 'ZHS16GBK') ;

select * from tmbb t where length(t.w) != lengthb( t.w ) ;

11、REPLACE 函数：

ORACLE中替换某一个字段值中的某个特定字符

update tablename set fopers = (case when instr(trim(fopers) , ',,,,') > 0 then replace(fopers , ',,,,' , '') else fopers end) ;
update tablename t set t.fopers = replace(fopers , ',,,,' , '') where instr ( trim( fopers ) , ',,,,')>0 ;

0 0