CASE语句

语法：

case ::=

case::=
    [ << label >> ]
    CASE expression
    {WHEN expression THEN <statements>} [,...n]
    [ELSE <statements>]
    END CASE;

参数：

expression PLOSCAR标量表达式

<statements> PLOSCAR语句列表

说明：

依次比较CASE和WHEN后表达式的值，如果相等则执行THEN后的statements，如果都不相等则执行ELSE后的statements。

示例:CASE语句

--  清理环境
DROP FUNCTION author_province(varchar(32));
DROP TABLE authors CASCADE;

--创建表
-- create a table
create table authors
(
    title varchar(32), 
    name varchar(32), 
    city varchar(32), 
    age integer
);

-- insert some rows
insert into authors values('engineer', 'zhangsan', 'hangzhou', 18);
insert into authors values('chief', 'lisi', 'shenzhen', 22);
insert into authors values('director', 'wanger', 'xiamen', 19);
insert into authors values('tester', 'zhaowu', 'chengdu', 20);
-- create a ploscar function using a case statement to access the table authors
create or replace function author_province(intitle varchar(32)) 
    return varchar(32) as
DECLARE
    _city varchar(32);
    _province varchar(32);
BEGIN
    select authors.city into _city from authors 
        where authors.title=intitle;
    case _city
        when cast('hangzhou' as varchar(32)) then 
            _province:='zhejiang';
        when cast('shenzhen' as varchar(32)) then 
            _province:='guangdong';
        when cast('xiamen' as varchar(32)) then 
            _province:='fujian';
        else 
            _province:='other';
    end case;
    return _province;
END;

/

select author_province('engineer');
AUTHOR_PROVINCE(varchar)      |
------------------------------
zhejiang                      |
总数目：1

--删除
DROP FUNCTION author_province(varchar(32));
DROP TABLE authors CASCADE;