Oracle 使用技巧(转)

1、CASE的用法

在sql语句中

CASE test_value

WHEN expression1 THEN value1

[[WHEN expression2 THEN value2] [...]]

[ELSE default_value]

END

比如1

SELECT last_name, job_id, salary,

     CASE job_id

           WHEN 'IT_PROG' THEN 1.10*salary

           WHEN 'ST_CLERK' THEN 1.15*salary

           WHEN 'SA_REP' THEN 1.20*salary

     ELSE salary END as df

FROM employees

比如2

SELECT

    CASE

        WHEN real_charge>=20000 and real_charge<30000 THEN 5000

        WHEN real_charge>=30000 and real_charge<40000 THEN 9000

        WHEN real_charge>=40000 and real_charge<50000 THEN 10000

        WHEN real_charge>=50000 and real_charge<60000 THEN 14000

        WHEN real_charge>=60000 and real_charge<70000 THEN 18000

        WHEN real_charge>=70000 and real_charge<80000 THEN 19000

        WHEN real_charge>=80000 and real_charge<90000 THEN 24000

        WHEN real_charge>=90000 and real_charge<100000 THEN 27000

        WHEN real_charge>=100000 and real_charge<110000 THEN 27000

        WHEN real_charge>=110000 and real_charge<120000 THEN 29000

        WHEN real_charge>=120000 THEN 36000

        ELSE

            0

        END as dfs

       ,acc_id,user_id,real_charge FROM okcai_jh_charge_200505

 

2、存在就更新，不存在就插入

语法：

MERGE INTO table

USING data_source

ON (condition)

WHEN MATCHED THEN update_clause

WHEN NOT MATCHED THEN insert_clause;

 

例：

MERGE INTO cm_user_credit

USING (select * from dual) ON (user_id =1302514690 )

WHEN MATCHED THEN update set credit_value = 1000

WHEN NOT MATCHED THEN insert (user_id,acc_id,bill_id,plan_id,region_code,credit_value) values(1302514690,1305032158,'13857141218',10070247,'571',1000);

 

 

 

 

3、行列的转换

<1>、固定列数的行列转换

表table

studentID	subject	grade
student1	语文	80
student1	数学	70
student1	英语	60
student2	语文	90
student2	数学	80
student2	英语	100
...

转换为

	语文	数学	英语
student1	80	70	60
student2	90	80	100

...

语句如下：

select studentID,sum(decode(subject,'语文', grade,0))as语文,

sum(decode(subject,'数学', grade,0)) as 数学,

sum(decode(subject,'英语', grade,0)) as 英语

from table

group by studentID

 

<2>、不定列行列转换

表table

c1	c2
1	我
1	是
1	谁
2	知
2	道
3	不

转换为

1	我	是	谁
2	知	道
3	不

这一类型的转换必须借助于PL/SQL来完成，这里给一个例子

CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)

RETURN VARCHAR2

IS

Col_c2 VARCHAR2(4000);

BEGIN

FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP

Col_c2 := Col_c2||cur.c2;

END LOOP;

Col_c2 := rtrim(Col_c2,1);

RETURN Col_c2;

END;

 

SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可

--例子：

create table okcai_1

(

user_id varchar2(10),

user_number varchar2(10),

user_num number(8)

)

user_id user_number user_num

---------------------

1 123 2

1 456 5

1 789 6

2 11 2

2 22 3

2 33 4

2 44 5

2 55 6

2 66 7

2 77 8

3 1234 1

3 5678 2

方式一：

create or replace function get_col(

       p_userId number,

       p_col    number

) return varchar

as

v_tmp varchar2(255);

begin

     select user_number||chr(9)||user_num into v_tmp

     from

     (select user_number,user_num,rownum row_id

      from okcai_1

      where user_id = p_userId) a

     where row_id = p_col;

     return ltrim(v_tmp);

     --return v_tmp;

end;

然后

select distinct user_id,get_col(user_id,1),get_col(user_id,2),get_col(user_id,3) .... from okcai_1

方式二：

create or replace function get_col(

       p_userId number,

       p_col    number

) return varchar

as

v_tmp varchar2(255);

