oracle - ORA-02014- 如何更新表中随机选择的行？

Question

我正在尝试从 cards 的表格中随机选择一张卡片带列c_value和 c_suit使用一个程序。选择它后，程序应该更新该条目的 taken字段为“Y”。

create or replace procedure j_prc_sel_card(p_value OUT number,
                                           p_suit OUT number)
AS

   CURSOR CUR_GET_RAND_CARD IS SELECT c_value, 
                                      c_suit
                                 FROM (SELECT c_value, 
                                              c_suit, 
                                              taken
                                         FROM jackson_card
                                     ORDER BY dbms_random.value)
                                WHERE rownum = 1
                        FOR UPDATE OF taken;

BEGIN

  OPEN CUR_GET_RAND_CARD;
  FETCH CUR_GET_RAND_CARD into p_value, p_suit;

  UPDATE jackson_card 
     SET taken = 'Y' 
   WHERE c_value = p_value 
     AND c_suit = p_suit;

  CLOSE CUR_GET_RAND_CARD;

END;

然后我试图获取所选卡并输出它作为开始。有了这个:

SET serveroutput on;

DECLARE v_value number;
        v_suit number;

BEGIN

  j_prc_sel_card(p_value => v_value,p_suit => v_suit);
  DBMS_OUTPUT.PUT_LINE(v_value);
  DBMS_OUTPUT.PUT_LINE(v_suit);

END;
/

但是我得到了标题中所述的错误，似乎我选择随机卡的方式阻止了我进行更新。提前致谢!

Answer 1

这是对场景的不同看法(我也在 a different answer 中解决了您的直接问题)。

鉴于我们确实在构建一个发卡程序(而不是使用业务场景的测试用例)，我不喜欢 TAKEN柱子。更新表列以标记过渡状态似乎是错误的。当我们想玩另一个游戏时会发生什么？

以下解决方案通过预先以随机顺序(洗牌)填充具有所有卡片的数组来解决此问题。通过简单地从堆栈中取出下一个条目来处理卡片。该软件包提供了一种用于用完卡片的方法选择:抛出用户定义的异常或再次循环播放卡片。

create or replace package card_deck is

    no_more_cards exception;
    pragma exception_init(no_more_cards, -20000);

    procedure shuffle;

    function deal_one 
        ( p_yn_continuous in varchar2 := 'N')
        return cards%rowtype;

end card_deck;
/

create or replace package body card_deck is

    type deck_t is table of cards%rowtype;
    the_deck deck_t;

    card_counter pls_integer;

    procedure shuffle is
    begin
        dbms_random.seed (to_number(to_char(sysdate, 'sssss')));
        select *
        bulk collect into the_deck
        from cards
        order by dbms_random.value;
        card_counter := 0;
    end shuffle;

    function deal_one
        ( p_yn_continuous in varchar2 := 'N')
        return cards%rowtype
    is
    begin
        card_counter := card_counter + 1;
        if card_counter > the_deck.count() 
        then
            if p_yn_continuous = 'N'
            then
                raise no_more_cards;
            else
                card_counter := 1;
            end if;
        end if;
        return the_deck(card_counter);
    end deal_one;

end card_deck;
/

这是在行动。不要使用开放式 LOOP如果您将连续交易模式设置为 Y .

SQL> set serveroutput on
SQL>
SQL> declare
  2      my_card cards%rowtype;
  3  begin
  4      card_deck.shuffle;
  5      loop
  6          my_card := card_deck.deal_one;
  7          dbms_output.put_line ('my card is '||my_card.c_suit||my_card.c_value);
  8      end loop;
  9  exception
 10      when card_deck.no_more_cards then
 11          dbms_output.put_line('no more cards!');
 12  end;
 13  /
my card is HA
my card is H7
my card is DJ
my card is CQ
my card is D9
my card is SK
no more cards!

PL/SQL procedure successfully completed.

SQL>

你可能认为我不是在处理一副完整的套牌。你不会是第一个这么想的 ;)