oracle常用函数

为方便演示示例：首先做如下初始化数据操作。

CREATE TABLE TEST_BANK(

       BANKID varchar2(100),

       BANKNAME varchar2(100)

);

INSERT INTO TEST_BANK(BANKID,BANKNAME) VALUES('1','银行1号');

INSERT INTO TEST_BANK(BANKID,BANKNAME) VALUES('2','银行2号');

 

CREATE TABLE TEST_CUSTOMER(

       CUSTOMERID varchar2(100),

       CUSTOMERNAME varchar2(100)

);

INSERT INTO TEST_CUSTOMER(CUSTOMERID,CUSTOMERNAME) VALUES('003','张三');

INSERT INTO TEST_CUSTOMER(CUSTOMERID,CUSTOMERNAME) VALUES('004','李四');

 

CREATE TABLE TEST_CUSTOMER2(

       CUSTOMERID varchar2(100),

       CUSTOMERNAME varchar2(100)

);

INSERT INTO TEST_CUSTOMER2(CUSTOMERID,CUSTOMERNAME) VALUES('003','张三');

INSERT INTO TEST_CUSTOMER2(CUSTOMERID,CUSTOMERNAME) VALUES('005','王五');

COMMIT;

 

执行完数据初始化后，查询数据如下所示：

注：以下每一个知识点都是以此数据为基础进行演示，部分演示示例会对表中数据进行修改。但是再结束后会手动将数据恢复至当前内容。即每一个演示示例彼此之间都没有任何数据联系。可单独拿出根据初始数据执行并查看结果。

 

1. MINUS（差集）

示例代码：SELECT * FROM TEST_CUSTOMER MINUS SELECT * FROM TEST_CUSTOMER2

查询结果：

1. INTERSECT（交集）

示例代码:SELECT * FROM TEST_CUSTOMER INTERSECT SELECT * FROM TEST_CUSTOMER2

查询结果：

1. UNION/UNION ALL  /Union：去重。/Union all ：不去重

示例代码：SELECT * FROM TEST_CUSTOMER UNION SELECT * FROM TEST_CUSTOMER2;

查询结果：

示例代码：SELECT * FROM TEST_CUSTOMER UNION ALL SELECT * FROM TEST_CUSTOMER2;

查询结果：

1. LISTAGG() WITHIN GROUP()（类比group_concat）将多行合并成一行

示例代码：SELECT INN.CUSTOMERID,INN.CUSTOMERNAME,LISTAGG(INN.CUSTOMERID2,'。我是分割标志呀。') WITHIN GROUP (ORDER BY INN.CUSTOMERID2) AS MERGES

FROM (SELECT C1.*,C2.CUSTOMERID AS CUSTOMERID2,C2.CUSTOMERNAME AS CUSTOMERNAME2 FROM TEST_CUSTOMER C1, TEST_CUSTOMER2 C2) INN

GROUP BY INN.CUSTOMERID,INN.CUSTOMERNAME;

查询结果：

1. MERGE INTO（类比insert on duplicate key）

Merge into 在我现在接触的系统中用的比较多。

基本语法：

MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)

WHEN MATCHED THEN

[UPDATE sql]

WHEN NOT MATCHED THEN

[INSERT sql]

演示示例目标：演示从TEST_CUSTOMER2根据CUSTOMERID 向 TEST_CUSTOMER表中merge数据，若重复了，则更新CUSTOMERNAME,若不重复，则插入数据。

5.1、制造数据从而能看明白张三三覆盖张三:update TEST_CUSTOMER2 SET CUSTOMERNAME = '张三三' WHERE CUSTOMERID='003';

组织好的数据如下：

执行代码:

MERGE INTO TEST_CUSTOMER C1 USING TEST_CUSTOMER2 C2

ON(C1.CUSTOMERID = C2.CUSTOMERID)

WHEN MATCHED THEN

  UPDATE SET CUSTOMERNAME = C2.CUSTOMERNAME

WHEN NOT MATCHED THEN

  INSERT (CUSTOMERID,CUSTOMERNAME) VALUES(C2.CUSTOMERID,C2.CUSTOMERNAME)

执行结果:

 

