db2case语句_DB2 CASE/IF 条件控制语句

一、CASE(可用于基本SQL和过程中)

1、删除表、创建表、注释、插入数据操作。

DROP TABLE  AA_WEEK;

CREATE TABLE AA_WEEK(

CODE SMALLINT NOT NULL,

EN_NAME VARCHAR(20) NOT NULL,

ABB VARCHAR(20),

CONSTRAINT P_AA_WEEK_CODE PRIMARY KEY (CODE)

);

COMMENT ON TABLE AA_WEEK IS

'星期';

COMMENT ON AA_WEEK

(CODE IS '星期代码',

EN_NAME IS '英文星期名',

ABB IS '英文缩写'

);

INSERT INTO AA_WEEK VALUES

(1,'Monday','Mon'),

(2,'Tuesday','Tues'),

(3,'Wednesday','Wed'),

(4,'Thursday','Thurs'),

(5,'Friday','Friday'),

(6,'Saturday','Sat'),

(7,'Sunday','Sun');

COMMIT;

2、CASE查询

两种语法模式：

(1):CASE

WHEN 条件  THEN 结果1

ELSE            结果2

END

(2):CASE 表达式1

WHEN 表达式2   THEN 结果1

ELSE            结果2

END

-- 单值表达式作为条件

SELECT

CASE CODE

WHEN 1 THEN '一'

WHEN 2 THEN '二'

WHEN 3 THEN '三'

WHEN 4 THEN '四'

WHEN 5 THEN '五'

WHEN 6 THEN '六'

WHEN 7 THEN '七'

ELSE 'CODE错误！'

END AS CN_CODE,

EN_NAME

FROM AA_WEEK;

CN_CODE EN_NAME

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

'一' 'Monday'

'二' 'Tuesday'

'三' 'Wednesday'

'四' 'Thursday'

'五' 'Friday'

'六' 'Saturday'

'七' 'Sunday'

'CODE错误！' 'HAHA'

-- 条件直接写到when后面

SELECT

CASE

WHEN CODE = 1 THEN '一'

WHEN CODE = 2 THEN '二'

WHEN CODE = 3 THEN '三'

WHEN CODE = 4 THEN '四'

WHEN CODE = 5 THEN '五'

WHEN CODE = 6 THEN '六'

WHEN CODE = 7 THEN '七'

ELSE 'CODE错误！'

END AS CN_CODE,

EN_NAME

FROM AA_WEEK;

CN_CODE EN_NAME

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

'一' 'Monday'

'二' 'Tuesday'

'三' 'Wednesday'

'四' 'Thursday'

'五' 'Friday'

'六' 'Saturday'

'七' 'Sunday'

'CODE错误！' 'HAHA'

╭──╮╭──╮

╰╮╭╯│╭─╯

││　│╰─╮

││　│╭─╯

╭╯╰╮││

╰──╯╰╯

二、IF语句格式(仅仅限于DB2过程、触发器、函数中使用)

IF (条件)

THEN 语句;

ELSEIF (条件)

THEN 语句;

ELSEIF (条件)

THEN 语句;

... ...

ELSE

语句;

END IF;

例子以后用的时候再写上。