刚刚写的ORACLE存储过程

CREATE OR REPLACE PROCEDURE P_SubtractPoint
(
uMsg OUT VARCHAR2
)

-- 在冻结、锁定、退单等要求减点数的操作中执行减点工作
-- SW_HYCJ 会员处理表，记录退单、锁定、冻结的会员记录

AS
-- 定义游标，将未处理的记录检索出来
CURSOR C_HYCL IS SELECT XH,UPPER(HYBH) HYBH,CLSJ,DS,HYZT FROM SW_HYCL WHERE CLID = 0;

uXH NUMBER;uHYBH VARCHAR2(20);uCLSJ DATE;uDS NUMBER;uHYZT VARCHAR2(20);uFJDBH VARCHAR2(20);uFJDQY VARCHAR2(20);
uMAX_CS NUMBER;uCS NUMBER;uExist NUMBER;uAZD NUMBER;uBZD NUMBER;
uLPC NUMBER; uINC NUMBER;uAPQ NUMBER;uAPH NUMBER;uBPQ NUMBER;uBPH NUMBER;
uCOUNTA NUMBER;uCOUNTB NUMBER;

-- 进入事务处理流程
BEGIN

  -- 进入游标处理流程
  FOR CC_HYCL IN C_HYCL LOOP
    uXH   := CC_HYCL.XH;
    uHYBH := CC_HYCL.HYBH;
    uCLSJ := CC_HYCL.CLSJ;
    uDS   := CC_HYCL.DS;
    uHYZT := CC_HYCL.HYZT;

    -- 获得标志，是否存在该会员编号的父节点记录
    SELECT COUNT(*) INTO uExist FROM SW_FZB WHERE UPPER(HYBH) = uHYBH;

    -- 判断是否存在父节点记录
    IF (uExist > 0 ) THEN  -- 如果存在

      -- 获得该会员所属团队的量碰层
      SELECT LPC INTO uLPC FROM SW_CSB WHERE fGroup = F_GetGroup(uHYBH) AND uCLSJ >= KSSJ AND uCLSJ <= JSSJ;

      -- 获得最小层数和最大层数，准备循环处理每一层的父节点
      SELECT MIN(FJDCS),MAX(FJDCS) INTO uCS,uMAX_CS FROM SW_FZB WHERE UPPER(HYBH) = uHYBH;

      -- 按层数循环开始处理每个父节点
      WHILE uCS <= uMAX_CS LOOP

        -- 获得指定层的父节点的编号、区域
        SELECT UPPER(FJDBH),FJDQY INTO uFJDBH,uFJDQY FROM SW_FZB WHERE UPPER(HYBH) = uHYBH AND FJDCS = uCS;

        -- 获得A、B区的余额点数和A、B区的总点数
        SELECT AQYEDS,BQYEDS,AQLJDS,BQLJDS INTO uAPQ,uBPQ,uAZD,uBZD FROM SW_HYB WHERE UPPER(HYBH) = uFJDBH;

        uINC := uDS;  --考虑大于量碰层后再增加点数
        IF (uCS + uLPC - 1) > uMAX_CS THEN
          uINC := 0;
        END IF;

        -- 判断父节点的区域
        IF (uFJDQY = 'A区') THEN
          uAPH := uAPQ - uINC;
          uAZD := uAZD - uDS;
          UPDATE SW_HYB SET
            AQLJRS = AQLJRS - 1,
            AQLJDS = AQLJDS - uDS,
            AQYEDS = AQYEDS - uINC
          WHERE UPPER(HYBH) = uFJDBH;
        ELSE
          uBPH := uBPQ - uINC;
          uBZD := uBZD - uDS;
          UPDATE SW_HYB SET
            BQLJRS = BQLJRS - 1,
            BQLJDS = BQLJDS - uDS,
            BQYEDS = BQYEDS - uINC
          WHERE UPPER(HYBH) = uFJDBH;
        END IF;

        -- 开始处理职务问题
        -- 先处理主任级别的职务
        IF (uAZD < 400 OR uBZD < 400) THEN
          SELECT COUNT(*) INTO uExist FROM SW_ZWB WHERE UPPER(HYBH) = uFJDBH AND ZWMC='主任';
          IF uExist > 0 THEN
            DELETE FROM SW_ZWB WHERE UPPER(HYBH) = uFJDBH AND ZWMC='主任';
            UPDATE SW_ZWB SET ZWMC = '' WHERE UPPER(HYBH) = uFJDBH;
          END IF;
        END IF;

        -- 处理经理级别的职务
        -- 计算A、B区的主任人数
        SELECT COUNT(*) INTO uCOUNTA FROM SW_FZB WHERE UPPER(FJDBH) = uFJDBH AND FJDQY ='A区' AND HYBH IN (SELECT HYBH FROM SW_ZWB WHERE ZWMC = '主任');
        SELECT COUNT(*) INTO uCOUNTB FROM SW_FZB WHERE UPPER(FJDBH) = uFJDBH AND FJDQY ='B区' AND HYBH IN (SELECT HYBH FROM SW_ZWB WHERE ZWMC = '主任');

        IF (uCOUNTA = 0 OR uCOUNTB = 0) THEN
          SELECT COUNT(*) INTO uExist FROM SW_ZWB WHERE UPPER(HYBH) = uFJDBH AND ZWMC = '经理';
          IF uExist > 0 THEN
            DELETE FROM SW_ZWB WHERE UPPER(HYBH) = uFJDBH AND ZWMC='经理';
            UPDATE SW_ZWB SET ZWMC = '主任' WHERE UPPER(HYBH) = uFJDBH;
          END IF;
        END IF;

        -- 处理总监级别的职务
        -- 计算A、B区的经理人数
        SELECT COUNT(*) INTO uCOUNTA FROM SW_FZB WHERE UPPER(FJDBH) = uFJDBH AND FJDQY ='A区' AND HYBH IN (SELECT HYBH FROM SW_ZWB WHERE ZWMC = '经理');
        SELECT COUNT(*) INTO uCOUNTB FROM SW_FZB WHERE UPPER(FJDBH) = uFJDBH AND FJDQY ='B区' AND HYBH IN (SELECT HYBH FROM SW_ZWB WHERE ZWMC = '经理');

        IF (uCOUNTA = 0 OR uCOUNTB = 0) THEN
          SELECT COUNT(*) INTO uExist FROM SW_ZWB WHERE UPPER(HYBH) = uFJDBH AND ZWMC = '总监';
          IF uExist > 0 THEN
            DELETE FROM SW_ZWB WHERE UPPER(HYBH) = uFJDBH AND ZWMC='总监';
            UPDATE SW_ZWB SET ZWMC = '经理' WHERE UPPER(HYBH) = uFJDBH;
          END IF;
        END IF;

        -- 修改 SW_Change ，加入相关变动数据
        DELETE FROM SW_Change WHERE F_Source = uHYBH AND F_Father = uFJDBH AND F_RQ = uCLSJ AND F_BZ = uHYZT;

        INSERT INTO SW_Change (F_XH,F_RQ,F_Source,F_Father,F_Point,F_AQ,F_BQ,F_AH,F_BH,F_JE,F_AREA,F_BZ,F_DESC)
        VALUES (CHANGE_XH.NEXTVAL,uCLSJ,uHYBH,uFJDBH,uDS,uAPQ,uBPQ,uAPH,uBPH,0,uFJDQY,uHYZT,uHYZT||'减点处理');

        -- 层数加1，继续循环处理下一个父节点
        uCS := uCS + 1;
      END LOOP;
    END IF;

    -- 设置该记录为已处理
    UPDATE SW_HYCL SET CLID = 1 WHERE XH = uXH;
  END LOOP;

  COMMIT;
  uMsg := '会员处理事务成功！';
EXCEPTION
  WHEN Others THEN
    ROLLBACK;
    uMsg := '会员处理事务失败！';
END P_SubtractPoint;

-- 手工添加错误信息
-- RAISE_APPLICATION_ERROR(-20010, 'ERROR：违反唯一索引约束！');