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

将select中的参数插入过程

  •  0
  • civesuas_sine  · 技术社区  · 6 年前

    我有这个选择:

     SELECT  gp.name, gda.value
              FROM   game_definition_aff gda,
             GAME_PARAMETAR gp,
             game_aff ga,
             game_name gn    
             WHERE   4355 = ga.aff_id 
              AND   ga.id = gda.game_aff_id
              AND   gp.id = gda.game_parametar_id
              AND   15 = ga.game_name_id
              AND   gn.game_name_id = ga.game_name_id
              and gp.name in ( 'MIN_BET','MAX_BET','MAX_WIN')
    

    NAME       -      VALUE
    
    MAX_WIN    -      100
    
    MAX_BET    -       50
    
    MIN_BET    -       10
    

    我还有程序:

    get_percentage (i_player_id, o_session_id, royal_tri_win, o_percentage,
                                 o_min_bet,
                                 o_max_bet,
                                 o_max_win,
                                 o_pot
                                );
    

    我该怎么做?

    这是程序,上面的代码在哪里。以及其中定义的所有参数。。

       PROCEDURE open_session_3w (
          i_player_id              NUMBER,
          old_session_id           NUMBER,
          i_ip_address             VARCHAR2,
          i_machine_number         VARCHAR2,
          o_last_bet         OUT   NUMBER,
          o_min_bet          OUT   NUMBER,
          o_max_bet          OUT   NUMBER,
          o_max_win          OUT   NUMBER,
          o_credits          OUT   NUMBER,
          o_session_id       OUT   NUMBER,
          o_state            OUT   VARCHAR2
       )
       IS
          o_percentage       NUMBER;
          o_pot              NUMBER;                    
          pom                weak_cur;
          p_active_session   NUMBER;
          p_parent           number;
          v_max_win           number;
          v_min_bet           number;
          v_max_bet           number;
       BEGIN
         select parent_id into p_parent from casino_users where party_id = i_player_id;
          check_pl_sess_3w (i_player_id, old_session_id);
          o_session_id :=
    
         player.open_new_session (i_player_id, i_ip_address,i_machine_number,'GAME SESSION');
    
     select MAX( CASE WHEN gp.name = 'MAX_WIN' THEN VALUE END ),
               MAX( CASE WHEN gp.name = 'MAX_BET' THEN VALUE END ),
               MAX( CASE WHEN gp.name = 'MIN_BET' THEN VALUE END )
        INTO v_max_win,v_max_bet,v_min_bet
              FROM   game_definition_aff gda,
             GAME_PARAMETAR gp,
             game_aff ga,
             game_name gn    
             WHERE   i_player_id = ga.aff_id 
              AND   ga.id = gda.game_aff_id
              AND   gp.id = gda.game_parametar_id
              AND   15 = ga.game_name_id
              AND   gn.game_name_id = ga.game_name_id
              and gp.name in ( 'MIN_BET','MAX_BET','MAX_WIN') ;
    
          player.get_percentage (i_player_id,
                                 o_session_id,
                                 royal_tri_win,
                                 o_percentage,
                                 COALESCE(v_min_bet,o_min_bet),
                                 COALESCE(v_max_bet,o_max_bet),
                                 COALESCE(v_max_win,o_max_win),
                                 o_pot
                                );
    
    1 回复  |  直到 6 年前
        1
  •  0
  •   Kaushik Nayak    6 年前

    使用MAX(CASE-WHEN)并将每个值存储在3个变量中。

    DECLARE
    v_max_win NUMBER;
    v_max_bet NUMBER;
    v_min_bet NUMBER;
    BEGIN
    
        select MAX( CASE WHEN gp.name = 'MAX_WIN' THEN VALUE END ),
               MAX( CASE WHEN gp.name = 'MAX_BET' THEN VALUE END ),
               MAX( CASE WHEN gp.name = 'MIN_BET' THEN VALUE END )
        INTO v_max_win,v_max_bet,v_min_bet
        FROM
        game_definition_aff gda,
                 GAME_PARAMETAR gp,
                 game_aff ga,
                 game_name gn    
        ..
    
    ..
    
    get_percentage (i_player_id, o_session_id, royal_tri_win, o_percentage,
                                 COALESCE( v_min_bet,o_min_bet),
                                 COALESCE( v_max_bet,o_min_bet),
                                 COALESCE( v_min_win,o_max_win),
                                 o_pot
                                );
    
    END;
    /
    

    或者,如果要避免局部变量,可以使用隐式游标循环。

    for rec in ( --select query above )
    LOOP
       get_percentage (i_player_id, o_session_id, royal_tri_win, o_percentage,
                                 COALESCE( rec.min_bet,o_min_bet),
                                 COALESCE( rec.max_bet,o_min_bet),
                                 COALESCE( rec.min_win,o_max_win),
                                 pot
                                );
    END LOOP;