oracle控制语句学习二——case流程控制语句

declare
v_community_type_name communitytype.name%type;
begin
select c.name into v_community_type_name 
from communitytype c
where c.community_type_id = '&community_type_id';
case
when v_community_type_name = '电子图书'
then 
dbms_output.put_line('查询到电子图书');
when v_community_type_name = '新书快读'
then 
dbms_output.put_line('查询到新书快读');
else
dbms_output.put_line('查询到其他资源库');
end case;
end;

case when 也可用于查询语句

select 
case
when id is null and parent_id is null
then 'yzhnykxyjs'
when id is null and parent_id is not null
then parent_id
else id
end id,
case
when id is null and parent_id is null
then (select com.name from community com where com.community_id = 'yzhnykxyjs')
when id is null and parent_id is not null
then (select com.name from community com where com.community_id = parent_id)
else (select col.name from collection col where col.collection_id = id)
end name,
case
when id is null and parent_id is null
then '0'
when id is null and parent_id is not null
then 'yzhnykxyjs'
else parent_id
end parent_id,
case
when id is null and parent_id is null
then 'community'
when id is null and parent_id is not null
then 'zsk'
else 'collection'
end object_type,
count
from
(select cc.collection_id id,count(1) count,cc.community_id parent_id
from community com
join community2community c2c on c2c.community_id = com.community_id and c2c.parent_community_id = 'yzhnykxyjs'
join community2collection cc on cc.community_id = c2c.community_id
join collection col on col.collection_id = cc.collection_id and col.withdrawn = 'N'
join collection2item c2i on c2i.collection_id = col.collection_id
join item i on i.item_id = c2i.item_id and i.withdrawn = 'N'
where not exists (select 1 from community2institution c2i where c2i.community_id = com.community_id)
and com.withdrawn = 'N'
group by rollup(cc.community_id,cc.collection_id)) allcom

输出如下:

1 qt_2934 参加会议情况 qt collection 3
2 qt_2935 实验室活动记事 qt collection 2
3 qt_2936 无法归类却有必要保存 qt collection 3
4 qt 其它 yzhnykxyjs zsk 8
5 cgk_2529 发明专利 cgk collection 27
6 cgk_2530 科技成果 cgk collection 23
7 cgk_2727 图书著作 cgk collection 10
8 cgk_3328 品种库 cgk collection 5
9 cgk 成果库 yzhnykxyjs zsk 65
10 xmk_2729 项目经费 xmk collection 1
11 xmk_2730 在研项目 xmk collection 5
12 xmk_2926 完成项目 xmk collection 17
13 xmk 项目库 yzhnykxyjs zsk 23
14 jxgz_2531 教学课件 jxgz collection 1
15 jxgz 教学工作 yzhnykxyjs zsk 1
16 shfw_2928 农业推广 shfw collection 1
17 shfw_2929 企业合作 shfw collection 1
18 shfw 社会服务 yzhnykxyjs zsk 2
19 xslw_2472 期刊论文 xslw collection 254
20 xslw_2526 会议论文 xslw collection 17
21 xslw_2527 学位论文 xslw collection 32
22 xslw_3597 jkljkl xslw collection 1
23 xslw 学术论文 yzhnykxyjs zsk 304
24 xspy_2533 硕士研究生 xspy collection 6
25 xspy_2534 博士研究生 xspy collection 6
26 xspy_2535 博士后 xspy collection 2
27 xspy 学生培养 yzhnykxyjs zsk 14
28 jssrcsml_2930 中文书刊 jssrcsml collection 1
29 jssrcsml 教师私人藏书目录 yzhnykxyjs zsk 1
30 yzhnykxyjs 浙江大学原子核农业科学研究所 0 community 418