DB2 CASE/IF 条件控制语句
╭──╮╭──╮╭──╮╭──╮
│╭─╯│╭╮││╭─╯│╭─╯
││ │╰╯││╰─╮│╰─╮
││ │╭╮│╰─╮││╭─╯
│╰─╮││││╭─╯││╰─╮
╰──╯╰╯╰╯╰──╯╰──╯
一、CASE（可用于基本SQL和过程中）
│╭─╯│╭╮││╭─╯│╭─╯
││ │╰╯││╰─╮│╰─╮
││ │╭╮│╰─╮││╭─╯
│╰─╮││││╭─╯││╰─╮
╰──╯╰╯╰╯╰──╯╰──╯
一、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
'星期';
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 '英文缩写'
);
(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;
(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
(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;
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'
--------------------
'一' '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'
--------------------
'一' 'Monday'
'二' 'Tuesday'
'三' 'Wednesday'
'四' 'Thursday'
'五' 'Friday'
'六' 'Saturday'
'七' 'Sunday'
'CODE错误！' 'HAHA'
╭──╮╭──╮
╰╮╭╯│╭─╯
││ │╰─╮
││ │╭─╯
╭╯╰╮││
╰──╯╰╯
二、IF语句格式(仅仅限于DB2过程、触发器、函数中使用)
IF (条件)
THEN 语句;
ELSEIF (条件)
THEN 语句;
ELSEIF (条件)
THEN 语句;
... ...
ELSE
语句;
END IF;
THEN 语句;
ELSEIF (条件)
THEN 语句;
ELSEIF (条件)
THEN 语句;
... ...
ELSE
语句;
END IF;
例子以后用的时候再写上。
本文转自 leizhimin 51CTO博客，原文链接：http://blog.51cto.com/lavasoft/37298，如需转载请自行联系原作者
