代码之家 › 专栏 › 技术社区 › civesuas_sine

计算中每场比赛的百分比

database-cursor plsql oracle

0

civesuas_sine · 技术社区 · 7 年前

我需要计算百分比,并在每一场比赛中累积它。我必须设置参数p\u百分比,它是可变的,参数p\u top也是可变的,他的任务是当p\u top满足时,然后做一些事情(更新或插入)。我在这里迷了路。执行循环或获取v\U百分比是否更好。我做得对吗? 谢谢

    CREATE OR REPLACE FUNCTION "jackpot" 
     (p_percentage number
     ,p_top number,
     p_player number,
     p_party_id number
     )
     RETURN NUMBER
    declare           
        Cursor c1 is                                                
            select p_percentage/game_in *100 into v_percentage from game
            where player_id = p_player ;
            rw c1%rowtype;
        v_top number;
        v_percentage number;
        BEGIN 
            open c1;
                 FOR i IN c1                        
                 while v_percentage <= p_top
                     LOOP                               
                     v_percentage:=v_percentage+v_percentage;       
                     end loop; 
                --update something
close c1;
            return v_percentage;
        END;
    end;

编辑

create or replace PROCEDURE super_six_jackpot (
      i_party_id          IN       NUMBER,
      i_jackpot_limit      IN       NUMBER,
      i_jackpot_perc       IN       NUMBER,
      o_pot_size           OUT      NUMBER
   )
   IS
      p_username              VARCHAR2(100);
      p_party_id              number;
      pot_perc                NUMBER;
      pot_size                NUMBER;
      parent_aff              NUMBER;
      ret_pot_size            NUMBER;
      pom                     weak_cur;
   BEGIN
        SELECT  c.party_id, p.aff_id
        into p_party_id, parent_aff
        FROM   casino_users c,pot_by_aff p
       WHERE   c.parent_id = p.aff_id
         AND   c.parent_id = :i_party_id;


        SELECT total
                        INTO   pot_perc
              FROM   (
                SELECT SUM( i_jackpot_perc/game_in * 100 ) OVER ( ORDER BY ROWNUM ) AS total
                FROM   game_record
                WHERE  party_id = p_party_id
                ORDER BY total DESC
              )
              WHERE total <= i_jackpot_limit
              AND   ROWNUM = 1;
                         EXECUTE IMMEDIATE 'UPDATE pot_by_aff
                                 SET bingo_jackpot_size = NVL (total, 0) 
                             WHERE aff_id = :parent_aff
                         RETURNING pot_size
                             INTO :ret_pot_size'
                     USING     parent_aff,
                           OUT ret_pot_size;      
         BEGIN
            OPEN pom FOR 'SELECT bingo_jackpot_size
                            FROM pot_by_aff
                           WHERE aff_id = :parent_aff' 
                           USING parent_aff;
            FETCH pom
             INTO ret_pot_size;
            CLOSE pom;
         END;
      o_pot_size      := ret_pot_size;

      END super_six_jackpot;

2 回复 | 直到 7 年前

1

Steven Feuerstein 7 年前

你只需要知道一个球员的总百分比是达到还是超过了顶部?为此,您可以在单个SQL语句中使用SUM函数,无需循环。我不知道game\u in来自何处,也不知道为什么要在SELECT中使用p\u百分比,但这可能会有所帮助:

CREATE OR REPLACE PROCEDURE give_jackpot (p_percentage    NUMBER,
                                          p_top           NUMBER,
                                          p_player        NUMBER,
                                          p_party_id      NUMBER)
IS
   l_total   NUMBER;
BEGIN
   SELECT SUM (p_percentage / game_in * 100)
     INTO l_total
     FROM game
    WHERE player_id = p_player;

   IF l_total >= p_top
   THEN
      /* insert or update here */
      NULL;
   END IF;
END;

2

0

MT0 7 年前

使用分析函数获取SQL查询中的累计总数。
您不使用 DECLARE 指定一个过程。
如果不需要,请不要使用双引号标识符。
您不能使用 SELECT ... INTO 在光标内。

像这样:

CREATE OR REPLACE FUNCTION jackpot (
  p_percentage number,
  p_top        number,
  p_player     number,
  p_party_id   number
) RETURN NUMBER
IS
   v_percentage number;
BEGIN
  SELECT total
  INTO   v_percentage
  FROM   (
    SELECT SUM( p_percentage/game_in *100 ) OVER ( ORDER BY ROWNUM ) AS total
    FROM   game
    WHERE  player_id = p_player
    ORDER BY total DESC
  )
  WHERE total <= p_top
  AND   ROWNUM = 1;

  --update something
  RETURN v_percentage;
END;
/