Oracle decode函数用法与表的行列转换

Oracle 分析函数

Oracle Model子句

Oracle decode函数用法与表的行列转换 

2012-05-17 11:37:51|  分类： Oracle |  标签：decode  |字号大中小 订阅

decode函数的语法格式

格式一：decode(expression,value,result1,result2)

说明：expression是表达式，value是表达式的值，result是显示的结果。对于格式一，通俗的说法为，如果expression的值与value相               等，则显示result1，否则显示result2。

例如：

SQL> select decode(1+2,3,'a','b') as demo1,

  2                 decode(1+2,4,'a','b') as demo2

  3  from dual

  4  /

D D

- -

a b

SQL>   

格式二：decode(expression,value1,result1,value2,result2,.......)

说明：如果expression的值等于value1，则显示result1，等于value2，则显示result2..........

例如：

SQL> select decode(employee_id,100,'emp_id = 100',110,'emp_id = 110',120,'emp_id = 120','default_val')

  2  from employees

  3  /

DECODE(EMPLO

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

emp_id = 100

default_val

default_val

.......................

default_val

emp_id = 110

.......................

emp_id = 120

default_val

通过观察，decode函数与case when语句功能相同。下面我们用decode函数实现表的行列转换。

有了decode函数的用法之后，这个思路也很容易找到，下面一一分解。

示例表：

SQL> create table t

  2  (

  3  sname varchar2(12),

  4  fraction number(3),

  5  subject varchar2(10)

  6  )

  7  tablespace example

  8  /

表已创建。

SQL>

对表时行一些数据的插入，插入后数据如下：

SQL> select sname,fraction,subject

  2  from t

  3  /

SNAME          FRACTION SUBJECT

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

张三                 80 English

张三                 80 Java

张三                 90 database

李四                 81 English

李四                 89 Java

李四                 88 database

已选择6行。

SQL>

转换后，变成如下结构：

STUD_NAME       ENGLISH   DATABASE       JAVA

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

李四                 81         88         89

张三                 80         90         80

有了decode函数思想之后，通过观察，本质上就是把数据进一下变换，就可以达到应用的效果。这步很容易做到，如下：

SQL> select sname as stud_name,

  2         decode(subject,'English',fraction,null) as English,

  3         decode(subject,'database',fraction,null) as database,

  4         decode(subject,'Java',fraction,null) as Java

  5  from t

  6  /

STUD_NAME       ENGLISH   DATABASE       JAVA

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

张三                 80

张三                                       80

张三                            90

李四                 81

李四                                       89

李四                            88

已选择6行。

SQL>

通过查询后的值发现，数据较为零碎，需要进行一下整理。再观察发现，同一姓名下的同一下科目的成绩只有一个值（实际情况也是这样的），这个时候我们可以想到使用sum函数对成绩进行求和运算，以姓名分组，即可。如下：

SQL> select sname as stud_name,

  2         sum(decode(subject,'English',fraction,null)) as English,

  3         sum(decode(subject,'database',fraction,null)) as database,

  4         sum(decode(subject,'Java',fraction,null)) as Java

  5  from t

  6  group by sname

  7  /

STUD_NAME       ENGLISH   DATABASE       JAVA

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

李四                 81         88         89

张三                 80         90         80

SQL>

刚才说到case when语句，下面我们使用case when语句来实现，如下：

SQL> select sname as stud_name,

  2        sum(case

  3                when subject = 'English' then

  4                    fraction

  5                else

  6                    null

  7            end

  8            ) as English,

  9        sum(case

10                when subject = 'database' then

11                    fraction

12                else

13                     null

14             end

15            ) as database,

16        sum(case

17                when subject = 'Java' then

18                    fraction

19                else

20                    null

21            end

22            ) as Java

23  from t

24  group by sname

25  /

STUD_NAME       ENGLISH   DATABASE       JAVA

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

李四                 81         88         89

张三                 80         90         80

SQL>

在11g中，还可以使用pivot函数方便实现，这里没有列出，有感兴趣的可以给我留言或查阅相关资料。

对于上述问题，我们可以使用存储过程来实现，如下：

SQL> create or replace procedure go_class(p_cur in out sys_refcursor)

  2  as

  3     l_query_sql varchar2(4000) := 'select sname ';

  4  begin

  5     for t_rec in (select distinct subject from t)

  6     loop

  7        l_query_sql := l_query_sql ||

  8           replace(',sum(decode(subject,''XX$'',fraction,null)) XX$',

  9                   'XX$',

10                   dbms_assert.simple_sql_name(t_rec.subject) );

11     end loop;

12

13     l_query_sql := l_query_sql || ' from t group by sname';

14

15     if not p_cur%isopen

16     then

17        open p_cur for l_query_sql;

18     end if;

19  end go_class;

20  /

过程已创建。

SQL>

SQL> alter procedure go_class compile

  2  /

过程已更改。

SQL>

说明：我们在上述过程中采用游标变量来作为参数，该参数为in out模式，表示可读写。最后SQL以游标的形式写出。我们使用

dbms_assert.simple_sql_name标识一个有效的SQL名称。关于dbms_assert包入SQL注入的问题，这里没有列出，感兴趣的请查阅相关资料或给我留言。

下面我们执行该过程，并观察其结果：

SQL> variable x refcursor

SQL> exec go_class(:x);

PL/SQL 过程已成功完成。

SQL> print x;

SNAME           ENGLISH       JAVA   DATABASE

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

李四                 81         89         88

张三                 80         80         90

SQL>

 

转： http://blog.163.com/donfang_jianping/blog/static/1364739512012417103134640/