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>