过程如下

Question

qiao 的 过 程 被 修 改 成 这个
CREATE OR REPLACE PROCEDURE UPDATE_TABLE3_TEST is
  p_GOODFOR1  CONSTANT varchar(6) := 'a';
  p_GOODFOR2  CONSTANT varchar(6) := 'b';
  p_GOODFOR3  CONSTANT varchar(6) := 'c';
  p_GOODFOR4  CONSTANT varchar(6) := 'd';
  p_GOODFOR5  CONSTANT varchar(6) := 'e';
  p_GOODFOR6  CONSTANT varchar(8) := 'f';
  p_GOODFOR7  CONSTANT varchar(6) := 'g';
  p_GOODFOR8  CONSTANT varchar(6) := 'h';
  p_GOODFOR9  CONSTANT varchar(6) := 'i';
  p_GOODFOR10 CONSTANT varchar(6) := 'j';
  p_GOODFOR11 CONSTANT varchar(6) := 'k';
  v_curr_good varchar2(1000);
  CURSOR TABLE1_INFO IS
    SELECT *
      FROM ht_fwmm
     WHERE htztbz = 0
       and issh = 0
       and tbbz = 0
       and sxfgot = 1;
BEGIN
  FOR c_TABLE1_INFO IN TABLE1_INFO LOOP
    IF c_TABLE1_INFO.fwyt = p_GOODFOR1 THEN
      IF c_TABLE1_INFO.jjfs = 1 THEN
        v_CURR_GOOD := c_TABLE1_INFO.aa;
      ELSIF c_TABLE1_INFO.jjfs = 2 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ab;
      ELSIF c_TABLE1_INFO.jjfs = 3 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ac;
      ELSE
        v_CURR_GOOD := NULL;
      END IF;
    
      IF v_CURR_GOOD IS NOT NULL THEN
        UPDATE inxmjy.ht_xxk s
           SET sxf = TO_NUMBER(v_CURR_GOOD) * (SELECT t.cl
                                      FROM INXMJY.SXF_SFBZ t
                                     WHERE t.YT = c_TABLE1_INFO.FWYT
                                       AND ROWNUM = 1)
         WHERE s.hth = c_TABLE1_INFO.htbh;
      
        update ht_fwmm set sxfgot = 0 where htbh = c_table1_info.htbh;
      
      END IF;
    
    ELSIF c_TABLE1_INFO.fwyt = p_GOODFOR2 THEN
    
      IF c_TABLE1_INFO.jjfs = 1 THEN
        v_CURR_GOOD := c_TABLE1_INFO.aa;
      ELSIF c_TABLE1_INFO.jjfs = 2 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ab;
      ELSIF c_TABLE1_INFO.jjfs = 3 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ac;
      ELSE
        v_CURR_GOOD := NULL;
      END IF;
    
      IF v_CURR_GOOD IS NOT NULL THEN
        UPDATE inxmjy.ht_xxk
           SET sxf = v_CURR_GOOD * (SELECT cl
                                      FROM inxmjy.sxf_sfbz
                                     WHERE YT = c_TABLE1_INFO.FWYT
                                       AND ROWNUM = 1)
         WHERE hth = c_TABLE1_INFO.htbh;
      
        update ht_fwmm set sxfgot = 0 where htbh = c_table1_info.htbh;
      END IF;
    
    ELSIF c_TABLE1_INFO.fwyt = p_GOODFOR3 THEN
    
      IF c_TABLE1_INFO.jjfs = 1 THEN
        v_CURR_GOOD := c_TABLE1_INFO.aa;
      ELSIF c_TABLE1_INFO.jjfs = 2 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ab;
      ELSIF c_TABLE1_INFO.jjfs = 3 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ac;
      ELSE
        v_CURR_GOOD := NULL;
      END IF;
    
      IF v_CURR_GOOD IS NOT NULL THEN
        UPDATE inxmjy.ht_xxk
           SET sxf = v_CURR_GOOD * (SELECT cl
                                      FROM inxmjy.sxf_sfbz
                                     WHERE YT = c_TABLE1_INFO.FWYT
                                       AND ROWNUM = 1)
         WHERE hth = c_TABLE1_INFO.htbh;
      
        update ht_fwmm set sxfgot = 0 where htbh = c_table1_info.htbh;
      END IF;
    
    ELSIF c_TABLE1_INFO.fwyt = p_GOODFOR4 THEN
    
      IF c_TABLE1_INFO.jjfs = 1 THEN
        v_CURR_GOOD := c_TABLE1_INFO.aa;
      ELSIF c_TABLE1_INFO.jjfs = 2 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ab;
      ELSIF c_TABLE1_INFO.jjfs = 3 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ac;
      ELSE
        v_CURR_GOOD := NULL;
      END IF;
    
      IF v_CURR_GOOD IS NOT NULL THEN
        UPDATE inxmjy.ht_xxk
           SET sxf = v_CURR_GOOD * (SELECT cl
                                      FROM inxmjy.sxf_sfbz
                                     WHERE YT = c_TABLE1_INFO.FWYT
                                       AND ROWNUM = 1)
         WHERE hth = c_TABLE1_INFO.htbh;
      
        update ht_fwmm set sxfgot = 0 where htbh = c_table1_info.htbh;
      END IF;
    
    ELSIF c_TABLE1_INFO.fwyt = p_GOODFOR5 THEN
    
      IF c_TABLE1_INFO.jjfs = 1 THEN
        v_CURR_GOOD := c_TABLE1_INFO.aa;
      ELSIF c_TABLE1_INFO.jjfs = 2 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ab;
      ELSIF c_TABLE1_INFO.jjfs = 3 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ac;
      ELSE
        v_CURR_GOOD := NULL;
      END IF;
    
      IF v_CURR_GOOD IS NOT NULL THEN
        UPDATE inxmjy.ht_xxk
           SET sxf = v_CURR_GOOD * (SELECT cl
                                      FROM inxmjy.sxf_sfbz
                                     WHERE YT = c_TABLE1_INFO.FWYT
                                       AND ROWNUM = 1)
         WHERE hth = c_TABLE1_INFO.htbh;
      
        update ht_fwmm set sxfgot = 0 where htbh = c_table1_info.htbh;
      END IF;
    
    ELSIF c_TABLE1_INFO.fwyt = p_GOODFOR6 THEN
    
      IF c_TABLE1_INFO.jjfs = 1 THEN
        v_CURR_GOOD := c_TABLE1_INFO.aa;
      ELSIF c_TABLE1_INFO.jjfs = 2 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ab;
      ELSIF c_TABLE1_INFO.jjfs = 3 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ac;
      ELSE
        v_CURR_GOOD := NULL;
      END IF;
    
      IF v_CURR_GOOD IS NOT NULL THEN
        UPDATE inxmjy.ht_xxk
           SET sxf = v_CURR_GOOD * (SELECT cl
                                      FROM inxmjy.sxf_sfbz
                                     WHERE YT = c_TABLE1_INFO.FWYT
                                       AND ROWNUM = 1)
         WHERE hth = c_TABLE1_INFO.htbh;
      
        update ht_fwmm set sxfgot = 0 where htbh = c_table1_info.htbh;
      END IF;
    
    ELSIF c_TABLE1_INFO.fwyt = p_GOODFOR7 THEN
    
      IF c_TABLE1_INFO.jjfs = 1 THEN
        v_CURR_GOOD := c_TABLE1_INFO.aa;
      ELSIF c_TABLE1_INFO.jjfs = 2 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ab;
      ELSIF c_TABLE1_INFO.jjfs = 3 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ac;
      ELSE
        v_CURR_GOOD := NULL;
      END IF;
    
      IF v_CURR_GOOD IS NOT NULL THEN
        UPDATE inxmjy.ht_xxk
           SET sxf = v_CURR_GOOD * (SELECT cl
                                      FROM inxmjy.sxf_sfbz
                                     WHERE YT = c_TABLE1_INFO.FWYT
                                       AND ROWNUM = 1)
         WHERE hth = c_TABLE1_INFO.htbh;
        update ht_fwmm set sxfgot = 0 where htbh = c_table1_info.htbh;
      END IF;
    
    