1. ROWNUM

ROWNUM是一个序列，是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1，第二条为2，依次类 推。如果你用>,>=,=,between...and这些条件，因为从缓冲区或数据文件中得到的第一条记录的rownum为1，则被删除， 接着取下条，可是它的rownum还是1，又被删除，依次类推，便没有了数据。

rowid 与 rownum 虽都被称为伪列，但它们的存在方式是不一样的，rowid 可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中唯一。只要记录没被搬动过，rowid是不变的。rowid 相对于表来说又像表中的一般列，所以以 rowid 为条件就不会有 rownum那些情况发生。

演示示例：

1. ROWID

oracle数据库的表中的每一行数据都有一个唯一的标识符，或者称为rowid，在oracle内部通常就是使用它来访问数据的。

演示示例：

1. CONNECT BY START WITH（递归查询）

select * from table [start with condition1]

connect by [prior] id=parentid

一般用来查找存在父子关系的数据，也就是树形结构的数据；其返还的数据也能够明确的区分出每一层的数据。

start with condition1 是用来限制第一层的数据，或者叫根节点数据；以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据以此类推。

connect by [prior] id=parentid 这部分是用来指明oracle在查找数据时以怎样的一种关系去查找；比如说查找第二层的数据时用第一层数据的id去跟表里面记录的parentid字段进行匹配，如果这个条件成立那么查找出来的数据就是第二层数据，同理查找第三层第四层…等等都是按这样去匹配。

创建数据库支持：

CREATE TABLE TEST_MENU(

       MANUID varchar2(100),

       MANUNAME varchar2(100),

       MANULEVEL NUMBER(4),

       PARENTID varchar2(100)

);

INSERT INTO TEST_MENU(MANUID,MANUNAME,MANULEVEL,PARENTID) VALUES

('1111','一级菜单',1,'0000');

INSERT INTO TEST_MENU(MANUID,MANUNAME,MANULEVEL,PARENTID) VALUES

('2222','二级菜单',2,'1111');

INSERT INTO TEST_MENU(MANUID,MANUNAME,MANULEVEL,PARENTID) VALUES

('3331','三级菜单1',3,'2222');

INSERT INTO TEST_MENU(MANUID,MANUNAME,MANULEVEL,PARENTID) VALUES

('3332','三级菜单2',3,'2222');

INSERT INTO TEST_MENU(MANUID,MANUNAME,MANULEVEL,PARENTID) VALUES

('3333','三级菜单3',3,'2222');

COMMIT;

创建后结果：

执行代码：
SELECT TM.*,LEVEL,CONNECT_BY_ROOT(MANUID)

FROM TEST_MENU TM

START WITH TM.MANUID = '1111'

CONNECT BY PRIOR TM.MANUID = TM.PARENTID

查询结果：

执行代码：

SELECT TM.*,LEVEL,CONNECT_BY_ROOT(MANUID)

FROM TEST_MENU TM

START WITH TM.MANUID = '2222'

CONNECT BY PRIOR TM.MANUID = TM.PARENTID

查询结果：

注意比较两者的start with 条件。查询结果的差别。

上面一直演示的是从根节点向叶节点递归查询。下面演示从叶子节点向根节点递归查询的示例。

执行代码：

SELECT TM.*,LEVEL,CONNECT_BY_ROOT(MANUID)

FROM TEST_MENU TM

START WITH TM.MANUID = '3333'

CONNECT BY TM.MANUID = PRIOR TM.PARENTID
查询结果：

1. NULL值

Null值不计数count。count()，遇到null值时，这条记录不会计算在内；CONCAT(a,null)结果为null等。

影响索引

给java开发带来空指针隐患。

Null值相比not null所占空间更大。

使用！=, NOT IN

自己在对于可空字段进行判断时，要判断not null and 字段!=”” ，更繁琐。

演示环境搭建：

INSERT INTO TEST_CUSTOMER (CUSTOMERID,CUSTOMERNAME) VALUES('007',null);

COMMIT;

先查询一遍演示表中内容

执行脚本：
select COUNT(*),COUNT(CUSTOMERID),COUNT(CUSTOMERNAME),MAX(CUSTOMERNAME),MIN(CUSTOMERNAME),CONCAT('A','B'),CONCAT(null,null) FROM TEST_CUSTOMER;
查询结果：

