<Oracle游标>存储过程中使用游标

最新推荐文章于 2022-04-18 15:58:50 发布

zzxiaozhi

最新推荐文章于 2022-04-18 15:58:50 发布

阅读量442

点赞数

分类专栏： Oracle数据库

本文链接：https://blog.csdn.net/zzxiaozhi/article/details/46291859

版权

Oracle数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

例子：

create or replace procedure Pro_Select_LifeStyle(jg      varchar2,
                                                 m_id    varchar2,
                                                 out_cur out sys_refcursor) is

  v_lsDiet_count1 number(1);
  v_lsDiet1 varchar2(100);
  v_lsDiet2 varchar2(100);

  v_lsDietGr varchar2(100);
  v_lsDietFr varchar2(100);
  v_lsDietMi varchar2(100);

  cursor ysxg is 
                       select t1.v  from  exam t, data t1
                 where t.id = t1.exam_id
                   and t.member_id = m_id
                   and t.表名 like '生活方式%'
                   and t1.n = '膳食习惯' ;
begin
      --查询条数
      select count(*) into v_lsDiet_count1
          from exam x1, data x2
         where x1.id = x2.exam_id
           and x1.member_id = m_id
           and x1.表名 like '生活方式%'
           and x2.n = '膳食习惯' ;
           --循环
           if v_lsDiet_count1>0 then
                    for r in ysxg loop
                        if r.v='1' then
                           v_lsDiet1 :='<d>1;</d>';
                      elsif r.v='2' then
                           v_lsDiet2 :='<d>2;</d>';
                       elsif r.v='3'  then
                           v_lsDietGr :='<d>3;</d>';
                       elsif r.v='4'  then
                           v_lsDietFr :='<d>4;</d>';
                       elsif r.v='5'  then
                           v_lsDietFr :='<d>5;</d>' ;
                        end if;
                   end loop;
           else 
               v_lsDietFr :='<r0>-1</r0>';
           end if;
  open out_cur for
    select <span style="font-family: Arial, Helvetica, sans-serif;">v_lsDiet1 ,</span><span style="font-family: Arial, Helvetica, sans-serif;">v_lsDiet2 ,</span><span style="font-family: Arial, Helvetica, sans-serif;">v_lsDietGr ,</span><span style="font-family: Arial, Helvetica, sans-serif;">v_lsDietFr </span><span style="font-family: Arial, Helvetica, sans-serif;">v_lsDietFr </span><span style="font-family: Arial, Helvetica, sans-serif;"> from dual;</span><span style="font-family: Arial, Helvetica, sans-serif;">
</span>
end Pro_Select_LifeStyle;