oracle高级编程札记

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

 

相关内容推荐