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

declarev_community_type_name communitytype.name%type;beginselect c.name into v_community_type_name from communitytype cwhere c.community_type_id = '&community_type_id';casewhen v_community_type_name = '电子图书'then dbms_output.put_line('查询到电子图书');when v_community_type_name = '新书快读'then dbms_output.put_line('查询到新书快读');elsedbms_output.put_line('查询到其他资源库');end case;end;

case when 也可用于查询语句

select casewhen id is null and parent_id is nullthen 'yzhnykxyjs'when id is null and parent_id is not nullthen parent_idelse idend id,casewhen id is null and parent_id is nullthen (select com.name from community com where com.community_id = 'yzhnykxyjs')when id is null and parent_id is not nullthen (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,casewhen id is null and parent_id is nullthen '0'when id is null and parent_id is not nullthen 'yzhnykxyjs'else parent_idend parent_id,casewhen id is null and parent_id is nullthen 'community'when id is null and parent_id is not nullthen 'zsk'else 'collection'end object_type,countfrom(select cc.collection_id id,count(1) count,cc.community_id parent_idfrom community comjoin community2community c2c on c2c.community_id = com.community_id and c2c.parent_community_id = 'yzhnykxyjs'join community2collection cc on cc.community_id = c2c.community_idjoin collection col on col.collection_id = cc.collection_id and col.withdrawn = 'N'join collection2item c2i on c2i.collection_id = col.collection_idjoin 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

输出如下:

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