begin

     select user_number||chr(9)||user_num into v_tmp

     from

     (select user_number,user_num,rownum row_id

      from okcai_1

      where user_id = p_userId) a

     where row_id = p_col;

     return ltrim(v_tmp);

     --return v_tmp;

end;

select distinct user_id,get_col_new(user_id) from okcai_1;

 

 

4、怎么实现一条记录根据条件多表插入

可以通过Insert all语句完成，仅仅是一个语句，如：

INSERT ALL

WHEN (id=1) THEN

INTO table_1(id, name)

values(‘id’,’name’)

WHEN (id=2) THEN

INTO table_2(id, name)

values(id,name)

ELSE

INTO table_other(id, name)

values(id, name)

SELECT id,name

FROM a;

如果没有条件的话，则完成每个表的插入，如

INSERT ALL

INTO table_1(id, name)

values(id,name)

INTO table_2(id2, name2)

values(id,name)

INTO table_other(id, name)

values(id, name)

SELECT id,name

FROM a;

 

5、查询从多少行到多少行的记录

可以应用到取排名最前，最后或是正中的记录，

select * from ( select rownum row_id,b.* from

(select a.* from sys_oper a) b )

where row_id between 15 and 20;

 

6、利用group by rollup和cube进行小计和合计。

group by rollup和cube的操作。

<1>下面的语句可以进行总计

select region_code,count(*) from aicbs.acc_woff_notify

group by rollup(region_code);

 

select khbh,sum(qrsl) from xgs_dh_dhmxb t

where fxsj>=to_date('2007-8-10','yyyy-mm-dd hh:mi:ss')

group by rollup(khbh);

<2> 对第1个字段小计，最后合计

select region_code,write_status,count(*) from aicbs.acc_woff_notify

group by rollup(region_code,write_status);

----------------------

   570     0       3

   570     1       2

   570             5   --此处小计了570的记录

   571     0       10

   571     1       2

   571             12 --此处小计了571的记录

   .....

                  100 --此处有总计

 

select khbh,ppbm,sum(qrsl) from xgs_dh_dhmxb t

where fxsj>=to_date('2007-8-10','yyyy-mm-dd hh:mi:ss')

group by rollup(khbh,ppbm);

<3> 对第1个字段小计，再对第2个字段小计，最后合计

select region_code,write_status,count(*) from aicbs.acc_woff_notify

group by cube(region_code,write_status);

		100	--此处有总计
	0	60	--对write_status＝0的小计
	1	39	--对write_status＝1的小计
	3	1	--对write_status＝3的小计
570		5	--此处小计了570的记录
570	0	3
570	1	2
571		12	--此处小计了571的记录
571	0	10
571	1	2

select khbh,ppbm,sum(qrsl) from xgs_dh_dhmxb t

where fxsj>=to_date('2007-8-10','yyyy-mm-dd hh:mi:ss')

group by cube(khbh,ppbm);

 

<5> 复合cube表达式,只做总计

select region_code,count(*) from aicbs.acc_woff_notify

group by cube(region_code);

   

select khbh,sum(qrsl) from xgs_dh_dhmxb t

where fxsj>=to_date('2007-8-10','yyyy-mm-dd hh:mi:ss')

group by cube(khbh);

<6>下面的语句可以按照rollup不同的字段进行小计

select region_code,write_status,count(*) from aicbs.acc_woff_notify

group by region_code,rollup(write_status);

 

select khbh,ppbm,sum(qrsl) from xgs_dh_dhmxb t

where fxsj>=to_date('2007-8-10','yyyy-mm-dd hh:mi:ss')

group by khbh,rollup(ppbm);

没有合计

 

 

 

7、删除重复行

假设表名为Tbl，表中有三列col1，col2，col3，

1、通过创建临时表

可以把数据先导入到一个临时表中，然后删除原表的数据，再把数据导回原表，SQL语句如下：

creat table tbl_tmp as select distinct * from tbl;

truncate table tbl;//清空表记录

insert into tbl select * from tbl_tmp;//将临时表中的数据插回来。

 

这种方法可以实现需求，但是很明显，对于一个千万级记录的表，这种方法很慢，在生产系统中，这会给系统带来很大的开销，不可行。

2、利用rowid

在oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列的内容都相同，但rowid不会相同。

SQL语句如下：

