Oracle DECODE 函数应用示例

来源：互联网发布：mac os 软件下载编辑：程序博客网时间：2024/05/21 11:03

/*Decode函数*/   --Decode函数的原型为: Decode(testValue, if1, then1, if2,then2.....else).   --针对testValue进行测试，若testValue等于if1则返回then1,若testValue等于if2则返回then2,....若都没有返回，刚返回else.   --常见用法是在Oracle中实现行转列(Convert Rows to Columns).

SQL脚本

Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->drop table student;/--1.创建表CREATE TABLE  STUDENT(   SID VARCHAR(10),   SName varchar(30),   sex varchar2(2) DEFAULT '1',   age integer,   address varchar(100),   primary key (SID));/--2.新增数据Declarei integer:=1;v_sql varchar(1000);begini:=1;while i<10 loopv_sql:=' insert into student(sid,SName,sex,age,address) values(:1,:2,:3,:4,:5)';if i<5 then  execute immediate  v_sql  using i,'同学'||i,to_char(MOD(i,2)),18,'hunan';else  execute immediate  v_sql  using i,'同学'||i,to_char(MOD(i,2)),19,'sichuan';end if;commit;i:=i+1;end loop;end;/commit;/--3.调用存储过程--==========================================================================================begin InsertTestData(10);end;//*Decode函数*/   --Decode函数的原型为: Decode(testValue, if1, then1, if2,then2.....else).   --针对testValue进行测试，若testValue等于if1则返回then1,若testValue等于if2则返回then2,....若都没有返回，刚返回else.   --常见用法是在Oracle中实现行转列(Convert Rows to Columns).   --4.用Decode 查询同学们的性别信息SELECT SName,        DECODE (sex,               '1', '男',               '0', '女'              ) AS 性别  FROM STUDENT;  /--5.按性别分组,统计家庭地址在hunan,sichuan 的同学个数SELECT age, SUM (DECODE (address, 'hunan', 1, 0)) AS "湖南省",               SUM (DECODE (address, 'sichuan', 1, 0)) AS "四川省"   FROM student   GROUP BY age;/

运行结果:

转载自: http://www.cnblogs.com/furenjun/archive/2010/08/05/oracleDecode.html

0 0