一些ORACLE SQL的小技巧

一些SQL的小技巧

 

工作中总结了一些SQL的技巧，请大家看看

 

1、CASE中使用范围

   我写了这样的一个查询：

SELECT

CASE Avalue

    WHEN Avalue = 0 THEN ‘没数据’

    WHEN Avalue > 100 THEN ‘超出范围’

    WHEN Avalue < 50 THEN ‘太小’

    ELSE ‘其他’

END

FROM ATable

   这个查询是错误的，因为CASE中不能使用比较运算符。但是，我们可以使用一些技巧达到选择范围的作用：

SELECT

CASE Avalue

    WHEN 0 THEN ‘没数据’

    ELSE

    (

        CASE SIGN(100- Avalue)

            WHEN –1 THEN ‘超出范围’

            ELSE

            (

                CASE SIGN(50- Avalue)

                    WHEN 1 THEN ‘太小’

                    ELSE ‘其他’

                END

            )

        END

    )

END

FROM ATable

可以用需要判断范围的数减去判断的值，并取其符号来进行判断。

虽然长了点，但毕竟在一个表达式中实现了，有的情况下可参考使用，本技巧也适用于decode函数。

 

2、使用CASE将同一列的统计分成多列

   有这样一个表：

userid	Number(10)	用户编号
account	Number(10)	用户金额
usertype	Number(5)	用户类型，1普通用户，2 VIP用户

现在用户需要显示这样一个报表

用户编号

普通用户金额

VIP用户金额

 

可以使用CASE将一列显示在多列上：

SELECT

UserID AS “用户编号”,

SUM(CASE usertype WHEN 1 THEN account ELSE 0 END) AS “普通用户金额”,

SUM(CASE usertype WHEN 2 THEN account ELSE 0 END) AS “VIP用户金额”

FROM Atable

GROUP BY UserID

 

3、存储过程中使用临时表

   对于大量的中间数据，使用临时表能大大提高性能。

   注意：临时表和普通表一样，在使用前一定要预先创建，不能动态创建。

--创建临时表

Create Global Temporary Table Temp_UserName

(

       UserName varchar2(100)

) on Commit Preserve Rows;

 

--创建一个索引便于提高查询速度

create index Temp_UserName_IDX1 on Temp_UserName (UserName);

 

   本例中的临时表使用会话级的临时表，每个用户每次连接数据库的时候起作用，用户会话结束后会自动删除数据，而且多个用户之间不会有影响。

--插入数据到临时表：

INSERT INTO Temp_UserName

SELECT UserName

FROM Atable;

 

4、一个用UPDATE和INSERT代替游标的例子

   发现一个存储过程效率很差，发现原来是使用了游标来处理大量数据。

处理的流程大概如下：

·用一个查询定义一个游标

·打开游标

·读一条记录

·判断该记录在A表中是否存在

·存在则更新A表

·不存在则插入记录到A表

·循环读取下一条记录

·关闭游标

 

其实，这样的处理过程用三条SQL语句就能够完成：

INSERT INTO Temp_UserName -- Temp_UserName是一个临时表，这条语句也可以省略

SELECT UserName

FROM User_Info

WHERE –一些过滤条件;

 

--先更新存在的记录

UPDATE A

SET (Field1, Field2) = (SELECT Field3, Field4 FROM User_Info WHERE EXISTS(SELECT 1 FROM User_Info WHERE UserName=A.UserName))

WHERE EXISTS(SELECT 1 FROM Temp_UserName WHERE UserName=A.UserName)

 

--插入不存在的记录

INSERT INTO A (Field1, Field2, UserName)

SELECT u.Field3, u.Field4, UserName

FROM User_Info u LEFT OUTER JOIN A ON u.UserName=A.UserName AND A.UserName IS NULL

WHERE EXISTS(SELECT 1 FROM Temp_UserName WHERE UserName=A.UserName)