oracle高级编程笔记

Oracle学习笔记

学习网站：http://school.itzcn.com/special-spid-35.html

1、更新语句：

update test 
set(max_price,proxy_price)=(select max_price,proxy_price from test where id=35668)
where id=35674;
commit;

 2、MERGE语句：可以方便地把多个操作结合成一个，可避免使用多个insert,update以及delete语句。

 eg.

create table person1(
pid number(4),
page number(3)
);
insert into person1 values(1,20);
insert into person1 values(2,21);
insert into person1 values(3,22);
commit;
create table person2(
pid number(4),
page number(3)
);
insert into person2 values(1,100);
insert into person2 values(4,100);
insert into person2 values(5,100);
select * from person1;
select * from person2;
merge into person1 p1
using person2 p2
on(p1.pid=p2.pid)
when matched then
  update set p1.page=p2.page
when not matched then
  insert(pid,page) values(p2.pid,p2.page);

--结果 3 行已合并。

 

3、

 

4、大对象

》LONG，LONG RAW数据类型：

LONG数据类型可以存储2GB以下的字符数据

LONG RAW数据类型可以存储2GB以下的二进制数据

RAW数据类型可以存储4KB的二进制数据。

 

》大对象四种类型：

CLOB 字符LOB类型，用于存储字符数据

NCLOB 国家语言字符集LOB类型，用于存储多字节字符数据，一般用于非英文字符

BLOB 二进制LOB类型，用于存储二进制数据

BFILE 二进制FILE类型，用于存储文件指针。

 

》LOB类型与较早类型相比优点：

1》LOB列最大可以存储128TB的数据，远远超过LONG和LONG RAW列存储的数据量2GB。

2》一个表可以有多个LOB列，但是一个表只能有一个LONG或LONG RAW 列。

3》LOG数据可以随机访问，LONG和LONG RAW数据只可以顺序访问。

CLOB:

create table clob_table(
id number primary key,
clob_column clob not null
);
--使用to_clob()
insert into clob_table(id,clob_column)
values(1,to_clob('这里是clob类型数据'));
select * from clob_table;

 

BLOB:

Bcreate table blob_table(
id number primary key,
blob_column blob not null
);
--to_blob()
insert into blob_table(id,blob_column)
values(1,to_blob('101010111110'));
commit;
select * from blob_table;

 

BFILE:

 

操作大对象APPEND()方法添加字符串，如，src_clob添加到dest_clob后面：

 

大对象COMPARE()比较，如果两个LOB相同则返回0；如果不相同则返回1或-1；如果参数值设置有误或不合理则返回空值。

 

大对象REASE(lob,amount,offset)方法用于删除一个LOB中的数据，删除方式是从指定的偏移量开始，删除指定数量的字符或字节。

 
 大对象FILEGETNAME(bfile,directory,filename)方法用于获取BFILE所指向的外部文件的目录和文件名。

 

大对象LOADCLOBFROMFILE()方法，用于将BFILE所指向的外部文件中的数据加载到CLOB/NCLOB中。加载方式是从指定的偏移量开始，加载指定数量的字节。

 

5、HAVING子句通常与GROUP BY子句一起使用，在完成对分组结果的统计后，可以使用HAVING子句对分组的结果进行进一步的筛选。一个HAVING子句最多可以包含40个表达式。

 

6、USING使用，如果在连接查询时使用了两个表中相同的多个列，那么就可以在USING子句中指定多个列名，形式如下：

select ... from  table1 inner join table2

using(column1,column2);

上述语句相当于下面的语句：

select ...  from table1 inner join table2

on table1.column1=table2.column2

and table1.column2=table2.column2;

 

7、Oracle分页sql示例:

SELECT *
FROM
  (SELECT t2.*,
    rownum rowno
  FROM
    (SELECT t1.*
    FROM
      (SELECT D.ID,
        D.USER_ID,
        D.USER_NAME,
        D.CREATED_AT,
        D.REMARKS
      FROM USER_TEST D
      WHERE 1  =1
      AND D.STATUS = '2'
      ORDER BY D.CREATED_AT DESC
      ) t1
    ) t2
  WHERE rownum <=25
  ) table_alias
WHERE table_alias.rowno >0; 

 MySQL分页SQL示例：

select * from table LIMIT 5,10;//返回6-15行

 SqlServer分页SQL示例：

select TOP 10 * from test order by id asc;

 

8、Oracle分组后，取每组第一条数据。

   row_number() over(partition by X)

--导出所有psam冻结的数据，只取第一条。（分组后取每组第一条数据）
select * 
from (select t.*,pb.*,row_number() over(partition by t.psamId order by t.psamId desc, t.blockingDate desc,t.blockingTime desc ) cn from PayTerminalBlockingTest  t join PayTerminalTest p on(t.psamId=p.psamId) join PayBranchTest pb on(p.branchId=pb.branchId) where  t.blockingFlag='1')  where cn = 1