Oracle队列实现

Oracle队列实现

-- 核心技术点：for update

创建测试表

create table t

  ( id       number primary key,

    processed_flag varchar2(1),

    payload  varchar2(20)

);

创建函数索引

create index

  t_idx on

  t( decode( processed_flag, 'N', 'N' ) );

插入几条测试数据

insert into t

  select r,

         case when mod(r,2) = 0 then 'N' else 'Y' end,

         'payload ' || r

    from (select level r

            from dual

         connect by level <= 5)

/

方式一，通过函数返回未锁定行

创建队列获取一行数据的函数

支持Oracle8.0及以后的版本

create or replace

  function get_first_unlocked_row

  return t%rowtype

  as

      resource_busy exception;

      pragma exception_init( resource_busy, -54 );

      l_rec t%rowtype;

  begin

      for x in ( select rowid rid

                   from t

                   where decode(processed_flag,'N','N') = 'N')

      loop

      begin

          select * into l_rec

            from t

           where rowid = x.rid and processed_flag='N'

             for update nowait;

          return l_rec;

      exception

          when resource_busy then null;

          when no_data_found then null;

      end;

      end loop;

      return null;

end;

/

获取未加锁的第一行数据

declare

      l_rec  t%rowtype;

  begin

      l_rec := get_first_unlocked_row;

      dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );

  end;

/

eoda/muphy> I got row 2, payload 2

获取未加锁的第二行数据

declare

      pragma autonomous_transaction;

      l_rec  t%rowtype;

  begin

      l_rec := get_first_unlocked_row;

      dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );

      commit;

  end;

/

eoda/muphy> I got row 4, payload 4

方式二，直接通过skip locked实现

获取未加锁的第一行数据

declare

      l_rec t%rowtype;

      cursor c

      is

      select *

        from t

       where decode(processed_flag,'N','N') = 'N'

         FOR UPDATE

        SKIP LOCKED;

  begin

      open c;

      fetch c into l_rec;

      if ( c%found )

      then

          dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );

      end if;

      close c;

  end;

/

eoda/muphy> I got row 2, payload 2

获取未加锁的第二行数据

declare

      pragma autonomous_transaction;

      l_rec t%rowtype;

      cursor c

      is

      select *

       from t

       where decode(processed_flag,'N','N') = 'N'

         FOR UPDATE

        SKIP LOCKED;

  begin

      open c;

      fetch c into l_rec;

      if ( c%found )

      then

          dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );

      end if;

      close c;

      commit;

  end;

/

eoda/muphy> I got row 4, payload 4

--参考自Oracle编程艺术 深入理解数据库体系结构第三版

转载于:https://www.cnblogs.com/muphy/p/10890075.html