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 bSQL>
格式二：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 = 100default_valdefault_val.......................default_valemp_id = 110.......................emp_id = 120default_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(case10 when subject = 'database' then11 fraction12 else13 null14 end15 ) as database,16 sum(case17 when subject = 'Java' then18 fraction19 else20 null21 end22 ) as Java23 from t24 group by sname25 /
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;1213 l_query_sql := l_query_sql || ' from t group by sname';1415 if not p_cur%isopen16 then17 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 refcursorSQL> 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/