Oracle 游标 + PL/SQL块(BEGIN END) 真的很好用...

  最近实习一直在做数据移植，鼓弄了一段时间的Oracle SQL，发现游标和PL/SQL结合那是好用的不得了，真的！

  下面是一小段例子和大家分享一下：

DECLARE 
CURSOR c1 IS select * from data_ofee_plan_NG order by system_type
FOR UPDATE OF FEE_PLAN_ID,FEE_PLAN; 
v_fee_plan_id data_ofee_plan_NG.FEE_PLAN_ID%TYPE := '';
v_fee_plan data_ofee_plan_NG.FEE_PLAN%TYPE := '';
v_tmpNum1 number(2) := 0;  --记录主记账资费脚标
v_tmpNum2 number(2) := 0;  --记录子记账资费脚标
v_Count number(6) := 0;   
v_subMax number(2) := 15;  --记录子记账资费的最大组合数
v_curSysType char(2) := '';   --记录当前系统类型

BEGIN 
   FOR r1 IN c1 LOOP 
  
     IF v_Count=0 THEN
           v_tmpNum1 := 1;
           v_tmpNum2 := 1;
           v_curSysType := r1.system_type;
           v_fee_plan := r1.system_type||'o'||to_char(v_tmpNum1);
           v_fee_plan_id := v_fee_plan||'0'||to_char(v_tmpNum2);
     ELSE IF v_curSysType=r1.system_type  THEN
              v_tmpNum2 := v_tmpNum2 + 1;
              IF v_tmpNum2 > v_subMax THEN
                v_tmpNum1 := v_tmpNum1 + 1;
                v_tmpNum2 := 1;
              END IF;
             
              v_fee_plan := r1.system_type||'o'||to_char(v_tmpNum1);
             
              IF v_tmpNum2 < 10 THEN
                v_fee_plan_id := v_fee_plan||'0'||to_char(v_tmpNum2);
              ELSE
                v_fee_plan_id := v_fee_plan||''||to_char(v_tmpNum2);
              END IF;
            
     ELSE
           v_tmpNum1 := 1;
           v_tmpNum2 := 1;
           v_curSysType := r1.system_type;
           v_fee_plan := r1.system_type||'o'||to_char(v_tmpNum1);
           v_fee_plan_id := v_fee_plan||'0'||to_char(v_tmpNum2);
        END IF;
     END IF;
    
     v_Count := v_Count + 1;
    
     UPDATE data_ofee_plan_NG SET fee_plan=v_fee_plan WHERE CURRENT OF c1; 
     UPDATE data_ofee_plan_NG SET fee_plan_id=v_fee_plan_id WHERE CURRENT OF c1;
    
   END LOOP; 
END;

   大家也不妨一起来体验一下真正意义上的数据库编程哦！