一些ORACLE SQL的小技巧

lltgmt9171

一些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)