nvl函数和decode函数的使用

一、nvl函数

        nvl函数是Oracle pl/sql中的一个函数，表达式为nvl(value,replace_val), 它的功能是 ：如果value的值为空则nvl函数返回replace_val的值，否则返回value的值，若value的值不为空则返回value的值。若value和replace_val都为空则返回空值。

                例如下查询语句：select (sum(t.num),1) from t_ user t ，如果sum（t.num）是空值的话就会返回1。

二、decode函数   

        decode函数表达式为decode(列名，值1，表达式1，值2，表达式2,......,值n,表达式n)。decode函数的执行类似于if-else语句，对于上述的decode函数表达式解释如下：

        select   decode(列名，值1，表达式1，值2，表达式2,......,值n,表达式n) from table 

    列名就是table中某一列的名称，在这里将充当条件，即：如果某一行的该列的字段值等于值1则返回表达式1的值，等于值2则返回表达式2的值。

下面举例说明上述函数的具体应用。

        有业务如下所述：在某系统中所有用户在sysuser表中，不同用户属于不同的角色用户表和角色表是通过sysuser表字段sysid和角色表字段id关联的。即，sysuser中sysid字段是引用的角色表id字段的值，比如：市卫生局，镇卫生院，村卫生室，药品供货商。由于市卫生局和镇卫生院属于药品监管单位，相关信息存放在userjd表中。村卫生室信息在表useryy,药品供货商信息在表usergys中。在上述表userjd,useryy,usergys中都有字段mc来表示机构名称。比如：南京市卫生局，xxx医药公司。在用户表sysuser中，有字段groupid表示用户类别。如下： 用户类别 0:系统管理员,1：卫生局 2:卫生院 3：卫生室 4:供货商。即当groupid为1时表示该用户是卫生局角色。

        现在有需求：查询出用户信息，包含用户所属的机构名称。

        分析： ①sysuser和角色表userjd,useryy,usergys的关联方式：sysuser.sysid = userjd.id及sysuser.sysid = useryy.id和                                                            sysuser.sysid=usergys.id

                  ②用户是属于卫生局还是卫生院或者卫生室及供货商是通过sysuser表的groupid字段值来确定的。

                   ③当需要查询用户机构信息需要关联查询角色表，角色表的mc表示机构信息。

                  则有查询语句

1.  select u.id,u.userid,u.username, nvl(jd.mc,nvl(yy.mc,gys.mc)) mc

    from sysuser u

    left join userjd jd

    on u.sysid = jd.id

    left join useryy yy

    on u.sysid = yy.id

    left join usergys gys

    on u.sysid = gys.id

查询结果如下：

这种方法效率较低，所以可以采用下面的方法二。

2.          

由于用户是属于卫生局还是卫生院或者卫生室及供货商是通过sysuser表的groupid字段值来确定的，所以可以使用decode函数根据groupid的值来确定从那张表中查询用户机构信息。

 select u.id,

                 u.userid,

                 u.username,

                 decode(u.groupid,

                        '1',

                        (select jd.mc

                           from userjd jd

                          where jd.id = u.sysid),

                        '2',

                        (select jd.mc

                           from userjd jd

                          where jd.id = u.sysid),

                        '3',

                        (select yy.mc

                           from useryy yy

                          where yy.id = u.sysid),

                        '4',

                        (select gys.mc

                           from usergys gys

                          where gys.id = u.sysid)) mc

                  from sysuser u

    查询结果如下

查询结果一致且正确，效率也大大提高。第一种使用了函数nvl，第二种使用了函数decode.