171208之Oracle总结(二)

1:decode——对case的简化
现假设我们有一个用户表t_user，其中拥有一个表示性别的字段gender，该字段对应的值可能为0、1或者空，其中0表示“男”，1表示“女”，空表示“未填”。

现需要我们在查询t_user的gender时不是显示对应的0和1，而是显示对应的文本，“男”或“女”。很显然，我们可以使用case when else来实现。

对应的SQL语句如下所示：

select case t.gender when 0 then '男' when 1 then '女' else '未填' end as gender from  t_user;  

如果我们是使用的Oracle数据库，那么我们就没必要写复杂的case when语句了，Oracle的decode函数可以帮我们实现同样的功能。

decode(value,if-1,then-1,if-2,then-2,..,if-n,then-n,defaultValue) 

其表示如果value的值为“if-1”则返回“then-1”，如果值为“if-n”则返回“then-n”，如果value的值不在给定的if值里面则将返回默认值defaultValue。

所以使用decode时，上面的逻辑可以简单的转化成如下：

select decode(t.gender,0,'男',1,'女','未填') as gender from t_user;  

很明显，下面的写法比上面的写法要简单很多。

2：使用connect by进行级联查询
connect by可以用于级联查询，常用于对具有树状结构的记录查询某一节点的所有子孙节点或所有祖辈节点。

现假设我们拥有一个菜单表t_menu，其中只有三个字段：id、name和parent_id。它们是具有父子关系的，最顶级的菜单对应的parent_id为0。

如果这个时候我们需要查询“菜单01”以及其下所有的子孙菜单应该怎么办呢？如果使用connect by的话这将会非常简单，使用如下SQL语句就可以达到对应的效果。

select * from t_menu connect by parent_id=prior id start with id=1;  

connect by是需要跟start with一起使用的。connect by后跟的是连接条件，在connect by后接的条件通常都需要使用关键字“prior”，可以简单的把它理解为上一级，所以上述例子中“connect by parent_id=prior id”就表示连接条件为parent_id等于上级的id，查找到下一级记录后又会找parent_id等于下一级记录的id的记录。

而prior对应的最顶层的记录就是通过start with来确定的，start with后接对应的筛选条件，表示最顶层的记录是哪些，最顶层的记录可以有多个，比如我想查找“菜单01”下的子孙菜单，但是不包括“菜单01”本身，那么我就可以使用如下的SQL语句进行查找，此时“start with parent_id=1”对应的记录就会有多条。

select * from t_menu connect by parent_id=prior id start with parent_id=1;  

此外，如果我们想查找“菜单010101”对应的祖辈菜单也非常简单，如下SQL就可以实现该功能，即从“菜单010101”的父菜单（对应id为4）开始查找。

select * from t_menu connect by id=prior parent_id start with id=4;  

3:Oracle之定时任务
我们有时候会这么一个需求：希望数据库能够定时的帮我们做某一件事。比如说我们希望数据库能够定期的把库里面某一张表、或某些表的数据汇总到另外一张汇总表里面。那么这个时候我们就可以通过使用数据库的定期任务来实现这么一个功能。

下面我们来举一个简单的例子，假如我们有如下两张表，t1和t2。
t1及其对应的sequence:

create table t1(a1 integer primary key, a2 varchar2(20), a3 varchar2(20), a4 date);  create sequence seq_t1 start with 1 increment by 1;  

t2及其对应的sequence：

create table t2(a1 integer primary key, a2 varchar2(20), a3 date);  create sequence seq_t2 start with 1 increment by 1;  

我们需要在每天凌晨两点的时候把表t1里面的a2和a4作为表t2里面的a2和a3插到表t2。Oracle的定期任务只是定时的执行某一个任务，但具体是什么任务还需要我们自己来指定。所以首先我们得来建立自己的任务，任务通常都是一个存储过程。

这里我们建立如下存储过程：

create or replace procedure copy_t1_to_t2 as    startTime date := trunc(sysdate - 1); --昨天0点    endTime   date := trunc(sysdate); --今天0点    cursor t1_cursor is      select a2, a4        from t1       where a4 >= startTime         and a4 < endTime; --定义一个游标查询t1昨天的记录  begin    for t in t1_cursor loop      insert into t2 (a1, a2, a3) values (seq_t2.nextval, t.a2, t.a4);--把t1里面的相关数据插到t2中      commit;--插入一条记录commit一次    end loop;  end;  

参考博客：
http://elim.iteye.com/blog/2099875
http://elim.iteye.com/blog/1983109