Answer 1

ELSIF c_TABLE1_INFO.fwyt = p_GOODFOR8 THEN
    
      IF c_TABLE1_INFO.jjfs = 1 THEN
        v_CURR_GOOD := c_TABLE1_INFO.aa;
      ELSIF c_TABLE1_INFO.jjfs = 2 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ab;
      ELSIF c_TABLE1_INFO.jjfs = 3 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ac;
      ELSE
        v_CURR_GOOD := NULL;
      END IF;
    
      IF v_CURR_GOOD IS NOT NULL THEN
        UPDATE inxmjy.ht_xxk
           SET sxf = v_CURR_GOOD * (SELECT cl
                                      FROM inxmjy.sxf_sfbz
                                     WHERE YT = c_TABLE1_INFO.FWYT
                                       AND ROWNUM = 1)
         WHERE hth = c_TABLE1_INFO.htbh;
        update ht_fwmm set sxfgot = 0 where htbh = c_table1_info.htbh;
      END IF;
    
    ELSIF c_TABLE1_INFO.fwyt = p_GOODFOR9 THEN
    
      IF c_TABLE1_INFO.jjfs = 1 THEN
        v_CURR_GOOD := c_TABLE1_INFO.aa;
      ELSIF c_TABLE1_INFO.jjfs = 2 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ab;
      ELSIF c_TABLE1_INFO.jjfs = 3 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ac;
      ELSE
        v_CURR_GOOD := NULL;
      END IF;
    
      IF v_CURR_GOOD IS NOT NULL THEN
        UPDATE inxmjy.ht_xxk
           SET sxf = v_CURR_GOOD * (SELECT cl
                                      FROM inxmjy.sxf_sfbz
                                     WHERE YT = c_TABLE1_INFO.FWYT
                                       AND ROWNUM = 1)
         WHERE hth = c_TABLE1_INFO.htbh;
        update ht_fwmm set sxfgot = 0 where htbh = c_table1_info.htbh;
      END IF;
    
    ELSIF c_TABLE1_INFO.fwyt = p_GOODFOR10 THEN
    
      IF c_TABLE1_INFO.jjfs = 1 THEN
        v_CURR_GOOD := c_TABLE1_INFO.aa;
      ELSIF c_TABLE1_INFO.jjfs = 2 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ab;
      ELSIF c_TABLE1_INFO.jjfs = 3 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ac;
      ELSE
        v_CURR_GOOD := NULL;
      END IF;
    
      IF v_CURR_GOOD IS NOT NULL THEN
        UPDATE inxmjy.ht_xxk
           SET sxf = v_CURR_GOOD * (SELECT cl
                                      FROM inxmjy.sxf_sfbz
                                     WHERE YT = c_TABLE1_INFO.FWYT
                                       AND ROWNUM = 1)
         WHERE hth = c_TABLE1_INFO.htbh;
        update ht_fwmm set sxfgot = 0 where htbh = c_table1_info.htbh;
      END IF;
    
    ELSIF c_TABLE1_INFO.fwyt = p_GOODFOR11 THEN
    
      IF c_TABLE1_INFO.jjfs = 1 THEN
        v_CURR_GOOD := c_TABLE1_INFO.aa;
      ELSIF c_TABLE1_INFO.jjfs = 2 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ab;
      ELSIF c_TABLE1_INFO.jjfs = 3 THEN
        v_CURR_GOOD := c_TABLE1_INFO.ac;
      ELSE
        v_CURR_GOOD := NULL;
      END IF;
    
      IF v_CURR_GOOD IS NOT NULL THEN
        UPDATE inxmjy.ht_xxk
           SET sxf = v_CURR_GOOD * (SELECT cl
                                      FROM inxmjy.sxf_sfbz
                                     WHERE YT = c_TABLE1_INFO.FWYT
                                       AND ROWNUM = 1)
         WHERE hth = c_TABLE1_INFO.htbh;
      
        update ht_fwmm set sxfgot = 0 where htbh = c_table1_info.htbh;
      END IF;
    
    END IF;
  
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SUBSTR('ERROR:' || SQLERRM, 1, 255));
END UPDATE_TABLE3_TEST;

Answer 2

首 先 上 面 那 个 帖 子 的 意 思 就 是 不 要 前 面 的常量，使 用 游 标 来。
其 次 中间update 那 段 是提示 表 或 视 图 找 不 到。。
我独 立 出来 运 行却通过。。
不知道 和 跨 表 空 间 有 没 有 关 系。。
现在需要的解决的问题是 goodfor需要使用游标从table2中获取。 使用二从循环来执行操作

Answer 3

简单来说就是或得　分组后的前三个记录　example :取班级上每个同学成绩最好的三个分数　
select name,rn from (select name,dens_rank()over( partiton by name order by score desc ) rn from table) where rn<4