1. 1、ROW_NUMBER OVER()（同分不同排名）

搭建演示环境：

create table TEST_ROW_NUMBER_OVER(

       id varchar(10) not null,

       name varchar(10) null,

       age varchar(10) null,

       salary int null

);

select * from TEST_ROW_NUMBER_OVER t;

 

insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a',10,8000);

insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a2',11,6500);

insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b',12,13000);

insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b2',13,4500);

insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c',14,3000);

insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c2',15,20000);

insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(4,'d',16,30000);

COMMIT;

搭建后结果：

10.1、一次排序：对查询结果进行排序（无分组）

演示脚本：

select id,name,age,salary,row_number()over(order by salary desc) rn

from TEST_ROW_NUMBER_OVER t;

查询结果:

10.2、根据id分组排序

演示脚本：

select id,name,age,salary,row_number()over(partition by id order by salary desc) rank

from TEST_ROW_NUMBER_OVER t;

查询结果：

1. RANK()同分排名

搭建演示环境：

create table TEST_ROW_NUMBER_OVER(

       id varchar(10) not null,

       name varchar(10) null,

       age varchar(10) null,

       salary int null

);

select * from TEST_ROW_NUMBER_OVER t;

 

insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a',10,8000);

insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(1,'a2',11,6500);

insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b',12,13000);

insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(2,'b2',13,4500);

insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c',14,3000);

insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(3,'c2',15,20000);

insert into TEST_ROW_NUMBER_OVER(id,name,age,salary) values(4,'d',16,30000);

COMMIT;

 

演示脚本：select id,name,age,salary,rank()over(order by id) rn

from TEST_ROW_NUMBER_OVER t ;

查询结果：

 

1. LAG/LEAD

lag 和lead 可以 获取结果集中，按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）；

lag ，lead 分别是向前，向后；

lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值）

准备演示数据：

INSERT INTO TEST_CUSTOMER (CUSTOMERID,CUSTOMERNAME)VALUES('100','100name');

INSERT INTO TEST_CUSTOMER (CUSTOMERID,CUSTOMERNAME)VALUES('101','101name');

INSERT INTO TEST_CUSTOMER (CUSTOMERID,CUSTOMERNAME)VALUES('102','102name');

INSERT INTO TEST_CUSTOMER (CUSTOMERID,CUSTOMERNAME)VALUES('103','103name');

COMMIT;
搭建好的演示数据：

演示向前脚本：
SELECT CUSTOMERID,CUSTOMERNAME,lag(CUSTOMERNAME,1,0) over ( order by CUSTOMERID ) FROM TEST_CUSTOMER;

查询结果：

演示向后脚本：

SELECT CUSTOMERID,CUSTOMERNAME,lead(CUSTOMERNAME,1,0) over ( order by CUSTOMERID ) FROM TEST_CUSTOMER;
查询结果：

1. SUM()OVER()累计排序。

搭建演示环境：

create table test(sales_id varchar2(2),sales varchar2(10),dest varchar2(10),dept varchar2(10),revenue number);

insert into test values('11','smith','hangzhou','市场',1000);

insert into test values('12','smith','wenzhou','市场',2000);

insert into test values('13','allen','wenzhou','渠道',3000);

insert into test values('14','allen','wenzhou','渠道',4000);

insert into test values('15','jekch','shanghai','渠道',2500);

insert into test values('11','smith','hangzhou','市场',1000);

insert into test values('12','smith','wenzhou','市场',2000);

commit;

搭建完毕后：

演示一：统计全部。

测试脚本：

select sales_id,sales,dest,dept, revenue,sum(revenue) over() as 总销售额 from test

查询结果：

 

演示二：根据sales递归统计

测试脚本：

select sales_id,sales,dest,dept,revenue,sum(revenue)over(order by sales)递加销售总额 from test;

查询结果：

演示三：按人统计
测试脚本：

select sales_id,sales,dest,dept,revenue,sum(revenue)over(partition by sales_id) from test
查询结果：