delete from tbl where rowid in (select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2)

 

如果已经知道每条记录只有一条重复的，这个sql语句适用。但是如果每条记录的重复记录有N条，这个N是未知的，就要考虑适用下面这种方法了。

3、利用max或min函数

这里也要使用rowid，与上面不同的是结合max或min函数来实现。SQL语句如下

delete from tbl a where rowid not in (select max(b.rowid) from tbl b

where a.col1=b.col1 and a.col2 = b.col2);//这里max使用min也可以

或者用下面的语句

delete from tbl a where rowid<(select max(b.rowid) from tbl b

where a.col1=b.col1 and a.col2 = b.col2);//这里如果把max换成min的话，前面的where子句中需要把"<"改为">"

跟上面的方法思路基本是一样的，不过使用了group by，减少了显性的比较条件，提高效率。SQL语句如下：

delete from tbl where rowid not in (select max(rowid) from tbl t group by t.col1, t.col2);

 

 

8、少用“%”

select xqbh,zybh,zyxm from sys_zyxx t where xqbh like'03%';

 

select xqbh,zybh,zyxm from sys_zyxx t where substr(xqbh,1,2)='03';

 

 

 

9、树形查询

create table test_zj(

bm     number(8),

bmmc   varchar2(20),

sjbm   number(8)

);

insert into test_zj values(1,'aaa',0);

insert into test_zj values(11,'aaa1',1);

insert into test_zj values(121,'aaa21',12);

insert into test_zj values(111,'aaa11',11);

insert into test_zj values(112,'aaa12',11);

insert into test_zj values(122,'aaa22',12);

insert into test_zj values(123,'aaa23',12);

insert into test_zj values(12,'aaa2',1);

insert into test_zj values(113,'aaa13',11);

 

select * from test_zj;

 

select bm,bmmc,sjbm,level

from test_zj

start with sjbm=0

connect by prior bm = sjbm;

 

select bm,bmmc,sjbm,level

from test_zj

start with sjbm=0

connect by sjbm = prior bm ;

 

 

 

 

10、对CLOB字段进行全文检索

SELECT * FROM A WHERE dbms_lob.instr(a.a,'K',1,1)>0;

 

--------------------------------------------------------------------------------------

 

Where子句中有“！=”将不使用索引 
select account_name from test where amount != 0  (不使用) 
select account_name from test where amount > 0  (使用) 

Where条件中对字段增加处理函数将不使用该列的索引 
select * from emp where to_char(hire_date,'yyyymmdd')='20080411' (不使用) 
select * from emp where hire_date = to_char('20080411','yyyymmdd') (使用) 

避免在索引列上使用IS NULL

select * from emp where dept_code is null  (不使用) 

select * from emp where dept_code is not null  (使用索引全扫描) 
select * from emp where dept_code > 0  (使用) 

通配符% 的使用 
select * from emp where name like '%A'  (不使用索引) 
select * from emp where name like 'A%'  (使用索引) 

可以通过建立反向索引..让 
select * from emp where name like '%A' 
也使用索引

 

最高效的删除重复记录方法 ( 因为使用了ROWID)例子： 
DELETE  FROM  EMP E  WHERE  E.ROWID > (SELECT MIN(X.ROWID) 
FROM  EMP X  WHERE  X.EMP_NO = E.EMP_NO); 

 

在含有子查询的SQL语句中,要特别注意减少对表的查询.例子： 
SELECT  EMP_NO FROM EMP WHERE (GROUP,NAME) = ( SELECT 
COLUMN1,COLUMN2 FROM  TEST WHERE  TEST_ID = 604) 

 

sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行 
在java代码中用到preparedStatement的時候尽量少用连接符“＋”连接字符串

 

避免在索引列上使用计算

　　WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描. 举例：

　　Sql代码

　　--低效：

　　SELECT …FROM DEPT WHERE SAL * 12 > 25000;

　　--高效:

　　SELECT … FROM DEPT WHERE SAL  > 25000/12;

 

用>=替代>

　　Sql代码

　　--如果DEPTNO上有一个索引

　　--高效：

　　SELECT *  FROM EMP  WHERE DEPTNO >=4

　　--低效：

　　SELECT *  FROM EMP   WHERE DEPTNO >3

　　两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录