- create or replace function get_tpl_mer(mer_id varchar2,
- busi_type_id number) return varchar2 is
- v_result varchar2(100);
- cursor v_job is
- select g.group_code
- from info_group g
- start with g.group_code in
- (select info_mer_addi.belong_group_code
- from info_mer_addi
- where info_mer_addi.mchnt_id = mer_id)
- connect by prior g.group_code = g.par_group_code;
- v_row v_job%rowtype;
- v_count number;
- begin
- select its.tpl_name
- into v_result
- from info_tpl_stmt its
- where its.tpl_id in (select itr.tpl_id
- from info_tpl_rel itr
- where itr.rec_stat = 0
- and itr.rel_id = mer_id)
- and its.tpl_type = 1
- and its.busi_type_id = busi_type_id
- and rownum = 1;
- return v_result;
- Exception
- when others then
- Dbms_Output.put_line(sqlerrm);
- v_result := '';
- for v_row in v_job loop
- select count(0)
- into v_count
- from info_tpl_stmt its
- where its.tpl_id in
- (select itr.tpl_id
- from info_tpl_rel itr
- where itr.rec_stat = 0
- and itr.rel_id = v_row.group_code)
- and its.tpl_type = 2
- and its.busi_type_id = busi_type_id;
- if v_count > 0 then
- select its.tpl_name
- into v_result
- from info_tpl_stmt its
- where its.tpl_id in
- (select itr.tpl_id
- from info_tpl_rel itr
- where itr.rec_stat = 0
- and itr.rel_id = v_row.group_code)
- and its.tpl_type = 2
- and its.busi_type_id = busi_type_id;
- exit;
- end if;
- end loop;
- return v_result;
- end get_tpl_mer;
Oracle创建函数和游标 总结
原创
©著作权归作者所有:来自51CTO博客作者小小Java鸟的原创作品,请联系作者获取转载授权,否则将追究法律责任
这个函数意思是:通过参数查询对应数据。如果没有数据获取父级数据。直到找到对应数据位置。
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
oracle 游标总结
for循环游标DECLARECURSOR C_EMP IS SELECT EMPNO, ENAME, J
SQL for循环 while循环 -
游标和存储过程总结
。。。
职场 游标 存储过程 休闲 -
游标_oracle
https://blog.csdn.net/weixin_41367660/article/details/80449032
mark_oracle .net JAVA